ConText, JDBC and Column Type

From: Lennart Staflin <lennarts_at_infotek.no>
Date: 28 Apr 1999 10:01:53 +0200
Message-ID: <yg0aevtrx5a.fsf@pc-lennarts.infotek.no>

Hi!

I am trying to use Oracle ConText via JDBC. But the column types reported by JDBC are strange for some integer columns.

Oracle 7.3.4 running on a Sparc with SunOS 5.5.1. Using Oracle JDBC from 8.0.5 (but I get similar results with Weblogics on the Sparc)

For example, I have a table texttab:

 Name Null? Type

• -------- ---- TEXTID NOT NULL NUMBER(9) TEXTEN VARCHAR2(200) NUFFRAN NUMBER(38)

and a ConText policy:

 exec ctx_ddl.create_policy('l_test', 'lennarts.texttab.texten');

If I issue the SQL query:

 SELECT * FROM texttab WHERE texten LIKE '%bar%'

the JDBC interface reports the type (getColumnType) of the column 'NUFFRAN' as NUMERIC with precision 38 and scale 0. Thats what I had expected.

But if I issue the SQL query:

 SELECT * FROM texttab WHERE CONTAINS(texten, 'bar', 1) > 1

the reported type is NUMERIC with precision 0 and scale 0.

I include a sample test program in Java for this. The output from the program is:

Without ConText

Column (1) TEXTID: (2) NUMBER (9,0)
Column (2) TEXTEN: (12) VARCHAR2 (0,0)
Column (3) NUFFRAN: (2) NUMBER (38,0)

WITH ConText

Column (1) TEXTID: (2) NUMBER (0,0)
Column (2) TEXTEN: (12) VARCHAR2 (0,0)
Column (3) NUFFRAN: (2) NUMBER (0,0)
Column (4) SCORE0: (2) NUMBER (0,0)

Notice 'NUFFRAN: (2) NUMBER(38,0)' which means JDBC type code 2, precision 38 scale 0.
And 'NUFFRAN: (2) NUMBER (0,0)' same type code but precision 0.

The program:

---

import java.sql.*;

class LCon
{

    public static void main (String args [])

        throws SQLException
    {

	try {
	    Class.forName("oracle.jdbc.driver.OracleDriver");


	}


	catch (ClassNotFoundException e) {
	    System.out.println("Cannot load oracle driver");
	    return;


	}


					   
	Connection conn =
	    DriverManager.getConnection ("jdbc:oracle:thin:@jenufa:8888:SL",
					 "lennarts", "*");

	// Create a Statement
	Statement stmt = conn.createStatement ();
	ResultSet rset = stmt.executeQuery
            ("select * from texttab where texten like '%bar%'");
        System.out.println("Without ConText");
        dumpColumns(rset);

        Statement stmt2 = conn.createStatement();
        rset = stmt2.executeQuery
            ("select * from texttab where contains(texten, 'bar', 1) > 1");
        System.out.println("WITH ConText");
        dumpColumns(rset);

    }

    static private void dumpColumns(ResultSet rset)

        throws SQLException
    {

        ResultSetMetaData metadata = rset.getMetaData();
        for (int i = 1; i <= metadata.getColumnCount(); ++i) {
            System.out.println("Column (" + i + ") "
                               + metadata.getColumnName(i)
                               + ": (" + metadata.getColumnType(i)
                               + ") " + metadata.getColumnTypeName(i)
                               + " (" + metadata.getPrecision(i)
                               + ","  + metadata.getScale(i)
                               + ")");
        }

    }
}

--
Lennart Staflin <lennarts_at_infotek.no> /*/ STEP Infotek AS, Gjerdrums vei 12, N-0486 Oslo, Norway, http://www.infotek.no/ Received on Wed Apr 28 1999 - 03:01:53 CDT