[Pljava-dev] Problem with PL/Java installation

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: <http://lists.pgfoundry.org/pipermail/pljava-dev/attachments/20060206/0403ad46/attachment.html>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: image001.jpg
Type: image/jpeg
Size: 9911 bytes
Desc: image001.jpg
URL: <http://lists.pgfoundry.org/pipermail/pljava-dev/attachments/20060206/0403ad46/attachment.jpg>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: image002.jpg
Type: image/jpeg
Size: 9887 bytes
Desc: image002.jpg
URL: <http://lists.pgfoundry.org/pipermail/pljava-dev/attachments/20060206/0403ad46/attachment-0001.jpg>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: image003.jpg
Type: image/jpeg
Size: 11409 bytes
Desc: image003.jpg
URL: <http://lists.pgfoundry.org/pipermail/pljava-dev/attachments/20060206/0403ad46/attachment-0002.jpg>