Page 1 of 1
Problem with parameter types
Posted: Thu 22 Apr 2010 05:11
by vdev
Hello again,
I am making a test project connecting to SQL2008.
I drop a TMSQuery, link it to the connection and put the statement
SELECT * FROM COUNTRY WHERE NAME=:0
On the parameters collection, I see the :0 defined but it has no type. If I do the same with ADO it immediately sets :0 as Widestring.
My problem is that in real life the entire project uses dynamic queries where I say
GetQuery("SELECT * FROM COUNTRY WHERE NAME=:0", VarArray(['Italy'])) and my getquery function creates the tmsquery and gets me the results.
Now I get an error message 2h.
Posted: Thu 22 Apr 2010 07:50
by Dimon
You can use the following way to assign parameters value:
Code: Select all
MSQuery.Params[0].Value := value0;
MSQuery.Open;
What is the problem with this way?
Posted: Thu 22 Apr 2010 08:46
by vdev
Hi,
I do assign the values that way.
The problem is that when the parameters collection is initialized by sdac, the parameters come as ptUnknown and ftUnknown.
In ADO, BDE, DBX, when you enter the query with params and you open the parameters collection editor you see that the parameters are already typed correctly by the engine.
Posted: Thu 22 Apr 2010 09:51
by Dimon
To solve the problem you can use the following code:
Code: Select all
MSQuery.FieldDefs.Update;
MSQuery.Params[0].DataType := MSQuery.FieldDefList.FieldByName(MSQuery.Params[0].Name).DataType;
Posted: Thu 22 Apr 2010 09:53
by Dimon
Or use the following way to assign parameter value:
Code: Select all
MSQuery.Params[0].AsString := value0;
Posted: Thu 22 Apr 2010 10:05
by vdev
Hi and thank you for your prompt reply.
The problem is that I do not fetch any fields in order to update fielddefs this is a mechanism that should be done automatically by the system or you need whenever you create a query by code to type the parameters by hand which is an overkill.
Why is this behavor different than all the other engines?
Note that my query is an update statement there is no select or nothing.
Posted: Fri 23 Apr 2010 07:18
by Dimon
Now SDAC doesn't support setting parameters data type on assigning SQL query. SQL Server converts parameter values to the needed type itself. Please specify the exact error message that arises in your application when you assign parameters via the Value property.
Posted: Fri 23 Apr 2010 12:48
by vdev
Hi,
any news on that?
Posted: Fri 23 Apr 2010 12:57
by Dimon
Dimon wrote:Now SDAC doesn't support setting parameters data type on assigning SQL query. SQL Server converts parameter values to the needed type itself. Please specify the exact error message that arises in your application when you assign parameters via the Value property.
Posted: Mon 26 Apr 2010 18:18
by vdev
Hi again,
the error message is
----
Invalid character value for cast specification.
Parameter[0] :prm0 - invalid value (Status = 2h).'.
----
My code is as follows:
Code: Select all
function TApp.CreateQuery(const ASQL: String; APrms: Variant): TDataset;
var
I: Integer;
begin
Result := TMSQuery.Create(Self);
with Result as TMSQuery do
begin
Connection := FDB;
DisableControls;
SQL.Text := ASql;
FieldDefs.Update; // devart suggestion
if Params.Count>0 then
begin
if VarIsArray(APrms) then
begin
for I:=0 To Params.Count-1 Do
begin
Params[i].ParamType := ptInput;
Params[i].Value := APrms[i];
end;
end Else
Params[0].Value:=APrms;
end;
end;
end;
Posted: Mon 26 Apr 2010 19:05
by vdev
Just in case it helps you, the offending queries are all update/delete statements...
Profiler shows the following:
Code: Select all
exec sp_executesql N'update COUNTRYTABLE set NAME=@P1 where Uid=@P2',N'@P1 nvarchar(30),@P2 uniqueidentifier',NULL,NULL
Posted: Tue 27 Apr 2010 08:36
by Dimon
We are investigating possibility to add new functionality with parameter types. As soon as we solve the problem we will let you know.
Posted: Fri 28 May 2010 11:38
by Dimon
New build of SDAC version 4.80.0.58 is available for download now.
TMSDataSetOptions.DescribeParams property, which provides automatic parameters describing when a statement is prepared, was added in this build.