Announcement Announcement Module
Collapse
No announcement yet.
How to handle CLOB data returned from a stored procedure. Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • How to handle CLOB data returned from a stored procedure.

    I am new to spring and want to store CLOB data returned from a stored procedure into a file. I am working with teradata database.

    My stored procedure takes three input parameters and returns a CLOB value.

    Bean class

    .....................................
    public class Publish{
    private JdbcTemplate template;

    public void setDataSource(DataSource dataSource){
    this.template = new JdbcTemplate(dataSource);
    }

    @SuppressWarnings("unchecked")
    public String genCanonicalSchema(){
    File schemaFile = new File("c:\\NSPCanSchema.xsd");
    String result = (String)this.template.execute(new MyCallableStatementCreator("genCanonicalSchema"), new MyCallableStatementCallback());
    Clob xmlClob= (Clob)results.get("pS");
    try{
    System.out.println("Stream Length "+xmlClob.length());
    Reader fread = xmlClob.getCharacterStream();
    FileWriter fw = new FileWriter(schemaFile);
    char[] cbuf=new char[1];
    while (fread.read(cbuf)> 0){
    fw.write(cbuf);
    }
    fw.close();
    }catch(IOException ioe){
    System.out.println("IOException");
    ioe.printStackTrace();
    }catch(SQLException sqle){
    System.out.println("SQLException");
    sqle.printStackTrace();
    }
    return (schemaFile.length() > 0) ?"success":"failure";*/
    return result;
    }

    private class MyCallableStatementCreator implements CallableStatementCreator{
    String action;
    String sqlQry = "SELECT * FROM TESTXML.CRM_NPS_SURVEY_RESP_CT";
    MyCallableStatementCreator(String action){
    this.action = action;
    }

    public CallableStatement createCallableStatement(Connection con)
    throws SQLException {
    CallableStatement cstmt=null;
    if (action.equals("genCanonicalSchema")){
    cstmt=con.prepareCall("call sysxml.gen_query_canonical_schema(?,?,?,?)");
    System.out.println("Generating Canonical schema...");
    cstmt.setString(1, sqlQry);
    cstmt.setString(2, "");
    cstmt.setString(3, "");
    cstmt.registerOutParameter(4, java.sql.Types.CLOB, "clobVar");
    }
    return cstmt;
    }
    }

    private class MyCallableStatementCallback implements CallableStatementCallback{

    public Object doInCallableStatement(CallableStatement cstmt)
    throws SQLException, DataAccessException {
    File schemaFile = new File("c:\\NSPCanSchema.xsd");
    Clob xmlClob= cstmt.getClob("clobVar");
    try{
    System.out.println("Stream Length "+xmlClob.length());
    Reader fread = xmlClob.getCharacterStream();
    FileWriter fw = new FileWriter(schemaFile);
    char[] cbuf=new char[1];
    while (fread.read(cbuf)> 0){
    fw.write(cbuf);
    }
    fw.close();
    }catch(IOException ioe){
    System.out.println("IOException");
    ioe.printStackTrace();
    }catch(SQLException sqle){
    System.out.println("SQLException");
    sqle.printStackTrace();
    }

    return (schemaFile.length() > 0)?"Success":"Failure";
    }

    }
    }

    .................................................. ....

    The client program that calls the stored procedure is .......

    public class TestPublishClient{

    public static void main(String[] args) {

    Publish pws = ContextUtil.getBean("publish");
    System.out.println(pws.genCanonicalSchema());
    }

    }

    .................................................. ................

    When I run my client program it throws the following exception.

    -----------------------------------------------------------

    Exception in thread "main" org.springframework.jdbc.UncategorizedSQLException : CallableStatementCallback; uncategorized SQLException for SQL []; SQL state [HY000]; error code [952]; [Teradata JDBC Driver] [TeraJDBC 12.00.00.105] [Error 952] [SQLState HY000] TeraCallableStatement: getClob: function not supported in this version; nested exception is java.sql.SQLException: [Teradata JDBC Driver] [TeraJDBC 12.00.00.105] [Error 952] [SQLState HY000] TeraCallableStatement: getClob: function not supported in this version
    Caused by: java.sql.SQLException: [Teradata JDBC Driver] [TeraJDBC 12.00.00.105] [Error 952] [SQLState HY000] TeraCallableStatement: getClob: function not supported in this version
    at com.teradata.jdbc.jdbc_4.util.ErrorFactory.makeLeg acyDriverException(ErrorFactory.java:149)
    at com.teradata.jdbc.jdbc_4.util.ErrorFactory.makeLeg acyDriverException(ErrorFactory.java:143)
    at com.teradata.jdbc.ComUtil.ThrowExcp(ComUtil.java:1 536)
    at com.teradata.jdbc.TeraCallableStatement.getClob(Te raCallableStatement.java:1342)
    at com.teradata.trm.webservices.Publish$MyCallableSta tementCallback.doInCallableStatement(Publish.java: 155)
    at org.springframework.jdbc.core.JdbcTemplate.execute (JdbcTemplate.java:842)
    at com.teradata.trm.webservices.Publish.genCanonicalS chema(Publish.java:41)
    at com.teradata.trm.webservices.client.TestPublishCli ent.main(TestPublishClient.java:12)


    --------------------------------------------------------------------------

    Can anybody help in resolving this exception?

    Is there anyway i can call this stored procedure and handle returned CLOB data efficiently? If so, please give me an example.


    Thanks in advance.
Working...
X