Re: FreeTDS and ODBC

Michal Seliga <[email protected]>
Newsgroups gmane.comp.db.tds.freetds
Message-ID <[email protected]> 
now really with files attached

Michal Seliga wrote:
> hi james, thank you for an idea
> 
> i was experimenting with some more queries (i was doing comparison between
> freetds, iodbc and unixodbc tools) and i found one which shows problems even sooner
> 
> 'select count(*) from customer' returns correct result when launched from tsql
> only, but returns nothing from iodbctest or isql (or qt application). in next i
> will be using only iodbc because this is what i have to use in future anyway and
> its lowest level which already shows any sign of problems
> 
> attached are 3 files
> 
> freetds_tsql.log - eberything worked as it should, i tried to use this log in
> comparison with one i got when i used iodbctest, but they seem to differ too much
> 
> iodbc_odbctrace.log - problem can be found in stage when SQLFetchScroll was
> called and it returned SQL_NO_DATA_FOUND instead of data
> 
> freetds_iodbctest.log - i don't know if i am reading this log correctly, its not
> so selfexplanatory as one from iodbc, and it differs too much from log i got
> when i used tsql. i don't know what's exactly is wrong, could you please help me
> again?
> 
> i clearly see that even first packet sent differs, so it might be actually
> problem that freedts doesn't cooperate with iodbc as it should? and as result it
> sometimes works and sometimes not?
> 
> 
> 
> 
> 
> 
> 
> 
> 
> James K. Lowden wrote:
>> Michal Seliga wrote:
>>> i try to connect to microsoft sql 2000 database which runs on another
>>> machine
>>>
>>> - tsql from freedts works (both using -S and configfile or using -H -p
>>> directly) - iodbctest works (using DSN with ServerName)
>>> - qt linked to iodbc library doesn't work  (every query returns empty
>>> resultset) - connection to database using isql (unixodbc) doesn't work -
>>> some queries work (for example select count(*) from sometable) , some
>>> return empty resultsets (for example select textcolumn from sometable)
>> It sounds to me like some upper layer is dropping your data.  That could
>> be because it's broken, or because it expects the ODBC driver to do
>> something it's not doing, or is doing differently.  
>>
>> TDSDUMP is your friend.  Make sure the data are being returned by the
>> server, and watch for errors that might not be shown by the qt layer.  
>>
>> HTH.
>>
>> --jkl
>> _______________________________________________
>> FreeTDS mailing list
>> [email protected]
>> http://lists.ibiblio.org/mailman/listinfo/freetds
>>
> _______________________________________________
> FreeTDS mailing list
> [email protected]
> http://lists.ibiblio.org/mailman/listinfo/freetds
>

_______________________________________________
FreeTDS mailing list
[email protected]
http://lists.ibiblio.org/mailman/listinfo/freetds
freetds_tsql.log (text/plain, 2.9 KB) 
util.c:162:Changed query state from IDLE to QUERYING
write.c:136:tds_put_string converting 30 bytes of "select count(*) from customer
"
write.c:164:tds_put_string wrote 60 bytes
util.c:162:Changed query state from QUERYING to PENDING
net.c:779:Sending packet
0000 01 01 00 44 00 00 01 00-73 00 65 00 6c 00 65 00 |...D.... s.e.l.e.|
0010 63 00 74 00 20 00 63 00-6f 00 75 00 6e 00 74 00 |c.t. .c. o.u.n.t.|
0020 28 00 2a 00 29 00 20 00-66 00 72 00 6f 00 6d 00 |(.*.). . f.r.o.m.|
0030 20 00 63 00 75 00 73 00-74 00 6f 00 6d 00 65 00 | .c.u.s. t.o.m.e.|
0040 72 00 0a 00            -                        |r...|

token.c:495:tds_process_tokens(0x9ed1260, 0xbfb3776c, (nil), 0x6914)
util.c:162:Changed query state from PENDING to READING
net.c:592:Received header
0000 04 01 00 21 00 33 01 00-                        |...!.3..|

net.c:671:Received packet
0000 81 01 00 00 00 01 00 26-04 00 d1 04 84 cb 00 00 |.......& ........|
0010 fd 10 00 c1 00 01 00 00-00                      |........ .|

token.c:510:processing result tokens.  marker is  81(TDS7_RESULT)
token.c:1520:processing TDS7 result metadata.
mem.c:563:tds_free_all_results()
token.c:1544:set current_results (1 column) to tds->res_info
token.c:1555:setting up column 0
token.c:1490:tds7_get_data_info: 
	colname =  (0 bytes)
	type = 38 (integer-null)
	server's type = 38 (integer-null)
	column_varint_size = 1
	column_size = 4 (4 on server)
