Java Code Examples for java.sql.CallableStatement#setDate()
The following examples show how to use
java.sql.CallableStatement#setDate() .
You can vote up the ones you like or vote down the ones you don't like,
and go to the original project or source file by following the links above each example. You may check out the related API usage on the sidebar.
Example 1
| Source File: CallableTest.java From gemfirexd-oss with Apache License 2.0 | 6 votes |
|
/**
* Calls a SQL procedure that takes non-numeric IN and OUT parameters.
* @throws SQLException
*/
public void testNonNumericTypesInAndOutProc() throws SQLException
{
CallableStatement cs = prepareCall
("call NON_NUMERIC_TYPES_IN_AND_OUT_PROC(?,?,?,?,?,?,?,?)");
cs.setDate(1, Date.valueOf("2002-05-12"));
cs.setTime(2, Time.valueOf("10:05:02"));
cs.setTimestamp(3, Timestamp.valueOf("2002-05-12 10:05:02.000000000"));
byte[] ba = new byte[2];
ba[0] = 1;
ba[1] = 2;
cs.setBytes(4, ba);
cs.registerOutParameter (5, java.sql.Types.DATE);
cs.registerOutParameter (6, java.sql.Types.TIME);
cs.registerOutParameter (7, java.sql.Types.TIMESTAMP);
cs.registerOutParameter (8, java.sql.Types.VARBINARY);
cs.execute();
assertEquals("OUT date", Date.valueOf("2002-05-12"), cs.getDate(5));
assertEquals("OUT time" , Time.valueOf("10:05:02"), cs.getTime(6));
assertEquals("OUT timestamp" ,
Timestamp.valueOf("2002-05-12 10:05:02.000000000"),
cs.getTimestamp(7));
assertTrue(Arrays.equals(ba, cs.getBytes(8)));
}
Example 2
| Source File: CallableTest.java From gemfirexd-oss with Apache License 2.0 | 5 votes |
|
/**
* Calls a SQL procedure that takes non-numeric IN and OUT parameters.
* @throws SQLException
*/
public void testNonNumericTypesInAndOutProc() throws SQLException
{
CallableStatement cs = prepareCall
("call NON_NUMERIC_TYPES_IN_AND_OUT_PROC(?,?,?,?,?,?,?,?)");
cs.setDate(1, Date.valueOf("2002-05-12"));
cs.setTime(2, Time.valueOf("10:05:02"));
cs.setTimestamp(3, Timestamp.valueOf("2002-05-12 10:05:02.000000000"));
byte[] ba = new byte[2];
ba[0] = 1;
ba[1] = 2;
cs.setBytes(4, ba);
cs.registerOutParameter (5, java.sql.Types.DATE);
cs.registerOutParameter (6, java.sql.Types.TIME);
cs.registerOutParameter (7, java.sql.Types.TIMESTAMP);
cs.registerOutParameter (8, java.sql.Types.VARBINARY);
cs.execute();
assertEquals("OUT date", Date.valueOf("2002-05-12"), cs.getDate(5));
assertEquals("OUT time" , Time.valueOf("10:05:02"), cs.getTime(6));
assertEquals("OUT timestamp" ,
Timestamp.valueOf("2002-05-12 10:05:02.000000000"),
cs.getTimestamp(7));
assertTrue(Arrays.equals(ba, cs.getBytes(8)));
}
Example 3
| Source File: CallableTest.java From spliceengine with GNU Affero General Public License v3.0 | 5 votes |
|
/**
* Calls a SQL procedure that takes non-numeric IN and OUT parameters.
* @throws SQLException
*/
public void testNonNumericTypesInAndOutProc() throws SQLException
{
CallableStatement cs = prepareCall
("call NON_NUMERIC_TYPES_IN_AND_OUT_PROC(?,?,?,?,?,?,?,?)");
cs.setDate(1, Date.valueOf("2002-05-12"));
cs.setTime(2, Time.valueOf("10:05:02"));
cs.setTimestamp(3, Timestamp.valueOf("2002-05-12 10:05:02.000000000"));
byte[] ba = new byte[2];
ba[0] = 1;
ba[1] = 2;
cs.setBytes(4, ba);
cs.registerOutParameter (5, java.sql.Types.DATE);
cs.registerOutParameter (6, java.sql.Types.TIME);
cs.registerOutParameter (7, java.sql.Types.TIMESTAMP);
cs.registerOutParameter (8, java.sql.Types.VARBINARY);
cs.execute();
assertEquals("OUT date", Date.valueOf("2002-05-12"), cs.getDate(5));
assertEquals("OUT time" , Time.valueOf("10:05:02"), cs.getTime(6));
assertEquals("OUT timestamp" ,
Timestamp.valueOf("2002-05-12 10:05:02.000000000"),
cs.getTimestamp(7));
assertTrue(Arrays.equals(ba, cs.getBytes(8)));
}
Example 4
| Source File: CallableTest.java From spliceengine with GNU Affero General Public License v3.0 | 5 votes |
|
/**
* Private helper for {@link #testTimeAndDateWithCalendar()}. This method
* calls a procedure that takes Date, Time and Timestamp arguments and
* returns the exact same values. Call the setters with one calendar and
* the getters with another calendar, and verify that the expected
* conversion between time zones has happened.
*
* @param cal1 the calendar to use for the setter methods
* @param cal2 the calendar to use for the getter methods
*/
private void testTimeAndDateWithCalendar(Calendar cal1, Calendar cal2)
throws SQLException
{
println("Running " + getName() + "() with " +
cal1.getTimeZone().getDisplayName() + " and " +
cal2.getTimeZone().getDisplayName());
CallableStatement cs = prepareCall(
"call NON_NUMERIC_TYPES_IN_AND_OUT_PROC(?,?,?,?,?,?,?,?)");
Date d = Date.valueOf("2010-04-14");
Time t = Time.valueOf("12:23:24");
Timestamp ts = new Timestamp(System.currentTimeMillis());
ts.setNanos(123456789);
cs.setDate(1, d, cal1);
cs.setTime(2, t, cal1);
cs.setTimestamp(3, ts, cal1);
cs.setNull(4, Types.VARBINARY); // we don't care about VARBINARY here
cs.registerOutParameter (5, java.sql.Types.DATE);
cs.registerOutParameter (6, java.sql.Types.TIME);
cs.registerOutParameter (7, java.sql.Types.TIMESTAMP);
cs.registerOutParameter (8, java.sql.Types.VARBINARY);
cs.execute();
assertSameDate(d, cal1, cs.getDate(5, cal2), cal2);
assertSameTime(t, cal1, cs.getTime(6, cal2), cal2);
assertSameTimestamp(ts, cal1, cs.getTimestamp(7, cal2), cal2);
}
Example 5
| Source File: ProcedureUtils.java From ureport with Apache License 2.0 | 4 votes |
|
private static StatementWrapper buildProcedureCallableStatement(String sql,Map<String, Object> pmap,Connection conn){
try {
Map<String,Object> paramMap=new LinkedHashMap<String,Object>();
int leftParnPos=sql.indexOf("(");
int rightParnPos=sql.indexOf(")");
String paramStr="";
if(leftParnPos>-1 && rightParnPos>-1){
paramStr=sql.substring(leftParnPos+1,rightParnPos);
}
int oracleCursorIndex=-1,paramIndex=0;
String[] str=paramStr.split(",");
for(String param:str){
paramIndex++;
param=param.trim();
if(param.toLowerCase().equals("oracle")){
sql=sql.replaceFirst(param, "?");
oracleCursorIndex=paramIndex;
continue;
}else if(!param.startsWith(":")){
continue;
}
sql=sql.replaceFirst(param, "?");
String paramName=param.substring(1,param.length());
Object paramValue=pmap.get(paramName);
paramMap.put(paramName, (paramValue==null ? "" : paramValue));
}
String procedure="{"+sql+"}";
CallableStatement cs= conn.prepareCall(procedure);
int index=1;
for(String name:paramMap.keySet()){
Object value=paramMap.get(name);
if(value instanceof String){
cs.setString(index,(String)value);
}else if(value instanceof Date){
Date date=(Date)value;
cs.setDate(index, new java.sql.Date(date.getTime()));
}else if(value instanceof Integer){
cs.setInt(index, (Integer)value);
}else if(value instanceof Float){
cs.setFloat(index, (Float)value);
}else if(value instanceof Double){
cs.setDouble(index, (Double)value);
}else{
cs.setObject(index, value);
}
index++;
}
if(oracleCursorIndex>-1){
cs.registerOutParameter(oracleCursorIndex, -10);
}
return new StatementWrapper(cs,oracleCursorIndex);
} catch (SQLException e) {
throw new ReportException(e);
}
}
Example 6
| Source File: BatchUpdateTest.java From gemfirexd-oss with Apache License 2.0 | 4 votes |
|
public void testCallableStatementBatch() throws SQLException {
println("Positive Callable Statement: " +
"statement testing callable statement batch");
CallableStatement cs = prepareCall("insert into t1 values(?)");
cs.setInt(1, 1);
cs.addBatch();
cs.setInt(1,2);
cs.addBatch();
executeBatchCallableStatement(cs);
cleanUpCallableStatement(cs, "t1");
/* For 'beetle' bug 2813 - setDate/setTime/setTimestamp
* calls on callableStatement throws ClassNotFoundException
* verify setXXXX() works with Date, Time and Timestamp
* on CallableStatement.
*/
cs = prepareCall("insert into datetab values(?)");
cs.setDate(1, Date.valueOf("1990-05-05"));
cs.addBatch();
cs.setDate(1,Date.valueOf("1990-06-06"));
cs.addBatch();
executeBatchCallableStatement(cs);
cleanUpCallableStatement(cs, "datetab");
cs = prepareCall("insert into timetab values(?)");
cs.setTime(1, Time.valueOf("11:11:11"));
cs.addBatch();
cs.setTime(1, Time.valueOf("12:12:12"));
cs.addBatch();
executeBatchCallableStatement(cs);
cleanUpCallableStatement(cs, "timestamptab");
cs = prepareCall("insert into timestamptab values(?)");
cs.setTimestamp(1, Timestamp.valueOf("1990-05-05 11:11:11.1"));
cs.addBatch();
cs.setTimestamp(1, Timestamp.valueOf("1992-07-07 12:12:12.2"));
cs.addBatch();
executeBatchCallableStatement(cs);
cleanUpCallableStatement(cs, "timestamptab");
// Try with a user type
cs = prepareCall("insert into usertypetab values(?)");
cs.setObject(1, Date.valueOf("1990-05-05"));
cs.addBatch();
cs.setObject(1,Date.valueOf("1990-06-06"));
cs.addBatch();
executeBatchCallableStatement(cs);
cleanUpCallableStatement(cs, "usertypetab");
}
Example 7
| Source File: BackupRestoreBigDataTest.java From gemfirexd-oss with Apache License 2.0 | 4 votes |
|
/**
* Execute the given sql statement as a prepared statement with the given args.
*
* @param preparedStmt The CallableStatement to be executed
* @param sqlStmt The sql statement text to execute.
* @param args A List of Objects containing values to be used as the CallableStatement's arguments.
*
* @return The total bytes for the row that was inserted
*/
private static int executePreparedStatementUpdate(CallableStatement preparedStmt, String sqlStmt, List<Object> args) {
try {
// Log the sql statement that would be executed by the Prepared Statement
Object anArg;
StringBuilder argsStr = new StringBuilder();
for (int argCtr = 0;argCtr < args.size();argCtr++) {
anArg = args.get(argCtr);
if (anArg instanceof Clob) {
argsStr.append("' a Clob of size ").append(((Clob) anArg).length()).append("'");
} else {
argsStr.append("'").append(anArg).append("'");
}
if (argCtr < (args.size() - 1)) {
argsStr.append(", ");
}
}
String pkIndex;
if (sqlStmt.startsWith("INSERT")) {
pkIndex = args.get(0).toString();
} else {
pkIndex = args.get(args.size() - 1).toString();
}
// Set the values for the Prepared Statement
Object arg;
for (int argIndex = 1;argIndex <= args.size();argIndex++) {
arg = args.get(argIndex - 1);
if (arg instanceof String) {
preparedStmt.setString(argIndex, (String) arg);
} else if (arg instanceof Integer) {
preparedStmt.setInt(argIndex, (Integer) arg);
} else if (arg instanceof Date) {
preparedStmt.setDate(argIndex, (Date) arg);
} else if (arg instanceof Clob) {
preparedStmt.setClob(argIndex, (Clob) arg);
}
}
// Execute the Prepared Statement
logWriter.fine("BackupRestoreBigDataTest.executePreparedStatementUpdate-sqlStmt=" + sqlStmt +
" with arg(s): " + argsStr +
"), primaryKey is " + pkIndex);
logWriter.info("Executing sqlStmt " + sqlStmt +
" on primaryKey: " + pkIndex + ".");
int result = preparedStmt.executeUpdate();
logWriter.info("BackupRestoreBigDataTest.executePreparedStatementUpdate-result=" + result);
return result;
} catch (SQLException e) {
throw new TestException(TestHelper.getStackTrace(e));
}
}
Example 8
| Source File: BatchUpdateTest.java From gemfirexd-oss with Apache License 2.0 | 4 votes |
|
public void testCallableStatementBatch() throws SQLException {
println("Positive Callable Statement: " +
"statement testing callable statement batch");
CallableStatement cs = prepareCall("insert into t1 values(?)");
cs.setInt(1, 1);
cs.addBatch();
cs.setInt(1,2);
cs.addBatch();
executeBatchCallableStatement(cs);
cleanUpCallableStatement(cs, "t1");
/* For 'beetle' bug 2813 - setDate/setTime/setTimestamp
* calls on callableStatement throws ClassNotFoundException
* verify setXXXX() works with Date, Time and Timestamp
* on CallableStatement.
*/
cs = prepareCall("insert into datetab values(?)");
cs.setDate(1, Date.valueOf("1990-05-05"));
cs.addBatch();
cs.setDate(1,Date.valueOf("1990-06-06"));
cs.addBatch();
executeBatchCallableStatement(cs);
cleanUpCallableStatement(cs, "datetab");
cs = prepareCall("insert into timetab values(?)");
cs.setTime(1, Time.valueOf("11:11:11"));
cs.addBatch();
cs.setTime(1, Time.valueOf("12:12:12"));
cs.addBatch();
executeBatchCallableStatement(cs);
cleanUpCallableStatement(cs, "timestamptab");
cs = prepareCall("insert into timestamptab values(?)");
cs.setTimestamp(1, Timestamp.valueOf("1990-05-05 11:11:11.1"));
cs.addBatch();
cs.setTimestamp(1, Timestamp.valueOf("1992-07-07 12:12:12.2"));
cs.addBatch();
executeBatchCallableStatement(cs);
cleanUpCallableStatement(cs, "timestamptab");
// Try with a user type
cs = prepareCall("insert into usertypetab values(?)");
cs.setObject(1, Date.valueOf("1990-05-05"));
cs.addBatch();
cs.setObject(1,Date.valueOf("1990-06-06"));
cs.addBatch();
executeBatchCallableStatement(cs);
cleanUpCallableStatement(cs, "usertypetab");
}
Example 9
| Source File: BackupRestoreBigDataTest.java From gemfirexd-oss with Apache License 2.0 | 4 votes |
|
/**
* Execute the given sql statement as a prepared statement with the given args.
*
* @param preparedStmt The CallableStatement to be executed
* @param sqlStmt The sql statement text to execute.
* @param args A List of Objects containing values to be used as the CallableStatement's arguments.
*
* @return The total bytes for the row that was inserted
*/
private static int executePreparedStatementUpdate(CallableStatement preparedStmt, String sqlStmt, List<Object> args) {
try {
// Log the sql statement that would be executed by the Prepared Statement
Object anArg;
StringBuilder argsStr = new StringBuilder();
for (int argCtr = 0;argCtr < args.size();argCtr++) {
anArg = args.get(argCtr);
if (anArg instanceof Clob) {
argsStr.append("' a Clob of size ").append(((Clob) anArg).length()).append("'");
} else {
argsStr.append("'").append(anArg).append("'");
}
if (argCtr < (args.size() - 1)) {
argsStr.append(", ");
}
}
String pkIndex;
if (sqlStmt.startsWith("INSERT")) {
pkIndex = args.get(0).toString();
} else {
pkIndex = args.get(args.size() - 1).toString();
}
// Set the values for the Prepared Statement
Object arg;
for (int argIndex = 1;argIndex <= args.size();argIndex++) {
arg = args.get(argIndex - 1);
if (arg instanceof String) {
preparedStmt.setString(argIndex, (String) arg);
} else if (arg instanceof Integer) {
preparedStmt.setInt(argIndex, (Integer) arg);
} else if (arg instanceof Date) {
preparedStmt.setDate(argIndex, (Date) arg);
} else if (arg instanceof Clob) {
preparedStmt.setClob(argIndex, (Clob) arg);
}
}
// Execute the Prepared Statement
logWriter.fine("BackupRestoreBigDataTest.executePreparedStatementUpdate-sqlStmt=" + sqlStmt +
" with arg(s): " + argsStr +
"), primaryKey is " + pkIndex);
logWriter.info("Executing sqlStmt " + sqlStmt +
" on primaryKey: " + pkIndex + ".");
int result = preparedStmt.executeUpdate();
logWriter.info("BackupRestoreBigDataTest.executePreparedStatementUpdate-result=" + result);
return result;
} catch (SQLException e) {
throw new TestException(TestHelper.getStackTrace(e));
}
}
Example 10
| Source File: JDBCExecutor.java From amforeas with GNU General Public License v3.0 | 4 votes |
|
/**
* Utility method which registers in a CallableStatement object the different {@link amforeas.jdbc.StoredProcedureParam}
* instances in the given list. Returns a List of {@link amforeas.jdbc.StoredProcedureParam} with all the OUT parameters
* registered in the CallableStatement
* @param cs the CallableStatement object where the parameters are registered.
* @param params a list of {@link amforeas.jdbc.StoredProcedureParam}
* @return a list of OUT {@link amforeas.jdbc.StoredProcedureParam}
* @throws SQLException if we fail to register any of the parameters in the CallableStatement
* @throws AmforeasBadRequestException
*/
private List<StoredProcedureParam> addParameters (final CallableStatement cs, final List<StoredProcedureParam> params) throws SQLException, AmforeasBadRequestException {
final List<StoredProcedureParam> outParams = new ArrayList<StoredProcedureParam>();
int i = 1;
for (StoredProcedureParam p : params) {
final Integer sqlType = p.getSqlType();
if (p.isOutParameter()) {
l.debug("Adding OUT parameter " + p.toString());
cs.registerOutParameter(i++, sqlType);
outParams.add(p);
} else {
l.debug("Adding IN parameter " + p.toString());
switch (sqlType) {
case Types.BIGINT:
case Types.INTEGER:
case Types.TINYINT:
// case Types.NUMERIC:
cs.setInt(i++, Integer.valueOf(p.getValue()));
break;
case Types.DATE:
cs.setDate(i++, (Date) AmforeasUtils.parseValue(p.getValue()));
break;
case Types.TIME:
cs.setTime(i++, (Time) AmforeasUtils.parseValue(p.getValue()));
break;
case Types.TIMESTAMP:
cs.setTimestamp(i++, (Timestamp) AmforeasUtils.parseValue(p.getValue()));
break;
case Types.DECIMAL:
cs.setBigDecimal(i++, (BigDecimal) AmforeasUtils.parseValue(p.getValue()));
break;
case Types.DOUBLE:
cs.setDouble(i++, Double.valueOf(p.getValue()));
break;
case Types.FLOAT:
cs.setLong(i++, Long.valueOf(p.getValue()));
break;
default:
cs.setString(i++, p.getValue());
break;
}
}
}
return outParams;
}
Example 11
| Source File: BatchUpdateTest.java From spliceengine with GNU Affero General Public License v3.0 | 4 votes |
|
public void testCallableStatementBatch() throws SQLException {
println("Positive Callable Statement: " +
"statement testing callable statement batch");
CallableStatement cs = prepareCall("insert into t1 values(?)");
cs.setInt(1, 1);
cs.addBatch();
cs.setInt(1,2);
cs.addBatch();
executeBatchCallableStatement(cs);
cleanUpCallableStatement(cs, "t1");
/* For 'beetle' bug 2813 - setDate/setTime/setTimestamp
* calls on callableStatement throws ClassNotFoundException
* verify setXXXX() works with Date, Time and Timestamp
* on CallableStatement.
*/
cs = prepareCall("insert into datetab values(?)");
cs.setDate(1, Date.valueOf("1990-05-05"));
cs.addBatch();
cs.setDate(1,Date.valueOf("1990-06-06"));
cs.addBatch();
executeBatchCallableStatement(cs);
cleanUpCallableStatement(cs, "datetab");
cs = prepareCall("insert into timetab values(?)");
cs.setTime(1, Time.valueOf("11:11:11"));
cs.addBatch();
cs.setTime(1, Time.valueOf("12:12:12"));
cs.addBatch();
executeBatchCallableStatement(cs);
cleanUpCallableStatement(cs, "timestamptab");
cs = prepareCall("insert into timestamptab values(?)");
cs.setTimestamp(1, Timestamp.valueOf("1990-05-05 11:11:11.1"));
cs.addBatch();
cs.setTimestamp(1, Timestamp.valueOf("1992-07-07 12:12:12.2"));
cs.addBatch();
executeBatchCallableStatement(cs);
cleanUpCallableStatement(cs, "timestamptab");
// Try with a user type
cs = prepareCall("insert into usertypetab values(?)");
cs.setObject(1, Date.valueOf("1990-05-05"));
cs.addBatch();
cs.setObject(1,Date.valueOf("1990-06-06"));
cs.addBatch();
executeBatchCallableStatement(cs);
cleanUpCallableStatement(cs, "usertypetab");
}
