[freetds] osql and isql -- how to test ODBC config?

[Tony Esposito]

Using FreeTDS with unixODBC on Linux.


(1) tsql works fine.

(2) but osql gets issue below:

looking up hostname for ip address 207.235.236.120
osql: warning: no DNS hostname found for "207.235.236.120"

in other blogs I see it mentioned that this is not an 'error' to be concerned with. And in fact it is shown as a' warning', not an 'error'.
But how does one check out the ODBC configuration? The osql tool stops because of this 'warning' so I never get connected and therefore never verify my ODBC setup.
And isql fails with:

[S1000][unixODBC][FreeTDS][SQL Server]Unable to connect to data source
[01000][unixODBC][FreeTDS][SQL Server]Unknown host machine name.
[ISQL]ERROR: Could not SQLConnect

Please advise.

Tony

[James K. Lowden]

On Tue, 11 Sep 2012 22:18:39 +0000

Post by Tony Esposito
osql: warning: no DNS hostname found for "207.235.236.120"

...

Post by Tony Esposito
The osql tool stops because of this 'warning' so I never get
connected and therefore never verify my ODBC setup.

The script shouldn't stop there because of that. Check the version
you're using; revision 1.12 doesn't exit after producing that
message on line 342. In fact, the only check remaining after that is
that the hostname has an address. If so, it prints what it found out
and starts isql.

Perhaps something in your environment causes the shell to
terminate the script when host(1) returns nonzero status? I'd be
interested it hear what you find out.

--jkl

[Tony Esposito]

Here's what I found out:

(1) tsql connects and returns data fine.
(2) fisql connects and returns data fine.

So it appears the FreeTDS client is setup correctly but...

isql connects yet returns no data when doing a SELECT (see error below):

isql -v txeis dba sql
+---------------------------------------+
| Connected! |
| |
| sql-statement |
| help [tablename] |
| quit |
| |
+---------------------------------------+
SQL> select * from rsccc.sr_stu_demo
SQLRowCount returns -1
SQL>

Yet isql will return system table information via the 'help' command (see SQL below):

+---------------------------------------+
| Connected! |
| |
| sql-statement |
| help [tablename] |
| quit |
| |
+---------------------------------------+
SQL> help rsccc.sr_stu_demo
+---------------------------------------------------------------------------------------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------+--------+---------------------------------------------------------------------------------------------------------------------------------+------------+------------+-------+-------+---------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------+-------------+
| TABLE_QUALIFIER | TABLE_OWNER | TABLE_NAME | COLUMN_NAME | TYPE_ID| TYPE_NAME | PRECISION | LENGTH | SCALE | RADIX | NULLABLE| REMARKS | SS_DOMAIN_ID| COLUMN_ID |
+---------------------------------------------------------------------------------------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------+--------+---------------------------------------------------------------------------------------------------------------------------------+------------+------------+-------+-------+---------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------+-------------+
+---------------------------------------------------------------------------------------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------+--------+---------------------------------------------------------------------------------------------------------------------------------+------------+------------+-------+-------+---------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Please advise.

-----Original Message-----
From: freetds-bounces at lists.ibiblio.org [mailto:freetds-bounces at lists.ibiblio.org] On Behalf Of James K. Lowden
Sent: Thursday, September 13, 2012 1:44 AM
To: freetds at lists.ibiblio.org
Subject: Re: [freetds] osql and isql -- how to test ODBC config?

On Tue, 11 Sep 2012 22:18:39 +0000

Post by Tony Esposito
osql: warning: no DNS hostname found for "207.235.236.120"

...

Post by Tony Esposito
The osql tool stops because of this 'warning' so I never get connected
and therefore never verify my ODBC setup.

The script shouldn't stop there because of that. Check the version you're using; revision 1.12 doesn't exit after producing that message on line 342. In fact, the only check remaining after that is that the hostname has an address. If so, it prints what it found out and starts isql.

Perhaps something in your environment causes the shell to terminate the script when host(1) returns nonzero status? I'd be interested it hear what you find out.

--jkl
_______________________________________________
FreeTDS mailing list
FreeTDS at lists.ibiblio.org
http://lists.ibiblio.org/mailman/listinfo/freetds


-----
No virus found in this message.
Checked by AVG - www.avg.com
Version: 2012.0.2221 / Virus Database: 2437/5266 - Release Date: 09/13/12

[James K. Lowden]

On Thu, 13 Sep 2012 14:35:03 +0000

Post by Tony Esposito
(1) tsql connects and returns data fine.
(2) fisql connects and returns data fine.
So it appears the FreeTDS client is setup correctly but...

One cannot generalize from tsql to odbc because ODBC requires
additional setup. "tsql servername" and "isql servername" may use
different servers and even different copies of FreeTDS.

Post by Tony Esposito
isql -v txeis dba sql
+---------------------------------------+
| Connected! |
| |
| sql-statement |
| help [tablename] |
| quit |
| |
+---------------------------------------+
SQL> select * from rsccc.sr_stu_demo
SQLRowCount returns -1
SQL>

No, the reply you quoted showed column headers only, no data.

Post by Tony Esposito
Please advise.

Your freetds.conf and odbc.ini look correct to me. I think isql is
swallowing an error from the server. I would try the same query using
bsqldb using TDSDUMP viz:

$ TDSDUMP=stdout bsqldb -S txeis -U dba -P sql

HTH.

--jkl

P.S. Please don't top-post. Inline replies such as I have used here
make the thread of the conversation easier for others to follow.

[Tony Esposito]

-----Original Message-----
From: freetds-bounces at lists.ibiblio.org [mailto:freetds-bounces at lists.ibiblio.org] On Behalf Of James K. Lowden
Sent: Saturday, September 15, 2012 10:23 AM
To: freetds at lists.ibiblio.org
Subject: Re: [freetds] osql and isql -- how to test ODBC config?

On Thu, 13 Sep 2012 14:35:03 +0000

Post by Tony Esposito
(1) tsql connects and returns data fine.
(2) fisql connects and returns data fine.
So it appears the FreeTDS client is setup correctly but...

One cannot generalize from tsql to odbc because ODBC requires additional setup. "tsql servername" and "isql servername" may use different servers and even different copies of FreeTDS.

Post by Tony Esposito
isql -v txeis dba sql
+---------------------------------------+
| Connected! |
| |
| sql-statement |
| help [tablename] |
| quit |
| |
+---------------------------------------+
SQL> select * from rsccc.sr_stu_demo
SQLRowCount returns -1
SQL>

No, the reply you quoted showed column headers only, no data.

Post by Tony Esposito
Please advise.

$ TDSDUMP=stdout bsqldb -S txeis -U dba -P sql

Tried the following from the Linux shell prompt: bsqldb -S txeis -U dba -P sql
And got the following error message:
bsqldb: Msg 20012, Level 2
Server name not found in configuration files

bsqldb: Msg 20013, Level 2
Unknown host machine name

HTH.
--jkl
P.S. Please don't top-post. Inline replies such as I have used here make the thread of the conversation easier for others to follow.
_______________________________________________
FreeTDS mailing list
FreeTDS at lists.ibiblio.org
http://lists.ibiblio.org/mailman/listinfo/freetds

Please advise ... I am not familiar with 'bsqldb'. Can I just run it from bash shell prompt?
Thank you for your time and effort. :-)

Tony Esposito
ETL Developer
Information Services Programming
Region 10 Education Service Center
400 E Spring Valley Rd
Richardson, TX 75081
(Ph) 972-348-1284
(Fax) 972-348-1285
Reveal Data Analytics

[Tony Esposito]

Sorry - felt I had to top-post the TDSDUMP and TDSDUMPCONFIG file contents. This is an addendum to the previous email.

TDSDUMP:
log.c:196:Starting log file for FreeTDS 0.91
on 2012-09-15 22:58:37 with debug flags 0x4fff.
config.c:714:Setting 'dump_file' to '/tmp/freetds.log' from $TDSDUMP.
iconv.c:330:tds_iconv_open(0x1122050, UTF-8)
iconv.c:187:local name for ISO-8859-1 is ISO-8859-1
iconv.c:187:local name for UTF-8 is UTF-8
iconv.c:187:local name for UCS-2LE is UCS-2LE
iconv.c:187:local name for UCS-2BE is UCS-2BE
iconv.c:349:setting up conversions for client charset "UTF-8"
iconv.c:351:preparing iconv for "UTF-8" <-> "UCS-2LE" conversion
iconv.c:391:preparing iconv for "ISO-8859-1" <-> "ISO-8859-1" conversion
iconv.c:394:tds_iconv_open: done
util.c:331:tdserror(0x1121010, 0x1122050, 20013, 0)
dblib.c:7929:dbperror(0x1121510, 20013, 0)
dblib.c:7981:20013: "Unknown host machine name"
dblib.c:8002:"Unknown host machine name", client returns 2 (INT_CANCEL)
util.c:361:tdserror: client library returned TDS_INT_CANCEL(2)
util.c:384:tdserror: returning TDS_INT_CANCEL(2)
login.c:423:IP address pointer is empty
login.c:425:Server txeis not found!
dblib.c:1443:dbclose(0x1121510)
dblib.c:258:dblib_del_connection(0x2b4413479fc0, 0x1122050)
mem.c:615:tds_free_all_results()
dblib.c:305:dblib_release_tds_ctx(1)
dblib.c:5882:dbfreebuf(0x1121510)

TDSDUMPCONFIG
log.c:196:Starting log file for FreeTDS 0.91
on 2012-09-15 23:15:09 with debug flags 0x4fff.
config.c:185:Getting connection information for [txeis].
config.c:189:Attempting to read conf files.
config.c:354:... $FREETDSCONF not set. Trying $FREETDS/etc.
config.c:367:... $FREETDS not set. Trying $HOME.
config.c:297:Found conf file '/root/.freetds.conf' (.freetds.conf).
config.c:483:Looking for section global.
config.c:540: Found section db246111.
config.c:554: Reached EOF
config.c:483:Looking for section txeis.
config.c:540: Found section db246111.
config.c:554: Reached EOF
config.c:303:[txeis] not found.
config.c:297:Found conf file '/usr/local/freetds/etc/freetds.conf' (default).
config.c:483:Looking for section global.
config.c:540: Found section db246111.
config.c:554: Reached EOF
config.c:483:Looking for section txeis.
config.c:540: Found section db246111.
config.c:554: Reached EOF
config.c:303:[txeis] not found.
config.c:208:Failed in reading conf file. Trying interface files.
config.c:1040:Looking for server txeis....
config.c:1057:Looking for server in /root/.interfaces.
config.c:931:Searching interfaces file //root/.interfaces.
config.c:956:Couldn't open /root/.interfaces.
config.c:1077:Looking for server in /usr/local/freetds/interfaces.
config.c:931:Searching interfaces file /usr/local/freetds/interfaces.
config.c:956:Couldn't open /usr/local/freetds/interfaces.
config.c:1111:Setting 'ip_port' to 4000 as a guess.
config.c:210:Failed to find [txeis] in configuration files; trying 'SYBASE' instead.
util.c:331:tdserror(0x1cb48010, 0x1cb49050, 20012, 0)
dblib.c:7929:dbperror(0x1cb48510, 20012, 0)
dblib.c:7981:20012: "Server name not found in configuration files"
dblib.c:8002:"Server name not found in configuration files", client returns 2 (INT_CANCEL)
util.c:361:tdserror: client library returned TDS_INT_CANCEL(2)
util.c:384:tdserror: returning TDS_INT_CANCEL(2)
config.c:714:Setting 'dump_file' to '/tmp/freetds.log' from $TDSDUMP.
config.c:224:Final connection parameters:
config.c:225: server_name = txeis
config.c:226: server_host_name =
config.c:227: ip_addr =
config.c:228: instance_name =
config.c:229: port = 4000
config.c:230: major_version = 5
config.c:231: minor_version = 0
config.c:232: block_size = 0
config.c:233: language = us_english
config.c:234: server_charset = iso_1
config.c:235: connect_timeout = 0
config.c:236: client_host_name = empower01
config.c:237: client_charset = UTF-8
config.c:238: app_name = bsqldb
config.c:239: user_name = dba
config.c:242: library = DB-Library
config.c:243: bulk_copy = 0
config.c:244: suppress_language = 0
config.c:245: encrypt level = 0
config.c:246: query_timeout = 0
config.c:249: database =
config.c:250: dump_file = /tmp/freetds.log
config.c:251: debug_flags = 0
config.c:252: text_size = 0
config.c:253: broken_dates = 0
config.c:254: emul_little_endian = 0
config.c:255: server_realm_name =

Not convinced the odbc.ini file is setup correctly. When changing [ ] reference to DB24611 in odbc.ini, bsqldb hangs but at least it finds the IP address.
TDSDUMPCONFIG file ( below ) looks better except I don't see a database specified.

