Meta data about query parameters

[Villeroy]

I can not find any way to identify the named parameters of a parsed query, for instance the :pY and :pM in

SELECT * FROM "Somwhere" WHERE Year("D") = :pY AND Month("D") = :pM

I mean, it should not be like this:

Function getParamCount(sDB, sQuery)
	dbx = CreateUnoService("com.sun.star.sdb.DatabaseContext")
	db = dbx.getByName(sDB)
	conn = db.getConnection("","")
	qry = db.QueryDefinitions.getByName(sQuery)
	cmd = qry.Command
	stmt = conn.prepareStatement(cmd)
	i = 0
on error goto exitErr:
	do
		i = i +1
		stmt.setString(i, "1")
	loop
exitErr:
	conn.close()
	getParamCount = i-1
End Function

[Ratslinger]

All information I have seen points to this information coming from the JDBC connector via JAVA. Here is one mention from SQL Workbench/J manual:

.
also see:

https://docs.oracle.com/javase/7/docs/api/java/sql/ParameterMetaData.html#getParameterCount()

[sokol92]

One way is to put the query on the form and handle the form event Fill parameters.
DatabaseParameterEvent has attribute Parameters, which supports XEnumerationAccess, it will enumerate all query parameters. The query parameter has a large number of properties, some of which are missing from the documentation.

See also XDatabaseParameterListener.

[Villeroy]

Thank you very much for taking me on the right track again. Things become weird now.
Run this with apso being installed and replace the 2 names sreg and sqry with the names of a data source and parameter query:

import uno
from apso_utils import msgbox, mri

sreg = "MailMergeTables"
sqry = 'Param_Month'
ctx = uno.getComponentContext()
smgr = ctx.ServiceManager
        
def testParameters():
    dtp = smgr.createInstance('com.sun.star.frame.Desktop')
    doc = dtp.loadComponentFromURL('private:factory/swriter', '_default',0,())
    view = doc.getCurrentController()
    view.FormDesignMode = False
    frm = doc.createInstance('com.sun.star.form.component.Form')
    frm.DataSourceName = sreg
    frm.CommandType = 1
    frm.Command = sqry
    frm.EscapeProcessing = True
    doc.DrawPage.Forms.insertByIndex(0, frm)
    doc.setModified(False)
    frm = doc.DrawPage.Forms.getByIndex(0)
    params = frm.Parameters
    mri(frm)

MRI shows property Parameters as XIndexAccess and XEnumerationAccess with RealName, Type, TypeName for each parameter.
However, frm.Parameters is void.
MRI sees property Parameters within the passed form object but these objects are inaccessible.

EDIT: When I add this listener to my frm, MRI does not show the Parameters anymore, the init routine is called but approveListener is never called. In MRI the frm seems to be locked. isLoaded() = False. No movement possible.

class paramListener(unohelper.Base, XDatabaseParameterListener):
    def __init__(self,):
        self.params = None
        msgbox("__init__")

    def approveParameter(self, ev):
        msgbox('approveParameter')
        self.params = ev.Parameters
        return True

EDIT: params = uno.invoke(frm, ‘getParameters’, ()) makes no difference. The object remains void.

[Villeroy]

This is the listener speaking:

import uno, unohelper
from apso_utils import msgbox, mri
from com.sun.star.form import XDatabaseParameterListener
sreg = "MailMergeTables"
sqry = 'Param_Month'
ctx = uno.getComponentContext()
smgr = ctx.ServiceManager

class paramListener(unohelper.Base, XDatabaseParameterListener):
    def approveParameter(self, ev):
        for p in ev.Parameters:
            msgbox(str(p.Type) +' | '+ p.TypeName +' | '+ p.RealName)
        return False
        
def testParameters():
    dtp = smgr.createInstance('com.sun.star.frame.Desktop')
    doc = dtp.loadComponentFromURL('private:factory/swriter', '_default',0,())
    view = doc.getCurrentController()
    view.FormDesignMode = False
    frm = doc.createInstance('com.sun.star.form.component.Form')
    frm.DataSourceName = sreg
    frm.CommandType = 1
    frm.Command = sqry
    frm.EscapeProcessing = True
    doc.DrawPage.Forms.insertByIndex(0, frm)
    pL = paramListener()
    frm.addParameterListener(pL)
    doc.setModified(False)

[strike]However, this works with a new document only. It does not work with an already loaded document. The form is created but the listener is deaf.[/strike]
The crucial point is not the document. The macro works only if the test form is attached to a blank forms container. As soon as there is any other form element, the listener remains deaf.

Same in Basic (yes, it fails if you don’t add the listener although this listener never reports anything):

Function getParamInfo()
sreg = "MailMergeTables"
sqry = "Param_Month"
GlobalScope.BasicLibraries.loadLibrary("MRILib")
    doc = StarDesktop.loadComponentFromURL("private:factory/swriter", "_default",0,Array())
    view = doc.getCurrentController()
    view.FormDesignMode = False
    frm = doc.createInstance("com.sun.star.form.component.Form")
    frm.DataSourceName = sreg
    frm.CommandType = 1
    frm.Command = sqry
    frm.EscapeProcessing = True
    doc.DrawPage.Forms.insertByIndex(0, frm)
    lstr = createUnoListener("my_", "com.sun.star.form.XDatabaseParameterListener")
    frm.addParameterListener(lstr)
    doc.setModified(False)
    params = frm.getParameters()
    for i = 0 to params.getCount()-1
    	p = params.getByIndex(i)
    	msgbox(p.Type &" | "& p. TypeName &" | "& p.RealName)
    next
End Function

function my_approveParameter(ev)
	msgbox "approve"
	my_approveParameter=True
End Function
sub my_disposing(ev)
End Sub

[sokol92]

Thanks a lot for the interesting research! I think we will return to it in the future.

In the meantime (almost by accident) a slightly shorter way has been discovered:

Sub TestQueryParam()
  Dim oDBC, oDataSource, oConn, oAnalyzer, oQueryParams, p
  oDBC=CreateUnoService("com.sun.star.sdb.DatabaseContext")
  oDataSource=oDBC.getByName(ConvertToUrl("C:\Temp\BirthNumber-To-BirthDate.odb"))
  oConn=oDataSource.getConnection("", "")  ' User, Password
  oAnalyzer=oConn.createInstance("com.sun.star.sdb.SingleSelectQueryComposer")
  oAnalyzer.setCommand "Select * From Employess Where Gender=:g", com.sun.star.sdb.CommandType.COMMAND 
  oQueryParams=oAnalyzer.Parameters
  ' Mri oQueryParams
  For Each p In oQueryParams
    Msgbox(p.Type & " | "& p.TypeName & " | " & p.RealName)
  Next p  
End Sub

[Villeroy]

This is way better. Shorter, less complicated and it does not require any temporary objects.
My method feels more like exploiting a bug.