[SOLVED] ZQuery INSERT SQL problem
lazarus / 16-Databases

----------------------------------------------------------------------
JD · 2012-06-19 15:15

Hi there everyone,

I have a database update problem and I need some insight as to how I
can solve the problem.

I'm trying to update a remote Firebird database table called "Foyers"
using Zeos components. The table has 6 fields:
PK_ID_FOYER - type integer (a primary key field)
NOM - type varchar
ADRESSE - type varchar
VILLE - type integer (a foreign key field)
TELEPHONE - type varchar
CONTACT - type varchar

This is the code snippet in question (it is part of a case statement)
is as follows. Note that this code is on the server. The client passes
the parameters to the server and the server updates the table based on
the contents of the parameters sent by the client

Code: [Select]cmdUpdateFoyer:
  begin
    // read the size of the stream sent by the client
    LStreamSize :=
StrToInt(LClientContext.Connection.IOHandler.ReadLn);
    // read the stream
    LClientContext.Connection.IOHandler.ReadStream(LStream,
LStreamSize, False);
    // create the foyers collection
    LFoyerRecord := TFoyers.Create;
    // create the proxy object for managing the collection
    LProxy := TProxy.Create(nil);
    // convert the stream received from the client to a collection
    LProxy.ReadFromStream(LStream, LFoyerRecord);
    //
    try
      with zqryFoyers do
      begin
        //
        try
          // Open the dataset
          Open;
          // Set the SQL text to
          // insert a record if the ID parameter is zero, edit an
existing record if it is not
          if StrToInt(LFoyerRecord[0].PropertyString[0]) = 0 then
            SQL.Text := SQLInsertFoyerRecord
          else
            SQL.Text := SQLModifyFoyerRecord;
          //ShowMessage(SQL.Text);
          // set the parameter values
          for intCount := 0 to zqryFoyers.Params.Count - 1 do
            if (intCount = 0) or (intCount = 3) then    // 0 - ID
primary key field; 3 - ville foreign key field
              zqryFoyers.Params[intCount].AsInteger :=
StrToInt(LFoyerRecord[0].PropertyString[intCount])
            else
              zqryFoyers.Params[intCount].AsString :=
LFoyerRecord[0].PropertyString[intCount];
          // Execute the SQL statement
          ExecSQL;
          // commit the changes to the table
          Connection.Commit;
          // set the operation results to 'success'
          strOperationFlag := strServerUpdateRecordSuccess;
        except
          // rollback the transaction because it failed!
          Connection.Rollback;
          // set the operation results to 'failure'
          strOperationFlag := strServerUpdateRecordFailure;
        end;
        // close the query
        Close;
      end;

After running the code above, I get an EConvertError on the address
field saying that the address is an invalid float!!!!

I'm really at my wits end with this problem. The address field is of
type VARCHAR. Why am I getting an error message saying it is an
invalid float?

----------------------------------------------------------------------
ludob · 2012-06-19 16:02

What is the stack trace when the error occurs? Does it point to Code:
[Select]zqryFoyers.Params[intCount].AsString :=
LFoyerRecord[0].PropertyString[intCount]; or Code: [Select]ExecSQL;
Also Code: [Select]if (intCount = 0) or (intCount = 3) then    // 0 -
ID primary key field; 3 - ville foreign key field doesn't seem to
correspond with your insert query. If I count correctly, FK_ID_VILLE
is index 2 instead of 3.  It looks like your insert query is omitting
the Foyer_id (or whatever is called) and your parameters are off by
1. 

Edit: When your parameters are one off, you are trying to assign the
value of Adresse to FK_ID_VILLE which would explain the EConvertError

----------------------------------------------------------------------
JD · 2012-06-19 16:50

Quote from: ludob on June 19, 2012, 06:02:00 pmWhat is the stack trace
when the error occurs? Does it point to Code:
[Select]zqryFoyers.Params[intCount].AsString :=
LFoyerRecord[0].PropertyString[intCount]; or Code: [Select]ExecSQL;The
error occurs on ExecSQL.

Quote from: ludob on June 19, 2012, 06:02:00 pmAlso Code: [Select]if
(intCount = 0) or (intCount = 3) then    // 0 - ID primary key field;
3 - ville foreign key field doesn't seem to correspond with your
insert query. If I count correctly, FK_ID_VILLE is index 2 instead of
3.  It looks like your insert query is omitting the Foyer_id (or
whatever is called) and your parameters are off by 1. 

Edit: When your parameters are one off, you are trying to assign the
value of Adresse to FK_ID_VILLE which would explain the EConvertError

You have a point there. But the problem is that the ID primary key
field is an auto generated field. I have a generator and a trigger in
the database that computes its value and updates the field for every
insert. That is why I don't include the ID field in the INSERT
statement. See the attached picture for the zqryFoyers paramaters
list.

