On 19/01/2011 11:47, Tim Bunce wrote:
I'm sorry to arrive here late. Seems I've been missing all the fun!
Thought you were in Paris so I didn't expect you to see this. It has
not been a lot of fun ;-)
Some observations:
- I think it's reasonable for execute_array() and execute_for_fetch()
to return an error (ie err() true and so trigger RaiseError etc.)
if execution of any of the tuples encountered an error.
The way I see it is that if some of the rows in the batch end up in
the table and some don't I'd expect a warning. However, if 1 or more
rows fail and no rows end up in the table I don't see any success so
I'd expect an error. In my example code, 1 row fails but no rows end
up successful so it is the latter.
It may be a change/bug in the Oracle libraries as I thought when you
execute a batch all the rows are executed no matter if some fail and
all successful ones end up in the table.
- That'll need a spec clarification and a clear warning in Changes.
- We should avoid breaking existing batch apps that use DBD::Oracle.
Agreed, but we've not got any working examples of anyone using
execute_array other than the one which started this off (and mine)
which was DBIx::Class and that was broken by DBD::Oracle/Oracle's
current behaviour. The big problem with the DBIx::Class code was
checking $sth->err which was 0 in this case because 0 indicates a
warning. However, DBD::Oracle contradicted itself since execute_array
returned undef (error) but then set a warning on err.
- I'm hopeful that the above change wouldn't. (John?)
So am I. If we could get the examples John has seen via his
DBD::Oracle maintainership or via Pythian customers I would be happy
to create test cases. We are running blind at the moment as we've not
got those solid examples of supposedly working code.
- We should review other database APIs that provide batch execution
in order to spec a reasonable common subset behaviour for the DBI.
Obviously ODBC and JDBC do batched statements. I might provide a JDBC
example but for now I've done an ODBC example (slightly more familiar
to me) - see below.
- Clearly we should bring DBD::Oracle, the DBI default behaviour, and
the
DBI spec into agreement with each other.
Exactly. This is really my main point. As it stands (and given it is
not a bug in Oracle) I see a nightmare for anyone trying to use
execute_array in a database neutral way as with DBI, all successful
rows are inserted and we know which ones failed and with DBD::Oracle
no rows are inserted (including the ones where there is no error) and
you cannot commit the good ones and it is difficult to know (if not
impossible) what really happened. This is not a dig at anyone in
particular as I added the array context execute_array to
DBI/DBD::Oracle but this is also why I suspect something has changed
in DBD::Oracle/Oracle.
- We *really* need a way to share tests across drivers.
Perhaps something like a separate DBI::TestSuite distro that the
DBI
and drivers could have as a prerequisite. That would contain
tests in
modules. The DBI and DBDs would have a test file that uses the
DBI::TestSuite module and calls a function that runs the tests.
This issue could provide the first test.
Tim.
I agree and I seem to remember a project to do something like this -
was it perhaps a google summer of code suggestion? But it is pretty
difficult and I think that puts a lot of people off. I briefly looked
at Test::Database so I could get more realistic test results for
DBD::ODBC but I ran in to a load of problems as Test::Database needs
some DBD methods writing and expects to be able to create a database
and in ODBC (via dozens of ODBC drivers) there is not single way to do
this. The gain was just not worth the pain for me. I'd be happy to
help someone do this but only in a minor way as right now I cannot
find the time to satisfy even half of my OS commitments (as an
example, I REALLY want to be able to set handle attributes on methods
in DBI [post from a week back] but I just cannot find time to do it -
something else is always cropping up).
Attached is a very rough and ready bit of C code (with little error
checking) that does batch inserts. You run it with something like:
./a.out 'DSN=mydsn;UID=username;PWD=password'
and it does batch inserts into a table called xtest that is defined as:
create table xtest(ky integer primary key, txt varchar(20))
It has 4 tests:
1. insert a batch successfully reading the parameter status array
(ArrayTupleStatus) to see what worked (autocommit)
2. insert a batch where 2 rows cannot be inserted because of a
duplicate key and with a parameter status array (autocommit)
3. insert a batch where 2 rows cannot be inserted because of a
duplicate key and without a parameter status array (autocommit)
4. insert a batch where 2 rows cannot be inserted with an explicit txn
and with a parameter status array
The code was run against the Easysoft ODBC Driver for MS SQL Server
but the same results are obtained when using the MS SQL Server driver
on Windows. No other ODBC attributes were changed (other than the ones
mentioned).
What it tells us is:
o SQL_SUCCESS is always returned when all the rows are inserted
o SQL_SUCCESS_WITH_INFO is returned if some of the rows were
successful but some were not
o it does not matter whether we provide a parameter status array
(ArrayTupleStatus) or not - the 2 results above stand i.e. even if the
ODBC driver cannot tell you which ones failed (because you did not
give a parameter status array) it still does the successful rows and
only returns SQL_SUCCESS_WITH_INFO if some failed.
o AutoCommit makes no difference - i.e., if auto commit is on or off
the end result is the same IF we commit afterwards.
The output from running the code is below. I apologise for the length
of the C code but this is just another example of what you can do in C
code you can do in 1/10 (or thereabouts) of the code in Perl.
$ ./a.out 'DSN=baugi;UID=sa;PWD=easysoft'
Successful batch AUTOCOMMIT with PARAMSTATUSARRAY
"delete from "xtest""
Setting bind by column
Setting Parameter Status Array Ptr
Setting Parameters Processed Ptr
Setting PARAMSETSIZE to 10
"insert into "xtest" (ky,txt) values(?,?)"
Inserting rows into table
SQLExecute=SUCCESS
RowCount=10
Param Status Array 0 = 0
Param Status Array 1 = 0
Param Status Array 2 = 0
Param Status Array 3 = 0
Param Status Array 4 = 0
Param Status Array 5 = 0
Param Status Array 6 = 0
Param Status Array 7 = 0
Param Status Array 8 = 0
Param Status Array 9 = 0
Params processed = 10
.
Resetting parameters
Closing statement
Clearing Parameter Status Array Ptr
Clearing Parameters Processed Ptr
0, this is row 0
1, this is row 1
2, this is row 2
3, this is row 3
4, this is row 4
5, this is row 5
6, this is row 6
7, this is row 7
8, this is row 8
9, this is row 9
Dropping Statement
Partially successful batch AUTOCOMMIT with PARAMSTATUSARRAY
"delete from "xtest""
Setting bind by column
Setting Parameter Status Array Ptr
Setting Parameters Processed Ptr
Setting PARAMSETSIZE to 10
"insert into "xtest" (ky,txt) values(?,?)"
Inserting rows into table
SQLExecute=SQL_SUCCESS_WITH_INFO
** Error from SQLExecute **
4 diagnostics found
** error: 23000:1:2627:[Easysoft][SQL Server Driver 10.0][SQL
Server]Violation o
f PRIMARY KEY constraint 'PK__xtest__3213D96D46D27B73'. Cannot insert
duplicate
key in object 'dbo.xtest'. **
** error: 23000:2:2627:[Easysoft][SQL Server Driver 10.0][SQL
Server]Violation o
f PRIMARY KEY constraint 'PK__xtest__3213D96D46D27B73'. Cannot insert
duplicate
key in object 'dbo.xtest'. **
** error: 01000:3:3621:[Easysoft][SQL Server Driver 10.0][SQL
Server]The stateme
nt has been terminated. **
** error: 01000:4:3621:[Easysoft][SQL Server Driver 10.0][SQL
Server]The stateme
nt has been terminated. **
** RowCount=8, expected 10 **
Param Status Array 0 = 0
Param Status Array 1 = 0
** Row 3 not executed, status=5**
** Row 4 not executed, status=5**
Param Status Array 4 = 0
Param Status Array 5 = 0
Param Status Array 6 = 0
Param Status Array 7 = 0
Param Status Array 8 = 0
Param Status Array 9 = 0
Params processed = 10
.
Resetting parameters
Closing statement
Clearing Parameter Status Array Ptr
Clearing Parameters Processed Ptr
0, this is row 0
1, this is row 1
4, this is row 4
5, this is row 5
6, this is row 6
7, this is row 7
8, this is row 8
9, this is row 9
Dropping Statement
Partially successful batch AUTOCOMMIT without PARAMSTATUSARRAY
"delete from "xtest""
Setting bind by column
Setting Parameters Processed Ptr
Setting PARAMSETSIZE to 10
"insert into "xtest" (ky,txt) values(?,?)"
Inserting rows into table
SQLExecute=SQL_SUCCESS_WITH_INFO
** Error from SQLExecute **
4 diagnostics found
** error: 23000:1:2627:[Easysoft][SQL Server Driver 10.0][SQL
Server]Violation o
f PRIMARY KEY constraint 'PK__xtest__3213D96D46D27B73'. Cannot insert
duplicate
key in object 'dbo.xtest'. **
** error: 23000:2:2627:[Easysoft][SQL Server Driver 10.0][SQL
Server]Violation o
f PRIMARY KEY constraint 'PK__xtest__3213D96D46D27B73'. Cannot insert
duplicate
key in object 'dbo.xtest'. **
** error: 01000:3:3621:[Easysoft][SQL Server Driver 10.0][SQL
Server]The stateme
nt has been terminated. **
** error: 01000:4:3621:[Easysoft][SQL Server Driver 10.0][SQL
Server]The stateme
nt has been terminated. **
** RowCount=8, expected 10 **
Params processed = 10
.
Resetting parameters
Closing statement
Clearing Parameter Status Array Ptr
Clearing Parameters Processed Ptr
0, this is row 0
1, this is row 1
4, this is row 4
5, this is row 5
6, this is row 6
7, this is row 7
8, this is row 8
9, this is row 9
Dropping Statement
Partially successful batch no AUTOCOMMIT WITH PARAMSTATUSARRAY
"delete from "xtest""
Setting bind by column
Setting Parameter Status Array Ptr
Setting Parameters Processed Ptr
Setting PARAMSETSIZE to 10
"insert into "xtest" (ky,txt) values(?,?)"
Inserting rows into table
SQLExecute=SQL_SUCCESS_WITH_INFO
** Error from SQLExecute **
4 diagnostics found
** error: 23000:1:2627:[Easysoft][SQL Server Driver 10.0][SQL
Server]Violation o
f PRIMARY KEY constraint 'PK__xtest__3213D96D46D27B73'. Cannot insert
duplicate
key in object 'dbo.xtest'. **
** error: 23000:2:2627:[Easysoft][SQL Server Driver 10.0][SQL
Server]Violation o
f PRIMARY KEY constraint 'PK__xtest__3213D96D46D27B73'. Cannot insert
duplicate
key in object 'dbo.xtest'. **
** error: 01000:3:3621:[Easysoft][SQL Server Driver 10.0][SQL
Server]The stateme
nt has been terminated. **
** error: 01000:4:3621:[Easysoft][SQL Server Driver 10.0][SQL
Server]The stateme
nt has been terminated. **
** RowCount=8, expected 10 **
Param Status Array 0 = 0
Param Status Array 1 = 0
** Row 3 not executed, status=5**
** Row 4 not executed, status=5**
Param Status Array 4 = 0
Param Status Array 5 = 0
Param Status Array 6 = 0
Param Status Array 7 = 0
Param Status Array 8 = 0
Param Status Array 9 = 0
Params processed = 10
.
Resetting parameters
Closing statement
Clearing Parameter Status Array Ptr
Clearing Parameters Processed Ptr
0, this is row 0
1, this is row 1
4, this is row 4
5, this is row 5
6, this is row 6
7, this is row 7
8, this is row 8
9, this is row 9
Dropping Statement
I'm not in a rush to provide a JDBC example as my experience is that
it will be pretty similar - I might if pushed hard.
What is not clear to me is what effect oci_mode = OCI_BATCH_ERRORS is
supposed to have. Also my current Oracle example seems to return a
SUCCESS_WITH_INFO when OCIExecute is called even though no rows are
committed. John seems to have a recollection that you can commit the
successful rows but I cannot duplicate it. We need real example usage
of execute_array for DBD::Oracle which worked as a test case.
Martin
