Parameter XX is of an unknown type

BIZ TMS RemoteDB
1

I have an issue where I am getting Parameter XX is of an unknown type due to me creating an Insert SQL query with all fields from a table as parameters so I can set any field/param value without changing the SQL statement. If I run Locally with GenericDatabase and TXDataset. These seem to be ok with not setting a particular parameter value. But with a remote connection I get this error if I do not set a value for a parameter so remains ftUnknown. I Understand the error just wonder if there is anyway to get around this as it only happens with the remote connection. Otherwise I have a huge job of changing lots of the program to only include currently used parameters in my inserts. The issue specifically arises here
RemoteDB.Client.Dataset
TRDBParam.GetIndexedDataSize
Result := Self.GetDataSize

2

I didn't even know that you can simply keep a parameter as ftUnknown. I don't think there is a workaround.

All parameters are included in the SQL, if the parameter type is unknown, how is it set and what does the RDBMS do with the unset parameter?

3

If you have statement "insert into sometable (fld1,fld2) values (:par1,:par2) and only set the value for par1, so parameterbyname('par1').AsString := 'mystring' then par1 datatype is ftString and par2 datatype is ftUnknown. If I debug the local connection using TGenericDatabase and Firedac the parameter is included with datatype ftUnknown as datatype and parameter.isnull = True. The main difference is that the GenericDatabase/Firedac code is using Assign(Params) where as the remote has its own logic in RemoteDB.Client.Database -> BuildStatementBody -> WriteParamValue to set parameter values. That's where the size is fetched (GetIndexedDataSize) and where the datatype is checked and fails.

If I put the following logic into (RemoteDB.Client.Dataset -> GetIndexedDataSize) then it works without error and the param value is not set in (RemoteDB.Client.Database ->WriteParamValue)

if DataType = ftUnknown then
  Result := 0   //tgk try to fix param of unknown type
else
  Result := Self.GetDataSize
4

It's funny because if you inspect source code for Self.GetDataSize, you will get exactly this code snippet:

  case DataType of
    ftUnknown: DatabaseErrorFmt(SUnknownFieldType, [Name], DataSet);

This is from Delphi, which means parameter ftUnknown is simply not accepted.

5

Yes I understand that part is functioning as it is coded to, my issue is that it works one way (local) and not the other (remote), and its due to a difference between the way Firedac assigns parameter values (accepts ftUnknown) and the way TxDataset is assigning parameter values. Firedac and FIB etc must not be getting the field size the same way. I may just have to change the code as I have shown above for my project

6

My point is: does FireDAC (without RemoteDB) simply works with parameters of type ftUnknown? The values are set to null in the database?

7

Yes exactly, FireDac is working with parameters of type ftUnknown and setting the values to null in the database