Robert Becskei
asked on
Why is there a Difference between the SQL String and using the Parameter ?
I had this question after viewing Dynamically Created Query.
I must confess I do not understand why is it working now ...
I though it was the same thing when I've built the query by sql add ...
or was that the reason for the integer error ? it thought somehow that because I gave it a number
that the Gestellname is integer?
Thanky you very much!!!!
I must confess I do not understand why is it working now ...
I though it was the same thing when I've built the query by sql add ...
or was that the reason for the integer error ? it thought somehow that because I gave it a number
that the Gestellname is integer?
Thanky you very much!!!!
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you for the explanation . Gestellname is nvarchar(50) , Kuid is integer , and Gestell (which is stupid name... it is actually the e-mail it is again nvarchar(50)...
for some reason I thought if in delphi the variable for Gestellname , Kuid, Email was string , the query would handle it . But now that I think about it I guess I was totaly wrong ... at the time of writing and testing there were only numeric Racks ( Gestelle ) in the table ... so it worked...
I dont really know why I did what I did :) I wrote the query in MSSQL it worked, I somehow thought doing the same in delphi would work as well... but I totaly forgot about the N .
But matters not , I will stick to paremeters . Thank you :)
for some reason I thought if in delphi the variable for Gestellname , Kuid, Email was string , the query would handle it . But now that I think about it I guess I was totaly wrong ... at the time of writing and testing there were only numeric Racks ( Gestelle ) in the table ... so it worked...
I dont really know why I did what I did :) I wrote the query in MSSQL it worked, I somehow thought doing the same in delphi would work as well... but I totaly forgot about the N .
But matters not , I will stick to paremeters . Thank you :)
on another note ... if you ever need true performance ...
Ado uses Variants, that's the .Value of the parameter assignment.
this also slows thing down ... but it's in order of milliseconds
if you do millions of inserts ... it tends to get noticed
Ado uses Variants, that's the .Value of the parameter assignment.
this also slows thing down ... but it's in order of milliseconds
if you do millions of inserts ... it tends to get noticed
Geert - I respect different opinions. I write "real life" sql-s and don't use sql-in-code like you wrote....My reasons why I use something goes out of scope.....
You made wrong example to show "performance" problem. Try this one:
With params:
...and you see what is a real difference... When you do batch insert - do it another way - not like this.
Your example show slowness assigning sql text to qry.sql.Text - and not preparing sql against setting parameters.
I just want to make point that I use single unit for all sql-s (in code) - not in thousands of query's on many, many forms. Very hard to locate and fix .... And when want to support different databases - just use another (compatible) unit.
You made wrong example to show "performance" problem. Try this one:
With params:
tt := GetTickCount;
for I := 1 to 20000 do
begin
qry.sql.Text := '';
qry.sql.Text := 'insert into Ambalaza (IDDok, IDArtikla) values (:column1, :column2)';
qry.Prepared := True;
qry.Parameters.ParamByName('column1').Value := Trunc(Random(1000000));
qry.Parameters.ParamByName('column2').Value := Trunc(Random(1000000));
//qry.ExecSQL;
end;
AddMsg('End with params ' + IntToStr(GetTickCount - tt) );
Without params:tt := GetTickCount;
for I := 1 to 2000 do
begin
qry.sql.Text := '';
qry.sql.Text := 'insert into Ambalaza (IDDok, IDArtikla) values ('+IntToStr(Trunc(Random(1000000)))+', '+IntToStr(Trunc(Random(1000000)))+')';
qry.Prepared := True;
//qry.ExecSQL;
end;
AddMsg('End without params ' + IntToStr(GetTickCount - tt) );
...and you see what is a real difference... When you do batch insert - do it another way - not like this.
Your example show slowness assigning sql text to qry.sql.Text - and not preparing sql against setting parameters.
I just want to make point that I use single unit for all sql-s (in code) - not in thousands of query's on many, many forms. Very hard to locate and fix .... And when want to support different databases - just use another (compatible) unit.
lol, i guess you don't have a clue about the backend
maybe that's why Tom Kyte just retired ?
because he gave up on repeating himself having to tell developers to use bind variables
if you comment out the qry.execsql nothing much will happen ...
maybe that's why Tom Kyte just retired ?
because he gave up on repeating himself having to tell developers to use bind variables
if you comment out the qry.execsql nothing much will happen ...
When you have string (nvarchar(50)) field - you must than prepare sql with quoted string.
Open in new window
.. so when message pops up - you string should have quoted string in it and than it is correct...
Without quotes - you force field to be converted to automatically detected type of a parameter you gave...
Sometimes might work - but it is not correct.