Re: Issue with SQLParamData and SQLPutData and image column

is there a valid case where a SQLParamData could return ERROR but
GetDiagRect returns NO_DATA? My understanding is that should not happen. Is
that correct?

[ODBC][639][1313679135.239507][SQLPutData.c][148]
                Entry:
                        Statement = 0x8179f10
                        Data = 0xfffa11a0
                        StrLen = 29
[ODBC][639][1313679135.239540][SQLPutData.c][309]
                Exit:[SQL_SUCCESS]
[ODBC][639][1313679135.239560][SQLParamData.c][166]
                Entry:
                        Statement = 0x8179f10
                        Value = 0xfffa91a0
[ODBC][639][1313679135.239605][SQLParamData.c][356]
                Exit:[SQL_ERROR]
                        Value = 0x2
[ODBC][639][1313679135.239673][SQLGetDiagRec.c][758]
                Entry:
                        Statement = 0x8179f10
                        Rec Number = 1
                        SQLState = 0xfffa1035
                        Native = 0xfffa103c
                        Message Text = 0xfffa0f35
                        Buffer Length = 256
                        Text Len Ptr = 0xfffa0f32
[ODBC][639][1313679135.239707][SQLGetDiagRec.c][795]
                Exit:[SQL_NO_DATA]

Thanks!
Michel
On Wed, Aug 17, 2011 at 3:09 PM, Michel Chamberland <[email protected]>wrote:

>
> 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
>
>
>
>


-- 
[email protected]
CEH, CCNA, CCNA Security, Security+, Network+, A+, Project+, MCP, CIW
Professional/JavaScript Specialist/Database Design Specialist