LO Calc - Query Parameters Input/Output to cells. Using form's "Fill Parameters" event

[QueryParamStuckMan]

Hi,

I’m a bit stuck at the moment. Hope someone can help point me in the right direction.
I have a an SQL query along the lines:

SELECT AVG(Col1), AVG(Col2) FROM mytable
WHERE :Val1 IS NULL OR Col1 = :Val1
AND :Val2 IS NULL OR Col2 = :Val2

I have a separate tables in calc display the results:
AVG Col1, AVG Col2
42%, 99%

And an input table to filter to be used as input:
Col1, Col2
Val1, Val2

My query is working fine when run it manually

• I get a prompt to enter the parameter value
• I get results to a table form output
• I drag the results into some target cells
• If I refresh using Data > Refresh Range, I get the query parameter prompt and the output table updates as expected.

My goal is to have just input cells and trigger the lookup again and populate the target cells with a button (or cell selection event).
I’m not looking for any DB inserts/updates.

For the input to the query - I have tried using a macro on the form’s “Fill Parameters” event:

Sub ParamCheck(oEvent)
If oEvent.Parameters.Count = 0 Then
	Exit Sub
End If

For i = 0 to oEvent.Parameters.Count -1
	prop = oEvent.Parameters.getByIndex(i)
	
	If prop.Name = "Val1" Then
		prop.Value = "something"
	End If
	If prop.Name = "Val2" Then
		prop.Value = "something_else"
	End If
Next
End Sub

This works fine when I run it manually again - I do not get a prompt box asking to type in the parameter values, it just uses the ones set in the macro.
However, if I then trigger a refresh of the Database Range using the ‘Refresh Range’ I still get a prompt box - it seems to ignore the Form’s Fill Parameters event.

I have also tried to update using this solution MySQL DATE_ADD not recognized in datagrid
which uses

oDBRange.getReferredCells.doImport(oDesc())

But this also prompts for manual entry of the parameters.

I would be very grateful for any help on this.

[Ratslinger]

Hello,

While your code for default parameter entry works, you could eliminate the entire For statement. The parameters are positional and can be accessed with:

oEvent.Source.setString(1,"something")
oEvent.Source.setString(2,"something_else")

With that said, I would abandon that and the form as requested.

The answer does lie in the link you noted. With that you can create the SQL on-the-fly and then execute. Have tested the some time ago and her is one snippet:

Sub Main3
oDBRange = thisComponent.DataBaseRanges.getByName("Import1")
oDesc() = oDBRange.getImportDescriptor()
For i = 0 to ubound(oDesc())
    oPrp = oDesc(i)
    If oPrp.Name = "DatabaseName" then
        oPrp.Value = "FlowerShop"
    elseIf oPrp.Name = "SourceType" then
        oPrp.Value = com.sun.star.sheet.DataImportMode.SQL
    elseIf oPrp.Name = "SourceObject" then
        oPrp.Value = "SELECT * FROM ""T_bouquets"" WHERE ""id_bouquet"" < 6"
    elseif oPrp.Name = "IsNative" then
        oPrp.Value = True
    Endif
    oDesc(i) = oPrp
Next
oDBRange.getReferredCells.doImport(oDesc())
End Sub

Had DB range set in Calc based upon DB table Query. The select statement in the macro can be modified to use your varying data (read the cell results). No need to use parameters just use variables.

Some further reference (along with the important one you noted):

DataImportMode

and this (may be a bit confusing):

Calc macro failing to update sheet data from Base query

Note: I do have difficulty understanding just what you have in Calc and what is in MySQL - multiple tables seem mentioned and the picture is not quite clear. Example - Col2 is in Calc & SQL (table field?)

[QueryParamStuckMan]

Thanks very much Ratslinger. I’ve gone ahead and used the raw SQL as you suggested, much simpler.
Also tried changing it to a stored procedure and you’ve answered there too so thanks for that too Calc: call a stored procedure - #2 by Ratslinger

As for the schema I mentioned, yes it was just 1 table with 2 columns Col1 and Col2. Sorry for confusion, I was mentioning additional tables when meaning Calc tables (1 for the filter and 1 for the results)

[Villeroy]

Parent forms can substitute parameters.
Forms can be attached to sheets.
PowerFilter.ods (“Report” sheet)
PowerFilter.odb

[GNK]

Thank you (again) Villeroy - while the OP may have found an alternative ‘macro-orientated’ solution from Ratslinger, these examples are very useful for using a more ‘LO-documents’ focused approach:

something like;

1. a QUERY or VIEW in REGISTERED_database with :parameters eg. Frequently asked questions - Base - The Document Foundation Wiki

2)in Calc > open DataSources window, and navigate to relevant .odb

3)place cursor on desired row in Calc and insert the query
4)and/or select + drag/drop the parameter query from the DataSource window to desired row (nb always starts first column)

5)prompt for parameters will now appear … and then data is inserted

6)(you could define a ‘Range’ for the cell data now - and use macros ect to refresh)

7)values will remain in sheet until ‘Refresh Data’ is called from the Calc data-menu (or other methods to refresh the defined cell-range), at which point the dialog for ’ please input :parameters’ will be called again (automatically by Calc)

This is a useful method for creating ‘native’ updatable spread-sheets views from dynamic queries to a database.
(it was not clear to me previously that it could be done this way)

!LoveYouLibre!

[Villeroy]

Works as well with Writer’s mail merge.