Page 1 of 1
Passing parameters from form to query
Posted: Thu Jun 02, 2011 5:21 pm
by joaofmateus
I have a forms where I write:
Enter the starting date ------>
Enter the ending date ------->
I have this query:
Code: Select all
SELECT "COD_CONTA" AS "CONTA", SUM ('DEBITO) AS "DEBITO", SUM ("CREDITO") AS "CREDITO" FROM "tMOVIMENTOS_CONTA" AS "tMOVIMENTOS_CONTA" WHERE "DATA_MOV" >= :DATAINICIAL AND "DATA_MOV" <= :DATAFINAL GROUP BY "COD_CONTA
I wanted to pass parameters from forms to the query.
Would somebody could help me?
Re: Passing parameters from form to query
Posted: Thu Jun 02, 2011 10:24 pm
by rudolfo
I wish there would be a less complicated answer to this, but I fear you won't be able to achieve this without macros. Here is an outline on how to do this (it is difficult to be more precise, because this depends on how you want to start the macro, button click, ...?)
- Retrieve the connection from the active DataSource
- Use the prepareCommand() method of the connection to create a prepared statement based on the query
- Retrieve your two date values from the current record of the form
- Bind these two variables to the place holders in the query (Note: Date parameters need to be of type "com.sun.star.util.Date" UNO structure: stmt.setDate(1,aBegin) and stmt.setDate(2,aEnd))
- Execute the prepared statement to retrieve a result set
- Iterate through the result set and use the getString() and getLong() to read the resulting fields into variables of your scripting language.
Using result of Queries has some OOo Basic code that might be good enough for you after some modifications.
I can give you some sample code, once you have decided to go the macro way. But be aware that you need some programming experience because most probably you will have to make some modifications to match it on you environment. And without experience (including debugging a macro) this will all be like chinese for you.
Re: Passing parameters from form to query
Posted: Thu Jun 02, 2011 11:16 pm
by Villeroy
Have a look at the attachment.