log.c:196:Starting log file for FreeTDS 0.91
on 2012-09-15 23:19:28 with debug flags 0x4fff.
config.c:185:Getting connection information for [DB246111].
config.c:189:Attempting to read conf files.
config.c:354:... $FREETDSCONF not set. Trying $FREETDS/etc.
config.c:367:... $FREETDS not set. Trying $HOME.
config.c:297:Found conf file '/root/.freetds.conf' (.freetds.conf).
config.c:483:Looking for section global.
config.c:540: Found section db246111.
config.c:554: Reached EOF
config.c:483:Looking for section DB246111.
config.c:540: Found section db246111.
config.c:543:Got a match.
config.c:565: host = '207.235.236.120'
config.c:595:Found host entry 207.235.236.120.
config.c:599:IP addr is 207.235.236.120.
config.c:565: port = '2638'
config.c:565: tds version = '5.0'
config.c:788:Setting tds version to 5.0 (0x500) from $TDSVER.
config.c:565: dump file = '/usr/freetdsdump.log'
config.c:565: client charset = 'UTF-8'
config.c:613:tds_parse_conf_section: client charset is UTF-8.
config.c:554: Reached EOF
config.c:301:Success: [DB246111] defined in /root/.freetds.conf.
config.c:714:Setting 'dump_file' to '/tmp/freetds.log' from $TDSDUMP.
config.c:224:Final connection parameters:
config.c:225: server_name = DB246111
config.c:226: server_host_name = 207.235.236.120
config.c:227: ip_addr = 207.235.236.120
config.c:228: instance_name =
config.c:229: port = 2638
config.c:230: major_version = 5
config.c:231: minor_version = 0
config.c:232: block_size = 0
config.c:233: language = us_english
config.c:234: server_charset = iso_1
config.c:235: connect_timeout = 0
config.c:236: client_host_name = empower01
config.c:237: client_charset = UTF-8
config.c:238: app_name = bsqldb
config.c:239: user_name = dba
config.c:242: library = DB-Library
config.c:243: bulk_copy = 0
config.c:244: suppress_language = 0
config.c:245: encrypt level = 0
config.c:246: query_timeout = 0
config.c:249: database =
config.c:250: dump_file = /tmp/freetds.log
config.c:251: debug_flags = 0
config.c:252: text_size = 0
config.c:253: broken_dates = 0
config.c:254: emul_little_endian = 0
config.c:255: server_realm_name =
'
Please advise and thanks again in advance. FYI ( In Sybase (not FreeTDS) lingo, for my environment, the Sybase server is 'txeis' and Sybase database is 'DB246111' that I am trying to connect to. It gets confusing to me when the name 'server' or 'servername' is used by different products to mean different things but not much I can do about it :-) )


Tony Esposito
ETL Developer
Information Services Programming
Region 10 Education Service Center
400 E Spring Valley Rd
Richardson, TX 75081
(Ph) 972-348-1284
(Fax) 972-348-1285
Reveal Data Analytics



-----Original Message-----
From: Tony Esposito
Sent: Saturday, September 15, 2012 10:55 PM
To: FreeTDS Development Group
Cc: Tony Esposito
Subject: RE: [freetds] osql and isql -- how to test ODBC config?



-----Original Message-----
From: freetds-bounces at lists.ibiblio.org [mailto:freetds-bounces at lists.ibiblio.org] On Behalf Of James K. Lowden
Sent: Saturday, September 15, 2012 10:23 AM
To: freetds at lists.ibiblio.org
Subject: Re: [freetds] osql and isql -- how to test ODBC config?

On Thu, 13 Sep 2012 14:35:03 +0000

Post by Tony Esposito
(1) tsql connects and returns data fine.
(2) fisql connects and returns data fine.
So it appears the FreeTDS client is setup correctly but...

One cannot generalize from tsql to odbc because ODBC requires additional setup. "tsql servername" and "isql servername" may use different servers and even different copies of FreeTDS.

Post by Tony Esposito
isql -v txeis dba sql
+---------------------------------------+
| Connected! |
| |
| sql-statement |
| help [tablename] |
| quit |
| |
+---------------------------------------+
SQL> select * from rsccc.sr_stu_demo
SQLRowCount returns -1
SQL>

No, the reply you quoted showed column headers only, no data.

Post by Tony Esposito
Please advise.

$ TDSDUMP=stdout bsqldb -S txeis -U dba -P sql

Tried the following from the Linux shell prompt: bsqldb -S txeis -U dba -P sql And got the following error message:
bsqldb: Msg 20012, Level 2
Server name not found in configuration files

bsqldb: Msg 20013, Level 2
Unknown host machine name

HTH.
--jkl
P.S. Please don't top-post. Inline replies such as I have used here make the thread of the conversation easier for others to follow.
_______________________________________________
FreeTDS mailing list
FreeTDS at lists.ibiblio.org
http://lists.ibiblio.org/mailman/listinfo/freetds

Please advise ... I am not familiar with 'bsqldb'. Can I just run it from bash shell prompt?
Thank you for your time and effort. :-)

Tony Esposito
ETL Developer
Information Services Programming
Region 10 Education Service Center
400 E Spring Valley Rd
Richardson, TX 75081
(Ph) 972-348-1284
(Fax) 972-348-1285
Reveal Data Analytics




-----
No virus found in this message.
Checked by AVG - www.avg.com
Version: 2012.0.2221 / Virus Database: 2437/5270 - Release Date: 09/15/12

[Tony Esposito]

And my 'help st_read_lvl' does return data.
If gives the structure of the table.

isql -v txeis dba sql
+---------------------------------------+
| Connected! |
| |
| sql-statement |
| help [tablename] |
| quit |
| |
+---------------------------------------+
SQL> help ST_READ_LVL
+---------------------------------------------------------------------------------------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------+--------+---------------------------------------------------------------------------------------------------------------------------------+------------+------------+-------+-------+---------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------+-------------+
| TABLE_QUALIFIER | TABLE_OWNER | TABLE_NAME | COLUMN_NAME | TYPE_ID| TYPE_NAME | PRECISION | LENGTH | SCALE | RADIX | NULLABLE| REMARKS | SS_DOMAIN_ID| COLUMN_ID |
+---------------------------------------------------------------------------------------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------+--------+---------------------------------------------------------------------------------------------------------------------------------+------------+------------+-------+-------+---------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------+-------------+
| DB246111 | rsccc | ST_READ_LVL | SCH_YR | 1 | char | 4 | 4 | 0 | | 0 | | 47 | 1 |
| DB246111 | rsccc | ST_READ_LVL | READ_CD | 1 | char | 1 | 1 | 0 | | 0 | | 47 | 2 |
| DB246111 | rsccc | ST_READ_LVL |
DESCRIPTION | 1 | char | 20 | 20 | 0 | | 0 | | 47 | 3 |
+---------------------------------------------------------------------------------------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------+--------+---------------------------------------------------------------------------------------------------------------------------------+------------+------------+-------+-------+---------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------+-------------+
SQLRowCount returns 3
3 rows fetched
SQL>

:-)

-----Original Message-----
From: Tony Esposito
Sent: Saturday, September 15, 2012 11:24 PM
To: FreeTDS Development Group
Cc: Tony Esposito
Subject: RE: [freetds] osql and isql -- how to test ODBC config?

Sorry - felt I had to top-post the TDSDUMP and TDSDUMPCONFIG file contents. This is an addendum to the previous email.

TDSDUMP:
log.c:196:Starting log file for FreeTDS 0.91
on 2012-09-15 22:58:37 with debug flags 0x4fff.
config.c:714:Setting 'dump_file' to '/tmp/freetds.log' from $TDSDUMP.
iconv.c:330:tds_iconv_open(0x1122050, UTF-8) iconv.c:187:local name for ISO-8859-1 is ISO-8859-1 iconv.c:187:local name for UTF-8 is UTF-8 iconv.c:187:local name for UCS-2LE is UCS-2LE iconv.c:187:local name for UCS-2BE is UCS-2BE iconv.c:349:setting up conversions for client charset "UTF-8"
iconv.c:351:preparing iconv for "UTF-8" <-> "UCS-2LE" conversion iconv.c:391:preparing iconv for "ISO-8859-1" <-> "ISO-8859-1" conversion
iconv.c:394:tds_iconv_open: done
util.c:331:tdserror(0x1121010, 0x1122050, 20013, 0) dblib.c:7929:dbperror(0x1121510, 20013, 0)
dblib.c:7981:20013: "Unknown host machine name"
dblib.c:8002:"Unknown host machine name", client returns 2 (INT_CANCEL)
util.c:361:tdserror: client library returned TDS_INT_CANCEL(2)
util.c:384:tdserror: returning TDS_INT_CANCEL(2) login.c:423:IP address pointer is empty login.c:425:Server txeis not found!
dblib.c:1443:dbclose(0x1121510)
dblib.c:258:dblib_del_connection(0x2b4413479fc0, 0x1122050)
mem.c:615:tds_free_all_results()
dblib.c:305:dblib_release_tds_ctx(1)
dblib.c:5882:dbfreebuf(0x1121510)

TDSDUMPCONFIG
log.c:196:Starting log file for FreeTDS 0.91
on 2012-09-15 23:15:09 with debug flags 0x4fff.
config.c:185:Getting connection information for [txeis].
config.c:189:Attempting to read conf files.
config.c:354:... $FREETDSCONF not set. Trying $FREETDS/etc.
config.c:367:... $FREETDS not set. Trying $HOME.
config.c:297:Found conf file '/root/.freetds.conf' (.freetds.conf).
config.c:483:Looking for section global.
config.c:540: Found section db246111.
config.c:554: Reached EOF
config.c:483:Looking for section txeis.
config.c:540: Found section db246111.
config.c:554: Reached EOF
config.c:303:[txeis] not found.
config.c:297:Found conf file '/usr/local/freetds/etc/freetds.conf' (default).
config.c:483:Looking for section global.
config.c:540: Found section db246111.
config.c:554: Reached EOF
config.c:483:Looking for section txeis.
config.c:540: Found section db246111.
config.c:554: Reached EOF
config.c:303:[txeis] not found.
config.c:208:Failed in reading conf file. Trying interface files.
config.c:1040:Looking for server txeis....
config.c:1057:Looking for server in /root/.interfaces.
config.c:931:Searching interfaces file //root/.interfaces.
config.c:956:Couldn't open /root/.interfaces.
config.c:1077:Looking for server in /usr/local/freetds/interfaces.
config.c:931:Searching interfaces file /usr/local/freetds/interfaces.
config.c:956:Couldn't open /usr/local/freetds/interfaces.
config.c:1111:Setting 'ip_port' to 4000 as a guess.
config.c:210:Failed to find [txeis] in configuration files; trying 'SYBASE' instead.
util.c:331:tdserror(0x1cb48010, 0x1cb49050, 20012, 0) dblib.c:7929:dbperror(0x1cb48510, 20012, 0)
dblib.c:7981:20012: "Server name not found in configuration files"
dblib.c:8002:"Server name not found in configuration files", client returns 2 (INT_CANCEL)
util.c:361:tdserror: client library returned TDS_INT_CANCEL(2)
util.c:384:tdserror: returning TDS_INT_CANCEL(2) config.c:714:Setting 'dump_file' to '/tmp/freetds.log' from $TDSDUMP.
config.c:224:Final connection parameters:
config.c:225: server_name = txeis
config.c:226: server_host_name =
config.c:227: ip_addr =
config.c:228: instance_name =
config.c:229: port = 4000
config.c:230: major_version = 5
config.c:231: minor_version = 0
config.c:232: block_size = 0
config.c:233: language = us_english
config.c:234: server_charset = iso_1
config.c:235: connect_timeout = 0
config.c:236: client_host_name = empower01
config.c:237: client_charset = UTF-8
config.c:238: app_name = bsqldb
config.c:239: user_name = dba
config.c:242: library = DB-Library
config.c:243: bulk_copy = 0
config.c:244: suppress_language = 0
config.c:245: encrypt level = 0
config.c:246: query_timeout = 0
config.c:249: database =
config.c:250: dump_file = /tmp/freetds.log
config.c:251: debug_flags = 0
config.c:252: text_size = 0
config.c:253: broken_dates = 0
config.c:254: emul_little_endian = 0
config.c:255: server_realm_name =

Not convinced the odbc.ini file is setup correctly. When changing [ ] reference to DB24611 in odbc.ini, bsqldb hangs but at least it finds the IP address.
TDSDUMPCONFIG file ( below ) looks better except I don't see a database specified.

log.c:196:Starting log file for FreeTDS 0.91
on 2012-09-15 23:19:28 with debug flags 0x4fff.
config.c:185:Getting connection information for [DB246111].
config.c:189:Attempting to read conf files.
config.c:354:... $FREETDSCONF not set. Trying $FREETDS/etc.
config.c:367:... $FREETDS not set. Trying $HOME.
config.c:297:Found conf file '/root/.freetds.conf' (.freetds.conf).
config.c:483:Looking for section global.
config.c:540: Found section db246111.
config.c:554: Reached EOF
config.c:483:Looking for section DB246111.
config.c:540: Found section db246111.
config.c:543:Got a match.
config.c:565: host = '207.235.236.120'
config.c:595:Found host entry 207.235.236.120.
config.c:599:IP addr is 207.235.236.120.
config.c:565: port = '2638'
config.c:565: tds version = '5.0'
config.c:788:Setting tds version to 5.0 (0x500) from $TDSVER.
config.c:565: dump file = '/usr/freetdsdump.log'
config.c:565: client charset = 'UTF-8'
config.c:613:tds_parse_conf_section: client charset is UTF-8.
config.c:554: Reached EOF
config.c:301:Success: [DB246111] defined in /root/.freetds.conf.
config.c:714:Setting 'dump_file' to '/tmp/freetds.log' from $TDSDUMP.
config.c:224:Final connection parameters:
config.c:225: server_name = DB246111
config.c:226: server_host_name = 207.235.236.120
config.c:227: ip_addr = 207.235.236.120
config.c:228: instance_name =
config.c:229: port = 2638
config.c:230: major_version = 5
config.c:231: minor_version = 0
config.c:232: block_size = 0
config.c:233: language = us_english
config.c:234: server_charset = iso_1
config.c:235: connect_timeout = 0
config.c:236: client_host_name = empower01
config.c:237: client_charset = UTF-8
config.c:238: app_name = bsqldb
config.c:239: user_name = dba
config.c:242: library = DB-Library
config.c:243: bulk_copy = 0
config.c:244: suppress_language = 0
config.c:245: encrypt level = 0
config.c:246: query_timeout = 0
config.c:249: database =
config.c:250: dump_file = /tmp/freetds.log
config.c:251: debug_flags = 0
config.c:252: text_size = 0
config.c:253: broken_dates = 0
config.c:254: emul_little_endian = 0
config.c:255: server_realm_name =
'
Please advise and thanks again in advance. FYI ( In Sybase (not FreeTDS) lingo, for my environment, the Sybase server is 'txeis' and Sybase database is 'DB246111' that I am trying to connect to. It gets confusing to me when the name 'server' or 'servername' is used by different products to mean different things but not much I can do about it :-) )


