All Forums Connectivity
BB150000 2 posts Joined 06/14
17 Jun 2014
Pulling "SHOW VIEW" data using JDBC

Hello all,
I'm currently working on a project where I am pulling all of the CREATE/REPLACE view DDL from our data warehouse using Java code.  I create a result set by querying the DBC.tables table and use the RequestText field to process the SQL unless the length of the SQL is greater than the RequestText field can hold.  When that occurs, I run a seperate query issuing a "SHOW VIEW DB.ViewName" command to Teradata.  This works great unless the length of the DDL is > 32,000. 
It appears that the ResultSet defaults to a data type of LONGVARCHAR(32000) for the result of the SHOW VIEW command, and anything that is larger than that is truncated at character 32,000.
Does anyone know how I can avoid this truncation? 
I assume that if I could change the data type of the ResultSet returned from TD to LONGVARCHAR(64000), it would fix the problem.  Anyone know how to do that or know of some other  solution?
Thanks in advance!

tomnolan 594 posts Joined 01/08
18 Jun 2014

The SHOW command output result set can contain mulitple rows. Are you calling the method to fetch all the rows returned by the Teradata Database?
The SHOW command output result set typically contains embedded carriage returns. Depending on how your program prints them out, your printed output lines may overwrite previously-printed lines, making it seem as though some output is missing.
The SHOW command is actually equivalent to a multi-statement request, because it can return multiple output result sets. You should use the Statement.execute method to execute the SHOW command, and your program should iterate through all returned result sets by calling Statement.getResultSet followed by Statement.getMoreResults. See the javadoc for more information about how to use these JDBC API methods.

BB150000 2 posts Joined 06/14
20 Jun 2014

Thanks tomnolan, that's the ticket.  I was incorrectly assuming that the view DDL would be returned in one record.  I should have known better.
Thanks again!

You must sign in to leave a comment.