On 20/01/11 12:31, John Scoles wrote: > On 19/01/2011 5:25 PM, Martin J. Evans wrote: > Great stuff Martin I am sure we can clear this up. > > One thing I did notice was you never set the 'ora_array_chunk_size' which is > defaulting to 1000
> I wonder if running this with this value set to say 2 or 3 will see a change > in behaviour. Doesn't seem to make any difference which I'm quite pleased about really since I wouldn't want another factor introduced. Have you reproduced my result John? I am using InstantClient 11.2 and Oracle 11.1.0.6.0 > No doubt there is some thing awry in there That we will have to get to the > bottom of. > > Cheers > John I will try to step back a number of DBD::Oracle and Instant Client versions to see if the behaviour changes. Martin -- Martin J. Evans Easysoft Limited http://www.easysoft.com >> 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 >
