Re: Clarification sought on execute_array

  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.

No doubt there is some thing awry in there That we will have to get to 
the bottom of.

Cheers
John
> 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