jQGrid Integration Kit for PL/SQL and Apex version 1.2 available

It's been over two years since I released the initial version of the jQGrid Integration Kit for PL/SQL and Apex.

Since then, I've added some additional features and fixed a few bugs:

Version 1.1

• Procedure include_static_files_once will make sure required JS and CSS files are correctly included
• Support for "popup_lov" column edit type
• Procedure render_grid supports p_read_only parameter that can be used to set read-only dynamically
• Support for some jQGrid built-in formatters based on Oracle column data type
• Lists of values (select list variety, not Popup LOV) now support bind variables and Static LOVs
• Date parser handles dates and times
• Bug Fix: JSON generator now handles NULL parameter values (Thanks to Matt Nolan for spotting this one)

Version 1.2

• Popup LOV now handles Apex bind variables and arbitrary LOV column names
• Support for jQGrid grouping (experimental)

The latest version can be downloaded here.

New installation: Run the install.sql script. Then run all scripts in the upg folder. Copy the files in the js folder to your web server.

Upgrade from previous version: Run all scripts in the upg folder. Copy the files in the js folder to your web server (overwriting any previous version of the same files; or copy to a different folder and change references accordingly).

Note on jQuery and jQGrid versions: The Integration Kit (via the include_static_files_once procedure) references a specific jQuery version (1.3.2). If your web page includes a more recent version of jQuery, you should modify/remove the reference in the Integration Kit to avoid conflicts.

The version of jQGrid bundled with the Integration Kit is version 3.6. At the time of writing, the latest jQGrid version available is 4.3.2; this has not been tested with the Integration Kit.

Have fun, and please put any bug reports into the project issue list.

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