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

Discussions related to Visual Prolog
kevin.allen@mckesson.com
Posts: 6
Joined: 3 Oct 2013 12:05

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

Unread post by kevin.allen@mckesson.com » 29 Oct 2015 16:19

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

Paul Cerkez
VIP Member
Posts: 94
Joined: 6 Mar 2000 0:01

its been a while since i did any SQL

Unread post by Paul Cerkez » 29 Oct 2015 16:40

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.
AI Rules!
P.

kevin.allen@mckesson.com
Posts: 6
Joined: 3 Oct 2013 12:05

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

Unread post by kevin.allen@mckesson.com » 29 Oct 2015 16:46

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.
Thanks,
Kevin

User avatar
Thomas Linder Puls
VIP Member
Posts: 1176
Joined: 28 Feb 2000 0:01

Unread post by Thomas Linder Puls » 29 Oct 2015 20:45

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.
Regards Thomas Linder Puls
PDC

Paul Cerkez
VIP Member
Posts: 94
Joined: 6 Mar 2000 0:01

Unread post by Paul Cerkez » 30 Oct 2015 1:00

yup, that's it.

Thanks Thomas.
AI Rules!
P.

kevin.allen@mckesson.com
Posts: 6
Joined: 3 Oct 2013 12:05

handling NULLs

Unread post by kevin.allen@mckesson.com » 30 Oct 2015 3:01

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
Thanks,
Kevin

Peter Muraya
VIP Member
Posts: 147
Joined: 5 Dec 2012 7:29

Unread post by Peter Muraya » 30 Oct 2015 6:12

I guess you would need to set the relevant parameter to null explicitly, e.g.,

Code: Select all

... Stmt:setParameterValue(1, null()), ...
Mutall Data Management Technical Support

User avatar
Thomas Linder Puls
VIP Member
Posts: 1176
Joined: 28 Feb 2000 0:01

Unread post by Thomas Linder Puls » 30 Oct 2015 8:49

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
Attachments
22001.png
SQLExecute state 22001
22001.png (6.56 KiB) Viewed 8125 times
Regards Thomas Linder Puls
PDC

kevin.allen@mckesson.com
Posts: 6
Joined: 3 Oct 2013 12:05

Unread post by kevin.allen@mckesson.com » 30 Oct 2015 12:43

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.
Thanks,
Kevin

User avatar
Thomas Linder Puls
VIP Member
Posts: 1176
Joined: 28 Feb 2000 0:01

Unread post by Thomas Linder Puls » 30 Oct 2015 14:56

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.
Regards Thomas Linder Puls
PDC

kevin.allen@mckesson.com
Posts: 6
Joined: 3 Oct 2013 12:05

ODBC required?

Unread post by kevin.allen@mckesson.com » 30 Oct 2015 19:05

Is ODBC 100% required to communicated to SQL server from Prolog?
Or is there some other way without ODBC?
Thanks,
Kevin

kevin.allen@mckesson.com
Posts: 6
Joined: 3 Oct 2013 12:05

sample ODBC SQL server project

Unread post by kevin.allen@mckesson.com » 2 Nov 2015 21:01

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 .
Attachments
ODBCsql.zip
(6.86 KiB) Downloaded 365 times
Thanks,
Kevin

User avatar
Thomas Linder Puls
VIP Member
Posts: 1176
Joined: 28 Feb 2000 0:01

Unread post by Thomas Linder Puls » 3 Nov 2015 9:37

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.
Regards Thomas Linder Puls
PDC

Post Reply