Page 1 of 2

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

Posted: Wed Nov 14, 2012 2:35 pm
by 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

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

Posted: Wed Nov 14, 2012 2:41 pm
by 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.

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

Posted: Wed Nov 14, 2012 2:53 pm
by Bryan641
Alert box says "No errors have been reported."

--Bryan

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

Posted: Wed Nov 14, 2012 3:02 pm
by 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?

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

Posted: Wed Nov 14, 2012 3:31 pm
by 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

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

Posted: Thu Nov 15, 2012 9:14 am
by 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

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

Posted: Fri Nov 16, 2012 12:56 pm
by 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.

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

Posted: Fri Nov 16, 2012 12:58 pm
by David
Bryan -- Can you provide example SQL statement with WITH clause which fails?

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

Posted: Mon Nov 26, 2012 4:17 pm
by 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.

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

Posted: Mon Dec 03, 2012 11:04 am
by 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.