B4J Question ResultSet MetaData Oracle

[XbNnX_507]

Hi, i'm getting an extrange error here.

    CallableStatement.RunMethod("registerOutParameter", Array( CursorIndex, OracleTypes.GetField("CURSOR")) )
    CallableStatement.RunMethod("execute", Null) 
    Dim rs As ResultSet = CallableStatement.RunMethod("getCursor", Array(CursorIndex)) '
    Dim jrs As JavaObject = rs
    Dim rsmd As JavaObject = jrs.RunMethod("getMetaData", Null)   ' <- error here

Waiting for debugger to connect...
Program started.
Error occurred on line: 61 (Main)
java.lang.RuntimeException: Method: getMetaData not found in: oracle.jdbc.driver.ForwardOnlyResultSet
    at anywheresoftware.b4j.object.JavaObject$MethodCache.getMethod(JavaObject.java:363)
    at anywheresoftware.b4j.object.JavaObject.RunMethod(JavaObject.java:120)
    at b4j.example.main._procedurequery(main.java:194)
    at b4j.example.main._appstart(main.java:111)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:498)
    at anywheresoftware.b4a.shell.Shell.runMethod(Shell.java:632)
    at anywheresoftware.b4a.shell.Shell.raiseEventImpl(Shell.java:237)
    at anywheresoftware.b4a.shell.Shell.raiseEvent(Shell.java:167)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:498)
    at anywheresoftware.b4a.BA.raiseEvent2(BA.java:91)
    at anywheresoftware.b4a.shell.ShellBA.raiseEvent2(ShellBA.java:98)
    at anywheresoftware.b4a.BA.raiseEvent(BA.java:78)
    at b4j.example.main.start(main.java:38)
    at com.sun.javafx.application.LauncherImpl.lambda$launchApplication1$166(LauncherImpl.java:863)
    at com.sun.javafx.application.PlatformImpl.lambda$runAndWait$179(PlatformImpl.java:326)
    at com.sun.javafx.application.PlatformImpl.lambda$null$177(PlatformImpl.java:295)
    at java.security.AccessController.doPrivileged(Native Method)
    at com.sun.javafx.application.PlatformImpl.lambda$runLater$178(PlatformImpl.java:294)
    at com.sun.glass.ui.InvokeLaterDispatcher$Future.run(InvokeLaterDispatcher.java:95)
    at com.sun.glass.ui.win.WinApplication._runLoop(Native Method)
    at com.sun.glass.ui.win.WinApplication.lambda$null$152(WinApplication.java:177)
    at java.lang.Thread.run(Thread.java:748)

Isn't this method supposed to return the resulset metadata?

 

[DonManfred]

Isn't this method supposed to return the resulset metadata?

the cursor does not have such a method based on the error.

getMetaData not found in: oracle.jdbc.driver.ForwardOnlyResultSet

 

[XbNnX_507]

the cursor does not have such a method based on the error.

what about the java resultset ?

 Dim rs As ResultSet = CallableStatement.RunMethod("getCursor", Array(CursorIndex)) ' <- this is doing the casting

 

[DonManfred]

no idea. Check the documentation. i never worked with stored procedures.
I just interpreted the Error

 

[XbNnX_507]

Ok thanks. @DonManfred .

Maybe someone else have other idea?

 

[Erel]

Are you using B4J v7.3?

 

[XbNnX_507]

Are you using B4J v7.3?

Yes. Java JDK 1.8.211

 

[OliverA]

It may not be possible. According to the answer to a question posted here: https://community.oracle.com/thread/196300https://community.oracle.com/thread/196300, the cursor returned by your stored procedure is a oracle.jdbc.driver.ForwardOnlyResultSet and that object seems to be limited on the methods that are available (a limited subset of a "regular" result set object).

 

[Erel]

getMetaData is part of ResultSet interface. It should be there. It might be a different issue related to the way the driver is implemented. Worth try to access this method with jReflector.

 

[OliverA]

Looks like the standard prepareCall method that the SQL library is using will only return a ForwardOnlyResultSet. That object's interface is limited (other methods do not work, but with a different error messsage). There is another prepareCall method whose signature is

CallableStatement prepareCall(String sql, int resultSetType, int resultSetConcurrency)

Where resultSetType can be

ResultSet.TYPE_FORWARD_ONLY
ResultSet.TYPE_SCROLL_INSENSITIVE
or ResultSet.TYPE_SCROLL_SENSITIVE

and sesultSetConcurrency can be

ResultSet.CONCUR_READ_ONLY
or ResultSet.CONCUR_UPDATABLE

For the standard prepareCall, the resultSetType is ResultSet.TYPE_FORWARD_ONLY and resultSetConcurrency is ResultSet.CONCUR_READ_ONLY. You'll need to do your own prepareCall with resultSetType set to either ResultSet.TYPE_SCROLL_INSENSITIVE or ResultSet.TYPE_SCROLL_SENSITIVE. In order to do you own call, you'll need to get a hold of the connection object of the SQL object.

Edit: Forgot sources and looks like there are some limitations that you may run into when using the prepareCall mentioned above.
Sources:
Limitations of ForwardOnlyResultSet:
https://www.javamadesoeasy.com/2015/12/solve-javasqlsqlexception-invalid.html
https://coderanch.com/t/487347/databases/Invalid-operation-resultset-beforeFirst
Info on prepareCall:
https://docs.oracle.com/cd/A97335_02/apps.102/a83724/resltse2.htm
Please read "Result Set Limitations and Downgrade Rules" section!

 

[XbNnX_507]

Well after trying everything with my little to non existent java knowledge
( that's why i use b4j lol )...
i did this...

Dim JO As JavaObject = Me
Dim rsmd As JavaObject =    JO.RunMethod("getMeta", Array ( rs ) ) '
#if java
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.ResultSetMetaData;
 
 public ResultSetMetaData  getMeta(ResultSet rs) throws SQLException {
    ResultSetMetaData rsmd = rs.getMetaData();
    return rsmd;
  }
#End If

From there i can do

rsmd.RunMethod("getColumnType", Array(index))

and it works.

Worth try to access this method with jReflector.

could be easier with jReflector but me and java not so well...

For the standard prepareCall, the resultSetType is ResultSet.TYPE_FORWARD_ONLY and resultSetConcurrency is ResultSet.CONCUR_READ_ONLY.

Possibly not related to the resulset type itself but the metadata type ( oracle ).


thanks @OliverA , @Erel

 

[OliverA]

and it works.

public ResultSetMetaData getMeta(ResultSet rs) throws SQLException {

Looks like it was just a casting issue. By calling your inline java method, you're recasting rs as a ResultSet object, which allowed you to call the getMetaData method. Good catch!