ADOQuery Parameters Error
Osmar
When setting the SQL text dinamically and THERE ARE PARAMETERS in the text,
I'm getting the following error:
'THE APPLICATION IS USING ARGUMENTS THAT ARE OF THE WRONG TYPE, ARE OUT OF
ACCEPTABLE RANGE, OR ARE IN CONFLICT WITH ONE ANOTHER'.
The table was created with this command in the SQL Server:
CREATE TABLE test_table (CCod char(8) null)
The code that raises the error is like this:
AdoQuery1.SQL.Text:= 'SELECT * FROM TEST_TABLE WHERE CCod = :Cod';
The error is generated in the unit ADOBD, in the function
TParameters.InternalRefresh, RefreshFromOleDB.
I reproduced the error using the application AdoTest, that comes with delphi
5 (Demos\Ado\AdoTest.dpr).
It seems to be a bug.
We are trying to convert a BDE app to use ADO and this kind of construct is
used a lot. If we can not use it we won't be able to do the job.
Anyone can help me. Thanks.
Alessandro Federici
the FetchOptions...
It is some parameter that you misset. Try changing them... ADO lacks in
error reporting (errors are often difficult to understand)
I use ADO with SQL server also to execute statements like yours and I never
had any problem.
Osmar <os...@metrics.com.br> wrote in message
news:82138t$ra...@forums.borland.com...
Mark Edington (Borland)
news:82138t$ra...@forums.borland.com...
> The error is generated in the unit ADOBD, in the function
> TParameters.InternalRefresh, RefreshFromOleDB.
Try using a TADODataset and set the CommandType to cmdUnknown. This will bypass
the routine which is causing the error. Please let me know if that solves the
problem.
Mark
Osmar
I've tryed using TADODataset and it didn't worked.
If I set "ADODataset1.CommandType := cmdUnknown;" the function
InitParameters in TADOCommand.AssignCommandText is bypassed, so I dont't get
the reported error, but it generates 2 new problems:
1. The parameters are not created
2. The command text is not parsed, so the command sent to SQL Server
contains ":", which is not valid.
If I set "ADODataset1.CommandType := cmdText;" then the original problem
returns.
Thats what I tryed:
ADODataset1.CommandType := cmdUnknown;
ADODataset1.CommandText:= 'Select * from Test_Table where CCod = :Cod';
ADODataset1.Parameters.ParamByName('Cod').Value:= ' ';
Thank you for your help.
Osmar
Mark Edington (Borland) <medi...@nolunchmeat.com> wrote in message
mensagem:821toh$43...@forums.borland.com...
Mark Edington (Borland)
> If I set "ADODataset1.CommandType := cmdUnknown;" the function
> InitParameters in TADOCommand.AssignCommandText is bypassed, so I dont't get
> the reported error, but it generates 2 new problems:
Hmmm. Ok, I'll need to look into this. I have a fair number of other things to
investigate so it may be a week or more before I can get back to you.
Mark
Arnold Johnson
Using your example
1) var sSQL : String
sSQL := 'Select * from Test_Table where CCod = :Cod';
ADODataset1.CommandType := cmdUnknown;
ADODataset1.CommandText:= sSQL;
ADODataSet1.Parameters.ParseSQL(sSQL, True);
ADODataset1.Parameters.ParamByName('Cod').Value:= ' ';
ADODataSet1.Open;
2) var sCod, sSQL : String
note for clarity [ should be double quotes (")
i.e. replace [ with "
sSQL := 'Select * from Test_Table where CCod = ['+sCod+'] ';
ADODataset1.CommandType := cmdUnknown;
ADODataset1.CommandText:= sSQL;
ADODataSet1.Open;
HTH
In article <8235lo$dd...@forums.borland.com>, Osmar
<os...@metrics.com.br> writes
>Mark,
>
>I've tryed using TADODataset and it didn't worked.
>
>If I set "ADODataset1.CommandType := cmdUnknown;" the function
>InitParameters in TADOCommand.AssignCommandText is bypassed, so I dont't get
>the reported error, but it generates 2 new problems:
>
>1. The parameters are not created
>2. The command text is not parsed, so the command sent to SQL Server
>contains ":", which is not valid.
>
>If I set "ADODataset1.CommandType := cmdText;" then the original problem
>returns.
>
>Thats what I tryed:
>
> ADODataset1.CommandType := cmdUnknown;
> ADODataset1.CommandText:= 'Select * from Test_Table where CCod = :Cod';
> ADODataset1.Parameters.ParamByName('Cod').Value:= ' ';
>
>Thank you for your help.
>
>Osmar
>
>
>
>Mark Edington (Borland) <medi...@nolunchmeat.com> wrote in message
>mensagem:821toh$43...@forums.borland.com...
>> Osmar <os...@metrics.com.br> wrote in message
>> news:82138t$ra...@forums.borland.com...
>>
>> > The error is generated in the unit ADOBD, in the function
>> > TParameters.InternalRefresh, RefreshFromOleDB.
>>
>> Try using a TADODataset and set the CommandType to cmdUnknown. This will
>bypass
>> the routine which is causing the error. Please let me know if that solves
>the
>> problem.
>>
>> Mark
>>
>>
>
>
--
Arnold Johnson
Osmar
ADODataset1.CommandType := cmdUnknown;
ADODataset1.CommandText:= 'Select * from Test_Table where CCod = ?';
ADODataSet1.Parameters.ParseSQL(':Cod', True);
ADODataset1.Parameters.ParamByName('Cod').Value:= 'ABC';
ADODataset1.Open;
Note that:
1. I could not use the ":ParmName" inside the SQL text (because the SQL was
not parsed before been sent to the server. I used "?" instead.
2. The parameter creation was forced using
ADODataSet1.Parameters.ParseSQL(':Cod', True);
It worked.
I think it's something that must be fixed because this is a very common way
of using a TQuery object (I have hundreds of this kind of construction).
Thanks.
Arnold Johnson <Arn...@win-pro.demon.co.uk> escreveu nas notícias de
mensagem:MCKYwDAd...@win-pro.demon.co.uk...
Osmar
I've made some changes to the ADOBD unit and the problem if fixed.
I don't know the internals of ADO so, I'm not sure if what I did is ok.
That's what I changed:
1. procedure RefreshFromOleDB;
before create the new parameter, test if it is already created. If yes, use
it:
if (I < (Command.CommandObject.Parameters.Count-1)) then
Parameter := Command.CommandObject.CreateParameter(Name,
wType, dwFlags and $F, ulParamSize, EmptyParam)
else
Parameter:= Command.CommandObject.Parameters.Item[I];
2. procedure InitParameters;
do not change the Direction of the parameter because it was set ok when the
SQL was parsed.
// List[I].Direction := Parameters[I].Direction; {osmar}
Please, make me know if its Ok.
Thank you,
Osmar
Mark Edington (Borland) <medi...@nolunchmeat.com> escreveu nas notícias de
mensagem:823pu1$jn...@forums.borland.com...
> Osmar <os...@metrics.com.br> wrote in message
> news:8235lo$dd...@forums.borland.com...
>
> > If I set "ADODataset1.CommandType := cmdUnknown;" the function
> > InitParameters in TADOCommand.AssignCommandText is bypassed, so I dont't
get
> > the reported error, but it generates 2 new problems:
>
Mark Edington (Borland)
> if (I < (Command.CommandObject.Parameters.Count-1)) then
> Parameter := Command.CommandObject.CreateParameter(Name,
> wType, dwFlags and $F, ulParamSize, EmptyParam)
> else
> Parameter:= Command.CommandObject.Parameters.Item[I];
That condition should always evaluate to false. Before RefreshFromOleDB is called
there is a call to clear which should delete all of the existing parameter
objects. So I guess the real question becomes how come the existing parameters
are not getting cleared for you? Since you are already debugging the code can you
trace into the call to clear (right before the call to RefreshFromOleDB and see if
you can tell what is going wrong?
I tried creating the table you are using with your create statement and tested it
with the ADOTest demo but I don't get the error you do. Can you tell me if you
changed any of the settings in the demo from the default (such as CursorLocation,
etc.) and also the exact steps you use to cause it to fail?
Mark
Osmar
I have installed the ADODB again an aplyed the unofficial patch.
I've reseted the initial configuration of AdoTest application and I'm still
getting the same error.
I've traced to the point where it seems to be happening.
The problem is that parameters inside
ADOCommand.CommandObject.Parameters are beeing created automatically by ADO.
It happens in the function
InitParameters
when the system calls
SetConnectionFlag(cfParameters, True);
before this call ADOCommand.CommandObject.Parameters evaluates to 0,
but after this ADOCommand.CommandObject.Parameters evaluates to 1.
Inside SetConnectionFlag it calls OpenConnection which calls
CommandObject.Set_ActiveConnection(FConnection.ConnectionObject);
and after this call the parameters are created.
I'm using SQL Server 6.5 and connecting via ODBC. The ODBC driver version is
3.70.06.90.
I've tryed to connect to MS SQL 7.0 using the "Microsoft OLE DB Provider for
SQL Server" and it worked OK, just the parameter name is returned as
"Param1" instead of "Cod" (I've written where CCod = :Cod)
If I connected to SQL 7 using ODBC then I get the error again.
Is seems to be something related to ODBC and ADO.
Hope this can help you.
Thanks.
Osmar
Mark Edington (Borland) <medi...@nolunchmeat.com> escreveu nas notícias de
mensagem:82ha17$br...@forums.borland.com...
Mark Edington (Borland)
> I've traced to the point where it seems to be happening.
Excellent job. This gives me something to work with. Although, it is still not
clear to me how you would have parameters still existing in the RefreshFromOleDB
method. If the parameters are being created as you say when the active connection
is set, there is still a call to Parameters.Clear that happens *after* that and
before the call to RefreshFromOleDB.
> Is seems to be something related to ODBC and ADO.
Is there some reason you are using the OLE DB provider instead of the native SQL
Server provider?
> Hope this can help you.
Yes. Thank you very much. I'll need to do some testing with the ODBC provider
and see if I can reproduce the problem now. However, given that it works
correctly with the native provider I'm not sure this is something that we can (or
should)
fix.
If have any additional information I will pass it along to you.
Mark
Mark Edington (Borland)
news:82jg71$rs...@forums.borland.com...
> Is seems to be something related to ODBC and ADO.
Ok, I think I've got a handle on this one now. Yes, the problem is specific to
the ODBC Provider when using a SQL Datasource that can return parameter
information (SQLServer, Oracle, etc). The error is being thrown on the call to
the CreateParameter method of the ADO Command object becuase the Direction
parameter is uninitialized. According to the SDK docs for
Command.CreateParameter method this parameter is optional, but apparently this
is not the case. You cannot create an ADO Parameter object with the Direction =
adParamUnknown, nor can you explictly set it to this value after the fact. Yet,
the Command object's Parameters collection contains items with the Direction
value set this way. I'm pretty sure this is a bug in the ODBC Provider.
At any rate, the workaround in VCL is fairly simple, we just need to check for
the Direction = adParamUnknown before calling CreateParameter and set it to
adParamInput instead. Here is a modified RefreshFromOleDBRoutine with that
change:
procedure RefreshFromOleDB;
var
I: Integer;
ParamCount: UINT;
ParamInfo: PDBParamInfoArray;
NamesBuffer: POleStr;
Name: WideString;
Parameter: _Parameter;
Direction: ParameterDirectionEnum;
OLEDBCommand: ICommand;
OLEDBParameters: ICommandWithParameters;
CommandPrepare: ICommandPrepare;
begin
OLEDBCommand := (Command.CommandObject as
ADOCommandConstruction).OLEDBCommand as ICommand;
OLEDBCommand.QueryInterface(ICommandWithParameters, OLEDBParameters);
OLEDBParameters.SetParameterInfo(0, nil, nil);
if Assigned(OLEDBParameters) then
begin
ParamInfo := nil;
NamesBuffer := nil;
try
OLEDBCommand.QueryInterface(ICommandPrepare, CommandPrepare);
if Assigned(CommandPrepare) then CommandPrepare.Prepare(0);
if OLEDBParameters.GetParameterInfo(ParamCount, PDBPARAMINFO(ParamInfo),
@NamesBuffer) = S_OK then
for I := 0 to ParamCount - 1 do
with ParamInfo[I] do
begin
{ When no default name, fabricate one like ADO does }
if pwszName = nil then
Name := 'Param' + IntToStr(I+1) else { Do not localize }
Name := pwszName;
{ ADO maps DBTYPE_BYTES to adVarBinary }
if wType = DBTYPE_BYTES then wType := adVarBinary;
{ ADO maps DBTYPE_STR to adVarChar }
if wType = DBTYPE_STR then wType := adVarChar;
Direction := dwFlags and $F;
{ Verify that the Direction is initialized }
if Direction = adParamUnknown then Direction := adParamInput; //
<== Workaround is here
Parameter := Command.CommandObject.CreateParameter(Name, wType,
Direction, ulParamSize, EmptyParam);
Parameter.Precision := bPrecision;
Parameter.NumericScale := ParamInfo[I].bScale;
Parameter.Attributes := dwFlags and $FFFFFFF0; { Mask out
Input/Output flags }
AddParameter.FParameter := Parameter;
end;
finally
if (ParamInfo <> nil) then GlobalMalloc.Free(ParamInfo);
if (NamesBuffer <> nil) then GlobalMalloc.Free(NamesBuffer);
end;
end;
end;
The other fix you posted isn't valid because you never want to assign the
parameter object from the Command object's collection to the TParameter
instance. You'll end up with an invalid reference after the connection is
closed which can cause other errors.
If you don't want to modify the VCL source you can also work around the problem
in your program by setting ParamCheck=False on the dataset and then creating the
parameter objects manually using the CreateParams method.
It's also worth noting that you will not encounter this problem if you set the
CommandText at design time or enclose the CommandText assignment inside of a
Try/Except block.
Thanks again for taking the time to track this down and report it. I'll make
sure it is fixed for the update.
Mark
Osmar
Thank you for your help. I'm going to apply the fix to the VCL.
Osmar.
Mark Edington (Borland) <medi...@nolunchmeat.com> escreveu nas notícias de
mensagem:82pl2i$jd...@forums.borland.com...
Dean Wooldridge
dean