Jump to content

SQL Statement: "ALTER TABLE .." problem


erich.wanker

Recommended Posts

Hello folks,

 

i have a little question:

If i use a sql statement  "ALTER TABLE" to create a new field at runtime - the new generated field seems to be "write protected" since i RESTART THE APACHE WEBSERVER ? .. The Statement: "Create Table with different fields" works normal ...

 

 

Delpi XE4  with  a Firebird 2.5 Database and ZeosLib-Components (7.1)

 

  • the ZConnection to the database is in the uniservermodule ( if not here - the database is not refreshing "to and from" other clients ! ..)
  • the ZQuerys and Datasource Components are in MainModule 

i tested differnet statements... allways the same result: "Create table"  works normal .. "Alter Table" just works correct after i reload the unigui.dll

 

 

 

 

Any ideas, why this happens ? ..

 

 

 

 

 

Example:

I create a new field at runtime (alter table)

- after this step -

i create a ZQuery at runtime  

- after this step -

i create a Dataset at runtime (connected with the ZQuery)

- after this step -

i create a uniDbEdit field with datasource and datafield 

 

.. now the uniDbEdit is "locked" .. and if i try to write in the field - i get a browser-message "the field "XY" can not be changed"

 

After RESTART of the WEBSERVER

..the new field exists allready

i create a ZQuery at runtime  

- after this step -

i create a Dataset at runtime (connected with the ZQuery)

- after this step -

i create a uniDbEdit field with datasource and datafield 

 

.. now the uniDbEdit is working perfect

 

 

 

 

how i try to create fields:

 

Way I

ASQL :='ALTER TABLE '+tabellenname+' ADD "'+feldname+'" CHAR(200) CHARACTER SET ISO8859_1 COLLATE DE_DE';
uniservermodule.ZConnection1.ExecuteDirect( ASQL ) ;
 
Way II
unimainmodule.ZUPDATE.SQL.add('ALTER TABLE '+tabellenname+' ADD "'+feldname+'" CHAR(200) CHARACTER SET ISO8859_1 COLLATE DE_DE');
unimainmodule.ZUPDATE.ExecSQL;

 

Link to comment
Share on other sites

Delpi XE4  with  a Firebird 2.5 Database and ZeosLib-Components (7.1)

 

  • the ZConnection to the database is in the uniservermodule ( if not here - the database is not refreshing "to and from" other clients ! ..)
  • the ZQuerys and Datasource Components are in MainModule 

 

Are you sure that ZesoLib/ZConnection is thread safe?

 

If not, your problem is completely another...

Link to comment
Share on other sites

 

This looks good:

 

We're using Zeos in heavily multithreaded servers and it works excellently. The only thing we've noticed in regard to ZSqlMonitor is that it hooks into all possible ZConnection objects, regardless of thread, so you should only have one in your app.

 

But this looks bad:

 

I know that Zeos is a little bit sensitive (very sensitive, actually) if you access the same ZConnection from several different threads (i.e. you create Queries in different threads that link to the same global Connection object)

As soon as you have two threads running, you either need to serialize access to queries/connections through critical sections, or instantiate one ZConnection per thread and tie the respective ZQuery objects to the thread-specific ZConnection.

Link to comment
Share on other sites

hmmm... i use ZeosLib a long time ...  for small and simple Database applications .. no threads or something ...

 

i use Delphi XE4 Professional ... and i did never use the included Interbase components like Tables, Querys and so on ..

 

Question:

...what kind of components do you recommend in this case (uniGui webapplication and a Firebird Database) ..if possible: a cheap suggestion ;-) ?

 

thanx for suggestion

:-)

Erich

 

p.s.: this "thread-problem-thing" could explain a lot of "strange" foible.. you remind http://forums.unigui.com/index.php?/topic/3637-bug-unisessionaddjs-produces-access-violations/ ?   .. i tryed to reproduce the problem in a small testcase (for farshad..) .. but was not able to reproduce the error in a simple small demo ...

Link to comment
Share on other sites

Question:

...what kind of components do you recommend in this case (uniGui webapplication and a Firebird Database) ..if possible: a cheap suggestion ;-) ?

 

If the vendor does not explicitially say it is thread safe, you must use a own connection for each session (-> place connection  component in MainModule). I use the built in intebase components for firebird database.

 

