Java Code Examples for java.sql.CallableStatement#setString()

The following examples show how to use java.sql.CallableStatement#setString() . These examples are extracted from open source projects. 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
/**
 * Helper method for testing getCrossReference - calls the ODBC procedure
 * @throws SQLException
 */
private ResultSet getCrossReferenceODBC(
        String parentcatalog, String parentschema, String parenttable,
        String foreigncatalog, String foreignschema, String foreigntable)
    throws SQLException
{
    CallableStatement cs = prepareCall("CALL SYSIBM.SQLFOREIGNKEYS(" +
            "?, ?, ?, ?, ?, ?, 'DATATYPE=''ODBC''')");

    cs.setString(1, parentcatalog);
    cs.setString(2, parentschema);
    cs.setString(3, parenttable);
    cs.setString(4, foreigncatalog);
    cs.setString(5, foreignschema);
    cs.setString(6, foreigntable);
    cs.execute();
    return cs.getResultSet();
}
 
Example 2
Source Project: gemfirexd-oss   File: CollationTest2.java    License: Apache License 2.0 6 votes vote down vote up
/**
 * Perform import using SYSCS_UTIL.IMPORT_TABLE procedure.
 */
protected void doImportTable(
String      schemaName, 
String      tableName, 
String      fileName, 
String      colDel, 
String      charDel, 
String      codeset,
int         replace) 
    throws SQLException 
{
    CallableStatement ps = 
        prepareCall(
            "call SYSCS_UTIL.IMPORT_TABLE (?, ?, ?, ?, ?, ?, ?)");
    ps.setString(1, schemaName);
    ps.setString(2, tableName);
    ps.setString(3, fileName);
    ps.setString(4, colDel);
    ps.setString(5, charDel);
    ps.setString(6, codeset);
    ps.setInt(   7, replace);
    ps.executeUpdate();
    ps.close();
}
 
Example 3
public static CallableStatement getStoreProcedureInvocation(Connection conn,int qNum, int startNum, int hopNum, boolean  runStats, boolean distinct, boolean indices, boolean runStatsSampledIndexAll) throws SQLException {
	String procName;// = runStats?"DB2INST2.GENPROC1" : "DB2INST2.GENPROC3";
	
	if (runStats) {
		if (indices) {
			procName = runStatsSampledIndexAll? "DB2INST2.GENPROC4" :"DB2INST2.GENPROC5";
		} else {
			procName =  "DB2INST2.GENPROC1";
		}
	} else {
		procName = "DB2INST2.GENPROC3";
	}	
	CallableStatement cstmt = conn.prepareCall("CALL "+procName+"(?, ?, ?,?,  ?)");
       cstmt.setString(1, getStartingPointStmt(qNum, startNum, hopNum));
       cstmt.setString(2, getRecursionStmt("SESSION.DELTAT", "SESSION.OLDDELTAT", distinct));
       cstmt.setString(3, getRecursionStmt("SESSION.OLDDELTAT", "SESSION.DELTAT", distinct));
     
       
       cstmt.setString(4, getFinalStmt());
       cstmt.setInt(5, hopNum);
       cstmt.execute();
       return cstmt;
}
 
Example 4
Source Project: gemfirexd-oss   File: CollationTest2.java    License: Apache License 2.0 6 votes vote down vote up
/**
 * Perform import using SYSCS_UTIL.IMPORT_TABLE procedure.
 */
protected void doImportTable(
String      schemaName, 
String      tableName, 
String      fileName, 
String      colDel, 
String      charDel, 
String      codeset,
int         replace) 
    throws SQLException 
{
    CallableStatement ps = 
        prepareCall(
            "call SYSCS_UTIL.IMPORT_TABLE (?, ?, ?, ?, ?, ?, ?)");
    ps.setString(1, schemaName);
    ps.setString(2, tableName);
    ps.setString(3, fileName);
    ps.setString(4, colDel);
    ps.setString(5, charDel);
    ps.setString(6, codeset);
    ps.setInt(   7, replace);
    ps.executeUpdate();
    ps.close();
}
 
Example 5
/**
 * Tests the SYSIBM.CLOBGETPOSITIONFROMLOCATOR stored procedure.
 *
 * @throws SQLException.
 */