util.c:162:Changed query state from READING to PENDING
token.c:495:tds_process_tokens(0x9ed1260, 0xbfb3776c, (nil), 0x6914)
util.c:162:Changed query state from PENDING to READING
token.c:510:processing result tokens.  marker is  d1(ROW)
token.c:621:tds_process_tokens::SET_RETURN stopping on current token
util.c:162:Changed query state from READING to PENDING
token.c:495:tds_process_tokens(0x9ed1260, 0xbfb3776c, (nil), 0x1508)
util.c:162:Changed query state from PENDING to READING
token.c:510:processing result tokens.  marker is  d1(ROW)
token.c:2166:tds_process_row(): reading column 0 
token.c:1915:tds_get_data: type 38, varint size 1
token.c:1975:tds_get_data(): wire column size is 4 
util.c:162:Changed query state from READING to PENDING
token.c:495:tds_process_tokens(0x9ed1260, 0xbfb3776c, (nil), 0x1508)
util.c:162:Changed query state from PENDING to READING
token.c:510:processing result tokens.  marker is  fd(DONE)
token.c:2201:tds_process_end: more_results = 0
		was_cancelled = 0
		error = 0
		done_count_valid = 1
token.c:2217:tds_process_end() state set to TDS_IDLE
util.c:162:Changed query state from READING to IDLE
token.c:2232:                rows_affected = 1
util.c:110:logic error: cannot change query state from IDLE to PENDING
token.c:495:tds_process_tokens(0x9ed1260, 0xbfb3776c, (nil), 0x6914)
token.c:498:tds_process_tokens() state is COMPLETED
mem.c:563:tds_free_all_results()
util.c:162:Changed query state from IDLE to DEAD
iodbc_odbctrace.log (text/plain, 3.8 KB) 
[000023.463424]
iodbctest       B7708B10 ENTER SQLPrepare
		SQLHSTMT          0x9449880
		SQLCHAR         * 0xbf982e9a
				  | select count(*) from customer            |
		SQLINTEGER        -3 (SQL_NTS)

[000023.463830]
iodbctest       B7708B10 EXIT  SQLPrepare with return code 0 (SQL_SUCCESS)
		SQLHSTMT          0x9449880
		SQLCHAR         * 0xbf982e9a
		SQLINTEGER        -3 (SQL_NTS)

[000023.463857]
iodbctest       B7708B10 ENTER SQLExecute
		SQLHSTMT          0x9449880

[000023.479834]
iodbctest       B7708B10 EXIT  SQLExecute with return code 0 (SQL_SUCCESS)
		SQLHSTMT          0x9449880

[000023.479863]
iodbctest       B7708B10 ENTER SQLNumResultCols
		SQLHSTMT          0x9449880
		SQLSMALLINT     * 0xbf981e98

[000023.479890]
iodbctest       B7708B10 EXIT  SQLNumResultCols with return code 0 (SQL_SUCCESS)
		SQLHSTMT          0x9449880
		SQLSMALLINT     * 0xbf981e98 (1)

[000023.479948]
iodbctest       B7708B10 ENTER SQLDescribeCol
		SQLHSTMT          0x9449880
		SQLUSMALLINT      1
		SQLCHAR         * 0xbf98429a
		SQLSMALLINT       50
		SQLSMALLINT     * 0x0
		SQLSMALLINT     * 0xbf981e96
		SQLULEN         * 0xbf981e8c
		SQLSMALLINT     * 0xbf981e94
		SQLSMALLINT     * 0xbf981e92

[000023.480007]
iodbctest       B7708B10 EXIT  SQLDescribeCol with return code 0 (SQL_SUCCESS)
		SQLHSTMT          0x9449880
		SQLUSMALLINT      1
		SQLCHAR         * 0xbf98429a
				  | (empty string)                           |
		SQLSMALLINT       50
		SQLSMALLINT     * 0x0
		SQLSMALLINT     * 0xbf981e96 (SQL_INTEGER)
		SQLULEN         * 0xbf981e8c (10)
		SQLSMALLINT     * 0xbf981e94 (0)
		SQLSMALLINT     * 0xbf981e92 (SQL_NO_NULLS)

[000023.480099]
iodbctest       B7708B10 ENTER SQLFetchScroll
		SQLHSTMT          0x9449880
		SQLUSMALLINT      1 (SQL_FETCH_NEXT)
		SQLLEN            1

[000023.480175]
iodbctest       B7708B10 EXIT  SQLFetchScroll with return code 100 (SQL_NO_DATA_FOUND)
		SQLHSTMT          0x9449880
		SQLUSMALLINT      1 (SQL_FETCH_NEXT)
		SQLLEN            1

[000023.480350]
iodbctest       B7708B10 ENTER SQLMoreResults
		SQLHSTMT          0x9449880

