Errors when binding strings with MSAccess ODBC Driver

[bzuillsmith]

The ODBC api expects the ColumnSize of the BindParameter call for strings to be the column size. node-odbc is setting it to 0. This causes the MSAccess ODBC Driver to throw HY104 Invalid precision value.

I'm not immediately sure where we can get the column size from on the fly, sigh. What a silly requirement of the api.

https://github.com/wankdanker/node-odbc/blob/de66ae910d05b337adec3f3405b1c92618087545/src/odbc.cpp#L689

[wankdanker]

What a silly requirement of the api.

Agreed!

I know I had played around with that value many times trying to figure out what worked best for most drivers. So, it landed at 0.

I think I had even tried setting it to the length of the incoming string. But that must have been wrong for something. Might have to check the git history for that.

[wankdanker]

interesting: http://stackoverflow.com/questions/5596905/what-to-pass-as-the-columnsize-argument-of-odbc-sqlbindparameter-in-a-c-c-prog

[bzuillsmith]

Yeah, that seems to line up with the SQL reference here: https://msdn.microsoft.com/en-us/library/ms716530(v=vs.85).aspx I'll give it a try next chance I get and maybe we can test it some more

[wankdanker]

One thing to consider is: Is ColumnSize the number of displayed characters? Or is it the total bytes that would be needed to store the string? Because depending on the encoding of the characters in string, the value reported by length may be less than the total bytes required to store the string (because of unicode madness).

[bzuillsmith]

I read in a couple places that it is the number of characters. I'll play with the value a bit and let you know how MSAccess behaves.

[bzuillsmith]

Finally got around to testing this. MS Access expects it to be the length of the string. Was able to successfully execute a prepared statement with parameters when I set ColumnSize to length. So if other drivers have an issue with length being the value, then we might have to have some kind of configuration.

[bzuillsmith]

Ah, the other challenge to this:

LongText aka Memo is a different parameter type in odbc (SQL_LONGVARCHAR as opposed to SQL_VARCHAR). So we would also have to change the ParameterType according to the MaxLength of VARCHAR. It's not likely the MaxLength is consistent across all DBs supporting ODBC so it would require some kind of config or we would need some way of querying the driver about its limitations for a particular param type. That's at least how I understand it so far.

[sfought]

I'm seeing a similar result with the Teradata 15 driver when I use a string parameter. [Teradata][ODBC Teradata Driver] Invalid precision Was there a workaround or resolution?

[LizanLycan]

The error still remains: { message: '[Microsoft][Controlador ODBC Microsoft Access]Valor de precisión no válido ', state: 'HY104' }