prepstmt.java

derby database source code.good for you.

			 rs = tempPreparedStatement[i].executeQuery();
			 rs.close();
		}
		for (int i = 0; i < numOfPreparedStatement; i++) 
			tempPreparedStatement[i].close();
		
	}
	
	private static void test5172(Connection conn) throws Exception
	{
		
		Statement stmt = conn.createStatement();
		
		try {
			stmt.executeUpdate("drop table tab1");
		}
		catch (SQLException se)
		{
	}
		
		stmt.executeUpdate( "CREATE TABLE TSTAB (I int, STATUS_TS  Timestamp, PROPERTY_TS Timestamp)" );
		stmt.executeUpdate("INSERT INTO TSTAB VALUES(1 , '2003-08-15 21:20:00','2003-08-15 21:20:00')");
		stmt.executeUpdate("INSERT INTO TSTAB VALUES(2 , '1969-12-31 16:00:00.0', '2003-08-15 21:20:00')");
		
		stmt.close();
		
		String timestamp = "20";
		String query =  "select STATUS_TS  " +
			"from   TSTAB " +
			"where  (STATUS_TS >= ? or " +
			"               PROPERTY_TS<?)";

		System.out.println("Negative test setString with Invalid Timestamp:" + timestamp);

		PreparedStatement ps = conn.prepareStatement(query);
		ps.setString(1,timestamp);
		ps.setString(2, timestamp );
		try {
			ResultSet rs = ps.executeQuery();
		}
		catch (SQLException e) {
			System.out.println("SQLState: " + e.getSQLState() + " message: " + e.getMessage());
		}

	}


	private static void test4975(Connection conn) throws Exception
	{
		BigDecimal minBigDecimalVal = null;
		BigDecimal rBigDecimalVal = null;
		String sminBigDecimalVal = null;

		PreparedStatement pstmt = null;
		ResultSet rs = null;
		Statement stmt = null;

		try
		{
			stmt = conn.createStatement();
			String createTableSQL = "create table Numeric_Tab (MAX_VAL NUMERIC(30,15), MIN_VAL NUMERIC(30,15), NULL_VAL NUMERIC(30,15) DEFAULT NULL)";
			// to create the Numeric Table
			stmt.executeUpdate(createTableSQL);
			
			String insertSQL = "insert into Numeric_Tab values(999999999999999,0.000000000000001, null)";
			stmt.executeUpdate(insertSQL);

			//to extract the Maximum Value of BigDecimal to be Updated 
			sminBigDecimalVal = "0.000000000000001";
			minBigDecimalVal = new BigDecimal(sminBigDecimalVal);
			logMsg("Minimum BigDecimal Value: " + minBigDecimalVal);

			// to update Null value column with Minimum value 
			String sPrepStmt = "update Numeric_Tab set NULL_VAL=?";

			// Uncomment and prepare the below statement instead to see JCC bug on setObject for decimal
			//String sPrepStmt = "update Numeric_Tab set NULL_VAL="+sminBigDecimalVal+" where 0.0 != ?";
			logMsg("Prepared Statement String: " + sPrepStmt);
			
			// get the PreparedStatement object
			pstmt = conn.prepareStatement(sPrepStmt);
			pstmt.setObject(1,minBigDecimalVal);
			pstmt.executeUpdate();

			//to query from the database to check the call of pstmt.executeUpdate
			//to get the query string
			String Null_Val_Query = "Select NULL_VAL from Numeric_Tab";
			logMsg(Null_Val_Query);
			rs = stmt.executeQuery(Null_Val_Query);
			rs.next();

			rBigDecimalVal = (BigDecimal) rs.getObject(1);
			logMsg("Returned BigDecimal Value after Updation: " + rBigDecimalVal);
			logMsg("Value returned from stmt: " + minBigDecimalVal);

			if(rBigDecimalVal.compareTo(minBigDecimalVal) == 0)
			{
				logMsg("setObject Method sets the designated parameter with the Object");
			}
			else
			{
				logErr("setObject Method does not set the designated parameter with the Object");
				throw new Exception("Call to setObject Method is Failed!");
			}
		}
		catch(SQLException sqle)
		{
			logErr("SQL Exception: " + sqle.getMessage());
			throw new Exception("Call to setObject is Failed!");
		}
		catch(Exception e)
		{
			logErr("Unexpected Exception: " + e.getMessage());
			throw new Exception("Call to setObject is Failed!");
		}

		finally
		{
			try
			{
				if(rs != null)
				{
					 rs.close();
					 rs = null;
				}
				if(pstmt != null)
				{
					 pstmt.close();
					 pstmt = null;
				}
				stmt.executeUpdate("drop table Numeric_Tab");
				if(stmt != null)
				{
					 stmt.close();
					 stmt = null;
				}
			}
			catch(Exception e){ }
		}
	}

	private static void logErr(String s)
	{
		System.err.println(s);
	}

	private static void logMsg(String s)
	{
		System.out.println(s);
	}

	private static void insertTab(Connection conn, String tabname , int numCols) throws SQLException
	{
		PreparedStatement ps = null;
		System.out.println("insertTab ( " + tabname + ","  + numCols + ")" );
		String insertSql = "insert into " + tabname + "(";
 		for (int i = 1; i <= numCols; i++)
		{
			insertSql += " c"+ i;
			if (i != numCols)
				insertSql += ", ";
			else 
				insertSql += ")";
		}
		insertSql += "  values (";
		for (int i = 1; i <= numCols; i++)
		{
			insertSql += "?";
			if (i != numCols)
				insertSql += ", ";
			else 
				insertSql += " )";
		}

		try {
			ps = conn.prepareStatement(insertSql);
			
			for (int i = 1; i <= numCols; i++)
				ps.setInt(i,i);
			ps.executeUpdate();
		} catch (SQLException e)
		{
			System.out.println("SQLState: " + e.getSQLState() + 
							   " message: " + e.getMessage());			
		}
		
	}

	private static void selectFromBigTab(Connection conn) throws SQLException
	{
		PreparedStatement ps = null;
		ResultSet rs = null;

		String selectSQL = "select * from bigtab";
		System.out.println(selectSQL);
		ps = conn.prepareStatement(selectSQL);
		rs = ps.executeQuery();
		while (rs.next())
		{
			System.out.println("Col # 500 = " + rs.getObject(500) +
					   "  Col 1000 = " + rs.getObject(1000));  
		}
		
		rs.close();
		ps.close();
   
	}
	private static void testBigDecimalSetObject(Connection conn) throws SQLException
	{
		setupDoubleTab(conn);
		testBigDecimalToDoubleConversion(conn);
	}



	private static void setupDoubleTab(Connection conn) throws SQLException
	{
		String sql;
		Statement stmt = conn.createStatement();
		try {
			stmt.executeUpdate("DROP TABLE doubletab");
		}
		catch (SQLException se)
		{
			//System.out.println("Table doubletab not dropped. " + se.getMessage());

		}

		sql = "CREATE TABLE doubletab (i int, doubleVal DOUBLE)";

		System.out.println(sql);
		stmt.executeUpdate(sql);
		conn.commit();
		
	}

	private static void testBigDecimalToDoubleConversion(Connection conn) throws SQLException
	{
		System.out.println("\n\ntestBigDecimalToDoubleConversion().");
		System.out.println(" Check that values are preserved when BigDecimal \n values which have more than 31 digits are converted \n to Double with setObject");		
		ResultSet rs = null;
		// Insert various double values
		double[] doubleVals = {1.0E-130,1.0E125, 0, -1.0E124};
		//BigDecimal[] bigDecimalVals = new BigDecimal[doubleVals.length];
		BigDecimal[] bigDecimalVals = { new BigDecimal(1.0E-130), 
										new BigDecimal(1.0E125),
										new BigDecimal(-1.0E124) ,
										new
											BigDecimal("12345678901234567890123456789012"),
										new BigDecimal("1.2345678901234567890123456789012")
		};

		String isql = "INSERT INTO doubletab VALUES (?, ?)";
		//System.out.println("conn.prepareStatement(" + isql +")");
		PreparedStatement insPs = conn.prepareStatement(isql);	  
		String ssql = "SELECT doubleVal FROM doubletab";
		PreparedStatement selPs = conn.prepareStatement(ssql);
		String dsql = "DELETE FROM doubletab";
		PreparedStatement delPs = conn.prepareStatement(dsql);
		for (int i = 0; i < bigDecimalVals.length; i ++)
		{
			BigDecimal bd = bigDecimalVals[i];
			// Insert value
			//System.out.println("ps.setObject(1," + bd + ",java.sql.Types.DOUBLE)");						
			insPs.setInt(1,i);
			insPs.setObject(2,bd,java.sql.Types.DOUBLE);
			insPs.executeUpdate();
			// Check Value
			rs = selPs.executeQuery();
			rs.next();
			checkDoubleMatch(bd.doubleValue() , rs.getDouble(1));
			// Clear out the table;
			delPs.executeUpdate();
		}
		insPs.close();
		selPs.close();
		delPs.close();
		rs.close();
		conn.commit();
	}

	static void testBigDecimalSetObjectWithScale(Connection conn) throws Exception
	{
		Statement stmt = conn.createStatement();
		String sql = null;

	System.out.println("\n\ntestBigDecimalSetObjectWithScale(). \nPass scale parameter of setObject");		

		try {
			stmt.executeUpdate("DROP TABLE numtab");
		}
		catch (SQLException se)
		{
			//System.out.println("Table numtab not dropped. " + se.getMessage());
		}
		sql = "CREATE TABLE numtab (num NUMERIC(10,6))";
		stmt.executeUpdate(sql);
		
		// make a big decimal from string
		BigDecimal bdFromString = new BigDecimal("2.33333333");
		
		// prepare a statement which updates the third column of the table with
		// the DOUBLE columns
		sql =  "INSERT INTO  numtab  VALUES(?)";
		PreparedStatement ps =  conn.prepareStatement(sql);
		// setObject using the big decimal value
		//System.out.println("ps.setObject(1," + bdFromString +		* ",java.sql.Types.DECIMAL,2)");
		int scale = 2;
		ps.setObject(1,bdFromString,java.sql.Types.DECIMAL,scale);
		ps.executeUpdate();
		// check the value
		sql = "SELECT num FROM numtab";
		ResultSet rs = stmt.executeQuery(sql);
		rs.next();
		// Check that the correct scale was set
		checkBigDecimalMatch(bdFromString.setScale(scale,
												   BigDecimal.ROUND_DOWN),
							 (BigDecimal)rs.getObject(1));
		rs.close();
		ps.close();
		stmt.close();

		conn.commit();
 	}

	private static void checkDoubleMatch(double expectedValue, double
										 actualValue) 	{
		if (actualValue == expectedValue)
			System.out.println("PASS: Actual value " + actualValue + " matches expected value: " + expectedValue);
		else
			new Exception("FAIL: Actual value: " + actualValue +
							" does not match expected value:" +