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

Martin J. Evans bohica at ntlworld.com
Sat Mar 7 08:33:46 GMT 2015


On 07/03/2015 04:25, Daniel Kasak wrote:
> 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

Glad you've got it sorted out.

I perhaps should have got you to check you'd built with -u first but 
your original problem sounded like something I'd recently fixed.

Martin
-- 
Martin J. Evans
Wetherby, UK

> On Sat, Mar 7, 2015 at 1:18 PM, Daniel Kasak <d.j.kasak.dk at gmail.com
> <mailto: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
>     <mailto: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 <mailto: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 <mailto: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>
>                     <mailto: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>
>                     <mailto: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>
>                     <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>
>                     <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>
>                     <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.__uni__xodbc.org
>                     <http://unixodbc.org>
>                     <mailto: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>
>                     <http://mailman.unixodbc.org/__mailman/listinfo/unixodbc-__support
>                     <http://mailman.unixodbc.org/mailman/listinfo/unixodbc-support>>
>
>
>
>
>
>
>
>



More information about the unixODBC-support mailing list