Can't get grid with custom sql to display any data

[Bryan641]

I've added a grid to my record format and set the custom sql to a field in my program, but my grid always displays as empty.

In debug I can copy the value in my custom sql field, then paste it into a STRSQL session and it runs fine. But when I activate Firebug and display the screen, there is no additional entry in the list of network requests to pull data into the grid and my grid is empty, but appears to have 100 rows with no data.

What am I doing wrong?

--Bryan

Grid settings:
id: DM190Grid
record formoat name: SFL#1
field type: grid
font family: Arial
font size: 13px
font style: normal
font weight: normal
header height: 30
header font family: Arial
header font size: 13px
header font weight: normal
header font color: #000000
header background: #fcf0c0
header image: /profoundui/userdata/custom/images/InVueGrid/header.png
column headings: Seq,BillTo,Customer name,PO,Orders
even row background: #dddddd
hover background: #fcf0c0
number of rows: 11
number of columns: 5
row height: 20
hover image: /profoundui/userdata/custom/images/InVueGrid/hover.png
column widths: 69,60,270,198,127
sortable columns: true
default sort order: Ascending
resizable columns: true
movable columns: false
custom sql: C1SQL (character 1024)
left: 5px
top: 220px
height: 230px
width: 725px

[David]

You can see the result of SQL errors by running the following in the script console in your browser after a failed SQL query:

Code: Select all

showErrors();

If you're using IE, you can also put this into the address bar and press Enter to do the same:

Code: Select all

javascript: showErrors();

This will bring up an alert box with the actual error. Does that give any clue?

Another way to see the same stuff is to look in the http server job logs, but it can be a bit tricky to work out which one is running your SQL, so using the showErrors() API is usually recommended. But, if you are running inside Atrium, looking at the job logs is the only way to see the errors.

[Bryan641]

Alert box says "No errors have been reported."

--Bryan

[David]

That's odd. This function will (or should) report any SQL errors that occur. We did have some issues with the error reporting in some recent versions of PUI.

What version are you using?

[Bryan641]

Ok, I tried pasting my SQL into the "custom sql" field of another plain grid I dropped on the page and it did the same thing. I restructured it to NOT use a "with" clause and it started working. Again, the sql was good as it was, but apparently the Grid widget doesn't submit sql statements it doesn't like.

--Bryan

[robhathome2]

Hi David

I'm getting the same issue AND I'm using a CTE (With at the start of the SQL).

Are you able to detail what type of SQL statements are acceptable for the custom SQL field?

We're currently using 4.1.1 on V5R4 and I've noticed that there was a change with regards to SQL in 4.1.2. Will this have corrected it?

Rob

[David]

CTE's were not previously allowed due to the way PUI was validating the SQL statements.

As of version 4.1.2, CTE's can be used.

[David]

Bryan -- Can you provide example SQL statement with WITH clause which fails?

[Bryan641]

with fruit as ( select decimal(1,3,0) as id, char('Apples',10) as name from qsys2/qsqptabl union all select decimal(2,3,0) as id, char('Bananas',10) as name from qsys2/qsqptabl union all select decimal(3,3,0) as id, char('Durian',10) as name from qsys2/qsqptabl union all select decimal(4,3,0) as id, char('Kiwifruit',10) as name from qsys2/qsqptabl union all select decimal(5,3,0) as id, char('Oranges',10) as name from qsys2/qsqptabl union all select decimal(6,3,0) as id, char('Peaches',10) as name from qsys2/qsqptabl ) select * from fruit

--Bryan

In this simplified scenario that will run without our specific database tables, simply removing "with fruit as (" from the beginning and ") select * from fruit" at the end will allow it to work.

[David]

I can recreate this -- we will work on a fix for the next update to PUI. The problem is that even though the SQL "system" will now accept CTE's, validation in the grid widget is preventing it from making a request to the server to run the CTE, I think because it would produce an error before.

This validation simply needs to be removed now.