Re: ADOCommand and grave accent problem
From: David Fallas (fallasd_at_sysmexdelphic.com)
Date: 11/29/04
- Next message: Pavel: "Re: Migrating from MDB database to internet"
- Previous message: Del M: "Re: Migrating from MDB database to internet"
- In reply to: Paolo Mancini: "Re: ADOCommand and grave accent problem"
- Next in thread: David Fallas: "Re: ADOCommand and grave accent problem"
- Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]
Date: Tue, 30 Nov 2004 07:54:41 +1300
Hello All,
So that the resolution is posted here for anyone who may encounter the same
problem - Paolo has spotted the solution. Clearing the ParamCheck flag was
the key - however, it MUST be cleared BEFORE setting the CommandText
property. For example the following code snippet now works without the error
being reported.
try
ADOCommand.Connection:= ADOConnection;
// ..clear this flag BEFORE setting CommandText..!
ADOCommand.ParamCheck:= FALSE;
ADOCommand.CommandText:= sExecute;
ADOCommand.CommandType:= cmdText;
ADOCommand.ExecuteOptions:= [eoExecuteNoRecords];
// ..added the following line to test Paolo's suggestion...but this MUST
BE before setting CommandText..!
// ADOCommand.ParamCheck:= FALSE;
ADOCommand.Execute;// ..<--- exception used to be raised here...
...
How this solution might fare when there is a concurrent requirement to check
a parameterised command I do not know. I assume that using a grave accent
character in an INSERT or UPDATE at the same time as having ADO check a
parameterised command, cannot be done.
"Paolo Mancini" <paolo.mancini(at)email.it> wrote in message
news:41a6f538@newsgroups.borland.com...
> Ciao David,
> yes of course you can... I will look for an explanation as soon as I
> can...
> One question: which database platform are you using?? I have SQL Server
but
> not Oracle...
>
> Paolo Mancini
>
>
> "David Fallas" <fallasd@sysmexdelphic.com> ha scritto nel messaggio
> news:41a65f54$1@newsgroups.borland.com...
> > Hi again Paolo,
> >
> > Thankyou for taking the time to test my problem. I changed the date
format
> > in my code to your suggestion (ie yyyy-mm-dd) but I still get the ADO
> > error
> > when using an ADO Command, and not when using an ADO Connection object.
> >
> > I thought that perhaps it might be a difference in the ConnectionString
> > property that I use and what you may have used in your test, so I
changed
> > that. My original ConnectionString was
> > 'DSN=TestDSN;UID=DatabaseUser;PWD=DatabasePassword' which I changed it
to
> > 'Provider=MSDASQL.1;Data Source=TestDSN;Initial Catalog=TESTTABLES;User
> > ID=DatabaseUser;Password=DatabasePassword'. But that made no difference
> > and
> > the ADO error still occurs with an ADO Command, not with an ADO
> > Connection.
> >
> > Next I thought perhaps it was a difference in the database configuration
> > itself between your test and mine. My ODBC Data Source configuration
> > specifies TestDSN as using the SQLServer driver, version
2000.81.9042.00.
> >
> > Finally I tested with an Oracle database using Microsoft ODBC for
Oracle,
> > version 2.573.9030.00 and I get exactly the same ADO error.
> >
> > Perhaps with your permission, I could zip up my test project and email
it
> > to
> > you.
> >
> > "Paolo Mancini" <paolo.mancini@email.it> wrote in message
> > news:41a51c14@newsgroups.borland.com...
> >> Ciao David,
> >> I have made some test before to say that I can reproduce your error
in
> > no
> >> way... I've tried more than once but nothing... Instead I receved an
> >> error
> >> using your code but it refers to the date format used: I've used
> > 'yyyy-mm-dd
> >> hh:nn:ss' not 'yyyymmdd hh:nn:ss' maybe the problem is there...
> >>
> >> Hope It Helps,
> >> Paolo Mancini
> >> "David Fallas" <fallasd@sysmexdelphic.com> ha scritto nel messaggio
> >> news:41a4d2c5@newsgroups.borland.com...
> >> > Hi Paolo,
> >> >
> >> > Thanks for your suggestion - but no joy I am afraid. I modified the
> >> > code
> >> > as
> >> > follows (see below), but I still get the same error.
> >> >
> >> > Please note that the cause of the problem is the presence of the
grave
> >> > accent [`] character. One solution would be to avoid using this
> > character
> >> > altogether but unfortunately, this is not possible since it is
> >> > sometimes
> >> > present legitimately in the data our customers wish to store.
> >> >
> >> > ...
> >> > try
> >> > ADOCommand.Connection:= ADOConnection;
> >> > ADOCommand.CommandText:= sExecute;
> >> > ADOCommand.CommandType:= cmdText;
> >> > ADOCommand.ExecuteOptions:= [eoExecuteNoRecords];
> >> > // ..added the following line to test Paolo's suggestion...
> >> > ADOCommand.ParamCheck:= FALSE;
> >> > ADOCommand.Execute;// <----- exception raised here, (even with
> >> > ParamCheck set to FALSE)
> >> > ...
> >> >
> >> > I have noted on MSDN that this grave accent character [`] has a
> > 'special'
> >> > purpose to modify an ACCESS database password to null (two grave
> >> > accents
> >> > together). I wonder if this designed feature has some side-effects.
> >> > "Paolo Mancini" <paolo.mancini@email.it> wrote in message
> >> > news:41a472cb@newsgroups.borland.com...
> >> >> Ciao David,
> >> >> I suppose the error doesn't come out from the CommandText itself
> > rather
> >> >> than by TADOCommand component...
> >> >> Try to look at the properties like ParamCheck and keep in mind that
> >> > whenever
> >> >> you set the commandtext in a TADOCommand it parses the command
trying
> > to
> >> >> inidividuate any parameter included in ( even the return one ). The
> >> >> TADOConnection doesn't this work for you... That's all.
> >> >>
> >> >> Hope it helps.
> >> >> Paolo Mancini
> >> >>
> >> >> "David Fallas" <fallasd@sysmexdelphic.com> ha scritto nel messaggio
> >> >> 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)
> >> >> >
> >> >> >
> >> >>
> >> >>
> >> >
> >> >
> >>
> >>
> >
> >
>
>
- Next message: Pavel: "Re: Migrating from MDB database to internet"
- Previous message: Del M: "Re: Migrating from MDB database to internet"
- In reply to: Paolo Mancini: "Re: ADOCommand and grave accent problem"
- Next in thread: David Fallas: "Re: ADOCommand and grave accent problem"
- Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]
Relevant Pages
|
