TFDQuery.Prepare cannot determine parameter types for INSERT query on MS SQL SERVER

Question

I open a TFDConnection for MS SQL Server with parameters:

DriverID=MSSQL
Password=test
User_Name=test
Server=VS2003-2008
Database=test
Single Connection=TRUE
Max Cursors=256
USE OLEDB=TRUE

I then create a TFDQuery (run time), set its connection to the above, fill it with an parameterized INSERT query:

insert into TT_ACT (TT_ACT_ID,TT_PARENT_ID,TT_FROMDATE,TT_TODATE,TT_NAME,TT_NR,TT_CODE,TT_GROUP...)
values (:TT_ACT_ID,:TT_PARENT_ID,:TT_FROMDATE,:TT_TODATE,:TT_NAME,:TT_NR,:TT_CODE,:TT_GROUP,...)

I then call Prepare for the query and get:

[FireDAC][Phys][MSSQL]-335 Parameter [TT_ACT_ID] data type is unknown.
Hint: Specify TFDParam.DataType or Assign TFDParam.Value before Prepare/Execute call

If I do the same for a FireBird database there are no issues.
I guess it has something to do with using OLEDB. There is no native MS SQL client on the machine.
There is no FDPhysMSSQLDriverLink on the datamodule where the TFDConnection resides, but adding one makes no difference.
Table TT_ACT exists.

What am I forgetting to make this work?

Cubbyhole

how are you setting up the params? the error seems to be saying that you've not specified the data type for your params, but you've not shown the param setup code. – Gurevich

this is a c# reference / example for what i'd expect to see an equivalent piece of code. command.Parameters.Add("@ID", SqlDbType.Int); – Gurevich

@Tanner. I do not; I let Prepare figure it out – Cubbyhole

Firebird describes so called XSQLVAR variables when the command is prepared (they parse command and returns collection of what is needed) whilst with ODBC you must explicitly ask for parameter descriptions for the prepared command (by calling SQLDescribeParam) which does not happen in FireDAC. But I wouldn't treat that as issue, because in the end, it is the developer who fills out the parameter collection and they must know what they do (which data types they use for which values they fill in). If it gets prepared doesn't change much. – Preponderate

I would follow the recommendation from help "It is recommended to setup parameters before the Prepare call." – Preponderate

Yeah, that crossed my mind. This is old code I'm converting to FireDAC, I may have to rewrite more than I originally thought... – Cubbyhole

@JanDoggen, I recently faced a similar problem porting a BDE application to FireDAC. Using AsString on a parameter in BDE internally converts to the required type, while FireDAC changes the type of the parameter. – Treacle

@Uwe, once you call Prepare, you should not (maybe must not) modify parameter data types (that's what drivers want). If you access values by As<T> accessor, you can expect exception like this, because you're saying, "hey FireDAC, set me this value into parameter that is supposed to be of type T" whilst on the other hand you expect fetching parameter collection "all-inclusive". – Preponderate

@Victoria, actually I learned that the hard way! For new code it wouldn't be a problem as I usually know about the underlying datatype and act accordingly. We just didn't emphasize that the whole code base was using As<T> all over the place happily ignoring the db field type completely. – Treacle

Answer 1

I would follow help here and avoid calling Prepare before parameters are defined (their data types are fully specified). You haven't missed anything but this note from help:

It is recommended to setup parameters before the Prepare call.

For common ODBC drivers (you are still talking to an ODBC driver, no matter if they internally uses OLE DB to communicate with the DBMS), FireDAC doesn't determine parameter data types for the prepared command. Instead, it prepares command statement on the target DBMS and tries to bind existing ones from the Params collection. That's how the Prepare method is implemented (Tokyo).

ODBC API provides the SQLDescribeParam function to obtain parameter details for the prepared command, but FireDAC doesn't use it anywhere (at this time). Instead, it leaves building parameter collection manually. Which is not wrong, because in the end, it is the developer who needs to know which value to assign to a certain command parameter so as to know this value type.

Preponderate

OK, I now setup all Params properly, then call Prepare. That works. – Cubbyhole

Answer 2

Set each of your parameters DataType property, and then you can call Prepare ie:

  qry.ParamByName('foo').DataType := TFieldType.ftString;
  qry.ParamByName('bar').DataType := TFieldType.ftInteger;
  qry.Prepare;

The DataType property is of type Data.DB.TFieldType, here is a list of all possible values

Shiny