Page 1 of 2
Problem when updating the same record twice - FIXED!
Posted: 15.09.2009, 20:03
by Prometeus
Hello,
I have a situation where, sometime, I need change the data of two register of same type, the second just after the first. The problem arises on the second update as it looks like Zeos is replicating the value of ALL field when they are NULL. The code is like below:
Code: Select all
with SQLTest do
begin
Close;
SQL.Clear;
SQL.Add('UPDATE test_table SET field1 = :field1, field2 = :field2, field3 = :field3,');
SQL.Add('date_test = :date_test, state_test = :state_test');
SQL.Add('WHERE (cod_testtable = :cod_testtable)');
if TwoUpdatesCheckBox.Checked then
begin
ParamByName('FIELD1').AsInteger := 44;
ParamByName('FIELD2').Clear;
ParamByName('FIELD3').AsInteger := 45;
ParamByName('DATE_TEST').AsDateTime := Now;
ParamByName('STATE_TEST').AsString := 'D';
ParamByName('COD_TESTTABLE').AsInteger := 2;
ExecSQL;
end;
ParamByName('FIELD1').AsInteger := StrToInt(Field1.Text);
ParamByName('FIELD2').AsInteger := StrToInt(Field2.Text);
ParamByName('FIELD3').AsInteger := StrToInt(Field3.Text);
ParamByName('DATE_TEST').AsDateTime := Now;
ParamByName('STATE_TEST').AsString := 'A';
ParamByName('COD_TESTTABLE').AsInteger := 3;
ExecSQL;
end;
In the above code, no matter what you put in 'Field2' for the second update it will get the NULL value of the first one. Why is this happening? If needed I can put the above sample as a project and the script for creating the test database.
I am using TESTING_REV679 version.
Thank you.
Posted: 22.09.2009, 13:53
by Prometeus
Hello,
This bug still persists on TESTING_REV692.
Posted: 23.09.2009, 13:57
by Prometeus
Hello,
Could someone confirm whether this is a bug or not? A reported this to ZEOS bug tracker but no answer as well.
Thank you.
Posted: 23.09.2009, 14:53
by mdaems
Prometeus,
I saw you filed this as a bug report as well. Unfortunately I seem to be the only one looking at bug reports and all other issues, so I didn't try your test case yet.
However, as far as I can understand your example code, this looks like a genuine bug.
The only way to get this problem solved quicker is to debug and submit a patch using the bug tracker, I'm afraid.
Mark
Posted: 23.09.2009, 20:31
by Prometeus
Hello, Mark!
Ok, I'll try do that.
Posted: 29.10.2009, 13:31
by Prometeus
Hello, Mark,
I tried follow the bug (that persists in TESTING_REV713) but to no avail. Can you or anyone else give me a clue to where should I look for it?
It's unbelievible that this bug did not happen to other user.
Thanks for any help.
BTW: I am using Delphi 7. A saw that Zeos uses ancestors from Delphi VCL. May the bug be on VCL? Can someone using newer Delphi version test the case that I put on SVN bugtracker? Thanks.
Posted: 29.10.2009, 16:22
by cariad
Confirmed in Delphi 2010.
Posted: 30.10.2009, 13:39
by Prometeus
Thanks, Cariad!
So the bug seems to be in Zeos indeed.
Posted: 08.11.2009, 12:08
by seawolf
unit ZDbcInterbase6Utils;
procedure TZParamsSQLDA.UpdateInt(const Index: Integer; Value: Integer);
var
SQLCode: SmallInt;
begin
CheckRange(Index);
SetFieldType(Index, sizeof(Integer), SQL_LONG + 1, 0);
{$R-}
with FXSQLDA.sqlvar[Index] do
begin
if (sqlind <> nil) and (sqlind^ = -1) then
Exit;
SQLCode := (sqltype and not(1));
if (sqlscale < 0) then
begin
case SQLCode of
SQL_SHORT : PSmallInt(sqldata)^ := Value * IBScaleDivisor[sqlscale];
SQL_LONG : PInteger(sqldata)^ := Value * IBScaleDivisor[sqlscale];
SQL_INT64,
SQL_QUAD : PInt64(sqldata)^ := Value * IBScaleDivisor[sqlscale];
SQL_DOUBLE : PDouble(sqldata)^ := Value;
else
raise EZIBConvertError.Create(SUnsupportedDataType);
end;
end
else
case SQLCode of
SQL_DOUBLE : PDouble(sqldata)^ := Value;
SQL_LONG : PInteger(sqldata)^ := Value;
SQL_D_FLOAT,
SQL_FLOAT : PSingle(sqldata)^ := Value;
SQL_BOOLEAN : PSmallint(sqldata)^ := Value;
SQL_SHORT : PSmallint(sqldata)^ := Value;
SQL_INT64 : PInt64(sqldata)^ := Value;
SQL_TEXT : EncodeString(SQL_TEXT, Index, AnsiString(IntToStr(Value)));
SQL_VARYING : EncodeString(SQL_VARYING, Index, AnsiString(IntToStr(Value)));
else
raise EZIBConvertError.Create(SUnsupportedDataType);
end;
end;
if (sqlind <> nil) then
sqlind^ := 0; // not null
{$IFOPT D+}
{$R+}
{$ENDIF}
end;
Second time sqlind^ = -1 and must be set to 0 in order to write the field
to the db
Posted: 12.11.2009, 00:06
by mdaems
seawolf,
Did you compile this? I get "Error: Undeclared identifier: 'sqlind'"
Mark
Posted: 13.11.2009, 14:47
by Prometeus
Seawolf and Mdaems,
The "Error: Undeclared identificer: 'sqlind'" may be bypassed if you reference it before like this:
...
with FXSQLDA.sqlvar[Index] do
if (sqlind <> nil) then
sqlind^ := 0; // not null
...
However the bug still persists on my test case. Watching the code for updating of other types on that unit it looks like the bug exists in all of them, as the code is virtually the same for all of them. The fix, when found, must be applied to all data types, isn't?
Lets keep trying throw this bug away...
Posted: 23.11.2009, 14:43
by Prometeus
Hello,
The bug still persists on TESTING_REV725. This is a serious bug when you have a list of values to be UPDATEd on any DB table and some of the fields must be NULL for some reason in the loop. The next iteration of that field will be NULL as well what may raise a 'ghost' effect on that table. I realized I have an application in such a situation and I couldn't figure out where was the bug. I tought it could be on Firebird but after Seawolf post I realized the problem was the same of other application I have whose situation is like the test case I created on bug tracker.
I am trying to fix this bug but to no avail until now. I guess there are other people with this bug in their applications and, probably, they do not know where else to look for because of this 'ghost' effect the bug introduces on the application.
Well, back to try finding out a solution.
Posted: 23.11.2009, 15:16
by seawolf
Sorry for the long delay .. after your post I check one more time and, to be honest, problem is not located in that function. I made some changes in a different function and I'm really close to resolve it. Unfortunately past week I was very busy. Now I have some free time to fix definetely that problem and post the solution.
Posted: 23.11.2009, 19:23
by Prometeus
Hello Seawolf,
Oh, good news! Glad to know you are close to fix it!! Can you confirm whether this bug affects other data type other then Integer?
Thank you!
Posted: 23.11.2009, 19:56
by seawolf
Problem is located in dbc\ZDbcInterbase6Statement.pas
If the first time a param is set null, after cannot be set as not null
This change cannot affect any data type.
Please test it (it works to me)
procedure TZInterbase6PreparedStatement.BindInParameters;
var
I: Integer;
TempBlob: IZBlob;
TempStream: TStream;
begin
if InParamCount <> FParamSQLData.GetFieldCount then
raise EZSQLException.Create(SInvalidInputParameterCount);
{$R-}
for I := 0 to FParamSQLData.GetFieldCount - 1 do
begin
FParamSQLData.UpdateNull(I,
DefVarManager.IsNull(InParamValues));
if DefVarManager.IsNull(InParamValues)then
Continue
else
case InParamTypes of
stBoolean:
FParamSQLData.UpdateBoolean(I,
SoftVarManager.GetAsBoolean(InParamValues));
stByte:
FParamSQLData.UpdateByte(I,
SoftVarManager.GetAsInteger(InParamValues));
stShort:
FParamSQLData.UpdateShort(I,
SoftVarManager.GetAsInteger(InParamValues));
stInteger:
FParamSQLData.UpdateInt(I,
SoftVarManager.GetAsInteger(InParamValues));
stLong:
FParamSQLData.UpdateLong(I,
SoftVarManager.GetAsInteger(InParamValues));
stFloat:
FParamSQLData.UpdateFloat(I,
SoftVarManager.GetAsFloat(InParamValues));
stDouble:
FParamSQLData.UpdateDouble(I,
SoftVarManager.GetAsFloat(InParamValues));
stBigDecimal:
FParamSQLData.UpdateBigDecimal(I,
SoftVarManager.GetAsFloat(InParamValues[I]));
stString:
FParamSQLData.UpdateString(I,
SoftVarManager.GetAsString(InParamValues[I]));
stUnicodeString:
FParamSQLData.UpdateString(I,
SoftVarManager.GetAsUnicodeString(InParamValues[I]));
stBytes:
FParamSQLData.UpdateBytes(I,
StrToBytes(SoftVarManager.GetAsString(InParamValues[I])));
stDate:
FParamSQLData.UpdateDate(I,
SoftVarManager.GetAsDateTime(InParamValues[I]));
stTime:
FParamSQLData.UpdateTime(I,
SoftVarManager.GetAsDateTime(InParamValues[I]));
stTimestamp:
FParamSQLData.UpdateTimestamp(I,
SoftVarManager.GetAsDateTime(InParamValues[I]));
stAsciiStream,
stUnicodeStream,
stBinaryStream:
begin
TempBlob := DefVarManager.GetAsInterface(InParamValues[I]) as IZBlob;
if not TempBlob.IsEmpty then
begin
TempStream := TempBlob.GetStream;
try
FParamSQLData.WriteBlob(I, TempStream);
finally
TempStream.Free;
end;
end;
end;
else
raise EZIBConvertError.Create(SUnsupportedParameterType);
end;
end;
{$IFOPT D+}
{$R+}
{$ENDIF}
inherited BindInParameters;
end;