FreeTDS Development Group

["James K. Lowden" <jklowden AT freetds.org>]

James K. Lowden wrote:
> 
> I'm trying to send the contents of a file as a parameter to a stored
> procedure using latest DBD::ODBC and CVS HEAD.  I can send COPYING.LIB
> (25,265 bytes).  I cannot send ChangeLog-2004 (99,533 bytes).  The
> script aborts without an error.  

DBD::ODBC 1.16 uses a maximum parameter buffer size of 32 KB.  Above that,
it uses data-at-execution (SQL_NEED_DATA).  SQLParamData in CVS HEAD fails
to work correctly.  The fix isn't obvious to me.  

I committed changes that make the log more obvious.  

The order of execution should be:

SQLExecute -> SQL_NEED_DATA
SQLParamData -> SQL_NEED_DATA (need part 1)
SQLPutData -> SQL_SUCCESS (send part 1)
SQLParamData -> SQL_NEED_DATA (need part N)
SQLPutData -> SQL_SUCCESS (send part N)
SQLParamData -> SQL_SUCCESS (1st param done)
SQLParamData -> SQL_NEED_DATA (2nd param, part 1)
SQLPutData -> SQL_SUCCESS (send part 1)
SQLParamData -> SQL_SUCCESS (2nd param done)
SQLParamData -> SQL_SUCCESS (all parameters done)

The important thing to understand is that SQLPutData cannot tell the
application whether or not all data have been received for a parameter. 
Only SQLParamData can do that.  SQLParamData must return SQL_SUCCESS
*twice* to assure the application that all paramater data have been sent. 


Also: SQLParamData should not return SQL_ERROR unless a SQLState can be
associated with the error.  I would say you can call SQLParamData
endlessly after it returns SQL_SUCCESS and it will keep returning
SQL_SUCCESS.  

http://msdn.microsoft.com/en-us/library/ms713824(VS.85).aspx

        "When the driver returns SQL_SUCCESS for SQLPutData, the application
calls SQLParamData again. SQLParamData returns SQL_NEED_DATA if more data
needs to be sent, in which case the application calls SQLPutData again. It
returns SQL_SUCCESS if all data-at-execution data has been sent. The
application then calls SQLParamData again. If the driver returns
SQL_NEED_DATA and another indicator in *ValuePtrPtr, it requires data for
another parameter or column and SQLPutData is called again. If the driver
returns SQL_SUCCESS, then all data-at-execution data has been sent and the
SQL statement can be executed or the SQLBulkOperations or SQLSetPos call
can be processed."  

AFAICT it's OK to call 

        SQLParamData, SQLPutData
        SQLParamData, SQLPutData 
        SQLParamData
or 
        SQLParamData, SQLPutData[, SQLPutData ...]
        SQLParamData

The above description follows the first sequence.  The example code on the
same page follows the second.  The first is of course more general. 

In my simpler test, I send all data with the first PUT, so I should see:

SQLExecute -> SQL_NEED_DATA
SQLParamData -> SQL_NEED_DATA
SQLPutData -> SQL_SUCCESS
SQLParamData -> SQL_SUCCESS (param done)
SQLParamData -> SQL_SUCCESS (all parameters done)

What I see instead is:

SQLExecute -> SQL_NEED_DATA
SQLParamData -> SQL_NEED_DATA
SQLPutData -> SQL_SUCCESS
SQLParamData -> SQL_ERROR (no message)

Why?  The logic in SQLParamData is quite odd:

        if (!stmt->param_data_called) {
                stmt->param_data_called = 1;
                *prgbValue = stmt->apd->records...
                ODBC_RETURN(stmt, SQL_NEED_DATA);
        }

"stmt->param_data_called" is broken by design: it's per-statement, not
per-parameter.  SQLParamData continues: 

        ++stmt->param_num;
        switch (res = parse_prepared_query(stmt, 1)) {
                                                ^^^ compute row?? 
        case SQL_NEED_DATA:
                *prgbValue = stmt->apd->records...
                ODBC_RETURN(stmt, SQL_NEED_DATA);
        case SQL_SUCCESS:
                return _SQLExecute(stmt);
        }

Why _SQLExecute?  This seems to be designed with something else in mind.  

Logic should be more like:

        do {
                if (!param->done) {
                        int need_data = 
                                curcol->column_size > 
                                curcol->column_cur_size;
                        if( need_data ) {
                                *prgbValue = stmt->apd->records...
                                return SQL_NEED_DATA;
                        } else {
                                param->done = 1;
                                return SQL_SUCCESS;
                        }
                }
        } while((param = next_data_exec_param()) != NULL);
        return SQL_SUCCESS;

and not call _SQLExecute(). 

I'm sure I could hack my way through this with an axe, but maybe there's
an easier way?  

Log snippet with DBI_TRACE=5 follows.  

--jkl

[log]
odbc.c:3318:SQLExecute(0x81fb800)
sql2tds.c:150:type=-1
sql2tds.c:156:trace
odbc.c:3332:SQLExecute returns SQL_NEED_DATA (start_parse_prepared_query
failed)
    dbd_st_execute (NEED DATA)...
odbc.c:5958:SQLParamData(0x81fb800, 0xbfbfeef0) [param_num 2,
param_data_called = 0]
odbc.c:6001:SQLParamData returns SQL_NEED_DATA
odbc.c:6011:SQLPutData(0x81fb800, 0x82a1000, 100000)
prepare_query.c:257:continue_parse_prepared_query with parameter 2
odbc.c:6019:SQLPutData returns SQL_SUCCESS, 0 bytes left
    dbd_st_execute (NEED DATA)...
odbc.c:5958:SQLParamData(0x81fb800, 0xbfbfeef0) [param_num 2,
param_data_called = 0]
odbc.c:6001:SQLParamData returns SQL_NEED_DATA
odbc.c:6011:SQLPutData(0x81fb800, 0x82a1000, 100000)
prepare_query.c:257:continue_parse_prepared_query with parameter 2
odbc.c:6019:SQLPutData returns SQL_SUCCESS, 0 bytes left
    dbd_st_execute (NEED DATA)...
odbc.c:5958:SQLParamData(0x81fb800, 0xbfbfeef0) [param_num 2,
param_data_called = 1]
odbc.c:6001:SQLParamData returns SQL_ERROR or SQL_NULL_DATA
error.c:613:SQLGetDiagRec(3, 0x81fb800, 1, 0xbfbfe900, 0xbfbfe8f8,
0xbfbfe910, 512, 0xbfbfe8fc)
error.c:618:SQLGetDiagRec returning 100
    !!dbd_error2(err_rc=-1, what=st_execute/SQLExecute,
handles=(8065000,81ff000,8201800)
    <- execute= undef at samples/odbc_rpc.pl line 125
    -> FETCH for DBD::ODBC::st (DBI::st=HASH(0x81cbcd8)~INNER 'Active')
thr#804e000
    .. FETCH DBI::st=HASH(0x81cbcd8) 'Active' = ''
    <- FETCH= '' at samples/odbc_rpc.pl line 129
    -> disconnect for DBD::ODBC::db (DBI::db=HASH(0x81dc5dc)~0x81dc558)
thr#804e000
odbc.c:4664:SQLGetConnectOption(0x81d8400, 102, 0xbfbfeef8)
odbc.c:4579:_SQLGetConnectAttr(0x81d8400, 102, 0xbfbfeef8, 256, 0x0)
odbc.c:2079:SQLDisconnect(0x81d8400)

[end log]