Missing local characters when using FreeTDS driver via unixODBC (isql), but not when using directly (tsql)

classic Classic list List threaded Threaded
3 messages Options
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Missing local characters when using FreeTDS driver via unixODBC (isql), but not when using directly (tsql)

France
Hi guys.

On x86_64 CentOS 7 with:
unixODBC 2.3.1 - 11.el7 from base repo and
FreeTDS 0.95.81 - 1.el7 from epel repo
i have a strange problem of missing whole results when there are local characters involved. If i use FreeTDS directly it works as expected.

Here is a simple example when it works, using FreeTDS directly via tsql:

[root@cbi etc]# tsql -S pantheonCloud -U USER -P PASSWD -D Unicode_Test
locale is "C"
locale charset is "ANSI_X3.4-1968"
using default charset "UTF-8"
Setting Unicode_Test as default database in login packet
1> select * from tHE_Stock where acIdent='5030932116277'
2> go
acWarehouse acIdent anStock anValue anLastPrice anReserved adTimeChg adTimeIns anUserIns anUserChg anMinStock anOptStock anMaxStock anQId
CENSORED 5030932116277 47.000000 132.55590248133191 2.8203383506666366 0.000000 Nov 15 2016 10:16:27:580AM Sep 18 2015 01:20:32:970PM 00.000000 0.000000 0.000000 1175761
CENSORED ŠVAGELJ 5030932116277 0.000000 0 29.778435722326424 0.000000 Jul 28 2016 09:41:36:953AM Sep 29 2015 02:22:52:443PM 7 1 0.000000 0.000000 0.000000 1176315
Maloprodajno skladišče 5030932116277 19.000000 565.79851538461571 29.778869230769249 0.000000 Jul 28 2016 09:41:36:953AM Sep 18 2015 01:35:41:723PM 440 1 0.000000 0.000000 0.000000 1175770
Veleprodajno skladišče 5030932116277 25.000000 722.46290373117938 28.898516149247175 0.000000 Nov 15 2016 10:16:27:173AM Sep 18 2015 11:49:51:390AM 10.000000 0.000000 0.000000 1175743
(4 rows affected)
1> quit

The first field returns everything, including local characters. I have just censored some of the info manually.

Now if i try to do it via unixODBC and isql:

[root@cbi etc]# isql pantheonDirect USER PASSWD
+---------------------------------------+
| Connected! |
| |
| sql-statement |
| help [tablename] |
| quit |
| |
+---------------------------------------+
SQL> select * from tHE_Stock where acIdent='5030932116277'
+-------------------------------+-----------------+-------------------------+-------------------------+-------------------------+-------------------------+------------------------+------------------------+------------+------------+-------------------------+-------------------------+-------------------------+------------+
| acWarehouse | acIdent | anStock | anValue | anLastPrice | anReserved | adTimeChg | adTimeIns | anUserIns | anUserChg | anMinStock | anOptStock | anMaxStock | anQId |
+-------------------------------+-----------------+-------------------------+-------------------------+-------------------------+-------------------------+------------------------+------------------------+------------+------------+-------------------------+-------------------------+-------------------------+------------+
| CENSORED | 5030932116277 | 47 | 132.55590248133191 | 2.8203383506666366 | 0 | 2016-11-15 10:16:27.580| 2015-09-18 13:20:32.970| 0 | 0 | 0 | 0 | 0 | 1175761 |
| | 5030932116277 | 0 | 0 | 29.778435722326424 | 0 | 2016-07-28 09:41:36.953| 2015-09-29 14:22:52.443| 7 | 1 | 0 | 0 | 0 | 1176315 |
| | 5030932116277 | 19 | 565.79851538461571 | 29.778869230769249 | 0 | 2016-07-28 09:41:36.953| 2015-09-18 13:35:41.723| 440 | 1 | 0 | 0 | 0 | 1175770 |
| | 5030932116277 | 25 | 722.46290373117938 | 28.898516149247175 | 0 | 2016-11-15 10:16:27.173| 2015-09-18 11:49:51.390| 18 | 0 | 0 | 0 | 0 | 1175743 |
+-------------------------------+-----------------+-------------------------+-------------------------+-------------------------+-------------------------+------------------------+------------------------+------------+------------+-------------------------+-------------------------+-------------------------+------------+
SQLRowCount returns 4
4 rows fetched
SQL> quit

