/** Test batched statements. */
public void testBatch() throws Exception {
Statement stmt = con.createStatement();
stmt.execute("create table #testbatch (id int, data varchar(255))");
for (int i = 0; i < 5; i++) {
if (i == 2) {
// This statement will generate an error
stmt.addBatch("INSERT INTO #testbatch VALUES ('xx', 'This is line " + i + "')");
} else {
stmt.addBatch("INSERT INTO #testbatch VALUES (" + i + ", 'This is line " + i + "')");
}
}
int x[];
try {
x = stmt.executeBatch();
} catch (BatchUpdateException e) {
x = e.getUpdateCounts();
}
if (con.getMetaData().getDatabaseProductName().toLowerCase().startsWith("microsoft")
&& ((JtdsDatabaseMetaData) con.getMetaData()).getDatabaseMajorVersion() > 6) {
assertEquals(5, x.length);
assertEquals(1, x[0]);
assertEquals(1, x[1]);
assertEquals(EXECUTE_FAILED, x[2]);
assertEquals(EXECUTE_FAILED, x[3]);
assertEquals(EXECUTE_FAILED, x[4]);
} else {
// Sybase or SQL Server 6.5 - Entire batch fails due to data conversion error
// detected in statement 3
assertEquals(5, x.length);
assertEquals(EXECUTE_FAILED, x[0]);
assertEquals(EXECUTE_FAILED, x[1]);
assertEquals(EXECUTE_FAILED, x[2]);
assertEquals(EXECUTE_FAILED, x[3]);
assertEquals(EXECUTE_FAILED, x[4]);
}
// Now without errors
stmt.execute("TRUNCATE TABLE #testbatch");
for (int i = 0; i < 5; i++) {
stmt.addBatch("INSERT INTO #testbatch VALUES (" + i + ", 'This is line " + i + "')");
}
x = stmt.executeBatch();
assertEquals(5, x.length);
assertEquals(1, x[0]);
assertEquals(1, x[1]);
assertEquals(1, x[2]);
assertEquals(1, x[3]);
assertEquals(1, x[4]);
}
/** Test for bug [1180169] JDBC escapes not allowed with Sybase addBatch. */
public void testBatchEsc() throws Exception {
Statement stmt = con.createStatement();
stmt.execute("CREATE TABLE #TESTBATCH (ts datetime)");
stmt.addBatch("INSERT INTO #TESTBATCH VALUES ({ts '1999-01-01 23:50:00'})");
int counts[] = stmt.executeBatch();
assertEquals(1, counts[0]);
stmt.close();
}
/**
* The first statement in this batch does not return an update count. SUCCESS_NO_INFO is expected
* instead.
*/
public void testNoCount() throws Exception {
Statement stmt = con.createStatement();
stmt.addBatch("create table #testbatch (id int, data varchar(255))");
stmt.addBatch("insert into #testbatch VALUES(1, 'Test line')");
int x[] = stmt.executeBatch();
assertEquals(2, x.length);
assertEquals(SUCCESS_NO_INFO, x[0]);
assertEquals(1, x[1]);
}
/** Test for bug [1371295] SQL Server continues after duplicate key error. */
public void testBatchDupKey() throws Exception {
Statement stmt = con.createStatement();
stmt.execute("create table #testbatch (id int, data varchar(255), PRIMARY KEY (id))");
for (int i = 0; i < 5; i++) {
if (i == 2) {
// This statement will generate an duplicate key error
stmt.addBatch("INSERT INTO #testbatch VALUES (1, 'This is line " + i + "')");
} else {
stmt.addBatch("INSERT INTO #testbatch VALUES (" + i + ", 'This is line " + i + "')");
}
}
int x[];
try {
x = stmt.executeBatch();
} catch (BatchUpdateException e) {
x = e.getUpdateCounts();
}
assertEquals(5, x.length);
assertEquals(1, x[0]);
assertEquals(1, x[1]);
assertEquals(EXECUTE_FAILED, x[2]);
assertEquals(1, x[3]);
assertEquals(1, x[4]);
// Now without errors
stmt.execute("TRUNCATE TABLE #testbatch");
for (int i = 0; i < 5; i++) {
stmt.addBatch("INSERT INTO #testbatch VALUES (" + i + ", 'This is line " + i + "')");
}
x = stmt.executeBatch();
assertEquals(5, x.length);
assertEquals(1, x[0]);
assertEquals(1, x[1]);
assertEquals(1, x[2]);
assertEquals(1, x[3]);
assertEquals(1, x[4]);
}
/**
* This test should generate an error as the second statement in the batch returns a result set.
*/
public void testResultSetError() throws Exception {
Statement stmt = con.createStatement();
stmt.addBatch("create table #testbatch (id int, data varchar(255))");
stmt.addBatch("insert into #testbatch VALUES(1, 'Test line')");
stmt.addBatch("SELECT 'This is an error'");
int x[];
try {
x = stmt.executeBatch();
fail("Expecting BatchUpdateException");
} catch (BatchUpdateException e) {
x = e.getUpdateCounts();
}
assertEquals(3, x.length);
assertEquals(SUCCESS_NO_INFO, x[0]);
assertEquals(1, x[1]);
assertEquals(EXECUTE_FAILED, x[2]);
}
/**
* test for bug [2827931] that implicitly also tests for bug [1811383]
*
* <p>example for statement that produces multiple update counts unexpectedly: IF
* sessionproperty('ARITHABORT') = 0 SET ARITHABORT ON
*/
public void testBatchUpdateCounts() throws SQLException {
Statement statement = con.createStatement();
statement.execute("CREATE TABLE #BATCHUC (id int)");
statement.addBatch("insert into #BATCHUC values (1)");
statement.addBatch("insert into #BATCHUC values (2) insert into #BATCHUC values (3)");
statement.addBatch(
"insert into #BATCHUC values (4) insert into #BATCHUC values (5) insert into #BATCHUC values (6)");
// below: create identifiable update counts to show if/how far they have been shifted due to bug
// [2827931]
statement.addBatch("insert into #BATCHUC select * from #BATCHUC");
statement.addBatch("insert into #BATCHUC select * from #BATCHUC where id=999");
statement.addBatch("insert into #BATCHUC select * from #BATCHUC where id=999");
statement.addBatch("insert into #BATCHUC select * from #BATCHUC where id=999");
statement.addBatch("insert into #BATCHUC select * from #BATCHUC where id=999");
assertEquals(
array2String(new int[] {1, 2, 3, 6, 0, 0, 0, 0, 0, 0}),
array2String(statement.executeBatch()));
statement.close();
}
private void insertRows(Connection connection) {
// Build the SQL INSERT statement
String sqlInsert = "insert into " + jtfTableName.getText() + " values (";
// Use a Scanner to read text from the file
Scanner input = null;
// Get file name from the text field
String filename = jtfFilename.getText().trim();
try {
// Create a scanner
input = new Scanner(new File(filename));
// Create a statement
Statement statement = connection.createStatement();
System.out.println(
"Driver major version? " + connection.getMetaData().getDriverMajorVersion());
// Determine if batchUpdatesSupported is supported
boolean batchUpdatesSupported = false;
try {
if (connection.getMetaData().supportsBatchUpdates()) {
batchUpdatesSupported = true;
System.out.println("batch updates supported");
} else {
System.out.println(
"The driver is of JDBC 2 type, but " + "does not support batch updates");
}
} catch (UnsupportedOperationException ex) {
System.out.println("The driver does not support JDBC 2");
}
// Determine if the driver is capable of batch updates
if (batchUpdatesSupported) {
// Read a line and add the insert table command to the batch
while (input.hasNext()) {
statement.addBatch(sqlInsert + input.nextLine() + ")");
}
statement.executeBatch();
jlblStatus.setText("Batch updates completed");
} else {
// Read a line and execute insert table command
while (input.hasNext()) {
statement.executeUpdate(sqlInsert + input.nextLine() + ")");
}
jlblStatus.setText("Single row update completed");
}
} catch (SQLException ex) {
System.out.println(ex);
} catch (FileNotFoundException ex) {
System.out.println("File not found: " + filename);
} catch (IOException ex) {
ex.printStackTrace();
} finally {
if (input != null) input.close();
}
}