Informix JDBC Driver Programmer's Guide Appendix A: Sample Code

Contents

Index

/***************************************************************************
 *  Title:         DBMetaData.java
 *
 *  Description:    Demo a databasemetata operation 
 *		
 *  An example of running the program: 
 *		
 *   java DBMetaData
 *      'jdbc:informix-
sqli://emily:1533:informixserver=emily3;user=rdtest;password=test'
 *   Expected result:
 *
 * >>>Simple databasemetadata operation test.
 * URL = "jdbc:informix-
sqli://emily:1533/testDB:informixserver=emily3;user=rdtest;password=test"
 * number of columns 8
 * procedure_catnull
 * procedure_schema,
 * procedure_name,
 * reserved1,
 * reserved2,
 * reserved3,
 * remarks,
 * procedure_type 
 * 
 * testDB,
 * <null>,
 * proc3,
 * <null>,
 * <null>,
 * <null>,
 * <null>,
 * 0 
 * 
 * >>>End of Simple databasemetadata operation test.
 *		
 ***************************************************************************
 */

 

import java.sql.*;
import java.util.*;
import java.math.BigDecimal ;
import java.io.*;
import java.lang.*;

public class DBMetaData {

    public static void main(String[] args)
    {
        if (args.length == 0)
	    {
	    System.out.println("ERROR: connection URL must be provided in order to run 
the demo!");
	    return;
	    }

        String url = args[0];
        StringTokenizer st = new StringTokenizer(url, ":");
        String token;
        String newUrl = "";

        for (int i = 0; i < 4; ++i)
	    { 
            if (!st.hasMoreTokens())
   	        {
	        System.out.println("ERROR: incorrect URL format!");
	        return;
	        }
            token = st.nextToken();
            if (newUrl != "")
	        newUrl += ":";
            newUrl += token;
            }

        newUrl += "/testDB";

        while (st.hasMoreTokens())
	    { 
            newUrl += ":" + st.nextToken();
            }

	Connection conn = null;
        int 	   rc;
	String     cmd=null;
	Statement  stmt = null;

        String testName = "Simple databasemetadata operation";

	System.out.println(">>>" + testName + " test.");
	System.out.println("URL = \"" + newUrl + "\"");

	try 
	    {
	    Class.forName("com.informix.jdbc.IfxDriver");
	    } 
	catch (Exception e)
	    {
	    System.out.println("ERROR: failed to load Informix JDBC driver.");
	    }

	try 
	    {
	    conn = DriverManager.getConnection(newUrl);
	    } 
	catch (SQLException e) 
	    {
	    System.out.println("ERROR: failed to connect!");
	    }

	try
	    {
            ResultSet            rs ;
            DatabaseMetaData     dmd ;

	    stmt = conn.createStatement();
            dmd = conn.getMetaData();
            stmt.executeUpdate(
            "create procedure proc3( i integer ) " +
            "returning integer ; " +
            "  define j integer ; " +
            "  for j in ( 1 to 5 ) " +
            "    let i = i + 1 ; " +
            "    return i with resume ; " +
            "  end for " +
            "end procedure"
             ) ;
             rs = dmd.getProcedures( null, null, "proc3" ) ;
             dispResult( rs ) ;
	    }
	catch (SQLException e)
	    {
	    System.out.println("ERROR: execution failed - statement: " + cmd);
	    System.out.println("ERROR: " + e.getMessage());
	    }

	try
	    {
	    stmt = conn.createStatement();
	    cmd = "drop procedure proc3";
	    rc = stmt.executeUpdate(cmd);
	    stmt.close();
	    }
	catch (SQLException e)
	    {
	    System.out.println("ERROR: execution failed - statement: " + cmd);
	    System.out.println("ERROR: " + e.getMessage());
	    }

	try 
	    {
            conn.close();
	    } 
	catch (SQLException e) 
	    {
	    System.out.println("ERROR: failed to close the connection!");
	    }

	System.out.println(">>>End of " + testName + " test.");
    }

  public static void dispResult( ResultSet rs ) 
  throws SQLException 
    {
    int               i ;
    int               ncols ;
    ResultSetMetaData rsmd ;
    String            s = null ;
    String            comma = null ;
    byte []           blob ;
    int               typ ;
    int               blen ;

    rsmd = rs.getMetaData() ;
    ncols = rsmd.getColumnCount() ;

    System.out.println( "number of columns " + ncols ) ;

    for( i=1; i<=ncols; ++i ) 
      {
      if( i > 1 )  
         {
         if (i < ncols) 
            comma = "," ;
         else 
            comma = " ";
         }
       System.out.println((rsmd.getColumnLabel( i )).trim() + comma ) ;
       }
    System.out.println( "" ) ;

    while( rs.next() ) {
      for( i=1; i<=ncols; ++i ) {
       // if( i > 1 ) System.out.println(", " ) ;

        typ = rsmd.getColumnType( i ) ;
        //System.out.print( "typ " + typ + " " ) ;

        switch( typ ) {
          case Types.CHAR:  
          case Types.VARCHAR:
          case Types.LONGVARCHAR:
            s = rs.getString( i ) ;
            break ;

          case Types.SMALLINT:   
            short v = rs.getShort( i ) ;
            if( !rs.wasNull() ) s = "" + v ;
            break ;
            
          case Types.INTEGER:   
            int vi = rs.getInt( i ) ;
            if( !rs.wasNull() ) s = "" + vi ;
            break ;

          case Types.DOUBLE: 
            double vd = rs.getDouble( i ) ;
            if( !rs.wasNull() ) s = "" + vd ;
            break ;

          case Types.FLOAT: 
            float vf = rs.getFloat( i ) ;
            if( !rs.wasNull() ) s = "" + vf ;
            break ;

          case Types.DECIMAL: 
          case Types.NUMERIC: 
            BigDecimal vb = rs.getBigDecimal( i, 8 ) ;
            if( !rs.wasNull() ) s = vb.toString() ;
            break ;

          case Types.DATE: 
            java.sql.Date vt = rs.getDate( i ) ;
            if( !rs.wasNull() ) s = vt.toString() ;
            break ;

          case Types.TIMESTAMP: 
            Timestamp vs = rs.getTimestamp( i ) ;
            if( !rs.wasNull() ) s = vs.toString() ;
            break ;

          case Types.LONGVARBINARY:
            //blen = ((VtiResultSet)rs).getBytesLength( i ) ;
            //System.out.print( blen + " " ) ;
            blob = rs.getBytes( i ) ;
            if( !rs.wasNull() ) 
              s = "[blob length " + blob.length + "]" ;
            break ;

          default:
            s = "" ;

        }
        if( rs.wasNull() ) 
             {
             if( i > 1 )  
                {
                if (i < ncols) 
                    comma = "," ;
                else 
                    comma = " ";
                }
             System.out.println("<null>" + comma ) ;
             }
        else 
             {
             if (i < ncols) 
                 comma = "," ;
             else 
                 comma = " ";
             System.out.println(s.trim() + comma ) ;
             }
      }
      System.out.println( "" ) ;
    }
  }
}