public void testClobGetPositionFromLocatorSP() throws SQLException {
    int locator = 0;

    String newStr = "simple";

    CallableStatement cs  = prepareCall
        ("? = CALL SYSIBM.CLOBCREATELOCATOR()");
    cs.registerOutParameter(1, java.sql.Types.INTEGER);
    cs.executeUpdate();
    locator = cs.getInt(1);
    cs.close();

    cs  = prepareCall("CALL SYSIBM.CLOBSETSTRING(?,?,?,?)");
    cs.setInt(1, locator);
    cs.setInt(2, 1);
    cs.setLong(3, newStr.length());
    cs.setString(4, newStr);
    cs.execute();

    cs.close();
    cs  = prepareCall
        ("? = CALL SYSIBM.CLOBGETPOSITIONFROMLOCATOR(?,?,?)");
    cs.registerOutParameter(1, java.sql.Types.BIGINT);
    cs.setInt(2, 1);
    //find the position of the bytes corresponding to
    //the String simple in the test string.
    cs.setInt(3, locator);
    cs.setLong(4, 1L);
    cs.executeUpdate();
    //check to see that the returned position and the expected position
    //of the substring simple in the string are matching.
    assertEquals("Error SYSIBM.CLOBGETPOSITIONFROMLOCATOR returns " +
        "the wrong value for the position of the Clob", 8, cs.getLong(1));
    cs.close();
}
 
Example 6
Source Project: gemfirexd-oss   File: GfxdCallbacksTest.java    License: Apache License 2.0 5 votes vote down vote up
public static void addListener(String listenerID, String schemaName,
    String tableName, String functionStr, String initInfoStr, String serverGroups) throws SQLException {
  Connection conn = getConnection();
  CallableStatement cs = conn
      .prepareCall("CALL SYS.ADD_LISTENER(?,?,?,?,?,?)");
  cs.setString(1, listenerID);
  cs.setString(2, schemaName);
  cs.setString(3, tableName);
  cs.setString(4, functionStr);
  cs.setString(5, initInfoStr);
  cs.setString(6, serverGroups);
  cs.execute();
}
 
Example 7
private void installJar(String resource, String jarName) throws SQLException, MalformedURLException
{        
    URL jar = SupportFilesSetup.getReadOnlyURL(resource);
    
    assertNotNull(resource, jar);
    
    CallableStatement cs = prepareCall("CALL SQLJ.INSTALL_JAR(?, ?, 0)");
    cs.setString(1, jar.toExternalForm());
    cs.setString(2, jarName);
    cs.executeUpdate();
    cs.close();
}
 
Example 8
/**
 * This method executes a callable statement to set the database property
 * to a given value. It checks that reset to any value other than "true"
 * fails.
 *
 * @param cs CallableStatement object used to set database property
 * @param value value of database property
 * @throws SQLException
 */
private void testPropertyReset(CallableStatement cs, String value) throws SQLException {

  cs.setString(2, value);

  try {
        cs.executeUpdate();
        if(value.compareToIgnoreCase("true") != 0)
          fail("FAIL: Should not be possible to reset sql authorization once it has been turned on");
      } catch (SQLException sqle) {
        assertSQLState(SQLStateConstants.PROPERTY_UNSUPPORTED_CHANGE, sqle);
      }

}
 
Example 9
public void testChangeIsoLevelCallableStatementSQL() throws SQLException {
    Connection c = getConnection();
    CallableStatement cs = prepareCall("CALL SET_ISOLATION_SQL(?)");
    for (int i = 0; i < 4; ++i) {
        cs.setString(1, isoLevels[i].getSqlName());
        cs.execute();
        verifyCachedIsolation(c);
    }
    cs.close();
}
 
