I have a little problem with FireDAC and the TStringField and TWideStringField design time generation.
I have a little problem with FireDAC and the TStringField and TWideStringField design time generation.
I have a TFDQuery which include a column like this...
CASE WHEN EXISTS(SELECT 1 FROM CONFIGURATIONS COM WHERE COM.CD_PARENT = ART.CD_ITEM)
THEN 'Y'
ELSE 'N'
END HAS_CONFIGURATION ,
The query works well in MS-SQL and in FireBird. The problem is that MS-SQL considers that the TField is TWideStringField, but Firebird considers that is TStringField.
Has anyone solved this? I need that in both cases was of the same resulting type, because the application shall work with both databases.
I've thought in make SQL TypeCasting of the value, but I can't find a common instruction for both databases. And if it's possible for other ones too.
I have a TFDQuery which include a column like this...
CASE WHEN EXISTS(SELECT 1 FROM CONFIGURATIONS COM WHERE COM.CD_PARENT = ART.CD_ITEM)
THEN 'Y'
ELSE 'N'
END HAS_CONFIGURATION ,
The query works well in MS-SQL and in FireBird. The problem is that MS-SQL considers that the TField is TWideStringField, but Firebird considers that is TStringField.
Has anyone solved this? I need that in both cases was of the same resulting type, because the application shall work with both databases.
I've thought in make SQL TypeCasting of the value, but I can't find a common instruction for both databases. And if it's possible for other ones too.
Did you try casting it in your SQL?
ReplyDeleteJeroen Wiert Pluimers
ReplyDeleteI've tried this in Firebird to force it to TWideStringField.
CASE WHEN EXISTS(SELECT 1 FROM CONFIGURATIONS COM WHERE COM.CD_PARENT = ART.CD_ITEM)
THEN CAST('Y' AS CHAR)
ELSE CAST('N' AS CHAR)
END HAS_CONFIGURATION ,
but... don't work!
Richard Baroniunas
ReplyDeleteI've created the persistent TFields with MS-SQL Server. And now I'm running with Firebird.
The TField generated with MS-SQL Server is TWideStringField. But with Firebird is expected a TStringField.
What I need is force the type to one of the two types, with the same instruction in both databases.
Try the FireDAC way for fun: { fn CONVERT(value, type) }.
ReplyDeleteReplace CAST with { fn CONVERT ('Y', CHAR) }. FireDAC generates the appropriate SQL depending on the database.
jeff weir
ReplyDeleteThe command works well, but do not solve my problem. I'm going to investigate more.
I don't know SQL Server and Firebird specifically but have you tried casting the entire case expression? You can certainly do that in PostgreSQL so I imagine you can do it in other databases too. You may also want to specify a maximum length as this may affect the field type that is determined by FireDAC.
ReplyDeleteI finally solved the challenge!
ReplyDeleteFirst, I changed the point of view. Instead of force Firebird to got a TStringField, I've forced MSSQL to got a TWideStringField.
The new problem is that the CAST works well in this sense with MSSQL but in Firebird we don't have the NVARCHAR type. This is, I need two different instructions. The hint to do this comes in the response of +Jeff Weir.
Preprocessing Command Text (FireDAC) is the solution.
The final query is like this...
SELECT ART.CD_ITEM ,
ART.CD_FAMILY ,
ART.CD_CATALOGUE ,
CAT.DS_CATALOGUE ,
FAM.DS_FAMILY ,
{IF MSSQL}
CASE WHEN EXISTS(SELECT 1 FROM CONFIGURATIONS COM WHERE COM.CD_PARENT = ART.CD_ITEM)
THEN CAST('Y' AS NVARCHAR)
ELSE CAST('N' AS NVARCHAR)
END HAS_CONFIGURATION ,
{fi}
{IF FIREBIRD}
CASE WHEN EXISTS(SELECT 1 FROM CONFIGURATIONS COM WHERE COM.CD_PARENT = ART.CD_ITEM)
THEN 'Y'
ELSE 'N'
END HAS_CONFIGURATION ,
{fi}
ART.DS_ITEM ,
ART.CD_TAX ,
TAX.DS_TAX ,
TAX.PRC_TAX ,
.
.
.
Thank's to all!
Richard Baroniunas
ReplyDeleteI think this is not a database problem or a software defect. Can be a Firebird characteristic.
By another hand, this problem was discovered during the execution of the Automated UnitTests of the application.
With a good set of engineering tools, you can overcome these difficulties.
This makes the difference between amateurs and professional developers. ;)
Attila Kovacs that indeed was the original problem. So either that was stated wrong, or the MSSQL cast should have been to VARCHAR.
ReplyDeleteThe original problem was: "I need that in both cases was of the same resulting type, because the application shall work with both databases".
ReplyDeleteI think it's clear: "The same" is the key.
As I can't get a TStringField from Firebird, the solution was to get a TWideStringFile from MSSQL.
jeff weir any documentation URLs on this?
ReplyDeleteJuan C. Cilleruelo your original formulation was "The problem is that MS-SQL considers that the TField is TWideStringField, but Firebird considers that is TStringField." which means you should have casted the MSSQL bits to VARCHAR instead of NVARCHAR.
docwiki.embarcadero.com - Preprocessing Command Text (FireDAC) - RAD Studio
ReplyDeletehttp://docwiki.embarcadero.com/RADStudio/Tokyo/en/Working_with_Commands_(FireDAC)
And, I can't remember when, but there is a good webinar with David I et al, speaking about this.
Jeroen Wiert Pluimers
ReplyDeleteThe context. The key is the context.
Anything out of context can be what you want.
The context is very important!
https://docwiki.embarcadero.com/RADStudio/en/Preprocessing_Command_Text_(FireDAC)#Conditional_Substitution
ReplyDeleteEdit - It seems TLS on the docwiki somehow fails, so here is the plain http one:
http://docwiki.embarcadero.com/RADStudio/Tokyo/en/Preprocessing_Command_Text_%28FireDAC%29#Conditional_Substitution
Jeroen Wiert Pluimers
ReplyDeleteExactly! Here is where I read to redact the solution.
Thanks!
docwiki.embarcadero.com - Preprocessing Command Text (FireDAC) - RAD Studio