[solved] Buggy ZUpdateSQL component
Moderators: gto, EgonHugeist, olehs
[solved] Buggy ZUpdateSQL component
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.
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.
You do not have the required permissions to view the files attached to this post.
Last edited by JD on 19.06.2012, 20:28, edited 1 time in total.
- EgonHugeist
- Zeos Project Manager
- Posts: 1936
- Joined: 31.03.2011, 22:38
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
"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
Best regards, Michael
You want to help? http://zeoslib.sourceforge.net/viewtopic.php?f=4&t=3671
You found a (possible) bug? Use the new bugtracker dude! http://sourceforge.net/p/zeoslib/tickets/
You want to help? http://zeoslib.sourceforge.net/viewtopic.php?f=4&t=3671
You found a (possible) bug? Use the new bugtracker dude! http://sourceforge.net/p/zeoslib/tickets/
I've noticed that the problem arises when I try to INSERT
The SQL generated by the component is as shown below:
I then modify the generated SQL removing the automatically generated primary key and some other non-essential fields
The code that fills the parameters sent remotely by the client is as follows:
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.
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)
Code: Select all
INSERT INTO foyers
(NOM, ADRESSE, VILLE, CODE_POSTAL, TELEPHONE, CONTACT)
VALUES
(:NOM, :ADRESSE, :VILLE, :CODE_POSTAL, :TELEPHONE, :CONTACT)
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;
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
- Zeos Project Manager
- Posts: 1936
- Joined: 31.03.2011, 22:38
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
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
Best regards, Michael
You want to help? http://zeoslib.sourceforge.net/viewtopic.php?f=4&t=3671
You found a (possible) bug? Use the new bugtracker dude! http://sourceforge.net/p/zeoslib/tickets/
You want to help? http://zeoslib.sourceforge.net/viewtopic.php?f=4&t=3671
You found a (possible) bug? Use the new bugtracker dude! http://sourceforge.net/p/zeoslib/tickets/
- EgonHugeist
- Zeos Project Manager
- Posts: 1936
- Joined: 31.03.2011, 22:38
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
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
Best regards, Michael
You want to help? http://zeoslib.sourceforge.net/viewtopic.php?f=4&t=3671
You found a (possible) bug? Use the new bugtracker dude! http://sourceforge.net/p/zeoslib/tickets/
You want to help? http://zeoslib.sourceforge.net/viewtopic.php?f=4&t=3671
You found a (possible) bug? Use the new bugtracker dude! http://sourceforge.net/p/zeoslib/tickets/
I've incorporated the changes you suggested and the changed code is as shown below:
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?
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;
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?
You do not have the required permissions to view the files attached to this post.
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.
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
- Zeos Project Manager
- Posts: 1936
- Joined: 31.03.2011, 22:38
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..
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..
Best regards, Michael
You want to help? http://zeoslib.sourceforge.net/viewtopic.php?f=4&t=3671
You found a (possible) bug? Use the new bugtracker dude! http://sourceforge.net/p/zeoslib/tickets/
You want to help? http://zeoslib.sourceforge.net/viewtopic.php?f=4&t=3671
You found a (possible) bug? Use the new bugtracker dude! http://sourceforge.net/p/zeoslib/tickets/
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.EgonHugeist wrote:JD,
Btw. You don't need to open the TZQuery before assigning the new statement. Drop the line which speeds up you code..