p.s.: this "thread-problem-thing" could explain a lot of "strange" foible.. you remind http://forums.unigui.com/index.php?/topic/3637-bug-unisessionaddjs-produces-access-violations/ ?   .. i tryed to reproduce the problem in a small testcase (for farshad..) .. but was not able to reproduce the error in a simple small demo ...

Such problems are not really reproducable. Thats the biggest problem with testing of threaded applications. And: You test with 2 - 3 connections and may have luck, the customer "tests" with > 100 connections/threads...

Link to comment
Share on other sites

thanks for infos ..

 

a last question ;-) ...

 

 

i had problems with "ZConnection1" placed in the uniMainModule ... the unigui-testapplication worked - but if i created a new record .. the new record was just visible on the machine, where the new record was created .. on a other machine no refresh  or some code was able to show the new record .. just a "load the uniGui.DLL new" showed the new record ...

 

So i placed the "ZConnection1" on the uniservermodule and all ZQuerys on uniMainModule ... and new records where shown on all machines .

 

 

QUESTION: If i use the delphi intebase components .. where should i put the connection and the querys ?  have i a problem with new records again ?

 

ThanX for your support

Erich

Link to comment
Share on other sites

i had problems with "ZConnection1" placed in the uniMainModule ... the unigui-testapplication worked - but if i created a new record .. the new record was just visible on the machine, where the new record was created .. on a other machine no refresh  or some code was able to show the new record .. just a "load the uniGui.DLL new" showed the new record ...

So i placed the "ZConnection1" on the uniservermodule and all ZQuerys on uniMainModule ... and new records where shown on all machines .

 

Be sure there is no open transaction (make a "commit") and/or look how to set "transaction isolation level" in Zeos:

- read commited: transaction sees new data by others only if data is commited

- read uncommited / dirty read: transactions sees data before commit

- snapshot: transaction makes a snapshot, changes by others are completly ignored

- ...

 

QUESTION: If i use the delphi intebase components .. where should i put the connection and the querys ?  have i a problem with new records again ?

I have the connection in MainModule. Queries can be on MainModule or on other modules and forms.

You can choose the isolation level you want.

 

Schöne Grüße aus Deutschland nach Österreich...

Link to comment
Share on other sites

In Delphi now included AnyDac - powefull component pack. Another rooles  DB components - unidac.

All db components have restriction on TConnection Component depends on sqlserver model. For example UNIDAC fpor SQLServer (etc sdac) must have  personsl TUniCOnnection per thread. And because need put TUniConnection to MainDataModule with is unic per thread not in ServerModule wich is separated between threads. Database layer MSSQL Client can hand one connection for several queries simalteneozly thred-safe but AnyDac Sdac Zeos all falld if put Connection component in ServerModule :( Be ready. Impossible put ZConnection1 in ServerModule.

 

 

Nik Gurov

(russian)

 

 

Are the SDAC connection components thread-safe?

Yes, SDAC is thread-safe, but there is a restriction. The same TMSConnection object cannot be used in several threads at the same time. So if you have a multithreaded application, you should have a TMSConnection object for each thread that uses SDAC.

- See more at: http://www.devart.com/sdac/faq.html#sthash.0ThnNgQe.dpuf
Link to comment
Share on other sites

QUESTION: If i use the delphi intebase components .. where should i put the connection and the querys ?  have i a problem with new records again ?

 

 

Hi erich.wanker.

 

 

I use standard components InterBase.
connection and the queries, it's desirable put in DataModule...
 
Sincerely
Link to comment
Share on other sites

ZComponents works with UniGui.

I have an implementation for 30 clients working 2 shifts/day.

It worked ok for the last year or so. No Problem.

 

About changing the structure of the database I have some observations.

1. Table or any query on the table to be modified must be closed.

2. Fields not to be registered in the application for that particular table.

3. Try ZSQLProcessor for the alter script.

4. If it's the case and if you need a temporary storage use TVirtualTable ( free component from DevArt).

    You can get away with it more elegant than changing something on the server. 

5. If you use Interbase Components with a Firebird server you're asking for trouble.

6. IBDac or UniDac also works perfectly with UniGui including Firebird ( tested !).

Link to comment
Share on other sites

Please sign in to comment

You will be able to leave a comment after signing in



Sign In Now
×
×
  • Create New...