Re: Issue with SQLParamData and SQLPutData and image column

thanks for the new stable 0.91! :)

Now have nicer trace but my issue remains :|

odbc.c:3510:SQLExecute returns SQL_NEED_DATA (start_parse_prepared_query
failed)
odbc.c:6032:SQLParamData(0x88bdde0, 0xffd4cb70) [param_num 2,
param_data_called = 0]
odbc.c:6076:SQLParamData returns SQL_NEED_DATA
odbc.c:6087:SQLPutData(0x88bdde0, 0xffd44954, 58)
prepare_query.c:300:continue_parse_prepared_query with parameter 2
odbc.c:6095:SQLPutData returns SQL_SUCCESS, 0 bytes left
odbc.c:6032:SQLParamData(0x88bdde0, 0xffd4cb70) [param_num 2,
param_data_called = 1]
sql2tds.c:185:type=4
sql2tds.c:193:trace
odbc.c:6076:SQLParamData returns SQL_ERROR
error.c:517:SQLGetDiagRec(3, 0x88bdde0, 1, 0xffd447ce, 0xffd443a8,
0xffd445ce, 512, 0xffd443a4)
error.c:517:SQLGetDiagRec(3, 0x88bdde0, 1, 0xffd445f5, 0xffd445fc,
0xffd444f5, 256, 0xffd444f2)
error.c:517:SQLGetDiagRec(2, 0x88c0ee8, 1, 0xffd445f5, 0xffd445fc,
0xffd444f5, 256, 0xffd444f2)


On Thu, Aug 18, 2011 at 10:54 AM, Michel Chamberland <[email protected]>wrote:

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



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