[SAP][ODBC Driver]Invalid attribute value

[serega]

I am connecting to SAP IQ via SQLAnywhere17 ODBC driver. This combination is not officially supported by turbodbc, but I would still like to get it to work. isql works fine, I can connect and query tables. I can also access the database via https://pypi.python.org/pypi/sqlanydb/, but it is very slow. I was able to query a few tables with turbodbc, which was very fast with the default configuration, but the great majority of them fails

Error: ODBC error
state: HY024
native error code: 0
message: [SAP][ODBC Driver]Invalid attribute value

I could not find any patterns common to the tables that work (5% of the database), and that don't. Any hints what I can try?

[dirkjonker]

It can help if you can provide ODBC trace information about the queries that fail. Instructions (linux) Instructions (windows) Don't forget to turn tracing off afterwards as it has a large impact on performance!

[MathMagique]

Hi! Thanks for reporting! I second Dirk's suggestion to turn on ODBC tracing. It should provide me with the attribute identifier and attribute value in question, so that I can start looking into the thing even if I do not have access to an SAP IQ instance.

[serega]

Thanks for the quick replies. I enabled ODBC tracing, and found the failing attribute

[ODBC][82156][1518633293.387255][SQLSetStmtAttr.c][266]
        Entry:
            Statement = 0x7fc4638c4c00
            Attribute = SQL_ATTR_ROW_ARRAY_SIZE
            Value = 0xccccc
            StrLen = -6
[ODBC][82156][1518633293.387454][SQLSetStmtAttr.c][936]
        Exit:[SQL_ERROR]
        DIAG [HY024] [SAP][ODBC Driver]Invalid attribute value

What I discovered is when the query succeeds the value of SQL_ATTR_ROW_ARRAY_SIZE is small.

[ODBC][3235][1518634051.268819][SQLSetStmtAttr.c][265]
        Entry:
            Statement = 0x7fc6bd4b6400
            Attribute = SQL_ATTR_ROW_ARRAY_SIZE
            Value = 0x4bb6
            StrLen = -6
[ODBC][3235][1518634051.269012][SQLSetStmtAttr.c][928]
        Exit:[SQL_SUCCESS]

It seems like the SQL_ATTR_ROW_ARRAY_SIZE is greater than 500,000 when it fails and less then 50,000 when it succeeds. I have a full trace of successful and failed execution.

For reference, I use latest anaconda distribution with turbodbc 2.5.0. For this last round of testing I compiled the latest unixODBC-2.3.5 from sources.

[serega]

Hi. I played with various configuration properties of both SQLAnywhere driver driver and turbodbc, and I was able to make it work by reducing the property "read_buffer_size" to 16Mb using make_options(). Converting my sql queries to pandas dataframes using turbodbc is about 20 - 25 times faster compare to pandas.read_sql_query() with sqlanydb.

[MathMagique]

Hi Sergey! A factor of 20 is a nice result! Thanks for providing the ODBC trace, and alos thanks for interpreting the results! Turbodbc uses information about the columns in the result set to determine the number of rows to retrieve in bulk if you use a read_buffer_size specified in Megabytes.

According to the ODBC reference, drivers are supposed to overwrite this value if the value exceeds their maximum:

If the specified rowset size exceeds the maximum rowset size supported by the data source, the driver substitutes that value and returns SQLSTATE 01S02 (Option value changed).

It seems that SQLAnywhere does not do that :-). It makes sense that reducing the buffer size will reduce the risk of the error you reported popping up. The "best" number will depend on the smallest table, however, where small is with respect to the number of bytes per row.

If you would be interested in adding your experiences to the databases section of the documentation, that would me much appreciated!