Watch, Follow, &
Connect with Us

For forums, blogs and more please visit our
Developer Tools Community.


Welcome, Guest
Guest Settings
Help

Thread: Problem with FireDAC TFDScript component


This question is not answered. Helpful answers available: 2. Correct answers available: 1.


Permlink Replies: 3 - Last Post: Oct 18, 2016 6:46 PM Last Post By: Michal Mucha
Michal Mucha

Posts: 6
Registered: 2/8/98
Problem with FireDAC TFDScript component  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Sep 30, 2016 11:19 AM
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

Dmitry Arefiev

Posts: 1,406
Registered: 12/7/03
Re: Problem with FireDAC TFDScript component  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Oct 1, 2016 10:32 PM   in response to: Michal Mucha in response to: Michal Mucha
Am I doing something wrong?
Or there is a bug in the FireDAC TFDScript?
What's up?

Could you please post a test application and SQL script to attachments forum ?
So, I will check it.

1. Is there an option to bypass all the script processing by FireDAC,
so the script would be passed to database API as is?

Not much. But set TFDScript.ScriptOptions.MacroExpand to False.

cli_ib = static_cast<TIBDatabase *> (TmpDatabase->CliObj);

(static_cast<TIBDatabase *> (TmpDatabase->CliObj))->Lib->Fisc_dsql_execute_immediate(....);

The documentation does not exist. Studying the source code
does not seem to be the most efficient way to get along.

The documentation covers the API, which we do support. In FireDAC
all DB API wrapping units are not supported, so not documented.

--
With best regards,
Dmitry
Michal Mucha

Posts: 6
Registered: 2/8/98
Re: Problem with FireDAC TFDScript component  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Oct 8, 2016 8:14 PM   in response to: Dmitry Arefiev in response to: Dmitry Arefiev
Could you please post a test application and SQL script to attachments forum ?
So, I will check it.

I am working on this. I don't think it makes sense to work with whole script.
It's bit large. I managed to find some of the "offending" procedures and triggers.

When I added "SET AUTOCOMMIT OFF" To the script i helped, but still
some script commands failed to pass. (BTW should a macro like this
be terminated by semicolon?)

How the command like SET AUTOCOMMIT OFF interacts with
properties:
TmpDatabase->TxOptions->AutoCommit = false;
TmpDatabase->TxOptions->AutoStop = false;
TmpTransact->Options->AutoCommit = false;
TmpTransact->Options->AutoStop = false;

They seem to be less "powerful". Could they be ignored if
the command "SET AUTOCOMMIT OFF" was not placed in the script

1. Is there an option to bypass all the script processing by FireDAC,
so the script would be passed to database API as is?

Not much. But set TFDScript.ScriptOptions.MacroExpand to False.

Unfortunately it didn't help.

cli_ib = static_cast<TIBDatabase *> (TmpDatabase->CliObj);

(static_cast<TIBDatabase *> (TmpDatabase->CliObj))->Lib->Fisc_dsql_execute_immediate(....);
Thank you for this hint. I still try to find the handle of transaction or association of
TFDTransaction with TIBTransaction

While struggling with the problem in one attempt I forgot to assign transaction to
TFDConnection, but it did nor "complain" about it and script started to execute.
Does it mean that a temporary TFDTransaction was create "behind the scenes"?
If yes (as I suspect) what are the values of properties

TFDTransaction->Options->AutoCommit
TmpTransact->Options->AutoStop

In the meantime I came across another problem but I will open another thread for that.

Regards

Michal Mucha
Michal Mucha

Posts: 6
Registered: 2/8/98
Re: Problem with FireDAC TFDScript component  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Oct 18, 2016 6:46 PM   in response to: Michal Mucha in response to: Michal Mucha
Michal Mucha wrote:
Could you please post a test application and SQL script to attachments forum ?
So, I will check it.

I am working on this. I don't think it makes sense to work with whole script.
It's bit large. I managed to find some of the "offending" procedures and triggers.

When I added "SET AUTOCOMMIT OFF" To the script i helped, but still
some script commands failed to pass. (BTW should a macro like this
be terminated by semicolon?)

How the command like SET AUTOCOMMIT OFF interacts with
properties:
TmpDatabase->TxOptions->AutoCommit = false;
TmpDatabase->TxOptions->AutoStop = false;
TmpTransact->Options->AutoCommit = false;
TmpTransact->Options->AutoStop = false;

They seem to be less "powerful". Could they be ignored if
the command "SET AUTOCOMMIT OFF" was not placed in the script

1. Is there an option to bypass all the script processing by FireDAC,
so the script would be passed to database API as is?

Not much. But set TFDScript.ScriptOptions.MacroExpand to False.

Unfortunately it didn't help.

cli_ib = static_cast<TIBDatabase *> (TmpDatabase->CliObj);

(static_cast<TIBDatabase *> (TmpDatabase->CliObj))->Lib->Fisc_dsql_execute_immediate(....);
Thank you for this hint. I still try to find the handle of transaction or association of
TFDTransaction with TIBTransaction

While struggling with the problem in one attempt I forgot to assign transaction to
TFDConnection, but it did nor "complain" about it and script started to execute.
Does it mean that a temporary TFDTransaction was create "behind the scenes"?
If yes (as I suspect) what are the values of properties

TFDTransaction->Options->AutoCommit
TmpTransact->Options->AutoStop

In the meantime I came across another problem but I will open another thread for that.

Regards

Michal Mucha

Hi Dmity
After some checking I found what causes problem.
In case of procedures and triggers the TFDScript
fails to pass a proper script code to the server
when there is line like this:
tmp = 1.0 /(1.0 + NEW.rabat * 0.01);

SET TERM ^ ;
CREATE TRIGGER aTable_Update FOR aTable
BEFORE UPDATE
POSITION 0
AS
...
tmp = 1.0 /(1.0 + NEW.rabat * 0.01);
...
END ^

When changed to
...
tmp2 = 1.0 + NEW.rabat * 0.01;
tmp = 1.0/tmp2;
...
it is passed properly.

Generally I found that code like
r = 1/const;
or
r = 1/variable_id;
works ok.
Code
r = 1/(expression);
is passed wrong.

I did not check it how it behaves in select statement.

It looks like TFDScript is tweaking somehow
the sql script code when it encounters "/(" and passes
incorrect code to the server.

My FireDAC version is 21.0.17707.5020.

Regards

Michal Mucha
Legend
Helpful Answer (5 pts)
Correct Answer (10 pts)

Server Response from: ETNAJIVE02