[000023.480414]
iodbctest       B7708B10 EXIT  SQLMoreResults with return code 100 (SQL_NO_DATA_FOUND)
		SQLHSTMT          0x9449880

[000023.480433]
iodbctest       B7708B10 ENTER SQLCloseCursor
		SQLHSTMT          0x9449880

[000023.480458]
iodbctest       B7708B10 EXIT  SQLCloseCursor with return code 0 (SQL_SUCCESS)
		SQLHSTMT          0x9449880

[000039.351433]
iodbctest       B7708B10 ENTER SQLCloseCursor
		SQLHSTMT          0x9449880

[000039.351519]
iodbctest       B7708B10 EXIT  SQLCloseCursor with return code 0 (SQL_SUCCESS)
		SQLHSTMT          0x9449880

[000039.351539]
iodbctest       B7708B10 ENTER SQLFreeHandle
		SQLSMALLINT       3 (SQL_HANDLE_STMT)
		SQLHSTMT          0x9449880

[000039.352032]
iodbctest       B7708B10 EXIT  SQLFreeHandle with return code -1 (SQL_ERROR)
		SQLSMALLINT       3 (SQL_HANDLE_STMT)
		SQLHSTMT          0x9449880

[000039.352058]
iodbctest       B7708B10 ENTER SQLDisconnect
		SQLHDBC           0x94497b8

[000039.352137]
iodbctest       B7708B10 EXIT  SQLDisconnect with return code 0 (SQL_SUCCESS)
		SQLHDBC           0x94497b8

[000039.352155]
iodbctest       B7708B10 ENTER SQLFreeHandle
		SQLSMALLINT       2 (SQL_HANDLE_DBC)
		SQLHDBC           0x94497b8

[000039.352197]
iodbctest       B7708B10 EXIT  SQLFreeHandle with return code 0 (SQL_SUCCESS)
		SQLSMALLINT       2 (SQL_HANDLE_DBC)
		SQLHDBC           0x94497b8

[000039.352218]
iodbctest       B7708B10 ENTER SQLFreeHandle
		SQLSMALLINT       1 (SQL_HANDLE_ENV)
		SQLHENV           0x9448e10

[000039.352238]
iodbctest       B7708B10 EXIT  SQLFreeHandle with return code 0 (SQL_SUCCESS)
		SQLSMALLINT       1 (SQL_HANDLE_ENV)
		SQLHENV           0x9448e10

** Trace finished on Thu Nov 26 11:13:54 2009
freetds_iodbctest.log (text/plain, 4.7 KB) 
util.c:162:Changed query state from IDLE to QUERYING
util.c:162:Changed query state from QUERYING to PENDING
net.c:779:Sending packet
0000 01 01 00 25 00 00 00 00-73 65 6c 65 63 74 20 63 |...%.... select c|
0010 6f 75 6e 74 28 2a 29 20-66 72 6f 6d 20 63 75 73 |ount(*)  from cus|
0020 74 6f 6d 65 72         -                        |tomer|

token.c:495:tds_process_tokens(0x97d1ee0, 0xbf8074bc, 0xbf8074b8, 0x6914)
util.c:162:Changed query state from PENDING to READING
net.c:592:Received header
0000 04 01 00 24 00 33 01 00-                        |...$.3..|

net.c:671:Received packet
0000 a0 01 00 00 a1 06 00 0d-00 01 00 26 04 d1 04 84 |........ ...&....|
0010 cb 00 00 fd 10 00 c1 00-01 00 00 00             |........ ....|

