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
#ifdef WIN32
# include <windows.h>
#endif
#include <stdio.h>
#include <sql.h>
#include <sqlext.h>
#include <stdlib.h>
#include <string.h>
#define ROWS 10 /* rows to insert in 1 batch */
long idata[ROWS]; /* integer parameter */
SQLCHAR cdata[ROWS][20]; /* string parameter */
static SQLRETURN do_create_rows(
SQLHDBC *hdbc,
char *table,
int duplicate_key,
int no_param_status);
static SQLRETURN do_a_error(
SQLSMALLINT type,
SQLHANDLE handle,
char *fn);
/* ./a.out 'DSN=baugi;UID=sa;PWD=easysoft' */
/* No proper error checking - just an example */
/* Needs a table create table xtest (ky int primary key, txt varchar(20)) */
main( int argc, char **argv )
{
SQLRETURN ret;
SQLHANDLE henv;
SQLHANDLE hstmt;
SQLHANDLE hdbc;
SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &henv);
/* we want ODBC 3.0 behaviour */
SQLSetEnvAttr(henv, SQL_ATTR_ODBC_VERSION,
(SQLPOINTER)SQL_OV_ODBC3, SQL_IS_UINTEGER);
SQLAllocHandle(SQL_HANDLE_DBC, henv, &hdbc);
ret = SQLDriverConnect(hdbc, NULL, argv[ 1 ], SQL_NTS,
NULL, 0, NULL, SQL_DRIVER_NOPROMPT);
if ( !SQL_SUCCEEDED( ret )) {
do_a_error(SQL_HANDLE_DBC, hdbc, "SQLDriverConnect conHandle");
exit(1);
}
printf("Successful batch AUTOCOMMIT with PARAMSTATUSARRAY\n");
do_create_rows(hdbc, "xtest", 0, 0);
printf("Partially successful batch AUTOCOMMIT with PARAMSTATUSARRAY\n");
do_create_rows(hdbc, "xtest", 1, 0);
printf("Partially successful batch AUTOCOMMIT without PARAMSTATUSARRAY\n");
do_create_rows(hdbc, "xtest", 1, 1);
ret = SQLSetConnectAttr(hdbc, SQL_ATTR_AUTOCOMMIT,
(SQLPOINTER)SQL_AUTOCOMMIT_OFF, 0);
if ( ret != SQL_SUCCESS)
printf( "SQLSetConnectAttr failed" );
printf("Partially successful batch no AUTOCOMMIT WITH PARAMSTATUSARRAY\n");
do_create_rows(hdbc, "xtest", 1, 0);
ret = SQLEndTran( SQL_HANDLE_DBC, hdbc, SQL_COMMIT );
if ( ret != SQL_SUCCESS ) {
do_a_error(SQL_HANDLE_DBC, hdbc, "SQLEndTran");
}
SQLFreeHandle(SQL_HANDLE_STMT, hstmt);
SQLDisconnect(hdbc);
SQLFreeHandle(SQL_HANDLE_DBC, hdbc);
SQLFreeHandle(SQL_HANDLE_ENV, henv);
}
static SQLRETURN do_create_rows(
SQLHDBC *hdbc,
char *table,
int duplicate_key,
int no_param_status)
{
SQLINTEGER len_ind[2][ROWS]; /* parameter lengths */
SQLINTEGER p1[ROWS]; /* first parameter array */
SQLCHAR p2[ROWS][20]; /* second parameter array */
SQLUSMALLINT param_status[ROWS]; /* parameter status */
SQLUINTEGER params_processed; /* parameters processed */
SQLINTEGER row_count; /* rows affected */
SQLRETURN ret; /* function status return */
unsigned int row; /* current row */
unsigned int i; /* loop variable */
SQLHSTMT hstmt; /* statement handle */
char qbuf[1024]; /* query buffer */
if (SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &hstmt) != SQL_SUCCESS) {
do_a_error(SQL_HANDLE_DBC, hdbc, "SQLAllocHandle");
return SQL_ERROR;
}
/*
* Delete all the rows.
*/
sprintf(qbuf, "delete from \"%s\"", table);
printf("\"%s\"\n", qbuf);
ret = SQLExecDirect(hstmt, (SQLCHAR *)qbuf, SQL_NTS);
if ((ret != SQL_SUCCESS) &&
(ret != SQL_NO_DATA)) /* no rows deleted */
do_a_error(SQL_HANDLE_STMT, hstmt, "SQLExecDirect");
/*
* Set the parameter binding type.
*/
printf("\tSetting bind by column\n");
if (!SQL_SUCCEEDED(SQLSetStmtAttr(hstmt, SQL_ATTR_PARAM_BIND_TYPE,
SQL_PARAM_BIND_BY_COLUMN, 0))) {
do_a_error(SQL_HANDLE_STMT, hstmt, "SQLSetStmtAttr");
return SQL_ERROR;
}
if (!no_param_status) {
/*
* Set the parameter status array and parameters processed.
*/
printf("\tSetting Parameter Status Array Ptr\n");
ret = SQLSetStmtAttr(hstmt, SQL_ATTR_PARAM_STATUS_PTR,
¶m_status[0], 0);
if (!SQL_SUCCEEDED(ret))
do_a_error(SQL_HANDLE_STMT, hstmt, "SQLSetStmtAttr");
}
printf("\tSetting Parameters Processed Ptr\n");
ret = SQLSetStmtAttr(hstmt, SQL_ATTR_PARAMS_PROCESSED_PTR,
¶ms_processed, 0);
if (!SQL_SUCCEEDED(ret))
do_a_error(SQL_HANDLE_STMT, hstmt, "SQLSetStmtAttr");
/*
* Set the parameter set size.
*/
printf("\tSetting PARAMSETSIZE to %d\n", ROWS);
ret = SQLSetStmtAttr(hstmt, SQL_ATTR_PARAMSET_SIZE,
(SQLPOINTER)ROWS, 0);
if (!SQL_SUCCEEDED(ret))
do_a_error(SQL_HANDLE_STMT, hstmt, "SQLSetStmtAttr");
/*
* Create the rows.
*/
sprintf(qbuf, "insert into \"%s\" (ky,txt) values(?,?)", table);
printf("\"%s\"\n", qbuf);
if (SQLPrepare(hstmt, (SQLCHAR *)qbuf, SQL_NTS) != SQL_SUCCESS)
{
do_a_error(SQL_HANDLE_STMT, hstmt, "SQLPrepare");
return SQL_ERROR;
}
/*
* Bind Parameters
*/
ret = SQLBindParameter(hstmt, 1, SQL_PARAM_INPUT, SQL_C_LONG, SQL_INTEGER,
5, 0, p1, 0, len_ind[0]);
if (!SQL_SUCCEEDED(ret)) {
do_a_error(SQL_HANDLE_STMT, hstmt, "SQLBindParameter");
return ret;
}
ret = SQLBindParameter(hstmt, 2, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_VARCHAR,
sizeof(p2[0]) - 1, 0, p2, sizeof(p2[0]),
len_ind[1]);
if (!SQL_SUCCEEDED(ret)) {
do_a_error(SQL_HANDLE_STMT, hstmt, "SQLBindParameter");
return ret;
}
printf("\tInserting rows into table\n");
for (row = 0; row < ROWS; row++) {
/* ky */
p1[row] = row;
len_ind[0][row] = sizeof(p1[0]);
/* txt */
sprintf(p2[row], "this is row %u", row);
len_ind[1][row] = SQL_NTS;
memset(param_status, 0xff, sizeof(param_status));
}
if (duplicate_key) {
p1[2] = 1;
p1[3] = 1;
}
ret = SQLExecute(hstmt);
if (ret == SQL_SUCCESS) {
printf("SQLExecute=SUCCESS\n");
} else if (ret == SQL_SUCCESS_WITH_INFO) {
printf("SQLExecute=SQL_SUCCESS_WITH_INFO\n");
do_a_error(SQL_HANDLE_STMT, hstmt, "SQLExecute");
} else {
do_a_error(SQL_HANDLE_STMT, hstmt, "SQLExecute");
return SQL_ERROR;
}
if (!SQL_SUCCEEDED(SQLRowCount(hstmt, &row_count))) {
do_a_error(SQL_HANDLE_STMT, hstmt, "SQLRowCount");
}
if (row_count != ROWS) {
fprintf(stderr, "** RowCount=%ld, expected %d **\n",
row_count, ROWS);
} else {
printf("RowCount=%ld\n", row_count);
}
if (!no_param_status) {
for (i = 0; i < ROWS; i++) {
if ((param_status[i] != SQL_PARAM_SUCCESS)) {
fprintf(stderr, "** Row %u not executed, status=%u**\n",
i+1, param_status[i]);
} else {
printf("Param Status Array %u = %u\n", i, param_status[i]);
}
}
}
if (params_processed != ROWS) {
fprintf(stderr, "** Only %ld rows processed **\n",
params_processed);
} else {
printf("Params processed = %ld\n", params_processed);
}
printf(".");
fflush(stdout);
printf("\n");
printf("\tResetting parameters\n");
if (!SQL_SUCCEEDED(SQLFreeStmt(hstmt, SQL_RESET_PARAMS))) {
do_a_error(SQL_HANDLE_STMT, hstmt, "SQLFreeStmt");
}
printf("\tClosing statement\n");
if (!SQL_SUCCEEDED(SQLFreeStmt(hstmt, SQL_CLOSE))) {
do_a_error(SQL_HANDLE_STMT, hstmt, "SQLFreeStmt");
}
printf("\tClearing Parameter Status Array Ptr\n");
ret = SQLSetStmtAttr(hstmt, SQL_ATTR_PARAM_STATUS_PTR, NULL, 0);
if (!SQL_SUCCEEDED(ret))
do_a_error(SQL_HANDLE_STMT, hstmt, "SQLSetStmtAttr");
printf("\tClearing Parameters Processed Ptr\n");
ret = SQLSetStmtAttr(hstmt, SQL_ATTR_PARAMS_PROCESSED_PTR, NULL, 0);
if (!SQL_SUCCEEDED(ret))
do_a_error(SQL_HANDLE_STMT, hstmt, "SQLSetStmtAttr");
sprintf(qbuf, "select * from %s", table);
ret = SQLExecDirect(hstmt, qbuf, SQL_NTS);
while(SQL_SUCCEEDED(SQLFetch(hstmt))) {
char c1[256], c2[256];
SQLGetData(hstmt, 1, SQL_C_CHAR, c1, sizeof(c1), NULL);
SQLGetData(hstmt, 2, SQL_C_CHAR, c2, sizeof(c2), NULL);
printf("%s, %s\n", c1, c2);
}
printf("\tDropping Statement\n");
ret = SQLFreeStmt(hstmt, SQL_DROP);
if (!SQL_SUCCEEDED(ret)) {
do_a_error(SQL_HANDLE_STMT, hstmt, "SQLFreeStmt");
}
return ret;
}
static SQLRETURN do_a_error(
SQLSMALLINT type,
SQLHANDLE handle,
char *fn)
{
SQLRETURN ret;
SQLCHAR state[7];
SQLCHAR text[1024];
SQLSMALLINT len;
int i=0;
SQLINTEGER native;
char cbuf[2048];
SQLINTEGER ndiags;
printf("** Error from %s **\n", fn);
ret = SQLGetDiagField(type, handle, 0, SQL_DIAG_NUMBER, &ndiags, 0, NULL);
printf("%ld diagnostics found\n", ndiags);
do
{
ret = SQLGetDiagRec(type, handle, ++i, state,
&native, text, sizeof(text), &len);
if (SQL_SUCCEEDED(ret))
{
sprintf(cbuf, "** error: %s:%d:%ld:%s **\n",
state, i, native, text);
fprintf(stderr, "%s\n", cbuf);
}
}
while (SQL_SUCCEEDED(ret));
return ret;
}