Tony Esposito
ETL Developer
Information Services Programming
Region 10 Education Service Center
400 E Spring Valley Rd
Richardson, TX 75081
(Ph) 972-348-1284
(Fax) 972-348-1285
Reveal Data Analytics



-----Original Message-----
From: Tony Esposito
Sent: Saturday, September 15, 2012 10:55 PM
To: FreeTDS Development Group
Cc: Tony Esposito
Subject: RE: [freetds] osql and isql -- how to test ODBC config?



-----Original Message-----
From: freetds-bounces at lists.ibiblio.org [mailto:freetds-bounces at lists.ibiblio.org] On Behalf Of James K. Lowden
Sent: Saturday, September 15, 2012 10:23 AM
To: freetds at lists.ibiblio.org
Subject: Re: [freetds] osql and isql -- how to test ODBC config?

On Thu, 13 Sep 2012 14:35:03 +0000

Post by Tony Esposito
(1) tsql connects and returns data fine.
(2) fisql connects and returns data fine.
So it appears the FreeTDS client is setup correctly but...

One cannot generalize from tsql to odbc because ODBC requires additional setup. "tsql servername" and "isql servername" may use different servers and even different copies of FreeTDS.

Post by Tony Esposito
isql -v txeis dba sql
+---------------------------------------+
| Connected! |
| |
| sql-statement |
| help [tablename] |
| quit |
| |
+---------------------------------------+
SQL> select * from rsccc.sr_stu_demo
SQLRowCount returns -1
SQL>

No, the reply you quoted showed column headers only, no data.

Post by Tony Esposito
Please advise.

$ TDSDUMP=stdout bsqldb -S txeis -U dba -P sql

Tried the following from the Linux shell prompt: bsqldb -S txeis -U dba -P sql And got the following error message:
bsqldb: Msg 20012, Level 2
Server name not found in configuration files

bsqldb: Msg 20013, Level 2
Unknown host machine name

HTH.
--jkl
P.S. Please don't top-post. Inline replies such as I have used here make the thread of the conversation easier for others to follow.
_______________________________________________
FreeTDS mailing list
FreeTDS at lists.ibiblio.org
http://lists.ibiblio.org/mailman/listinfo/freetds

Please advise ... I am not familiar with 'bsqldb'. Can I just run it from bash shell prompt?
Thank you for your time and effort. :-)

Tony Esposito
ETL Developer
Information Services Programming
Region 10 Education Service Center
400 E Spring Valley Rd
Richardson, TX 75081
(Ph) 972-348-1284
(Fax) 972-348-1285
Reveal Data Analytics




-----
No virus found in this message.
Checked by AVG - www.avg.com
Version: 2012.0.2221 / Virus Database: 2437/5270 - Release Date: 09/15/12

-----
No virus found in this message.
Checked by AVG - www.avg.com
Version: 2012.0.2221 / Virus Database: 2437/5270 - Release Date: 09/15/12

[James K. Lowden]

On Sun, 16 Sep 2012 03:55:16 +0000

Post by Tony Esposito
I am not familiar with 'bsqldb'.
Can I just run it from bash shell prompt?

I think you know the answer to that now, but for the record bsqldb is
documented as a man page. "man bsqldb" is your friend. :-)

I misled you, though. I meant to suggest you use bsqlodbc (similarly
documented). I hope it will do a better job of error reporting.

Because you're using Sybase Anywhere I'll draw your attention to a
footnote in the UG:
http://www.freetds.org/userguide/freetdsconf.htm#FTN.AEN942.

See also the "ASA database" in freetds.conf. It might be something in
that neighborhood. I suspect so only because I see nothing wrong with
your odbc.ini and because osql didn't report a problem.

Post by Tony Esposito
bsqldb -S txeis -U dba -P sql
bsqldb: Msg 20012, Level 2 Server name not found in configuration
files
bsqldb: Msg 20013, Level 2
Unknown host machine name

Msg 20012 comes from FreeTDS. It means FreeTDS couldn't convert the
provided name to an ip addreess. The relevant part of the TDSDUMP is

Post by Tony Esposito
dblib.c:7929:dbperror(0x1121510, 20013, 0)
dblib.c:7981:20013: "Unknown host machine name"
dblib.c:8002:"Unknown host machine name", client returns 2
(INT_CANCEL) util.c:361:tdserror: client library returned
TDS_INT_CANCEL(2) util.c:384:tdserror: returning TDS_INT_CANCEL(2)
login.c:423:IP address pointer is empty
login.c:425:Server txeis not found!

What should the "provided name" be? Outside ODBC, servers are known to
FreeTDS by their servername, a section name in freetds.conf. Yours is
DB246111 because

Post by Tony Esposito
[DB246111]
host = 207.235.236.120
port = 2638
tds version = 5.0

so your command in bsqldb would be

$ bsqldb -S DB246111 -U dba -P sql

just as with tsql.

Inside ODBC, it's too dark to read. (Apologies to Groucho Marx.)

In odbc.ini, the servername property names a freetds.conf servername,
and the utility (bsqlodbc or isql) is provided with the DSN name (Data
Source Name, not to be confused with DNS). Yours is txeis because

Post by Tony Esposito
[txeis]
Driver=FreeTDS
Description=Sybase TXEIS Database
Servername=DB246111
Port=2638
TDS_Version=5.0

so your command would be

$ bsqlodbc -S txeis -U dba -P sql

HTH.

--jkl

[Tony Esposito]

Thanks for the suggestions. I unfortunately get no results from 'man bsqldb' or man bsqlodbc' from my Linux system. Did you mean to refer to 'man' via the web instead?

Did you happen to see my follow-up emails? I can connect and access the system tables by virtue of the fact that the 'help' command works in 'isql' and returns the table description (see follow-up email I sent last night for an example of this).
What I don't seem to be getting is access to database-specific tables. The tool 'isql' shows no rows returned. (-1 rsow count). I tried 'bsqlodbc' and it just hangs....

I tried your reference to ASA database in the freetds.conf file and no change.

Any help would be greatly appreciated. I greatly appreciate the time you have already spent. You are the best!
'

Tony Esposito
ETL Developer
Information Services Programming
Region 10 Education Service Center
400 E Spring Valley Rd
Richardson, TX 75081
(Ph) 972-348-1284
(Fax) 972-348-1285
Reveal Data Analytics




-----Original Message-----
From: freetds-bounces at lists.ibiblio.org [mailto:freetds-bounces at lists.ibiblio.org] On Behalf Of James K. Lowden
Sent: Sunday, September 16, 2012 3:01 PM
To: FreeTDS Development Group
Subject: Re: [freetds] osql and isql -- how to test ODBC config?

On Sun, 16 Sep 2012 03:55:16 +0000

Post by Tony Esposito
I am not familiar with 'bsqldb'.
Can I just run it from bash shell prompt?

I think you know the answer to that now, but for the record bsqldb is documented as a man page. "man bsqldb" is your friend. :-)

I misled you, though. I meant to suggest you use bsqlodbc (similarly documented). I hope it will do a better job of error reporting.

Because you're using Sybase Anywhere I'll draw your attention to a footnote in the UG:
http://www.freetds.org/userguide/freetdsconf.htm#FTN.AEN942.

See also the "ASA database" in freetds.conf. It might be something in that neighborhood. I suspect so only because I see nothing wrong with your odbc.ini and because osql didn't report a problem.

Post by Tony Esposito
bsqldb -S txeis -U dba -P sql
bsqldb: Msg 20012, Level 2 Server name not found in configuration
files
bsqldb: Msg 20013, Level 2
Unknown host machine name

Msg 20012 comes from FreeTDS. It means FreeTDS couldn't convert the provided name to an ip addreess. The relevant part of the TDSDUMP is

Post by Tony Esposito
dblib.c:7929:dbperror(0x1121510, 20013, 0)
dblib.c:7981:20013: "Unknown host machine name"
dblib.c:8002:"Unknown host machine name", client returns 2
(INT_CANCEL) util.c:361:tdserror: client library returned
TDS_INT_CANCEL(2) util.c:384:tdserror: returning TDS_INT_CANCEL(2)
login.c:423:IP address pointer is empty login.c:425:Server txeis not
found!

What should the "provided name" be? Outside ODBC, servers are known to FreeTDS by their servername, a section name in freetds.conf. Yours is
DB246111 because

Post by Tony Esposito
[DB246111]
host = 207.235.236.120
port = 2638
tds version = 5.0

so your command in bsqldb would be

$ bsqldb -S DB246111 -U dba -P sql

just as with tsql.

Inside ODBC, it's too dark to read. (Apologies to Groucho Marx.)

In odbc.ini, the servername property names a freetds.conf servername, and the utility (bsqlodbc or isql) is provided with the DSN name (Data Source Name, not to be confused with DNS). Yours is txeis because

Post by Tony Esposito
[txeis]
Driver=FreeTDS
Description=Sybase TXEIS Database
Servername=DB246111
Port=2638
TDS_Version=5.0

so your command would be

$ bsqlodbc -S txeis -U dba -P sql

HTH.

--jkl
_______________________________________________
FreeTDS mailing list
FreeTDS at lists.ibiblio.org
http://lists.ibiblio.org/mailman/listinfo/freetds


-----
No virus found in this message.
Checked by AVG - www.avg.com
Version: 2012.0.2221 / Virus Database: 2437/5271 - Release Date: 09/16/12

[Tony Esposito]

More interesting stuff...
Viewing Sybase Central, the Sybase DBA tool, shows that I have an active connection when I use isql. In other words, I am making a successful connection to the database with the config I have but for some reason not returning rows (or not able to see table due to privs). Yet 'help' can see the system catalog tables and return information about the tables I want to select from...

-----Original Message-----
From: Tony Esposito
Sent: Sunday, September 16, 2012 3:20 PM
To: FreeTDS Development Group
Cc: Tony Esposito
Subject: RE: [freetds] osql and isql -- how to test ODBC config?

Thanks for the suggestions. I unfortunately get no results from 'man bsqldb' or man bsqlodbc' from my Linux system. Did you mean to refer to 'man' via the web instead?

Did you happen to see my follow-up emails? I can connect and access the system tables by virtue of the fact that the 'help' command works in 'isql' and returns the table description (see follow-up email I sent last night for an example of this).
What I don't seem to be getting is access to database-specific tables. The tool 'isql' shows no rows returned. (-1 rsow count). I tried 'bsqlodbc' and it just hangs....

I tried your reference to ASA database in the freetds.conf file and no change.

Any help would be greatly appreciated. I greatly appreciate the time you have already spent. You are the best!
'

Tony Esposito
ETL Developer
Information Services Programming
Region 10 Education Service Center
400 E Spring Valley Rd
Richardson, TX 75081
(Ph) 972-348-1284
(Fax) 972-348-1285
Reveal Data Analytics




-----Original Message-----
From: freetds-bounces at lists.ibiblio.org [mailto:freetds-bounces at lists.ibiblio.org] On Behalf Of James K. Lowden
Sent: Sunday, September 16, 2012 3:01 PM
To: FreeTDS Development Group
Subject: Re: [freetds] osql and isql -- how to test ODBC config?

On Sun, 16 Sep 2012 03:55:16 +0000

Post by Tony Esposito
I am not familiar with 'bsqldb'.
Can I just run it from bash shell prompt?

I think you know the answer to that now, but for the record bsqldb is documented as a man page. "man bsqldb" is your friend. :-)

I misled you, though. I meant to suggest you use bsqlodbc (similarly documented). I hope it will do a better job of error reporting.

Because you're using Sybase Anywhere I'll draw your attention to a footnote in the UG:
http://www.freetds.org/userguide/freetdsconf.htm#FTN.AEN942.

See also the "ASA database" in freetds.conf. It might be something in that neighborhood. I suspect so only because I see nothing wrong with your odbc.ini and because osql didn't report a problem.

Post by Tony Esposito
bsqldb -S txeis -U dba -P sql
bsqldb: Msg 20012, Level 2 Server name not found in configuration
files
bsqldb: Msg 20013, Level 2
Unknown host machine name

Msg 20012 comes from FreeTDS. It means FreeTDS couldn't convert the provided name to an ip addreess. The relevant part of the TDSDUMP is

Post by Tony Esposito
dblib.c:7929:dbperror(0x1121510, 20013, 0)
dblib.c:7981:20013: "Unknown host machine name"
dblib.c:8002:"Unknown host machine name", client returns 2
(INT_CANCEL) util.c:361:tdserror: client library returned
TDS_INT_CANCEL(2) util.c:384:tdserror: returning TDS_INT_CANCEL(2)
login.c:423:IP address pointer is empty login.c:425:Server txeis not
found!

What should the "provided name" be? Outside ODBC, servers are known to FreeTDS by their servername, a section name in freetds.conf. Yours is
DB246111 because

Post by Tony Esposito
[DB246111]
host = 207.235.236.120
port = 2638
tds version = 5.0

so your command in bsqldb would be

$ bsqldb -S DB246111 -U dba -P sql

just as with tsql.

Inside ODBC, it's too dark to read. (Apologies to Groucho Marx.)

In odbc.ini, the servername property names a freetds.conf servername, and the utility (bsqlodbc or isql) is provided with the DSN name (Data Source Name, not to be confused with DNS). Yours is txeis because

Post by Tony Esposito
[txeis]
Driver=FreeTDS
Description=Sybase TXEIS Database
Servername=DB246111
Port=2638
TDS_Version=5.0

so your command would be

$ bsqlodbc -S txeis -U dba -P sql

HTH.

