Problem with insert into returning with oracle db

[ztminhas]

Good Morning
I am having a problem with trying to get a returned value for some test data. Here is my code for making the table:

CREATE TABLE TABLE1 
(
  ID NUMBER(*, 0) GENERATED ALWAYS AS IDENTITY INCREMENT BY 1 MAXVALUE 9999999999999999999999999999 MINVALUE 1 CACHE 20 NOT NULL 
, NAME VARCHAR2(20 BYTE) NOT NULL 
, CONSTRAINT TABLE1_PK PRIMARY KEY (ID )
)

Here is my Basic code to insert and hopefully return some the id key column:

Option Explicit
Sub main 
Dim stmt As Object
Dim resultset As object
	oraCon =makeOracleODBCConnection("TESTDSN")
	Set stmt= oraCon.prepareCall("INSERT INTO TABLE1 (NAME) VALUES (?) RETURNING ID INTO ?" )
	stmt.setString(1, "FREDO")
	stmt.registerOutParameter(2, com.sun.star.sdbc.DataType.INTEGER,-1)
	stmt.executeUpdate()
	While resultSet.Next
		Print stmt.getInt(1)
	wend
	closeDBObject(resultSet)
	closeDBObject(stmt)
	closeDBObject(oraCon)
End Sub
function makeOracleODBCConnection(src As String) As com.sun.star.sdbc.XConnection
	sUser$ = "MY_USERNAME"
	sPass$ = "MY_PASSWORD"
	sURL$ ="sdbc:ado:DSN="& src & ";Uid="& sUser$ &";PWD=" & sPass$
	makeOracleODBCConnection= connectionManager(sURL) 
End Function
Function connectionManager(sURL As String) As com.sun.star.sdbc.XConnection
	oManager = CreateUnoService("com.sun.star.sdbc.DriverManager")
	connectionManager= oManager.getConnection(sURL) 
End Function

Sub closeDBObject(dbObject As Object)
	dbObject.close()
	dbObject = Nothing
End Sub

I get the error message:

BASIC runtime error.
Property or method not found: registerOutParameter.

Thank you for all of your help.

[RobertG]

With PostgreSQL this code will work:

stSql = "INSERT INTO ""Test1"" (""Fieldname"") Values('Ne Content') RETURNING ""ID"""
oResult = oSQL_Statement.executeQuery(stSql)
oResult.Next
loID = oResult.getLong(1)

Note: Only executeQuery will get a result, but will also execute the Insert command. If you start executeUpdate you won’t get a result.

[ztminhas]

I modified my code:

Sub Main
     oraCon = makeOracleODBCConnection( "TESTDSN")
     stmt= oraCon.prepareCall(INSERT INTO TABLE1 (NAME) VALUES (?) RETURNING D INTO :ID)
     stmt.setString(1,FREDO)
     stmt.registerOutParameter(2, com.sun.star.sdbc.DataType.INTEGER, -1)
     rs = stmt.executeQuery()
     print rs.getInt(2)
End Sub

Still get the same error.

BASIC runtime error.
Property or method not found: registerOutParameter.

I made a variation of the code:

Sub Main
REM CHANGED FROM USING ODBC WHICH RELIES ON SDBC:ADO TO USING JDBC**
	oraCon = makeOracleJDBCConnection( "TESTDSN")
	stmt= oraCon.prepareCall("INSERT INTO TABLE1 (NAME) VALUES (?) RETURNING ID INTO ?")
	stmt.setString(1,"FREDO")
	stmt.registerOutParameter(2, com.sun.star.sdbc.DataType.INTEGER, -1)
	rs = stmt.executeQuery()
	print rs.getInt(2)
End Sub

And then i got this result:

BASIC runtime error.
An exception occurred 
Type: com.sun.star.sdbc.SQLException
Message: Not all return parameters registered.

[RobertG]

Don’t know anything about special code of Oracle. In PostgreSQL you won’t need “INTO ?” and you won’t need registerOutParameter. You only have to execute the query and will get the first value if you move the cursor to Next.
Might be

rs = stmt.executeQuery()
rs.NEXT
print rs.getInt(1)

Only one result is given - so the returning value is the first and only value, which could be read out by the query.

[ztminhas]

I modified my SQL code to be:

"BEGIN INSERT INTO TABLE1 (NAME) VALUES ('FREDO') RETURNING ID INTO ?; END;"

The resulting Basic code is now:

Sub Main
	oraCon = makeOracleJDBCConnection( "TESTDSN")
	stmt= oraCon.prepareCall("BEGIN INSERT INTO TABLE1 (NAME) VALUES (?) RETURNING ID INTO ?; END;")
	stmt.setString(1,"FREDO")
	stmt.registerOutParameter(2, com.sun.star.sdbc.DataType.INTEGER, -1)
	rs = stmt.executeQuery()
	print rs.getInt(2)
End Sub

This time it actually does an insert, the output parameter seems to be registered, however it does not return any resultset. The error I get is:

BASIC runtime error.
An exception occurred 
Type: com.sun.star.sdbc.SQLException
Message: Invalid column index.

[ztminhas]

Wow just a bit of modifcations and I finally got it. Here is the proper code:

Sub Main
	oraCon = makeOracleJDBCConnection( "TESTDSN")
	stmt= oraCon.prepareCall("BEGIN INSERT INTO TABLE1 (NAME) VALUES (?) RETURNING ID INTO ?; END;")
	stmt.setString(1,"FREDO")
	stmt.registerOutParameter(2, com.sun.star.sdbc.DataType.INTEGER, -1)
	stmt.executeUpdate()
	print stmt.getInt(2)
End Sub

It does not return a resultset. It returns the value of the ID column from the returning clause. Work continues.