"DateTime contains an invalid value Table" on SQLBindFile

[WesleyDeprez]

Hi

When we are doing the SQLBindFile in DF24.0 on a table that has a Datetime-field, we are getting the error 'DateTime contains an invalid value Table'.
There is however a valid date in the field since we can see the value in other views.

This problem looks very simular to the problem in underlaying thread:
https://support.dataaccess.com/Forum...ht=SQLBindFile

Is there any chance this is again a problem in mssqldrv.dll?
We are using DF24.0 version 24.0.25.46 with mssqldrv.dll version 24.0.25.10

Best regards
Wesley Deprez

[Vincent Oorsprong]

Wesley,

May I suggest to use the - introduced in v23 - cSQLExecutor? It will be better, faster, easier to use.

[danwalsh46]

Vincent.

this is the question:

Is there any chance this is again a problem in mssqldrv.dll?

[Samuel Pizarro]

What about running programs still using the old class, that are migrated to DF 24. ?

I say that, because we use this a lot, and we are planning to upgrade to df 24 soon.

So, please, I don't want to hear that we need to change existing code to use the SqlExecutor...

[Harm Wibier]

The old class should work just fine and is still fully supported. It is also not marked obsolete yet, but we might do this is the near future to steer new development towards the new API.

Personally I was not aware of this SQLBindFile function and never used it. Reading about it in the doc does make me feel this is pretty raw, dangerous and error prone functionality. So I would definitely recommend against using it. However, we will look into this specific issue.

[Samuel Pizarro]

If its properly advised in time that it becomes deprecated and then later removed I am fine with changing the code. But definitely that was not the case for df24.

thanks for the feedback

[Marcia Booth]

Hi Wesley,

Can you test this in the latest build of DataFlex 24.0 (24.0.32.57) that uses mssqldrv 24.0.32.10?

Also, what version of SQL Server? What is the SQL type on the server (datetime, datetime2)? What is it mapped to in the INT file? Any other table with datetime showing the error or is this the only table where you experience this issue?

[WesleyDeprez]

Hi Marcia

The probleem still occurs in the latest build of DataFlex 24.0 (24.0.32.57) that uses mssqldrv 24.0.32.10.

So the SQL type of the field is datetime and in the INT-file there is a reference to this datetime. (FIELD_TYPE DATETIME)
When i remove the reference of the datetime in the INT-file, there is no error when using SQLBindFile.
But then Dataflex handles this field as a normal date so we cant save the time anymore.

So i checked what SQL type of field there is created when using Database Builder.
This is a datetime2 and there is no reference in the INT-file.
Dataflex handles this field correct as a datetime but i still have the error when using SQLBindFile.

This error occurs from the moment there is a datetime/datetime2 field in the table you are trying to bound.

Best regards
Wesley Deprez

[Martin Moleman]

Thanks, we could reproduce the issue.
We will be looking into this.

[raveens]

FYI - we have the same issue too with DF24.0 using DB2

The reason why we can't use the new cSQLExecutor is because we NEED the SQLBindFile functionality of binding the result-set to the file buffer one-record at a time.

Send SQLFetchActivatesBuffer of hstmt iMainFile True
...
Get SQLFetch of hstmt to iFetchResult
If (iFetchResult) Send SQLBindFile of hstmt iMainFile
...