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
