[solved] Buggy ZUpdateSQL component

[JD]

Hi there,

After labouring over ZUpdateSQL for a week, I've concluded that the component is buggy when adding parameters.

At runtime, I've noticed that sometimes, the number of parameters I see in the Lazarus IDE object inspector is not the same as the number of parameters in the Params property dialog.

See the attached screenshot.

When this happens, I get a List index() out of bounds error. This is a big problem for me. How do I get around this problem? Is there a fix in the svn version?

Thanks for your help.

[ism]

Try last svn version testing-egonhugeist

[EgonHugeist]

JD,

"Buggy ZUpdateSQL component" is a hard judgement. Let's find out if that is true. I see clearly what you mean. I've tried to reproduce you issue and i fail.

Here we need some code examples of your statements to chack what's going wrong. The params, you see are only the params, which where detected in the ModifySQL string. Normaly all 3 statement should have the same params and count of them.

And i'm still wondering for what you need that component? Don't you think it is slightly a better solution to assign your UpdateStrings to the TZQuery.SQL? Then assign the Params and say TZQuery.ExecSQL; ? This component is (in my mind) made for complicated updates like you have some Fields from joined tables which the TZQuery-component can't generate by itselves. And also nobody use it like you, i think. Anyway if you want to use it like this then it should be possible.

So please give us more details...

Michael

[JD]

I've noticed that the problem arises when I try to INSERT

The SQL generated by the component is as shown below:

Code: Select all

INSERT INTO foyers
  (PK_ID_FOYER, NOM, ADRESSE, VILLE, CODE_POSTAL, FK_ID_PAYS, TELEPHONE, 
   CONTACT)
VALUES
  (:PK_ID_FOYER, :NOM, :ADRESSE, :VILLE, :CODE_POSTAL, :FK_ID_PAYS, :TELEPHONE, 
   :CONTACT)

I then modify the generated SQL removing the automatically generated primary key and some other non-essential fields

Code: Select all

INSERT INTO foyers
  (NOM, ADRESSE, VILLE, CODE_POSTAL, TELEPHONE, CONTACT)
VALUES
  (:NOM, :ADRESSE, :VILLE, :CODE_POSTAL, :TELEPHONE, :CONTACT)

The code that fills the parameters sent remotely by the client is as follows:

Code: Select all

// this is part of a CASE statement
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 qryWrite do
        begin
          // Set the update object
          UpdateObject := updFoyer;
          // Set the SQL text
          SQL.Text := 'select * from foyers';
          // Open the query
          Open;
          // set the parameter values
          for intCount := 0 to updFoyer.ParamCount - 1 do
             if intCount = 0 then
                updFoyer.Params[intCount].AsInteger := StrToInt(LFoyerRecord[0].PropertyString[intCount])
             else
                updFoyer.Params[intCount].AsString := LFoyerRecord[0].PropertyString[intCount];
           //
           try
             // Insert a record if the ID parameter is zero, edit an existing record if it is not
             if updFoyer.Params[0].AsInteger = 0 then
               Insert
             else
               Edit;
               // Post the changes to the database
             Post;
             // commit the changes to the table
             Connection.Commit;
             // close the query
             Close;
             // 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;
        end;

When I run the application, it inserts a blank record into the table !!!

If I use ZQuery instead, will the various string variables for Insert, Modify not be truncated because the SQL statement is too long. It may be fine for the example here but I have other tables with many fields.



Thanks for your assistance.

[EgonHugeist]

JD,

Ok, i'll give you a small advice now from my smartphone.

Tzquery.updateobject := YourUpdateSql-component;
Now assign your statements.
Tzquery.edit/insert/delete; //now the Query assigns the given statement from the UpdateObject
//normaly it is the best to assign the ParamTypes for the Param now but you can use all as string which works equal
Tzquery.Param[x].asstring :='the value'; //you see: don't use the updateobject use the ZQuery instead
.
.
ZQuery.Post.

For your case forget about this component. You don't need it here.
Just use:
ZQuery.SQL.Text := 'your statement';
Now assign the params... (ParamCheck=True must be set)
ZQuery.ExecSQL or ExecuteUpdate;

This way is more elegant and equal valid.

I have never used the component like you did it. So actually i can not say if that is valid. I'll proof this next weekend. This component is made that you do not have to close the ZQuery, before you execute a statement like i propose in the second way, for ReadOnly-Fields/Queries or complicated statements. Both ways are proofed and do work perfektly. I know only one small bug which Zeos does not support in this case: If you name a param as :and ,:where ... or :like (keywords) then they where not detected yet. On the other hand eyerythink is fine. Trust me on that.

But your picture shows me that something is going wrong here. This i want to find out. So i hope you are available if i have some more questions. Actually i think this is not a bug it is just a differnce between the availabe fields of the selected table and the statement you have assigned.

Michael

[JD]

Thanks for the tips, Michael. I'll make the changes you suggested and I'll let you know how it turns out.

[EgonHugeist]

JD,

ok i'm waiting for repley... One thing i want to tell you. The params of the Upate-Component are not made to assign the values. You have them only to say which type they are from.

Michael

[JD]

I've incorporated the changes you suggested and the changed code is as shown below:

Code: Select all

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?

[JD]

Problem solved!!!

I just learned that a ZQuery's parameter list is recreated whenever a new SQL is assigned to the query. This also explains the discrepancy in the object inspector and the parameter count in the first picture I attached. So it was not a bug after all.

Once I realised this, I modified the for..loop that uses the parameter count and the code now works normally.

@EgonHugeist Thanks a million for your suggestions and your patience.

[EgonHugeist]

JD,

i saw your post this morning but was out of time to answer. Yep you're right and nice to see that you've learned how to use the Parameters right. Thank you too for confirmation that everything is fine.

Michael

Btw. You don't need to open the TZQuery before assigning the new statement. Drop the line which speeds up you code..

[JD]

JD,
Btw. You don't need to open the TZQuery before assigning the new statement. Drop the line which speeds up you code..

Thanks for the tip. I had been wondering about the necessity of the 'Open' and 'Close' statements in the code snippet. I've since removed them and the code works perfectly as you rightly pointed out.