PowerBuilder – Datawindow SQL changes made easy
One application I developed involved retrieving from a large dataset based on any number of criteria. Yea, I know, this sounds just like the application you worked on right? Anyway, there are a variety of ways to achieve this in PB but the one approach I like best when dealing with a variable number of search parameters is to modify the datawindow object SQL prior to the retrieve of the data.
Except in my very first PB job, I have not worked anywhere with the requirement of ‘database independence’. All the apps I’ve coded run against a specific database (Oracle, SQL Server, SQL Anywhere, etc.). As such I almost never create datawindow objects in graphic mode – I simply find this too restrictive. To implement the type of functionality this article is about, you need to have your Datasource in ‘Convert to Syntax’ mode.
Now you have something like this:
SELECT pm.partNo,
pm.partDesc,
cm.catId,
cm.catDesc,
gp.productGrpDesc,
pa.availableAmt,
um.uomCode,
lm.locationCode
FROM dbo.PartMaster pm
JOIN dbo.catMaster cm on pm.catKey = cm.catKey
JOIN dbo.productGrpMaster gp on pm.productGrpKey = gp.procustGrpKey
LEFT JOIN dbo.locationMaster lm on pm.partKey = lm.partKey
LEFT JOIN dbo.partAvailability pa on lm.locationKey = pa.locationKey
JOIN dbo.uomMaster um on pa.uomKey = um.uomKey
WHERE 1 = 1
Notice that there are no retrieval arguments in the statement.
Add ‘placeholders’ to the SQL in the form of comments. In the case of SQL Server you can use a double dash (–). Decide on a standard for these for ease of use. Our sql now looks like:
SELECT pm.partNo,
pm.partDesc,
cm.catId,
cm.catDesc,
gp.productGrpDesc,
pa.availableAmt,
um.uomCode,
lm.locationCode
FROM dbo.PartMaster pm
JOIN dbo.catMaster cm on pm.catKey = cm.catKey --cat_j
JOIN dbo.productGrpMaster gp on pm.productGrpKey = gp.procustGrpKey --pg_j
LEFT JOIN dbo.locationMaster lm on pm.partKey = lm.partKey --lm_j
LEFT JOIN dbo.partAvailability pa on lm.locationKey = pa.locationKey --pa_j
JOIN dbo.uomMaster um on pa.uomKey = um.uomKey
WHERE 1 = 1 --cat --pg --lm --pa
The placeholders on the JOIN lines can be used to limit items returned to a single specified value. To use lists of values you use the placeholders in the WHERE clause.
Many times you will see manipulation of the datawindow SQL being done in the sqlpreview event. For the purposes of this, however, you need to change the sql prior to the actual retrieve (in a ‘pre’ retreive event) since the comments (our placeholders) are not passed to the database (they are stripped off and don’t appear in the sqlpreview event). Use either of the following to obtain the SQL from the datawindow object:
string ls_sql ls_sql = dw_1.Object.DataWindow.Table.Select ls_sql = dw_1.getsqlselect( )
Now you have to generate the strings you want to insert into the SQL using whatever technique you have developed. Then replace the placeholder(s) with the desired string(s).
string ls_cat_j, ls_new_sql ls_cat_j = 'AND cm.catKey = 12345 ' ls_new_sql = replace(ls_sql, Pos(ls_sql,'--cat_j'), 7, ls_cat_j) // '7' is the length of '--cat_j'
And finally put the new SQL into play with:
dw_1.Object.DataWindow.Table.Select = ls_new_sql
If you are dealing with an updateable datawindow you should use the SetSQLSelect method (read the Help for more details).
You might also be interested in