Example 10
@Override
public int insertTU(int headerId, String tuId, String creationId, String creationDate, String changeId,
		String changeDate, String creationTool, String creationToolVersion, String client, String projectRef,
		String jobRef) throws SQLException {
	CallableStatement stmt = null;
	ResultSet rs = null;
	try {
		String sql = dbConfig.getOperateDbSQL("insert-tu");
		if (null == conn) {
			return -1;
		}
		stmt = conn.prepareCall(sql);
		int i = 1;
		stmt.setInt(i++, headerId);
		stmt.setString(i++, tuId);
		stmt.setString(i++, creationId);
		stmt.setTimestamp(i++, DateUtils.getTimestampFromUTC(creationDate));
		stmt.setString(i++, changeId);
		stmt.setTimestamp(i++, DateUtils.getTimestampFromUTC(changeDate));
		stmt.setString(i++, creationTool);
		stmt.setString(i++, creationToolVersion);
		stmt.setString(i++, client);
		stmt.setString(i++, projectRef);
		stmt.setString(i++, jobRef);
		stmt.registerOutParameter(i, Types.INTEGER);
		stmt.executeUpdate();
		return  stmt.getInt(i);
	} finally {
		if (rs != null) {
			rs.close();
		}
		if (stmt != null) {
			stmt.close();
		}
	}
}
 
Example 11
Source Project: pinpoint   File: MariaDB_IT_Base.java    License: Apache License 2.0 5 votes vote down vote up
protected final void executeCallableStatement() throws Exception {

        final int expectedResultSize = 1;
        final int expectedTotalCount = 3;
        final int expectedMatchingId = 2;
        final String outputParamCountName = "outputParamCount";

        Connection conn = null;
        CallableStatement cs = null;
        ResultSet rs = null;
        try {
            conn = DriverManager.getConnection(JDBC_URL, "root", null);

            cs = conn.prepareCall(CALLABLE_STATEMENT_QUERY);
            cs.setString(1, CALLABLE_STATEMENT_INPUT_PARAM);
            cs.registerOutParameter(2, CALLABLE_STATMENT_OUTPUT_PARAM_TYPE);

            rs = cs.executeQuery();
            int resultCount = 0;
            while (rs.next()) {
                ++resultCount;
                if (resultCount > expectedResultSize) {
                    fail();
                }
                assertEquals(expectedMatchingId, rs.getInt(1));
                assertEquals(CALLABLE_STATEMENT_INPUT_PARAM, rs.getString(2));
            }
            assertEquals(expectedResultSize, resultCount);

            final int totalCount = cs.getInt(outputParamCountName);
            assertEquals(expectedTotalCount, totalCount);

        } finally {
            closeResultSet(rs);
            closeStatement(cs);
            closeConnection(conn);
        }
    }
 
Example 12
/**
 * 将TMX的header接点的主要属性写入到mheader表中
 * @throws SQLException
 */
public String insertHeader(Hashtable<String, String> params) throws SQLException {
	CallableStatement stmt = null;
	try {
		String sql = dbConfig.getOperateDbSQL("insert-mheader");
		stmt = conn.prepareCall(sql);
		int i = 1;
		stmt.setString(i++, params.get("CREATIONTOOL"));
		stmt.setString(i++, params.get("CTVERSION"));
		stmt.setString(i++, params.get("TMF"));
		stmt.setString(i++, params.get("SRCLANG"));
		stmt.setString(i++, params.get("ADMINLANG"));
		stmt.setString(i++, params.get("DATATYPE"));
		stmt.setString(i++, params.get("SEGTYPE"));
		stmt.setString(i++, params.get("CREATIONID"));
		stmt.setString(i++, params.get("CREATIONDATE"));
		stmt.setString(i++, params.get("CHANGEID"));
		stmt.setString(i++, params.get("CHANGEDATE"));
		stmt.setString(i++, params.get("ENCODING"));
		stmt.registerOutParameter(i++, Types.INTEGER);
		stmt.execute();
		return stmt.getString(i - 1);
	} finally {
		if (stmt != null) {
			stmt.close();
		}
	}
}
 
Example 13
Source Project: quetzal   File: TestDB2Graph2.java    License: Eclipse Public License 2.0 5 votes vote down vote up
public static CallableStatement getStoreProcedureInvocation(Connection conn,int qNum, int startNum, int hopNum, boolean  runStats, boolean distinct, boolean indices, boolean runStatsSampledIndexAll) throws SQLException {
	String procName; // = runStats? "DB2INST2.GENPROC2":"DB2INST2.GENPROC0";
	if (runStats) {
		procName =  "DB2INST2.GENPROC2";
	} else {
		procName = "DB2INST2.GENPROC0";
	}
	//String procName = runStats? "DB2INST2.GENPROC2":"DB2INST2.GENPROC0";
	CallableStatement cstmt = conn.prepareCall("CALL "+procName+"(?, ?, ?, ?)"); cstmt.setString(1, getStartingPointStmt(qNum, startNum, hopNum));
       cstmt.setString(2, getRecursionStmt(distinct));
       cstmt.setString(3, getFinalStmt());
       cstmt.setInt(4, hopNum);
       cstmt.execute();
       return cstmt;
}
 
