[BCB / MySQL] Temporary tables

[edwardvb]

i am trying to upgrade from an old Zeos-version (6.1.5-stable) to version 6.6.6-stable and at the same time upgrade MySQL version 4.0 (!!!) to version 5. Doing that for Borland C++ Builder 6, it is not going smooth, but I managed to get it installed. However, I am having a problem with the following piece of code, which worked fine in version 6.1.5-stable:

Code: Select all

   AnsiString SQLstring;

   SQLstring = "CREATE TEMPORARY TABLE temp SELECT * FROM mytable";
   ZSQLProcessor1->Script->Text = SQLstring;
   ZSQLProcessor1->Execute();
   ZQuery1->Active = false;
   ZQuery1->SQL->Add("SELECT * FROM temp");
   ZQuery1->Active = true;

as said, this code always working in version 6.1.5-stable, but now i get the error: 'SQL Error: Table 'temp' doesn't exist'.
When I perform both queries in MySQL workbench, it works fine, so I have the feeling that it has something to with a setting of one of the zeos-objects, but i am not sure.

I checked the processlist in MySQL en saw that both the ZSQLProcessor1 and ZQuery1 object use the same MySQL connection, so that should not be the problem.

Any ideas? thanks !

[edwardvb]

Hmm...something to add: i changed the ZSQLscript to:

Code: Select all

SQLstring = "UPDATE mytable SET mycolumn = CONCAT(mycolumn, '-1')"; 
   ZSQLProcessor1->Script->Text = SQLstring; 
   ZSQLProcessor1->Execute(); 

but the column mycolumn is not changed at all... so i think somehow my ZSQLProcessor is not Executed....


edit:
ok, I found some things again...

first of all, when i set the script through the code, nothing happens... but when i set the script in the ZSQLProcessor object itself, then the query is run.... so somehow the script is not set via the code...

so i tried this:

Code: Select all

TStringList *Test = new TStringList;
Test->Clear();
Test->Add(SQLstring);
ZSQLProcessor1->Script = Test;
ZSQLProcessor1->Execute();

And then my script was run... i did a little dance...

but after that i tried it this way:

Code: Select all

ZSQLProcessor1->Script = new TStringList;
ZSQLProcessor1->Script->Clear();
ZSQLProcessor1->Script->Add(SQLstring);
ZSQLProcessor1->Execute();

but then the code crashed already at the "Clear"-statement... AccessViolation!

I still have a the feeling that it is just a setting that I forgot to set...

[geert]

I don't know if this helps (it's not completely the same), but this works for me in BDS 2006 (used extensively) :

Code: Select all

// I do not assign a different stringlist to ->Script, as you do in your last example

ZSQLProcessor1->Script->Clear();
ZSQLProcessor1->DelimiterType = dtDefault;
ZSQLProcessor1->Script->LoadFromFile("script.sql");  // This is of course not the same as what you do

ZConnection->StartTransaction();
ZSQLProcessor1->Execute();
ZConnection->Commit();

So - I don't know if this is of much help, but the above works for me...
(edit : I seem to be using 6.6.5-stable, not 6.6.6-stable)

[mdaems]

As far as I understand there's no need to create a new TStrings object and then assign it to the TZSQLProcessor->Script.
Actually, I think it's dangerous as internally the Script is represented by a TZSQLStrings object, which probably causes the AccessViolation.
Just remove " ZSQLProcessor1->Script = new TStringList; " as the Script is already instantiated, so need to do it again (and certainly not with a different object type.

Mark