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

classic Classic list List threaded Threaded
11 messages Options
Reply | Threaded
Open this post in threaded view
|

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

Daniel Kasak
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

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

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

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

_______________________________________________
unixODBC-support mailing list
[hidden email]
http://mailman.unixodbc.org/mailman/listinfo/unixodbc-support
Reply | Threaded
Open this post in threaded view
|

Re: unicode issues :/ st_fetch/SQLFetch (long truncated DBI attribute LongTruncOk not set and/or LongReadLen too small)

Nick Gorham-2
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
>
> 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
>
> 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
>
> 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

Hi,

  Well it may be unixODBC, but looking at that log, the app is using
bound columns, and the driver manager is not involved with returning
data in that case.

Not wanting to point fingers, but I would suspect that its a DBD::ODBC
issue, are they the same version in both cases? are they both built the
same?

--
Nick
_______________________________________________
unixODBC-support mailing list
[hidden email]
http://mailman.unixodbc.org/mailman/listinfo/unixodbc-support
Reply | Threaded
Open this post in threaded view
|

Re: unicode issues :/ st_fetch/SQLFetch (long truncated DBI attribute LongTruncOk not set and/or LongReadLen too small)

Martin J. Evans
In reply to this post by Daniel Kasak
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
>
> 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
>
> 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
>
> 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
[hidden email]
http://mailman.unixodbc.org/mailman/listinfo/unixodbc-support
Reply | Threaded
Open this post in threaded view
|

Re: unicode issues :/ st_fetch/SQLFetch (long truncated DBI attribute LongTruncOk not set and/or LongReadLen too small)

Daniel Kasak
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 <[hidden email]> 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

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

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

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
[hidden email]
http://mailman.unixodbc.org/mailman/listinfo/unixodbc-support


_______________________________________________
unixODBC-support mailing list
[hidden email]
http://mailman.unixodbc.org/mailman/listinfo/unixodbc-support
Reply | Threaded
Open this post in threaded view
|

Re: unicode issues :/ st_fetch/SQLFetch (long truncated DBI attribute LongTruncOk not set and/or LongReadLen too small)

Daniel Kasak
I've installed DBD::ODBC from git just now and tested. Same error :/

On Fri, Mar 6, 2015 at 8:06 AM, Daniel Kasak <[hidden email]> 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 <[hidden email]> 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

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

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

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
[hidden email]
http://mailman.unixodbc.org/mailman/listinfo/unixodbc-support



_______________________________________________
unixODBC-support mailing list
[hidden email]
http://mailman.unixodbc.org/mailman/listinfo/unixodbc-support
Reply | Threaded
Open this post in threaded view
|

Re: unicode issues :/ st_fetch/SQLFetch (long truncated DBI attribute LongTruncOk not set and/or LongReadLen too small)

Martin J. Evans
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 <[hidden email] <mailto:[hidden email]>> 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 <[hidden email] <mailto:[hidden email]>> 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
>         [hidden email] <mailto:[hidden email]>
>         http://mailman.unixodbc.org/__mailman/listinfo/unixodbc-__support <http://mailman.unixodbc.org/mailman/listinfo/unixodbc-support>
>
>
>

_______________________________________________
unixODBC-support mailing list
[hidden email]
http://mailman.unixodbc.org/mailman/listinfo/unixodbc-support
Reply | Threaded
Open this post in threaded view
|

Re: unicode issues :/ st_fetch/SQLFetch (long truncated DBI attribute LongTruncOk not set and/or LongReadLen too small)

Daniel Kasak
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 <[hidden email]> 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 <[hidden email] <mailto:[hidden email]>> 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 <[hidden email] <mailto:[hidden email]>> 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@mailman.__unixodbc.org <mailto:[hidden email]>
        http://mailman.unixodbc.org/__mailman/listinfo/unixodbc-__support <http://mailman.unixodbc.org/mailman/listinfo/unixodbc-support>






