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

Home -> Community -> Usenet -> c.d.o.misc -> 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