Sunday, March 7, 2010

jQGrid Integration Kit for PL/SQL and Apex

I started developing applications back in the good (?) old client/server days. I was fortunate enough to discover Delphi quite early. Even from the start, the lowly 16-bit Delphi version 1 had a kick-ass DBGrid control which allowed you to quickly and easily build data-centric applications. Just write a SQL statement in a TDataSet component, connect it to the grid, and voila! Instant multi-row display and editing out of the box, without any coding.





Fast forward a decade. While I do enjoy building web applications (with PL/SQL and Apex) these days, I've always missed the simplicity of that DBGrid in Delphi. Creating updateable grids with Apex is pretty tedious work (not being entirely satisfied with the built-in updateable tabular forms, I've employed a combination of the apex_item API, page processes for updates and deletes, and custom-made Javascript helpers). It doesn't help that you have to refer to the tabular form arrays by number, rather than by name (g_f01, g_f02, etc.), and that you are restricted to a total of 50 columns per page.

Enter jQGrid, "an Ajax-enabled JavaScript control that provides solutions for representing and manipulating tabular data on the web".

jQGrid can be integrated with any server-side technology, so I decided to integrate it with PL/SQL and Apex.



Features



As of version 1.0, the jQGrid for PL/SQL and Apex has the following features:




  • Single line of PL/SQL code to render grid

  • Populate data based on REF CURSOR or SQL text (with or without bind variables). The REF CURSOR support is based on my REF Cursor to JSON utility package.

  • Define display modes (read only, sortable, editable) and edit types (checkbox, textarea, select list) per column

  • Store grid configuration in database, or specify settings via code (for read-only grids)

  • Ajax updates (insert, update, delete) based on either automatic row processing (dynamic SQL) or against your own package API

  • Multiple grids per page

  • Integrated logging and instrumentation

  • Usable without Apex (for stand-alone PL/SQL Web Toolkit applications) or with Apex, optionally integrated with Apex session security




The jQGrid Integration Kit for PL/SQL is free and open source. Download and try it now!.

26 comments:

Dimitri Gielis said...

Very nice Morten!

