Grid Selection criteria from client side

Use this board to ask questions or have discussions with other Rich Displays users.
HowardA
Profound User
Posts: 29
Joined: Fri Feb 08, 2013 8:34 am
First Name: howard
Last Name: Arner
Company Name: Citco Sys
State / Province: Georgia
Country: United States
Contact:

Grid Selection criteria from client side

Post by 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
Profound User
Posts: 29
Joined: Fri Feb 08, 2013 8:34 am
First Name: howard
Last Name: Arner
Company Name: Citco Sys
State / Province: Georgia
Country: United States
Contact:

Re: Grid Selection criteria from client side

Post by 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
Experienced User
Posts: 2711
Joined: Wed Aug 01, 2012 8:58 am
First Name: Scott
Last Name: Klement
Company Name: Profound Logic
City: Milwaukee
State / Province: Wisconsin

Re: Grid Selection criteria from client side

Post by 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
Profound User
Posts: 29
Joined: Fri Feb 08, 2013 8:34 am
First Name: howard
Last Name: Arner
Company Name: Citco Sys
State / Province: Georgia
Country: United States
Contact:

Re: Grid Selection criteria from client side

Post by 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
Profound User
Posts: 29
Joined: Fri Feb 08, 2013 8:34 am
First Name: howard
Last Name: Arner
Company Name: Citco Sys
State / Province: Georgia
Country: United States
Contact:

Re: Grid Selection criteria from client side

Post by 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
Last edited by HowardA on Wed Jul 10, 2013 2:12 pm, edited 1 time in total.
HowardA
Profound User
Posts: 29
Joined: Fri Feb 08, 2013 8:34 am
First Name: howard
Last Name: Arner
Company Name: Citco Sys
State / Province: Georgia
Country: United States
Contact:

Re: Grid Selection criteria from client side

Post by HowardA »

Ok, I just updated to latest Profound and tried this again using the parameter markers.
7-3-2013 10-11-33 AM.png
7-3-2013 10-11-33 AM.png (20.08 KiB) Viewed 3985 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
Profound User
Posts: 29
Joined: Fri Feb 08, 2013 8:34 am
First Name: howard
Last Name: Arner
Company Name: Citco Sys
State / Province: Georgia
Country: United States
Contact:

Re: Grid Selection criteria from client side

Post by HowardA »

Tried several more times various syntax changes and still can't get it to function.
Scott Klement
Experienced User
Posts: 2711
Joined: Wed Aug 01, 2012 8:58 am
First Name: Scott
Last Name: Klement
Company Name: Profound Logic
City: Milwaukee
State / Province: Wisconsin

Re: Grid Selection criteria from client side

Post by 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
Profound User
Posts: 29
Joined: Fri Feb 08, 2013 8:34 am
First Name: howard
Last Name: Arner
Company Name: Citco Sys
State / Province: Georgia
Country: United States
Contact:

Re: Grid Selection criteria from client side

Post by 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
7-12-2013 7-39-59 AM.png (8.22 KiB) Viewed 3927 times
Work# 0077.png
Work# 0077.png (7.6 KiB) Viewed 3927 times
Attachments
Work# 0076.png
Work# 0076.png (6.86 KiB) Viewed 3927 times
Scott Klement
Experienced User
Posts: 2711
Joined: Wed Aug 01, 2012 8:58 am
First Name: Scott
Last Name: Klement
Company Name: Profound Logic
City: Milwaukee
State / Province: Wisconsin

Re: Grid Selection criteria from client side

Post by 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...
Post Reply

Who is online

Users browsing this forum: No registered users and 1 guest