token.c:510:processing result tokens.  marker is  a0(COLNAME)
mem.c:563:tds_free_all_results()
token.c:510:processing result tokens.  marker is  a1(COLFMT)
token.c:977:processing result. type = 38(integer-null), varint_size 1
util.c:162:Changed query state from READING to PENDING
odbc.c:3269:odbc_process_tokens: tds_process_tokens returned 1
odbc.c:3270:    result_type=4049, TDS_DONE_COUNT=10, TDS_DONE_ERROR=0
odbc.c:3340:odbc_process_tokens: returning result_type 4049
odbc.c:3119:_SQLExecute: odbc_process_tokens returned result_type 4049
token.c:495:tds_process_tokens(0x97d1ee0, 0xbf8074bc, 0xbf8074b8, 0x6914)
util.c:162:Changed query state from PENDING to READING
token.c:510:processing result tokens.  marker is  d1(ROW)
token.c:621:tds_process_tokens::SET_RETURN stopping on current token
util.c:162:Changed query state from READING to PENDING
odbc.c:3269:odbc_process_tokens: tds_process_tokens returned 1
odbc.c:3270:    result_type=4040, TDS_DONE_COUNT=10, TDS_DONE_ERROR=0
odbc.c:3340:odbc_process_tokens: returning result_type 4040
odbc.c:3119:_SQLExecute: odbc_process_tokens returned result_type 4040
odbc.c:4100:SQLNumResultCols(0x97c3f48, 0xbf8074fe)
odbc.c:4100:SQLNumResultCols(0x97c3f48, 0xbf80758e)
odbc.c:1702:SQLDescribeCol(0x97c3f48, 1, 0xbf8074cc, 256, 0xbf8074ca, 0xbf8074c8, 0xbf8074bc, 0xbf8074c6, 0xbf8074c4)
odbc.c:1974:SQLColAttributes(0x97c3f48, 1, 8, (nil), 0, (nil), 0xbf8074ac)
odbc.c:1766:_SQLColAttribute(0x97c3f48, 1, 8, (nil), 0, (nil), 0xbf8074ac)
odbc.c:1807:odbc:SQLColAttribute: fDescType is 8
error.c:574:SQLError((nil), (nil), 0x97c3f48, 0xbf8072a6, 0xbf8072b4, 0xbf8072bc, 512, 0xbf8072ba)
error.c:510:_SQLGetDiagRec(3, 0x97c3f48, 1, 0xbf8072a6, 0xbf8072b4, 0xbf8072bc, 512, 0xbf8072ba)
error.c:574:SQLError((nil), 0x97d1bd0, (nil), 0xbf8072a6, 0xbf8072b4, 0xbf8072bc, 512, 0xbf8072ba)
error.c:510:_SQLGetDiagRec(2, 0x97d1bd0, 1, 0xbf8072a6, 0xbf8072b4, 0xbf8072bc, 512, 0xbf8072ba)
error.c:574:SQLError((nil), (nil), 0x97c3f48, 0xbf8072a6, 0xbf8072b4, 0xbf8072bc, 512, 0xbf8072ba)
error.c:510:_SQLGetDiagRec(3, 0x97c3f48, 1, 0xbf8072a6, 0xbf8072b4, 0xbf8072bc, 512, 0xbf8072ba)
error.c:574:SQLError((nil), 0x97d1bd0, (nil), 0xbf8072a6, 0xbf8072b4, 0xbf8072bc, 512, 0xbf8072ba)
error.c:510:_SQLGetDiagRec(2, 0x97d1bd0, 1, 0xbf8072a6, 0xbf8072b4, 0xbf8072bc, 512, 0xbf8072ba)
odbc.c:3848:SQLFreeStmt(0x9699628, 1)
odbc.c:3769:_SQLFreeStmt(0x9699628, 1, 0)
odbc.c:3848:SQLFreeStmt(0x97c3f48, 1)
odbc.c:3769:_SQLFreeStmt(0x97c3f48, 1, 0)
query.c:1934:tds_send_cancel: not in_cancel and not idle
query.c:1943:tds_send_cancel: sending cancel packet
net.c:779:Sending packet
0000 06 01 00 08 00 00 00 00-                        |........|

token.c:495:tds_process_tokens(0x97d1ee0, 0xbf80934c, (nil), 0x0)
util.c:162:Changed query state from PENDING to READING
token.c:510:processing result tokens.  marker is  d1(ROW)
token.c:2166:tds_process_row(): reading column 0 
token.c:1915:tds_get_data: type 38, varint size 1
token.c:1975:tds_get_data(): wire column size is 4 
token.c:510:processing result tokens.  marker is  fd(DONE)
token.c:2201:tds_process_end: more_results = 0
		was_cancelled = 0
		error = 0
		done_count_valid = 1
token.c:2232:                rows_affected = 1
net.c:592:Received header
0000 04 01 00 11 00 33 01 00-                        |.....3..|

net.c:671:Received packet
0000 fd 20 00 fd 00 00 00 00-00                      |. ...... .|

token.c:510:processing result tokens.  marker is  fd(DONE)
token.c:2201:tds_process_end: more_results = 0
		was_cancelled = 1
		error = 0
		done_count_valid = 0
token.c:2217:tds_process_end() state set to TDS_IDLE
util.c:162:Changed query state from READING to IDLE
token.c:2232:                rows_affected = 0
odbc.c:2005:SQLDisconnect(0x97d1bd0)
mem.c:563:tds_free_all_results()
util.c:162:Changed query state from IDLE to DEAD
odbc.c:3680:SQLFreeHandle(2, 0x0x97d1bd0)
odbc.c:3706:_SQLFreeConnect(0x97d1bd0)
odbc.c:3680:SQLFreeHandle(1, 0x0x97d0a60)
odbc.c:3744:_SQLFreeEnv(0x97d0a60)
lmpx.com only provides a reader for public news (NNTP) servers. It is not affiliated with the servers or forums shown here and is not responsible for the content of articles, which is written by their respective authors.