I've some recommendations:
- for the demo application, use supporting objects to create the necessary database objects.
That makes it easier for people to import and test on their server.
- in tables.sql is a little type (look at row 32 there is a comma which shouldn't)
- the source of the images in the APEX app are still pointing to your dev server (/i/muledev/devtest/img/undo_24.gif).
- During import of the app you could ask for the path they installed the js/css into (see supporting objects)
- I think there is no configuration of the grid demo_order_items
- I get this error when running page 2: "missing } after property list: var rs = eval (({"ROWSET":[{"ROW__ID":..." It seems a mix up in the json stream.
I put the example online here; http://examples.apex-evangelists.com/pls/apex/f?p=848:2:0
So it's easier for you to see what I see.

Keep up the good work!

Thanks,
Dimitri

Morten Braten said...

@Dimitri: Great feedback, thanks for testing this out!

I'll work on improving the packaging of the demo application.

With regard to the JSON error you get, it seems that for some reason, the dates (HIREDATE, etc.) are not quoted in the output (although they are on my development machine, and you seem to use the same date format...). Can you try running the JSON_UTIL_PKG.REF_CURSOR_TO_JSON function directly (from SqlPlus or TOAD) and see what the output looks like? Refer to my previous post (http://ora-00001.blogspot.com/2010/02/ref-cursor-to-json.html) for some examples of the output you should see.

Prabahar said...

Morten very nice indeed

I got same error message like Dimitri menstioned. I have
tested JSON_UTIL_PKG.REF_CURSOR_TO_JSON like you said and i am getting below error msg in TOAD

and this is what i am executing
select json_util_pkg.ref_cursor_to_json(cursor(select * from emp))
from dual

Error
ORA-31011: XML parsing failed
ORA-19202: Error occurred in XML processing
LPX-00601: Invalid token in: '*[count(../*[name(../*)=name(.)])=count(../*) and count(../*);1]'
ORA-06512: at "SYS.XMLTYPE", line 187
ORA-06512: at "APEX_APP.JSON_UTIL_PKG", line 269
ORA-06512: at line 1

Thanks for good work
Prabahar

Dimitri Gielis said...

Hi Morten,

Are you on 11g? I tested this on a 10gR1 db... I've seen before 10gR1 isn't great for XML.

Thanks,
Dimitri

Morten Braten said...

@Prabahar: What version of Oracle are you using?

@Dimitri: I am using Oracle XE (10gR2) to develop and test. Do you have an XE (or 10gR2) instance you can try it on?

Prabahar said...

Morten
I am using 11.1.0.6

Bhavin said...

Very good work Morten!

As Dimitri pointed earlier it would be nice to use Supporting Objects to import & test.

I am looking forward to install Demo Application but before I install and do any testing, would this demo application work on Oracle APEX hosted site? (www.apex.oracle.com). If so what changes do I have to make.

Please do reply. Also looking forward to your updated version.

Very good work Morten.

Thanks & Regards,
Bhavin

Morten Braten said...

@Bhavin: This should work on apex.oracle.com (assuming the JSON generation works as expected there).

However, due to security (we are not allowed to call our own procedures directly from the URL at apex.oracle.com), the jqgrid_public_pkg cannot be called, so insert/update/delete will not work (so the grids will in effect be read-only at apex.oracle.com).

Bhavin said...

Hi Morton,

Many thanks for your prompt reply.

I have downloaded the demo application and all the necessary objects (pkg, tables, types, js, css, img etc.).

I am not getting any output for page 2 and page 3 giving error as configuration for ord detail is missing.

I have put the js/css file on page template so not running the jqgrid_pkg.include_static_files region.

Please send me your email id so I can send my login credentials to look at. Your help would be appreciated.

Thanks & Regards,
Bhavin

Morten Braten said...

@Prabahar: I tried this on my own 11.1.0.6 database, and it works.

I think the XSL stylesheet is missing a few characters in your installation (see line 196 of JSON_UTIL_PKG in your database and compare it to the source on disk).

Please try to recompile the package body from SqlPlus, but run the "SET SCAN OFF" command first, to make sure the special characters in the XSLT string are not being misinterpreted. (Will add this to my installation script for the next release.)

Morten Braten said...

@Bhavin: The configuration for the grid on page 3 is missing from the demo files (as Dimitri pointed out), will fix this in next release.

If you want to send me your email address (or the Apex login credentials), you can put it in a comment on this blog article, and then delete the comment immediately afterwards. I will get your email in the comment notification, but it will not be visible to anyone else.

Bhavin said...
This comment has been removed by the author.
Morten Braten said...

@Bhavin: I logged into your workspace and looked at the demo application. You had mis-spelled #WORKSPACE_IMAGES# (as #WORSPACE_IMAGES#, missing the "K") in the page template, I corrected this.

Page 2 is working and displays three grids now (but as noted they are read-only since we can't call URL procedures on apex.oracle.com).

Bhavin said...

Moten,

Many thanks for sorting out the issue.

Thanks & Regards,
Bhavin

SATHEESH KUMAR said...

I have downloaded your JQgrid application. It's amazing.

Thanks for your good work.

mnolan said...
This comment has been removed by the author.
Vinicius said...

hello

How i use without apex?

Do you have some example?


Thank you

Morten Braten said...

@Vinicius: To use the integration kit without Apex, you use the same code but put it in a stand-alone PL/SQL procedure (invoked via mod_plsql) instead of a PL/SQL page region in Apex.

See the example code under "Basic example" on the project page: http://code.google.com/p/jqgrid-for-plsql/

Vinicius said...

Hello

I do exactly equal, but dont data load in the grid.
There are data in array but not show in the grid.

what i can do to resolve this?

Tobin said...

Hi Morten,

I download the package and install it today. But I have many errors when running the install.sql script.
Example:
**************
When compiling "jqgrid_pkg.pkb"
Error(104,7): PLS-00905: object EPDDEV.JQGRID_UTIL_PKG is invalid
Error(104,7): PL/SQL: Statement ignored
Error(169,5): PL/SQL: Statement ignored
Error(169,22): PLS-00905: object EPDDEV.JQGRID_UTIL_PKG is invalid
Error(257,5): PL/SQL: Statement ignored
Error(257,22): PLS-00905: object EPDDEV.JQGRID_UTIL_PKG is invalid
Error(302,3): PLS-00905: object EPDDEV.JQGRID_UTIL_PKG is invalid
Error(302,3): PL/SQL: Statement ignored
Error(373,3): PLS-00905: object EPDDEV.JQGRID_UTIL_PKG is invalid
Error(373,3): PL/SQL: Statement ignored
Error(409,3): PLS-00905: object EPDDEV.JQGRID_UTIL_PKG is invalid
Error(409,3): PL/SQL: Statement ignored
Error(450,5): PLS-00905: object EPDDEV.JQGRID_UTIL_PKG is invalid
Error(450,5): PL/SQL: Statement ignored
Error(561,3): PLS-00905: object EPDDEV.JQGRID_UTIL_PKG is invalid
Error(561,3): PL/SQL: Statement ignored
Error(562,3): PLS-00905: object EPDDEV.JQGRID_UTIL_PKG is invalid
Error(562,3): PL/SQL: Statement ignored
Error(564,3): PLS-00905: object EPDDEV.JQGRID_UTIL_PKG is invalid
Error(564,3): PL/SQL: Statement ignored
**************
compiling "jqgrid_util_pkg.pks"
Error(52,3): PL/SQL: Declaration ignored
Error(53,40): PLS-00201: identifier 'T_DICTIONARY' must be declared
****************
compiling "jqgrid_util_pkg.pkb"
Error: PL/SQL: Compilation unit analysis terminated
Error(1,14): PLS-00905: object EPDDEV.JQGRID_UTIL_PKG is invalid
Error(1,14): PLS-00304: cannot compile body of 'JQGRID_UTIL_PKG' without its specification
****************
Compiling "jqgrid_public_pkg.pkb"
Error(51,5): PLS-00905: object EPDDEV.JQGRID_UTIL_PKG is invalid
Error(51,5): PL/SQL: Statement ignored
Error(117,5): PLS-00905: object EPDDEV.JQGRID_UTIL_PKG is invalid
Error(117,5): PL/SQL: Statement ignored
Error(134,5): PLS-00905: object EPDDEV.JQGRID_UTIL_PKG is invalid
Error(134,5): PL/SQL: Statement ignored
Error(160,3): PL/SQL: Statement ignored
Error(160,12): PLS-00905: object EPDDEV.JQGRID_UTIL_PKG is invalid
Error(173,22): PLS-00201: identifier 'T_DICTIONARY' must be declared
Error(173,22): PL/SQL: Item ignored
Error(201,5): PLS-00905: object EPDDEV.JQGRID_UTIL_PKG is invalid
Error(201,5): PL/SQL: Statement ignored
Error(210,3): PL/SQL: Statement ignored
Error(210,11): PLS-00905: object EPDDEV.JQGRID_UTIL_PKG is invalid
Error(213,5): PLS-00320: the declaration of the type of this expression is incomplete or malformed
Error(213,5): PL/SQL: Statement ignored
Error(214,5): PLS-00320: the declaration of the type of this expression is incomplete or malformed
Error(214,5): PL/SQL: Statement ignored
Error(225,7): PLS-00905: object EPDDEV.JQGRID_UTIL_PKG is invalid
Error(225,7): PL/SQL: Statement ignored

Morten Braten said...

@Tobin: Did you create the types, such as T_DICTIONARY, before compiling the packages?

- Morten

Elie said...

Hi, Morten.

I just now (April 2012) discovered your "jQGrid Integration Kit for PL-SQL and Apex". Is there a version of this more recent than 1.0.0 (March 2010)? I saw a comment you made on the Apex Forum back in 2011 saying that an update would be forthcoming "soon".

I really like what you've done, integrating the jQGrid plugin with Apex. As far as I can tell, even the most recent Apex version (4.1.1) doesn't support the features you've implemented in your package. And so, I'm hoping an updated version (with bug fixes, etc.) might be around the corner.

Thank you very much.

Elie

Morten Braten said...

@Elie: Yes, I have made some rather important enhancements since version 1.0, such as adding popup LOVs that support Apex bind variables and arbitrary column names. I'm actually at version 1.2 by my own count.

I just need to do a final QA review and package it all up, hopefully I'll get that done in the not-to-distant future (yeah, I know I've said "soon" before...!).

- Morten

Elie said...

Hello Morten.

Thanks very much for your quick response.

I, and from the comments I've seen in Google searching, many others as well, appreciate all you are doing, especially at your own time/expense.

I will be checking this blog for any further updates.

I do have two other questions.

1) What you've done, integrating jQGrid with Apex, adds much value to the Apex IDE. As already mentioned, even the newest Apex 4.1.1 release doesn't have this functionality built-in. And so, I'm wondering if Oracle has approached you for permission to add your code into the default Apex release.

2) Have you done, or, perhaps, planning to do, an integration of jQTree with Apex?

Thanks, again, for all your fine work.

Elie

Morten Braten said...

@Elie:

1) No, but they are welcome to use anything from the integration kit should they find it useful.

2) I had not heard about jQTree before you mentioned it. Looks nice. Somebody should make an Apex plugin based on it... :-)

- Morten

Elie said...

Hi, Morten.

Well, hopefully, someone from the Apex team will take notice of your work and approach you about integrtaing your jQGrid with Apex.

I look forward to your next release.

Thanks so much for doing this. It's greatly appreciated.

Elie