Watch, Follow, &
Connect with Us

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


Welcome, Guest
Guest Settings
Help

Thread: NULL values appearing in first elements of Array DML



Permlink Replies: 2 - Last Post: Dec 29, 2015 1:27 PM Last Post By: Elias Sabbagh
Elias Sabbagh

Posts: 11
Registered: 2/28/00
NULL values appearing in first elements of Array DML
Click to report abuse...   Click to reply to this thread Reply
  Posted: Dec 29, 2015 12:50 PM
RAD Studio 10 Seattle, Window 8 64 bit, Firebird 2.5 embedded

I'm trying to get my app to quickly store 16K records of electric field values to a Firebird table using Array DML from FireDAC. The table definition is

CREATE TABLE DEPHIINCFLDDB
(
PRODUCTID bigint NOT NULL,
J integer NOT NULL,
K integer NOT NULL,
RE Double precision NOT NULL,
IM Double precision NOT NULL,
CONSTRAINT PK_DEPHIINCFLDDB_1 PRIMARY KEY (PRODUCTID,J,K)
);
ALTER TABLE DEPHIINCFLDDB ADD MAG COMPUTED BY (sqrt(re*re+im*im));
ALTER TABLE DEPHIINCFLDDB ADD PH COMPUTED BY (atan2(im,re));

My database presents a view to the outside world that allows reads and writes for certain roles, and uses insert, update, and delete triggers to actually modify the DEPHIINCFLDDB table, while doing some additional logging. The view definition is

CREATE VIEW VEPHIINCFLDDB (PRODUCTID, J, K, RE, IM, MAG, PH)
AS
select Dephiincflddb.PRODUCTID,
Dephiincflddb.j,
Dephiincflddb.k,
Dephiincflddb.re,
Dephiincflddb.im,
Dephiincflddb.mag,
Dephiincflddb.ph
from Dephiincflddb;

and the insert trigger to handle inserting to this view is

SET TERM ^ ;
CREATE TRIGGER VEPHIINCFLDDB_BI0 FOR VEPHIINCFLDDB ACTIVE
BEFORE INSERT POSITION 0
AS
BEGIN
insert into Dephiincflddb values (new.PRODUCTID, new.j, new.k, new.re, new.im);
update productbase set modificationdate=current_timestamp where productbase.id=new.PRODUCTID;
END^
SET TERM ; ^

All pretty straightforward. In my application, I've configured a TFDQuery with the following SQL:

insert into VEPHIINCFLDDB fields (productid, j, k, re, im) values (:productid, :j, :k, :re, :im)

and I use Array DML to step along a data pointer I get from my field computation code like this:

qry.Params.ArraySize := dimz*dimr;
for k := 0 to dimz-1 do
begin
for j := 0 to dimr-1 do
begin
Offset := k*dimr+j;
{ Get re and im here }
if( (re <> 0.0) or (im <> 0.0) ) then
begin
qry.Params[0].AsLargeInts[Offset] := ProductID;
qry.Params[1].AsIntegers[Offset] := j+1; // 1-based
qry.Params[2].AsIntegers[Offset] := k+1; // 1-based
qry.Params[3].AsFloats[Offset] := re;
qry.Params[4].AsFloats[Offset] := im;
end;
end;
end;
qry.Execute(dimz*dimr);

When I execute the query, I get an exception from the Firebird driver, saying that IM is getting a NULL on insert. I fire up the FireDAC Monitor, and sure enough, the first Array DML parameter set is all NULL:

