Examples of java.sql.Clob

java.sql.Clob
A Java interface mapping for the SQL CLOB type.
An SQL CLOB type stores a large array of characters as the value in a column of a database.
The java.sql.Clob interface provides methods for setting and retrieving data in the Clob, for querying Clob data length, for searching for data within the Clob.

            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

View Full Code Here

         ClientProperty prop = new ClientProperty(name, null, null);
         prop.setValue((byte[])val);
      }
      if (val instanceof Clob) { // only for relatively small clobs (< 10MB)
         try {
            Clob clob = (Clob)val;
            InputStream in = clob.getAsciiStream();
            ByteArrayOutputStream baos = new ByteArrayOutputStream();
            // StringBuffer strBuf = new StringBuffer();
            byte[] buf = new byte[100000];
            int read = 0;
            int count=0;

View Full Code Here

   }
   
   
   private final String getContent(ResultSet rs, int clobPos) throws Exception {
      String content = null;
      Clob clob = rs.getClob(clobPos);
      if (clob != null) {
         long length = clob.length();
         if (length > Integer.MAX_VALUE)
            throw new Exception("ReplicationConverter.addInfo: the content is too big ('" + length + "' bytes) to fit in one msg, can not process");


         if (useReaderCharset) {
            char[] cbuf = new char[(int)length];
            clob.getCharacterStream().read(cbuf);
            content = new String(cbuf);
         }
         else {
            byte[] buf = new byte[(int)length];
            clob.getAsciiStream().read(buf);
            /*
            if (debug) {
               Map map = Charset.availableCharsets();
               String[] keys = (String[])map.keySet().toArray(new String[map.size()]);
               for (int i=0; i < keys.length; i++) {

View Full Code Here

  {
    // We always get the CLOB, even when we are not reading the contents.
    // Since the CLOB is just a pointer to the CLOB data rather than the
    // data itself, this operation should not take much time (as opposed
    // to getting all of the data in the clob).
    Clob clob = rs.getClob(index);


    if (rs.wasNull())
      return null;


    // CLOB exists, so try to read the data from it
    // based on the user's directions
    if (_readClobs)
    {
      // User said to read at least some of the data from the clob
      String clobData = null;
      if (clob != null)
      {
        int len = (int)clob.length();
        if (len > 0)
        {
          int charsToRead = len;
          if ( ! _readCompleteClobs)
          {
            charsToRead = _readClobsSize;
          }
          if (charsToRead > len)
          {
            charsToRead = len;
          }
          clobData = clob.getSubString(1, charsToRead);
        }
      }


      // determine whether we read all there was in the clob or not
      boolean wholeClobRead = false;

View Full Code Here

                        return new JDBCInputStream(blob.getBinaryStream(), conn);
                    }
                    break;


                case Types.CLOB :
                    Clob clob = rs.getClob(1);
                    if (clob != null) {
                        return new JDBCInputStream(clob.getAsciiStream(), conn);
                    }
                    break;


                default :
                    String value = rs.getString(1);

View Full Code Here

        {
          int columnType = resultSet.getMetaData().getColumnType(columnIndex.intValue());
          switch (columnType)
          {
            case Types.CLOB:
              Clob clob = resultSet.getClob(columnIndex.intValue());
              if (resultSet.wasNull())
              {
                objValue = null;
              }
              else
              {
                objValue = clobToString(clob);
              }
              break;
              
            default:
              objValue = resultSet.getString(columnIndex.intValue());
              if(resultSet.wasNull())
              {
                objValue = null;
              }
              break;
          }
        }
        else if (clazz.equals(Clob.class))
        {
          objValue = resultSet.getClob(columnIndex.intValue());
          if(resultSet.wasNull())
          {
            objValue = null;
          }
        }
        else if (clazz.equals(Reader.class))
        {
          Reader reader = null;
          long size = -1;
          
          int columnType = resultSet.getMetaData().getColumnType(columnIndex.intValue());
          switch (columnType)
          {
            case Types.CLOB:
              Clob clob = resultSet.getClob(columnIndex.intValue());
              if (!resultSet.wasNull())
              {
                reader = clob.getCharacterStream();
                size = clob.length();
              }
              break;
              
            default:
              reader = resultSet.getCharacterStream(columnIndex.intValue());

View Full Code Here

                }


                break;
            }
            case Types.SQL_CLOB : {
                Clob clob = getClob(columnIndex);


                if (clob == null) {
                    sqlxml = null;
                } else {
                    sqlxml = new JDBCSQLXML(clob.getCharacterStream());
                }


                break;
            }
            case Types.SQL_CHAR :
            case Types.SQL_VARCHAR :
            case Types.VARCHAR_IGNORECASE : {
                java.io.Reader reader = getCharacterStream(columnIndex);


                if (reader == null) {
                    sqlxml = null;
                } else {
                    sqlxml = new JDBCSQLXML(reader);
                }


                break;
            }
            case Types.SQL_NCHAR :
            case Types.SQL_NVARCHAR : {
                java.io.Reader nreader = getNCharacterStream(columnIndex);


                if (nreader == null) {
                    sqlxml = null;
                } else {
                    sqlxml = new JDBCSQLXML(nreader);
                }


                break;
            }
            case Types.SQL_BLOB : {
                Blob blob = getBlob(columnIndex);


                if (blob == null) {
                    sqlxml = null;
                } else {
                    sqlxml = new JDBCSQLXML(blob.getBinaryStream());
                }


                break;
            }
            case Types.SQL_BINARY :
            case Types.SQL_VARBINARY : {
                java.io.InputStream inputStream = getBinaryStream(columnIndex);


                if (inputStream == null) {
                    sqlxml = null;
                } else {
                    sqlxml = new JDBCSQLXML(inputStream);
                }


                break;
            }
            case Types.OTHER :
            case Types.JAVA_OBJECT : {
                Object data = getObject(columnIndex);


                if (data == null) {
                    sqlxml = null;
                } else if (data instanceof SQLXML) {
                    sqlxml = (SQLXML) data;
                } else if (data instanceof String) {
                    sqlxml = new JDBCSQLXML((String) data);
                } else if (data instanceof byte[]) {
                    sqlxml = new JDBCSQLXML((byte[]) data);
                } else if (data instanceof Blob) {
                    Blob blob = (Blob) data;


                    sqlxml = new JDBCSQLXML(blob.getBinaryStream());
                } else if (data instanceof Clob) {
                    Clob clob = (Clob) data;


                    sqlxml = new JDBCSQLXML(clob.getCharacterStream());
                } else {
                    throw Util.notSupported();
                }


                break;

View Full Code Here

                            Reader reader = resultSet.getCharacterStream(i + 1);
                            readerToXml(reader, columns[i]);
                            break;


                        case Types.CLOB:
                            Clob clob = resultSet.getClob(i + 1);
                            if (clob != null) {
                                Reader clobReader = clob.getCharacterStream();
                                readerToXml(clobReader, columns[i]);
                            }
                            break;


                        // TODO base64 BLOBs?

View Full Code Here

            }
            streamBytes(blob.getBinaryStream(), response);
            break;


        case Types.CLOB:
            Clob clob = resultSet.getClob(columnIndex);
            if (clob == null) {
                throw new Exception("CLOB value is null.");
            }
            // wrap our reader in order to strip invalid XML chars
            Reader reader1 = new XMLTextReader(clob.getCharacterStream());
            streamCharacters(reader1, response);
            break;


        case Types.LONGVARCHAR:
        case Types.VARCHAR:

View Full Code Here


            if (this.updateQueryDefinition == null) {


                // in-place modification
                // Get a reference to the CLOB
                Clob clob = resultSet.getClob(columnIndex);
                if (clob == null) {
                    throw new Exception("CLOB value is null.");
                }


                // clear the CLOB. If we don't do this, previous data in the
                // CLOB that exceeds the length of the new data will not be
                // overwritten.
                // FIXME Not supported by MySQL
                // blob.truncate(0);


                // copy the request body to the CLOB
                writeCharacters(request.getReader(), clob.setCharacterStream(1));


            } else {


                // use the update query (this is the most common situation:
                // MySQL, Oracle)
                Query updateQuery = new Query(this.updateQueryDefinition,
                        parameterResolver);


                // The blob object should be already in the parameters via the
                // BlobColumnDomain
                Clob clob = getClob(updateQuery.getStatementParameters());
                if (clob == null) {
                    throw new Exception("Unable to get reference to a CLOB. "
                            + "Maybe the CLOB field is null on the db "
                            + "or the wrong column name has been specified.");
                }


                // clear the CLOB. If we don't do this, previous data in the
                // CLOB that exceeds the length of the new data will not be
                // overwritten.
                // FIXME Not supported by MySQL
                // clob.truncate(0);


                // write the request body to the blob
                writeCharacters(request.getReader(), clob.setCharacterStream(1));
                // perform the update
                updateQuery.execute(connection);


            }
            break;

View Full Code Here

0 1 2 3 4 5 6 7 8 9

TOP

Related Classes of java.sql.Clob

anvil.util.SQLUtil

br.net.woodstock.rockframework.core.jdbc.util.JDBCUtils

br.net.woodstock.rockframework.jdbc.util.JDBCUtils

cn.org.zeronote.orm.extractor.BaseRowProcessor

com.alibaba.druid.bvt.filter.FilterChainTest_Clob

com.alibaba.druid.bvt.filter.FilterChainTest_Clob_2

com.alibaba.druid.bvt.filter.FilterChainTest_NClob

com.alibaba.druid.bvt.filter.FilterChainTest_NClob_2

com.alibaba.druid.filter.FilterChainImpl

com.alibaba.druid.filter.stat.StatFilter

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.