How to link to an exsisting Query in Split HSQLDB, opening it and using it with User input with a Macro

dreamquartz1 · July 7, 2026, 8:32pm

So far no clear indicator on how to link to a saved Query in a split HSQLDB. It appears that the Query needs to be copied into the Macro, which is not really possible, because it is large, with different User options for data input.
My goal is to create a csv-format file to be used to import data into a MS based software program. I am running Ubuntu, and the program is not available in Ubuntu. It is fully propriatory.

Can someone shed some light to it

Dream

CRDF · July 7, 2026, 10:07pm

Did you set up a variable pointing to that query🤔

Dim QThatQuery As Object
[...]
QThatQuery = oQueries.getByName("ThatQuery")
[...]

Please show your code.

dreamquartz1 · July 8, 2026, 12:36am

Sub aQueryContent
Dim oDatabaseFile As Object
Dim oQuery As Object
Dim stQuery As String

oDatabaseFile = ThisComponent.Parent.CurrentController.DataSource
oQuery = oDatabaseFile.getQueryDefinitions()
stQuery = oQuery.getByName("Query").Command
MsgBox stQuery
End Sub

See above.
I can therefore now show the content of the Query, but cannot execute it @ this point.

I know I have to use something like: stResultSet = ?.executeQuery(stQuery), but cannot figure it out.
I also have no idea @ this point in time on how to connect to the DataSource from outside the DataBase.

Dream

RobertG · July 8, 2026, 6:13am

… and now:

oConnection = oDatabaseFile.ActiveConnection()
oSQL_Statement = oConnection.createStatement()
oResult = oSQL_Statement.executeQuery(stQuery)
WHILE oResult.next
   stText = oResult.getString(1)
...(and this for all fields)
WEND

Or do you want to open the query in query view?

SUB OpenTable(oEvent AS OBJECT)
	DIM oController AS OBJECT
	DIM oConnection AS OBJECT
	oField = oEvent.Source.Model
	stName = oField.Tag
	oController = ThisDatabasedocument.CurrentController
	IF NOT oController.isconnected THEN oController.connect
	oConnection = oController.ActiveConnection
	DIM URL AS NEW com.sun.star.util.URL
	DIM Args(5) AS NEW com.sun.star.beans.PropertyValue
	URL.Complete = ".component:DB/DataSourceBrowser"
	Dispatch = StarDesktop.queryDispatch(URL,"_Blank",8)
	Args(0).Name = "ActiveConnection"
	Args(0).Value = oConnection
	Args(1).Name = "CommandType"
	Args(1).Value = 0 '0=Table 1=SQL_Query 2=Command
	Args(2).Name = "Command"
	Args(2).Value = stName
	Args(3).Name = "ShowMenu"
	Args(3).Value = True
	Args(4).Name = "ShowTreeView"
	Args(4).Value = False
	Args(5).Name = "ShowTreeViewButton"
	Args(5).Value = False
	Dispatch.dispatch(URL, Args)
END SUB

Name of a table is saved here in tag of a button, which should start to open the table. Should also work for queries, see comment for “CommandType”.

dreamquartz1 · July 8, 2026, 6:41pm

Sub OpenBaseShowQueryRun
Dim oConnection As Object
Dim oDatabaseContext As Object
Dim oDatabaseFile As Object
REM Dim oDataSource As Object
Dim oInteractionHandler as Object
Dim oQuery As Object
Dim oResult As Object
Dim oSQL_Statement As Object

Dim stQuery As String

oDatabaseContext = createUnoService("com.sun.star.sdb.DatabaseContext")
oDatabaseFile = oDatabaseContext.getByName("DataBase")

If Not oDatabaseFile.IsPasswordRequired Then
  oConnection = oDatabaseFile.GetConnection("","")
Else
  oInteractionHandler = createUnoService("com.sun.star.sdb.InteractionHandler")
  oConnection = oDatabaseFile.ConnectWithCompletion(oInteractionHandler)
  End If
 oQuery = oDatabaseFile.getQueryDefinitions()
stQuery = oQuery.getByName("Query").Command
REM MsgBox stQuery
REM oConnection = oDatabaseFile.ActiveConnection()
oSQL_Statement = oConnection.createStatement()
oResult = oSQL_Statement.executeQuery(stQuery)
End Sub

This throws the error on oResult = oSQL_Statement.executeQuery(stQuery)

BASIC runtime error.
An exception has occurred
Type: co.sun.start.sdbc.SQLException
Message: parameter marker not allowed.

Could not find any info on it.
There are some REMs placed, due to the suspicion that they are not needed.

Leaving out ‘oSQL_Statement’, ‘oResult’, and remove ‘REM’ for ‘MsgBox’, the Macro does connect to the DataBase and again will show the content of the Query.

For your 2nd suggestion, I have to check further, but want to run the query and have the User provide input.

Dream

RobertG · July 8, 2026, 7:04pm

Seems there is a parameter in the query (like :parameter). The query couldn’t work with parameter in SQL.
.
Try to set the content for the parameter trough a table. I’m not using any parameter in all of my databases. Using “tbl_Filter” instead and reading the entry for one row of this table instead of parameters.

CRDF · July 8, 2026, 7:14pm

Parameter markers are for Prepared Statements.