...
-> TFDCustomCommand.InternalExecute [Command="insert into vEPhiIncFldDB (productid, j, k, re, im) values (:productid, :j, :k, :re, :im) ...
-> Execute [Command="insert into vEPhiIncFldDB (productid, j, k, re, im) values (:productid, :j, :k, :re, :im) ...
. isc_dsql_allocate_statement [db_handle=$00000557]
. isc_dsql_prepare [tra_handle=$000005A5, stmt_handle=$000005A7, sql="EXECUTE BLOCK(I0_0 BIGINT=?,I0_1 INTEGER=?,I0_2 INTEGER=?,I0_3 DOUBLE PRECISION=?,I0_4 DOUBLE PRECISION=? ...
. isc_dsql_sql_info [stmt_handle=$000005A7, info=21]
. isc_dsql_describe_bind [stmt_handle=$000005A7, dialect=3]
. isc_dsql_describe_bind [stmt_handle=$000005A7, dialect=3]
-> Sent
. Var [N=0, Name="#1", Type=SQL_INT64, Prec=0, Scale=0, Size=8, Data(0)=NULL]
. Var [N=1, Name="#2", Type=SQL_LONG, Prec=0, Scale=0, Size=4, Data(0)=NULL]
. Var [N=2, Name="#3", Type=SQL_LONG, Prec=0, Scale=0, Size=4, Data(0)=NULL]
. Var [N=3, Name="#4", Type=SQL_DOUBLE, Prec=0, Scale=0, Size=8, Data(0)=NULL]
. Var [N=4, Name="#5", Type=SQL_DOUBLE, Prec=0, Scale=0, Size=8, Data(0)=NULL]
. Var [N=5, Name="#6", Type=SQL_INT64, Prec=0, Scale=0, Size=8, Data(0)=138369]
. Var [N=6, Name="#7", Type=SQL_LONG, Prec=0, Scale=0, Size=4, Data(0)=2]
. Var [N=7, Name="#8", Type=SQL_LONG, Prec=0, Scale=0, Size=4, Data(0)=1]
. Var [N=8, Name="#9", Type=SQL_DOUBLE, Prec=0, Scale=0, Size=8, Data(0)=0.000830228220065483869]
. Var [N=9, Name="#10", Type=SQL_DOUBLE, Prec=0, Scale=0, Size=8, Data(0)=-0.00109069799889230357]
. Var [N=10, Name="#11", Type=SQL_INT64, Prec=0, Scale=0, Size=8, Data(0)=138369]
. Var [N=11, Name="#12", Type=SQL_LONG, Prec=0, Scale=0, Size=4, Data(0)=3]
. Var [N=12, Name="#13", Type=SQL_LONG, Prec=0, Scale=0, Size=4, Data(0)=1]
. Var [N=13, Name="#14", Type=SQL_DOUBLE, Prec=0, Scale=0, Size=8, Data(0)=0.00165372387644789837]
. Var [N=14, Name="#15", Type=SQL_DOUBLE, Prec=0, Scale=0, Size=8, Data(0)=-0.00217555513570815741]
. Var [N=15, Name="#16", Type=SQL_INT64, Prec=0, Scale=0, Size=8, Data(0)=138369]
. Var [N=16, Name="#17", Type=SQL_LONG, Prec=0, Scale=0, Size=4, Data(0)=4]
. Var [N=17, Name="#18", Type=SQL_LONG, Prec=0, Scale=0, Size=4, Data(0)=1]
. Var [N=18, Name="#19", Type=SQL_DOUBLE, Prec=0, Scale=0, Size=8, Data(0)=0.00246383041548481861]
. Var [N=19, Name="#20", Type=SQL_DOUBLE, Prec=0, Scale=0, Size=8, Data(0)=-0.00324879621700662792]
. Var [N=20, Name="#21", Type=SQL_INT64, Prec=0, Scale=0, Size=8, Data(0)=138369]
. Var [N=21, Name="#22", Type=SQL_LONG, Prec=0, Scale=0, Size=4, Data(0)=5]
. Var [N=22, Name="#23", Type=SQL_LONG, Prec=0, Scale=0, Size=4, Data(0)=1]
. Var [N=23, Name="#24", Type=SQL_DOUBLE, Prec=0, Scale=0, Size=8, Data(0)=0.0032540424884428972]
. Var [N=24, Name="#25", Type=SQL_DOUBLE, Prec=0, Scale=0, Size=8, Data(0)=-0.00430477569699743313]
. Var [N=25, Name="#26", Type=SQL_INT64, Prec=0, Scale=0, Size=8, Data(0)=138369]
...

The first record appears to have been skipped, and the other records have been filled in correctly. It looks like there's an off-by-one error somewhere in the FireDAC Array DML code that writes out the EXECUTE BLOCK Firebird statement and fills it with parameters. I've fiddled with different kludges, trying to add an extra empty tuple to the params array, and then skipping it with Execute(size, 1), but nothing helps -- the first tuple of params is always NULL. I've tried inserting to the table rather than the view, and that didn't help, either.

Has anybody else seen this behavior? Is there a workaround? Have I made a mistake in my code, or is there something I'm forgetting?

Thanks,
Elias Sabbagh
Victor Technologies, LLC
Lajos Juhasz

Posts: 801
Registered: 3/14/14
Re: NULL values appearing in first elements of Array DML
Click to report abuse...   Click to reply to this thread Reply
  Posted: Dec 29, 2015 1:14 PM   in response to: Elias Sabbagh in response to: Elias Sabbagh
Elias Sabbagh wrote:

Has anybody else seen this behavior? Is there a workaround? Have I
made a mistake in my code, or is there something I'm forgetting?

From your code snippet it's hard to decide what is the problem. My
guess is that for k=0 and j=0 both re and im are zero and no values are
written in the first element of the parameters array, you can check
that easily using a break point. It would be a bit better to write this
code as following:

  OffSet:=0;
  qry.Params.ArraySize := dimz*dimr;
    for k := 0 to dimz-1 do
    begin
      for j := 0 to dimr-1 do
      begin
        { Get re and im here }
        if( (re <> 0.0) or (im <> 0.0) ) then
        begin
          qry.Params[0].AsLargeInts[Offset] := ProductID;
          qry.Params[1].AsIntegers[Offset] := j+1;   // 1-based
          qry.Params[2].AsIntegers[Offset] := k+1;  // 1-based
          qry.Params[3].AsFloats[Offset] := re;
          qry.Params[4].AsFloats[Offset] := im;
          inc(OffSet);  
        end;
      end;
    end;
    qry.Execute(Offset+1);

This is the only way to make sure that you're using continues indexes
in case when the condition can be false.

Of course this is an untested code!

Elias Sabbagh

Posts: 11
Registered: 2/28/00
Re: NULL values appearing in first elements of Array DML
Click to report abuse...   Click to reply to this thread Reply
  Posted: Dec 29, 2015 1:27 PM   in response to: Lajos Juhasz in response to: Lajos Juhasz
Lajos Juhasz wrote:
Elias Sabbagh wrote:

Has anybody else seen this behavior? Is there a workaround? Have I
made a mistake in my code, or is there something I'm forgetting?

From your code snippet it's hard to decide what is the problem. My
guess is that for k=0 and j=0 both re and im are zero and no values are
written in the first element of the parameters array, you can check
that easily using a break point. It would be a bit better to write this
code as following:

  OffSet:=0;
  qry.Params.ArraySize := dimz*dimr;
    for k := 0 to dimz-1 do
    begin
      for j := 0 to dimr-1 do
      begin
        { Get re and im here }
        if( (re <> 0.0) or (im <> 0.0) ) then
        begin
          qry.Params[0].AsLargeInts[Offset] := ProductID;
          qry.Params[1].AsIntegers[Offset] := j+1;   // 1-based
          qry.Params[2].AsIntegers[Offset] := k+1;  // 1-based
          qry.Params[3].AsFloats[Offset] := re;
          qry.Params[4].AsFloats[Offset] := im;
          inc(OffSet);  
        end;
      end;
    end;
    qry.Execute(Offset+1);

This is the only way to make sure that you're using continues indexes
in case when the condition can be false.

Of course this is an untested code!


Wait a sec, I think you're right -- I had modified this code from its original version which used Insert() and Post() methods on a TDataSet, and which would skip inserting if the data was zero. Of course I've blundered and forgotten that we can't skip defining any parameter tuples just because RE and IM are zero! I need to quickly scan through the data and see how many values are zero, and then set my Params.ArraySize to dimz*dimr - count of zero data. I'll try this, and I'm sure it'll work :)
Legend
Helpful Answer (5 pts)
Correct Answer (10 pts)

Server Response from: ETNAJIVE02