FreeTDS Development Group

[Michel Chamberland <mercjr AT gmail.com>]

Hello List,

After reading many posts from the mailing list, MSDN odbc documentation and
the freetds documentation, I am turning to the list for help. I have code
that used to work with the datadirect odbc drivers but I now want to start
using unixodbc with freetds. Everything looks fine until I run a SQLPutData
(and send all the data in one swoop since its only 27 bytes in my test
case). The problem I am having is that SQLPutData returns SQL_SUCCESS then
when I call SQLParamData to see if I need to send anything else, it returns
SQL_ERROR but if i try a GetDiagRec for the STMT, it doesn't report any
errors so I'm not sure what to fix. Stepping thru the code doesn't point to
anything interesting.. I am not looking for spoon feeding but any idea or
suggestions to help me solve this issue will be deeply appreciated!

*Here is a (very) simplified version of my code:*

snprintf(request, sizeof(request), " { ? =  call upload_file (
'testfile.txt', '2011-08-17 18:40', ? , ? ) }")
SQLPrepare (hstmt, (UCHAR *) request, SQL_NTS); // this returns SQL_SUCCESS

SQLBindParameter(hstmt, 1 ,SQL_PARAM_OUTPUT, SQL_C_SLONG, SQL_INTEGER, 0, 0,
&ProcRetVal, 0, NULL); // this returns SQL_SUCCESS
SQLBindParameter(hstmt, 2, SQL_PARAM_INPUT, SQL_C_BINARY,
SQL_LONGVARBINARY, 0, 0, (SQLPOINTER) 2, 0, &filedata ); // this
returns SQL_SUCCESS
SQLBindParameter(hstmt, 3 ,SQL_PARAM_INPUT_OUTPUT, SQL_C_SLONG, SQL_INTEGER,
0, 0, &DontWork, 0, NULL); // this returns SQL_SUCCESS

        filedata = SQL_LEN_DATA_AT_EXEC(blobsize);
        retcode = SQLExecute(hstmt); // this returns SQL_NEED_DATA
if (retcode ==  SQL_NEED_DATA)
 {
            while (retcode ==  SQL_NEED_DATA)
    {
      retcode = SQLParamData(hstmt, &pToken); // this returns SQL_NEED_DATA
on the first pass and then SQL_ERROR on the second pass
        extract_error("SQLParamData1"); <-- this does the GetDiagRec call
if (retcode ==  SQL_NEED_DATA)
 {
        while (read data from file)
          {
    SQLPutData(hstmt, Data, cbData) // this returns SQL_SUCCESS
          }

       }
        }

    }

SQLNumResultCols (hstmt, &numCols); // this returns SQL_SUCCESS
SQLFreeStmt (hstmt, SQL_CLOSE); // this returns SQL_SUCCESS

*here is the sproc signature:*

CREATE procedure [dbo].[upload_file]
@sFileName varchar(50),
 @dtFileLastModified datetime,
@imgBinary image,
@nResultCode int = 0 OUTPUT,
AS
BEGIN
...

*here is the trace:*

log.c:190:Starting log file for FreeTDS 0.82
        on 2011-08-17 14:40:54 with debug flags 0x4fff.
iconv.c:197:names for ISO-8859-1: ISO-8859-1
iconv.c:197:names for UTF-8: UTF-8
iconv.c:197:names for UCS-2LE: UCS-2LE
iconv.c:197:names for UCS-2BE: UCS-2BE
iconv.c:363:iconv to convert client-side data to the "ISO-8859-1" character
set
iconv.c:516:tds_iconv_info_init: converting "ISO-8859-1"->"UCS-2LE"
iconv.c:516:tds_iconv_info_init: converting "ISO-8859-1"->"UCS-2LE"
net.c:210:Connecting to 10.25.205.124 port 1433 (TDS version 8.0)
net.c:264:tds_open_socket: connect(2) returned "Operation now in progress"
net.c:303:tds_open_socket() succeeded
util.c:162:Changed query state from DEAD to IDLE
login.c:735:quietly sending TDS 7+ login packet
token.c:312:tds_process_login_tokens()
net.c:592:Received header
0000 04 01 01 7d 00 47 01 00-                        |...}.G..|

