[unixODBC-support] unicode issues :/ st_fetch/SQLFetch (long truncated DBI attribute LongTruncOk not set and/or LongReadLen too small)

Daniel Kasak d.j.kasak.dk at gmail.com
Fri Mar 6 22:26:40 GMT 2015


Excellent :) Thanks for helping out.

Code:
http://tesla.duckdns.org/downloads/odbc_test.pl

Table DDL:
http://tesla.duckdns.org/downloads/DimCustomer.sql

Data:
http://tesla.duckdns.org/downloads/DimCustomerData.csv

If it's easier for you, I can set up port forwarding to my SQL Server
instance ( it's purely for testing this kind of thing ). Let me know ...

The database is Microsoft's AdventureWorksDW2014 demo database:
https://msftdbprodsamples.codeplex.com/releases/view/125550

It may ( or may not ) be easier for you to just download that demo - it
comes as a SQL Server backup file, and you don't have to mess around with
loading the CSV ( I exported it from SQL Server Management Studio ). To be
honest, I haven't used SQL Server for about 15 years, so I'm not sure what
he easiest way of loading data is these days.

If what I'm seeing is correct though, you can define a table with a single
nvarchar column, insert any non-ASCII character, as dummy data to trigger
this bug.

2 things to note in Linux:

1) If you use a substring() function on the column ( selecting *only* to
non-ASCII character ), you'll get zero records back, and the truncation
warnings.

2) If you *don't* substring() the column, you get data back, but it's
corrupted.

In Windows, the above behaves as expected.

Let me know if there is anything else you need ...

Dan

On Sat, Mar 7, 2015 at 12:31 AM, Martin J. Evans <bohica at ntlworld.com>
wrote:

