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