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 »

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: 106
Joined: 6 Mar 2000 0:01

its been a while since i did any SQL

Unread post 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.
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 »

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: 1398
Joined: 28 Feb 2000 0:01

Unread post 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.
Regards Thomas Linder Puls
PDC
Paul Cerkez
VIP Member
Posts: 106
Joined: 6 Mar 2000 0:01

Unread post by Paul Cerkez »

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 »

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 »

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: 1398
Joined: 28 Feb 2000 0:01

Unread post 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
Attachments
SQLExecute state 22001
SQLExecute state 22001
22001.png (6.56 KiB) Viewed 22615 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 »

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: 1398
Joined: 28 Feb 2000 0:01

Unread post 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.
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 »

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 »

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 507 times
Thanks,
Kevin
User avatar
Thomas Linder Puls
VIP Member
Posts: 1398
Joined: 28 Feb 2000 0:01

Unread post 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.
Regards Thomas Linder Puls
PDC
Post Reply