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

Use this board to ask questions or have discussions with other Rich Displays users.
Bryan641
Profound User
Posts: 72
Joined: Fri Oct 01, 2010 5:07 pm
First Name: Bryan
Last Name: Leaman
Company Name: InVue Security
Phone: 704-752-6513 x221
Address 1: 15015 Lancaster Hwy
City: Charlotte
State / Province: North Carolina
Zip / Postal Code: 28277
Contact:

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

Post 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
User avatar
David
Profound Logic Staff Member
Posts: 690
Joined: Fri Jan 04, 2008 12:11 pm
First Name: David
Last Name: Russo
Company Name: Profound Logic Software
Contact:

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

Post 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.
Bryan641
Profound User
Posts: 72
Joined: Fri Oct 01, 2010 5:07 pm
First Name: Bryan
Last Name: Leaman
Company Name: InVue Security
Phone: 704-752-6513 x221
Address 1: 15015 Lancaster Hwy
City: Charlotte
State / Province: North Carolina
Zip / Postal Code: 28277
Contact:

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

Post by Bryan641 »

Alert box says "No errors have been reported."

--Bryan
User avatar
David
Profound Logic Staff Member
Posts: 690
Joined: Fri Jan 04, 2008 12:11 pm
First Name: David
Last Name: Russo
Company Name: Profound Logic Software
Contact:

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

Post 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?
Bryan641
Profound User
Posts: 72
Joined: Fri Oct 01, 2010 5:07 pm
First Name: Bryan
Last Name: Leaman
Company Name: InVue Security
Phone: 704-752-6513 x221
Address 1: 15015 Lancaster Hwy
City: Charlotte
State / Province: North Carolina
Zip / Postal Code: 28277
Contact:

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

Post 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
robhathome2
Profound User
Posts: 42
Joined: Mon Jun 27, 2011 9:11 am
First Name: Rob
Last Name: Horton
Company Name: Design Group Plc
Contact:

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

Post 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
User avatar
David
Profound Logic Staff Member
Posts: 690
Joined: Fri Jan 04, 2008 12:11 pm
First Name: David
Last Name: Russo
Company Name: Profound Logic Software
Contact:

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

Post 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.
User avatar
David
Profound Logic Staff Member
Posts: 690
Joined: Fri Jan 04, 2008 12:11 pm
First Name: David
Last Name: Russo
Company Name: Profound Logic Software
Contact:

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

Post by David »

Bryan -- Can you provide example SQL statement with WITH clause which fails?
Bryan641
Profound User
Posts: 72
Joined: Fri Oct 01, 2010 5:07 pm
First Name: Bryan
Last Name: Leaman
Company Name: InVue Security
Phone: 704-752-6513 x221
Address 1: 15015 Lancaster Hwy
City: Charlotte
State / Province: North Carolina
Zip / Postal Code: 28277
Contact:

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

Post 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.
User avatar
David
Profound Logic Staff Member
Posts: 690
Joined: Fri Jan 04, 2008 12:11 pm
First Name: David
Last Name: Russo
Company Name: Profound Logic Software
Contact:

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

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

Who is online

Users browsing this forum: No registered users and 1 guest