[SOLVED] ZQuery INSERT SQL problem

[JD]

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]

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]

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;

The error occurs on 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

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]

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".

See 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]

When 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]

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.

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]

The 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]

Wouldn'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]

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]

So 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]

Got it. Thanks.