Example 14
private void removeJar(String jarName) throws SQLException
{
    CallableStatement cs = prepareCall("CALL SQLJ.REMOVE_JAR(?, 0)");       
    cs.setString(1, jarName);       
    cs.executeUpdate();        
    cs.close();
}
 
Example 15
public int getTestcasesCount( String whereClause ) throws DatabaseAccessException {

        String sqlLog = new SqlRequestFormatter().add("where", whereClause).format();
        Connection connection = getConnection();
        CallableStatement callableStatement = null;
        ResultSet rs = null;
        try {

            callableStatement = connection.prepareCall("{ call sp_get_testcases_count(?) }");
            callableStatement.setString(1, whereClause);

            rs = callableStatement.executeQuery();
            int testcasesCount = 0;
            while (rs.next()) {
                testcasesCount = rs.getInt("testcasesCount");
                logQuerySuccess(sqlLog, "test cases", testcasesCount);
                break;
            }

            return testcasesCount;
        } catch (Exception e) {
            throw new DatabaseAccessException("Error when " + sqlLog, e);
        } finally {
            DbUtils.closeResultSet(rs);
            DbUtils.close(connection, callableStatement);
        }
    }
 
Example 16
/**
 * 将TMX的header接点的主要属性写入到mheader表中
 * @throws SQLException
 */
public String insertHeader(Hashtable<String, String> params) throws SQLException {
	CallableStatement stmt = null;
	try {
		String sql = dbConfig.getOperateDbSQL("insert-mheader");
		stmt = conn.prepareCall(sql);
		int i = 1;
		stmt.setString(i++, params.get("CREATIONTOOL"));
		stmt.setString(i++, params.get("CTVERSION"));
		stmt.setString(i++, params.get("TMF"));
		stmt.setString(i++, params.get("SRCLANG"));
		stmt.setString(i++, params.get("ADMINLANG"));
		stmt.setString(i++, params.get("DATATYPE"));
		stmt.setString(i++, params.get("SEGTYPE"));
		stmt.setString(i++, params.get("CREATIONID"));
		stmt.setString(i++, params.get("CREATIONDATE"));
		stmt.setString(i++, params.get("CHANGEID"));
		stmt.setString(i++, params.get("CHANGEDATE"));
		stmt.setString(i++, params.get("ENCODING"));
		stmt.registerOutParameter(i++, Types.INTEGER);
		stmt.execute();
		return stmt.getString(i - 1);
	} finally {
		if (stmt != null) {
			stmt.close();
		}
	}
}
 
Example 17
Source Project: skywalking   File: SQLExecutor.java    License: Apache License 2.0 5 votes vote down vote up
public void insertData(String sql, String id, String value) throws SQLException {
    CallableStatement preparedStatement = connection.prepareCall(sql);
    preparedStatement.setString(1, id);
    preparedStatement.setString(2, value);
    preparedStatement.execute();
    preparedStatement.close();
}
 
