Grid Selection criteria from client side

[HowardA]

I have a grid that I want to fill with data from a file using a database file from the grid data properties. I want to use selection criteria that is determined on the client side (without having to go back to the server). So, I'd have selection criteria built from screen elements, and then use a grid refresh. (at least this is the concept I'd like to use).
Is this do-able?
Can I just key in some javascript like get("ELEMENT"); where element is an SQL statement to do the selection?

[HowardA]

Also, the grid.refresh doesn't seem to work??
Here's what I was using: getObj("sflCovTrn").grid.refresh();
I display the grid with simple selection criteria: Field1='A'
the data is displayed properly. Then I use DFU to update the data in the database. Then I press a button on the Rich Display file which has the grid.refresh (above) and the data does not change in the grid.
Based on the description in the api doc, I expected the data in the grid to refresh.

[Scott Klement]

Hello Howard,

The Grid widget will cache the data that it receives, and will only re-request it if the SQL statement or one of the parameter values changes.

I would recommend against putting the SQL statement in a field on the screen, because a 'hacker' (or disgruntled employee) could use a Javascript debugger to change the SQL statement, allowing them to run a different query than the one you were expecting, possibly bypassing security and accessing a file they weren't meant to access.

Instead, I would either build the SQL statement in the RPG program, and then bind the variable directly to the 'custom SQL' property on the grid control, or if you need to make changes and re-run from the client side, I would use parameter markers for the bits you need to change, and then just change the parameter markers from the client-side. (And then use 'refresh', which should now work becuase the parameter values have changed.)

[HowardA]

Thanks Scott,
This sounds promising! I'll need to upgrade my version of profound to have that capability and then give it a try.

[HowardA]

I'm going to move on upgrading to current profound UI version, but in the mean time I tried this: script: "CTKEY=" + get("selcovtrn") ;
as selection criteria. I get an error when it runs :

Operation: Run SQL SELECT QueryId: -104Message: Token ) was not valid. Valid tokens: ( + - ? : DAY INF NAN NOT RID ROW RRN CASE CAST CHAR DATE DAYS.SQLPrepare()

I took this from online examples and can't figure why it doesn't work. any ideas?
Thanks

[HowardA]

Ok, I just updated to latest Profound and tried this again using the parameter markers.

7-3-2013 10-11-33 AM.png (20.08 KiB) Viewed 4279 times

When I run this, I get the following error:

Operation: Run SQL SELECT QueryId: CPF9897Message: The server response is missing or invalid.

If I replace the "?" in the designer with a valid hard coded value and recompile, the data does appear. I did validate the selcovtrn field contains a valid value.
Is there a way to trouble shoot why this isn't working ?

[HowardA]

Tried several more times various syntax changes and still can't get it to function.

[Scott Klement]

Are you using the new, more secure, SQL mode? Or is this with the old SQL mode? This is controlled by the PUI_SEC_LEVEL setting in your httpd.conf, as discussed here:
http://www.profoundlogic.com/docs/displ ... urity+Mode

[HowardA]

It was set to use the new SQL mode. I changed to "unsecure" to see what happens, but when I do the "parameter value" goes away in the designer. So, I've set it back to the new SQL mode.
If I key a constant value for the selection criteria it is fine, but not when using the parameter value. Am I not formatting the syntax correctly?
script: get("selcovtrn");

7-12-2013 7-39-59 AM.png (8.22 KiB) Viewed 4221 times

Work# 0077.png (7.6 KiB) Viewed 4221 times

[Scott Klement]

Hi Howard,

The "parameter value" functionality does not work in the old SQL engine -- that's why I was asking about which one you were using, and is also why the designer removes that property when you are using the old one. I just wanted to be sure that you were using the new SQL engine.

I don't know why this isn't working for you. It looks like it should work.

However, I would also add that there is an easier way than the way you're doing it. You can just bind the 'parameter value' to the SELCOVTRN field. A script isn't needed to get data from a field...