One can observe, that the (first) fields, who contain local characters are completely empty. The same happens when I use the driver via PHP PDO ODBC.
What can I do in order to get actual data back? Why would local characters disappear, as soon as we send the data via unixODBC? is this a bug?

Here is how we define working resource pantheonCloud in freetds.conf:
[pantheonCloud]
host = sahosting.domain
port = 4XXX1
tds version = 7.0
client charset = UTF-8
Database = Unicode_Test

Here is how we define driver for unixODBC in odbcinst.ini:
[FreeTDS]
Description=FreeTDS v0.59 from EPL
Driver=/usr/lib64/libtdsodbc.so.0.0.0
UsageCount=1

Here is is how we define resource PantheonDirect in odbc.ini:
[PantheonDirect]
Driver = FreeTDS
Description = PantheonTDS
Server = sahosting.domain
Port = 4XXX1
Database = Unicode_Test
tds version = 7.0
client charset = UTF-8

I have tried changing tds version, as well as setting server charset to cp1250 in odbc.ini, but it doesn’t help.

Regards,
France Presern
_______________________________________________
unixODBC-support mailing list
[hidden email]
http://mailman.unixodbc.org/mailman/listinfo/unixodbc-support
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Missing local characters when using FreeTDS driver via unixODBC (isql), but not when using directly (tsql)

Nick Gorham-2
On 18/11/16 16:01, France wrote:
> Hi guys.
>
> On x86_64 CentOS 7 with:
> unixODBC 2.3.1 - 11.el7 from base repo and
> FreeTDS 0.95.81 - 1.el7 from epel repo
> i have a strange problem of missing whole results when there are local characters involved. If i use FreeTDS directly it works as expected.
>
> Here is a simple example when it works, using FreeTDS directly via tsql:

Check your LANG. isql is just a test app, it gets cracaters as
SQL_C_CHAR and uses printf to display, it may be that the locale is
causing printf to fail to display unicode.

But I will repeat that isql is just a test application. If you need
data, try coding or using Perl so you have direct control over whet you get.

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

Re: Missing local characters when using FreeTDS driver via unixODBC (isql), but not when using directly (tsql)

France

On 18 Nov 2016, at 17:20, Nick Gorham <[hidden email]> wrote:

> On 18/11/16 16:01, France wrote:
>> Hi guys.
>>
>> On x86_64 CentOS 7 with:
>> unixODBC 2.3.1 - 11.el7 from base repo and
>> FreeTDS 0.95.81 - 1.el7 from epel repo
>> i have a strange problem of missing whole results when there are local characters involved. If i use FreeTDS directly it works as expected.
>>
>> Here is a simple example when it works, using FreeTDS directly via tsql:
>
> Check your LANG. isql is just a test app, it gets cracaters as SQL_C_CHAR and uses printf to display, it may be that the locale is causing printf to fail to display unicode.
>
> But I will repeat that isql is just a test application. If you need data, try coding or using Perl so you have direct control over whet you get.
>
> --
> NIck
> _______________________________________________
> unixODBC-support mailing list
> [hidden email]
> http://mailman.unixodbc.org/mailman/listinfo/unixodbc-support

Thank you for your fast response. I will set $LANG (and other relevant enc settings) to UTF-8 on monday when I get back into the office.
I have used isql just for this example. Developer get’s the exact same symptoms when using PHP. I guess we will need to set the equivalent of $LANG there also.
All hints are welcome for monday. :-)

Regards,
F.

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