SDAC 4.50 Stored Procedure Parameters

[Alan009]

Error takes places when trying to provide some parameters into stored procedure using dynamic SQL Query.

Test stored procedure code:

CREATE PROCEDURE TestProc
@MyFirst int, @MySecond bit
AS
BEGIN
SELECT 1;
END
GO

Delphi code, which causes error:

Query := TMSQuery.Create(nil);
try
Query.Connection := FConnection;
Query.SQL.Text := 'EXEC TestProc :FirstParam, :SecondParam';
Query.Prepared := True;
Query.Params.ParamByName('FirstParam').AsInteger := 1;
Query.Params.ParamByName('SecondParam').AsBoolean := False;
Query.Open; // !!! HERE IS AN EXCEPTION (EMSError)
finally
Query.Free;
end;

Exception message says: "Implicit conversion from data type sql_variant to int is not allowed. Use the CONVERT function to run this query".

What is the reason of the problem?

System information:
- SDAC 4.50
- Windows XP SP2
- MS SQL Server 2005
- CodeGear Delphi 2007 for Win32 (SP3)

[tinof]

Hi,

i am not sure, but please try to specify the parameter types:

Code: Select all

...
Query.Prepared := True; 
Query.Params.ParamByName('FirstParam').datatype := ftInteger; 
Query.Params.ParamByName('FirstParam').AsInteger := 1; 
Query.Params.ParamByName('SecondParam').datatype := ftBoolean;
Query.Params.ParamByName('SecondParam').AsBoolean := False; 
Query.Open; // !!! HERE IS AN EXCEPTION (EMSError) 
...

[Alan009]

The type of parameter is already specified inside AsInteger and AsBoolean:

procedure TParam.SetAsInteger(Value: Longint);
begin
FDataType := ftInteger;
Self.Value := Value;
end;

procedure TParam.SetAsBoolean(Value: Boolean);
begin
FDataType := ftBoolean;
Self.Value := Value;
end;

So that is not the solution to the problem.

I suppose, that this problem comes from some bug in SDAC 4.50, because with SDAC 3.80 all worked fine.

Can anybody reproduce this error?

[Alan009]

Need help!!!

[Alan009]

Can anybody reproduce the problem using the example above?

[Antaeus]

You should setup parameter types or assign a value to them before preparation:

Code: Select all

  Query.SQL.Text := 'EXEC TestProc :FirstParam, :SecondParam';
  Query.Params.ParamByName('FirstParam').DataType := ftInteger;
  Query.Params.ParamByName('SecondParam').DataType := ftBoolean;
  Query.Prepared := True; 

[Alan009]

Thanks for the answer, it works.

But still, I'm a little bit frustrated. We usually used queries, which were created and prepared at once, and then we executed them thousands of times, just having to set parameter values before execution. That was very convinient and lead to high perfomance.
Now we will have to transform our code into something like that:

FQuery := TMSQuery.Create(...); // It is called somewhere once

The following code is executed many times in cycle:

FQuery.Params.ParamByName('...') := ... ;
if not FQuery.Prepared then FQuery.Prepared := True; // EXCESS CODE
FQuery.Open;

Previously (using SDAC 3.80) we could prepare the created query without having to set parameter values first, and we think that was more convinient.

What caused you to change the behaviour?

[Antaeus]

This change was necessary for performance optimization of prepared queries execution. This optimization eliminates additional round trips to server, and performs preparation and the first call to the prepared routine in a single batch.

In order to revert the old behavior with preparation and parameters, you should do the following:
1) add the OLEDBAccess unit to the uses clause of a unit in your application;
2) add the following line to the initialization section of the unit:
ParamsInfoOldBehavior := True

[Alan009]

Thanks a lot! It's really great. BTW, the way to restore old behaviour is very smart and easy But if the new behavior increases perfomance, we'll better make some modifications to our code instead of reverting to old behavior