Problem with parameter types

[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.

[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?

[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.

[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;

[Dimon]

Or use the following way to assign parameter value:

Code: Select all

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

[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.

[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.

[vdev]

Hi,

any news on that?

[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.

[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;

[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

[Dimon]

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

[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.