> On 05/03/15 22:55, Daniel Kasak wrote:
>
>> I've installed DBD::ODBC from git just now and tested. Same error :/
>>
>
> ok, that rules that out.
>
> Can you give us a small example bit of perl which demonstrates the problem.
>
> The schema for the table would be useful too.
>
> Martin
>
>  On Fri, Mar 6, 2015 at 8:06 AM, Daniel Kasak <d.j.kasak.dk at gmail.com
>> <mailto:d.j.kasak.dk at gmail.com>> wrote:
>>
>>     Hi. Thanks for the responses :)
>>
>>     I'm using DBD::ODBC version 1.50, which is the latest version I see
>> on CPAN ( and my cpan client agrees ).
>>
>>     I'm not using bound input parameters on this particular connection -
>> I'm using it on other connections inside the app, but definitely not this
>> connection. The window where this particular failure happens is a SQL
>> client. Users can enter any SQL they want. I do very limited parsing to
>> detect whether they've entered a 'select' query or not. If it's a select,
>> the SQL gets passed to Gtk3::Ex::DBI ( which is also my code ) as a
>> 'pass-through' query ... in which case no further parsing is done, and
>> bound input parameters are not used. The SQL gets executed, and a datasheet
>> is populated from the results of the query execution.
>>
>>     I've just rebooted into Windows ... and yes I have DBD::ODBC version
>> 1.50 in Windows too.
>>
>>     Dan
>>
>>     On Fri, Mar 6, 2015 at 2:02 AM, Martin J. Evans <bohica at ntlworld.com
>> <mailto:bohica at ntlworld.com>> wrote:
>>
>>         On 05/03/15 13:47, Daniel Kasak wrote:
>>
>>             Hi all.
>>
>>             I'm struggling to understand some issues I'm seeing talking
>> to SQL Server. I'm pretty sure I've isolated the issue to unixODBC.
>>
>>             I've loaded by Microsoft's AdventureWorksDW2014 database for
>> testing purposes. To give an idea of the data, here's a query I've been
>> testing. It fails in Linux, but runs and displays find in Windows:
>>
>>             http://tesla.duckdns.org/__images/windows_odbc.png <
>> http://tesla.duckdns.org/images/windows_odbc.png>
>>
>>             As you can see, I'm substring-ing out each character ( I'm
>> debugging yet other issues ), and also getting hex values for each
>> character. Note the 6th character ( column S6 ), with hex value f3 ( column
>> H6 ). Also note the complete string at the end.
>>
>>             ---
>>
>>             Issue 1)
>>
>>             http://tesla.duckdns.org/__images/linux_odbc_1.png <
>> http://tesla.duckdns.org/images/linux_odbc_1.png>
>>
>>             Note in this screenshot, the last column - the complete
>> string. That doesn't look right. It's difficult to say *why* it doesn't
>> look right, but my feeling is that the driver is sending a multibyte
>> character, and somewhere along the line it's being interpreted as 2
>> single-byte characters.
>>
>>             Issue 2)
>>
>>             You might have noticed I'd commented out the expression for
>> S6 in the Linux query. If I run the query with the S6 expression, I get an
>> empty recordset back, and the following in STDERR:
>>
>>             DBD::ODBC::st fetchrow_array failed: st_fetch/SQLFetch (long
>> truncated DBI attribute LongTruncOk not set and/or LongReadLen too small)
>> (SQL-HY000)
>>
>>             Now ... note that this is a single character ... and that
>> things weren't too long when I selected the entire column at once. I've
>> tried setting LongReadLen to various large values. That doesn't stop this
>> error. If I also set LongTruncOk, the error disappears, *and* I get a
>> recordset, but the S6 column is null.
>>
>>             What's going on here? If this was related to the 1st issue (
>> and I feel it might be ) ... I'd say the driver manager was expecting a
>> single byte character only for the entire field, but was receiving a
>> multi-byte character.
>>
>>             ---
>>
>>             I built unixODBC-2.3.2 with the following configure options:
>>                --enable-iconv --disable-drivers --disable-driverc
>> --with-iconv-char-enc=UTF8 --with-iconv-ucode-enc=UTF16LE
>>
>>             I've tested with freetds-0.91, *and* with Microsoft's native
>> client for Linux. Interestingly, I get exactly the same behaviour with both.
>>
>>             I've obviously also tested in Windows, which produced the 1st
>> screenshot. Based on the fact that it works perfectly in Windows, I'd
>> assume Microsoft's drivers, *and* DBD::ODBC, *and* my code are behaving
>> well. And based on the fact that both freetds and MS's drivers produce
>> exactly the same failure under Linux, I'm guessing the problem lies
>> somewhere in unixODBC. I do concede that it's possible that the same bug
>> exists in freetds and Microsoft's native client, and that unixODBC is
>> completely innocent.
>>
>>             An odbc trace is at ( pasting it in the message make the
>> message too big to post, apparently ):
>>             http://tesla.duckdns.org/__downloads/trace.log <
>> http://tesla.duckdns.org/downloads/trace.log>
>>
>>             Note that this will have *two* executions of the query. My
>> app 1st executes each query with the filter "where 0=1" ... to analyse the
>> columns and decide how to go about things. Then it execute the 'real' query.
>>
>>             Any help greatly appreciated.
>>
>>             Dan
>>
>>
>>         Sounds like it could be this bug:
>>
>>         1.51_3 2015-01-17
>>
>>            [BUG FIXES]
>>
>>            RT101579 - using bound input parameters for numeric columns
>> (e.g.,
>>            SQL_NUMERIC) only works the first time and will quite likey
>> fail
>>            with "string data, right truncation" on the second and
>> subsequent
>>            calls to execute. Thanks to Laura Cox for finding.
>>
>>         What version of DBD::ODBC are you using?
>>
>>         What does you perl code look like?
>>
>>         Martin
>>
>>
>>         _________________________________________________
>>         unixODBC-support mailing list
>>         unixODBC-support at mailman.__unixodbc.org <mailto:unixODBC-support@
>> mailman.unixodbc.org>
>>         http://mailman.unixodbc.org/__mailman/listinfo/unixodbc-__support
>> <http://mailman.unixodbc.org/mailman/listinfo/unixodbc-support>
>>
>>
>>
>>
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://mailman.unixodbc.org/pipermail/unixodbc-support/attachments/20150307/f755e2d4/attachment-0001.html>


More information about the unixODBC-support mailing list