Using Parameterized Queries in Morfik

Morfik allows you to define a form with data aware controls that can be bound to a data source. Probably the most useful form of data source is a parameterized query. As the name implies this is a query with user specified parameters that allows you to control which records are returned. The query is defined using an SQL select statement that looks like this:

SELECT * FROM <some_table> WHERE <some_field1> = PARAM1 …

This all seems pretty obvious and it is, except that it took me about an hour to figure out that the parameters weren’t identified in any of the usual ways. I tried all kinds of different combinations until I finally realized that, when using the visual query designer, any valid symbol that doesn’t match a field name from the table is considered a parameter. When creating the query by keying the SQL directly, parameters are created by right clicking on the SQL editor window and selecting Parameters. Once the parameter is defined in the grid you can reference it in the SQL.

Once I got over that hurdle it took a while longer to figure out how to open the data aware form using a user supplied value for the parameter. Like most things with Morfik this is quite simple once you figure out how it works.

The OpenForm method takes 3 parameters. The first two, the form to open and the target window to open the form in, are well documented in the Morfik developers manual. For the third parameter it just says that this is for passing parameters to the form. Not very helpful. What you actually have to do is pass a string of the form “<param_name>=<param_value>”. From the sample Select statement above <param_name> would be PARAM1 and <param_value> would be whatever you want to search for. Multiple parameters should be separated by commas and double quotes are required around each parameter set.

There is a good article in the Morfik Wiki here:

http://www.morfikwiki.com/Query_parameter

Leave a Reply