SOLVED: Firebird time conversion with SQL CAST

[iru]

Gentlefolk,
Environment: Laz 2.0.12, FPC 3.2.0, Firebird 3.07. Read the Firbird documentation.

I have a tIBQuery with  an Select SQL statement which includes
    '...A.RESULT_TYPE, cast(A.RESULT_PERF as varchar(15)),...'.

RESULT.PERF is defined in Firebird as type 'time'.

In the Select SQL Editor it 'Looks OK' on clicking test.

At run time on opening the table I get an EDataBaseError exception Field not found: "RESULT_PERF".

The field exists and if I remove the 'CAST' the table opens correctly.

Using vDebugStr := IBQDataResult.FieldByName('RESULT_PERF').AsString I get the time as '12:23:45'.

Unfortunately I need the millie seconds (123)  of '12:23:45.123' which is in the fied in the Firebird record.

Any suggestions, Ian

[dsiders]

Gentlefolk,
Environment: Laz 2.0.12, FPC 3.2.0, Firebird 3.07. Read the Firbird documentation.

I have a tIBQuery with  an Select SQL statement which includes
    '...A.RESULT_TYPE, cast(A.RESULT_PERF as varchar(15)),...'.

RESULT.PERF is defined in Firebird as type 'time'.

In the Select SQL Editor it 'Looks OK' on clicking test.

At run time on opening the table I get an EDataBaseError exception Field not found: "RESULT_PERF".

The field exists and if I remove the 'CAST' the table opens correctly.

Using vDebugStr := IBQDataResult.FieldByName('RESULT_PERF').AsString I get the time as '12:23:45'.

Unfortunately I need the millie seconds (123)  of '12:23:45.123' which is in the fied in the Firebird record.

Any suggestions, Ian

I would try giving the expression an explicit column alias, like:

Code: MySQL  [Select][+][-]

1. ... cast(A.RESULT_PERF as varchar(15)) as RESULT_PERF ...

[iru]

Thank you for the response.

Using cast(A.RESULT_PERF as varchar(13)) as RESULT_PERF resolved the unknown field error!

However things now crash with an EConvertError -
Invalid argument index in format "Field %S is to small to receive the data. Expected %D, Actual %D"

Chasing this back through the code in IBCustomDataset at line 3984 we have a length test:

        if (fdDataType = SQL_VARYING) or (fdDataType = SQL_TEXT) then
        begin
          if fdDataLength <= Field.DataSize then

fdDataLength = 13 and Field.DataSize = 8, and an exception is created.

The Field is IBQResultDataRESULT_TIME: TIBTimeField;

I will look into the creation and contents of this field definition....

Thanks, once again. Ian

[iru]

Gentlefolk,

I ground my way through the field/buffer code in IBCustomDataSet around line 3980. All looked Ok except for the length of the data field.

Stepped my way through the TIBStringField.Bind code at 1610 of IBCustomDataSet. Strange could not see the RESULT_PERF field.

Used the IBQuery 'Field Editor', The field was there, something wrong somewhere so I deleted the field and re-created it.

All OK, no errors and I can see the millie seconds at the end of the time string.

Onwards...... Ian