[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
Sat Mar 7 04:25:25 GMT 2015


Once again, I'll answer my own post.

The Netezza ODBC drivers have an option in the .odbcinst.ini file:

UnicodeTranslationOption

 ... which supports the values:
 utf8
 utf16

Setting it to utf16 fixes things. Now I'm getting unicode well supported
across multiple databases. Yay :)

Thanks for pointing me in the right direction, and sorry for the noise.

Dan

On Sat, Mar 7, 2015 at 1:18 PM, Daniel Kasak <d.j.kasak.dk at gmail.com> wrote:

> OK now I'll withdraw my entire question. I've found that building
> DBD::ODBC with unicode support:
>
> perl Makefile.PL -u
>
>  ... fixes the issues I'm seeing with SQL Server. Unfortunately, now
> connecting to Netezza is completely broken.
>
> When I call DBI->connect() ... I get an error back:
>
> DBI
> connect('DRIVER=NetezzaSQL;server=nz_host;Port=5480;UID=admin;PWD=n3t322a;Database=SYSTEM','admin',...)
> failed: [unixODBC]Srtrbtsaeepy
>
>  ... with 2 boxes at the end that I can't seem to copy + paste for some
> reason. Also the exact text of the error seems to change with each
> connection attempt.
>
> I don't see anything at the database end about an attempted connection. I
> guess the Netezza ODBC drivers don't support this setup? Is there anything
> I can do to work around this?
>
> ODBC trace:
>
> http://tesla.duckdns.org/downloads/netezza.log
>
> Dan
>
> On Sat, Mar 7, 2015 at 9:55 AM, Daniel Kasak <d.j.kasak.dk at gmail.com>
> wrote:
>
>> Actually I have to correct myself ... when selecting the entire column at
>> once, I *am* getting the correct data, at least as far as Perl is
>> concerned. It seems I have multiple bugs ... Gtk3 is not displaying unicode
>> stuff correctly. Anyway, the substring() bug remains.
>>
>> Dan
>>
>> On Sat, Mar 7, 2015 at 9:26 AM, Daniel Kasak <d.j.kasak.dk at gmail.com>
>> wrote:
>>
>>> 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 at 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/2420f511/attachment.html>


More information about the unixODBC-support mailing list