[Solved] How to open a recordset in a Basic script

[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.

[Villeroy]

https://www.pitonyak.org/database/

[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

[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.

[UnklDonald418]

is it possible to do this without registering?

Code: Select all

DB=ConvertToURL("Full Path and Name of Database")
Stmt=createStatement(DB)

[Hypno]

Isn't there some construct like "currenntDB"?

[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

[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