Example 18
private List<String> generateKeyConstraintsCreateScripts( Connection connection, DbTable table ) {

        List<String> indexesNames = getAllIndexesNamesForTable(connection, table);
        List<String> indexesCreateScripts = new ArrayList<>();

        final String scriptFileName = "generateTableKeyConstraintScript.sql";

        String scriptContent = loadScriptFromClasspath(scriptFileName);

        // create the db procedure
        try {
            createDatabaseProcedure(connection, scriptContent);
            for (String indexName : indexesNames) {
                CallableStatement callableStatement = null;
                ResultSet rs = null;
                try {
                    callableStatement = connection.prepareCall("{ call generateTableKeyConstraintScript(?,?,?) }");
                    callableStatement.setString(1, table.getTableSchema());
                    callableStatement.setString(2, table.getTableName());
                    callableStatement.setString(3, indexName);
                    if (LOG.isTraceEnabled()) {
                        LOG.trace("Executing SQL query: " + callableStatement.toString());
                    }
                    rs = callableStatement.executeQuery();
                    String createQuery = new String();
                    if (rs.next()) {
                        createQuery = rs.getString(1);
                    }
                    if (!StringUtils.isNullOrEmpty(createQuery) && !"NULL".equalsIgnoreCase(createQuery)) {
                        indexesCreateScripts.add(createQuery);
                    }

                } finally {
                    DbUtils.closeStatement(callableStatement);
                }
            }
        } catch (SQLException e) {
            throw new DbException(DbUtils.getFullSqlException("Error while generating Key constraints for table '"
                                                              + table.getFullTableName() + "'", e),
                                  e);
        } finally {
            // drop the newly created procedure
            executeUpdate("DROP PROCEDURE generateTableKeyConstraintScript", connection);
        }
        return indexesCreateScripts;
    }
 
Example 19
public void testCallProcedureWithInAndOutParameter()
   throws Exception  {
  
  setup();
  int number=2;
  CallableStatement cs = prepareCall("CALL PROCEDURE_INOUT_PARAMETERS(?, ?, ?)");
  cs.setInt(1, number);
  cs.registerOutParameter(2, java.sql.Types.VARCHAR);
  cs.setString(2, "INOUT_PARAMETER");
  cs.registerOutParameter(3, java.sql.Types.INTEGER);
  cs.execute();
  
  String[][] results=new String[2][1];
  results[0][0]="1";
  results[1][0]="1";
           
  int rsIndex=-1;
  do {
    ++rsIndex;
    int rowIndex=0;
    ResultSet rs = cs.getResultSet();
    ResultSetMetaData metaData = rs.getMetaData();
    int rowCount = metaData.getColumnCount();
    while (rs.next()) {
      String row="";
      for (int i = 1; i <=rowCount; ++i) {
        Object value = rs.getObject(i);
        row+=value.toString();          
      }
      if(rsIndex>1 || rowIndex>1) {
        fail("the result is not correct!");
      }
      if(!row.equals(results[rsIndex][rowIndex])) {
        fail("the result is not correct!");
      }
      ++rowIndex;
    }
  } while (cs.getMoreResults());    
  
  String outValue=cs.getString(2);
  String outParameter="INOUT_PARAMETER"+"Modified";
  if(!outValue.equals(outParameter)) {
    fail("the out parameter is supposed to "+outParameter+" but "+outValue);
  }
  
  int parameter3=cs.getInt(3);    
  if(parameter3!=number) {
    fail("the out parameter is supposed to "+number+" but "+parameter3);
  }
           
}
 
