/**
* Test batched callable statements where the call includes literal parameters which prevent the
* use of RPC calls.
*/
public void testCallStmtBatch2() throws Exception {
dropProcedure("jTDS_PROC");
try {
Statement stmt = con.createStatement();
stmt.execute("create table #testbatch (id int, data varchar(255))");
stmt.execute(
"create proc jTDS_PROC @p1 varchar(10), @p2 varchar(255) as "
+ "INSERT INTO #testbatch VALUES (convert(int, @p1), @p2)");
CallableStatement cstmt = con.prepareCall("{call jTDS_PROC (?, 'literal parameter')}");
for (int i = 0; i < 5; i++) {
if (i == 2) {
cstmt.setString(1, "XXX");
} else {
cstmt.setString(1, Integer.toString(i));
}
cstmt.addBatch();
}
int x[];
try {
x = cstmt.executeBatch();
} catch (BatchUpdateException e) {
x = e.getUpdateCounts();
}
if (con.getMetaData().getDatabaseProductName().toLowerCase().startsWith("microsoft")) {
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 {
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++) {
cstmt.setString(1, Integer.toString(i));
cstmt.addBatch();
}
try {
x = cstmt.executeBatch();
} catch (BatchUpdateException e) {
x = e.getUpdateCounts();
}
assertEquals(5, x.length);
assertEquals(1, x[0]);
assertEquals(1, x[1]);
assertEquals(1, x[2]);
assertEquals(1, x[3]);
assertEquals(1, x[4]);
} finally {
dropProcedure("jTDS_PROC");
}
}
/** Test batched callable statements where the call has no parameters. */
public void testCallStmtNoParams() throws Exception {
dropProcedure("jTDS_PROC");
try {
Statement stmt = con.createStatement();
stmt.execute("create table #testbatch (id numeric(10) identity, data varchar(255))");
stmt.execute(
"create proc jTDS_PROC as " + "INSERT INTO #testbatch (data) VALUES ('same each time')");
CallableStatement cstmt = con.prepareCall("{call jTDS_PROC}");
for (int i = 0; i < 5; i++) {
cstmt.addBatch();
}
int x[];
try {
x = cstmt.executeBatch();
} catch (BatchUpdateException e) {
x = e.getUpdateCounts();
}
assertEquals(5, x.length);
assertEquals(1, x[0]);
assertEquals(1, x[1]);
assertEquals(1, x[2]);
assertEquals(1, x[3]);
assertEquals(1, x[4]);
} finally {
dropProcedure("jTDS_PROC");
}
}
/** Test batched prepared statements. */
public void testPrepStmtBatch() throws Exception {
Statement stmt = con.createStatement();
stmt.execute("create table #testbatch (id int, data varchar(255))");
PreparedStatement pstmt = con.prepareStatement("INSERT INTO #testbatch VALUES (?, ?)");
for (int i = 0; i < 5; i++) {
if (i == 2) {
pstmt.setString(1, "xxx");
} else {
pstmt.setInt(1, i);
}
pstmt.setString(2, "This is line " + i);
pstmt.addBatch();
}
int x[];
try {
x = pstmt.executeBatch();
} catch (BatchUpdateException e) {
x = e.getUpdateCounts();
}
if (con.getMetaData().getDatabaseProductName().toLowerCase().startsWith("microsoft")) {
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 - 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++) {
pstmt.setInt(1, i);
pstmt.setString(2, "This is line " + i);
pstmt.addBatch();
}
x = pstmt.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 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]);
}
/**
* 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]);
}
public void run() {
try {
PreparedStatement pstmt =
con.prepareStatement(
"insert into #testConcurrentBatch (v1, v2, v3, v4, v5, v6) values (?, ?, ?, ?, ?, ?)");
for (int i = 0; i < 64; ++i) {
// Make sure we end up with 64 different prepares, use the binary representation of i to
// set each
// of the 6 parameters to either an int or a string.
int mask = i;
for (int j = 1; j <= 6; ++j, mask >>= 1) {
if ((mask & 1) != 0) {
pstmt.setInt(j, i);
} else {
pstmt.setString(j, String.valueOf(i));
}
}
pstmt.addBatch();
}
int x[];
try {
x = pstmt.executeBatch();
} catch (BatchUpdateException e) {
e.printStackTrace();
x = e.getUpdateCounts();
}
if (x.length != 64) {
throw new SQLException("Expected 64 update counts, got " + x.length);
}
for (int i = 0; i < x.length; ++i) {
if (x[i] != 1) {
throw new SQLException("Error at position " + i + ", got " + x[i] + " instead of 1");
}
}
// Rollback the transaction, exposing any race conditions.
con.rollback();
pstmt.close();
} catch (SQLException ex) {
ex.printStackTrace();
exceptions.add(ex);
}
}
/** Test for bug [1371295] SQL Server continues after duplicate key error. */
public void testPrepStmtBatchDupKey() throws Exception {
Statement stmt = con.createStatement();
stmt.execute("create table #testbatch (id int, data varchar(255), PRIMARY KEY (id))");
PreparedStatement pstmt = con.prepareStatement("INSERT INTO #testbatch VALUES (?, ?)");
for (int i = 0; i < 5; i++) {
if (i == 2) {
pstmt.setInt(1, 1); // Will cause duplicate key batch will continue
} else {
pstmt.setInt(1, i);
}
pstmt.setString(2, "This is line " + i);
pstmt.addBatch();
}
int x[];
try {
x = pstmt.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++) {
pstmt.setInt(1, i);
pstmt.setString(2, "This is line " + i);
pstmt.addBatch();
}
x = pstmt.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 PreparedStatement batch with no parameters. */
public void testPrepStmtNoParams() throws Exception {
Statement stmt = con.createStatement();
stmt.execute(
"create table #testbatch (id numeric(10) identity, data varchar(255), PRIMARY KEY (id))");
PreparedStatement pstmt =
con.prepareStatement("INSERT INTO #testbatch (data) VALUES ('Same each time')");
for (int i = 0; i < 5; i++) {
pstmt.addBatch();
}
int x[];
try {
x = pstmt.executeBatch();
} catch (BatchUpdateException e) {
x = e.getUpdateCounts();
}
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 PreparedStatement batch with variable parameter types. */
public void testPrepStmtVariableParams() throws Exception {
Statement stmt = con.createStatement();
stmt.execute("create table #testbatch (id int, data int, PRIMARY KEY (id))");
PreparedStatement pstmt =
con.prepareStatement("INSERT INTO #testbatch VALUES (?, convert(int, ?))");
for (int i = 0; i < 5; i++) {
pstmt.setInt(1, i);
if (i == 2) {
// This statement will require a string param instead of an int
pstmt.setString(2, "123");
} else {
pstmt.setInt(2, 123);
}
pstmt.addBatch();
}
int x[];
try {
x = pstmt.executeBatch();
} catch (BatchUpdateException e) {
x = e.getUpdateCounts();
}
assertEquals(5, x.length);
assertEquals(1, x[0]);
assertEquals(1, x[1]);
assertEquals(1, x[2]);
assertEquals(1, x[3]);
assertEquals(1, x[4]);
ResultSet rs = stmt.executeQuery("SELECT * FROM #testbatch");
assertNotNull(rs);
int i = 0;
while (rs.next()) {
assertEquals(123, rs.getInt(2));
i++;
}
assertEquals(5, i);
}
/** 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]);
}