--jkl
_______________________________________________
FreeTDS mailing list
FreeTDS at lists.ibiblio.org
http://lists.ibiblio.org/mailman/listinfo/freetds


-----
No virus found in this message.
Checked by AVG - www.avg.com
Version: 2012.0.2221 / Virus Database: 2437/5271 - Release Date: 09/16/12

-----
No virus found in this message.
Checked by AVG - www.avg.com
Version: 2012.0.2221 / Virus Database: 2437/5271 - Release Date: 09/16/12

[Frediano Ziglio]

It's possible that you are in the wrong database? Or that you don't
have permission for some columns/rows ?

Frediano

Post by Tony Esposito
More interesting stuff...
Viewing Sybase Central, the Sybase DBA tool, shows that I have an active connection when I use isql. In other words, I am making a successful connection to the database with the config I have but for some reason not returning rows (or not able to see table due to privs). Yet 'help' can see the system catalog tables and return information about the tables I want to select from...
-----Original Message-----
From: Tony Esposito
Sent: Sunday, September 16, 2012 3:20 PM
To: FreeTDS Development Group
Cc: Tony Esposito
Subject: RE: [freetds] osql and isql -- how to test ODBC config?
Thanks for the suggestions. I unfortunately get no results from 'man bsqldb' or man bsqlodbc' from my Linux system. Did you mean to refer to 'man' via the web instead?
Did you happen to see my follow-up emails? I can connect and access the system tables by virtue of the fact that the 'help' command works in 'isql' and returns the table description (see follow-up email I sent last night for an example of this).
What I don't seem to be getting is access to database-specific tables. The tool 'isql' shows no rows returned. (-1 rsow count). I tried 'bsqlodbc' and it just hangs....
I tried your reference to ASA database in the freetds.conf file and no change.
Any help would be greatly appreciated. I greatly appreciate the time you have already spent. You are the best!
'
Tony Esposito
ETL Developer
Information Services Programming
Region 10 Education Service Center
400 E Spring Valley Rd
Richardson, TX 75081
(Ph) 972-348-1284
(Fax) 972-348-1285
Reveal Data Analytics
-----Original Message-----
From: freetds-bounces at lists.ibiblio.org [mailto:freetds-bounces at lists.ibiblio.org] On Behalf Of James K. Lowden
Sent: Sunday, September 16, 2012 3:01 PM
To: FreeTDS Development Group
Subject: Re: [freetds] osql and isql -- how to test ODBC config?
On Sun, 16 Sep 2012 03:55:16 +0000

Post by Tony Esposito
I am not familiar with 'bsqldb'.
Can I just run it from bash shell prompt?

I think you know the answer to that now, but for the record bsqldb is documented as a man page. "man bsqldb" is your friend. :-)
I misled you, though. I meant to suggest you use bsqlodbc (similarly documented). I hope it will do a better job of error reporting.
http://www.freetds.org/userguide/freetdsconf.htm#FTN.AEN942.
See also the "ASA database" in freetds.conf. It might be something in that neighborhood. I suspect so only because I see nothing wrong with your odbc.ini and because osql didn't report a problem.

Post by Tony Esposito
bsqldb -S txeis -U dba -P sql
bsqldb: Msg 20012, Level 2 Server name not found in configuration
files
bsqldb: Msg 20013, Level 2
Unknown host machine name

Msg 20012 comes from FreeTDS. It means FreeTDS couldn't convert the provided name to an ip addreess. The relevant part of the TDSDUMP is

Post by Tony Esposito
dblib.c:7929:dbperror(0x1121510, 20013, 0)
dblib.c:7981:20013: "Unknown host machine name"
dblib.c:8002:"Unknown host machine name", client returns 2
(INT_CANCEL) util.c:361:tdserror: client library returned
TDS_INT_CANCEL(2) util.c:384:tdserror: returning TDS_INT_CANCEL(2)
login.c:423:IP address pointer is empty login.c:425:Server txeis not
found!

What should the "provided name" be? Outside ODBC, servers are known to FreeTDS by their servername, a section name in freetds.conf. Yours is
DB246111 because

Post by Tony Esposito
[DB246111]
host = 207.235.236.120
port = 2638
tds version = 5.0

so your command in bsqldb would be
$ bsqldb -S DB246111 -U dba -P sql
just as with tsql.
Inside ODBC, it's too dark to read. (Apologies to Groucho Marx.)
In odbc.ini, the servername property names a freetds.conf servername, and the utility (bsqlodbc or isql) is provided with the DSN name (Data Source Name, not to be confused with DNS). Yours is txeis because

Post by Tony Esposito
[txeis]
Driver=FreeTDS
Description=Sybase TXEIS Database
Servername=DB246111
Port=2638
TDS_Version=5.0

so your command would be
$ bsqlodbc -S txeis -U dba -P sql
HTH.
--jkl

[Tony Esposito]

Hi Frediano,
I am certain I am connecting to the correct database. But check out this feedback I got from an unixODBC mailing list:

"-1 from a SQLRowCount is entirly ok, it just means that the driver does not know how many rows are in the result set which is common with forward only cursors. Beyond that, I don't know what to suggest, I can only assume its a driver problem."

Assuming this is true, is there some parameter that needs to be set to tell it a buffer or textsize required to get the data returned to the screen?
Please advise and thanks for your help.

Tony Esposito
ETL Developer
Information Services Programming
Region 10 Education Service Center
400 E Spring Valley Rd
Richardson, TX 75081
(Ph) 972-348-1284
(Fax) 972-348-1285
Reveal Data Analytics



-----Original Message-----
From: freetds-bounces at lists.ibiblio.org [mailto:freetds-bounces at lists.ibiblio.org] On Behalf Of Frediano Ziglio
Sent: Monday, September 17, 2012 4:36 AM
To: FreeTDS Development Group
Subject: Re: [freetds] osql and isql -- how to test ODBC config?

It's possible that you are in the wrong database? Or that you don't have permission for some columns/rows ?

Frediano

Post by Tony Esposito
More interesting stuff...
Viewing Sybase Central, the Sybase DBA tool, shows that I have an active connection when I use isql. In other words, I am making a successful connection to the database with the config I have but for some reason not returning rows (or not able to see table due to privs). Yet 'help' can see the system catalog tables and return information about the tables I want to select from...
-----Original Message-----
From: Tony Esposito
Sent: Sunday, September 16, 2012 3:20 PM
To: FreeTDS Development Group
Cc: Tony Esposito
Subject: RE: [freetds] osql and isql -- how to test ODBC config?
Thanks for the suggestions. I unfortunately get no results from 'man bsqldb' or man bsqlodbc' from my Linux system. Did you mean to refer to 'man' via the web instead?
Did you happen to see my follow-up emails? I can connect and access the system tables by virtue of the fact that the 'help' command works in 'isql' and returns the table description (see follow-up email I sent last night for an example of this).
What I don't seem to be getting is access to database-specific tables. The tool 'isql' shows no rows returned. (-1 rsow count). I tried 'bsqlodbc' and it just hangs....
I tried your reference to ASA database in the freetds.conf file and no change.
Any help would be greatly appreciated. I greatly appreciate the time you have already spent. You are the best!
'
Tony Esposito
ETL Developer
Information Services Programming
Region 10 Education Service Center
400 E Spring Valley Rd
Richardson, TX 75081
(Ph) 972-348-1284
(Fax) 972-348-1285
Reveal Data Analytics
-----Original Message-----
From: freetds-bounces at lists.ibiblio.org
[mailto:freetds-bounces at lists.ibiblio.org] On Behalf Of James K.
Lowden
Sent: Sunday, September 16, 2012 3:01 PM
To: FreeTDS Development Group
Subject: Re: [freetds] osql and isql -- how to test ODBC config?
On Sun, 16 Sep 2012 03:55:16 +0000

Post by Tony Esposito
I am not familiar with 'bsqldb'.
Can I just run it from bash shell prompt?

I think you know the answer to that now, but for the record bsqldb is
documented as a man page. "man bsqldb" is your friend. :-)
I misled you, though. I meant to suggest you use bsqlodbc (similarly documented). I hope it will do a better job of error reporting.
http://www.freetds.org/userguide/freetdsconf.htm#FTN.AEN942.
See also the "ASA database" in freetds.conf. It might be something in that neighborhood. I suspect so only because I see nothing wrong with your odbc.ini and because osql didn't report a problem.

Post by Tony Esposito
bsqldb -S txeis -U dba -P sql
bsqldb: Msg 20012, Level 2 Server name not found in configuration
files
bsqldb: Msg 20013, Level 2
Unknown host machine name

Msg 20012 comes from FreeTDS. It means FreeTDS couldn't convert the
provided name to an ip addreess. The relevant part of the TDSDUMP is

Post by Tony Esposito
dblib.c:7929:dbperror(0x1121510, 20013, 0)
dblib.c:7981:20013: "Unknown host machine name"
dblib.c:8002:"Unknown host machine name", client returns 2
(INT_CANCEL) util.c:361:tdserror: client library returned
TDS_INT_CANCEL(2) util.c:384:tdserror: returning TDS_INT_CANCEL(2)
login.c:423:IP address pointer is empty login.c:425:Server txeis not
found!

What should the "provided name" be? Outside ODBC, servers are known
to FreeTDS by their servername, a section name in freetds.conf. Yours
is
DB246111 because

Post by Tony Esposito
[DB246111]
host = 207.235.236.120
port = 2638
tds version = 5.0

so your command in bsqldb would be
$ bsqldb -S DB246111 -U dba -P sql
just as with tsql.
Inside ODBC, it's too dark to read. (Apologies to Groucho Marx.)
In odbc.ini, the servername property names a freetds.conf servername,
and the utility (bsqlodbc or isql) is provided with the DSN name (Data
Source Name, not to be confused with DNS). Yours is txeis because

Post by Tony Esposito
[txeis]
Driver=FreeTDS
Description=Sybase TXEIS Database
Servername=DB246111
Port=2638
TDS_Version=5.0

so your command would be
$ bsqlodbc -S txeis -U dba -P sql
HTH.
--jkl

_______________________________________________
FreeTDS mailing list
FreeTDS at lists.ibiblio.org
http://lists.ibiblio.org/mailman/listinfo/freetds


-----
No virus found in this message.
Checked by AVG - www.avg.com
Version: 2012.0.2221 / Virus Database: 2437/5273 - Release Date: 09/17/12

[Tony Esposito]

Hello again,
Also, since 'isql help' command is working, we know then that the ODBC API is being referenced 'ok' since 'help' uses the SQL Columns and SQLTables API calls. It appears that when a SELECT is done, the SQLPrepare ODBC API call is failing maybe due to not know how to handle the rows returned. I tried a SELECT which returns 1 row, 1 column and I still get the error SQLRowCount returns -1.
HTH

Tony Esposito
ETL Developer
Information Services Programming
Region 10 Education Service Center
400 E Spring Valley Rd
Richardson, TX 75081
(Ph) 972-348-1284
(Fax) 972-348-1285
Reveal Data Analytics




-----Original Message-----
From: Tony Esposito
Sent: Monday, September 17, 2012 6:49 PM
To: FreeTDS Development Group
Cc: Tony Esposito
Subject: RE: [freetds] osql and isql -- how to test ODBC config?

Hi Frediano,
I am certain I am connecting to the correct database. But check out this feedback I got from an unixODBC mailing list:

"-1 from a SQLRowCount is entirly ok, it just means that the driver does not know how many rows are in the result set which is common with forward only cursors. Beyond that, I don't know what to suggest, I can only assume its a driver problem."

Assuming this is true, is there some parameter that needs to be set to tell it a buffer or textsize required to get the data returned to the screen?
Please advise and thanks for your help.

Tony Esposito
ETL Developer
Information Services Programming
Region 10 Education Service Center
400 E Spring Valley Rd
Richardson, TX 75081
(Ph) 972-348-1284
(Fax) 972-348-1285
Reveal Data Analytics



-----Original Message-----
From: freetds-bounces at lists.ibiblio.org [mailto:freetds-bounces at lists.ibiblio.org] On Behalf Of Frediano Ziglio
Sent: Monday, September 17, 2012 4:36 AM
To: FreeTDS Development Group
Subject: Re: [freetds] osql and isql -- how to test ODBC config?

It's possible that you are in the wrong database? Or that you don't have permission for some columns/rows ?

Frediano

Post by Tony Esposito
More interesting stuff...
Viewing Sybase Central, the Sybase DBA tool, shows that I have an active connection when I use isql. In other words, I am making a successful connection to the database with the config I have but for some reason not returning rows (or not able to see table due to privs). Yet 'help' can see the system catalog tables and return information about the tables I want to select from...
-----Original Message-----
From: Tony Esposito
Sent: Sunday, September 16, 2012 3:20 PM
To: FreeTDS Development Group
Cc: Tony Esposito
Subject: RE: [freetds] osql and isql -- how to test ODBC config?
Thanks for the suggestions. I unfortunately get no results from 'man bsqldb' or man bsqlodbc' from my Linux system. Did you mean to refer to 'man' via the web instead?
Did you happen to see my follow-up emails? I can connect and access the system tables by virtue of the fact that the 'help' command works in 'isql' and returns the table description (see follow-up email I sent last night for an example of this).
What I don't seem to be getting is access to database-specific tables. The tool 'isql' shows no rows returned. (-1 rsow count). I tried 'bsqlodbc' and it just hangs....
I tried your reference to ASA database in the freetds.conf file and no change.
Any help would be greatly appreciated. I greatly appreciate the time you have already spent. You are the best!
'
Tony Esposito
ETL Developer
Information Services Programming
Region 10 Education Service Center
400 E Spring Valley Rd
Richardson, TX 75081
(Ph) 972-348-1284
(Fax) 972-348-1285
Reveal Data Analytics
-----Original Message-----
From: freetds-bounces at lists.ibiblio.org
[mailto:freetds-bounces at lists.ibiblio.org] On Behalf Of James K.
Lowden
Sent: Sunday, September 16, 2012 3:01 PM
To: FreeTDS Development Group
Subject: Re: [freetds] osql and isql -- how to test ODBC config?
On Sun, 16 Sep 2012 03:55:16 +0000

