Skip to Content.
Sympa Menu

freetds - Re: [freetds] stored procedure call with OUTPUT params not working using ruby-odbc/unixodbc/freetds/mssql

freetds AT lists.ibiblio.org

Subject: FreeTDS Development Group

List archive

Chronological Thread  
  • From: Frediano Ziglio <freddy77 AT gmail.com>
  • To: FreeTDS Development Group <freetds AT lists.ibiblio.org>
  • Subject: Re: [freetds] stored procedure call with OUTPUT params not working using ruby-odbc/unixodbc/freetds/mssql
  • Date: Tue, 24 Mar 2009 17:05:26 +0100

2009/3/23 Chris Hall <christopher.k.hall AT gmail.com>:
> 24000 (0) [unixODBC][FreeTDS][SQL Server]Invalid cursor state
> odbc_test.rb:48:in `fetch_all'
>
> if i change that line as:
>
> res = []
> while stmt.more_results
>  res << stmt.fetch_all
> end
>
> I get past the cursor error, however, I'm not getting my output parameter
> now.
>
> Thanks for the help.
>
> new freetds.log
>
> 17:06:38.211759 99779 (log.c:190):Starting log file for FreeTDS 0.82
>        on 2009-03-23 17:06:38 with debug flags 0xffff.
> 17:06:38.211868 99779 (iconv.c:197):names for ISO-8859-1: ISO-8859-1
> 17:06:38.211884 99779 (iconv.c:197):names for UTF-8: UTF-8
> 17:06:38.211894 99779 (iconv.c:197):names for UCS-2LE: UCS-2LE
> 17:06:38.211903 99779 (iconv.c:197):names for UCS-2BE: UCS-2BE
> 17:06:38.211912 99779 (iconv.c:363):iconv to convert client-side data
> to the "ISO-8859-1" character set
> 17:06:38.211936 99779 (iconv.c:516):tds_iconv_info_init: converting
> "ISO-8859-1"->"UCS-2LE"
> 17:06:38.211953 99779 (iconv.c:516):tds_iconv_info_init: converting
> "ISO-8859-1"->"UCS-2LE"
> 17:06:38.211995 99779 (net.c:210):Connecting to 10.211.55.3 port 4444
> (TDS version 8.0)
> 17:06:38.212127 99779 (net.c:264):tds_open_socket: connect(2) returned
> "Operation now in progress"
> 17:06:38.214740 99779 (net.c:303):tds_open_socket() succeeded
> 17:06:38.214791 99779 (util.c:162):Changed query state from DEAD to IDLE
> 17:06:38.214809 99779 (login.c:735):quietly sending TDS 7+ login packet
> 17:06:38.214969 99779 (token.c:312):tds_process_login_tokens()
> 17:06:38.216317 99779 (net.c:592):Received header
> 0000 04 01 01 c9 00 36 01 00-                        |.....6..|
>
> 17:06:38.216368 99779 (net.c:671):Received packet
> 0000 e3 23 00 01 0a 41 00 64-00 50 00 6c 00 61 00 74 |.#...A.d .P.l.a.t|
> 0010 00 66 00 6f 00 72 00 6d-00 06 6d 00 61 00 73 00 |.f.o.r.m ..m.a.s.|
> 0020 74 00 65 00 72 00 ab 8e-00 45 16 00 00 02 00 29 |t.e.r... .E.....)|
> 0030 00 43 00 68 00 61 00 6e-00 67 00 65 00 64 00 20 |.C.h.a.n .g.e.d. |
> 0040 00 64 00 61 00 74 00 61-00 62 00 61 00 73 00 65 |.d.a.t.a .b.a.s.e|
> 0050 00 20 00 63 00 6f 00 6e-00 74 00 65 00 78 00 74 |. .c.o.n .t.e.x.t|
> 0060 00 20 00 74 00 6f 00 20-00 27 00 41 00 64 00 50 |. .t.o.  .'.A.d.P|
> 0070 00 6c 00 61 00 74 00 66-00 6f 00 72 00 6d 00 27 |.l.a.t.f .o.r.m.'|
> 0080 00 2e 00 18 43 00 48 00-52 00 49 00 53 00 48 00 |....C.H. R.I.S.H.|
> 0090 41 00 4c 00 4c 00 34 00-44 00 34 00 39 00 5c 00 |A.L.L.4. D.4.9.\.|
> 00a0 53 00 51 00 4c 00 45 00-58 00 50 00 52 00 45 00 |S.Q.L.E. X.P.R.E.|
> 00b0 53 00 53 00 00 01 00 e3-08 00 07 05 09 04 d0 00 |S.S..... ........|
> 00c0 34 00 e3 17 00 02 0a 75-00 73 00 5f 00 65 00 6e |4......u .s._.e.n|
> 00d0 00 67 00 6c 00 69 00 73-00 68 00 00 ab 8a 00 47 |.g.l.i.s .h.....G|
> 00e0 16 00 00 01 00 27 00 43-00 68 00 61 00 6e 00 67 |.....'.C .h.a.n.g|
> 00f0 00 65 00 64 00 20 00 6c-00 61 00 6e 00 67 00 75 |.e.d. .l .a.n.g.u|
> 0100 00 61 00 67 00 65 00 20-00 73 00 65 00 74 00 74 |.a.g.e.  .s.e.t.t|
> 0110 00 69 00 6e 00 67 00 20-00 74 00 6f 00 20 00 75 |.i.n.g.  .t.o. .u|
> 0120 00 73 00 5f 00 65 00 6e-00 67 00 6c 00 69 00 73 |.s._.e.n .g.l.i.s|
> 0130 00 68 00 2e 00 18 43 00-48 00 52 00 49 00 53 00 |.h....C. H.R.I.S.|
> 0140 48 00 41 00 4c 00 4c 00-34 00 44 00 34 00 39 00 |H.A.L.L. 4.D.4.9.|
> 0150 5c 00 53 00 51 00 4c 00-45 00 58 00 50 00 52 00 |\.S.Q.L. E.X.P.R.|
> 0160 45 00 53 00 53 00 00 01-00 ad 36 00 01 71 00 00 |E.S.S... ..6..q..|
> 0170 01 16 4d 00 69 00 63 00-72 00 6f 00 73 00 6f 00 |..M.i.c. r.o.s.o.|
> 0180 66 00 74 00 20 00 53 00-51 00 4c 00 20 00 53 00 |f.t. .S. Q.L. .S.|
> 0190 65 00 72 00 76 00 65 00-72 00 00 00 00 00 09 00 |e.r.v.e. r.......|
> 01a0 05 77 e3 13 00 04 04 34-00 30 00 39 00 36 00 04 |.w.....4 .0.9.6..|
> 01b0 34 00 30 00 39 00 36 00-fd 00 00 00 00 00 00 00 |4.0.9.6. ........|
> 01c0 00                     -                        |.|
>
> 17:06:38.216689 99779 (token.c:316):looking for login token, got  
> e3(ENVCHANGE)
> 17:06:38.216704 99779 (token.c:108):tds_process_default_tokens()
> marker is e3(ENVCHANGE)
> 17:06:38.216745 99779 (token.c:316):looking for login token, got  ab(INFO)
> 17:06:38.216756 99779 (token.c:108):tds_process_default_tokens()
> marker is ab(INFO)
> 17:06:38.216773 99779 (token.c:2451):tds_process_msg() reading message
> from server
> 17:06:38.216798 99779 (token.c:2516):tds_process_msg() calling client
> msg handler
> 17:06:38.216813 99779 (odbc.c:2032):msgno 5701 20003
> 17:06:38.216847 99779 (token.c:2529):tds_process_msg() returning TDS_SUCCEED
> 17:06:38.216858 99779 (token.c:316):looking for login token, got  
> e3(ENVCHANGE)
> 17:06:38.216867 99779 (token.c:108):tds_process_default_tokens()
> marker is e3(ENVCHANGE)
> 17:06:38.216903 99779 (token.c:2281):tds_process_env_chg(): 5 bytes of
> collation data received
> 17:06:38.216914 99779 (token.c:2282):tds->collation was
> 0000 00 00 00 00 00         -                        |.....|
>
> 17:06:38.216938 99779 (iconv.c:985):setting server single-byte charset
> to "CP1252"
> 17:06:38.216956 99779 (iconv.c:516):tds_iconv_info_init: converting
> "ISO-8859-1"->"CP1252"
> 17:06:38.216967 99779 (token.c:2292):tds->collation now
> 0000 09 04 d0 00 34         -                        |....4|
>
> 17:06:38.216978 99779 (token.c:316):looking for login token, got  
> e3(ENVCHANGE)
> 17:06:38.216988 99779 (token.c:108):tds_process_default_tokens()
> marker is e3(ENVCHANGE)
> 17:06:38.216999 99779 (token.c:316):looking for login token, got  ab(INFO)
> 17:06:38.217008 99779 (token.c:108):tds_process_default_tokens()
> marker is ab(INFO)
> 17:06:38.217018 99779 (token.c:2451):tds_process_msg() reading message
> from server
> 17:06:38.217029 99779 (token.c:2516):tds_process_msg() calling client
> msg handler
> 17:06:38.217038 99779 (odbc.c:2032):msgno 5703 20003
> 17:06:38.217051 99779 (token.c:2529):tds_process_msg() returning TDS_SUCCEED
> 17:06:38.217060 99779 (token.c:316):looking for login token, got  
> ad(LOGINACK)
> 17:06:38.217071 99779 (token.c:316):looking for login token, got  
> e3(ENVCHANGE)
> 17:06:38.217081 99779 (token.c:108):tds_process_default_tokens()
> marker is e3(ENVCHANGE)
> 17:06:38.217093 99779 (token.c:316):looking for login token, got  fd(DONE)
> 17:06:38.217102 99779 (token.c:108):tds_process_default_tokens()
> marker is fd(DONE)
> 17:06:38.217116 99779 (token.c:2201):tds_process_end: more_results = 0
>                was_cancelled = 0
>                error = 0
>                done_count_valid = 0
> 17:06:38.217127 99779 (token.c:2217):tds_process_end() state set to TDS_IDLE
> 17:06:38.217136 99779 (token.c:2232):                rows_affected = 0
> 17:06:38.217145 99779 (token.c:393):leaving tds_process_login_tokens()
> returning 1
> 17:06:38.217191 99779 (mem.c:563):tds_free_all_results()
> 17:06:38.217222 99779 (util.c:162):Changed query state from IDLE to QUERYING
> 17:06:38.217233 99779 (write.c:136):tds_put_string converting 19 bytes
> of "set textsize 64512 "
> 17:06:38.217244 99779 (write.c:164):tds_put_string wrote 38 bytes
> 17:06:38.217262 99779 (util.c:162):Changed query state from QUERYING to
> PENDING
> 17:06:38.217272 99779 (net.c:779):Sending packet
> 0000 01 01 00 2e 00 00 00 00-73 00 65 00 74 00 20 00 |........ s.e.t. .|
> 0010 74 00 65 00 78 00 74 00-73 00 69 00 7a 00 65 00 |t.e.x.t. s.i.z.e.|
> 0020 20 00 36 00 34 00 35 00-31 00 32 00 20 00       | .6.4.5. 1.2. .|
>
> 17:06:38.217340 99779 (token.c:495):tds_process_tokens(0x101e0f0,
> 0xbfffd26c, 0xbfffd268, 0x100)
> 17:06:38.217353 99779 (util.c:162):Changed query state from PENDING to
> READING
> 17:06:38.217687 99779 (net.c:592):Received header
> 0000 04 01 00 11 00 36 01 00-                        |.....6..|
>
> 17:06:38.217730 99779 (net.c:671):Received packet
> 0000 fd 00 00 be 00 00 00 00-00                      |........ .|
>
> 17:06:38.217745 99779 (token.c:510):processing result tokens.  marker
> is  fd(DONE)
> 17:06:38.217761 99779 (token.c:2201):tds_process_end: more_results = 0
>                was_cancelled = 0
>                error = 0
>                done_count_valid = 0
> 17:06:38.217772 99779 (token.c:2217):tds_process_end() state set to TDS_IDLE
> 17:06:38.217782 99779 (util.c:162):Changed query state from READING to IDLE
> 17:06:38.217791 99779 (token.c:2232):                rows_affected = 0
> 17:06:38.217801 99779 (util.c:110):logic error: cannot change query
> state from IDLE to PENDING
> 17:06:38.217851 99779 (token.c:495):tds_process_tokens(0x101e0f0,
> 0xbfffd26c, 0xbfffd268, 0x100)
> 17:06:38.217862 99779 (token.c:498):tds_process_tokens() state is COMPLETED
> 17:06:38.217904 99779 (odbc.c:4672):SQLGetFunctions: fFunction is 999
> 17:06:38.217927 99779 (odbc.c:5587):SQLGetInfo(0x802a00, 77,
> 0xbfffd588, 20, 0x0)
> 17:06:38.217938 99779 (odbc.c:4841):_SQLGetInfo(0x802a00, 77,
> 0xbfffd588, 20, 0x0
> 17:06:38.217970 99779 (error.c:609):SQLGetDiagRec(2, 0x802a00, 1,
> 0xbfffd1de, 0xbfffd1ec, 0xbfffcfde, 512, 0xbfffd1e8)
> 17:06:38.217983 99779 (error.c:510):_SQLGetDiagRec(2, 0x802a00, 1,
> 0xbfffd1de, 0xbfffd1ec, 0xbfffcfde, 512, 0xbfffd1e8)
> 17:06:38.218041 99779 (odbc.c:1504):SQLAllocStmt(0x802a00, 0x82c410)
> 17:06:38.218053 99779 (odbc.c:1403):_SQLAllocStmt(0x802a00, 0x82c410)
> 17:06:38.218074 99779 (odbc.c:4079):SQLGetStmtAttr(0x101daa0, 10010,
> 0xbfffe7cc, 4, 0x0)
> 17:06:38.218085 99779 (odbc.c:3917):_SQLGetStmtAttr(0x101daa0, 10010,
> 0xbfffe7cc, 4, 0x0)
> 17:06:38.218097 99779 (odbc.c:4079):SQLGetStmtAttr(0x101daa0, 10011,
> 0xbfffe7cc, 4, 0x0)
> 17:06:38.218107 99779 (odbc.c:3917):_SQLGetStmtAttr(0x101daa0, 10011,
> 0xbfffe7cc, 4, 0x0)
> 17:06:38.218118 99779 (odbc.c:4079):SQLGetStmtAttr(0x101daa0, 10012,
> 0xbfffe7cc, 4, 0x0)
> 17:06:38.218128 99779 (odbc.c:3917):_SQLGetStmtAttr(0x101daa0, 10012,
> 0xbfffe7cc, 4, 0x0)
> 17:06:38.218139 99779 (odbc.c:4079):SQLGetStmtAttr(0x101daa0, 10013,
> 0xbfffe7cc, 4, 0x0)
> 17:06:38.218149 99779 (odbc.c:3917):_SQLGetStmtAttr(0x101daa0, 10013,
> 0xbfffe7cc, 4, 0x0)
> 17:06:38.218174 99779 (odbc.c:4124):SQLPrepare(0x101daa0, {call
> dbo.up_TestSP4(?, ?)}, -3)
> 17:06:38.218223 99779 (odbc.c:795):SQLNumParams(0x101daa0, 0xbfffe7ec)
> 17:06:38.218258 99779 (error.c:609):SQLGetDiagRec(3, 0x101daa0, 1,
> 0xbfffe22e, 0xbfffe23c, 0xbfffe02e, 512, 0xbfffe238)
> 17:06:38.218270 99779 (error.c:510):_SQLGetDiagRec(3, 0x101daa0, 1,
> 0xbfffe22e, 0xbfffe23c, 0xbfffe02e, 512, 0xbfffe238)
> 17:06:38.218306 99779 (error.c:609):SQLGetDiagRec(3, 0x101daa0, 1,
> 0xbfffe22e, 0xbfffe23c, 0xbfffe02e, 512, 0xbfffe238)
> 17:06:38.218318 99779 (error.c:510):_SQLGetDiagRec(3, 0x101daa0, 1,
> 0xbfffe22e, 0xbfffe23c, 0xbfffe02e, 512, 0xbfffe238)
> 17:06:38.218371 99779 (odbc.c:3848):SQLFreeStmt(0x101daa0, 0)
> 17:06:38.218382 99779 (odbc.c:3769):_SQLFreeStmt(0x101daa0, 0, 0)
> 17:06:38.218393 99779 (odbc.c:3848):SQLFreeStmt(0x101daa0, 3)
> 17:06:38.218403 99779 (odbc.c:3769):_SQLFreeStmt(0x101daa0, 3, 0)
> 17:06:38.218419 99779 (odbc.c:1224):SQLBindParameter(0x101daa0, 1, 1,
> 4, 12, 10, 0, 0x101e96c, 4, 0x101e95c)
> 17:06:38.218431 99779 (odbc.c:1125):_SQLBindParameter(0x101daa0, 1, 1,
> 4, 12, 10, 0, 0x101e96c, 4, 0x101e95c)
> 17:06:38.218444 99779 (odbc.c:1224):SQLBindParameter(0x101daa0, 2, 4,
> 4, 12, 0, 0, 0x101ec70, 32, 0x101e9a4)

This way ruby is binding SQL_C_LONG and SQL_VARCHAR while should bind
with SQL_C_LONG and SQL_INTEGER to get proper value.

> 17:06:38.218456 99779 (odbc.c:1125):_SQLBindParameter(0x101daa0, 2, 4,
> 4, 12, 0, 0, 0x101ec70, 32, 0x101e9a4)
> 17:06:38.218482 99779 (odbc.c:3237):SQLExecute(0x101daa0)
> 17:06:38.218502 99779 (sql2tds.c:150):type=12
> 17:06:38.218512 99779 (sql2tds.c:156):trace
> 17:06:38.218564 99779 (sql2tds.c:150):type=12
> 17:06:38.218575 99779 (sql2tds.c:156):trace
> 17:06:38.218584 99779 (odbc.c:2965):_SQLExecute(0x101daa0)
> 17:06:38.218594 99779 (odbc.c:2970):_SQLExecute() starting with state 0
> 17:06:38.218618 99779 (mem.c:563):tds_free_all_results()
> 17:06:38.218628 99779 (util.c:162):Changed query state from IDLE to QUERYING
> 17:06:38.218644 99779 (query.c:1292):tds_put_data_info putting param_name
> 17:06:38.218655 99779 (query.c:1326):tds_put_data_info putting status
> 17:06:38.218665 99779 (query.c:1420):tds_put_data: colsize = 1
> 17:06:38.218674 99779 (query.c:1449):tds_put_data: not null param
> varint_size = 2
> 17:06:38.218684 99779 (query.c:1292):tds_put_data_info putting param_name
> 17:06:38.218694 99779 (query.c:1326):tds_put_data_info putting status
> 17:06:38.218703 99779 (query.c:1420):tds_put_data: colsize = -1
> 17:06:38.218712 99779 (query.c:1423):tds_put_data: null param
> 17:06:38.218721 99779 (util.c:162):Changed query state from QUERYING to
> PENDING
> 17:06:38.218731 99779 (net.c:779):Sending packet
> 0000 03 01 00 41 00 00 01 00-0e 00 64 00 62 00 6f 00 |...A.... ..d.b.o.|
> 0010 2e 00 75 00 70 00 5f 00-54 00 65 00 73 00 74 00 |..u.p._. T.e.s.t.|
> 0020 53 00 50 00 34 00 00 00-00 00 a7 0a 00 09 04 d0 |S.P.4... ........|
> 0030 00 34 01 00 31 00 01 a7-01 00 09 04 d0 00 34 ff |.4..1... ......4.|
> 0040 ff                     -                        |.|
>
> 17:06:38.218838 99779 (token.c:495):tds_process_tokens(0x101e0f0,
> 0xbfffe7fc, 0xbfffe7f8, 0x6914)
> 17:06:38.218853 99779 (util.c:162):Changed query state from PENDING to
> READING
> 17:06:38.219144 99779 (net.c:592):Received header
> 0000 04 01 00 33 00 36 01 00-                        |...3.6..|
>
> 17:06:38.219189 99779 (net.c:671):Received packet
> 0000 ff 11 00 c1 00 01 00 00-00 79 00 00 00 00 ac 11 |........ .y......|
> 0010 00 00 01 00 00 00 00 a7-01 00 09 04 d0 00 34 01 |........ ......4.|
> 0020 00 2a fe 00 00 e0 00 00-00 00 00                |.*...... ...|
>

Here server reply with a "*"... don't ask me why...


...

freddy77




Archive powered by MHonArc 2.6.24.

Top of Page