String Truncation on Transfer to ClientDataset

Question

I'm using Firebird 2.1, DBExpress Driver from DevArt and Delphi 2010. Some of my reports that used to work with Delphi 2006 stopped working and produced an error message indicating that "arithmetic exception, numeric overflow, or string truncation" had occurred. The error occurred at this point in my code:

cds.Data := dsProvider.Data;

I found the place in my SQL statement that caused the error:

iif(ytd.hPayType <> -1,t.sCode, 'NET') sPayType

T.sCode is a Varchar(10) field. My conclusion is that the query returns data to the dsProvider and that when the dsProvider.Data is passed to the cds.Data, the cds component sets the field width based on the first value it receives. I get the same error message if I change the "iif" to a CASE statement. I managed to work around the issue by doing this:

 CAST(iif(ytd.hPayType <> -1,t.sCode, 'NET') AS varchar(10)) sPayType

Since this used to work in Delphi 2006 without the CAST, I assume that the new behavior is due to an update to the TClientDataset. It would be nice to have the old, more forgiving behavior. Is there a way to configure the ClientDataset to accept this without a complaint or do I need to just tell my users to CAST on string results based on iif and CASE statements?

Answer 1

Well, with a little more experience, it looks like I am seeing this truncation error show up consistently with the Delphi 2010 version of ClientDatasets. If I find a resolution that does not involve having to use CAST in the query, I will post it here. But for now, I am going to close this posting.

Answer 2

I used to work a lot with firebird in my last job, this error happens when you already have a large (length) varchar field value stored in the db and you are trying to "get" the string in delphi, try updating the value in the db to a smaller (length) varchar. I'm not sure if will work for you but give it a try.