Re: Implementing execute_for_fetch in DBD::ODBC

Sorry, I missed off my proposal for the change to execute_for_fetch spec 
- see below:

On 27/11/2011 13:36, Martin J. Evans wrote:
> 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 change I propose is the tuple_status array currently contains a rows 
affected per tuple or -1 (for unknown - which is what DBD::Oracle does). 
I propose adding -2 meaning tuple in error (for when I cannot attribute 
an exact error string - see below) and -3 SQL_PARAM_UNUSED - this tuple 
was not used. It is important for me as some ODBC drivers can error on 
any tuple but continue but because individual  error records are not 
available per tuple (see below) this is the only way to know which tuple 
failed if more than one tuple fails. Also, some drivers will stop on a 
tuple failure so SQL_PARAM_UNUSED would indicate this. The second one 
(-3) might not be necessary as tuples_executed could probably indicate 
this i.e., 5 tuples provided, 4 executed if tuple  4 errored and tuple 5 
was not executed.

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