Be gentle. My coding life began doing operations research programming in Fortran.
I have a form in a macro and wish to use info from a user to send back a listbox of items while in calc.
My stumbling block is the connect method to SQL via ODBC. The DB is registered in base via ODBC but I have no clue as to the methods available to do this in the macro. Pointing me to to the proper OO documention or an example would be appreciated. benttriker.
Hello,
There are a few methods to connect to a database from Calc.
You have stated that you have a DB already registered. The connection to the base file is easy using the registered name.
Sub ConnectDB
Dim oContext As Object
Dim oDB As Object
Dim oCon As Object
oContext = CreateUnoService("com.sun.star.sdb.DatabaseContext")
oDB = oContext.getByName("Registered_Name_Here")
oCon = oDB.getConnection("User_Name_Here","Password_Here")
End Sub
oCon is the connection you can use to perform your SQL with such as:
oStatement = oCon.CreateStatement()
sSQL = "SELECT NAME FROM MYTABLE"
oResult = oStatement.executeQuery(sSQL)
Do While oResult.next()
Print oResult.getString(1)
Loop
If you want to bypass the Base file and connect directly to the Database, then the basic directions can be found in the AndrewBase document found here → Andrew Pitonyak Documents. Look at page 106 - 8.6.6. Paradox using ODBC. This was the basis for my answer on this post → Connect to mssql (ODBC) database via Macro.
Thanks for the pithy, correct and gentle answer. Also the link to the Pitonyak docs is greatly appreciated. As per the proverb, better to be taught to fish than given a fish. Hope this may also be helpful to others. benttriker