Problems with sql queries in delphi 6

Dev Shed Forums Sponsor:

January 28th, 2004, 04:16 AM

petermol

Registered User

Join Date: Jan 2004

Posts: 3

Time spent in forums: < 1 sec
Reputation Power: 0

Problems with sql queries in delphi 6

Hi, I use queries to manipulate data in a access table and normally all goes well but..

When the querie contains characters like this > ` ' ( ) ' :' and some other ones

I get all kinds of error messages and when i remove those characters all goes well.

But I have to insert this data as it is in a database, does anyone knows how to do this ?

This is a example of a querie is use :

query3.Active := false ;
query3.SQL.Text := 'insert into sneeuwhoogten (code,plaats,sneeuwnieuws) values('+#39 + code + #39 + ',' + #39 + plaats + #39 + ','+#39 + snownews + #39 + ')' ;
Query3.execsql ;

where code,plaats and snownews are strings.

Thanks in advance

January 28th, 2004, 12:07 PM

Scorpions4ever

Banned ;)

Dev Shed God 9th Plane (9000 - 9499 posts)

Join Date: Nov 2001

Location: Woodland Hills, Los Angeles County, California, USA

Posts: 9,389

Time spent in forums: 1 Month 4 Weeks 1 Day 22 h 1 m 11 sec
Reputation Power: 4080

This is why you should always parametrize all queries. Assuming that Query3 is a TADOQuery object:

Code:

query3.Active := false;
query3.SQL.Text := 'insert into sneeuwhoogten (code,plaats,sneeuwnieuws) values (:code, :plaats, :sneewnews)' ;
query3.Parameters.ParamByName('code').Value := code;
query3.Parameters.ParamByName('plaats').Value := plaats;
query3.Parameters.ParamByName('sneewnews').Value :=  snownews;
query3.ExecSQL;

If you're using a BDE object (i.e.) TQuery instead of TADOQuery, change the part where you set the params to something like this:

Code:

query3.ParamByName('code').AsString := code;
query3.ParamByName('plaats').AsInteger := plaats;
// change AsInteger, AsString, AsDateTime as needed..

The advantage of parametrizing is that
(1) The engine takes care of escaping characters correctly for you, so you don't have to escape any characters yourself.
(2) If you need to execute the statement again with different values of code, plaats and snownews, you don't need to prepare the SQL string again. All you need to do is set the parameter values again and execute:

Code:

with Query3 do
begin
    Close;
    ParamByName('code').AsString := code;
    ... Set rest of params here ...
    ExecSQL;
end;

__________________
Up the Irons
What Would Jimi Do? Smash amps. Burn guitar. Take the groupies home.
"Death Before Dishonour, my Friends!!" - Bruce D ickinson, Iron Maiden Aug 20, 2005 @ OzzFest
Down with Sharon Osbourne

Last edited by Scorpions4ever : January 28th, 2004 at 12:09 PM.

January 28th, 2004, 04:55 PM

petermol

Registered User

Join Date: Jan 2004

Posts: 3

Time spent in forums: < 1 sec
Reputation Power: 0

Thats it !!!!

It works great, thank you very much I never knew that it could be done this way.
What i did now was that I filtered the string and replace or deleted the character that was causing the problem, but your solution is much much better.
Thanks again !!! :-)
(Excuse my bad englisch)

Problems with sql queries in delphi 6

Developer Shed Advertisers and Affiliates