Hello Ferry,
I believe using filtering will be much slower then using a parameter query
(my database is a remote postgresql).
So instead I want to use a parameter query with named parameters (e.g.
where
Date > :StartDate) .
On opening the form I enumerate the controls and check for Bindable,
Dataaware controls of which the datafield is not bound (to a column).
(this step works it seems)
Then I want to create parameters based on the controls name using
com.sun.star.sdbc.XParameters for each unbound control.
(see 14.5.1 of the api)
Unfortunately I don't understand how to create NamedParameters.
oForm.setDate seems to accept only a parameterIndex.
That is correct - the interface com.sun.star.sdbc.XParameters is designed
to work with Prepared Statements and does not support named parameters.
To work with these you will need to use a
com.sun.star.sdb.SingleSelectQueryAnalyzer.
I don't have any example code that does exactly what you are doing but I do
have some Basic code that works with named parameters -
What I have is my own dialog box that contains two lists boxes, a text box
and a couple of buttons.
The dialog is displayed and lists the named parameters from a query in the
left list box. The user can then set the values for these parameters using
the text box, the values are displayed in the right list box. The user can
move up and down the lists, and when all the parameters are set to his
liking click the Done button.
The named parameters are then updated in the query with the values supplied.
Here is the outer most procedure in the utility.
sub exportQuery( Stmt as variant, _
QueryDef as new "com.sun.star.sdb.QueryDefinition", _
FileName as string _
)
dim oSQA as variant
dim vParamVals as variant
oSQA = Stmt.getConnection.CreateInstance( "
com.sun.star.sdb.SingleSelectQueryAnalyzer" )
oSQA.setQuery( QueryDef.Command )
if oSQA.Parameters.Count = 0 then
exportSimpleQuery( Stmt, QueryDef.Command , FileName )
else
if getParameterValues( QueryDef.Name, oSQA, vParamValS ) then
exportParamQuery( Stmt, oSQA, vParamVals, FileName )
end if
end if
end sub
The most useful line of that for you is
oSQA = Stmt.getConnection.CreateInstance( "
com.sun.star.sdb.SingleSelectQueryAnalyzer" )
You must always use CreateInstance against an open database connection.
Next look at the line
oSQA.setQuery( QueryDef.Command )
SetQurey expects a string that is a legal SQL select statement. In my case I
get this from an existing QeryDefinition in the database. You will need to
retrieve this from the dataform control in your form.
getParameterValues( QueryDef.Name, oSQA, vParamValS ) is the routine that
manages the custom dialog box I mentioned above, and the user entered values
are returned in the array vParamValS.
Here is a function that returns all the parameters in the query as a
stringlist, again it has some extra stuff you wouldn't need but you can
strip that out.
function getParameterNames( SQA as variant, vParamVals as variant ) as
variant
dim strList as variant
dim enParams as variant
dim cntr as integer
redim strList( SQA.Parameters.Count - 1 )
enParams = SQA.Parameters.CreateEnumeration
cntr = 0
while enParams.HasMoreElements
strList( cntr ) = enParams.NextElement.Name
vParamVals( cntr, cParamName ) = strList( cntr )
vParamvals( cntr, cParamvalue ) = ""
cntr = cntr + 1
wend
getParameterNames = strList
end function
The final step is to update the query itself and this I do with this
routine.
sub exportParamQuery( Stmt as variant, _
SQA as variant, _
vParamVals as variant, _
FileName as string)
dim strCmd as string
dim cntr as integer
strCmd = SQA.getQuery
for cntr = 0 to SQA.Parameters.Count - 1
strCmd = ReplaceString(StrCmd, vParamVals( cntr, 1 ), ":" &
vParamVals( cntr, 0 ) )
next
exportSimpleQuery( Stmt, strCmd, FileName )
end sub
The routine ReplaceString can be found in the TOOLS library that is
installed as a shared library in the OO.o installation.
At this point you would update the dataform control with the SQL command in
the variable strCMD.
I hope that helps with your project. If there is anything specific I can
help with please don't hesitate to ask.
Sincerely
Drew
