freddy77
commented
6 years ago Sorry for the late reply. SQL_POSITION should be used to position in the specific row but I cannot find from the log where you specify the amount of batch rows to fetch? You have to specify multiple row, then use SQL_POSITION/SQLGetData. Note that is you specify 100 rows and you have 150 rows in the results, first SQLFetch will retrieve 100 rows and you have to use SQL_POSITION to scan all these rows, second SQLFetch will return other 50 rows and still you have to scan them with SQL_POSITION.
We are trying to read variable length Spatial data from Microsoft SQL Server Spatial 2012 on Linux using FreeTDS driver.
The size of the data is not known in advance and is read using Long Data algorithm, similar to what is described here: https://docs.microsoft.com/en-us/sql/odbc/reference/develop-app/getting-long-data
The driver returns SQL_GD_BLOCK for SQLGetInfo(SQL_GETDATA_EXTENSIONS), which according to the docs indicate that SQLGetData can be called for an unbound column in any row in a block (where the rowset size is greater than 1) of data after positioning to that row with SQLSetPos. https://docs.microsoft.com/en-us/sql/odbc/reference/syntax/sqlgetinfo-function
The cursor is set to SQL_CURSOR_STATIC. SQLFetch is called to fetch a block of rows.
SQLSetPos(SQL_POSITION) is called to position the cursor on each row of the block one at a time. https://docs.microsoft.com/en-us/sql/odbc/reference/syntax/sqlsetpos-function
SQLGetData is used to read the data into a buffer. https://docs.microsoft.com/en-us/sql/odbc/reference/syntax/sqlgetdata-function
SQLFetch and SQLSetPos do not return any errors. SQLGetData returns "[FreeTDS][SQL Server]Invalid cursor state" for any row in the block.
Our question is, does FreeTDS driver support block fetch for unbound columns as the return of SQL_GD_BLOCK indicates?
I am attaching a freetds.log file which captures the query and the failure. odbc_export.h:837:SQLPrepareW(0x7fd608000dd0, SELECT [UT_SpatialColumn_Last].[ALAND] AS [ALAND], [UT_SpatialColumn_Last].[AWATER] AS [AWATER], [UT_SpatialColumn_Last].[ID] AS [ID], [UT_SpatialColumn_Last].[NAME] AS [NAME], [UT_SpatialColumn_Last].[Geography] AS [Geography] FROM [dbo].[UT_Spat, -3)
We have spent a good deal of time experimenting with fetch sizes and gathering corresponding freetds driver logs. We would be happy to share them if it helps you to troubleshoot this issue.
Thanks a lot,
boris
freetds.log