0

i am trying to connect to teradata and execute a statement and reading the data using stringbuilder. I want the get this as output when i define the outparameter as string the output is getting truncated as it is greater than 50K characters, i want this to be coverted as CLOB and give me the output as CLOB, could some one please help me how i can achieve this.

here is the code i am using .. 

import java.sql.*;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.PreparedStatement;
import java.util.*;
import java.sql.*;
public class getDDLinfo {
    public static void getDDL (int [] status, String objectType, String objName, String [] ddl)
            throws SQLException {
        ddl[0] = "Unknown failure in XSP.";
        status[0] = 1;
        Connection conn = DriverManager.getConnection("jdbc:default:connection");

        try {
            String sql = "show " + objectType + " " + objName + ";";
            PreparedStatement stmt = conn.prepareStatement(sql);

            StringBuilder result = new StringBuilder();
            ResultSet rs = stmt.executeQuery();
            while (rs.next()) {
                result.append(rs.getString(1));
                result.append("\n");
            }
            if (result.length() > 0) {
                ddl[0] = result.toString();
            }
                      rs.close();
            stmt.close();
            conn.close();
            status[0] = 0;
        } catch (SQLException e) {
            status[0] = e.getErrorCode();
            ddl[0] = e.getMessage();
        }
    }
        public static String toHexString(byte[] ba)
{
        StringBuilder str = new StringBuilder();
        for(int i = 0; i < ba.length; i++)
            str.append(String.format("%x ", ba[i]));
        return str.toString();
    }
}
lucky
  • 41
  • 1
  • 1
  • 4
  • What error are you getting? Please provide a [minimal reproducible example](https://stackoverflow.com/help/minimal-reproducible-example) – DCTID Dec 17 '19 at 19:12
  • Each call to String.format creates a new StringBuilder, which kind of negates the benefit of using a StringBuilder to avoid creating lots of objects. See https://stackoverflow.com/questions/19450452/how-to-convert-byte-array-to-hex-format-in-java/19469285#19469285. – VGR Dec 17 '19 at 20:19
  • So this is a Java XSP (since your connection is jdbc:default:connection)? What does the CREATE PROCEDURE definition look like? In an XSP that `READS SQL DATA` you need to use the com.teradata.jdbc.jdbc_4.Clob as the implementing class for the Clob object. Then use the clob's setString method to append to the value. – Fred Dec 17 '19 at 20:41
  • Hi Fred, thanks for taking time and responding.. here is the Procedure definition. If i replace VARCHAR(64000) with CLOB it throws me an error String not able to convert ..etc. but if i use the same definiton it the output is getting truncated if my table definition is greater than 64K replace procedure $DP.$PROCNAME (OUT status INTEGER, IN objType varchar(80), IN objName varchar(80), OUT ddl varchar(64000) ) LANGUAGE JAVA CONTAINS SQL PARAMETER STYLE JAVA EXTERNAL NAME '$JARNAME:$METHODNAME'; – lucky Dec 18 '19 at 09:01
  • Fred- appreciate if you could give me an example how to use to Clob and if i use setstring can that handle more than 64K ? – lucky Dec 18 '19 at 09:04

0 Answers0