Ps = Con.prepareStatement(sSQL)
' set the values - first index = 1 (first ?):
Ps.setString(1, "The field data")
' run the query:
Rst = Ps.executeQuery()
While Rst.next()
    [...]

The user provided parameters are set via variables in the Prepared Statements bindings, as above.
Or via the Filter Table Method.

dreamquartz1 · July 8, 2026, 9:38pm

Indeed that is the case. It is a Query with multiple options for different types of User Input.
The Query is used throughout the DataBase for different reasons. The information is very fluid because of the nature of the information. Data is entered multiple times per week and likely linked to different projects. There is no way to create a View of the Query, because of its dynamics.

Weird though. I do remember that in my younger years that BASIC allowed for User Input. Data from external sources could be manipulated and used in other environments. Do not understand the restrictions @ this point. Will look @ what CRDF came up with. Apparently things have changed.

Dream

dreamquartz1 · July 8, 2026, 9:39pm

I will look at your suggestions, but please see my response to RobertG

Dream

CRDF · July 8, 2026, 9:55pm

and it does indeed.
You get user inputs via Form Controls and insert these into BASIC variables.
Then you “bind” these to the query via Prepared Statement. Like almost all languages manipulating SQL statements.
What Base does not have is the resource for the query referencing the Form’s controls, like MS Access does. So they use also the clever trick of the FilterTable Method…
You can also

alter the (saved) query .Command, replacing the parameter marks with the BASIC variables;
run the query;
replace again the SELECT (.Command) with parameter marks. That’s to say: reinsert the “original” SQL.

dreamquartz1 · July 8, 2026, 10:46pm

Maybe it can be simple, but not sure.
This is what I would like to automate: Open Calc Sheet->Ctrl+Shift+F4->Select DataSource->Select Query->Run…
The Query only needs to be started in my case.

Am I overthinking it by going the route I was taking, is my question now.

Dream

CRDF · July 8, 2026, 10:56pm

Yes… I don’t know about this thing of running queries from Calc.
Because user needs to pass the parameters of course.
It asks for the parameter (:first_name).
So I think that when refreshing it asks again
CalcDataSource

Wanderer · July 9, 2026, 5:29am

I usually just drag the query to A1 of an empty sheet. This creates a database range in my Calc-Sheet to be refreshed by menu Data->Refresh(range)
.
You can even delete the contents in the database-range now to have an “empty” sheet and save this. A call to refresh fills the file after opening.
.
In one case I created a button on the sheet to call a macro for refresh and update some formatting according to the new content, but a macro is not really necessary for this and more a question for speed and convenience.

That sentence puzzles me a bit. So you are not interested in the result of the query?

BASIC does, but there are two problems

What I remember from younger years used the command line. So you have to adapt for GUI first
Input is more restricted in SQL. As this is also used, when you access a database somewhere else in the world (instead of residing on your desktop) this may be understandable. Also we are now aware of SQL-injections…

But as Robertg already noted: The actual error you faced seems to be a result of an automatic conversion of your database.

CRDF · July 9, 2026, 10:24am

Let me try to understand what you do want to accomplish
User, FROM CALC, accesses this query, sets its parameters, and sees the result IN A CALC SHEET.
Is it this?

Villeroy · July 9, 2026, 1:09pm

I don’t know how often I pointed you to solutions that are (almost) free of macro code.
https://forum.openoffice.org/en/forum/search.php?keywords=&terms=all&author=dreamquartz&sc=1&sf=all&sr=topics&sk=t&sd=d&st=0&ch=300&t=0&submit=Search

Just enter the filter criteria into a filter table accessible through a form on sheet and refresh a database range. It’s trivial and fool-proof to use.

Villeroy · July 9, 2026, 3:38pm

One more time:

Register the database as “PowerFilter”.
Test the spreadsheet.
– Sheet “Input Form” has a filter record and a filtered subform with an editable record set.
– Sheet “Report” has another filter record and database range linked to a filtered record set. Incudes a one-liner macro.
– The pivot sheet has a pivot table which can be filtered by itself.
PowerFilter.odb (371.6 KB)
PowerFilter.ods (69.9 KB)

dreamquartz1 · July 9, 2026, 5:56pm

@CRDF What you show in your screenshot is indeed what I am looking for.
Selecting the Query from the DataBase, as you indicate as Query1, will run the query, and it shows the ‘Parameter Input’
Please note there are more Parameters but relevant to be shown

The following steps are:

Select appropriate Parameter
Enter Value
OK
ResultSet
Please note that there is way more data but not relevant to be shown
Selection of the results are made by ‘marking’ the specific rows.
Using the following buttons
For Reference: Left → Right numbered #01-09

With Cursor @ A1 in Calc Sheet
5. #09, to place the specific ResultSet on the Calc Sheet
6. Save As “Name”.csv
7. #01 for refresh of the Query if so needed.

The document “Name”.csv is then transferred to a MS based computer program, to be imported for use.
This last part is a fully manual action, because that system is not attached to the interweb.

The User is using this principle in a lot of cases for different ResultSets. The ResultSets are always of the csv-format, and exported/imported to different environments not under the control of the User.

Dream

Hennii · July 13, 2026, 1:24am

I ran into something similar a while ago. If the query is already saved and quite large, duplicating the SQL inside the macro doesn’t seem like a good solution. It would be much easier if the macro could just call the existing query and pass the required parameters. I’m interested to see if there’s a clean way to do this in LibreOffice Base as well.