By the way, if I type the the following insert statement into a
Firebird DB Admin software like IBExpert or FlameRobin and run it, it
works without problems

insert into foyers (Nom, Adresse, Fk_ID_Ville, Telephone, Contact)
    values ('Sir Lancelot', 'At Home', 40, '9876543210', 'Ring Ring
Ring')


So I concluded that the problem is in the program code and not the
INSERT SQL.

----------------------------------------------------------------------
ludob · 2012-06-19 17:05

Code: [Select]By the way, if I type the the following insert statement
into a Firebird DB Admin software like IBExpert or FlameRobin and run
it, it works without problems Sure. But try in flamerobin Code:
[Select]insert into foyers (Nom, Adresse, Fk_ID_Ville, Telephone,
Contact)
    values (0,'Sir Lancelot', 'At Home', 40, '9876543210')Params[0] is
":Nom" in the insert query and I guess Params[0] is ":ID_Foyer". Your
code shows that you assign a string to Params[2] which is
":FK_ID_VILLE".

QuoteSee the attached picture for the zqryFoyers paramaters list.When
you assign a new sql to a zquery the parameter list is re-created from
the query...

----------------------------------------------------------------------
JD · 2012-06-19 19:05

Quote from: ludob on June 19, 2012, 07:05:50 pmWhen you assign a new
sql to a zquery the parameter list is re-created from the query...

Eureka!!! That's it! Thanks a million. I didn't know that. I've
modified the code and it now works

But does this mean that I no longer need to create parameters in the
design mode since the INSERT/MODIFY statements will automatically
recreate the parameter lists.

JD

----------------------------------------------------------------------
ludob · 2012-06-20 07:50

QuoteBut does this mean that I no longer need to create parameters in
the design mode since the INSERT/MODIFY statements will automatically
recreate the parameter lists.Creating parameter lists in design mode
is AFAIK only useful when using a static query (sql never changes).
You can then pre-define the different TParam properties and query them
at run-time (DataType or size fe.). When params are auto-created they
are "type-less". The DataType gets set implicitly by methods like
TParam.AsString, etc.

----------------------------------------------------------------------
BigChimp · 2012-06-20 08:17

Quote from: ludob on June 20, 2012, 09:50:19 amThe DataType gets set
implicitly by methods like TParam.AsString, etc.
Wouldn't the DataType property be set by FPC? Then using AsString etc
casts the value between the DataType and the specified type...

----------------------------------------------------------------------
ludob · 2012-06-20 08:58

QuoteWouldn't the DataType property be set by FPC? Then using AsString
etc casts the value between the DataType and the specified type...I
was talking about the AsString setter. To clarify: in a typical usage
as
Code: [Select]sql.query.text:='update table set name=:name where
ID=:ID';
//sql.params[0].DataType is ftUnknown
sql.params[0].AsString:='BigChimp';
//sql.params[0].DataType is now ftString
The AsString assigns 'BigChimp' to the Value and ftString to the
DataType.

Value is a variant and the AsString getter does a simple variant to
string conversion. DataType is not used there.

----------------------------------------------------------------------
BigChimp · 2012-06-20 09:04

I was talking about AsString, too... but didn't know the param
DataType was influenced by AsString and friends.

So in fact setting params[n].DataType is rather superfluous as you can
use the As... setters/getters anwyay?

Thanks - you live & learn.

----------------------------------------------------------------------
ludob · 2012-06-20 09:50

QuoteSo in fact setting params[n].DataType is rather superfluous as
you can use the As... setters/getters anwyay?Setting Param.Datatype is
important for ParamType=ptOutput in the Oracle connection when values
are returned in parameters as in 'insert into.. returning name into
:name' type of clauses.  There you would something like
Code: [Select]q1.sql.Text:='insert into testdate values (:idx,:dt)
returning dt into :dtr';
q1.Params.ParamByName('idx').asinteger := random(100);
q1.Params.ParamByName('dt').asdate := now();
q1.Params.ParamByName('dtr').ParamType:=ptOutput;
q1.Params.ParamByName('dtr').DataType:=ftDate;
q1.ExecSQL;
v:=q1.Params.ParamByName('dtr').AsDate;

There are also cases where influencing data binding could be useful.
For example:
Code: [Select]q1.Params.ParamByName('guid').AsString='{21EC2020-3AEA-
1069-A2DD-08002B30309D}';
q1.Params.ParamByName('guid').DataType:=ftGuid;
ftGuid is supported in Postgresql but there is no Param.AsGUID.

----------------------------------------------------------------------
BigChimp · 2012-06-20 10:22

Got it. Thanks.