Hi there,
I need to find a mechanism to define filter criteria for queries. The query SQL will be stored as meta data, together with the filter criteria a user might need to toggle during run-time.
Since we already have parameters that we can use in TDataSet, my first attempt was to use this and simply extract the parameter names. This leaves me with two problems:
1. Identifying the field that the parameter is used for
2. Specifying values for the parameter during run-time
None of this is a problem when we're working with simple operators, such as 'where balance > :minbal' or 'where firstname = :fname'. However, when we start doing things such as 'where length in (:alength)', we run into problems.
The first issue (i.e. specifying the field associated with a parameter) can be solved with a few parsing exercises so I'm not overly concerned with that. However, how do I specify a parameter value for alength (integer) in the example above?. The filter should execute as something like 'where length in (1,3,5)', but how can I implement this? I've tried using the parameter's NativeStr property but don't get any query results. Param.Value and Param.AsString also don't work. I'm testing with Oracle and MySQL, but my solution should be generic across all databases...
Can this be done?
Ben
Query parameter values
Moderators: gto, EgonHugeist
-
mdaems
- Zeos Project Manager
- Posts: 2766
- Joined: 20.09.2005, 15:28
- Location: Brussels, Belgium
- Contact:
Hi ejg,
I'm afraid you'll have to find a less 'automatic' solution.
If I understand correctly your users will be presented a reusable query and a set of possible filters that are predefined. If they choose a filter specifying parameters they must also provide parameter values.
If this assumption is right you need to store
- the sql statements
- the possible filters (a where condition without the 'where' part, eventually including parameters)
- for every filter containing parameters : the expected input type for every parameter.
This allows every type of filter to be made. Even concatenating filters is possible using the AND operator. The only assumption here is that the person adding new filters knows what datatype the parameter must have, but in my opinion that's a must anyway when you want to write filters.
Mark
I'm afraid you'll have to find a less 'automatic' solution.
If I understand correctly your users will be presented a reusable query and a set of possible filters that are predefined. If they choose a filter specifying parameters they must also provide parameter values.
If this assumption is right you need to store
- the sql statements
- the possible filters (a where condition without the 'where' part, eventually including parameters)
- for every filter containing parameters : the expected input type for every parameter.
This allows every type of filter to be made. Even concatenating filters is possible using the AND operator. The only assumption here is that the person adding new filters knows what datatype the parameter must have, but in my opinion that's a must anyway when you want to write filters.
Mark
Hi Mark,
Thanks for the reply
Yes when you include parameters in your queries, the dataset automatically parses them and extracts the parameter names. The applicable fields and data types can be specified by the person composing the query, and values can be supplied via a front-end by the end-user.
In general this works quite well, and adds to performance. The problem I'm experiencing is when parameters are included after operators such as IN. For example, if you'd like to add something in your where clause which says something like:
'where afield in (:aparam)', supplying a value for this parameter becomes a problem. if afield is, for instance, an integer type, you can't set the parameter value to '(1,5,9)', even if you're using parameter.value.asstring. even if the field is a string type, the query executes but no data is returned. i've looked at the parameter's nativestr property to solve this, but this also returns no data
Do you perhaps know how one can handle parameter values for these types of operators?
Regards,
Ben
Thanks for the reply
Yes when you include parameters in your queries, the dataset automatically parses them and extracts the parameter names. The applicable fields and data types can be specified by the person composing the query, and values can be supplied via a front-end by the end-user.
In general this works quite well, and adds to performance. The problem I'm experiencing is when parameters are included after operators such as IN. For example, if you'd like to add something in your where clause which says something like:
'where afield in (:aparam)', supplying a value for this parameter becomes a problem. if afield is, for instance, an integer type, you can't set the parameter value to '(1,5,9)', even if you're using parameter.value.asstring. even if the field is a string type, the query executes but no data is returned. i've looked at the parameter's nativestr property to solve this, but this also returns no data
Do you perhaps know how one can handle parameter values for these types of operators?
Regards,
Ben