Re: ADOCommand and grave accent problem

From: David Fallas (fallasd_at_sysmexdelphic.com)
Date: 11/29/04 

Date: Tue, 30 Nov 2004 10:54:03 +1300

In the end, I was not entirely happy with simply clearing the ParamCheck
flag. I found that I need to have the ParamCheck set to TRUE when I am using
parameterised SQL - which I do for any 'long' fields - BLOBS, TEXT,
LONGVARCHAR etc. I therefore investigated this further and found that the
REAL problem is in the SQL, and the way that Borland have written
ParseSQL( ) in ADODB.pas. This method treats a grave accent character [`]
the same as if it were an apostrophe ['] or a quotation mark ["]. Once I had
found this I realised that instead of clearing the ParamCheck flag - I
simply needed to add an extra grave accent character (ie whenever one grave
accent is found, a second one must be added), which then enabled ParseSQL( )
to parse for parameters correctly.

Philosophically, I believe that placing a grave accent character in the same
category as an apostrophe and a quotation mark is not correct. It is not
documented as having the same purpose anywhere that I can find (whereas the
'accepted' uses of apostrophes and quotation marks are extensively
documented in many different reference manuals - SQL, Delphi, VB etc, etc).

"David Fallas" <fallasd@sysmexdelphic.com> wrote in message
news:41a3efb7@newsgroups.borland.com...
> Win2k, MDAC 2.7, Delphi 6, Update 2, SQLServer 8.0.
>
> The following causes an ADO exception (EADOError): 'Parameter object is
> improperly defined. Inconsistent or incomplete information was provided'
>
> ...
> sCols:= 'testchar,testnumber,testdate';
> sVals:= QuotedStr('grave accent [`]') + ',10,' +
> QuotedStr(FormatDateTime('yyyymmdd hh:nn:ss', Now));
> sExecute:= 'INSERT INTO testtable(' + sCols + ')VALUES(' + sVals + ')';
> ADOConnection:= TADOConnection.Create(nil);
> ADOCommand:= TADOCommand.Create(nil);
> try
> ADOConnection.ConnectionString:= _ConnectionString;
> ADOConnection.Open;
> try
> ADOCommand.Connection:= ADOConnection;
> ADOCommand.CommandText:= sExecute;
> ADOCommand.CommandType:= cmdText;
> ADOCommand.ExecuteOptions:= [eoExecuteNoRecords];
> ADOCommand.Execute;// <----- exception raised here
> ...
>
> The following does not:
> ...
> sCols:= 'testchar,testnumber,testdate';
> sVals:= QuotedStr('grave accent [`]') + ',10,' +
> QuotedStr(FormatDateTime('yyyymmdd hh:nn:ss', Now));
> sExecute:= 'INSERT INTO testtable(' + sCols + ')VALUES(' + sVals + ')';
> ADOConnection:= TADOConnection.Create(nil);
> try
> ADOConnection.ConnectionString:= _ConnectionString;
> ADOConnection.Open;
> try
> ADOConnection.Execute(sExecute, cmdText);// <----- NO exception here
> ...
>
> What is it about the grave accent character in ADOCommand?
>
> I raised this issue a couple of years ago in these news groups, but I
don't
> recall any responses. It's still there, and still causing headaches. Any
> ideas welcome.
>
> BTW - table definition is as follows:
>
> CREATE TABLE testtable(
> testchar VARCHAR(40),
> testnumber NUMERIC,
> testdate DATETIME)
>
>