_______________________________________________
unixODBC-support mailing list
[hidden email]
http://mailman.unixodbc.org/mailman/listinfo/unixodbc-support
Reply | Threaded
Open this post in threaded view
|

Re: unicode issues :/ st_fetch/SQLFetch (long truncated DBI attribute LongTruncOk not set and/or LongReadLen too small)

Daniel Kasak
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 <[hidden email]> wrote:
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 <[hidden email]> 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 <[hidden email] <mailto:[hidden email]>> 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 <[hidden email] <mailto:[hidden email]>> 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@mailman.__unixodbc.org <mailto:[hidden email]>
        http://mailman.unixodbc.org/__mailman/listinfo/unixodbc-__support <http://mailman.unixodbc.org/mailman/listinfo/unixodbc-support>







_______________________________________________
unixODBC-support mailing list
[hidden email]
http://mailman.unixodbc.org/mailman/listinfo/unixodbc-support
Reply | Threaded
Open this post in threaded view
|

Re: unicode issues :/ st_fetch/SQLFetch (long truncated DBI attribute LongTruncOk not set and/or LongReadLen too small)

Daniel Kasak
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?

Dan

On Sat, Mar 7, 2015 at 9:55 AM, Daniel Kasak <[hidden email]> 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 <[hidden email]> wrote:
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 <[hidden email]> 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 <[hidden email] <mailto:[hidden email]>> 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 <[hidden email] <mailto:[hidden email]>> 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@mailman.__unixodbc.org <mailto:[hidden email]>
        http://mailman.unixodbc.org/__mailman/listinfo/unixodbc-__support <http://mailman.unixodbc.org/mailman/listinfo/unixodbc-support>








_______________________________________________
unixODBC-support mailing list
[hidden email]
http://mailman.unixodbc.org/mailman/listinfo/unixodbc-support
Reply | Threaded
Open this post in threaded view
|

Re: unicode issues :/ st_fetch/SQLFetch (long truncated DBI attribute LongTruncOk not set and/or LongReadLen too small)

Daniel Kasak
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 <[hidden email]> 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?

Dan

On Sat, Mar 7, 2015 at 9:55 AM, Daniel Kasak <[hidden email]> 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 <[hidden email]> wrote:
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 <[hidden email]> 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 <[hidden email] <mailto:[hidden email]>> 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 <[hidden email] <mailto:[hidden email]>> 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@mailman.__unixodbc.org <mailto:[hidden email]>
        http://mailman.unixodbc.org/__mailman/listinfo/unixodbc-__support <http://mailman.unixodbc.org/mailman/listinfo/unixodbc-support>









_______________________________________________
unixODBC-support mailing list
[hidden email]
http://mailman.unixodbc.org/mailman/listinfo/unixodbc-support
Reply | Threaded
Open this post in threaded view
|

Re: unicode issues :/ st_fetch/SQLFetch (long truncated DBI attribute LongTruncOk not set and/or LongReadLen too small)

Martin J. Evans
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 <[hidden email]
> <mailto:[hidden email]>> 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 <[hidden email]
>     <mailto:[hidden email]>> 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
>         <[hidden email] <mailto:[hidden email]>> 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
>             <[hidden email] <mailto:[hidden email]>> 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
>                     <[hidden email]
>                     <mailto:[hidden email]>
>                     <mailto:[hidden email]
>                     <mailto:[hidden email]>__>> 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
>                     <[hidden email] <mailto:[hidden email]>
>                     <mailto:[hidden email]
>                     <mailto:[hidden email]>>> 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
>                              [hidden email]
>                     <http://unixodbc.org>
>                     <mailto:[hidden email]
>                     <mailto:[hidden email]>>
>                     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>>
>
>
>
>
>
>
>
>

_______________________________________________
unixODBC-support mailing list
[hidden email]
http://mailman.unixodbc.org/mailman/listinfo/unixodbc-support