Page 1 of 1
[Solved] How to open a recordset in a Basic script
Posted: Thu May 18, 2023 7:56 am
by Hypno
Forgive my question but I can't find any example to solve my problem, it's probably not complicated but for someone for whom English is not a native language it's not easy.
I would like to parse data from tables recursively using a Basic script (too complicated to do with SQL). For this, I need to open several sets of records, one of which is read-write (directly a specific table) and the other may be read-only (here it can be either a table or some simple SQL query). None of these recordset objects are associated with a form or subform. Of course, it's about tables or SQL queries from the current database. And macro support also in the current database, of course.
I will be grateful for some simple example in Basic how to open such objects in the current database. I'm good at programming in general, but working with databases at the scripting level in OO is new to me. I mean just opening the tables (SQL queries) as objects.
Re: How to open a recordset in a Basic script
Posted: Thu May 18, 2023 10:14 am
by Villeroy
Re: How to open a recordset in a Basic script
Posted: Sat May 20, 2023 4:25 am
by UnklDonald418
Here is an example of opening a Registered Base database (*.odb) and retrieving a result set from it using Basic
Code: Select all
Sub SimpleConnection()
Dim Context
Dim DB
Dim Conn
Dim Stmt
Dim Result
Dim strSQL As String
REM create database context
Context=CreateUnoService("com.sun.star.sdb.DatabaseContext")
REM get the database
DB=Context.getByName("YourRegistered DatabaseName")
REM establish connection to database
Conn=DB.getConnection("","") 'if needed username and password in the double quotes
REM create a statement service
Stmt=Conn.createStatement()
REM create an SQL command
strSQL="SELECT * FROM SOME_TABLE_NAME"
REM finally, execute and store results in ResultSet
Result=Stmt.executeQuery(strSQL)
While Result.next() 'step through the result set
REM Result object is a single row/record and contains the data for each field in that row.
REM You can inspect the contents of the Result object with the MRI tool, the XRay Tool or the Base IDE
Wend
Conn.close()
End Sub
Re: How to open a recordset in a Basic script
Posted: Mon May 22, 2023 7:42 am
by Hypno
Thank you for the information. Just what I was looking for.
I already figured it out but thanks for the reply. It turned out that I was doing everything right but... I didn't register the database before. So I wasted my time just discovering that the database must be registered.
Alternatively, I have a question, is it possible to do this without registering? If you can provide a few lines of code, I'd appreciate it. A few (over a dozen) years ago I worked a lot with Access and VBA and now it's very hard for me to switch to OO.
Re: [Solved] How to open a recordset in a Basic script
Posted: Mon May 22, 2023 8:11 pm
by UnklDonald418
is it possible to do this without registering?
Code: Select all
DB=ConvertToURL("Full Path and Name of Database")
Stmt=createStatement(DB)
Re: [Solved] How to open a recordset in a Basic script
Posted: Tue May 23, 2023 7:17 am
by Hypno
Isn't there some construct like "currenntDB"?
Re: [Solved] How to open a recordset in a Basic script
Posted: Tue May 23, 2023 3:54 pm
by Villeroy
We don't know all that shit by heart. We look it up.
Install the
MRI extension and restart the office.
Open your database and call menu:Tools>AddOns>MRI
Double-click "CurrentController" on tab "Properties"
Double-click "connect" on tab "Methods"
Double-click "ActiveConnection" on tab "Properties"
Hit Ctrl+H
viewtopic.php?t=49294
Re: [Solved] How to open a recordset in a Basic script
Posted: Tue May 23, 2023 10:21 pm
by UnklDonald418
Isn't there some construct like "currenntDB"?
Not built in but this function reportedly will do much the same.
Code: Select all
Function ThisConnection2() As Object
REM GET ACTIVE CONNECTION OF THE FIRST FORM - Similar to MS Access CurrentDB()
REM usage Conn = ThisConnection2()
On Error Goto HandleErr
Dim Forms As Object
Dim FirstForm As Object
Dim Doc As Object
Doc=ThisComponent
If Doc.ImplementationName<>"SwXTextDocument" Then REM not a writer form
Exit Function
End If
Forms=Doc.DrawPage.Forms
If Forms.Count<1 Then REM NO FORMS.
Exit Function
End If
ThisConnection2=Forms.getByIndex(0).ActiveConnection
HandleErr:
Exit Function
End Function