Is there a way in FireDAC to prepare a query to get the correct fieldtypes of the parameters? ADO can do that - before setting their values you can see what type they require. I need that for some custom value conversions before passing values to the params.
Is there a way in FireDAC to prepare a query to get the correct fieldtypes of the parameters? ADO can do that - before setting their values you can see what type they require. I need that for some custom value conversions before passing values to the params.
In FD they are ftUnknown all the time and need to be set by either setting the Value or explicitly (like for strings). And when I call Prepare it tells me that I need to set the types before calling Prepare. Isn't that why I call prepare so the DBMS can tell me what types it wants?!
In FD they are ftUnknown all the time and need to be set by either setting the Value or explicitly (like for strings). And when I call Prepare it tells me that I need to set the types before calling Prepare. Isn't that why I call prepare so the DBMS can tell me what types it wants?!
Radek Červinka Sorry I don't get how that answers my question.
ReplyDeleteStefan Glienke
ReplyDeleteSorry, we had same problem with stored procedures and solved with this. Instead setting procedure name we call helper and this helper call FillParams. But maybe is this wrong way to solve your problem. Sorry.
Radek Červinka Thanks but my question was not how to fill the params (that works well so far) but how to prepare the statement so it tells the datatypes before providing values to fill in (as ADO obviously does because before I specify the values for the params they have the proper DataType set)
ReplyDeletehttp://docwiki.embarcadero.com/Libraries/XE8/en/FireDAC.Comp.Client.TFDCustomQuery_Properties -See params,
ReplyDeletehttp://docwiki.embarcadero.com/Libraries/XE8/en/FireDAC.Stan.Param.TFDParam.DataType
I seem to recall that sql query parameters basically are untyped, i.e. not implied by the expression they are used in. Using Params, you can set explicit type.
ReplyDeleteFor query - http://docwiki.embarcadero.com/RADStudio/XE8/en/Executing_Commands_%28FireDAC%29
ReplyDeleteThe DataType is specified either explicitly or implicitly, by assigning a parameter value to the Value or AsXxxx property. Otherwise, the FormatOptions.DefaultParamDataType is used.
BTW: For storedProc is possible http://docwiki.embarcadero.com/RADStudio/XE8/en/Executing_Stored_Procedures_%28FireDAC%29
Lars Fosdal In ADO when I have lets say an update statement using parameters the parameters have the proper DataType even before I set their values (after the statement has been prepared I think - using MS SQL - no clue if that is also the case for other databases). In FD I don't.
ReplyDeleteCan't say I've noticed that. Did it require an active designtime connection?
ReplyDeleteLars Fosdal I am not working with designtime components. But yes, of course the TADOConnection used by the TADOQuery is open at that moment.
ReplyDeleteI have a table with 3 fields (integer, varbinary(max), uniqueidentifier - doesn't matter) and when I write an update statement using parameters they have ftInteger, ftVarBytes and ftGuid set before I set their Value property.
We have our own wrapper for building queries, but I did have to add some settings when passing Stored Proc params.
ReplyDeleteResult := TFDStoredProc.Create(nil);
Result.Connection := FConnection;
Result.StoredProcName := aStoredProcName;
Result.ResourceOptions.ParamCreate := False;
Result.Params.Clear; // Clean-up last call
Result.Params.BindMode := pbByNumber;
... fill params ...
Perhaps
http://docwiki.embarcadero.com/Libraries/XE8/en/FireDAC.Comp.Client.TFDQuery.ResourceOptions
has something similar?
(Yeah, the docs suck)
ReplyDeleteThis is relevant to my interests...
ReplyDeleteHave you tried to call Prepare after you set SQL?
ReplyDeleteMany DB's does not provide information about parameter data types. But at least InterBase, Firebird and SQL Server provide this info. FireDAC InterBase and Firebird drivers use this feature and provide parameter info after Prepare call. Other FireDAC drivers do not provide it.
ReplyDeleteDmitry Arefiev Thanks - I was only using MSSQL so I did not know if that's a DBMS feature or ADO. So I have to find another way to do what I was trying.
ReplyDeleteThis is, imho, a downside of tdataset. It's messy to implement the better functionality. The dc component wants to limit roundtrips to the server. The more the dcs know about the server and the specific sql intricacies the better, but also more error prone to client-server version mismatch. IBO has units that parse the sql in order to provide this "service" to you as an developer. So, maybe ado does a roundtrip? I do not think that you should generally expect this functionality from tdataset compliant components. And if you do, before you applaud, check the sql monitor.
ReplyDelete