Post by Tony Esposito
I am not familiar with 'bsqldb'.
Can I just run it from bash shell prompt?

I think you know the answer to that now, but for the record bsqldb is
documented as a man page. "man bsqldb" is your friend. :-)
I misled you, though. I meant to suggest you use bsqlodbc (similarly documented). I hope it will do a better job of error reporting.
http://www.freetds.org/userguide/freetdsconf.htm#FTN.AEN942.
See also the "ASA database" in freetds.conf. It might be something in that neighborhood. I suspect so only because I see nothing wrong with your odbc.ini and because osql didn't report a problem.

Post by Tony Esposito
bsqldb -S txeis -U dba -P sql
bsqldb: Msg 20012, Level 2 Server name not found in configuration
files
bsqldb: Msg 20013, Level 2
Unknown host machine name

Msg 20012 comes from FreeTDS. It means FreeTDS couldn't convert the
provided name to an ip addreess. The relevant part of the TDSDUMP is

Post by Tony Esposito
dblib.c:7929:dbperror(0x1121510, 20013, 0)
dblib.c:7981:20013: "Unknown host machine name"
dblib.c:8002:"Unknown host machine name", client returns 2
(INT_CANCEL) util.c:361:tdserror: client library returned
TDS_INT_CANCEL(2) util.c:384:tdserror: returning TDS_INT_CANCEL(2)
login.c:423:IP address pointer is empty login.c:425:Server txeis not
found!

What should the "provided name" be? Outside ODBC, servers are known
to FreeTDS by their servername, a section name in freetds.conf. Yours
is
DB246111 because

Post by Tony Esposito
[DB246111]
host = 207.235.236.120
port = 2638
tds version = 5.0

so your command in bsqldb would be
$ bsqldb -S DB246111 -U dba -P sql
just as with tsql.
Inside ODBC, it's too dark to read. (Apologies to Groucho Marx.)
In odbc.ini, the servername property names a freetds.conf servername,
and the utility (bsqlodbc or isql) is provided with the DSN name (Data
Source Name, not to be confused with DNS). Yours is txeis because

Post by Tony Esposito
[txeis]
Driver=FreeTDS
Description=Sybase TXEIS Database
Servername=DB246111
Port=2638
TDS_Version=5.0

so your command would be
$ bsqlodbc -S txeis -U dba -P sql
HTH.
--jkl

_______________________________________________
FreeTDS mailing list
FreeTDS at lists.ibiblio.org
http://lists.ibiblio.org/mailman/listinfo/freetds


-----
No virus found in this message.
Checked by AVG - www.avg.com
Version: 2012.0.2221 / Virus Database: 2437/5273 - Release Date: 09/17/12

[James K. Lowden]

On Sun, 16 Sep 2012 20:20:09 +0000

Post by Tony Esposito
I tried 'bsqlodbc' and it just hangs....

It doesn't "just" hang. :-) Dimes to donuts it's waiting for the
server. TDSDUMP will show the status.

One possibility is that your query runs a long time, and that isql uses
a shorter query timeout than bsqlodbc does. That would be consistent
with you seeing an active connection in admin tools, and with being
able to retrieve metadata.

If the same query runs quickly using bsqldb, it could be an
indication that connection properties are altering the behavior of the
query, or that the ODBC driver is somehow altering the SQL text.

Post by Tony Esposito
I unfortunately get no results from 'man bsqldb' or man bsqlodbc'
from my Linux system.

You will find the file bsqlodbc.1 in your FreeTDS build tree. By
running "make install", it is copied to $PREFIX/share/man/man1. PREFIX
is defined in the configure script; the default is /usr/local.

If you have run "make install" and "man bsqlodbc" fails to find the
page, it's because man(1) isn't looking at the directory where it was
installed. IOW your system is misconfigured! "man man" should display
the man utility's manual, which explains how & where man pages are
searched. GNU man has a -w option that reports the search path, and a
-l option to read uninstalled files.

HTH.

--jkl

[Tony Esposito]

-----Original Message-----
From: freetds-bounces at lists.ibiblio.org [mailto:freetds-bounces at lists.ibiblio.org] On Behalf Of James K. Lowden
Sent: Monday, September 17, 2012 3:54 PM
To: freetds at lists.ibiblio.org
Subject: Re: [freetds] osql and isql -- how to test ODBC config?

On Sun, 16 Sep 2012 20:20:09 +0000

Post by Tony Esposito
I tried 'bsqlodbc' and it just hangs....

It doesn't "just" hang. :-) Dimes to donuts it's waiting for the server. TDSDUMP will show the status.

I like that!! :-) Dimes to donuts.
Anyway, I tried a SELECT that returns 1 row, 1 column and it still fails with SQL RowCount -1. I have been told by the unixODBC group -1 from a SQLRowCount is entirely ok and it just means that the driver does not know how many rows are in the result set which is common with forward only cursors.

One possibility is that your query runs a long time, and that isql uses a shorter query timeout than bsqlodbc does. That would be consistent with you seeing an active connection in admin tools, and with being able to >retrieve metadata.
If the same query runs quickly using bsqldb, it could be an indication that connection properties are altering the behavior of the query, or that the ODBC driver is somehow altering the SQL text.

Post by Tony Esposito
I unfortunately get no results from 'man bsqldb' or man bsqlodbc'
from my Linux system.

You will find the file bsqlodbc.1 in your FreeTDS build tree. By running "make install", it is copied to $PREFIX/share/man/man1. PREFIX is defined in the configure script; the default is /usr/local.
If you have run "make install" and "man bsqlodbc" fails to find the page, it's because man(1) isn't looking at the directory where it was installed. IOW your system is misconfigured! "man man" should display the man >utility's manual, which explains how & where man pages are searched. GNU man has a -w option that reports the search path, and a -l option to read uninstalled files.

I did find bsqlodbc1 but not in my build tree. Should I reinstall? How does one uninstall? And why does 'tsql' work just fine?
Thanks for your help.

HTH.

--jkl
_______________________________________________
FreeTDS mailing list
FreeTDS at lists.ibiblio.org
http://lists.ibiblio.org/mailman/listinfo/freetds


-----
No virus found in this message.
Checked by AVG - www.avg.com
Version: 2012.0.2221 / Virus Database: 2437/5273 - Release Date: 09/17/12

[James K. Lowden]

On Tue, 18 Sep 2012 00:36:34 +0000

Post by Tony Esposito
I tried a SELECT that returns 1 row, 1 column and it still fails with
SQL RowCount -1. I have been told by the unixODBC group -1 from a
SQLRowCount is entirely ok

More than OK: normal.

After the server has completed execution of a query, it replies with a
DONE packet. That packet includes two fields: a row count, and an "is
the row count valid" flag. If the row count is invalid, its value is
normally -1.

Why would the server return an invalid count? When it isn't keeping
track. When you issue an insert/update/delete, the returned row count
is always valid. The client has no way to know the count of affected
rows other than hearing from the server. For SELELCT, however, the
row count is never valid. The rows were returned; the client can count
for himself how many there were. After all, the server can't know how
many rows were presented to the user, only how many it sent.

Post by Tony Esposito
I did find bsqlodbc1 but not in my build tree. Should I reinstall?

Probably not. The bsqlodbc.1 file was generated from a template and
installed in PREFIX, per the configure script, which
defaults to /usr/local. If man(1) doesn't find it, I'd suggest looking
for it there and updating your man configuration.

$ ./configure --help | grep -E 'datarootdir|mandir'
--datarootdir=DIR read-only arch.-independent data root
[PREFIX/share]
--mandir=DIR man documentation [DATAROOTDIR/man]

HTH.

--jkl

[Tony Esposito]

Thanks James.

See bottom of thread for latest results. Seems to me error 100 means "no rows returned".
I am making a connection to the correct database.

Tony Esposito
ETL Developer
Information Services Programming
Region 10 Education Service Center
400 E Spring Valley Rd
Richardson, TX 75081
(Ph) 972-348-1284
(Fax) 972-348-1285
Reveal Data Analytics

-----Original Message-----
From: freetds-bounces at lists.ibiblio.org [mailto:freetds-bounces at lists.ibiblio.org] On Behalf Of James K. Lowden
Sent: Tuesday, September 18, 2012 8:32 AM
To: freetds at lists.ibiblio.org
Subject: Re: [freetds] osql and isql -- how to test ODBC config?

On Tue, 18 Sep 2012 00:36:34 +0000

Post by James K. Lowden

Post by Tony Esposito
I tried a SELECT that returns 1 row, 1 column and it still fails with
SQL RowCount -1. I have been told by the unixODBC group -1 from a
SQLRowCount is entirely ok

More than OK: normal.
After the server has completed execution of a query, it replies with a DONE packet. That packet includes two fields: a row count, and an "is the row count valid" flag. If the row count is invalid, its value is normally -1.
Why would the server return an invalid count? When it isn't keeping track. When you issue an insert/update/delete, the returned row count is always valid. The client has no way to know the count of affected rows other than >hearing from the server. For SELELCT, however, the row count is never valid. The rows were returned; the client can count for himself how many there were. After all, the server can't know how many rows were presented to >the user, only how many it sent.

Post by Tony Esposito
I did find bsqlodbc1 but not in my build tree. Should I reinstall?

Probably not. The bsqlodbc.1 file was generated from a template and installed in PREFIX, per the configure script, which defaults to /usr/local. If man(1) doesn't find it, I'd suggest looking for it there and updating your man >configuration.
$ ./configure --help | grep -E 'datarootdir|mandir'
--datarootdir=DIR read-only arch.-independent data root
[PREFIX/share]
--mandir=DIR man documentation [DATAROOTDIR/man]
HTH.

---------------------------------------------------------------------------
(1) Set my MANPATH so now I can find the man pages for bsqlodbc.
(2) Ran bsqlodbc and get the following:

user #> bsqlodbc -U dba -P sql -S txeis -D DB246111 -i input.sql -o output.sql -e error.sql

Input file input.sql contents: select * from sr_stu_demo;
Output file output.sql contents: <empty>
Error file error.sql contents: bsqlodbc: error: 100 is an unknown return code for SQLExecute
_______________________________________________

[James K. Lowden]

On Tue, 18 Sep 2012 19:56:14 +0000

Post by Tony Esposito
(1) Set my MANPATH so now I can find the man pages for bsqlodbc.

Hurrah!

Post by Tony Esposito
user #> bsqlodbc -U dba -P sql -S txeis -D DB246111 -i input.sql -o output.sql -e error.sql
Input file input.sql contents: select * from sr_stu_demo;
Output file output.sql contents: <empty>
Error file error.sql contents: bsqlodbc: error: 100 is an
unknown return code for SQLExecute

As I suspected, a missing error message. But why?

The number 100 has some significance but shouldn't show up in this
context. The TDS library can return error 100 to indicate iconv is not
supported for TDS protocol version 7.3, but you're using TDS 5.0 and
the error's being returned from SQLExecute, not SQLConnect.

I'm afraid all I can suggest is to use TDSDUMP with the above command
and, and lets have another look at the output. The log will show a lot
of the call stack. It might yield a clue of where to look.

You have found a bug, by definition. I think there's something funny
in your configuration, something you'll easily be able to fix. The bug
is that you shouldn't be able to get the system to produce that
message, no matter what you do. So I am interested in how it
happened.

--jkl

[Frediano Ziglio]

Post by James K. Lowden
On Tue, 18 Sep 2012 19:56:14 +0000

Post by Tony Esposito
(1) Set my MANPATH so now I can find the man pages for bsqlodbc.

Hurrah!

Post by Tony Esposito
user #> bsqlodbc -U dba -P sql -S txeis -D DB246111 -i input.sql -o
output.sql -e error.sql
Input file input.sql contents: select * from sr_stu_demo;
Output file output.sql contents: <empty>
Error file error.sql contents: bsqlodbc: error: 100 is an
unknown return code for SQLExecute

As I suspected, a missing error message. But why?
The number 100 has some significance but shouldn't show up in this
context. The TDS library can return error 100 to indicate iconv is not
supported for TDS protocol version 7.3, but you're using TDS 5.0 and
the error's being returned from SQLExecute, not SQLConnect.
I'm afraid all I can suggest is to use TDSDUMP with the above command
and, and lets have another look at the output. The log will show a lot
of the call stack. It might yield a clue of where to look.
You have found a bug, by definition. I think there's something funny
in your configuration, something you'll easily be able to fix. The bug
is that you shouldn't be able to get the system to produce that
message, no matter what you do. So I am interested in how it
happened.

No, 100 in ODBC is SQL_NO_DATA. From documentation
SQLExecute/SQLExecDirect should return SQL_NO_DATA if not data are
returned. It seems that MS ODBC never return this value. I think that
I changed (not in 0.91!) our driver to return SQL_NO_DATA according to
MS ODBC. However it's a bug in isql in this case.

Frediano

[Tony Esposito]

James, Frediano,
The ANSI standard error code 100 stands for 'no rows found', as Frediano pointed out.
Question: Given that this may be an 'isql' error and that 'tsql' works, would the ODBC layer actually be working and it is just that we need another way to test it? (Maybe not since bsqlodbc does not work.)
I guess I need to know the next step, if there is any, to take.

James,
TDSDUMP file from an execution of bsqlodbc attached -- seems Frediano has a point as the dunp file shows SQLExecute returning SQL_NO_DATA.

Just a reminder: I am attempting to access Sybase Anywhere 10. Could it be that FreeTDS 0.91 was never fully tested against Sybase Anywhere 10 since version 10 is so old?
Appreciate all the help, guys. You've been a pleasure to work with. And we will get this figured out. :-)