net.c:671:Received packet
<snip/>
0120 01 71 00 00 01 16 4d 00-69 00 63 00 72 00 6f 00 |.q....M. i.c.r.o.|
0130 73 00 6f 00 66 00 74 00-20 00 53 00 51 00 4c 00 |s.o.f.t.  .S.Q.L.|
0140 20 00 53 00 65 00 72 00-76 00 65 00 72 00 00 00 | .S.e.r. v.e.r...|
0150 00 00 0a 32 06 40 e3 13-00 04 04 34 00 30 00 39 |...2.@.. ...4.0.9|
0160 00 36 00 04 34 00 30 00-39 00 36 00 fd 00 00 00 |.6..4.0. 9.6.....|
0170 00 00 00 00 00         -                        |.....|

token.c:316:looking for login token, got  e3(ENVCHANGE)
token.c:108:tds_process_default_tokens() marker is e3(ENVCHANGE)
token.c:316:looking for login token, got  ab(INFO)
token.c:108:tds_process_default_tokens() marker is ab(INFO)
token.c:2451:tds_process_msg() reading message from server
token.c:2516:tds_process_msg() calling client msg handler
odbc.c:2032:msgno 5701 20003
token.c:2529:tds_process_msg() returning TDS_SUCCEED
token.c:316:looking for login token, got  e3(ENVCHANGE)
token.c:108:tds_process_default_tokens() marker is e3(ENVCHANGE)
token.c:2281:tds_process_env_chg(): 5 bytes of collation data received
token.c:2282:tds->collation was
0000 00 00 00 00 00         -                        |.....|

iconv.c:985:setting server single-byte charset to "CP1252"
iconv.c:516:tds_iconv_info_init: converting "ISO-8859-1"->"CP1252"
token.c:2292:tds->collation now
0000 09 04 d0 00 34         -                        |....4|

token.c:316:looking for login token, got  e3(ENVCHANGE)
token.c:108:tds_process_default_tokens() marker is e3(ENVCHANGE)
token.c:316:looking for login token, got  ab(INFO)
token.c:108:tds_process_default_tokens() marker is ab(INFO)
token.c:2451:tds_process_msg() reading message from server
token.c:2516:tds_process_msg() calling client msg handler
odbc.c:2032:msgno 5703 20003
token.c:2529:tds_process_msg() returning TDS_SUCCEED
token.c:316:looking for login token, got  ad(LOGINACK)
token.c:316:looking for login token, got  e3(ENVCHANGE)
token.c:108:tds_process_default_tokens() marker is e3(ENVCHANGE)
token.c:316:looking for login token, got  fd(DONE)
token.c:108:tds_process_default_tokens() marker is fd(DONE)
token.c:2201:tds_process_end: more_results = 0
                was_cancelled = 0
                error = 0
                done_count_valid = 0
