// jTDS JDBC Driver for Microsoft SQL Server and Sybase
// Copyright (C) 2004 The jTDS Project
//
// This library is free software; you can redistribute it and/or
// modify it under the terms of the GNU Lesser General Public
// License as published by the Free Software Foundation; either
// version 2.1 of the License, or (at your option) any later version.
//
// This library is distributed in the hope that it will be useful,
// but WITHOUT ANY WARRANTY; without even the implied warranty of
// MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
// Lesser General Public License for more details.
//
// You should have received a copy of the GNU Lesser General Public
// License along with this library; if not, write to the Free Software
// Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA
//
package net.sourceforge.jtds.jdbc;
import java.math.BigDecimal;
import java.sql.*;
import java.util.*;
import junit.framework.Assert;
/**
* @version $Id: PreparedStatementTest.java,v 1.46.2.4 2009-12-30 12:15:49 ickzon Exp $
*/
public class PreparedStatementTest extends TestBase {
public PreparedStatementTest( String name )
{
super(name);
}
/**
* Test for trailing line comment breaking connection state on metadata
* retrieval when autocommit is false
*/
public void testBug695()
throws Exception
{
Connection localCon = getConnection();
Statement stmt = localCon.createStatement( ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE );
stmt.executeUpdate( "CREATE TABLE #metaData (id int, data varchar(8000))" );
stmt.executeUpdate( "INSERT INTO #metaData (id, data) VALUES (1, 'Data1')" );
stmt.close();
// If AutoCommit is true then the test will pass
localCon.setAutoCommit( false );
// If the trailing SQL line comment is removed from the statement
// text then the test will pass
PreparedStatement pstmt = localCon.prepareStatement( "SELECT id FROM #metaData WHERE data=? GROUP BY id--" );
ResultSetMetaData rsmd = pstmt.getMetaData();
// Tracing with SQLProfiler shows execution of :
// SET FMTONLY ON SELECT id ... GROUP BY id-- SET FMTONLY OFF
// So the SET FMTONLY OFF is ignored, all following statements are
// executed with FMTONLY ON
assertNotNull( "No meta data returned for simple statement", rsmd );
assertEquals( 1, rsmd.getColumnCount() );
assertEquals( "id", rsmd.getColumnName( 1 ) );
pstmt.close();
// Simple statement that should return a row
// but we don't get a row with setAutoCommit(false) and the trailing --
// Tracing with SQLProfiler shows execution of
pstmt = localCon.prepareStatement( "SELECT getdate()" );
ResultSet rs = pstmt.executeQuery();
Assert.assertTrue( rs.next() );
rs.close();
pstmt.close();
localCon.close();
}
public void testBug686()
throws Exception
{
PreparedStatement st = null;
try
{
st = con.prepareStatement( "set xact_abort on\n" + // hmm. only with TX?...
// "begin tran\n" +
"create table #temp (id int not null primary key)\n" + // Result 1 is an update count: 0 null
"insert #temp values (1)\n" + // Result 2 is an update count: 1 null
"select * from #temp\n" + // Result 3 is a ResultSet: SQLServerResultSet:1
"insert #temp values (2);\n" + // Result 4 is an update count: 1 null
// Result 5 is an error: com.microsoft.sqlserver.jdbc.SQLServerException: Invalid object name '#notexists'.
"update #notexists set bar=1;\n" +
"insert #temp select * from #temp\n" + // [end] Done processing 4 results
"drop table #temp\n" +
// "commit\n" +
"select @@error, @@trancount;\n" );
st.execute();
try
{
dumpAll( st );
fail( "expected an error: Invalid object name '#notexists'" );
}
catch( Exception e )
{
// expected,
}
// dump the remaining result (used to fail due to bug #686)
dumpAll( st );
}
finally
{
st.close();
con.close();
}
}
public void testBug657()
throws Exception
{
// prepare test data
Statement stmt = con.createStatement();
stmt.execute( "CREATE TABLE #Bug657 (A int)" );
for( int i = 0; i < 100; i++ )
{
stmt.executeUpdate( "INSERT INTO #Bug657(A) VALUES("+ i +")" );
}
stmt.close();
// select limited data subset using TOP
PreparedStatement pstmt = con.prepareStatement( "SELECT * from #Bug657 Bug657X WHERE (Bug657X.A IN (SELECT TOP 50 A FROM #Bug657))" );
ResultSet rs = pstmt.executeQuery();
// ensure the correct number of rows is returned
for( int i = 0; i < 50; i ++ )
{
Assert.assertTrue( rs.next() );
}
Assert.assertFalse( rs.next() );
}
public void testPreparedStatement() throws Exception {
PreparedStatement pstmt = con.prepareStatement("SELECT * FROM #test");
Statement stmt = con.createStatement();
makeTestTables(stmt);
makeObjects(stmt, 10);
stmt.close();
ResultSet rs = pstmt.executeQuery();
dump(rs);
rs.close();
pstmt.close();
}
public void testScrollablePreparedStatement() throws Exception {
Statement stmt = con.createStatement();
makeTestTables(stmt);
makeObjects(stmt, 10);
stmt.close();
PreparedStatement pstmt = con.prepareStatement("SELECT * FROM #test",
ResultSet.TYPE_SCROLL_SENSITIVE,
ResultSet.CONCUR_READ_ONLY);
ResultSet rs = pstmt.executeQuery();
assertTrue(rs.isBeforeFirst());
while (rs.next()) {
}
assertTrue(rs.isAfterLast());
//This currently fails because the PreparedStatement
//Doesn't know it needs to create a cursored ResultSet.
//Needs some refactoring!!
// SAfe Not any longer. ;o)
while (rs.previous()) {
}
assertTrue(rs.isBeforeFirst());
rs.close();
pstmt.close();
}
public void testPreparedStatementAddBatch1()
throws Exception {
int count = 50;
Statement stmt = con.createStatement();
stmt.execute("CREATE TABLE #psbatch1 (f_int INT)");
int sum = 0;
con.setAutoCommit(false);
PreparedStatement pstmt = con.prepareStatement("INSERT INTO #psbatch1 (f_int) VALUES (?)");
for (int i = 0; i < count; i++) {
pstmt.setInt(1, i);
pstmt.addBatch();
sum += i;
}
int[] results = pstmt.executeBatch();
assertEquals(results.length, count);
for (int i = 0; i < count; i++) {
assertEquals(results[i], 1);
}
pstmt.close();
con.commit();
con.setAutoCommit(true);
ResultSet rs = stmt.executeQuery("SELECT SUM(f_int) FROM #psbatch1");
assertTrue(rs.next());
System.out.println(rs.getInt(1));
assertEquals(rs.getInt(1), sum);
rs.close();
stmt.close();
}
/**
* Test for [924030] EscapeProcesser problem with "{}" brackets
*/
public void testPreparedStatementParsing1() throws Exception {
dropProcedure( "sp_psp1" );
dropTable( "psp1" );
String data = "New {order} plus {1} more";
Statement stmt = con.createStatement();
stmt.execute("CREATE TABLE psp1 (data VARCHAR(32))");
stmt.close();
stmt = con.createStatement();
stmt.execute("create procedure sp_psp1 @data VARCHAR(32) as INSERT INTO psp1 (data) VALUES(@data)");
stmt.close();
PreparedStatement pstmt = con.prepareStatement("{call sp_psp1('" + data + "')}");
pstmt.execute();
pstmt.close();
stmt = con.createStatement();
ResultSet rs = stmt.executeQuery("SELECT data FROM psp1");
assertTrue(rs.next());
assertTrue(data.equals(rs.getString(1)));
assertFalse(rs.next());
rs.close();
stmt.close();
}
/**
* Test for bug [1008882] Some queries with parameters cannot be executed with 0.9-rc1
*/
public void testPreparedStatementParsing2() throws Exception {
PreparedStatement pstmt = con.prepareStatement(" SELECT ?");
pstmt.setString(1, "TEST");
ResultSet rs = pstmt.executeQuery();
assertTrue(rs.next());
assertEquals("TEST", rs.getString(1));
assertFalse(rs.next());
pstmt.close();
rs.close();
}
/**
* Test for "invalid parameter index" error.
*/
public void testPreparedStatementParsing3() throws Exception {
PreparedStatement pstmt = con.prepareStatement(
"UPDATE dbo.DEPARTMENTS SET DEPARTMENT_NAME=? WHERE DEPARTMENT_ID=?");
pstmt.setString(1, "TEST");
pstmt.setString(2, "TEST");
pstmt.close();
}
/**
* Test for [931090] ArrayIndexOutOfBoundsException in rollback()
*/
public void testPreparedStatementRollback1() throws Exception {
Connection localCon = getConnection();
Statement stmt = localCon.createStatement();
stmt.execute("CREATE TABLE #psr1 (data BIT)");
localCon.setAutoCommit(false);
PreparedStatement pstmt = localCon.prepareStatement("INSERT INTO #psr1 (data) VALUES (?)");
pstmt.setBoolean(1, true);
assertEquals(1, pstmt.executeUpdate());
pstmt.close();
localCon.rollback();
ResultSet rs = stmt.executeQuery("SELECT data FROM #psr1");
assertFalse(rs.next());
rs.close();
stmt.close();
localCon.close();
try {
localCon.commit();
fail("Expecting commit to fail, connection was closed");
} catch (SQLException ex) {
assertEquals("HY010", ex.getSQLState());
}
try {
localCon.rollback();
fail("Expecting rollback to fail, connection was closed");
} catch (SQLException ex) {
assertEquals("HY010", ex.getSQLState());
}
}
/**
* Test for bug [938494] setObject(i, o, NUMERIC/DECIMAL) cuts off decimal places
*/
public void testPreparedStatementSetObject1() throws Exception {
BigDecimal data = new BigDecimal(3.7D);
Statement stmt = con.createStatement();
stmt.execute("CREATE TABLE #psso1 (data MONEY)");
PreparedStatement pstmt = con.prepareStatement("INSERT INTO #psso1 (data) VALUES (?)");
pstmt.setObject(1, data);
assertEquals(1, pstmt.executeUpdate());
pstmt.close();
ResultSet rs = stmt.executeQuery("SELECT data FROM #psso1");
assertTrue(rs.next());
assertEquals(data.doubleValue(), rs.getDouble(1), 0);
assertFalse(rs.next());
rs.close();
stmt.close();
}
/**
* Test for bug [938494] setObject(i, o, NUMERIC/DECIMAL) cuts off decimal places
*/
public void testPreparedStatementSetObject2() throws Exception {
BigDecimal data = new BigDecimal(3.7D);
Statement stmt = con.createStatement();
stmt.execute("CREATE TABLE #psso2 (data MONEY)");
PreparedStatement pstmt = con.prepareStatement("INSERT INTO #psso2 (data) VALUES (?)");
pstmt.setObject(1, data, Types.NUMERIC);
assertEquals(1, pstmt.executeUpdate());
pstmt.close();
ResultSet rs = stmt.executeQuery("SELECT data FROM #psso2");
assertTrue(rs.next());
assertEquals(data.doubleValue(), rs.getDouble(1), 0);
assertFalse(rs.next());
rs.close();
stmt.close();
}
/**
* Test for bug [938494] setObject(i, o, NUMERIC/DECIMAL) cuts off decimal places
*/
public void testPreparedStatementSetObject3() throws Exception {
BigDecimal data = new BigDecimal(3.7D);
Statement stmt = con.createStatement();
stmt.execute("CREATE TABLE #psso3 (data MONEY)");
PreparedStatement pstmt = con.prepareStatement("INSERT INTO #psso3 (data) VALUES (?)");
pstmt.setObject(1, data, Types.DECIMAL);
assertEquals(1, pstmt.executeUpdate());
pstmt.close();
ResultSet rs = stmt.executeQuery("SELECT data FROM #psso3");
assertTrue(rs.next());
assertEquals(data.doubleValue(), rs.getDouble(1), 0);
assertFalse(rs.next());
rs.close();
stmt.close();
}
/**
* Test for bug [938494] setObject(i, o, NUMERIC/DECIMAL) cuts off decimal places
*/
public void testPreparedStatementSetObject4() throws Exception {
BigDecimal data = new BigDecimal(3.7D);
Statement stmt = con.createStatement();
stmt.execute("CREATE TABLE #psso4 (data MONEY)");
PreparedStatement pstmt = con.prepareStatement("INSERT INTO #psso4 (data) VALUES (?)");
pstmt.setObject(1, data, Types.NUMERIC, 4);
assertEquals(1, pstmt.executeUpdate());
pstmt.close();
ResultSet rs = stmt.executeQuery("SELECT data FROM #psso4");
assertTrue(rs.next());
assertEquals(data.doubleValue(), rs.getDouble(1), 0);
assertFalse(rs.next());
rs.close();
stmt.close();
}
/**
* Test for bug [938494] setObject(i, o, NUMERIC/DECIMAL) cuts off decimal places
*/
public void testPreparedStatementSetObject5() throws Exception {
BigDecimal data = new BigDecimal(3.7D);
Statement stmt = con.createStatement();
stmt.execute("CREATE TABLE #psso5 (data MONEY)");
PreparedStatement pstmt = con.prepareStatement("INSERT INTO #psso5 (data) VALUES (?)");
pstmt.setObject(1, data, Types.DECIMAL, 4);
assertEquals(1, pstmt.executeUpdate());
pstmt.close();
ResultSet rs = stmt.executeQuery("SELECT data FROM #psso5");
assertTrue(rs.next());
assertEquals(data.doubleValue(), rs.getDouble(1), 0);
assertFalse(rs.next());
rs.close();
stmt.close();
}
/**
* Test for bug [1204658] Conversion from Number to BigDecimal causes data
* corruption.
*/
public void testPreparedStatementSetObject6() throws Exception {
final Long TEST_VALUE = new Long(2265157674817400199L);
Statement s = con.createStatement();
s.execute("CREATE TABLE #psso6 (test_value NUMERIC(22,0))");
PreparedStatement ps = con.prepareStatement(
"insert into #psso6(test_value) values (?)");
ps.setObject(1, TEST_VALUE, Types.DECIMAL);
assertEquals(1, ps.executeUpdate());
ps.close();
ResultSet rs = s.executeQuery("select test_value from #psso6");
assertTrue(rs.next());
assertEquals("Persisted value not equal to original value",
TEST_VALUE.longValue(), rs.getLong(1));
assertFalse(rs.next());
rs.close();
s.close();
}
/**
* Test for bug [985754] row count is always 0
*/
public void testUpdateCount1() throws Exception {
int count = 50;
Statement stmt = con.createStatement();
stmt.execute("CREATE TABLE #updateCount1 (data INT)");
PreparedStatement pstmt = con.prepareStatement("INSERT INTO #updateCount1 (data) VALUES (?)");
for (int i = 1; i <= count; i++) {
pstmt.setInt(1, i);
assertEquals(1, pstmt.executeUpdate());
}
pstmt.close();
ResultSet rs = stmt.executeQuery("SELECT COUNT(*) FROM #updateCount1");
assertTrue(rs.next());
assertEquals(count, rs.getInt(1));
assertFalse(rs.next());
stmt.close();
rs.close();
pstmt = con.prepareStatement("DELETE FROM #updateCount1");
assertEquals(count, pstmt.executeUpdate());
pstmt.close();
}
/**
* Test for parameter markers in function escapes.
*/
public void testEscapedParams() throws Exception {
PreparedStatement pstmt = con.prepareStatement("SELECT {fn left(?, 2)}");
pstmt.setString(1, "TEST");
ResultSet rs = pstmt.executeQuery();
assertTrue(rs.next());
assertEquals("TE", rs.getString(1));
assertFalse(rs.next());
rs.close();
pstmt.close();
}
/**
* Test for bug [ 1059916 ] whitespace needed in preparedStatement.
*/
public void testMissingWhitespace() throws Exception
{
PreparedStatement pstmt = con.prepareStatement(
"SELECT name from master..syscharsets where description like?and?between csid and 10");
pstmt.setString(1, "ISO%");
pstmt.setInt(2, 0);
ResultSet rs = pstmt.executeQuery();
assertNotNull(rs);
assertTrue(rs.next());
}
/**
* Test for bug [1022968] Long SQL expression error.
* NB. Test must be run with TDS=7.0 to fail.
*/
public void testLongStatement() throws Exception {
Statement stmt = con.createStatement(
ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE);
stmt.execute("CREATE TABLE #longStatement (id int primary key, data varchar(8000))");
StringBuilder buf = new StringBuilder(4096);
buf.append("SELECT * FROM #longStatement WHERE data = '");
for (int i = 0; i < 4000; i++) {
buf.append('X');
}
buf.append("'");
ResultSet rs = stmt.executeQuery(buf.toString());
assertNotNull(rs);
assertFalse(rs.next());
rs.close();
stmt.close();
}
/**
* Test for bug [1047330] prep statement with more than 2100 params fails.
*/
public void testManyParametersStatement() throws Exception {
final int PARAMS = 2110;
Statement stmt = con.createStatement();
makeTestTables(stmt);
makeObjects(stmt, 10);
stmt.close();
StringBuilder sb = new StringBuilder(PARAMS * 3 + 100);
sb.append("SELECT * FROM #test WHERE f_int in (?");
for (int i = 1; i < PARAMS; i++) {
sb.append(", ?");
}
sb.append(")");
try {
// This can work if prepareSql=0
PreparedStatement pstmt = con.prepareStatement(sb.toString());
// Set the parameters
for (int i = 1; i <= PARAMS; i++) {
pstmt.setInt(i, i);
}
// Execute query and count rows
ResultSet rs = pstmt.executeQuery();
int cnt = 0;
while (rs.next()) {
++cnt;
}
// Make sure this worked
assertEquals(9, cnt);
} catch (SQLException ex) {
assertEquals("22025", ex.getSQLState());
}
}
/**
* Test for bug [1010660] 0.9-rc1 setMaxRows causes unlimited temp stored
* procedures. This test has to be run with logging enabled or while
* monitoring it with SQL Profiler to see whether the temporary stored
* procedure is executed or the SQL is executed directly.
*/
public void testMaxRows() throws SQLException {
Statement stmt = con.createStatement();
stmt.execute("CREATE TABLE #maxRows (val int)"
+ " INSERT INTO #maxRows VALUES (1)"
+ " INSERT INTO #maxRows VALUES (2)");
PreparedStatement pstmt = con.prepareStatement(
"SELECT * FROM #maxRows WHERE val<? ORDER BY val");
pstmt.setInt(1, 100);
pstmt.setMaxRows(1);
ResultSet rs = pstmt.executeQuery();
assertNotNull(rs);
assertTrue(rs.next());
assertEquals(1, rs.getInt(1));
assertFalse(rs.next());
rs.close();
pstmt.close();
stmt.executeUpdate("DROP TABLE #maxRows");
stmt.close();
}
/**
* Test for bug [1050660] PreparedStatement.getMetaData() clears resultset.
*/
public void testMetaDataClearsResultSet() throws Exception {
Statement stmt = con.createStatement(
ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE);
stmt.executeUpdate(
"CREATE TABLE #metaDataClearsResultSet (id int primary key, data varchar(8000))");
stmt.executeUpdate("INSERT INTO #metaDataClearsResultSet (id, data)"
+ " VALUES (1, '1')");
stmt.executeUpdate("INSERT INTO #metaDataClearsResultSet (id, data)"
+ " VALUES (2, '2')");
stmt.close();
PreparedStatement pstmt = con.prepareStatement(
"SELECT * FROM #metaDataClearsResultSet ORDER BY id");
ResultSet rs = pstmt.executeQuery();
assertNotNull(rs);
ResultSetMetaData rsmd = pstmt.getMetaData();
assertEquals(2, rsmd.getColumnCount());
assertEquals("id", rsmd.getColumnName(1));
assertEquals("data", rsmd.getColumnName(2));
assertEquals(8000, rsmd.getColumnDisplaySize(2));
assertTrue(rs.next());
assertEquals(1, rs.getInt(1));
assertEquals("1", rs.getString(2));
assertTrue(rs.next());
assertEquals(2, rs.getInt(1));
assertEquals("2", rs.getString(2));
assertFalse(rs.next());
rs.close();
pstmt.close();
}
/**
* Test for bad truncation in prepared statements on metadata retrieval
* (patch [1076383] ResultSetMetaData for more complex statements for SQL
* Server).
*/
public void testMetaData() throws Exception {
Statement stmt = con.createStatement(
ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE);
stmt.executeUpdate("CREATE TABLE #metaData (id int, data varchar(8000))");
stmt.executeUpdate("INSERT INTO #metaData (id, data)"
+ " VALUES (1, 'Data1')");
stmt.executeUpdate("INSERT INTO #metaData (id, data)"
+ " VALUES (1, 'Data2')");
stmt.executeUpdate("INSERT INTO #metaData (id, data)"
+ " VALUES (2, 'Data3')");
stmt.executeUpdate("INSERT INTO #metaData (id, data)"
+ " VALUES (2, 'Data4')");
stmt.close();
// test simple statement
PreparedStatement pstmt = con.prepareStatement("SELECT id " +
"FROM #metaData " +
"WHERE data=? GROUP BY id");
ResultSetMetaData rsmd = pstmt.getMetaData();
assertNotNull("No meta data returned for simple statement", rsmd);
assertEquals(1, rsmd.getColumnCount());
assertEquals("id", rsmd.getColumnName(1));
pstmt.close();
// test more complex statement
pstmt = con.prepareStatement("SELECT id, count(*) as count " +
"FROM #metaData " +
"WHERE data=? GROUP BY id");
rsmd = pstmt.getMetaData();
assertNotNull("No metadata returned for complex statement", rsmd);
assertEquals(2, rsmd.getColumnCount());
assertEquals("id", rsmd.getColumnName(1));
assertEquals("count", rsmd.getColumnName(2));
pstmt.close();
}
/**
* <p> Regression test for bug #647, {@link PreparedStatement} doesn't return
* {@link ResultSetMetaData} for SQL statements using a WITH clause. </p>
*/
public void testWithClauseMetaData()
throws Exception
{
PreparedStatement stm = con.prepareStatement( "with bug647( X ) as ( select 'A' ) select * from bug647" );
ResultSetMetaData rmd = stm.getMetaData();
// check meta data available before executing the statement
// this used to fail due to bug #647
assertNotNull( rmd );
assertEquals( 1, rmd.getColumnCount() );
assertEquals( "X", rmd.getColumnName( 1 ) );
assertEquals( Types.VARCHAR, rmd.getColumnType( 1 ) );
// execute statement and re-check meta data
ResultSet res = stm.executeQuery();
rmd = stm.getMetaData();
assertNotNull( rmd );
assertEquals( 1, rmd.getColumnCount() );
assertEquals( "X", rmd.getColumnName( 1 ) );
assertEquals( Types.VARCHAR, rmd.getColumnType( 1 ) );
// check meta data provided by ResultSet
rmd = res.getMetaData();
assertEquals( 1, rmd.getColumnCount() );
assertEquals( "X", rmd.getColumnName( 1 ) );
assertEquals( Types.VARCHAR, rmd.getColumnType( 1 ) );
// check data
assertTrue( res.next() );
assertEquals( "A", res.getString( 1 ) );
assertFalse( res.next() );
// cleanup
res.close();
stm.close();
}
/**
* Test for bug [1071397] Error in prepared statement (parameters in outer
* join escapes are not recognized).
*/
public void testOuterJoinParameters() throws SQLException {
Statement stmt = con.createStatement();
stmt.executeUpdate(
"CREATE TABLE #outerJoinParameters (id int primary key)");
stmt.executeUpdate(
"INSERT #outerJoinParameters (id) values (1)");
stmt.close();
// Real dumb join, the idea is to see the parser works fine
PreparedStatement pstmt = con.prepareStatement(
"select * from "
+ "{oj #outerJoinParameters a left outer join #outerJoinParameters b on a.id = ?}"
+ "where b.id = ?");
pstmt.setInt(1, 1);
pstmt.setInt(2, 1);
ResultSet rs = pstmt.executeQuery();
assertTrue(rs.next());
assertEquals(1, rs.getInt(1));
assertEquals(1, rs.getInt(2));
assertFalse(rs.next());
rs.close();
pstmt.close();
pstmt = con.prepareStatement("select {fn round(?, 0)}");
pstmt.setDouble(1, 1.2);
rs = pstmt.executeQuery();
assertTrue(rs.next());
assertEquals(1, rs.getDouble(1), 0);
assertFalse(rs.next());
rs.close();
pstmt.close();
}
/**
* Inner class used by {@link PreparedStatementTest#testMultiThread} to
* test concurrency.
*/
static class TestMultiThread extends Thread {
static Connection con;
static final int THREAD_MAX = 10;
static final int LOOP_MAX = 10;
static final int ROWS_MAX = 10;
static int live;
static Exception error;
int threadId;
TestMultiThread(int n) {
threadId = n;
}
public void run() {
try {
con.rollback();
PreparedStatement pstmt = con.prepareStatement(
"SELECT id, data FROM #TEST WHERE id = ?",
ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
for (int i = 1; i <= LOOP_MAX; i++) {
pstmt.clearParameters();
pstmt.setInt(1, i);
ResultSet rs = pstmt.executeQuery();
while (rs.next()) {
rs.getInt(1);
rs.getString(2);
}
}
pstmt.close();
} catch (Exception e) {
System.err.print("ID=" + threadId + ' ');
e.printStackTrace();
error = e;
}
synchronized (this.getClass()) {
live--;
}
}
static void startThreads(Connection con) throws Exception {
TestMultiThread.con = con;
con.setAutoCommit(false);
Statement stmt = con.createStatement();
stmt.execute("CREATE TABLE #TEST (id int identity primary key, data varchar(255))");
for (int i = 0; i < ROWS_MAX; i++) {
stmt.executeUpdate("INSERT INTO #TEST (data) VALUES('This is line " + i + "')");
}
stmt.close();
con.commit();
live = THREAD_MAX;
for (int i = 0; i < THREAD_MAX; i++) {
new TestMultiThread(i).start();
}
while (live > 0) {
sleep(1);
}
if (error != null) {
throw error;
}
}
}
/**
* Test <code>Connection</code> concurrency by running
* <code>PreparedStatement</code>s and rollbacks at the same time to see
* whether handles are not lost in the process.
*/
public void testMultiThread() throws Exception {
TestMultiThread.startThreads(con);
}
/**
* Test for bug [1094621] Decimal conversion error: A prepared statement
* with a decimal parameter that is -1E38 will fail as a result of the
* driver generating a parameter specification of decimal(38,10) rather
* than decimal(38,0).
*/
public void testBigDecBadParamSpec() throws Exception
{
Statement stmt = con.createStatement();
stmt.execute(
"create table #test (id int primary key, val decimal(38,0))");
BigDecimal bd =
new BigDecimal("99999999999999999999999999999999999999");
PreparedStatement pstmt =
con.prepareStatement("insert into #test values(?,?)");
pstmt.setInt(1, 1);
pstmt.setBigDecimal(2, bd);
assertEquals(1, pstmt.executeUpdate()); // Worked OK
pstmt.setInt(1, 2);
pstmt.setBigDecimal(2, bd.negate());
assertEquals(1, pstmt.executeUpdate()); // Failed
}
/**
* Test for bug [1111516 ] Illegal Parameters in PreparedStatement.
*/
public void testIllegalParameters() throws Exception
{
Statement stmt = con.createStatement();
stmt.execute("create table #test (id int)");
PreparedStatement pstmt =
con.prepareStatement("select top ? * from #test");
pstmt.setInt(1, 10);
try {
pstmt.executeQuery();
// This won't fail in unprepared mode (prepareSQL == 0)
// fail("Expecting an exception to be thrown.");
} catch (SQLException ex) {
assertTrue("37000".equals(ex.getSQLState())
|| "42000".equals(ex.getSQLState()));
}
pstmt.close();
}
/**
* <p> Test for bug #378, collation-related exception on update. </p>
*
* <p> If a statement prepare fails the statement should still be executed
* (unprepared) and a warning should be added to the connection (the prepare
* failed, this is a connection event even if it happened on statement
* execute). </p>
*/
public void testPrepareFailWarning() throws SQLException
{
// preparation succeeds in SQL server 2008 and above (what about Sybase?)
if( con.getMetaData().getURL().toLowerCase().contains( "microsoft" ) && con.getMetaData().getDatabaseMajorVersion() < 10 )
{
try
{
// FIXME: we need another test case working for newer SQL Server versions and Sybase ASE
PreparedStatement pstmt = con.prepareStatement( "CREATE VIEW prepFailWarning AS SELECT 1 AS value" );
pstmt.execute();
// Check that a warning was generated on the connection.
// Although not totally correct (the warning should be generated on
// the statement) the warning is generated while preparing the
// statement, so it belongs to the connection.
assertNotNull( con.getWarnings() );
pstmt.close();
Statement stmt = con.createStatement();
ResultSet rs = stmt.executeQuery( "SELECT * FROM prepFailWarning" );
assertTrue( rs.next() );
assertEquals( 1, rs.getInt( 1 ) );
assertFalse( rs.next() );
rs.close();
stmt.close();
}
finally
{
Statement stmt = con.createStatement();
stmt.execute( "DROP VIEW prepFailWarning" );
stmt.close();
}
}
}
/**
* Test that preparedstatement logic copes with commit modes and
* database changes.
*/
public void testPrepareModes() throws Exception {
//
// To see in detail what is happening enable logging and study the prepare
// statements that are being executed.
// For example if maxStatements=0 then the log should show that each
// statement is prepared and then unprepared at statement close.
// If maxStatements < 4 then you will see statements being unprepared
// when the cache is full.
//
// DriverManager.setLogStream(System.out);
Statement stmt = con.createStatement();
stmt.execute("CREATE TABLE #TEST (id int primary key, data varchar(255))");
//
// Statement prepared with auto commit = true
//
PreparedStatement pstmt1 = con.prepareStatement("INSERT INTO #TEST (id, data) VALUES (?,?)");
pstmt1.setInt(1, 1);
pstmt1.setString(2, "Line one");
assertEquals(1, pstmt1.executeUpdate());
//
// Move to manual commit mode
//
con.setAutoCommit(false);
//
// Ensure a new transaction is started
//
ResultSet rs = stmt.executeQuery("SELECT * FROM #TEST");
assertNotNull(rs);
rs.close();
//
// With Sybase this execution should cause a new proc to be created
// as we are now in chained mode
//
pstmt1.setInt(1, 2);
pstmt1.setString(2, "Line two");
assertEquals(1, pstmt1.executeUpdate());
//
// Statement prepared with auto commit = false
//
PreparedStatement pstmt2 = con.prepareStatement("SELECT * FROM #TEST WHERE id = ?");
pstmt2.setInt(1, 2);
rs = pstmt2.executeQuery();
assertNotNull(rs);
assertTrue(rs.next());
assertEquals("Line two", rs.getString("data"));
//
// Change catalog
//
String oldCat = con.getCatalog();
con.setCatalog("master");
//
// Executiion from another database should cause SQL Server to create
// a new handle or store proc
//
pstmt2.setInt(1, 1);
rs = pstmt2.executeQuery();
assertNotNull(rs);
assertTrue(rs.next());
assertEquals("Line one", rs.getString("data"));
//
// Now change back to original database
//
con.setCatalog(oldCat);
//
// Roll back transaction which should cause SQL Server procs (but not
// handles to be lost) causing statement to be prepared again.
//
pstmt2.setInt(1, 1);
rs = pstmt2.executeQuery();
assertNotNull(rs);
assertTrue(rs.next());
assertEquals("Line one", rs.getString("data"));
//
// Now return to auto commit mode
//
con.setAutoCommit(true);
//
// With Sybase statement will be prepared again as now in chained off mode
//
pstmt2.setInt(1, 1);
rs = pstmt2.executeQuery();
assertNotNull(rs);
assertTrue(rs.next());
assertEquals("Line one", rs.getString("data"));
pstmt2.close();
pstmt1.close();
stmt.close();
//
// Now we create a final prepared statement to demonstate that
// the cache is flushed correctly when the number of statements
// exceeds the cachesize. For example setting maxStatements=1
// will cause three statements to be unprepared when this statement
// is closed
//
pstmt1 = con.prepareStatement("SELECT id, data FROM #TEST");
pstmt1.executeQuery();
pstmt1.close();
}
/**
* Test that statements which cannot be prepared are remembered.
*/
public void testNoPrepare() throws Exception {
// DriverManager.setLogStream(System.out);
Statement stmt = con.createStatement();
stmt.execute("CREATE TABLE #TEST (id int primary key, data text)");
//
// Statement cannot be prepared on Sybase due to text field
//
PreparedStatement pstmt1 = con.prepareStatement("INSERT INTO #TEST (id, data) VALUES (?,?)");
pstmt1.setInt(1, 1);
pstmt1.setString(2, "Line one");
assertEquals(1, pstmt1.executeUpdate());
//
// This time should not try and prepare
//
pstmt1.setInt(1, 2);
pstmt1.setString(2, "Line two");
assertEquals(1, pstmt1.executeUpdate());
pstmt1.close();
}
/**
* Tests that float (single precision - 32 bit) values are not converted to
* double (thus loosing precision).
*/
public void testFloatValues() throws Exception {
Statement stmt = con.createStatement();
stmt.executeUpdate("create table #floatTest (v real)");
stmt.executeUpdate("insert into #floatTest (v) values (2.3)");
stmt.close();
PreparedStatement pstmt = con.prepareStatement(
"select * from #floatTest where v = ?");
pstmt.setFloat(1, 2.3f);
ResultSet rs = pstmt.executeQuery();
assertTrue(rs.next());
assertEquals(2.3f, rs.getFloat(1), 0);
assertTrue(rs.getObject(1) instanceof Float);
assertEquals(2.3f, ((Float) rs.getObject(1)).floatValue(), 0);
// Just make sure that conversion to double will break this
assertFalse(2.3 - rs.getDouble(1) == 0);
assertFalse(rs.next());
rs.close();
pstmt.close();
}
public void testNegativeScale() throws Exception {
Statement stmt = con.createStatement();
stmt.execute("CREATE TABLE #testNegativeScale (val decimal(28,10))");
PreparedStatement pstmt = con.prepareStatement(
"INSERT INTO #testNegativeScale VALUES(?)");
pstmt.setBigDecimal(1, new BigDecimal("2.9E7"));
assertEquals(1, pstmt.executeUpdate());
pstmt.close();
ResultSet rs = stmt.executeQuery("SELECT * FROM #testNegativeScale");
assertNotNull(rs);
assertTrue(rs.next());
assertEquals(29000000, rs.getBigDecimal(1).intValue());
stmt.close();
}
/**
* Test for bug [1623668] Lost apostrophes in statement parameter values(prepareSQL=0)
*/
public void testPrepareSQL0() throws Exception {
Properties props = new Properties();
props.setProperty("prepareSQL", "0");
Connection con = getConnection(props);
try {
Statement stmt = con.createStatement();
stmt.execute("CREATE TABLE #prepareSQL0 (position int, data varchar(32))");
stmt.close();
PreparedStatement ps = con.prepareStatement("INSERT INTO #prepareSQL0 (position, data) VALUES (?, ?)");
String data1 = "foo'foo";
String data2 = "foo''foo";
String data3 = "foo'''foo";
ps.setInt(1, 1);
ps.setString(2, data1);
ps.executeUpdate();
ps.setInt(1, 2);
ps.setString(2, data2);
ps.executeUpdate();
ps.setInt(1, 3);
ps.setString(2, data3);
ps.executeUpdate();
ps.close();
ps = con.prepareStatement("SELECT data FROM #prepareSQL0 ORDER BY position");
ResultSet rs = ps.executeQuery();
rs.next();
assertEquals(data1, rs.getString(1));
rs.next();
assertEquals(data2, rs.getString(1));
rs.next();
assertEquals(data3, rs.getString(1));
rs.close();
} finally {
con.close();
}
}
/**
* Test for bug [2814376] varchar-type is truncated in non-unicode
* environment.
*/
public void testMultiByteCharTruncation() throws Exception {
String encoding = "SJIS";
final int chars = 8000;
final String ch = new String("\u3042");
// create string with bytecount > charcount
StringBuilder sb = new StringBuilder();
for(int i = 0; i < chars; i++)
sb.append(ch);
final String text = sb.toString();
final int ID = 1;
final int length = text.getBytes(encoding).length;
// ensure string contains multi-byte chars
assertTrue(text.getBytes(encoding).length > chars);
// create connection with charset/sendStringParametersAsUnicode properties set
Properties newProps = new Properties(props);
newProps.put("charset", encoding);
newProps.put("sendStringParametersAsUnicode", "false");
Connection con = DriverManager.getConnection(newProps.getProperty("url"), newProps);
// create temporary table
Statement st = con.createStatement();
st.execute("create table #testUnicodeTrunc (id int primary key, data text)");
st.close();
// insert test date into table
PreparedStatement ps1 = con.prepareStatement("insert into #testUnicodeTrunc values(?,?)");
ps1.setInt(1, ID);
ps1.setString(2, text);
assertEquals(1, ps1.executeUpdate());
// read back test data
PreparedStatement ps2 = con.prepareStatement("select data from #testUnicodeTrunc where id = " + ID);
ResultSet rs = ps2.executeQuery();
// ensure the value is read back from DB without data loss
assertTrue(rs.next());
int rl = rs.getString(1).getBytes(encoding).length;
assertEquals("data truncated", length, rl);
assertEquals("data corrupted",text, rs.getString(1));
ps1.close();
ps2.close();
}
/**
* Test for bug [1374127], Arithmetic overflow at sql_variant.
*/
public void testArithmeticOverflow() throws Exception {
Statement st = con.createStatement();
st.execute("create table #testArithemicOverflow (id int primary key, data sql_variant)");
st.execute("insert into #testArithemicOverflow values (1,1)");
st.close();
long seed = System.currentTimeMillis();
Random r = new Random(seed);
Float value = new Float(0.000803f);
PreparedStatement ps1 = con.prepareStatement("update #testArithemicOverflow set data = ? where id = ?");
PreparedStatement ps2 = con.prepareStatement("select data from #testArithemicOverflow where id = ?");
try {
for (int i = 0; i < 1000; i++) {
if (i > 0) {
value = new Float(r.nextFloat() * Float.MAX_VALUE * (r.nextBoolean() ? 1 : -1));
}
ps1.setFloat(1, value.floatValue());
ps1.setInt(2, 1);
assertEquals(1, ps1.executeUpdate());
ps2.setInt(1, 1);
ResultSet rs = ps2.executeQuery();
assertTrue(rs.next());
assertEquals(value, new Float(rs.getFloat(1)));
rs.close();
}
} catch (Throwable t) {
System.out.println("seed " + seed + ", value " + value);
fail(t.getMessage());
} finally {
ps1.close();
ps2.close();
}
}
public static void main(String[] args) {
junit.textui.TestRunner.run(PreparedStatementTest.class);
}
}