Java Stored Procedures Accessed from VB HELP!
Date: 17 Oct 2002 02:50:19 -0700
Message-ID: <625a4aef.0210170150.1e59a114_at_posting.google.com>
PROBLEM: POL-8000 error when calling Java function from Oracle.
Hi all,
This might be a simple fix, but I have tried EVERYTHING. So any help would be appreciated.
Basically we have Java JDK2.1.4, Oracle Lite 4 (i think) and VB 6. By trade I am a Java and PL/SQL developer and do not know much about VB.
I created a table as follows and inserted some data into is as follows
CREATE TABLE TESTPROTO (id NUMBER NOT NULL, name VARCHAR2(2000) NOT NULL); COMMIT; INSERT INTO TESTPROTO VALUES(1,'jason');
INSERT INTO TESTPROTO VALUES(2,'van wyk');
INSERT INTO TESTPROTO VALUES(3,'anthony');
INSERT INTO TESTPROTO VALUES(4,'gareth');
INSERT INTO TESTPROTO VALUES(1,'jasonduplicate');
COMMIT; I have created the following class file
import java.sql.*;
class testproto {
public static String test(Connection conn) throws Exception {
Statement stmt = null;
ResultSet retset = null;
try {
stmt = conn.createStatement();
}catch (SQLException e)
{
System.out.println("conn.createStatement failed: " +
e.getMessage() + "\n");
System.exit(0);
}//catch
try {
retset = stmt.executeQuery("SELECT name " +
"FROM TEST " +
"WHERE id = 1");
if (retset.next()) {
return(retset.getString(1));
} else return("");
}//try
catch (Exception e) {System.out.println(e);}
return "";
}//get_person_name
Then I mapped the java function to an oracle function as follows
CREATE OR REPLACE java class using bfile
('d:\working\prototype','testproto.class');
CREATE OR REPLACE FUNCTION testfunc
RETURN VARCHAR2
AS LANGUAGE JAVA
NAME 'testproto.test(java.sql.Connection) return java.lang.String';
COMMIT; Then I ran the following statement
SELECT testfunc() FROM dual;
What was returned was Jason which is correct. So I can assume that I have set everything up correctly on the oracle lite side.
Now comes the problem I created the following VB code and attached it to a button so that when you click on the button the code is executed
Dim CPw1 As rdoQuery
Dim en As rdoEnvironment
Dim con As rdoConnection
Dim Qy As New rdoQuery
Dim rs As rdoResultset
Set en = rdoEngine.rdoEnvironments(0) Set con = en.OpenConnection(dsName:="Data1", _
Prompt:=rdDriverNoPrompt, _
Connect:="Uid=SYSTEM;pwd=MANAGER;DSN=POLITE")
QSQL = "{call testfunc1()}"
Set Qy = con.CreateQuery("", QSQL)
Set rs = Qy.OpenResultset(rdOpenStatic, rdConcurReadOnly) MsgBox (rs(0))
rs.Close
(the above code can succesfully call a stored PLSQL function in a
Oracle db tested and it worked)
The datat1 datasource is set up correctly because if I connect a grid to it I can display data. So then when I run the VB Application and press the button, I get an POL-8000 error Could not start the Java Virtual Machine.
According to the help files one must have javai.dll in your path and I have put it in my path. I removed it and it gave me the same error and then i put it back but no luck. If I change the function to a function that does not exit I get the error no such attribute or method which is the correct error message thown by oracle. So I know that VB is connecting to ODBC and ODBC to the db, but oracle lite will not start the function.
The version of teh ODBC driver that I am using is Oracle Lite ODBC 40 driver version 4;
The os is NT4 sp 6 and I here is a copy of the ODBC Log file
Project1 e8-e3 ENTER SQLFetch HSTMT 0B611A20 Project1 e8-e3 EXIT SQLFetch with return code 0 (SQL_SUCCESS) HSTMT 0B611A20 Project1 e8-e3 ENTER SQLFetch HSTMT 0B611A20 Project1 e8-e3 EXIT SQLFetch with return code 100
(SQL_NO_DATA_FOUND)
HSTMT 0B611A20 Project1 e8-e3 ENTER SQLFreeStmt HSTMT 0B611A20 UWORD 1 <SQL_DROP> Project1 e8-e3 EXIT SQLFreeStmt with return code 0
(SQL_SUCCESS)
HSTMT 0B611A20 UWORD 1 <SQL_DROP> Project1 e8-e3 ENTER SQLAllocStmt HDBC 0B612280 HSTMT * 0B951CC8 Project1 e8-e3 EXIT SQLAllocStmt with return code 0
(SQL_SUCCESS)
HDBC 0B612280 HSTMT * 0x0B951CC8 ( 0x0b611a20) Project1 e8-e3 ENTER SQLSetStmtOption HSTMT 0B611A20 UWORD 9 <SQL_ROWSET_SIZE> SQLLEN 100 Project1 e8-e3 EXIT SQLSetStmtOption with return code 0
(SQL_SUCCESS)
HSTMT 0B611A20 UWORD 9 <SQL_ROWSET_SIZE> SQLLEN 100 Project1 e8-e3 ENTER SQLPrepare HSTMT 0B611A20 UCHAR * 0x0B951B7C [ -3] "{call testfunc()}\ 0" SDWORD -3 Project1 e8-e3 EXIT SQLPrepare with return code 0
(SQL_SUCCESS)
HSTMT 0B611A20 UCHAR * 0x0B951B7C [ -3] "{call testfunc()}\ 0" SDWORD -3 Project1 e8-e3 ENTER SQLNumParams HSTMT 0B611A20 SWORD * 0x0012F518 Project1 e8-e3 EXIT SQLNumParams with return code 0
(SQL_SUCCESS)
HSTMT 0B611A20 SWORD * 0x0012F518 (0) Project1 e8-e3 ENTER SQLSetStmtOption HSTMT 0B611A20 UWORD 0 <SQL_QUERY_TIMEOUT> SQLLEN 30 Project1 e8-e3 EXIT SQLSetStmtOption with return code -1
(SQL_ERROR)
HSTMT 0B611A20 UWORD 0 <SQL_QUERY_TIMEOUT> SQLLEN 30
DIAG [S1C00] [ODBC S1C00] driver not capable of this operation (0)
Project1 e8-e3 ENTER SQLExecute HSTMT 0B611A20 Project1 e8-e3 EXIT SQLExecute with return code -1
(SQL_ERROR)
HSTMT 0B611A20
DIAG [S1000] [POL-8000] could not start the Java Virtual Machine
(-8000)
Project1 e8-e3 ENTER SQLErrorW HENV 0B6118E0 HDBC 0B612280 HSTMT 0B611A20 WCHAR * 0x0012F1BC (NYI) SDWORD * 0x0012F464 WCHAR * 0x0012EDBC SWORD 511 SWORD * 0x0012F446 Project1 e8-e3 EXIT SQLErrorW with return code 0
(SQL_SUCCESS)
HENV 0B6118E0 HDBC 0B612280 HSTMT 0B611A20 WCHAR * 0x0012F1BC (NYI) SDWORD * 0x0012F464 (-8000) WCHAR * 0x0012EDBC [ 51] "[POL-8000] could not star" SWORD 511 SWORD * 0x0012F446 (51) Project1 e8-e3 ENTER SQLErrorW HENV 0B6118E0 HDBC 0B612280 HSTMT 0B611A20 WCHAR * 0x0012F1BC (NYI) SDWORD * 0x0012F464 WCHAR * 0x0012EDBC SWORD 511 SWORD * 0x0012F446 Project1 e8-e3 EXIT SQLErrorW with return code 100
(SQL_NO_DATA_FOUND)
HENV 0B6118E0 HDBC 0B612280 HSTMT 0B611A20 WCHAR * 0x0012F1BC (NYI) SDWORD * 0x0012F464 WCHAR * 0x0012EDBC SWORD 511 SWORD * 0x0012F446 Project1 e8-e3 ENTER SQLFreeStmt HSTMT 0B611A20 UWORD 0 <SQL_CLOSE> Project1 e8-e3 EXIT SQLFreeStmt with return code 0
(SQL_SUCCESS)
HSTMT 0B611A20 UWORD 0 <SQL_CLOSE> Project1 e8-e3 ENTER SQLSetStmtOption HSTMT 0B611A20 UWORD 0 <SQL_QUERY_TIMEOUT> SQLLEN 30 Project1 e8-e3 EXIT SQLSetStmtOption with return code -1
(SQL_ERROR)
HSTMT 0B611A20 UWORD 0 <SQL_QUERY_TIMEOUT> SQLLEN 30
DIAG [S1C00] [ODBC S1C00] driver not capable of this operation (0)
Project1 e8-e3 ENTER SQLFreeStmt HSTMT 0B611A20 UWORD 1 <SQL_DROP> Project1 e8-e3 EXIT SQLFreeStmt with return code 0Received on Thu Oct 17 2002 - 11:50:19 CEST
(SQL_SUCCESS)
HSTMT 0B611A20 UWORD 1 <SQL_DROP> Project1 e8-e3 ENTER SQLDisconnect HDBC 0B612280 Project1 e8-e3 EXIT SQLDisconnect with return code 0
(SQL_SUCCESS)
HDBC 0B612280