Tony Esposito
ETL Developer
Information Services Programming
Region 10 Education Service Center
400 E Spring Valley Rd
Richardson, TX 75081
(Ph) 972-348-1284
(Fax) 972-348-1285
Reveal Data Analytics




-----Original Message-----
From: freetds-bounces at lists.ibiblio.org [mailto:freetds-bounces at lists.ibiblio.org] On Behalf Of Frediano Ziglio
Sent: Wednesday, September 19, 2012 4:20 AM
To: FreeTDS Development Group
Subject: Re: [freetds] osql and isql -- how to test ODBC config?

Post by James K. Lowden
On Tue, 18 Sep 2012 19:56:14 +0000

Post by Tony Esposito
(1) Set my MANPATH so now I can find the man pages for bsqlodbc.

Hurrah!

Post by Tony Esposito
user #> bsqlodbc -U dba -P sql -S txeis -D DB246111 -i input.sql -o
output.sql -e error.sql
Input file input.sql contents: select * from sr_stu_demo;
Output file output.sql contents: <empty>
Error file error.sql contents: bsqlodbc: error: 100 is an
unknown return code for SQLExecute

As I suspected, a missing error message. But why?
The number 100 has some significance but shouldn't show up in this
context. The TDS library can return error 100 to indicate iconv is
not supported for TDS protocol version 7.3, but you're using TDS 5.0
and the error's being returned from SQLExecute, not SQLConnect.
I'm afraid all I can suggest is to use TDSDUMP with the above command
and, and lets have another look at the output. The log will show a
lot of the call stack. It might yield a clue of where to look.
You have found a bug, by definition. I think there's something funny
in your configuration, something you'll easily be able to fix. The
bug is that you shouldn't be able to get the system to produce that
message, no matter what you do. So I am interested in how it
happened.

No, 100 in ODBC is SQL_NO_DATA. From documentation SQLExecute/SQLExecDirect should return SQL_NO_DATA if not data are returned. It seems that MS ODBC never return this value. I think that I changed (not in 0.91!) our driver to return SQL_NO_DATA according to MS ODBC. However it's a bug in isql in this case.

Frediano
_______________________________________________
FreeTDS mailing list
FreeTDS at lists.ibiblio.org
http://lists.ibiblio.org/mailman/listinfo/freetds


-----
No virus found in this message.
Checked by AVG - www.avg.com
Version: 2012.0.2221 / Virus Database: 2441/5277 - Release Date: 09/19/12
-------------- next part --------------
An embedded and charset-unspecified text was scrubbed...
Name: freetds dump file.txt
Url: http://lists.ibiblio.org/pipermail/freetds/attachments/20120919/365638b9/attachment.txt

[Tony Esposito]

James, Frediano,
Came across this during research. Tried it but no change. Thought you might find it interesting.

"Some versions of hsODBC do not seem to recognise SQL_WCHAR, SQL_WVARCHAR and SQL_WLONGVARCHAR columns. If a table contains these column types, hsODBC will just error producing no rows. A workaround for this is to add "DisguiseWide=1" to the DSN in your odbc.ini file."

-----Original Message-----
From: Tony Esposito
Sent: Wednesday, September 19, 2012 11:39 AM
To: FreeTDS Development Group; freetds-bounces at lists.ibiblio.org; James K. Lowden
Cc: Tony Esposito
Subject: RE: [freetds] osql and isql -- how to test ODBC config?

James, Frediano,
The ANSI standard error code 100 stands for 'no rows found', as Frediano pointed out.
Question: Given that this may be an 'isql' error and that 'tsql' works, would the ODBC layer actually be working and it is just that we need another way to test it? (Maybe not since bsqlodbc does not work.)
I guess I need to know the next step, if there is any, to take.

James,
TDSDUMP file from an execution of bsqlodbc attached -- seems Frediano has a point as the dunp file shows SQLExecute returning SQL_NO_DATA.

Just a reminder: I am attempting to access Sybase Anywhere 10. Could it be that FreeTDS 0.91 was never fully tested against Sybase Anywhere 10 since version 10 is so old?
Appreciate all the help, guys. You've been a pleasure to work with. And we will get this figured out. :-)

Tony Esposito
ETL Developer
Information Services Programming
Region 10 Education Service Center
400 E Spring Valley Rd
Richardson, TX 75081
(Ph) 972-348-1284
(Fax) 972-348-1285
Reveal Data Analytics




-----Original Message-----
From: freetds-bounces at lists.ibiblio.org<mailto:freetds-bounces at lists.ibiblio.org> [mailto:freetds-bounces at lists.ibiblio.org]<mailto:[mailto:freetds-bounces at lists.ibiblio.org]> On Behalf Of Frediano Ziglio
Sent: Wednesday, September 19, 2012 4:20 AM
To: FreeTDS Development Group
Subject: Re: [freetds] osql and isql -- how to test ODBC config?

Post by James K. Lowden
On Tue, 18 Sep 2012 19:56:14 +0000

Post by Tony Esposito
(1) Set my MANPATH so now I can find the man pages for bsqlodbc.

Hurrah!

Post by Tony Esposito
user #> bsqlodbc -U dba -P sql -S txeis -D DB246111 -i input.sql -o
output.sql -e error.sql
Input file input.sql contents: select * from sr_stu_demo;
Output file output.sql contents: <empty>
Error file error.sql contents: bsqlodbc: error: 100 is an
unknown return code for SQLExecute

As I suspected, a missing error message. But why?
The number 100 has some significance but shouldn't show up in this
context. The TDS library can return error 100 to indicate iconv is
not supported for TDS protocol version 7.3, but you're using TDS 5.0
and the error's being returned from SQLExecute, not SQLConnect.
I'm afraid all I can suggest is to use TDSDUMP with the above command
and, and lets have another look at the output. The log will show a
lot of the call stack. It might yield a clue of where to look.
You have found a bug, by definition. I think there's something funny
in your configuration, something you'll easily be able to fix. The
bug is that you shouldn't be able to get the system to produce that
message, no matter what you do. So I am interested in how it
happened.

No, 100 in ODBC is SQL_NO_DATA. From documentation SQLExecute/SQLExecDirect should return SQL_NO_DATA if not data are returned. It seems that MS ODBC never return this value. I think that I changed (not in 0.91!) our driver to return SQL_NO_DATA according to MS ODBC. However it's a bug in isql in this case.

Frediano
_______________________________________________
FreeTDS mailing list
FreeTDS at lists.ibiblio.org<mailto:FreeTDS at lists.ibiblio.org>
http://lists.ibiblio.org/mailman/listinfo/freetds


-----
No virus found in this message.
Checked by AVG - www.avg.com<http://www.avg.com>
Version: 2012.0.2221 / Virus Database: 2441/5277 - Release Date: 09/19/12

-----
No virus found in this message.
Checked by AVG - www.avg.com<http://www.avg.com>
Version: 2012.0.2221 / Virus Database: 2441/5277 - Release Date: 09/19/12

[James K. Lowden]

On Wed, 19 Sep 2012 16:39:22 +0000

Post by Tony Esposito
The ANSI standard error code 100 stands for 'no rows found',

Let's not get ahead of ourselves. ANSI isn't involved. ODBC is
Microsoft's. It is the basis for the X/Open SQL CLI, which is
"governed" by a vendor consortium. Most people call that ODBC, too,
probably because that what it pretty much is, and maybe also because
it's easier to say and remember.

SQL_NO_DATA is defined by the standard as a symbolic constant. Its
value -- the numeric value it compiles to -- isn't mentioned in the
standard. It's up to each driver manager to supply values for such
symbols. Many, including unixODBC, choose 100, probably because
Microsoft did.

I think by recompiling bsqlodbc with ODBCVER=0x300, it will produce a
"no rows returned" message. You could try "export
CFLAGS=-DODBCVER=0x300", then reconfigure, build, and install. That
should at least get you a valid message.

Post by Tony Esposito
Given that this may be an 'isql' error and that 'tsql' works, would
the ODBC layer actually be working

...

Post by Tony Esposito
TDSDUMP file from an execution of bsqlodbc attached

Sadly, no: the dump makes clear the server returned no data, just as
the driver reported. The dump shows normal behavior, see below.

Post by Tony Esposito
Could it be that FreeTDS 0.91 was never fully tested against Sybase
Anywhere 10 since version 10 is so old?

Oh, yes. If someone else reading this message has used that
combination, I hope he'll speak up. You could be the first.

You may well be wondering now why tsql (and bsqldb) returns data, and
bsqlodbc and isql do not. I'm wondering too, but I'll give you the
low-down on the differences to help you sort it out. Bear in mind I've
never used Sybase Anywhere except experimentally. I just know a little
something about the protocol and the driver.

When the client connects to the server, it sends a login packet. That
packet includes a field with flags, and one of those flags is a
bit that indicates to the server the client is an ODBC driver. That is
the sole difference in the login packet: one bit.

The connection has server-side properties, things affected by the T-SQL
SET statement. The server may set some of those properties in response
to seeing the ODBC-driver-in-use bit. The driver may also emit SET
statements prior to issuing the first query. In addition, TDS 5.0 has
a capability-negotiation stage during connection setup, wherein the
client and server each represent what they want and need.

FreeTDS has a very simple view of all this: the server does as it
likes, and the client requires very little. As you can see from the
dump, no SET statements are issued, and the Capability exchange is
brief.

Then we come to the SQL. DB-Library and CT-Library (and tsql) simply
accept the SQL from the application and squirt it out the wire. ODBC
can't be so simple: it constructs a stored procedure

0010 74 72 32 38 36 30 30 30-34 00 63 72 65 61 74 65 |tr286000 4.create|
0020 20 70 72 6f 63 20 69 6f-74 72 32 38 36 30 30 30 | proc io tr286000|
0030 20 61 73 20 73 65 6c 65-63 74 20 2a 20 66 72 6f | as sele ct * fro|
0040 6d 20 73 72 5f 73 74 75-5f 64 65 6d 6f 0a |m sr_stu _demo.|

What comes back looks right

net.c:609:Received packet
0000 04 01 00 21 00 00 00 00-e7 0d 00 20 00 0a 69 6f |...!.... ... ..io|
0010 74 72 32 38 36 30 30 30-fd 00 00 01 00 00 00 00 |tr286000 ........|
0020 00 - |.|

token.c:555:processing result tokens. marker is e7(TDS5_DYNAMIC)
token.c:555:processing result tokens. marker is fd(DONE)

That's an E7 packet, and you can see we're behind in our documentation:

http://www.freetds.org/tds.html#t231

but we can parse it using Sybase's document, "TDS 5.0 Functional
Specification Version 3.8", available on their website.

e7 TDS_DYNAMIC
0d00 13 bytes in message
20 TDS_DYN_ACK, an acknowledgement
00 Status, TDS_DYNAMIC_UNUSED, none
0a IdLen, 10 more bytes
69 6f 74 72 32 38 36 30 30 30 "iotr286000"

So far, so good. Now the driver executes the query.

odbc.c:3496:SQLExecute(0x15084270)
prepare_query.c:203:parsing 0 parameters
odbc.c:3211:_SQLExecute(0x15084270)
odbc.c:3216:_SQLExecute() starting with state 0
odbc.c:3334:End prepare, execute
...
net.c:741:Sending packet
0000 0f 01 00 1a 00 00 00 00-e7 0f 00 02 00 0a 69 6f |........ ......io|
0010 74 72 32 38 36 30 30 30-00 00 |tr286000 ..|

which is right, "e7 0f 00 02 00 " mean E7 packet, 15 bytes long,
type TDS_DYN_EXEC, no status. Comes back the answer:

net.c:609:Received packet
0000 04 01 00 21 00 00 00 00-e7 0d 00 20 00 0a 69 6f |...!.... ... ..io|
0010 74 72 32 38 36 30 30 30-fd 00 00 01 00 00 00 00 |tr286000 ........|
0020 00 - |.|

In "e7 0d 00 20 00 ", that 20 is TDS_DYN_ACK, acknowledging the
request. Then

token.c:555:processing result tokens. marker is e7(TDS5_DYNAMIC)
token.c:555:processing result tokens. marker is fd(DONE)

No rows returned.

You might want to try this yourself.

create procedure iotr286000
as
select * from sr_stu_demo

execute iotr286000

That will probably work; you're not using TDS_DYNAMIC packets.

Recommendations:

1. Make absolutely sure you're connecting to the same database
on the same server with both tsql and bsqlodbc. Use bsqlodbc to update
a table, and verify your update is there with tsql. Try "select
@@servername, db_name()" on both servers. This whole thing
smells like misdirection.?

2. Inspect the properties of each connection. Make them the same to
see if that matters.

3. Verify it works with Sybase's own driver, with the driver installed
on a machine other than the server.

If Sybase's driver works on the same database that FreeTDS doesn't, and
you really, really want this to work, use tcpdump (or windump on
Windows) to capture the tcp/ip traffic for both connections -- the
working one with Sybase's driver, and the non-working one with
FreeTDS. Please be sure to use an account whose password you don't
mind publishing for all time on the Internet. Post those dumps where I
can look at them, and ... wait. I will try to help you find the
difference and construct a patch to fix it.

I know full well the work involved. That's why any for-profit venture
would tell you to upgrade to the latest version (which will probably
work). But this is free software, where time isn't money, where anyone
who wants to can join the party.

Regards,

--jkl

[Frediano Ziglio]

Post by James K. Lowden
On Wed, 19 Sep 2012 16:39:22 +0000

Post by Tony Esposito
The ANSI standard error code 100 stands for 'no rows found',

