SOLVED: Firebird time conversion with SQL CAST
lazarus / 16-Databases

----------------------------------------------------------------------
iru · 2021-06-05 11:17

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 · 2021-06-05 17:04

Quote from: iru on June 05, 2021, 01:17:51 pmGentlefolk,
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][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i ...
cast(A.RESULT_PERF as varchar(15)) as RESULT_PERF ...

----------------------------------------------------------------------
iru · 2021-06-05 23:04

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 · 2021-06-06 01:02

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