Archives of the TeradataForum

Message Posted: Wed, 09 Nov 2005 @ 22:46:09 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

My last comment related to views. I don't ever recall the dictionary showing much information about views.

As far as prepared queries go, the JDBC and ODBC API (and CLI for that matter) defines some functions that give access to this feature. In Teradata, the query is passed from a client application to the Parsing Engine where it is "compiled" and meta-data is returned. In other words, a client application must make the request of the database.

If the view had data in it, you could get similar info in queryman with a query such as this:

     select type (LogDate       ), type (LogTime       ), type (LogonDate), etc from dbc.accesslog sample 1

If the view is "empty", the above simply returns an empty result set - which is not very helpful.

Below is a simple Java example that prints the datatypes of the columns in the access log query.

Here is the output of the sample program (you can also get display widths, nullable info and so on).

     Column = LogDate, type = DATE
     Column = LogTime, type = TIME
     Column = LogonDate, type = DATE
     Column = LogonTime, type = TIME
     Column = LogicalHostId, type = SMALLINT
     Column = IFPNo, type = SMALLINT
     Column = SessionNo, type = INTEGER
     Column = UserName, type = VARCHAR
     Column = AccountName, type = VARCHAR
     Column = OwnerName, type = VARCHAR
     Column = AccessType, type = CHAR
     Column = Frequency, type = CHAR
     Column = EventCount, type = INTEGER
     Column = Result, type = CHAR
     Column = DatabaseName, type = VARCHAR
     Column = TVMName, type = VARCHAR
     Column = ColumnName, type = VARCHAR
     Column = StatementType, type = VARCHAR
     Column = StatementText, type = VARCHAR

And here is the Java source code that produced it.

     import java.sql.*;

     class TestJdbcPrepare {
         public static void main (String [] args) {
             String driverName = "";

             try {

                         // We aren't using the Teradata JDBC driver here because it doesn't
                         // support getting meta data from a prepared statement.
                         // Teradata and the Teradata ODBC driver support this operation, so
                         // we will use the JDBC->ODBC bridge in this example.

                 driverName = "sun.jdbc.odbc.JdbcOdbcDriver";
                 Class.forName (driverName);
                     // Define the connection details:
                     // jdbc = use jdbc - don't change this
                     // odbc = use the jdbc->odbc bridge driver - don't change this
                     // local = the name of the ODBC DSN to use to establish the
                     //         connection, you should change this to match one of your DSN's.
                 String dbURL = "jdbc:odbc:local";
                 String uid = "dbc";
                 String pwd = "dbc";

                     // Get a connection to the database.
                 Connection conn = DriverManager.getConnection (dbURL, uid, pwd);
                     // Prepare the query we are interested in getting meta-data for.
                 PreparedStatement stmt = conn.prepareStatement ("select * from dbc.accesslog;");
                 ResultSetMetaData rsmd = stmt.getMetaData ();
                     // Dump selected values from the meta data.
                 for (int i = 1; i <= rsmd.getColumnCount (); i++) {
                     System.out.println ("Column = " + rsmd.getColumnName (i) + ", type = " + rsmd.getColumnTypeName (i));
                 }
             }
             catch (SQLException e) {
                 System.out.println ("SQLException connecting " + e);
             }
             catch (ClassNotFoundException e) {
                 System.out.println ("Class not found (" + driverName + "):" + e);
             }
         }
     }

Regards

Glenn Mc

	<Prev	Next>		<<First	<Prev	Next>	Last>>