Let's not get ahead of ourselves. ANSI isn't involved. ODBC is
Microsoft's. It is the basis for the X/Open SQL CLI, which is
"governed" by a vendor consortium. Most people call that ODBC, too,
probably because that what it pretty much is, and maybe also because
it's easier to say and remember.
SQL_NO_DATA is defined by the standard as a symbolic constant. Its
value -- the numeric value it compiles to -- isn't mentioned in the
standard. It's up to each driver manager to supply values for such
symbols. Many, including unixODBC, choose 100, probably because
Microsoft did.
I think by recompiling bsqlodbc with ODBCVER=0x300, it will produce a
"no rows returned" message. You could try "export
CFLAGS=-DODBCVER=0x300", then reconfigure, build, and install. That
should at least get you a valid message.

Post by Tony Esposito
Given that this may be an 'isql' error and that 'tsql' works, would
the ODBC layer actually be working

...

Post by Tony Esposito
TDSDUMP file from an execution of bsqlodbc attached

Sadly, no: the dump makes clear the server returned no data, just as
the driver reported. The dump shows normal behavior, see below.

Post by Tony Esposito
Could it be that FreeTDS 0.91 was never fully tested against Sybase
Anywhere 10 since version 10 is so old?

Oh, yes. If someone else reading this message has used that
combination, I hope he'll speak up. You could be the first.
You may well be wondering now why tsql (and bsqldb) returns data, and
bsqlodbc and isql do not. I'm wondering too, but I'll give you the
low-down on the differences to help you sort it out. Bear in mind I've
never used Sybase Anywhere except experimentally. I just know a little
something about the protocol and the driver.
When the client connects to the server, it sends a login packet. That
packet includes a field with flags, and one of those flags is a
bit that indicates to the server the client is an ODBC driver. That is
the sole difference in the login packet: one bit.
The connection has server-side properties, things affected by the T-SQL
SET statement. The server may set some of those properties in response
to seeing the ODBC-driver-in-use bit. The driver may also emit SET
statements prior to issuing the first query. In addition, TDS 5.0 has
a capability-negotiation stage during connection setup, wherein the
client and server each represent what they want and need.
FreeTDS has a very simple view of all this: the server does as it
likes, and the client requires very little. As you can see from the
dump, no SET statements are issued, and the Capability exchange is
brief.
Then we come to the SQL. DB-Library and CT-Library (and tsql) simply
accept the SQL from the application and squirt it out the wire. ODBC
can't be so simple: it constructs a stored procedure
0010 74 72 32 38 36 30 30 30-34 00 63 72 65 61 74 65 |tr286000 4.create|
0020 20 70 72 6f 63 20 69 6f-74 72 32 38 36 30 30 30 | proc io tr286000|
0030 20 61 73 20 73 65 6c 65-63 74 20 2a 20 66 72 6f | as sele ct * fro|
0040 6d 20 73 72 5f 73 74 75-5f 64 65 6d 6f 0a |m sr_stu _demo.|
What comes back looks right
net.c:609:Received packet
0000 04 01 00 21 00 00 00 00-e7 0d 00 20 00 0a 69 6f |...!.... ... ..io|
0010 74 72 32 38 36 30 30 30-fd 00 00 01 00 00 00 00 |tr286000 ........|
0020 00 - |.|
token.c:555:processing result tokens. marker is e7(TDS5_DYNAMIC)
token.c:555:processing result tokens. marker is fd(DONE)
http://www.freetds.org/tds.html#t231
but we can parse it using Sybase's document, "TDS 5.0 Functional
Specification Version 3.8", available on their website.
e7 TDS_DYNAMIC
0d00 13 bytes in message
20 TDS_DYN_ACK, an acknowledgement
00 Status, TDS_DYNAMIC_UNUSED, none
0a IdLen, 10 more bytes
69 6f 74 72 32 38 36 30 30 30 "iotr286000"
So far, so good. Now the driver executes the query.
odbc.c:3496:SQLExecute(0x15084270)
prepare_query.c:203:parsing 0 parameters
odbc.c:3211:_SQLExecute(0x15084270)
odbc.c:3216:_SQLExecute() starting with state 0
odbc.c:3334:End prepare, execute
...
net.c:741:Sending packet
0000 0f 01 00 1a 00 00 00 00-e7 0f 00 02 00 0a 69 6f |........ ......io|
0010 74 72 32 38 36 30 30 30-00 00 |tr286000 ..|
which is right, "e7 0f 00 02 00 " mean E7 packet, 15 bytes long,
net.c:609:Received packet
0000 04 01 00 21 00 00 00 00-e7 0d 00 20 00 0a 69 6f |...!.... ... ..io|
0010 74 72 32 38 36 30 30 30-fd 00 00 01 00 00 00 00 |tr286000 ........|
0020 00 - |.|
In "e7 0d 00 20 00 ", that 20 is TDS_DYN_ACK, acknowledging the
request. Then
token.c:555:processing result tokens. marker is e7(TDS5_DYNAMIC)
token.c:555:processing result tokens. marker is fd(DONE)
No rows returned.
You might want to try this yourself.
create procedure iotr286000
as
select * from sr_stu_demo
execute iotr286000
That will probably work; you're not using TDS_DYNAMIC packets.

Quite strange. The server accept the execute statement and reply
correctly with no error. The strange thing is that it didn't even
return the row format!
Now, why? Does the "create proc" confuse this version and server
create a procedure instead of executing the code inside? I always saw
this syntax but perhaps there is a capability bit that say to not use
this syntax. Or perhaps there is another option that say to not return
any rows and use cursor? I don't this so cause dynamic does not mix
that much with cursors in TDS 5.0.

Post by James K. Lowden
1. Make absolutely sure you're connecting to the same database
on the same server with both tsql and bsqlodbc. Use bsqlodbc to update
a table, and verify your update is there with tsql. Try "select
@@servername, db_name()" on both servers. This whole thing
smells like misdirection.
2. Inspect the properties of each connection. Make them the same to
see if that matters.
3. Verify it works with Sybase's own driver, with the driver installed
on a machine other than the server.
If Sybase's driver works on the same database that FreeTDS doesn't, and
you really, really want this to work, use tcpdump (or windump on
Windows) to capture the tcp/ip traffic for both connections -- the
working one with Sybase's driver, and the non-working one with
FreeTDS. Please be sure to use an account whose password you don't
mind publishing for all time on the Internet. Post those dumps where I
can look at them, and ... wait. I will try to help you find the
difference and construct a patch to fix it.
I know full well the work involved. That's why any for-profit venture
would tell you to upgrade to the latest version (which will probably
work). But this is free software, where time isn't money, where anyone
who wants to can join the party.
Regards,

Frediano

[Frediano Ziglio]

Post by James K. Lowden
On Wed, 19 Sep 2012 16:39:22 +0000

... omissis ...

Post by James K. Lowden
Then we come to the SQL. DB-Library and CT-Library (and tsql) simply
accept the SQL from the application and squirt it out the wire. ODBC
can't be so simple: it constructs a stored procedure
0010 74 72 32 38 36 30 30 30-34 00 63 72 65 61 74 65 |tr286000 4.create|
0020 20 70 72 6f 63 20 69 6f-74 72 32 38 36 30 30 30 | proc io tr286000|
0030 20 61 73 20 73 65 6c 65-63 74 20 2a 20 66 72 6f | as sele ct * fro|
0040 6d 20 73 72 5f 73 74 75-5f 64 65 6d 6f 0a |m sr_stu _demo.|
What comes back looks right
net.c:609:Received packet
0000 04 01 00 21 00 00 00 00-e7 0d 00 20 00 0a 69 6f |...!.... ... ..io|
0010 74 72 32 38 36 30 30 30-fd 00 00 01 00 00 00 00 |tr286000 ........|
0020 00 - |.|
token.c:555:processing result tokens. marker is e7(TDS5_DYNAMIC)
token.c:555:processing result tokens. marker is fd(DONE)

... omissis ...

We discovered and tested the fix with Tony (I went off list cause I
sent some snapshot on mail).

See change at http://gitorious.org/freetds/freetds/commit/c54b7c8499c6d059d5c14c269259f82a592b52b0
(for 0.91 branch, code for 0.92 is different cause capability changed)

Mainly we didn't support DYNPROC capability that fine not requesting
for this feature but handling like this feature was agreed with the
server. I tested with my Sybase test server and no regressions
occurred. Tony tested with his Sybase SQL Anywhere 10 and now it works
correctly (actually he tested version in 0.92, hope backport works
too)

Frediano

[James K. Lowden]

On Wed, 26 Sep 2012 09:16:22 +0100

Post by Frediano Ziglio
Mainly we didn't support DYNPROC capability that fine not requesting
for this feature but handling like this feature was agreed with the
server. I tested with my Sybase test server and no regressions
occurred. Tony tested with his Sybase SQL Anywhere 10 and now it works
correctly (actually he tested version in 0.92, hope backport works
too)

Very nice, Freddy. Well done!

--jkl

[James K. Lowden]

On Wed, 19 Sep 2012 10:19:47 +0100

Post by Frediano Ziglio

Post by James K. Lowden
You have found a bug, by definition. I think there's something
funny in your configuration, something you'll easily be able to
fix. The bug is that you shouldn't be able to get the system to
produce that message, no matter what you do. So I am interested in
how it happened.

No, 100 in ODBC is SQL_NO_DATA. From documentation
SQLExecute/SQLExecDirect should return SQL_NO_DATA if not data are
returned. It seems that MS ODBC never return this value. I think that
I changed (not in 0.91!) our driver to return SQL_NO_DATA according to
MS ODBC. However it's a bug in isql in this case.

Thanks, Freddy. It's not isql this time. bsqlodbc doesn't recognize
100 as a valid return code. That might be because of this in sql.h:

#if (ODBCVER >= 0x0300)
#define SQL_NO_DATA 100
#endif

src/apps/Makefile.am doesn't define ODBCVER for bsqlodbc. Perhaps it
should?

I found http://msdn.microsoft.com/en-us/library/windows/desktop/ms715424
(v=vs.85).aspx. Words fail me.

--jkl

[Tony Esposito]

One more thing -- is there a way to verify/determine the $ODBC_HOME directory (i.e. the home directory for FreeTDS)? In this case it would probably return with something like /usr/local/freetds. I need to ensure that another product is able to access the $ODBC_HOME/lib directory.
Thx. :-)

-----Original Message-----
From: Tony Esposito
Sent: Tuesday, September 18, 2012 2:56 PM
To: freetds at lists.ibiblio.org
Cc: 'tony.esposito at region10.org'
Subject: RE: [freetds] osql and isql -- how to test ODBC config?

Thanks James.

See bottom of thread for latest results. Seems to me error 100 means "no rows returned".
I am making a connection to the correct database.

Tony Esposito
ETL Developer
Information Services Programming
Region 10 Education Service Center
400 E Spring Valley Rd
Richardson, TX 75081
(Ph) 972-348-1284
(Fax) 972-348-1285
Reveal Data Analytics

-----Original Message-----
From: freetds-bounces at lists.ibiblio.org [mailto:freetds-bounces at lists.ibiblio.org] On Behalf Of James K. Lowden
Sent: Tuesday, September 18, 2012 8:32 AM
To: freetds at lists.ibiblio.org
Subject: Re: [freetds] osql and isql -- how to test ODBC config?

On Tue, 18 Sep 2012 00:36:34 +0000

Post by James K. Lowden

Post by Tony Esposito
I tried a SELECT that returns 1 row, 1 column and it still fails with
SQL RowCount -1. I have been told by the unixODBC group -1 from a
SQLRowCount is entirely ok

More than OK: normal.
After the server has completed execution of a query, it replies with a DONE packet. That packet includes two fields: a row count, and an "is the row count valid" flag. If the row count is invalid, its value is normally -1.
Why would the server return an invalid count? When it isn't keeping track. When you issue an insert/update/delete, the returned row count is always valid. The client has no way to know the count of affected rows other than >hearing from the server. For SELELCT, however, the row count is never valid. The rows were returned; the client can count for himself how many there were. After all, the server can't know how many rows were presented to >the user, only how many it sent.

Post by Tony Esposito
I did find bsqlodbc1 but not in my build tree. Should I reinstall?

Probably not. The bsqlodbc.1 file was generated from a template and installed in PREFIX, per the configure script, which defaults to /usr/local. If man(1) doesn't find it, I'd suggest looking for it there and updating your man >configuration.
$ ./configure --help | grep -E 'datarootdir|mandir'
--datarootdir=DIR read-only arch.-independent data root
[PREFIX/share]
--mandir=DIR man documentation [DATAROOTDIR/man]
HTH.

---------------------------------------------------------------------------
(1) Set my MANPATH so now I can find the man pages for bsqlodbc.
(2) Ran bsqlodbc and get the following:

user #> bsqlodbc -U dba -P sql -S txeis -D DB246111 -i input.sql -o output.sql -e error.sql

Input file input.sql contents: select * from sr_stu_demo;
Output file output.sql contents: <empty>
Error file error.sql contents: bsqlodbc: error: 100 is an unknown return code for SQLExecute
_______________________________________________

[James K. Lowden]

On Tue, 18 Sep 2012 21:40:37 +0000

Post by Tony Esposito
One more thing -- is there a way to verify/determine the $ODBC_HOME
directory (i.e. the home directory for FreeTDS)? In this case it
would probably return with something like /usr/local/freetds. I need
to ensure that another product is able to access the $ODBC_HOME/lib
directory.

I think what you want is available from "tsql -C".

$ tsql -C | grep conf
Compile-time settings (established with the "configure" script)
freetds.conf directory: /usr/local/etc

The "freetds.conf directory" is also the directory where the ODBC
driver looks for odbc.ini. It can be overridden by the environment
variables listed in the UG.

Your question made me realize this information isn't in the manual.
The environment variables should be listed in each utility's man page,
and the tsql page could benefit from an example of tsql -C (which
should be tsql -V, but never mind).

--jkl

[Tony Esposito]

Cc: unixODBC mailing list for assistance.

FreeTDS and unixODBC installed on CentOS 5.6. FreeTDS seems to work fine -- see below -- yet while isql connects it does not return data. Yet isql 'help' does return system table data.
Any help would be greatly appreciated!
Thanks.