token.c:2217:tds_process_end() state set to TDS_IDLE
token.c:2232:                rows_affected = 0
token.c:393:leaving tds_process_login_tokens() returning 1
odbc.c:4672:SQLGetFunctions: fFunction is 999
odbc.c:5587:SQLGetInfo(0x948c7a8, 77, 0xff9506b8, 20, (nil))
odbc.c:4841:_SQLGetInfo(0x948c7a8, 77, 0xff9506b8, 20, (nil)
odbc.c:5587:SQLGetInfo(0x948c7a8, 10000, 0xff9506b8, 5, (nil))
odbc.c:4841:_SQLGetInfo(0x948c7a8, 10000, 0xff9506b8, 5, (nil)
odbc.c:5918:SQLSetConnectOption(0x948c7a8, 0, 1800)
odbc.c:5827:_SQLSetConnectAttr(0x948c7a8, 0, 0x708, -3)
error.c:609:SQLGetDiagRec(2, 0x948c7a8, 1, 0xff9536e2, 0xff9532bc,
0xff9534e2, 512, 0xff9532b8)
error.c:510:_SQLGetDiagRec(2, 0x948c7a8, 1, 0xff9536e2, 0xff9532bc,
0xff9534e2, 512, 0xff9532b8)
error.c:609:SQLGetDiagRec(2, 0x948c7a8, 2, 0xff9536e2, 0xff9532bc,
0xff9534e2, 512, 0xff9532b8)
error.c:510:_SQLGetDiagRec(2, 0x948c7a8, 2, 0xff9536e2, 0xff9532bc,
0xff9534e2, 512, 0xff9532b8)
odbc.c:1504:SQLAllocStmt(0x948c7a8, 0x948e2b8)
odbc.c:1403:_SQLAllocStmt(0x948c7a8, 0x948e2b8)
odbc.c:4079:SQLGetStmtAttr(0x948dc98, 10010, 0xff9537a4, 4, (nil))
odbc.c:3917:_SQLGetStmtAttr(0x948dc98, 10010, 0xff9537a4, 4, (nil))
odbc.c:4079:SQLGetStmtAttr(0x948dc98, 10011, 0xff9537a4, 4, (nil))
odbc.c:3917:_SQLGetStmtAttr(0x948dc98, 10011, 0xff9537a4, 4, (nil))
odbc.c:4079:SQLGetStmtAttr(0x948dc98, 10012, 0xff9537a4, 4, (nil))
odbc.c:3917:_SQLGetStmtAttr(0x948dc98, 10012, 0xff9537a4, 4, (nil))
odbc.c:4079:SQLGetStmtAttr(0x948dc98, 10013, 0xff9537a4, 4, (nil))
odbc.c:3917:_SQLGetStmtAttr(0x948dc98, 10013, 0xff9537a4, 4, (nil))
odbc.c:4124:SQLPrepare(0x948dc98,  { ? = call upload_file ( 'testfile.txt',
'2011-08-17 18:40', ? , ? ) }, -3)
odbc.c:1224:SQLBindParameter(0x948dc98, 1, 4, -16, 4, 0, 0, 0xff953978, 0,
(nil))
odbc.c:1125:_SQLBindParameter(0x948dc98, 1, 4, -16, 4, 0, 0, 0xff953978, 0,
(nil))
odbc.c:1224:SQLBindParameter(0x948dc98, 2, 1, -2, -4, 0, 0, 0x2, 0,
0xff953984)
odbc.c:1125:_SQLBindParameter(0x948dc98, 2, 1, -2, -4, 0, 0, 0x2, 0,
0xff953984)
odbc.c:1224:SQLBindParameter(0x948dc98, 3, 2, -16, 4, 0, 0, 0xff953974, 0,
(nil))
odbc.c:1125:_SQLBindParameter(0x948dc98, 3, 2, -16, 4, 0, 0, 0xff953974, 0,
(nil))
*odbc.c:3237:SQLExecute(0x948dc98)*
*sql2tds.c:150:type=-4*
*sql2tds.c:156:trace*
*odbc.c:5764:SQLParamData(0x948dc98, 0xff953970)*
*odbc.c:5806:SQLPutData(0x948dc98, 0xff94b970, 29)*
*odbc.c:5764:SQLParamData(0x948dc98, 0xff953970)*
*sql2tds.c:150:type=4*
*sql2tds.c:156:trace*
*error.c:609:SQLGetDiagRec(3, 0x948dc98, 1, 0xff94b6ce, 0xff94b2a8,
0xff94b4ce, 512, 0xff94b2a4)*
*error.c:510:_SQLGetDiagRec(3, 0x948dc98, 1, 0xff94b6ce, 0xff94b2a8,
0xff94b4ce, 512, 0xff94b2a4)*
odbc.c:4100:SQLNumResultCols(0x948dc98, 0xff953992)
odbc.c:3848:SQLFreeStmt(0x948dc98, 0)
odbc.c:3769:_SQLFreeStmt(0x948dc98, 0, 0)
error.c:609:SQLGetDiagRec(3, 0x948dc98, 1, 0xff953ad6, 0xff9536b0,
0xff9538d6, 512, 0xff9536ac)
error.c:510:_SQLGetDiagRec(3, 0x948dc98, 1, 0xff953ad6, 0xff9536b0,
0xff9538d6, 512, 0xff9536ac)
odbc.c:3680:SQLFreeHandle(3, 0x0x948dc98)
odbc.c:3769:_SQLFreeStmt(0x948dc98, 1, 0)
odbc.c:2005:SQLDisconnect(0x948c7a8)
mem.c:563:tds_free_all_results()
util.c:162:Changed query state from IDLE to DEAD
odbc.c:3680:SQLFreeHandle(2, 0x0x948c7a8)
odbc.c:3706:_SQLFreeConnect(0x948c7a8)
odbc.c:3680:SQLFreeHandle(1, 0x0x948bbb8)
odbc.c:3744:_SQLFreeEnv(0x948bbb8)


Thanks for any help you may be able to provide!!
-Michel