[Pljava-dev] Problem with PL/Java installation
Patrick Ng
patrick.ng at zuji.com
Mon Feb 6 03:06:01 UTC 2006
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://pgfoundry.org/pipermail/pljava-dev/attachments/20060206/0403ad46/attachment.html
-------------- next part --------------
A non-text attachment was scrubbed...
Name: not available
Type: image/jpeg
Size: 9911 bytes
Desc: image001.jpg
Url : http://pgfoundry.org/pipermail/pljava-dev/attachments/20060206/0403ad46/attachment.jpe
-------------- next part --------------
A non-text attachment was scrubbed...
Name: not available
Type: image/jpeg
Size: 9887 bytes
Desc: image002.jpg
Url : http://pgfoundry.org/pipermail/pljava-dev/attachments/20060206/0403ad46/attachment-0001.jpe
-------------- next part --------------
A non-text attachment was scrubbed...
Name: not available
Type: image/jpeg
Size: 11409 bytes
Desc: image003.jpg
Url : http://pgfoundry.org/pipermail/pljava-dev/attachments/20060206/0403ad46/attachment-0002.jpe
More information about the Pljava-dev
mailing list