B4J Question alfa/beta jRDC2 sqlType (Solved)

Omar Moreno · Jun 6, 2022

Hola
Estoy probando esta versión alfa/beta jRDC2 de @Oliver A.
https://www.b4x.com/android/forum/t...server-with-stored-procedures-support.123294/
En Oracle me funcionan los comandos:
• Ejecutar la solicitud
• Ejecutar comando
Pero el comando ExecuteCall me da un error cuando trato de usar el parámetro:
"sqlType": "REF_CURSOR"
En Oracle este es el procedimiento almacenado:
[CÓDIGO=sql]CREAR O REEMPLAZAR PROCEDIMIENTO pa_nomusua1(idx en VARCHAR2, rcurx OUT SYS_REFCURSOR) COMO
EMPEZAR
ABRIR rcurx PARA SELECCIONAR nombre DESDE vendedores DONDE vendedor = idx;
--dbms_sql.return_result(rcurx);
FIN;[/CÓDIGO]
El archivo config.properties tiene esta declaración:
[CÓDIGO=b4x]sql.pa_nomusua1=llamar a pa_nomusua1(?,?)[/CÓDIGO]
En el cliente, la subrutina para obtener los datos SYS_REFCURSOR es esta:

B4X:

Sub ObtenerReg3
    '
    Dim req3 como DBRequestManager = CreateRequest
    '
    Dim cmd As DBCommand = CreateCommand("pa_nomusua1", _
      Array("1",CreateMap("rcurx":"varResponse", "type":"OUT", "sqlType": "REF_CURSOR")))
    '
    Wait For (req3.ExecuteCall(cmd,0, Null)) JobDone(j3 As HttpJob)
    '
    If j3.Success Then
        Log("Call executed successfully")
        req3.HandleCallJobAsync(j3, "req3")
        Wait For (req3) req3_CallResult(resultSets As List, parameters As Map)
        'Let's print out the returned result sets. resultSets is a list containing 0 or more DBResult objects.
        For Each res3 As DBResult In resultSets
            req3.PrintTable(res3)
        Next
        'Let's print the returned OUT parameters
        Log($"Parameters map content: ${parameters}"$)
        '
    Else
        Log($"ERROR: ${j3.ErrorMessage}"$)
    End If
    '
    j3.Release
End Sub

Server error message:

ExecuteCall2: Connection supports named parameters = true
Creating CallableStatement
Setting standard parameter value of 1 for index 1
sqlTypeOrdinal = 2012
Registering out parameter index 2, with SQL type: 2012
Executing CallableStatement
CallableStatement returned one or more result sets: false
Output parameter index 2 has value of: oracle.jdbc.driver.ForwardOnlyResultSet@ba0690d
CallableStatement returned 1 OUT parameters
(RuntimeException) java.lang.RuntimeException: Cannot serialize object: oracle.jdbc.driver.ForwardOnlyResultSet@1bc4f159
Command: call: pa_nomusua1, took: 6ms, client=127.0.0.1

Error message on the client:

ResponseError. Reason: java.lang.RuntimeException: Cannot serialize object: oracle.jdbc.driver.ForwardOnlyResultSet@1bc4f159, Response: <html>
<head>
<meta http-equiv="Content-Type" content="text/html;charset=utf-8"/>
<title>Error 500 java.lang.RuntimeException: Cannot serialize object: oracle.jdbc.driver.ForwardOnlyResultSet@1bc4f159</title>
</head>
<body><h2>HTTP ERROR 500</h2>
<p>Problem accessing /rdc. Reason:
<pre> java.lang.RuntimeException: Cannot serialize object: oracle.jdbc.driver.ForwardOnlyResultSet@1bc4f159</pre></p><hr><a href="http://eclipse.org/jetty">Powered by Jetty:// 9.4.z-SNAPSHOT</a><hr/>
</body>
</html>

¿Se incluirán estas subrutinas en la versión original de B4J?

Gracias por sus respuestas.

OliverA · Jun 6, 2022

Omar Moreno said:
"sqlType": "REF_CURSOR"

I may have to add support for this type. I'll take a look at it when I get a chance

Omar Moreno · Jun 8, 2022

Hi OliverA.
In the TestHandler class of the original version of JRDC2 I have performed these tests:

TestHandler B4X:

Dim Jo As JavaObject = Me
Dim RSet As ResultSet = Jo.RunMethod("desdeJava", Array("1"))
Do While RSet.NextRow '<--- OK
Log($"Cursor.GetString(Cursor.GetColumnName(0)): ${RSet.GetString(RSet.GetColumnName(0))}"$)
Log($"Cursor.GetString2(0): ${RSet.GetString2(0)}"$)
Loop

