Examples of CallableStatement

java.sql.CallableStatement
The interface used to execute SQL stored procedures. The JDBC API provides a stored procedure SQL escape syntax that allows stored procedures to be called in a standard way for all RDBMSs. This escape syntax has one form that includes a result parameter and one that does not. If used, the result parameter must be registered as an OUT parameter. The other parameters can be used for input, output or both. Parameters are referred to sequentially, by number, with the first parameter being 1.
```
 {?= call <procedure-name>[(<arg1>,<arg2>, ...)]} {call <procedure-name>[(<arg1>,<arg2>, ...)]} 
```
IN parameter values are set using the set methods inherited from {@link PreparedStatement}. The type of all OUT parameters must be registered prior to executing the stored procedure; their values are retrieved after execution via the get methods provided here.
A CallableStatement can return one {@link ResultSet} object ormultiple ResultSet objects. Multiple ResultSet objects are handled using operations inherited from {@link Statement}.
For maximum portability, a call's ResultSet objects and update counts should be processed prior to getting the values of output parameters. @see Connection#prepareCall @see ResultSet
org.apache.derby.client.am.CallableStatement

Examples of java.sql.CallableStatement

         conn.setAutoCommit(true);
         String sql = null;
         {
          // col2xml_cdata(?, ?)
            sql = "{? = call " + this.replPrefix + "test_clob(?,?,?,?)}"; // name text, content text)
            CallableStatement st = conn.prepareCall(sql);
            st.setString(2, "COL2XML_CDATA");
            st.setString(3, "prova");
            st.setString(4, "test");
            st.setLong(5, 1L); // loop only once
            st.registerOutParameter(1, Types.CLOB);
            ResultSet rs = st.executeQuery();
            Clob clob = st.getClob(1);
            long len = clob.length();
            byte[] buf = new byte[(int)len];
            clob.getAsciiStream().read(buf);
            rs.close();
            st.close();
            log.fine("The return value of the query '" + sql + "' is '" + new String(buf) + "'");
            assertEquals(sql, "<col name=\"test\"><![CDATA[prova]]></col>", new String(buf));
         }
         {
            // col2xml_base64(?, ?)
            sql = "{? = call " + this.replPrefix + "test_blob(?,?,?,?)}"; // name text, content text)
            CallableStatement st = conn.prepareCall(sql);
            st.setString(2, "COL2XML_BASE64");
            st.setBytes(3, "prova".getBytes());
            st.setString(4, "test");
            st.setLong(5, 1L); // loop only once
            st.registerOutParameter(1, Types.CLOB);
            ResultSet rs = st.executeQuery();
            Clob clob = st.getClob(1);
            long len = clob.length();
            byte[] buf = new byte[(int)len];
            clob.getAsciiStream().read(buf);
            rs.close();
            st.close();
            log.fine("The return value of the query '" + sql + "' is '" + new String(buf) + "'");
            assertEquals(sql, "<col name=\"test\" encoding=\"base64\">cHJvdmE=</col>", new String(buf));
            String tmp = extractBase64Part(new String(buf));
            byte[] ret = org.xmlBlaster.util.Base64.decode(tmp);
            String returnName = new String(ret);
            assertEquals("The returned (decoded) String is checked", "prova", returnName);
            
         }
         {  
            // col2xml_base64(?, ?) CONTENTS BIGGER THAN 4 kB 
            sql = "{? = call " + this.replPrefix + "test_blob(?,?,?,?)}"; // name text, content text)
            CallableStatement st = conn.prepareCall(sql);
            st.setString(2, "COL2XML_BASE64");
            int nmax = 256;
            byte[] blob = new byte[nmax];
            for (int i=0; i < nmax; i++)
               blob[i] = (byte)i;
            st.setBytes(3, blob);
            st.setString(4, "test");
            st.setLong(5, (long)nmax); // loop multiple times to simulate big contents
            st.registerOutParameter(1, Types.CLOB);
            ResultSet rs = st.executeQuery();
            Clob clob = st.getClob(1);
            long len = clob.length();
            byte[] buf = new byte[(int)len];
            clob.getAsciiStream().read(buf);
            rs.close();
            st.close();
            String tmp = extractBase64Part(new String(buf));
            byte[] ret = org.xmlBlaster.util.Base64.decode(tmp);
            assertEquals("Comparison of lenght of input blob with output blob", nmax * nmax, ret.length);
            log.fine("success");
         }
         // now testing the " + this.replPrefix + "needs_prot for the three cases ...
         { // needs no protection needs_prot(?)
            sql = "{? = call " + this.replPrefix + "test_clob(?,?,?,?)}"; // name text, content text)
            CallableStatement st = conn.prepareCall(sql);
            st.setString(2, "NEEDS_PROT");
            st.setString(3, "prova");
            st.setString(4, "unused");
            st.setLong(5, 1L);
            // st.registerOutParameter(1, Types.INTEGER);
            st.registerOutParameter(1, Types.CLOB);
            ResultSet rs = st.executeQuery();
            // int ret = st.getInt(1);
            Clob clob = st.getClob(1);
            long len = clob.length();
            byte[] buf = new byte[(int)len];
            clob.getAsciiStream().read(buf);
            String txt = new String(buf);
            log.fine("The return value of the query '" + sql + "' is '" + txt + "'");
            int ret = -1000;
            try {
               ret = Integer.parseInt(txt);
            }
            catch (Throwable e) {
               assertTrue("Conversion exception should not occur on '" + sql + "' where ret is '" + txt + "'", false);
            }
            rs.close();
            st.close();
            assertEquals(sql, 0, ret);
         }
         { // needs BASE64 needs_prot(?)
            sql = "{? = call " + this.replPrefix + "test_clob(?,?,?,?)}"; // name text, content text)
            CallableStatement st = conn.prepareCall(sql);
            st.setString(2, "NEEDS_PROT");
            st.setString(3, "<![[CDATAsome text]]>");
            st.setString(4, "unused");
            st.setLong(5, 1L);
            // st.registerOutParameter(1, Types.INTEGER);
            st.registerOutParameter(1, Types.CLOB);
            ResultSet rs = st.executeQuery();
            // int ret = st.getInt(1);
            Clob clob = st.getClob(1);
            long len = clob.length();
            byte[] buf = new byte[(int)len];
            clob.getAsciiStream().read(buf);
            String txt = new String(buf);
            log.fine("The return value of the query '" + sql + "' is '" + txt + "'");
            int ret = -1000;
            try {
               ret = Integer.parseInt(txt);
            }
            catch (Throwable e) {
               assertTrue("Conversion exception should not occur on '" + sql + "' where ret is '" + txt + "'", false);
            }
            rs.close();
            st.close();
            assertEquals(sql, 2, ret);
         }
         { // needs CDATA needs_prot(?)
            sql = "{? = call " + this.replPrefix + "test_clob(?,?,?,?)}"; // name text, content text)
            CallableStatement st = conn.prepareCall(sql);
            st.setString(2, "NEEDS_PROT");
            st.setString(3, "this is a &lt;a");
            st.setString(4, "unused");
            st.setLong(5, 1L);
            // st.registerOutParameter(1, Types.INTEGER);
            st.registerOutParameter(1, Types.CLOB);
            ResultSet rs = st.executeQuery();
            // int ret = st.getInt(1);
            Clob clob = st.getClob(1);
            long len = clob.length();
            byte[] buf = new byte[(int)len];
            clob.getAsciiStream().read(buf);
            String txt = new String(buf);
            log.fine("The return value of the query '" + sql + "' is '" + txt + "'");
            int ret = -1000;
            try {
               ret = Integer.parseInt(txt);
            }
            catch (Throwable e) {
               assertTrue("Conversion exception should not occur on '" + sql + "' where ret is '" + txt + "'", false);
            }
            rs.close();
            st.close();
            assertEquals(sql, 1, ret);
         }
         { // needs CDATA needs_prot(?)
            sql = "{? = call " + this.replPrefix + "test_clob(?,?,?,?)}"; // name text, content text)
            CallableStatement st = conn.prepareCall(sql);
            st.setString(2, "NEEDS_PROT");
            st.setString(3, "&lt;this is a");
            st.setString(4, "unused");
            st.setLong(5, 1L);
            // st.registerOutParameter(1, Types.INTEGER);
            st.registerOutParameter(1, Types.CLOB);
            ResultSet rs = st.executeQuery();
            // int ret = st.getInt(1);
            Clob clob = st.getClob(1);
            long len = clob.length();
            byte[] buf = new byte[(int)len];
            clob.getAsciiStream().read(buf);
            String txt = new String(buf);
            log.fine("The return value of the query '" + sql + "' is '" + txt + "'");
            int ret = -1000;
            try {
               ret = Integer.parseInt(txt);
            }
            catch (Throwable e) {
               assertTrue("Conversion exception should not occur on '" + sql + "' where ret is '" + txt + "'", false);
            }
            rs.close();
            st.close();
            assertEquals(sql, 1, ret);
         }
         { // needs CDATA needs_prot(?)
            sql = "{? = call " + this.replPrefix + "test_clob(?,?,?,?)}"; // name text, content text)
            CallableStatement st = conn.prepareCall(sql);
            st.setString(2, "NEEDS_PROT");
            st.setString(3, "a&lt;this is a");
            st.setString(4, "unused");
            st.setLong(5, 1L);
            // st.registerOutParameter(1, Types.INTEGER);
            st.registerOutParameter(1, Types.CLOB);
            ResultSet rs = st.executeQuery();
            // int ret = st.getInt(1);
            Clob clob = st.getClob(1);
            long len = clob.length();
            byte[] buf = new byte[(int)len];
            clob.getAsciiStream().read(buf);
            String txt = new String(buf);
            log.fine("The return value of the query '" + sql + "' is '" + txt + "'");
            int ret = -1000;
            try {
               ret = Integer.parseInt(txt);
            }
            catch (Throwable e) {
               assertTrue("Conversion exception should not occur on '" + sql + "' where ret is '" + txt + "'", false);
            }
            rs.close();
            st.close();
            assertEquals(sql, 1, ret);
         }
         
         // now testing the " + this.replPrefix + "needs_prot for the three cases ...
         { // needs no protection
            // col2xml(?, ?)
            sql = "{? = call " + this.replPrefix + "test_clob(?,?,?,?)}"; // name text, content text)
            CallableStatement st = conn.prepareCall(sql);
            st.setString(2, "COL2XML");
            st.setString(3, "colValue");
            st.setString(4, "colName");
            st.setLong(5, 1L);
            st.registerOutParameter(1, Types.CLOB);
            ResultSet rs = st.executeQuery();
            Clob clob = st.getClob(1);
            long len = clob.length();
            byte[] buf = new byte[(int)len];
            clob.getAsciiStream().read(buf);
            rs.close();
            st.close();
            log.fine("The return value of the query '" + sql + "' is '" + new String(buf) + "'");
            assertEquals(sql, "<col name=\"colName\">colValue</col>", new String(buf));
         }
         {  // col2xml(?, ?)
            sql = "{? = call " + this.replPrefix + "test_clob(?,?,?,?)}"; // name text, content text)
            CallableStatement st = conn.prepareCall(sql);
            st.setString(2, "COL2XML");
            st.setString(3, "prova");
            st.setString(4, "test");
            st.setLong(5, 1L);
            st.registerOutParameter(1, Types.CLOB);
            ResultSet rs = st.executeQuery();
            Clob clob = st.getClob(1);
            long len = clob.length();
            byte[] buf = new byte[(int)len];
            clob.getAsciiStream().read(buf);
            rs.close();
            st.close();
            log.fine("The return value of the query '" + sql + "' is '" + new String(buf) + "'");
            assertEquals(sql, "<col name=\"test\">prova</col>", new String(buf));
         }
         { // needs BASE64 col2xml(?, ?)
            sql = "{? = call " + this.replPrefix + "test_clob(?,?,?,?)}"; // name text, content text)
            CallableStatement st = conn.prepareCall(sql);
            st.setString(2, "COL2XML");
            st.setString(3, "<![CDATA[colValue]]>");
            st.setString(4, "colName");
            st.setLong(5, 1L);
            st.registerOutParameter(1, Types.CLOB);
            ResultSet rs = st.executeQuery();
            Clob clob = st.getClob(1);
            long len = clob.length();
            byte[] buf = new byte[(int)len];
            clob.getAsciiStream().read(buf);
            rs.close();
            st.close();
            log.fine("The return value of the query '" + sql + "' is '" + new String(buf) + "'");
            assertEquals(sql, "<col name=\"colName\" encoding=\"base64\">PCFbQ0RBVEFbY29sVmFsdWVdXT4=</col>", new String(buf));
         }
         { // needs CDATA col2xml(?, ?)
            sql = "{? = call " + this.replPrefix + "test_clob(?,?,?,?)}"; // name text, content text)
            CallableStatement st = conn.prepareCall(sql);
            st.setString(2, "COL2XML");
            st.setString(3, "c&lt;olValue");
            st.setString(4, "colName");
            st.setLong(5, 1L);
            st.registerOutParameter(1, Types.CLOB);
            ResultSet rs = st.executeQuery();
            Clob clob = st.getClob(1);
            long len = clob.length();
            byte[] buf = new byte[(int)len];
            clob.getAsciiStream().read(buf);
            rs.close();
            st.close();
            log.fine("The return value of the query '" + sql + "' is '" + new String(buf) + "'");
            assertEquals(sql, "<col name=\"colName\"><![CDATA[c&lt;olValue]]></col>", new String(buf));
         }
         // now test the counter ... (this invocation is used in SpecificDefault.incrementReplKey
         {
            long oldVal = 0;
            for (int i=0; i < 2; i++) {
               // sql = "{? = call nextval('repl_seq')}";
               sql = "{? = call " + this.replPrefix + "increment()}";
               CallableStatement st = conn.prepareCall(sql);
               // st.registerOutParameter(1, Types.BIGINT);
               st.registerOutParameter(1, Types.INTEGER);
               ResultSet rs = st.executeQuery();
               // long ret = st.getLong(1);
               long ret = st.getLong(1);
               rs.close();
               st.close();
               log.fine("The return value of the query '" + sql + "' is '" + ret + "'. The maximum integer value is '" + Integer.MAX_VALUE + "'");
               if (i == 0)
                  oldVal = ret;
               else
                  assertEquals(sql, oldVal + i , ret);
            }
         }
         
         {  // tests that by broadcast statements they are written in the ITEMS table
            // make sure there is nothing in the ITEMS table
            sql = "DELETE FROM " + this.dbHelper.getIdentifier(this.replPrefix + "ITEMS");
            this.pool.update(sql);
            
            sql = "SELECT * FROM " + this.dbHelper.getIdentifier(this.replPrefix + "ITEMS");
            
            long maxResponseEntries = 10L;
            boolean isHighPrio = true;
            boolean isMaster = false;
            String sqlTopic = null;
            String statementId = "1";
            this.dbSpecific.broadcastStatement(sql, maxResponseEntries, isHighPrio, isMaster, sqlTopic, statementId);
            Statement st = conn.createStatement();
            try {
               ResultSet rs = st.executeQuery("SELECT content FROM " + this.dbHelper.getIdentifier(this.replPrefix + "ITEMS"));
               if (rs.next()) {
                  Clob clob = rs.getClob(1);
                  long len = clob.length();
                  byte[] buf = new byte[(int)len];
                  clob.getAsciiStream().read(buf);
                  String txt = new String(buf);
                  log.info("The statement to broadcast is '" + txt + "'");
                  assertFalse("There must not be any results after a SELECT statement to broadcast", rs.next());
               }
               else
                  assertTrue("There must be entries in the ITEMS table. Seems that the broadcastStatement function does not work", false);
            }
            finally {
               st.close();
            }
         }
      } 
      catch (Exception ex) {
         ex.printStackTrace();

View Full Code Here

Examples of java.sql.CallableStatement

         conn.setAutoCommit(true);
         String sql = null;
         { // test the test methods themselves first
             sql = "{? = call " + this.replPrefix + "test_blob(?,?,?,?)}";
             try {
                CallableStatement st = conn.prepareCall(sql);
                st.setString(2, "TEST");
                String tmp = new String("Haloooooo");
                st.setBytes(3, tmp.getBytes());
                st.setString(4, "name");
                st.setLong(5, 1L);
                st.registerOutParameter(1, Types.CLOB);
                ResultSet rs = st.executeQuery();
                Clob clob = st.getClob(1);
                long len = clob.length();
                byte[] buf = new byte[(int)len];
                clob.getAsciiStream().read(buf);
                rs.close();
                st.close();
                log.fine("The return value of the query '" + sql + "' is '" + new String(buf) + "'");
                assertEquals("", "TEST", new String(buf));
             }
             catch (SQLException sqlEx) {
                sqlEx.printStackTrace();
                assertTrue("an exception should not occur when testing '" + sql + "'", false);
             }
          }
         { // test the test methods themselves first
             sql = "{? = call " + this.replPrefix + "test_clob(?,?,?,?)}";
             try {
                CallableStatement st = conn.prepareCall(sql);
                st.setString(2, "TEST");
                String tmp = new String("Haloooooo");
                st.setString(3, tmp);
                st.setString(4, "name");
                st.setLong(5, 1L);
                st.registerOutParameter(1, Types.CLOB);
                ResultSet rs = st.executeQuery();
                Clob clob = st.getClob(1);
                long len = clob.length();
                byte[] buf = new byte[(int)len];
                clob.getAsciiStream().read(buf);
                rs.close();
                st.close();
                log.fine("The return value of the query '" + sql + "' is '" + new String(buf) + "'");
                assertEquals("", "TEST", new String(buf));
             }
             catch (SQLException sqlEx) {
                sqlEx.printStackTrace();
                assertTrue("an exception should not occur when testing '" + sql + "'", false);
             }
         }
         {
            sql = "{? = call " + this.replPrefix + "base64_helper(?, ?)}";
            try {
               CallableStatement st = conn.prepareCall(sql);
               st.setInt(2, 2);
               st.setLong(3, 1000L);
               st.registerOutParameter(1, Types.VARCHAR);
               ResultSet rs = st.executeQuery();
               String ret = st.getString(1);
               rs.close();
               st.close();
               log.fine("The return value of the query '" + sql + "' is '" + ret + "'");
               // no assert here, just testing for exceptions 
            }
            catch (SQLException sqlEx) {
               sqlEx.printStackTrace();
               assertTrue("an exception should not occur when testing '" + sql + "'", false);
            }
         }
         {
            sql = "{? = call " + this.replPrefix + "base64_enc_raw_t(?)}"; // name text, content text)
            try {
               CallableStatement st = conn.prepareCall(sql);
               
               // int nmax = 256;
               int nmax = 2000;
               byte[] in = new byte[nmax];
               for (int i=0; i < nmax; i++) {
                  in[i] = (byte)i;
               }
               st.setBytes(2, in);
               // st.registerOutParameter(1, Types.VARCHAR); // worked for oracle 10
               st.registerOutParameter(1, Types.CLOB);
               ResultSet rs = st.executeQuery();
               // String ret = st.getString(1);
               Clob clob = st.getClob(1);
               long len = clob.length();
               byte[] buf = new byte[(int)len];
               clob.getAsciiStream().read(buf);
               rs.close();
               st.close();
               byte[] out = Base64.decode(buf);
               log.fine("The return value of the query '" + sql + "' is '" + new String(buf) + "'");
               assertEquals("wrong number of return values ", in.length, out.length);
               for (int i=0; i < in.length; i++) {
                  assertEquals("entry '" + i + "' is wrong: ", in[i], out[i]);
               }
            }
            catch (SQLException sqlEx) {
               sqlEx.printStackTrace();
               assertTrue("an exception should not occur when testing '" + sql + "'", false);
            }
         }
         {
            sql = "{? = call " + this.replPrefix + "base64_enc_vch_t(?)}"; // name text, content text)
            try {
               CallableStatement st = conn.prepareCall(sql);


               String test = "this is a simple base64 encoding test for clobs";
               st.setString(2, test);
               st.registerOutParameter(1, Types.CLOB);
               ResultSet rs = st.executeQuery();
               // String ret = st.getString(1);
               Clob clob = st.getClob(1);
               long len = clob.length();
               byte[] buf = new byte[(int)len];
               clob.getAsciiStream().read(buf);
               rs.close();
               st.close();
               log.fine("The return value of the query '" + sql + "' is '" + new String(buf) + "'");
               String out = new String(Base64.decode(buf));
               assertEquals("invocation '" + sql + "' gave the wrong result ", test, out);
            }
            catch (SQLException sqlEx) {
               sqlEx.printStackTrace();
               assertTrue("an exception should not occur when testing '" + sql + "'", false);
            }
         }
         
         {
           // base64_enc_blob(?)
             sql = "{? = call " + this.replPrefix + "test_blob(?,?,?,?)}"; // name text, content text)
             try {
                CallableStatement st = conn.prepareCall(sql);
                int nmax = 2000;
                byte[] in = new byte[nmax];
                for (int i=0; i < nmax; i++) {
                   in[i] = (byte)i;
                }
                st.setString(2, "BASE64_ENC_BLOB");
                st.setBytes(3, in);
                st.setString(4, "unused");
                st.setLong(5, 1L); // loop only once
                st.registerOutParameter(1, Types.CLOB);
                ResultSet rs = st.executeQuery();
                Clob clob = st.getClob(1);
                long len = clob.length();
                byte[] buf = new byte[(int)len];
                clob.getAsciiStream().read(buf);
                rs.close();
                st.close();
                log.fine("The return value of the query '" + sql + "' is '" + new String(buf) + "'");
                
                byte[] out = Base64.decode(buf);
                assertEquals("wrong number of return values ", in.length, out.length);
                for (int i=0; i < in.length; i++) {
                   assertEquals("entry '" + i + "' is wrong: ", in[i], out[i]);
                }
             }
             catch (SQLException sqlEx) {
                sqlEx.printStackTrace();
                assertTrue("an exception should not occur when testing '" + sql + "'", false);
             }
          }
         
/*
         {
            sql = "{? = call " + this.replPrefix + "base64_enc_blob(?)}"; // name text, content text)
            try {
               // conn.setAutoCommit(false); // needed since reusing lob objects
               // first retreiving the LOB object
               // String tmpSql = "{? = call " + this.replPrefix + "create_blob()}"; // name text, content text)
               // CallableStatement tmpSt = conn.prepareCall(tmpSql);
               // tmpSt.registerOutParameter(1, Types.BLOB);
               // ResultSet tmpRs = tmpSt.executeQuery();
               // Blob blob = tmpSt.getBlob(1);
               CallableStatement st = conn.prepareCall(sql);
               int nmax = 32000;
               byte[] in = new byte[nmax];
               for (int i=0; i < nmax; i++) {
                  in[i] = (byte)i;
               }
               // ByteArray works for ora10
               // ByteArrayInputStream bais = new ByteArrayInputStream(in);
               // st.setBinaryStream(2, bais, in.length);
               BLOB blob = BLOB.createTemporary(conn, true, BLOB.MODE_READWRITE);
               blob.open(BLOB.MODE_READWRITE);
               // The following did not work for 8.1.6. To make it 
               // work it needed the old driver and the next line code
               // which in the new driver is deprecated.
               // OutputStream os = blob.setBinaryStream(1);
               
               // this raises an AbstractMethodError with both old and new driver
               // OutputStream os = ((java.sql.Blob)blob).setBinaryStream(1L);
               
               // This works with old driver on 8.1.6 (but oracle specific)
               // OutputStream os = blob.getBinaryOutputStream();
               // os.write(in);
               // os.close();


               // this raises an AbstractMethodError too in oracle with old and new driver
               // ((java.sql.Blob)blob).setBytes(1, in);
               ((java.sql.Blob)blob).setBytes(1, in, 0, in.length);
               st.setBlob(2, blob);
               
               st.registerOutParameter(1, Types.CLOB);
               ResultSet rs = st.executeQuery();
               // String ret = st.getString(1);
               Clob clob = st.getClob(1);
               long len = clob.length();
               byte[] buf = new byte[(int)len];
               clob.getAsciiStream().read(buf);
               // tmpRs.close();
               // tmpSt.close();
               rs.close();
               st.close();
               conn.setAutoCommit(true);
               log.fine("The return value of the query '" + sql + "' is '" + new String(buf) + "'");
               
               byte[] out = Base64.decodeBase64(buf);
               assertEquals("wrong number of return values ", in.length, out.length);
               for (int i=0; i < in.length; i++) {
                  assertEquals("entry '" + i + "' is wrong: ", in[i], out[i]);
               }
            }
            catch (SQLException sqlEx) {
               sqlEx.printStackTrace();
               assertTrue("an exception should not occur when testing '" + sql + "'", false);
            }
         }
 */     
         {
            // base64_enc_clob(?)
            sql = "{? = call " + this.replPrefix + "test_clob(?,?,?,?)}"; // name text, content text)
            try {
               CallableStatement st = conn.prepareCall(sql);


               String test = "this is a simple base64 encoding test for clobs";
               st.setString(2, "BASE64_ENC_CLOB");
               st.setString(3, test);
               st.setString(4, "unused");
               st.setLong(5, 1L); // loop only once
               st.registerOutParameter(1, Types.CLOB);
               ResultSet rs = st.executeQuery();
               Clob clob = st.getClob(1);
               long len = clob.length();
               byte[] buf = new byte[(int)len];
               clob.getAsciiStream().read(buf);
               rs.close();
               st.close();
               log.fine("The return value of the query '" + sql + "' is '" + new String(buf) + "'");
               String out = new String(Base64.decode(buf));
               assertEquals("invocation '" + sql + "' gave the wrong result ", test, out);
            }
            catch (SQLException sqlEx) {
               sqlEx.printStackTrace();
               assertTrue("an exception should not occur when testing '" + sql + "'", false);
            }
         }
         {
            // fill_blob_char(?)
            sql = "{? = call " + this.replPrefix + "test_clob(?,?,?,?)}"; // name text, content text)
            try {
               CallableStatement st = conn.prepareCall(sql);
               String result = "<col name=\"fill\">this is a simple test string for the fill_blob_char</col>";
               String test = "this is a simple test string for the fill_blob_char";
               st.setString(2, "FILL_BLOB_CHAR2");
               st.setString(3, test);
               st.setString(4, "fill");
               st.setLong(5, 1L); // loop only once
               st.registerOutParameter(1, Types.CLOB);
               ResultSet rs = st.executeQuery();
               Clob clob = st.getClob(1);
               long len = clob.length();
               byte[] buf = new byte[(int)len];
               clob.getAsciiStream().read(buf);
               rs.close();
               st.close();
               log.fine("The return value of the query '" + sql + "' is '" + new String(buf) + "'");
               String out = new String(buf);
               assertEquals("invocation '" + sql + "' gave the wrong result ", result+result, out);
            }
            catch (SQLException sqlEx) {
               sqlEx.printStackTrace();
               assertTrue("an exception should not occur ocalhostwhen testing '" + sql + "'", false);
            }
         }


         {
            sql = "{? = call " + this.replPrefix + "check_tables(?,?,?,?,NULL)}"; // name text, content text)
            try {
               CallableStatement st = conn.prepareCall(sql);
               st.setString(2, "dbName");    // database name
               st.setString(3, "schema");
               st.setString(4, "table");
               st.setString(5, "COMMAND");
               st.registerOutParameter(1, Types.VARCHAR);
               ResultSet rs = st.executeQuery();
               String ret = st.getString(1);
               log.fine("The return value of the query '" + sql + "' is '" + ret + "'");
               rs.close();
               st.close();
            }
            catch (SQLException sqlEx) {
               sqlEx.printStackTrace();
               assertTrue("an exception should not occur when testing '" + sql + "'", false);
            }
         }


         {
            sql = "{? = call " + this.replPrefix + "check_tables(?,?,?,?,?)}"; // name text, content text)
            try {
               CallableStatement st = conn.prepareCall(sql);
               st.setString(2, "dbName");    // database name
               st.setString(3, "schema");
               st.setString(4, "table");
               st.setString(5, "COMMAND");
               st.setString(6, "TEST CONTENT");
               st.registerOutParameter(1, Types.VARCHAR);
               ResultSet rs = st.executeQuery();
               String ret = st.getString(1);
               log.fine("The return value of the query '" + sql + "' is '" + ret + "'");
               rs.close();
               st.close();
            }
            catch (SQLException sqlEx) {
               sqlEx.printStackTrace();
               assertTrue("an exception should not occur when testing '" + sql + "'", false);
            }
         }


         // TEST HERE THE repl_add_table function behaviour
         {
            sql = "{? = call " + this.replPrefix + "add_table(?,?,?,?)}"; // name text, content text)
            try {
               try {
                  this.pool.update("DELETE FROM " + "TEST_REPLICATION");
               }
               catch (Exception e) {
               }


               CallableStatement st = conn.prepareCall(sql);
               st.setString(2, null);
               st.setString(3, this.specificHelper.getOwnSchema(this.pool));
               st.setString(4, "TEST_REPLICATION");
               st.setString(5, "CREATE");
               st.registerOutParameter(1, Types.VARCHAR);
               ResultSet rs = st.executeQuery();
               String ret = st.getString(1);
               log.fine("The return value of the query '" + sql + "' is '" + ret + "'");
               rs.close();
               st.close();
               // assertEquals("Checking the invocation of '" + this.replPrefix + "add_table': and addition which must result in no operation", "FALSE", ret);
            }
            catch (SQLException sqlEx) {
               sqlEx.printStackTrace();
               assertTrue("an exception should not occur when testing '" + sql + "'", false);
            }
         }
         // TEST HERE THE repl_add_table function behaviour
         {
            sql = "{? = call " + this.replPrefix + "add_table(?,?,?,?)}"; // name text, content text)
            try {
               boolean force = false;
               boolean forceSend = false;
               TableToWatchInfo tableToWatch = new TableToWatchInfo(null, this.specificHelper.getOwnSchema(this.pool), "TEST_REPLICATION");
               tableToWatch.setActions("");
               tableToWatch.setTrigger(null);
               this.dbSpecific.addTableToWatch(tableToWatch, force, null, forceSend);
               CallableStatement st = conn.prepareCall(sql);
               st.setString(2, null);
               st.setString(3, this.specificHelper.getOwnSchema(this.pool));
               st.setString(4, "TEST_REPLICATION");
               st.setString(5, "CREATE");
               st.registerOutParameter(1, Types.VARCHAR);
               ResultSet rs = st.executeQuery();
               String ret = st.getString(1);
               log.fine("The return value of the query '" + sql + "' is '" + ret + "'");
               rs.close();
               st.close();
               assertEquals("Checking the invocation of '" + this.replPrefix + "add_table': and addition which must result in no operation", "TRUE", ret);
               // and the entry should be in the repl_items table
            }
            catch (SQLException sqlEx) {
               sqlEx.printStackTrace();

View Full Code Here

Examples of java.sql.CallableStatement

      Statement oStmt = oConn.createStatement();
      oStmt.executeQuery("SELECT k_sp_del_adhoc_mailing ('" + getString(DB.gu_mailing) + "')");
      oStmt.close();
      bRetVal = true;
    } else {
      CallableStatement oCall = oConn.prepareCall("{ call k_sp_del_adhoc_mailing ('" + getString(DB.gu_mailing) + "') }");
      bRetVal = oCall.execute();
      oCall.close();
    }
    return bRetVal;    
  }

View Full Code Here

Examples of java.sql.CallableStatement

   * if no instance was found with such name.
   * @throws SQLException
   */


  protected static String getUIdFromName(JDCConnection oConn, Integer iDomainId, String sInstanceNm, String sStoredProc) throws SQLException {
    CallableStatement oCall;
    PreparedStatement oStmt;
    ResultSet oRSet;


    String sInstanceId;


    if (null==iDomainId) {
      if (JDCConnection.DBMS_POSTGRESQL==oConn.getDataBaseProduct()) {
        if (DebugFile.trace)
          DebugFile.writeln("Connection.prepareStatement(SELECT " + sStoredProc + "('" + sInstanceNm + "')");


        oStmt = oConn.prepareStatement("SELECT " + sStoredProc + "(?)", ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
        oStmt.setString(1, sInstanceNm);
        oRSet = oStmt.executeQuery();
        if (oRSet.next())
          sInstanceId = oRSet.getString(1);
        else
          sInstanceId = null;
        oRSet.close();
        oStmt.close();
      }
      else {
        if (DebugFile.trace)
          DebugFile.writeln("Connection.prepareCall({ call " + sStoredProc + " ('" + sInstanceNm + "',?)})");


        oCall = oConn.prepareCall("{ call " + sStoredProc + " (?,?)}");


        oCall.setString(1, sInstanceNm);
        oCall.registerOutParameter(2, java.sql.Types.CHAR);


        oCall.execute();


        sInstanceId = oCall.getString(2);


        if (JDCConnection.DBMS_ORACLE==oConn.getDataBaseProduct() && null!=sInstanceId)
          sInstanceId = sInstanceId.trim();


        oCall.close();
        oCall = null;
      }
    }
    else {
      if (JDCConnection.DBMS_POSTGRESQL==oConn.getDataBaseProduct()) {
        if (DebugFile.trace)
          DebugFile.writeln("Connection.prepareStatement(SELECT " + sStoredProc + "(" + iDomainId.toString() + ",'" + sInstanceNm + "')");


        oStmt = oConn.prepareStatement("SELECT " + sStoredProc + "(?,?)", ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
        oStmt.setInt(1, iDomainId.intValue());
        oStmt.setString(2, sInstanceNm);
        oRSet = oStmt.executeQuery();
        if (oRSet.next())
          sInstanceId = oRSet.getString(1);
        else
          sInstanceId = null;
        oRSet.close();
        oStmt.close();
      }
      else {
        if (DebugFile.trace)
          DebugFile.writeln("Connection.prepareCall({ call " + sStoredProc + " (" + iDomainId.toString() + ",'" + sInstanceNm + "',?)})");


        oCall = oConn.prepareCall("{ call " + sStoredProc + " (?,?,?) }");
        try {
          oCall.setQueryTimeout(15);
        }
        catch (SQLException sqle) {}


        oCall.setInt(1, iDomainId.intValue());
        oCall.setString(2, sInstanceNm);
        oCall.registerOutParameter(3, java.sql.Types.CHAR);


        oCall.execute();


        sInstanceId = oCall.getString(3);


        if (null!=sInstanceId) sInstanceId = sInstanceId.trim();


        oCall.close();
        oCall = null;
      }
    }


    return sInstanceId;

View Full Code Here

Examples of java.sql.CallableStatement

      oStmt.close();
    } else {
      if (DebugFile.trace) {
        DebugFile.writeln("Connection.prepareCall({ call k_sp_get_date_fare('"+getStringNull(DB.gu_product,null)+"','"+dtWhen.toString()+"',?) }");
      }
      CallableStatement oCall = oConn.prepareCall("{ call k_sp_get_date_fare(?,?,?) }");
      oCall.setString(1, getStringNull(DB.gu_product,null));
      oCall.setDate(2, dtWhen);
      oCall.registerOutParameter(3, java.sql.Types.DECIMAL);
      oCall.execute();
      oFare = oCall.getBigDecimal(3);
      oCall.close();
    }


    if (DebugFile.trace) {
      DebugFile.decIdent();
      if (null==oFare)

View Full Code Here

Examples of java.sql.CallableStatement

      oStmt.close();
    } else {
      if (DebugFile.trace) {
        DebugFile.writeln("Connection.prepareCall({ call k_sp_get_prod_fare('"+getStringNull(DB.gu_product,null)+"','"+sIdFare+"',?) }");
      }
      CallableStatement oCall = oConn.prepareCall("{ call k_sp_get_prod_fare(?,?,?) }");
      oCall.setString(1, getStringNull(DB.gu_product,null));
      oCall.setString(2, sIdFare);
      oCall.registerOutParameter(3, java.sql.Types.DECIMAL);
      oCall.execute();
      oFare = oCall.getBigDecimal(3);
      oCall.close();
    }


    if (DebugFile.trace) {
      DebugFile.decIdent();
      if (null==oFare)

View Full Code Here

Examples of java.sql.CallableStatement

   * Then k_sp_del_product storedprocedure is called.
   * @param oConn Database Connection
   * @throws SQLException
   */
  public boolean delete(JDCConnection oConn) throws SQLException {
    CallableStatement oStmt;


    if (DebugFile.trace) {
      DebugFile.writeln("Begin Product.delete(Connection)" );
      DebugFile.incIdent();
    }


    try {
      eraseImages(oConn);
    } catch (SQLException sqle) {
      if (DebugFile.trace) DebugFile.writeln("SQLException: Product.eraseImages() " + sqle.getMessage());
      throw new SQLException(sqle.getMessage());
    }


    // Begin SQLException
      eraseLocations(oConn);


      if (DebugFile.trace) DebugFile.writeln("Connection.prepareCall({call k_sp_del_product ('" + getStringNull(DB.gu_product,"null") + "')}");


      oStmt = oConn.prepareCall("{call k_sp_del_product ('" + getString(DB.gu_product) + "')}");
      oStmt.execute();
      oStmt.close();
    // End SQLException


    if (DebugFile.trace) {
      DebugFile.decIdent();
      DebugFile.writeln("End Product.delete() : " + getString(DB.gu_product) );

View Full Code Here

Examples of java.sql.CallableStatement

   * @throws SQLException
   */
  public Integer getPosition(JDCConnection oConn, String sCategoryId) throws SQLException {
    Statement oStmt;
    ResultSet oRSet;
    CallableStatement oCall;
    Object oPos;
    Integer iPos;


    if (DebugFile.trace) {
      DebugFile.writeln("Begin Product.getPosition([Connection], " + sCategoryId + ")" );
      DebugFile.incIdent();
    }


    if (oConn.getDataBaseProduct()==JDCConnection.DBMS_POSTGRESQL) {
      oStmt = oConn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);


      if (DebugFile.trace) DebugFile.writeln("Statement.executeQuery(SELECT k_sp_cat_obj_position('" + getStringNull(DB.gu_product, "null") + "','" + sCategoryId + "'))");


      oRSet = oStmt.executeQuery("SELECT k_sp_cat_obj_position ('" + getString(DB.gu_product) + "','" + sCategoryId + "')");
      oRSet.next();
      oPos = new Integer(oRSet.getInt(1));
      oRSet.close();
      oStmt.close();
    }
    else {
      // Patched for MySQL at v 3.0.13
      oCall = oConn.prepareCall("{ call k_sp_cat_obj_position(?,?,?)}");
      oCall.setString(1, getString(DB.gu_product));
      oCall.setString(2, sCategoryId);
      oCall.registerOutParameter(3, Types.INTEGER);
      oCall.execute();
      oPos = oCall.getObject(3);
      oCall.close();
      oCall = null;
    }


    if (null==oPos)
      iPos = null;

View Full Code Here

Examples of java.sql.CallableStatement

    } // wend
    oRSet.close();
    oStmt.close();


    // Borrar los mensajes y la categoría subyacente
    CallableStatement oCall;


    if (DebugFile.trace) DebugFile.writeln("Connection.prepareCall({ call k_sp_del_newsgroup('" + sNewsGroupGUID + "') })");


    oCall = oConn.prepareCall("{ call k_sp_del_newsgroup('" + sNewsGroupGUID + "') }");


    if (DebugFile.trace) DebugFile.writeln("CallableStatement.execute({ call k_sp_del_newsgroup('" + sNewsGroupGUID + "') })");
    oCall.execute();
    oCall.close();


    return true;
  } // delete

View Full Code Here

Examples of java.sql.CallableStatement

    */
   public long incrementReplKey(Connection conn) throws Exception {
      if (conn == null)
         throw new Exception(
               "SpecificDefault.incrementReplKey: the DB connection is null");
      CallableStatement st = null;
      try {
         st = conn.prepareCall("{? = call " + this.replPrefix + "increment()}");
         st.registerOutParameter(1, Types.INTEGER);
         st.executeQuery();
         long ret = st.getLong(1);
         return ret;
      } finally {
         try {
            if (st != null)
               st.close();
         } catch (Exception ex) {
         }
      }
   }

View Full Code Here

0 1 2 3 4 5

TOP

All source code are property of their respective owners. Java is a trademark of Sun Microsystems, Inc and owned by ORACLE Inc. Contact coftware#gmail.com.