libeoffice conected to oracle registeroutparameter to return resultset

Question

Good Morning,

I have the following code:

Dim oraCon As Object

Sub Main
    openOracle("DB_LOC")    
    stmt  = oraCon.prepareCall("VARIABLE x REFCURSOR DECLARE V_Sqlstatement Varchar2(2000); BEGIN V_Sqlstatement:= 'SELECT * FROM DUAL'; OPEN x for v_Sqlstatement; ?:=x End;")
REM Whate do i put here?
    stmt.registerOutParameter(1, ????,????)
    rs=stmt.executeQuery()
End Sub

I am trying to find out what I should put in the registerOutParameter

Answer 1

The call expects XOutParameters, where the 2nd parameter is a DataType constant.

A complete example is given by user rodolfo at https://forum.openoffice.org/en/forum/viewtopic.php?t=41149.

sFormat = "minor"
oStmt = oConnection.prepareCall("CALL test_proc(?,?,?)")
' Input parameter binding is exactly the same as for prepared statements '
oStmt.setString(1, sFormat)

' Parameter 2, because it is the position of the question mark that is relevant. '
' For counting of the position all parameters (in, out) are equal '
oStmt.registerOutParameter(2, com.sun.star.sdbc.DataType.VARCHAR, "ignored")

' The final scale parameter says that we expect 2 digits after the decimal separator '
oStmt.registerNumericOutParameter(3, com.sun.star.sdbc.DataType.DECIMAL, 2)

oStmt.execute()
' NOTE: The .getXXX() methods are methods of the statement object and not of '
'       the result set! '
MsgBox "Mode: " & sFormat & " -- Version: " & oStmt.getString(2) _
      & chr(10) & "Numerical: " & oStmt.getString(3)

oStmt.close()

EDIT:

Multiple results are explained at https://api.libreoffice.org/docs/idl/ref/interfacecom_1_1sun_1_1star_1_1sdbc_1_1XStatement.html#ace0d6b1f30106a88de49a775ab275436:

execute() boolean execute ( [in] string sql )
raises ( SQLException )
executes a SQL statement that may return multiple results.

Under some (uncommon) situations a single SQL statement may return multiple result sets and/or update counts. Normally you can ignore this unless you are (1) executing a stored procedure that you know may return multiple results or (2) you are dynamically executing an unknown SQL string. The navigation through multiple results is covered by com::sun::star::sdbc::XMultipleResults.

The execute method executes a SQL statement and indicates the form of the first result. You can then use com::sun::star::sdbc::XMultipleResults::getResultSet() or com::sun::star::sdbc::XMultipleResults::getUpdateCount() to retrieve the result, and com::sun::star::sdbc::XMultipleResults::getMoreResults() to move to any subsequent result(s).

Parameters sql any SQL statement Returns TRUE if the next result is a ResultSet; FALSE if it is an update count or there are no more results Exceptions SQLException if a database access error occurs.