I am trying to import Oracle data into my OfficeBase data base. My goal is to open a record which connects directly to oracle like this:
Hello,
Your process may be the long way around if you have little or no experience with LO macros. It may be better to dump the data from the Oracle DB and import into the database you are using (not stated as Base is NOT a database). HSQLDB embedded is not recommended.
As for direction with your request, cannot give specifics as this depends entirely on the actual connection and data to be moved around. Do not have Oracle access currently but did test some time back with a resulting problem - slow initial access ( around 4 minutes). See this post → base to oracle database connection slow. Also, see bug report noted in comments.
Did not use connection in basic for Oracle but have for other databases. See → How do I connect to SQL server in a macro in Windows. Toward the end of my answer is a link to the Pitonyak document which contains connection information and at the end another link to the use of that code for a MSSQL connection (ALL DB connections are similar).
For SQL, some samples can be found here → In base Convert Query to macro with a few links in the answer pointing to related information.
Edit:
If you are looking for specifics to the actual URL string, only you have that information. That depends upon the connector used (your code shows JDBC but original question never stated) and the location and port of the database. For explanation (and how I did my original testing) refer to Connecting to Oracle Database. This may also be of help → JDBC Driver Connection URL strings.
Can provide the driver class name I used for JDBC - oracle.jdbc.driver.OracleDriver
Well I have something at least partially going using PostGres:
Sub Main
Dim oParms() As New com.sun.star.beans.PropertyValue
sUser$ = "uid"
sPass$ = "pwd"
sURL$ = "jdbc:postgresql://localhost:5432/postgres"
oManager = CreateUnoService("com.sun.star.sdbc.DriverManager")
AppendProperty(oParms(), "user", sUser)
AppendProperty(oParms(), "password", sPass)
AppendProperty(oParms(), "JavaDriverClass", "org.postgresql.Driver")
oCon = oManager.getConnectionWithInfo(sURL, oParms())
oStatement = oCon.CreateStatement()
sSQL = "select * from table order by id desc"
oResult = oStatement.executeQuery(sSQL)
Do While oResult.next()
s = oResult.getString(3)
print s
Loop
oCon.close()
End Sub
Sub AppendProperty(oProperties(), sName As String, ByVal oValue)
AppendToArray(oProperties(), CreateProperty(sName, oValue))
End Sub
Sub AppendToArray(oData(), ByVal x)
Dim iUB As Integer 'The upper bound of the array.
Dim iLB As Integer 'The lower bound of the array.
iUB = UBound(oData()) + 1
iLB = LBound(oData())
ReDim Preserve oData(iLB To iUB)
oData(iUB) = x
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 FunctionHello,
Not certain why this post. Please specify. What exactly are you looking for?
It does not answer the original question and should not be posted as an answer. Please delete and re-post as edit to original question. This should have been part of the original question.
Hello,
My question was
use Basic to connect to the oracle database using a proper connection string. Which is:
sUser$ = “uid”
sPass$ = “pwd”
***sURL$ = "jdbc:postgresql://localhost:5432/postgres"***
oManager = CreateUnoService("com.sun.star.sdbc.DriverManager")
AppendProperty(oParms(), "user", sUser)
AppendProperty(oParms(), "password", sPass)
AppendProperty(oParms(), "JavaDriverClass", "org.postgresql.Driver")
oCon = oManager.getConnectionWithInfo(sURL, oParms())
you see the connection string.
Which is
oStatement = oCon.CreateStatement()
sSQL = "select * from table order by id desc"
oResult = oStatement.executeQuery(sSQL)
Do While oResult.next()
s = oResult.getString(3)
print s
Loop
The only difference is the connection string for oracle.
Hello,
Have asked that this be re-posted. Have also answered this comment about the connection string in my edited answer. The information for SQL was provided in the original answer in a link and cannot give any specifics as only generalities are provided. Would need table, field and type information. Where it is coming from and going to and maybe much more.