Subject Re: [IBO] SQL Insert Error
Author John W. (Jack) Cane
Post date 2013-09-23T00:37:22Z
Tried truncating the real-number string representation using the Delphi copy command, so that the number is represented as 'x.xxx', in contrast to the original 15 significant figures. Got the same error.

Actually considering changing the column type to varchar, posting with FloatToStr(), and retrieving with StrToFloat(). In most applications, with modern cpu's, would anyone notice the extra steps?

Or is there a better way to enter real numbers into Firebird? I'm currently using sql data type Float.

Best,
jwc
from iMac

On Sep 22, 2013, at 6:16 PM, John W. (Jack) Cane <jwcane@...> wrote:

Thanks Helen.

FB is returning the same error at runtime.

I only need three digits to right of decimal point. Modified the query as follows:

'Insert into table(columns) Values(''str1'',''str2'',Cast(0.804753839969635 as Float(3)),''str3'',''str4'') returning pkValue'

I'm assuming that the '' represent double-single quotes of Delphi usage.

Best,
jwc
from iMac

On Sep 22, 2013, at 4:51 AM, Helen Borrie <helebor@...> wrote:

 

At 04:09 p.m. 22/09/2013, John W. (Jack) Cane wrote:

>After numerous successful inserts into FireBird using the same code and TIB_dSQL, FB just issued an error, apparently(?) unhappy with a float number, which was obtained by division of two integers. The query text reads as follows:
>
>'Insert into Images(FileName, Color, Aspect, Genus, Species) Values(''L1161616-2.jpg'',''Red'',0.804753839969635,''Phragmipedium'',''longifolium hybrid Sorcerers Apprentice'') returning kImage'
>
>Delphi runtime error message is:
>
>"First chance exception class EIB_ISCError with message 'ISC ERROR CODE:35544321
>
>"ISC ERROR MESSAGE: arithmetic exception, numeric overflow, or string truncation string right truncation"
>
>All SQL data types are varchar except the third column (float).
>
>If anyone can show me how to fix this, would be grateful.

You'll need to include a CAST expression on the data for that float field.

Realise also that the number that will be stored in a FLOAT (single precision number) will have 9 digits, of which *at most* 7 will be in any way reliable.

Helen