Generally it does not work (at all).
Environmant
Windows 10 64bit
C++ Builder XE7 Enterprise
Database server Firebird 1.56 (I know it's old but my
program "must" work with all versions of FB 1.56 up).
fbclient.dll is from this version.
I try to port from IBX to FireDAC. For what
I could check so far most of the FireDAC
components work OK, except for TFDScript.
Practically it fails every time I try to use it.
Let's start with the biggest issue.
I am trying to run a script which creates database.
Size of the whole scipt is about 3.2 MB.
The script runs without any problem from isc or
flamerobin or IBX TIBScript.
TFDConnection is used to create database and this works
ok. The empty database is created.
Then I try to run a script and bizarre things happen.
Some examples to illustrate the problem
Case 1.
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
// Heros
TFDConnection *TmpDatabase; // created with designer
TFDTransaction *TmpTransact; // created with designer
TFDScript *TmpScript; // created with designer
...
// TFDSQLScript *ascript = NULL; // created dynamically in code
...
// Action
...
TmpDatabase->TxOptions->AutoCommit = false;
TmpDatabase->TxOptions->AutoStop = false;
TmpTransact->Options->AutoCommit = false;
TmpTransact->Options->AutoStop = false;
TmpDatabase->Open();
...
if (TmpDatabase->Connected)
{
...
TmpScript->SQLScripts->Clear();
// ascript = TmpScript->SQLScripts->Add();
// ascript->SQL->Clear();
TmpScript->ScriptOptions->CommandSeparator = String(_T(";")); // this was added in despair after may tries but did not change anything
TmpScript->ScriptOptions->CommitEachNCommands = 1; // avoided 0 (not to revert to AutoCommit) but it fails regardless the number > 0 used here
TmpScript->ScriptOptions->MaxStringWidth = 150; // I need this
TmpScript->ScriptOptions->SQLDialect = 3;
...
TmpScript->SQLScriptFileName = String(L"c:
test
base
bscript.sql");
// ascript->SQL->LoadFromFile(String(L"c:
test
base
bscript.sql")); // this does not work too, i.e. the same effect
try
{
if (!TmpTransact->Active)
TmpTransact->StartTransaction();
// Application->ProcessMessages();
TmpScript->ValidateAll();
TmpScript->ExecuteAll();
// after processing about 200 lines of script code execution terminates as if
// complete script processing finished correctly. It repeatedly exits after fourth
// stored procedure was created and terminated with
// END^
// SET TERM ; ^
if (TmpTransact->Active)
TmpTransact->Commit();
}
catch (Exception &E)
{
MessageDlg(E.Message, mtInformation, TMsgDlgButtons() << mbOK, 0);
}
...
In this case, as I wrote above in comments, after processing about 200 lines of script
code execution terminates as if script processing finished correctly. It repeatedly
exits after fourth stored procedure was created and terminated with:
END^
SET TERM ; ^
What is wrong?
What might be the reason of the failure?
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
Case 2
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
// Heros
TFDConnection *TmpDatabase; // created with designer
TFDTransaction *TmpTransact; // created with designer
TFDScript *TmpScript; // created with designer
...
TFDSQLScript *ascript = NULL; // created dynamically in code
...
// Action
...
TmpDatabase->TxOptions->AutoCommit = false;
TmpDatabase->TxOptions->AutoStop = false;
TmpTransact->Options->AutoCommit = false;
TmpTransact->Options->AutoStop = false;
TmpDatabase->Open();
...
if (TmpDatabase->Connected)
{
...
TmpScript->SQLScripts->Clear();
ascript = TmpScript->SQLScripts->Add();
ascript->SQL->Clear();
TmpScript->ScriptOptions->CommandSeparator = String(_T(";")); // this was added in despair after may tries but did not change anything
TmpScript->ScriptOptions->CommitEachNCommands = 1; // avoided 0 but it fails regardless the number > 0 used here
TmpScript->ScriptOptions->MaxStringWidth = 150; // I need this
TmpScript->ScriptOptions->SQLDialect = 3;
...
// loop pulling script commands
...
ascript->SQL->Add(String(ptr)); // strings pointed to by ptr are pulled out from buffer
try
{
if (!TmpTransact->Active)
TmpTransact->StartTransaction();
// Application->ProcessMessages();
TmpScript->ValidateAll();
TmpScript->ExecuteAll(); // here the exception is thrown with the message:
// "[FireDAC][Comp][Clnt]-514 Transaction [TmpTransact] must be active" (?? sic!)
// I give up
if (TmpTransact->Active)
TmpTransact->Commit();
ascript->SQL->Clear();
}
catch (Exception &E)
{
MessageDlg(E.Message, mtInformation, TMsgDlgButtons() << mbOK, 0);
// [FireDAC][Comp][Clnt]-514 Transaction [TmpTransact] must be active
// ascript->SQL->SaveToFile(logfnam);
ascript->SQL->Clear();
}
...
// next iteration of the loop
...
In this case the exception is thrown immediately when processing
the first script command. It happens to be:
DECLARE EXTERNAL FUNCTION abs
DOUBLE PRECISION
RETURNS DOUBLE PRECISION BY VALUE
ENTRY_POINT 'IB_UDF_abs' MODULE_NAME 'ib_udf';
Four lines of script code.
The message:
"[FireDAC][Comp][Clnt]-514 Transaction [TmpTransact] must be active"
is, well ... astonishing, since the transaction was
started and active.
Am I doing something wrong?
Or there is a bug in the FireDAC TFDScript?
What's up?
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
Two more questions.
1. Is there an option to bypass all the script processing by FireDAC,
so the script would be passed to database API as is?
2. How to "dig out" database handle from TFDConnection,
so I could write my own function calling isc_.. functions from
API?
Say
...
TFDConnection *TmpDatabase;
TIBDatabase *cli_ib;
...
cli_ib = static_cast<TIBDatabase *> (TmpDatabase->CliObj);
and then?
The documentation does not exist. Studying the source code
does not seem to be the most efficient way to get along.
Well, generally the documentation, delicately speaking,
is inadequate or nonexistent (e.g. try to find something about
FireDAC.Phys.IBWrapper.TIBDatabase) (nb. This latter remark
applies to all libraries, but FiraDAC seems to be outstanding).
FireDAC examples for BCB do not exist at all, and those in Delphi
are rather trivial.
I would appreciate any clues for what I might be doing wrong or
clear information if it is a bug in component library.
Regards
Michal Mucha
Connect with Us