[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]

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.

[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.

[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.

[Paul Cerkez]

yup, that's it.

Thanks Thomas.

[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

[Peter Muraya]

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

Code: Select all

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

[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

[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.

[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.

[kevin.allen@mckesson.com]

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

[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 .

[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.