Ask Your Question
0

Base: run a query with a variable?..

asked 2015-02-13 03:43:41 +0200

darkpenguin gravatar image

I have a Form of a "query" type, and that query asks for a :variable. I want to write a macro that passes something as that variable when refreshing that form. Is that possible?

While we're at it, is it possible to write a macro to execute another query from the same project and pass a variable to it?

I am trying to read the manual, which has something about establishing connections with databases and running queries, but that doesn't look like what I want. I just want to call a query that's already in the project I'm working with, one that might even be associated with the form this macro is being run from! Is there no simple way to, like, result=ThisProject.PredefinedQueries.getByName("MyQuery").Execute?!..

edit retag flag offensive close merge delete

1 Answer

Sort by » oldest newest most voted
1

answered 2015-02-13 06:41:53 +0200

doug gravatar image

updated 2015-02-13 06:45:30 +0200

Here is a way to access the stored query definition and run it indirectly. In this example, the entire LibreOffice file object is named New Database.odb.

Context=CreateUnoService("com.sun.star.sdb.DatabaseContext")
DB=Context.getByName("New Database") 
Queries = Db.getQueryDefinitions()
sql_str = Queries.getByName("Query1").command

root_form = ThisComponent.Drawpage.Forms
mainform = root_form.getByIndex(0)
conn = mainform.ActiveConnection
sql = conn.createStatement()

result = sql.executeQuery(sql_str)
result.next
MsgBox result.getString(1)

This will return a message box with the first column of the first row of the query, assuming it is a string. If you explore the object Queries.getByName("Query1") with dbg_methods and dbg_properties, you might find a way to run the query directly. If you explore result in the same way, you will see the other methods to return row contents. Every data type has its own method. The code is intended to be run from a form.

h/t baseprogramming.com

edit flag offensive delete link more
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2015-02-13 03:43:41 +0200

Seen: 2,090 times

Last updated: Feb 13 '15