Implementing execute_for_fetch in DBD::ODBC

Hi,

I've been trying to implement execute_for_fetch in DBD::ODBC with some 
success. However, it is very basic right now and only does:

o binds everything as a string in SQLBindParameter and hence relies on 
driver/database being able to convert a string to the native column 
type. Probably not as big a problem as it sounds as DBD::ODBC used to do 
this all the time without problems.

o cannot support lobs

o doesn't do unicode yet

If I understand correctly, as soon as I create execute_for_fetch, DBI 
will use it - yes? If this happens it is possible I could break existing 
code using DBD::ODBC which uses execute_array/execute_for_fetch unless I 
can implement everything I do when binding parameters normally. Is there 
are way around this? i.e., is there some way to offer execute_for_fetch 
(as I have it now) so people can try it without breaking anyone who uses 
execute_for_fetch/execute_array now?

The DBI spec says

"If \@tuple_status is passed then the execute_for_fetch method uses it 
to return status information. The tuple_status array holds one element 
per tuple. If the corresponding execute() did not fail then the element 
holds the return value from execute(), which is typically a row count. 
If the execute() did fail then the element holds a reference to an array 
containing ($sth->err, $sth->errstr, $sth->state)."

In ODBC, you set a SQL_ATTR_PARAM_STATUS_ARRAY attribute and it contains 
a status for each row - the status is:

SQL_PARAM_SUCCESS - row successfully processed
SQL_PARAM_SUCCESS_WITH_INFO - processed but there is some infomrational
SQL_PARAM_ERROR - error on row
SQL_PARAM_UNUSED - row not used - possibly because a previous row failed
SQL_PARAM_IGNORE - not relevant here as DBD::ODBC never sets the 
PARAM_OPERATIONS_ARRAY
SQL_PARAM_DIAG_UNAVAILABLE

As far as I am aware there is no way for ODBC to obtain a rows affected 
per tuple. I can however, return the above which at least tells you 
which tuples were used or errored. Would this be ok, and if so, would 
anyone mind if I amended the DBI spec to allow for this. As far as I am 
aware, only DBD::Oracle does execute_for_fetch.

The second issue is that for either row or column wise binding in ODBC I 
need to know the longest length or each parameter. In column-wise 
binding (the one most used in ODBC) the parameters are bound like this:

p1_row1|p1_row2|p_row3

the size in bytes between each parameter value must be exactly the same 
as that is specified to the driver to allow it to walk the parameters in 
the block of memory assigned to the parameters. This is someone 
annoying. Same issue exists in row-wise binding. Working this out slows 
the execute_for_fetch down considerably.

The third issue is I'm not sure right now if I can apportion any errors 
to each tuple correctly after any tuple fails. Basically, after all the 
tuples are executed and some fail I have a stack of errors but there 
could be more than one error per tuple and there is no way to know that. 
e.g., if you have the tuples:

(1,2,3,'data too long', 5)

which is inserted into a table with a varchar(2) I can generate:

4 rows were affected
5 tuples were executed
tuple status $VAR1 = [
           0,
           0,
           0,
           [
             8152,
             '[Microsoft][ODBC SQL Server Driver][SQL Server]String or 
binary data would be truncated.',
             '22001'
           ],
           0
         ];

However, this only works because only one tuple failed. Basically, I can 
loop through the PARAM_STATUS_ARRAY (one value for each tuple) and as 
soon as I find one in error I can go and get a error - but I cannot know 
how many error records I retrieve apply to the tuple I'm setting so I 
have little choice to do something like this:

err_record_no = 1;
@param_status contains one status as above per tuple
foreach (my $row = 0; $row < scalar(@tuples); $row++) {
   if (($param_status[$row] == SQL_PARAM_SUCCESS) ||
        ($param_status[$row] == SQL_PARAM_UNUSED)) {
        $tuple_status[$row] = $param_status[$row];
   } else {
      # get error record err_record_no then increment err_record_no
     $tuple_status[$row] = [$native, $msg, $state]'
   }
}

If 2 tuples, failed, 2 error records would be retrieved but as in this 
case the second error would probably be:

DIAG [01000] [Microsoft][ODBC SQL Server Driver][SQL Server]The 
statement has been terminated. (3621)

because the first tuple which failed generated 2 errors. I guess this is 
just a limitation of ODBC.

Any comments?

Martin