Can't get grid with custom sql to display any data
-
- 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
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
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
- 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
You can see the result of SQL errors by running the following in the script console in your browser after a failed SQL query:
If you're using IE, you can also put this into the address bar and press Enter to do the same:
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.
Code: Select all
showErrors();
Code: Select all
javascript: showErrors();
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.
-
- 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
Alert box says "No errors have been reported."
--Bryan
--Bryan
- 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
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?
What version are you using?
-
- 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
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
--Bryan
-
- 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
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
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
- 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
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.
As of version 4.1.2, CTE's can be used.
- 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
Bryan -- Can you provide example SQL statement with WITH clause which fails?
-
- 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
--Bryanwith 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
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
- 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
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.
This validation simply needs to be removed now.
Who is online
Users browsing this forum: No registered users and 0 guests