dbExpress +MySQL TEMPORARY TABLE(s)


2004-04-05 06:27:35 PM
delphi1
D7, MysQL 4.0.18
Is there a way to see/work with MySQLs:
CREATE TEMPORARY TABLE ...
in ClientDataSet (even ReadOnly just to show some reports)
As you all (should) know MySQL driver limits:
- Temp Tables are unique and valid only in SAME connection that created
them
- 1 Statement per connection (1 at a time)
...so dbExpress actually "clone" connection to do its job
and voila TEMPTables are lost/invisible in New/Cloned connection.
Any workaround? Anything that helps (ease the pain)... Anyone?
He-e-e-lp?!?
1. so far I have managed to let it show TempTable in cds but is it right
way to do so?
c1: TSQLConnection; // Separate connection
tsqlds1 : TSQLDataSet;
tdsp1 : TDataSetProvider;
tcds1 : TClientDataset;
tds1 : TDataSource;
DBGRid1 : TDBGrid;
c1.AutoClone := False;
dm.tsds1.GetMetadata:= False;
// No Metadata = no indexes but All processing done on Server anyway
// is THIS right? Is it Enough? s.th. else that will "AutoClone"
connection
// I need cds only to show result(set) i.e. ReadOnly
2. While trying to resolve this issue I have stumbled on another dbExpress
"bug"
whether it is in code or in Help subsystem.. Who knows? - please speakup
or should i file request at QC? ;-)
Help states: TSQLConnection :
function ExecuteDirect(const SQL: string ): Integer;
ExecuteDirect returns 0 if the command was successfully executed.
Otherwise, it returns a dbExpress error code.
but ACTUALLY if SQL statement is UPDATE or INSERT: function returns
AffectedRows instead?!?
It's easy to test: Since I have got to 1) CREATE TABLE then 2)INSERT or
UPDATE thru same connection I decided to try ExecuteDirect (no much
choice here)
but ot may dismay 1) works as expected while 2) ... ???
1)
ErrorCode := dm.c1.ExecuteDirect(Format(sCreateTbl, ['tmp']));
if ErrorCode <>0 then // returns 0 :-)
reLog.Lines.Append('dbExpress error code:'+IntToStr(ErrorCode));
2)
ErrorCode := dm.c1.ExecuteDirect('INSERT INTO tmp VALUES(1,1);'));
if ErrorCode <>0 then // returns 1 = RowsAffected
reLog.Lines.Append('dbExpress error code:'+IntToStr(ErrorCode));
// still dont believe. Try this:
dm.c1.ExecuteDirect('INSERT INTO tmp VALUES(1,1);'));
dm.c1.ExecuteDirect('INSERT INTO tmp VALUES(1,1);'));
dm.c1.ExecuteDirect('INSERT INTO tmp VALUES(1,1);'));
ErrorCode := dm.c1.ExecuteDirect('UPDATE tmp SET id=2 WHERE id=1);'));
if ErrorCode <>0 then // returns 3 = RowsAffected
reLog.Lines.Append('dbExpress error code:'+IntToStr(ErrorCode));
// so ExecuteDirtect seems to return RowsAffected instead of "dbExpress
error code"
// in this case is this dbExpress HELP mistake or TSQLConnection should
be FIXed
What if RowsAffected = 12293;
// is this means Multiple connections not supported. OR I have just
affected 12293 rows?
12293 3005 Multiple connections not supported.
for Ref.
see: www.delphifaq.com/fq/q0004.shtml