From: patrick.ng at zuji.com (Patrick Ng) Date: Mon, 6 Feb 2006 11:06:01 +0800 Subject: [Pljava-dev] Problem with PL/Java installation Message-ID: <631AA2476DFFD047873427369B8C0698024C2F61@teasgex1.teasin.com> Hi, I have encountered a very strange problem with running PL/Java on Windows. I wrote a Java class : import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.Statement; import java.sql.ResultSet; import java.sql.SQLException; import java.security.*; public class plj_miscellaneous { private static String jdbc_url = "jdbc:default:connection"; // use loopback jdbc driver public static boolean chkDupRecordExistsErrorsTable( String p_program_name, String p_record_info, String p_error_code, String p_error_description, String p_status, String p_created_by ) throws SQLException { Connection conn = DriverManager.getConnection( jdbc_url ); String query = "SELECT COUNT(*) FROM METAHSIA.ERRORS WHERE PROGRAM_NAME = ? AND RECORD_INFO = ? AND ERROR_CODE = ? AND ERROR_DESCRIPTION = ? AND (STATUS = ? OR STATUS = 'MAILED') AND CREATED_BY = ?"; PreparedStatement stmt = conn.prepareStatement( query ); stmt.setString( 1, p_program_name ); stmt.setString( 2, p_record_info ); stmt.setString( 3, p_error_code ); stmt.setString( 4, p_error_description ); stmt.setString( 5, p_status ); stmt.setString( 6, p_created_by ); ResultSet rs = stmt.executeQuery(); rs.next(); int count = rs.getInt( "count" ); stmt.close(); conn.close(); if (count > 0) return( true ); else return( false ); } public static int updateBatchTotals( String p_program_name, String p_processed_status, Integer p_number_processed ) throws SQLException { Connection conn = DriverManager.getConnection( jdbc_url ); int v_status = 0; try { Statement st = conn.createStatement(); String v_passed = "P"; String v_errors = "E"; if (p_processed_status.equals(v_passed)) { st.executeUpdate("INSERT INTO METAHSIA.BATCH_TOTALS (PROGRAM_NAME, TOTAL_PROCESSED, STATUS, CREATED_BY, CREATED_ON) " + "VALUES ('" + p_program_name + "', " + p_number_processed + ", 'CREATED', '" + p_program_name + "', CURRENT_DATE)"); } else if (p_processed_status.equals(v_errors)) { st.executeUpdate("INSERT INTO METAHSIA.BATCH_TOTALS (PROGRAM_NAME, TOTAL_ERRORS, STATUS, CREATED_BY, CREATED_ON) " + "VALUES ('" + p_program_name + "', " + p_number_processed + ", 'CREATED', '" + p_program_name + "', CURRENT_DATE)"); } } catch (Exception e) { System.err.println("updateBatchTotals Catch : "); System.err.println(e.getMessage()); v_status = -1; } finally { if (conn != null) { conn.close(); } else { System.out.println( "finally : DB connection not opened, no closing done" ); } return( v_status ); } } public static String checkTableColumnDataType( String p_owner_name, String p_schema_name, String p_table_name, String p_column_name ) throws SQLException { Connection conn = DriverManager.getConnection( jdbc_url ); String query = "SELECT D.TYPNAME FROM PG_TABLES A, PG_CLASS B, PG_ATTRIBUTE C, PG_TYPE D WHERE A.TABLEOWNER = ? AND A.SCHEMANAME = ? AND A.TABLENAME = ? AND C.ATTNAME = ? AND A.TABLENAME = B.RELNAME AND B.OID = C.ATTRELID AND C.ATTTYPID = D.OID ORDER BY A.TABLENAME, C.ATTNAME"; PreparedStatement stmt = conn.prepareStatement( query ); String v_ret_string = null; try { stmt.setString( 1, p_owner_name.toLowerCase() ); stmt.setString( 2, p_schema_name.toLowerCase() ); stmt.setString( 3, p_table_name.toLowerCase() ); stmt.setString( 4, p_column_name.toLowerCase() ); ResultSet rs = stmt.executeQuery(); rs.next(); v_ret_string = rs.getString(1); stmt.close(); conn.close(); } catch (Exception e) { v_ret_string = "checkTableColumnDataType Catch : " + e.getMessage(); } finally { if (conn != null) { conn.close(); } else { System.out.println( "finally : DB connection not opened, no closing done" ); } if (stmt != null) { stmt.close(); } else { System.out.println( "finally : PreparedStatement not opened, no closing done" ); } return( v_ret_string ); } } } which compiles beautifully and generates the jar file in D:\FengShui\Project-LightSabre\PL-Java\plj_miscellaneous.jar I then run this : select sqlj.install_jar('file:///D:/FengShui/Project-LightSabre/PL-Java/plj_mis cellaneous.jar', 'plj_miscellaneous', true); select sqlj.set_classpath('metahsia', 'plj_miscellaneous'); CREATE OR REPLACE FUNCTION metahsia.chkDupRecordExistsErrorsTable( TEXT,TEXT,TEXT,TEXT,TEXT,TEXT ) RETURNS BOOLEAN AS 'plj_miscellaneous.chkDupRecordExistsErrorsTable( java.lang.String,java.lang.String,java.lang.String,java.lang.String,java .lang.String,java.lang.String )' LANGUAGE java; CREATE OR REPLACE FUNCTION metahsia.updateBatchTotals( TEXT,TEXT,INTEGER ) RETURNS INTEGER AS 'plj_miscellaneous.updateBatchTotals( java.lang.String,java.lang.String,java.lang.Integer )' LANGUAGE java; CREATE OR REPLACE FUNCTION metahsia.checkTableColumnDataType( TEXT,TEXT,TEXT,TEXT ) RETURNS TEXT AS 'plj_miscellaneous.checkTableColumnDataType( java.lang.String,java.lang.String,java.lang.String,java.lang.String )' LANGUAGE java; while login using psql. In sqlj schema, I can find the following tables : classpath_entry - which contains the values jar_entry - which contains the values jar_repository - which contains the values At psql prompt, when I do a : select metahsia.checktablecolumndatatype('hsia', 'metahsia', 'usr', 'password'); I keep getting ERROR: java.lang.ClassNotFoundException: plj_miscellaneous However, these 3 tables' values seem correct. Do u have any idea why this is so? Is it a bug with PostgreSQL? Best regards -------------- next part -------------- An HTML attachment was scrubbed... URL: -------------- next part -------------- A non-text attachment was scrubbed... Name: image001.jpg Type: image/jpeg Size: 9911 bytes Desc: image001.jpg URL: -------------- next part -------------- A non-text attachment was scrubbed... Name: image002.jpg Type: image/jpeg Size: 9887 bytes Desc: image002.jpg URL: -------------- next part -------------- A non-text attachment was scrubbed... Name: image003.jpg Type: image/jpeg Size: 11409 bytes Desc: image003.jpg URL: