testdbbasics.java

java开源的企业总线.xmlBlaster

      if (!this.specificHelper.isOracle()) {
         log.info("Stop testInternalFunctions (nothing to be done since not oracle)");
         return;
      }
      
      I_DbPool pool = (I_DbPool)info.getObject("db.pool");
      assertNotNull("pool must be instantiated", pool);
      Connection conn = null;
      
      try {
         conn  = pool.reserve();
         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.decodeBase64(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.decodeBase64(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.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);
             }
          }
         
/*
         {
            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.decodeBase64(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");