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.
[Solved] How to open a recordset in a Basic script
[Solved] How to open a recordset in a Basic script
Last edited by Hypno on Mon May 22, 2023 7:43 am, edited 1 time in total.
LibreOffice 7.2.2.1 (x64), Windows 10
Re: How to open a recordset in a Basic script
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
-
- Volunteer
- Posts: 1549
- Joined: Wed Jun 24, 2015 12:56 am
- Location: Colorado, USA
Re: How to open a recordset in a Basic script
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
If your problem has been solved, please edit this topic's initial post and add "[Solved]" to the beginning of the subject line
Apache OpenOffice 4.1.14 & LibreOffice 7.6.2.1 (x86_64) - Windows 10 Professional- Windows 11
Apache OpenOffice 4.1.14 & LibreOffice 7.6.2.1 (x86_64) - Windows 10 Professional- Windows 11
Re: How to open a recordset in a Basic script
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.
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.
LibreOffice 7.2.2.1 (x64), Windows 10
-
- Volunteer
- Posts: 1549
- Joined: Wed Jun 24, 2015 12:56 am
- Location: Colorado, USA
Re: [Solved] How to open a recordset in a Basic script
is it possible to do this without registering?
Code: Select all
DB=ConvertToURL("Full Path and Name of Database")
Stmt=createStatement(DB)
If your problem has been solved, please edit this topic's initial post and add "[Solved]" to the beginning of the subject line
Apache OpenOffice 4.1.14 & LibreOffice 7.6.2.1 (x86_64) - Windows 10 Professional- Windows 11
Apache OpenOffice 4.1.14 & LibreOffice 7.6.2.1 (x86_64) - Windows 10 Professional- Windows 11
Re: [Solved] How to open a recordset in a Basic script
Isn't there some construct like "currenntDB"?
LibreOffice 7.2.2.1 (x64), Windows 10
Re: [Solved] How to open a recordset in a Basic script
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
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
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
-
- Volunteer
- Posts: 1549
- Joined: Wed Jun 24, 2015 12:56 am
- Location: Colorado, USA
Re: [Solved] How to open a recordset in a Basic script
Not built in but this function reportedly will do much the same.Isn't there some construct like "currenntDB"?
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
If your problem has been solved, please edit this topic's initial post and add "[Solved]" to the beginning of the subject line
Apache OpenOffice 4.1.14 & LibreOffice 7.6.2.1 (x86_64) - Windows 10 Professional- Windows 11
Apache OpenOffice 4.1.14 & LibreOffice 7.6.2.1 (x86_64) - Windows 10 Professional- Windows 11