Problem with parameter types

Discussion of open issues, suggestions and bugs regarding SDAC (SQL Server Data Access Components) for Delphi, C++Builder, Lazarus (and FPC)
Post Reply
vdev
Posts: 17
Joined: Tue 15 Sep 2009 08:28

Problem with parameter types

Post by vdev » Thu 22 Apr 2010 05:11

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.

Dimon
Devart Team
Posts: 2885
Joined: Mon 05 Mar 2007 16:32

Post by Dimon » Thu 22 Apr 2010 07:50

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?

vdev
Posts: 17
Joined: Tue 15 Sep 2009 08:28

Post by vdev » Thu 22 Apr 2010 08:46

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.

Dimon
Devart Team
Posts: 2885
Joined: Mon 05 Mar 2007 16:32

Post by Dimon » Thu 22 Apr 2010 09:51

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;

Dimon
Devart Team
Posts: 2885
Joined: Mon 05 Mar 2007 16:32

Post by Dimon » Thu 22 Apr 2010 09:53

Or use the following way to assign parameter value:

Code: Select all

MSQuery.Params[0].AsString := value0;  

vdev
Posts: 17
Joined: Tue 15 Sep 2009 08:28

Post by vdev » Thu 22 Apr 2010 10:05

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.

Dimon
Devart Team
Posts: 2885
Joined: Mon 05 Mar 2007 16:32

Post by Dimon » Fri 23 Apr 2010 07:18

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.

vdev
Posts: 17
Joined: Tue 15 Sep 2009 08:28

Post by vdev » Fri 23 Apr 2010 12:48

Hi,

any news on that?

Dimon
Devart Team
Posts: 2885
Joined: Mon 05 Mar 2007 16:32

Post by Dimon » Fri 23 Apr 2010 12:57

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.

vdev
Posts: 17
Joined: Tue 15 Sep 2009 08:28

Post by vdev » Mon 26 Apr 2010 18:18

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;

vdev
Posts: 17
Joined: Tue 15 Sep 2009 08:28

Post by vdev » Mon 26 Apr 2010 19:05

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

Dimon
Devart Team
Posts: 2885
Joined: Mon 05 Mar 2007 16:32

Post by Dimon » Tue 27 Apr 2010 08:36

We are investigating possibility to add new functionality with parameter types. As soon as we solve the problem we will let you know.

Dimon
Devart Team
Posts: 2885
Joined: Mon 05 Mar 2007 16:32

Post by Dimon » Fri 28 May 2010 11:38

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.

Post Reply