parameters.refresh bug

It seems MS have not fixed the parameters.refresh bug yet.If I use this
code, it would throw out an exception of 'AV in module SQLOLEDB.dll'. By the
way, my SQL is 2000 + SP4 under Win2000 +SP4.
Jack ZhongJack Zhong (invalid@.email.com) writes:
> It seems MS have not fixed the parameters.refresh bug yet.If I use this
> code, it would throw out an exception of 'AV in module SQLOLEDB.dll'. By
> the way, my SQL is 2000 + SP4 under Win2000 +SP4.
Which code?
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||>
> Which code?
>
(in Delphi 7)
sp.Parameters.Refresh;
.....
sp.ExecProc;
sp is a stored procedure component wrapped in datamodule. There would be an
AV exception if fire sp.Parameters.Refresh; If this line is disabled, it
works perfectly well. I knew there was a Parameters.Refresh bug in previous
SQLOLEDB.dll before, but my SQL might be the latest, it is SP4 patched.
The following is snippet of my codes in Delphi 7.
function TBiz.TransData(FGuest: TGuest; FIndex: Byte): Boolean;
begin
begin
with dm.prTransGuest do
begin
try
Parameters.Refresh;
Parameters.ParamByName('@.ref').Value := Ref ;
..
Parameters.ParamByName('@.position').Value := FIndex;
Prepared:=True ;
ExecProc;
Result := True;
except
Result := False;
end;
end;
end;
end;
Jack Zhong|||Jack Zhong (invalid@.email.com) writes:
> (in Delphi 7)
> sp.Parameters.Refresh;
> .....
> sp.ExecProc;
> sp is a stored procedure component wrapped in datamodule. There would be
> an AV exception if fire sp.Parameters.Refresh; If this line is
> disabled, it works perfectly well. I knew there was a Parameters.Refresh
> bug in previous SQLOLEDB.dll before, but my SQL might be the latest, it
> is SP4 patched.
I don't know Delphi or have any access to it, so I cannot repro. But I
know we use the .Refresh method in our code (ADO + VB6), and I have not
heard about any access violation.
Besides, the .Refresh method is not in SQLOLEDB proper, but is part of
ADO. True, though, that ADO uses SQLOLEDB (or whichever provider you
use).
Is Delphi 7 a recent version of Delphi?
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Merry Christmas, Erland.
I am a betrayer to VB, ^_*. Yes, there are a great many AVs in Delphi than
in VB, VB is much friendly to programmers sometimes.
Actually, the AV is somewhat strange. There are two function routines of
similar structure in the same loop to insert data into different tables, the
other function would be OK even though there is also a Parameters.Refresh.
The only diffrence is the underlying tables are slightly different. One has
primary key and the other one contains no primary keys. Inserting data into
the latter table, *without primary key*, might throw out AV if execute
Parameters.Refresh before insertion. I am not sure whether this might be the
point in which the problem lies. However, if I put a TADOStoredProcedure
component on the form and set the values in the properties inspector, then
no matter how many times I fire it, there is no AV at all.
By the way, for there are some differences between Pascal data types and SQL
server, I modified some parameters data types, for instance from
ftWideString to ftString, I got an AV in module mo15.dll if I enable
Parameters.Refresh.
Delphi 7 is an Adult in the Delphi family, Delphi 2006 is the latest one. D7
might be the latest one for Win32 enviroment.
Following is the snippet of my codes, I have added some comments to it. BTW,
the TBiz is a class to implement business rules.
procedure TBiz.ButtonApplyClick(FMbr: tstrings; FContact: TContact);
//class routine respond to ButtonApply click event in host application
var
i : Integer;
begin
if Assigned(FMbr) then
begin
for i := 0 to FMbr.Count - 1 do //repeatedly insert data into tables
with two stored procedure
begin
if TGuest(FMbr.Objects[i]).Rounds <> 0 then
begin
if ( AddNew( TGuest(FMbr.Objects[i]) ) <> 0) then
TransData(TGuest(FMbr.Objects[i]), i + 1);
end; //end if
end; //end for
case Operation of
0: New; //Operation create new.
1: Append; //Operation append
end;
end;
end;
function TBiz.TransData(FGuest: TGuest; FIndex: Byte): Boolean;
begin
begin
with dm.prTransGuest do //prTransGuest is a stored procedure in dm
(TDataMudle);
begin
try
Parameters.Refresh;
Parameters.ParamByName('@.ref').Value := Ref ;
..
Parameters.ParamByName('@.position').Value := FIndex;
Prepared;
ExecProc; //throw out AV exception on the second try, if
Parameters.Refresh enabled
Result := True;
except
Result := False;
end;
end;
end;
end;
function TBiz.AddNew(FGuest: TGuest): LongWord;
begin
with dm.prAddNew do
begin
with Parameters do
begin
try
try
Parameters.Refresh;
ParamByName('@.FirstName').Value := FGuest.FirstName;
..
Prepared;
ExecProc; //No AV even enable the above Parameters.Refresh
Result := ParamByName('@.Return_Value').Value;
except
result := 0;
end;
finally
end;
end;
end;
end;|||Jack Zhong (invalid@.email.com) writes:
> Actually, the AV is somewhat strange. There are two function routines
> of similar structure in the same loop to insert data into different
> tables, the other function would be OK even though there is also a
> Parameters.Refresh. The only diffrence is the underlying tables are
> slightly different. One has primary key and the other one contains no
> primary keys. Inserting data into the latter table, *without primary
> key*, might throw out AV if execute Parameters.Refresh before
> insertion. I am not sure whether this might be the point in which the
> problem lies. However, if I put a TADOStoredProcedure component on the
> form and set the values in the properties inspector, then no matter how
> many times I fire it, there is no AV at all.
There is one thing that I find strange in your code. You are refreshing
the parameters for each time in the loop, but you never set up any
command object. Or this is implicit in some way.
Anyway, calling .Refresh for every call to the procedure is hardly good
for performance, since that is a roundtrip to the server. Ideally, you
could call .Refresh once, and the reuse the parameter collection. Un-
fortunately, when I have tried to reuse command objects in ADO, it has
started doing things I don't like at all.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||"Erland Sommarskog" <esquel@.sommarskog.se> '
news:Xns9735F04F06514Yazorman@.127.0.0.1...
> There is one thing that I find strange in your code. You are refreshing
> the parameters for each time in the loop, but you never set up any
> command object. Or this is implicit in some way.
>
This might be the difference between Delphi and VB, I am afraid. Delphi
could add parameter list in the property inspector, so it is not needed to
add parameters.refresh in the routines again. But If there were some
parameters added to the procedure and the programmer forgot to add them to
the inspector box, Delphi would throw out 'parameter not found' error.

> Anyway, calling .Refresh for every call to the procedure is hardly good
> for performance, since that is a roundtrip to the server. Ideally, you
> could call .Refresh once, and the reuse the parameter collection. Un-
> fortunately, when I have tried to reuse command objects in ADO, it has
> started doing things I don't like at all.
>
I would move Refresh command to the form.create routine, this might avoid
refreshing SP for many times.
Thanks.

> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx