Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Calling Java stored procedures from VB HELP!

Calling Java stored procedures from VB HELP!

From: Jason Nicholls <Jason.Nicholls_at_BAESystems-ifs-nm.com>
Date: Thu, 17 Oct 2002 10:18:36 +0100
Message-ID: <3dae7e91@post.usenet.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 0 (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







-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=

Received on Thu Oct 17 2002 - 04:18:36 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US