Page 1 of 1

bindParamenter issue: [Microsoft][ODBC Microsoft Access Driver]String data, right truncated

Posted: 29 Oct 2015 16:19
by kevin.allen@mckesson.com
I have taken the example project "SimpleODBC". changed 1 line

%Stmt:setParameterValue(1, string(Name)),
Stmt:bindParameter_string(1, string::length(Name), odbcStatement::input),

And now the program errors with:
Native ODBC exception

Predicate name = execute
StatementString = insert into PersonBin(name, age, birthday, longfield) values
(?,?,?,?)
ChildClassName = odbcStatementSupport
SqlState = 22001
NativeErrorCode = 31
NativeErrorMessage = [Microsoft][ODBC Microsoft Access Driver]String data, right truncated

We have been working on same error message in our Project onsite, but using SQL Server driver.

Any help would be appreciated.

Thank you;
Kevin Allen
Kevin.Allen@McKesson.com

its been a while since i did any SQL

Posted: 29 Oct 2015 16:40
by Paul Cerkez
but I assume that the ? in the
StatementString = insert into PersonBin(name, age, birthday, longfield) values (?,?,?,?)
values section are replaced with bound variables or fixed values.

If not, the input string will error. As written, it looks like an attempt to create a blank record.

RE: bindParamenter issue: [Microsoft][ODBC Microsoft Access Driver]String data, right truncated

Posted: 29 Oct 2015 16:46
by kevin.allen@mckesson.com
In the example file you will see that there are fixed values.
so there are no blank records being written.

I would post the entire example project, but it is too large (5mb) zipped to post here.

Posted: 29 Oct 2015 20:45
by Thomas Linder Puls
If I change the code exactly like you explain, I get this:

Code: Select all

clauses     insertBinaryDemo(Stmt) :-         stdio::write("\n\nInsert from facts to PersonBin table:\n(There must be 3 rows in this table)"),         Stmt:prepare("insert into PersonBin(name, age, birthday, longfield) values (?,?,?,?)"),         foreach person_db(Name, Age, BirthYear, BirthMonth, BirthDate) do             % Stmt:setParameterValue(1, string(Name)),             Stmt:bindParameter_string(1, string::length(Name), odbcStatement::input),             Stmt:setParameterValue(2, integer(Age)),             BirthDay = time::newDate(BirthYear, BirthMonth, BirthDate),             Stmt:setParameterValue(3, localTimeValue(BirthDay:getTime())),             Stmt:setParameterValue(4, binary(generateLongBinary())),             Stmt:execute(),             Stmt:resetParameter_all(),             stdio::writef("\n%(%, %)", Name, Age, BirthDay:formatShortDate())         end foreach.
With this code I also get then mentioned SQL truncate exception.

However that code is nonsense, because it never set any value for the Name parameter.

If both binding the parameter and setting the value:

Code: Select all

clauses     insertBinaryDemo(Stmt) :-         stdio::write("\n\nInsert from facts to PersonBin table:\n(There must be 3 rows in this table)"),         Stmt:prepare("insert into PersonBin(name, age, birthday, longfield) values (?,?,?,?)"),         foreach person_db(Name, Age, BirthYear, BirthMonth, BirthDate) do             Stmt:bindParameter_string(1, string::length(Name), odbcStatement::input),             Stmt:setParameterValue(1, string(Name)),             Stmt:setParameterValue(2, integer(Age)),             BirthDay = time::newDate(BirthYear, BirthMonth, BirthDate),             Stmt:setParameterValue(3, localTimeValue(BirthDay:getTime())),             Stmt:setParameterValue(4, binary(generateLongBinary())),             Stmt:execute(),             Stmt:resetParameter_all(),             stdio::writef("\n%(%, %)", Name, Age, BirthDay:formatShortDate())         end foreach.
then the exception disappears.

(I believe this is exactly what Paul is saying).

By the way, if I zip the simpleODBC project it becomes 552 KB. I believe you also zip the OBJ and EXE directory, but that is not necessary. If I also exclude the Access database, then the project zips to 9 KB.

Posted: 30 Oct 2015 1:00
by Paul Cerkez
yup, that's it.

Thanks Thomas.

handling NULLs

Posted: 30 Oct 2015 3:01
by kevin.allen@mckesson.com
ok, understand.. thanks.

So if there is never a value for the Name parameter, then you would get this error, as if it is trying to handle a NULL.

What if the value is truly NULL for the value? Would that cause the same issue

Posted: 30 Oct 2015 6:12
by Peter Muraya
I guess you would need to set the relevant parameter to null explicitly, e.g.,

Code: Select all

... Stmt:setParameterValue(1, null()), ...

Posted: 30 Oct 2015 8:49
by Thomas Linder Puls
The normal situation where this exception is given is when you try to insert a string that is longer than the database field you insert it into.

E.g. '1234' in to a 2 character field, because the result will become '12' which is right truncated.

The image is from MSDN SQLExecute Function

Posted: 30 Oct 2015 12:43
by kevin.allen@mckesson.com
Yes, I understand.. Thanks.

there is a another situation where Microsoft is reporting this behavior.
Would this be possible in how data is handled:

https://msdn.microsoft.com/en-us/librar ... .110).aspx

Basically is sounds like Unicode does not always translate correctly and can cause the truncation error.

sorry for all the questions.

Posted: 30 Oct 2015 14:56
by Thomas Linder Puls
I doubt it has any relevance, since is says (my highlight):
This topic contains a list of known issues with the ODBC driver on Linux.

ODBC required?

Posted: 30 Oct 2015 19:05
by kevin.allen@mckesson.com
Is ODBC 100% required to communicated to SQL server from Prolog?
Or is there some other way without ODBC?

sample ODBC SQL server project

Posted: 2 Nov 2015 21:01
by kevin.allen@mckesson.com
The attached is based on example code.
what I would like to do is have this modified so that the data being passed during binding is UTF-8 codepage.
Or can someone tell me how to edit this myself to change data codepage .

Posted: 3 Nov 2015 9:37
by Thomas Linder Puls
When you use the "string" functor you use utf-16. The database is responsible of converting the data from utf-16 into whatever codepage the field have.

If you want to make the conversion yourself you should use the "string8" functor:

Code: Select all

            Stmt:setParameterValue(2, string8(string8::toUtf8(ClaimID))),
But in that case you are responsible for choosing the correct codepage.