Example 20
public void testBug43576() throws Exception {
    createTable("TMIX91P",
            "(F01SMALLINT         SMALLINT NOT NULL, F02INTEGER          INTEGER,F03REAL             REAL,"
                    + "F04FLOAT            FLOAT,F05NUMERIC31X4      NUMERIC(31,4), F06NUMERIC16X16     NUMERIC(16,16), F07CHAR_10          CHAR(10),"
                    + " F08VARCHAR_10       VARCHAR(10), F09CHAR_20          CHAR(20), F10VARCHAR_20       VARCHAR(20), F11DATE         DATE,"
                    + " F12DATETIME         DATETIME, PRIMARY KEY (F01SMALLINT))");

    this.stmt.executeUpdate("INSERT INTO TMIX91P VALUES (1,1,1234567.12,1234567.12,111111111111111111111111111.1111,.111111111111111,'1234567890',"
            + "'1234567890','CHAR20CHAR20','VARCHAR20ABCD','2001-01-01','2001-01-01 01:01:01.111')");

    this.stmt.executeUpdate("INSERT INTO TMIX91P VALUES (7,1,1234567.12,1234567.12,22222222222.0001,.99999999999,'1234567896','1234567896','CHAR20',"
            + "'VARCHAR20ABCD','2001-01-01','2001-01-01 01:01:01.111')");

    this.stmt.executeUpdate("INSERT INTO TMIX91P VALUES (12,12,1234567.12,1234567.12,111222333.4444,.1234567890,'2234567891','2234567891','CHAR20',"
            + "'VARCHAR20VARCHAR20','2001-01-01','2001-01-01 01:01:01.111')");

    createProcedure("MSQSPR100",
            "\n( p1_in  INTEGER , p2_in  CHAR(20), OUT p3_out INTEGER, OUT p4_out CHAR(11))\nBEGIN "
                    + "\n SELECT F01SMALLINT,F02INTEGER, F11DATE,F12DATETIME,F03REAL \n FROM TMIX91P WHERE F02INTEGER = p1_in; "
                    + "\n SELECT F02INTEGER,F07CHAR_10,F08VARCHAR_10,F09CHAR_20 \n FROM TMIX91P WHERE  F09CHAR_20 = p2_in ORDER BY F02INTEGER ; "
                    + "\n SET p3_out  = 144; \n SET p4_out  = 'CHARACTER11'; \n SELECT p3_out, p4_out; END");

    String sql = "{call MSQSPR100(1,'CHAR20',?,?)}";

    CallableStatement cs = this.conn.prepareCall(sql);

    cs.registerOutParameter(1, Types.INTEGER);
    cs.registerOutParameter(2, Types.CHAR);

    cs.execute();
    cs.close();

    createProcedure("bug43576_1", "(OUT nfact VARCHAR(100), IN ccuenta VARCHAR(100),\nOUT ffact VARCHAR(100),\nOUT fdoc VARCHAR(100))\nBEGIN"
            + "\nSET nfact = 'ncfact string';\nSET ffact = 'ffact string';\nSET fdoc = 'fdoc string';\nEND");

    createProcedure("bug43576_2", "(IN ccuent1 VARCHAR(100), IN ccuent2 VARCHAR(100),\nOUT nfact VARCHAR(100),\nOUT ffact VARCHAR(100),"
            + "\nOUT fdoc VARCHAR(100))\nBEGIN\nSET nfact = 'ncfact string';\nSET ffact = 'ffact string';\nSET fdoc = 'fdoc string';\nEND");

    Properties props = new Properties();
    props.put("jdbcCompliantTruncation", "true");
    props.put("useInformationSchema", "true");
    Connection conn1 = null;
    conn1 = getConnectionWithProps(props);
    try {
        CallableStatement callSt = conn1.prepareCall("{ call bug43576_1(?, ?, ?, ?) }");
        callSt.setString(2, "xxx");
        callSt.registerOutParameter(1, java.sql.Types.VARCHAR);
        callSt.registerOutParameter(3, java.sql.Types.VARCHAR);
        callSt.registerOutParameter(4, java.sql.Types.VARCHAR);
        callSt.execute();

        assertEquals("ncfact string", callSt.getString(1));
        assertEquals("ffact string", callSt.getString(3));
        assertEquals("fdoc string", callSt.getString(4));

        CallableStatement callSt2 = conn1.prepareCall("{ call bug43576_2(?, ?, ?, ?, ?) }");
        callSt2.setString(1, "xxx");
        callSt2.setString(2, "yyy");
        callSt2.registerOutParameter(3, java.sql.Types.VARCHAR);
        callSt2.registerOutParameter(4, java.sql.Types.VARCHAR);
        callSt2.registerOutParameter(5, java.sql.Types.VARCHAR);
        callSt2.execute();

        assertEquals("ncfact string", callSt2.getString(3));
        assertEquals("ffact string", callSt2.getString(4));
        assertEquals("fdoc string", callSt2.getString(5));

        CallableStatement callSt3 = conn1.prepareCall("{ call bug43576_2(?, 'yyy', ?, ?, ?) }");
        callSt3.setString(1, "xxx");
        // callSt3.setString(2, "yyy");
        callSt3.registerOutParameter(2, java.sql.Types.VARCHAR);
        callSt3.registerOutParameter(3, java.sql.Types.VARCHAR);
        callSt3.registerOutParameter(4, java.sql.Types.VARCHAR);
        callSt3.execute();

        assertEquals("ncfact string", callSt3.getString(2));
        assertEquals("ffact string", callSt3.getString(3));
        assertEquals("fdoc string", callSt3.getString(4));
    } finally {
        conn1.close();
    }
}