Using java code I can get the data for the ResultSet.
If you activate the While structure then there are no rows for the ResultSet that receives the data.

TestHandler Java:

#If JAVA
import java.sql.*;
import oracle.jdbc.*;
public static Object desdeJava(String idx) throws Exception {
try {
DriverManager.registerDriver (new oracle.jdbc.OracleDriver());
Connection conn = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:xe", "user", "pass");
CallableStatement stmt = conn.prepareCall("BEGIN pa_nomusua1(?, ?); END;");
stmt.setString(1, idx); //<--- Id Usuario
stmt.registerOutParameter(2, OracleTypes.CURSOR); //<--- REF CURSOR
stmt.execute();
//*************************************************************
//ResultSet rs = ((OracleCallableStatement)stmt).getCursor(2);
//while (rs.next()) {
// System.out.println(rs.getString("nombre"));
//}
//
//rs.close();
//rs = null;
//stmt.close();
//stmt = null;
//conn.close();
//conn = null;
//*************************************************************
return ((OracleCallableStatement)stmt).getCursor(2);
}
catch (SQLException e) {
System.out.println(e.getLocalizedMessage());
return null;
}}
#End If

This way I can get the column number and its name, but the Cursor.NextRow doesn't find any rows:

TestHandler B4X:

Dim con As SQL = Main.rdcConnector1.GetConnection
Dim Cursor As ResultSet
'Dim storeprox As JavaObject = con.CreateCallStatement("BEGIN pa_nomusua1(?,?); END;", Null)
Dim storeprox As JavaObject = con.CreateCallStatement("CALL pa_nomusua1(?,?)", Null)

storeprox.RunMethod("setString", Array(1,"1"))
storeprox.RunMethod("registerOutParameter", Array(2,2012)) 'REF_CURSOR 2012
storeprox.RunMethod("execute", Null)
Cursor = storeprox.RunMethod("getObject", Array(2))
If Cursor.IsInitialized Then
Log($"Total de columnas: ${Cursor.ColumnCount}"$)
If Cursor.ColumnCount > 0 Then
Log($"Nombre de columna 0: ${Cursor.GetColumnName(0)}"$)
End If
Do While Cursor.NextRow '<--- !!!!!!
Log($"Cursor.GetString(Cursor.GetColumnName(0)): ${Cursor.GetString(Cursor.GetColumnName(0))}"$)
Log($"Cursor.GetString2(0): ${Cursor.GetString2(0)}"$)
Loop
Cursor.Close
End If
storeprox.RunMethod("close", Null)
con.Close

In this way Cursor.ColumnCount returns -1
java.sql.SQLException: Unable to fetch on a PLSQL statement: next

TestHandler B4X:

Dim con As SQL = Main.rdcConnector1.GetConnection
Dim Cursor As ResultSet
Dim storeprox As JavaObject = con.CreateCallStatement("BEGIN pa_nomusua1(?,?); END;", Null)
'Dim storeprox As JavaObject = con.CreateCallStatement("CALL pa_nomusua1(?,?)", Null)
storeprox.RunMethod("setString", Array(1,"1"))
storeprox.RunMethod("registerOutParameter", Array(2,2012)) 'REF_CURSOR 2012
Cursor = con.ExecCall(storeprox)
If Cursor.IsInitialized Then
Log($"Total de columnas: ${Cursor.ColumnCount}"$)
If Cursor.ColumnCount > 0 Then
Log($"Nombre de columna 0: ${Cursor.GetColumnName(0)}"$)
End If
Do While Cursor.NextRow '<--- !!!!!!
Log(Cursor.GetString2(0))
Loop
Cursor.Close
End If
'
con.Close

I hope I can give you clues.
Greetings.

OliverA · Jun 8, 2022

Omar Moreno said:
OracleTypes.CURSOR

Out of the box, the modded JRDC2 only supports JDBC data types. I pm'd you with an updated version that includes Oracle JDBC types. Please test and report back.

Omar Moreno · Jun 14, 2022

Solucionado el envio de REF_CURSOR con JSON
Gracias a la ayuda de OliverA

B4J Question alfa/beta jRDC2 sqlType (Solved)

Omar Moreno

Member

OliverA

Expert

Omar Moreno

Member

OliverA

Expert

Omar Moreno

Member

Similar Threads