Problem with insert into returning with oracle db - followup

[ztminhas]

Good Morning,

This is a continuation of the problem I had described before at Problem with insert into returning with oracle db - English - Ask LibreOffice.

My solution to this problem was to make a connection with Oracle’s OJDBC driver. However I would like to do the same thing with LibreOffice’s SDBC-ODBC driver. The code is at the link mentioned above.

To summarize: in order for me to solve the insert into returning problem I used JDBC driver. However I would like to accomplish the same task using LO’s SDBC-ODBC driver. If I need to modify my SQL to accomplish this task I am willing to do so. I appreciate all suggestions. Thank you.

Regards

Zafar

[Ratslinger]

@ztminhas
A few years ago I tried some testing with Oracle 11g XE and was not happy. JDBC was slow (see → Base to oracle database connection slow - #4 by Ratslinger) and could not get Base to work with ODBC. There is also a link in the comments to a bug report.

Don’t believe I retained any of the installed Oracle DB elements on my OS, but may check later for the heck of it.

I realize this is of no help to you but thought I’d present my experience with this.

[ztminhas]

I am coming to the conclusion that JDBC is the way to go for most DB connections.

[Ratslinger]

I do not necessarily agree with that. Have been successful with PostgreSQL (my personal preference), MySQL and SqLite in using ODBC. This accommodates those wanting to avoid Java. Firebird server did sort of connect in the past but now I get no connection at all.
.
My preference is to use a native connector, SBDC, but will use either JDBC or ODBC also.

[ztminhas]

Agreed. Just to beat a dead horse I installed Oracle 19 personal version. And made connection via SDBC to Oracle 19 ODBC. Still no way to make the prepareCall method work. Using preparedStatement does not return any value. When I executeupdate the preparedStatement I do see a Cursor Name appear however no resultset is available.

[ztminhas]

Good Evening,
Finally got it:

Sub Main
	GlobalScope.BasicLibraries.loadLibrary("ScriptForge")
	oraCon = getOdbcDSNConnection("ORCL")
	stmt = oraCon.createStatement()
	stmt=oraCon.prepareStatement("DECLARE DAID INTEGER; CUR1 sys_refcursor; BEGIN INSERT INTO TABLE1(NAME) VALUES(?) RETURNING ID INTO DAID; OPEN CUR1 FOR SELECT DAID  FROM DUAL; dbms_sql.return_result(CUR1); END;")
	stmt.setString(1, "SAMPLE NAME")
	rs = stmt.executeQuery()
	rs.next
	print rs.getInt(1)
	rs.Close
	Set rs=Nothing
	stmt.Close
	stmt=Nothing
End Sub
Function getOdbcDSNConnection(dsn As String) As Object
Dim oParms() As New com.sun.star.beans.PropertyValue
		sURL="sdbc:odbc:" & dsn' & ";UID=UUUUUU;PWD=PPPPPP"
	oManager = CreateUnoService("com.sun.star.sdbc.DriverManager")
	AppendProperty(oParms(), "user", "UUUUUU")
	AppendProperty(oParms(), "password","PPPPPP")
	getOdbcDSNConnection = oManager.getConnectionWithInfo(sURL, oParms())
End Function

Sub AppendProperty(oProperties(), sName As String, ByVal oValue)
	oProperties=SF_Array.Append(oProperties(), CreateProperty(sName, oValue))
End Sub

Function CreateProperty(sName$, oValue) As com.sun.star.beans.PropertyValue
Dim oProperty As New com.sun.star.beans.PropertyValue
	oProperty.Name = sName
	oProperty.Value = oValue
	CreateProperty() = oProperty
End Function

[Ratslinger]

@ztminhas
Glad it works for you but for me it is of no help at all.
.
I have this all set in Linux with an older version of LO (no ScriptForge which to me is a waste anyway).
My first issue is to connect Base with Oracle 11g XE (what I have installed) using ODBC - tried many ways but no success. Have connected (noted in my link) with Oracle ODBC but not to Base.
.
Well, not important to me anyway. Throw it on the shelf for another couple of years!