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.


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!.