On 27/11/2011 13:59, Martin J. Evans wrote:
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?
Something like I define execute_for_fetch but I could add a switch to
uninstall that method from DBI. That way the default would be to use it
giving me lots of feedback but for the cases where I've not got full
support it could be turned off. The problem I'm having is that binding
parameters (normally without arrays is full of special cases for drivers
and data types) and for people hitting these being able to turn it off
until I see how bad the situation is would get me more feedback.
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.
I'm a bit rusty on ODBC diagnostics. It turns out via SQLGetDiagField I
can probably associate an error with a parameter set - I'll investigate
this further.
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
Martin