Tony Esposito
ETL Developer
Information Services Programming
Region 10 Education Service Center
400 E Spring Valley Rd
Richardson, TX 75081
(Ph) 972-348-1284
(Fax) 972-348-1285
Reveal Data Analytics



-----Original Message-----
From: Tony Esposito
Sent: Thursday, September 13, 2012 9:35 AM
To: freetds at lists.ibiblio.org
Cc: 'tony.esposito at region10.org'
Subject: RE: [freetds] osql and isql -- how to test ODBC config?
Importance: High

Here's what I found out:

(1) tsql connects and returns data fine.
(2) fisql connects and returns data fine.

So it appears the FreeTDS client is setup correctly but...

isql connects yet returns no data when doing a SELECT (see error below):

isql -v txeis dba sql
+---------------------------------------+
| Connected! |
| |
| sql-statement |
| help [tablename] |
| quit |
| |
+---------------------------------------+
SQL> select * from rsccc.sr_stu_demo
SQLRowCount returns -1
SQL>

Yet isql will return system table information via the 'help' command (see SQL below):

+---------------------------------------+
| Connected! |
| |
| sql-statement |
| help [tablename] |
| quit |
| |
+---------------------------------------+
SQL> help rsccc.sr_stu_demo
+---------------------------------------------------------------------------------------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------+--------+---------------------------------------------------------------------------------------------------------------------------------+------------+------------+-------+-------+---------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------+-------------+
| TABLE_QUALIFIER | TABLE_OWNER | TABLE_NAME | COLUMN_NAME | TYPE_ID| TYPE_NAME | PRECISION | LENGTH | SCALE | RADIX | NULLABLE| REMARKS | SS_DOMAIN_ID| COLUMN_ID |
+---------------------------------------------------------------------------------------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------+--------+---------------------------------------------------------------------------------------------------------------------------------+------------+------------+-------+-------+---------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------+-------------+
+---------------------------------------------------------------------------------------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------+--------+---------------------------------------------------------------------------------------------------------------------------------+------------+------------+-------+-------+---------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Please advise.

-----Original Message-----
From: freetds-bounces at lists.ibiblio.org [mailto:freetds-bounces at lists.ibiblio.org] On Behalf Of James K. Lowden
Sent: Thursday, September 13, 2012 1:44 AM
To: freetds at lists.ibiblio.org
Subject: Re: [freetds] osql and isql -- how to test ODBC config?

On Tue, 11 Sep 2012 22:18:39 +0000

Post by Tony Esposito
osql: warning: no DNS hostname found for "207.235.236.120"

...

Post by Tony Esposito
The osql tool stops because of this 'warning' so I never get connected
and therefore never verify my ODBC setup.

The script shouldn't stop there because of that. Check the version you're using; revision 1.12 doesn't exit after producing that message on line 342. In fact, the only check remaining after that is that the hostname has an address. If so, it prints what it found out and starts isql.

Perhaps something in your environment causes the shell to terminate the script when host(1) returns nonzero status? I'd be interested it hear what you find out.

--jkl
_______________________________________________
FreeTDS mailing list
FreeTDS at lists.ibiblio.org
http://lists.ibiblio.org/mailman/listinfo/freetds


-----
No virus found in this message.
Checked by AVG - www.avg.com
Version: 2012.0.2221 / Virus Database: 2437/5266 - Release Date: 09/13/12

[Tony Esposito]

Environment (Linux client accessing Windows 'server'):

CentOS 5.6 Linux 64-bit
FreeTDS 0.91
unixODBC 2.3.1
|
|
|
V
Windows XP Professional SP3 32-bit
Sybase SQL Anywhere 10

Just some extra info that may be of help. Also, cannot execute any stored procedures -- get same error as when trying to execute SELECTs
i.e. SQLRowCount returns -1


Tony Esposito
ETL Developer
Information Services Programming
Region 10 Education Service Center
400 E Spring Valley Rd
Richardson, TX 75081
(Ph) 972-348-1284
(Fax) 972-348-1285
Reveal Data Analytics



-----Original Message-----
From: Tony Esposito
Sent: Thursday, September 13, 2012 10:01 AM
To: 'unixodbc-support at mailman.unixodbc.org.'
Cc: 'tony.esposito at region10.org'; 'freetds at lists.ibiblio.org'
Subject: FW: [freetds] osql and isql -- how to test ODBC config?
Importance: High

Cc: unixODBC mailing list for assistance.

FreeTDS and unixODBC installed on CentOS 5.6. FreeTDS seems to work fine -- see below -- yet while isql connects it does not return data. Yet isql 'help' does return system table data.
Any help would be greatly appreciated!
Thanks.

Tony Esposito
ETL Developer
Information Services Programming
Region 10 Education Service Center
400 E Spring Valley Rd
Richardson, TX 75081
(Ph) 972-348-1284
(Fax) 972-348-1285
Reveal Data Analytics



-----Original Message-----
From: Tony Esposito
Sent: Thursday, September 13, 2012 9:35 AM
To: freetds at lists.ibiblio.org
Cc: 'tony.esposito at region10.org'
Subject: RE: [freetds] osql and isql -- how to test ODBC config?
Importance: High

Here's what I found out:

(1) tsql connects and returns data fine.
(2) fisql connects and returns data fine.

So it appears the FreeTDS client is setup correctly but...

isql connects yet returns no data when doing a SELECT (see error below):

isql -v txeis dba sql
+---------------------------------------+
| Connected! |
| |
| sql-statement |
| help [tablename] |
| quit |
| |
+---------------------------------------+
SQL> select * from rsccc.sr_stu_demo
SQLRowCount returns -1
SQL>

Yet isql will return system table information via the 'help' command (see SQL below):

+---------------------------------------+
| Connected! |
| |
| sql-statement |
| help [tablename] |
| quit |
| |
+---------------------------------------+
SQL> help rsccc.sr_stu_demo
+---------------------------------------------------------------------------------------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------+--------+---------------------------------------------------------------------------------------------------------------------------------+------------+------------+-------+-------+---------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------+-------------+
| TABLE_QUALIFIER | TABLE_OWNER | TABLE_NAME | COLUMN_NAME | TYPE_ID| TYPE_NAME | PRECISION | LENGTH | SCALE | RADIX | NULLABLE| REMARKS | SS_DOMAIN_ID| COLUMN_ID |
+---------------------------------------------------------------------------------------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------+--------+---------------------------------------------------------------------------------------------------------------------------------+------------+------------+-------+-------+---------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------+-------------+
+---------------------------------------------------------------------------------------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------+--------+---------------------------------------------------------------------------------------------------------------------------------+------------+------------+-------+-------+---------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Please advise.

-----Original Message-----
From: freetds-bounces at lists.ibiblio.org [mailto:freetds-bounces at lists.ibiblio.org] On Behalf Of James K. Lowden
Sent: Thursday, September 13, 2012 1:44 AM
To: freetds at lists.ibiblio.org
Subject: Re: [freetds] osql and isql -- how to test ODBC config?

On Tue, 11 Sep 2012 22:18:39 +0000

Post by Tony Esposito
osql: warning: no DNS hostname found for "207.235.236.120"

...

Post by Tony Esposito
The osql tool stops because of this 'warning' so I never get connected
and therefore never verify my ODBC setup.

The script shouldn't stop there because of that. Check the version you're using; revision 1.12 doesn't exit after producing that message on line 342. In fact, the only check remaining after that is that the hostname has an address. If so, it prints what it found out and starts isql.

Perhaps something in your environment causes the shell to terminate the script when host(1) returns nonzero status? I'd be interested it hear what you find out.

--jkl
_______________________________________________
FreeTDS mailing list
FreeTDS at lists.ibiblio.org
http://lists.ibiblio.org/mailman/listinfo/freetds


-----
No virus found in this message.
Checked by AVG - www.avg.com
Version: 2012.0.2221 / Virus Database: 2437/5266 - Release Date: 09/13/12

[Tony Esposito]

I should have included these files earlier, my apologies:

==========================
freetds.conf
==========================

[DB246111]
host = 207.235.236.120
port = 2638
tds version = 5.0
dump file = /usr/freetdsdump.log
client charset = UTF-8

==========================
odbc.ini
==========================

[ODBC Data Sources]
txeis = Sybase TXEIS Database

[txeis]
Driver=FreeTDS
Description=Sybase TXEIS Database
Servername=DB246111
Port=2638
TDS_Version=5.0
Trace=No

[Default]
Driver=/usr/local/freetds/lib/libtdsodbc.so

==========================
odbcinst.ini
==========================

[FreeTDS]
Description=Free TDS 5.0 for Sybase Anywhere
Driver=/usr/local/freetds/lib/libtdsodbc.so

==========================

Tony Esposito
ETL Developer
Information Services Programming
Region 10 Education Service Center
400 E Spring Valley Rd
Richardson, TX 75081
(Ph) 972-348-1284
(Fax) 972-348-1285
Reveal Data Analytics



Threading = 1
-----Original Message-----
From: Tony Esposito
Sent: Thursday, September 13, 2012 10:35 AM
To: 'unixodbc-support at mailman.unixodbc.org.'
Cc: 'tony.esposito at region10.org'; 'freetds at lists.ibiblio.org'
Subject: RE: [freetds] osql and isql -- how to test ODBC config?

Environment (Linux client accessing Windows 'server'):

CentOS 5.6 Linux 64-bit
FreeTDS 0.91
unixODBC 2.3.1
|
|
|
V
Windows XP Professional SP3 32-bit
Sybase SQL Anywhere 10

Just some extra info that may be of help. Also, cannot execute any stored procedures -- get same error as when trying to execute SELECTs i.e. SQLRowCount returns -1


Tony Esposito
ETL Developer
Information Services Programming
Region 10 Education Service Center
400 E Spring Valley Rd
Richardson, TX 75081
(Ph) 972-348-1284
(Fax) 972-348-1285
Reveal Data Analytics



-----Original Message-----
From: Tony Esposito
Sent: Thursday, September 13, 2012 10:01 AM
To: 'unixodbc-support at mailman.unixodbc.org.'
Cc: 'tony.esposito at region10.org'; 'freetds at lists.ibiblio.org'
Subject: FW: [freetds] osql and isql -- how to test ODBC config?
Importance: High

Cc: unixODBC mailing list for assistance.

FreeTDS and unixODBC installed on CentOS 5.6. FreeTDS seems to work fine -- see below -- yet while isql connects it does not return data. Yet isql 'help' does return system table data.
Any help would be greatly appreciated!
Thanks.

Tony Esposito
ETL Developer
Information Services Programming
Region 10 Education Service Center
400 E Spring Valley Rd
Richardson, TX 75081
(Ph) 972-348-1284
(Fax) 972-348-1285
Reveal Data Analytics



-----Original Message-----
From: Tony Esposito
Sent: Thursday, September 13, 2012 9:35 AM
To: freetds at lists.ibiblio.org
Cc: 'tony.esposito at region10.org'
Subject: RE: [freetds] osql and isql -- how to test ODBC config?
Importance: High

Here's what I found out:

(1) tsql connects and returns data fine.
(2) fisql connects and returns data fine.

So it appears the FreeTDS client is setup correctly but...

isql connects yet returns no data when doing a SELECT (see error below):

isql -v txeis dba sql
+---------------------------------------+
| Connected! |
| |
| sql-statement |
| help [tablename] |
| quit |
| |
+---------------------------------------+
SQL> select * from rsccc.sr_stu_demo
SQLRowCount returns -1
SQL>

Yet isql will return system table information via the 'help' command (see SQL below):

+---------------------------------------+
| Connected! |
| |
| sql-statement |
| help [tablename] |
| quit |
| |
+---------------------------------------+
SQL> help rsccc.sr_stu_demo
+---------------------------------------------------------------------------------------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------+--------+---------------------------------------------------------------------------------------------------------------------------------+------------+------------+-------+-------+---------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------+-------------+
| TABLE_QUALIFIER | TABLE_OWNER | TABLE_NAME | COLUMN_NAME | TYPE_ID| TYPE_NAME | PRECISION | LENGTH | SCALE | RADIX | NULLABLE| REMARKS | SS_DOMAIN_ID| COLUMN_ID |
+---------------------------------------------------------------------------------------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------+--------+---------------------------------------------------------------------------------------------------------------------------------+------------+------------+-------+-------+---------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------+-------------+
+---------------------------------------------------------------------------------------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------+--------+---------------------------------------------------------------------------------------------------------------------------------+------------+------------+-------+-------+---------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Please advise.

-----Original Message-----
From: freetds-bounces at lists.ibiblio.org [mailto:freetds-bounces at lists.ibiblio.org] On Behalf Of James K. Lowden
Sent: Thursday, September 13, 2012 1:44 AM
To: freetds at lists.ibiblio.org
Subject: Re: [freetds] osql and isql -- how to test ODBC config?

On Tue, 11 Sep 2012 22:18:39 +0000

Post by Tony Esposito
osql: warning: no DNS hostname found for "207.235.236.120"

...

Post by Tony Esposito
The osql tool stops because of this 'warning' so I never get connected
and therefore never verify my ODBC setup.

The script shouldn't stop there because of that. Check the version you're using; revision 1.12 doesn't exit after producing that message on line 342. In fact, the only check remaining after that is that the hostname has an address. If so, it prints what it found out and starts isql.

Perhaps something in your environment causes the shell to terminate the script when host(1) returns nonzero status? I'd be interested it hear what you find out.

--jkl
_______________________________________________
FreeTDS mailing list
FreeTDS at lists.ibiblio.org
http://lists.ibiblio.org/mailman/listinfo/freetds


-----
No virus found in this message.
Checked by AVG - www.avg.com
Version: 2012.0.2221 / Virus Database: 2437/5266 - Release Date: 09/13/12