Delphi DBExpress SQLDataSet.ExecSQL freezes

Question

I am comparing BDE and DBExpress components and try to execute simple update in TMPTABLE.

When I use BDE TQuery Query.ExecSQL works fine, but if I use DBExpress TSQLDataSet, it freezes in SQLDataSet.ExecSQL.

I use Delphi XE3 in Win7 64b. Database is Oracle.
BDE Query uses ODBC-connection, but DBExpress SQLDataSet uses Oracle driver.

What is wrong with this?

procedure Test;
var
  Query      : TQuery;       // dbe 
  SQLDataSet : TSQLDataSet;  // dbexpress 
begin  
  Query := TQuery.Create(nil);   
  Query.DatabaseName := 'DatabaseName';  
  Query.SQL.Add('update TMPTABLE set STATUS = 1');  
  Query.ExecSQL; // <-- OK  
  Query.Free;  

  SQLDataSet := TSQLDataSet.Create(nil);  
  SQLDataSet.SQLConnection := SQLConnection;  
  SQLDataSet.CommandType   := ctQuery;  
  SQLDataSet.CommandText   := 'update TMPTABLE set STATUS = 1';  
  SQLDataSet.ExecSQL;  // <-- Freezes here  
  SQLDataSet.Free;  
end;

These are SQLConnection Params:

DriverUnit=Data.DBXOracle
DriverPackageLoader=TDBXDynalinkDriverLoader,DBXCommonDriver170.bpl
 DriverAssemblyLoader=Borland.Data.TDBXDynalinkDriverLoader,Borland.Data.DbxCommonDriver,Version=17.0.0.0,Culture=neutral,PublicKeyToken=91d62ebb5b0d1b1b
MetaDataPackageLoader=TDBXOracleMetaDataCommandFactory,DbxOracleDriver170.bpl
MetaDataAssemblyLoader=Borland.Data.TDBXOracleMetaDataCommandFactory,Borland.Data.DbxOracleDriver,Version=17.0.0.0,Culture=neutral,PublicKeyToken=91d62ebb5b0d1b1b
GetDriverFunc=getSQLDriverORACLE
LibraryName=dbxora.dll
LibraryNameOsx=libsqlora.dylib
VendorLib=oci.dll
VendorLibWin64=oci.dll
VendorLibOsx=libociei.dylib
DataBase=Database Name
User_Name=user
Password=password
MaxBlobSize=-1
LocaleCode=0000
IsolationLevel=ReadCommitted
RowsetSize=20
OSAuthentication=False
MultipleTransactions=False
TrimChar=False
BlobSize=-1
ErrorResourceFile=
OS Authentication=False
Multiple Transaction=False
Trim Char=False
Decimal Separator=.

It freezes. I have to reset debugging. What debug do you mean? — pKarelian, Oct 14 '13 at 13:28
Can you retrieve records via select, and then do a `SQLDataSet.Open`? maybe you have problems with your provider/driver. — kobik, Oct 14 '13 at 14:14
Select gives dataset. Connection to database is ok. I tried DbGo component too but update fails again. BDE component works fine. — pKarelian, Oct 14 '13 at 14:33
Assuming dbExpress has source code provided, enable DebugDCUs and step into the execSQL (and into functions therein) to find out what line it's actually freezing on. This will give a better clue as to what is going on — Matt Allwood, Oct 14 '13 at 15:21
Usually `T[SQL]DataSet` is not used in conjunction with `ExecSQL`. try to use `TSQLQuery`. I know it's a long-shot because `TSQLDataSet` should also work... — kobik, Oct 14 '13 at 16:02
I try to compile 32-bit application in 64-bit Win7 Delphi XE3. How can I be sure that Oracle driver is 32-bit in TSQLConnection? — pKarelian, Oct 15 '13 at 06:00
Thanks Matt for DebugDCU hint! CheckResult(FMethodTable.FDBXCommand_Execute(FCommandHandle, ReaderHandle)) in function TDBXDynalinkCommand.DerivedExecuteQuery (Data.DBXDynalink.pas) where my update freezes. — pKarelian, Oct 15 '13 at 06:07
So this ('update TMPTABLE set STATUS = 1') is impossible with dbExpress. BDE rules! — pKarelian, Nov 05 '13 at 11:47

score 0 · Answer 1 · answered Jan 21 '14 at 05:55

I finally understood what was wrong with this. Example was too simple. I didn't see that there was already bde transaction running like this.

DataBase.StartTransaction; // BDE StartTansaction 

BDEQuery.ExecSQL;       // <-- BDE update OK  
SQLDataSet.ExecSQL;     // <-- DBX update Freezes, because it waits for bde commit  

DataBase.Commit;        //  BDE Commit

Delphi DBExpress SQLDataSet.ExecSQL freezes

1 Answers1