jQGrid Integration Kit for Oracle PL/SQL and Application Express (Apex)

Introduction

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.

The jQGrid Integration Kit for PL/SQL is free and open source. Contact me if you want to contribute to the project.

http://jqgrid-for-plsql.googlecode.com/files/jqgrid_for_plsql_screenshot.jpg'>

Features

• Single line of PL/SQL code to render grid
  
• Populate data based on REF CURSOR or SQL text (with or without bind variables)
  
• 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

Installation

Configuration

declare

l_config jqgrid_pkg.t_grid_config;

begin



-- configure grid

l_config.grid.grid_name := 'my_product_info_grid';

l_config.grid.grid_title := 'Product Information';

l_config.grid.grid_height := 200;

l_config.grid.table_name := 'DEMO_PRODUCT_INFO'; -- from the Apex demo application

l_config.grid.pk_column1 := 'PRODUCT_ID';

l_config.grid.read_only := jqgrid_pkg.g_false;



-- save configuration

jqgrid_pkg.save_grid_config (l_config);



-- add all columns from specified table, with appropriate defaults

jqgrid_util_pkg.create_column_defaults (l_config, p_delete_existing_columns => true);



commit;



end;

declare

l_config jqgrid_pkg.t_grid_config;

begin



-- configure grid

l_config.grid.grid_name := 'my_product_info_grid';

l_config.grid.grid_title := 'Product Information';

l_config.grid.grid_height := 200;

l_config.grid.table_name := 'DEMO_PRODUCT_INFO'; -- from the Apex demo application

l_config.grid.pk_column1 := 'PRODUCT_ID';

l_config.grid.read_only := jqgrid_pkg.g_false;



l_config.grid_columns(1).column_name := 'PRODUCT_NAME';

l_config.grid_columns(1).display_name := 'Product Name';

l_config.grid_columns(1).editable := jqgrid_pkg.g_true;



l_config.grid_columns(2).column_name := 'PRODUCT_DESCRIPTION';

l_config.grid_columns(2).display_name := 'Description';

l_config.grid_columns(2).editable := jqgrid_pkg.g_true;

l_config.grid_columns(2).edit_type := jqgrid_pkg.g_edit_type_text_area;



l_config.grid_columns(3).column_name := 'LIST PRICE';

l_config.grid_columns(3).display_name := 'Price ($)';

l_config.grid_columns(3).editable := jqgrid_pkg.g_true;

l_config.grid_columns(3).data_type := jqgrid_pkg.g_data_type_number;



-- and so on for every column you want to display in the grid...



jqgrid_pkg.save_grid_config (l_config);



commit;



end;

Table/Column Reference

• JQGRID.APEX_APPLICATION_ID: When an Apex application ID is specified, the user must be logged into Apex to update the grid
• JQGRID.FOOTER_TEXT: Text and/or HTML printed below the grid
• JQGRID.GRID_HEIGHT: Height of the grid, in pixels
• JQGRID.GRID_MESSAGES: Display status messages to user? (true, false)
• JQGRID.GRID_NAME: The name that identifies the grid. The name will also be used to generate various pieces of Javascript, so make sure the name does not contain any spaces or weird characters. You know what I'm talking about.
• JQGRID.GRID_REMARKS: Internal remarks, not displayed to the user
• JQGRID.GRID_TITLE: Title that will be displayed in the grid header
• JQGRID.HEADER_TEXT: Text and/or HTML printed above the grid
• JQGRID.LOG_LEVEL: Log level for grid (NONE, ERROR, WARN, INFO, DEBUG)
• JQGRID.MULTI_SELECT: Render the checkboxes to select rows? (true, false)
• JQGRID.PUBLIC_API_BASE_URL: The base URL to the PL/SQL package that handles DML operations, including the trailing slash. Only needed if you have set up alternate DADs in the PL/SQL gateway, leave blank in a default installation.
• JQGRID.READ_ONLY: If grid is read-only, editing is disabled (true, false)
• JQGRID.ROW_UPDATE_API_RECTYPE: Record type for row updates via API. This can be a user-defined PL/SQL record type, or a record type based on a table's %ROWTYPE. The record type must contain a field that matches the name of each editable column in the grid.
• JQGRID.SCHEMA_NAME: Schema of primary table for DML operations
• JQGRID.TABLE_NAME: Name of primary table for DML operations
• JQGRID_COLUMN.COLUMN_NAME: The column name, derived from the REF Cursor or SQL statement that populates the grid with data.
• JQGRID_COLUMN.GRID_NAME: Foreign key to grid definition
• JQGRID_COLUMN.UPDATE_COLUMN_NAME: Specify the actual column to update, if different from the displayed column. Relevant for columns which are displayed as select lists (combo boxes).

Usage

Note: These examples assume you have installed the contents of the "demo" folder into your database.

Basic example

Add a page to your Apex application. Add a PL/SQL region to the page, and add the following code to the region:

begin



-- required to include the necessary Javascript and CSS files

jqgrid_pkg.include_static_files_once (p_path => 'path_to_root_folder_for_static_files_on_web_server');



-- note: there are several ways to get a ref cursor

-- here we are calling a function that returns a ref cursor

-- other alternatives include using the CURSOR() function or the OPEN .. FOR statement



jqgrid_pkg.render_grid ('my_product_info_grid', demo_product_pkg.get_products (p_category => 'Video') );



end;

Render (read-only) grid from on-the-fly configuration (not stored in database)

declare

l_config jqgrid_pkg.t_grid_config;

begin



l_config.grid.grid_name := 'employees_custom';

l_config.grid.grid_title := 'Testing jQGrid from PL/SQL !';

l_config.grid.grid_height := 500;



-- note: although the columns are set to be editable (on the client side),

--       there will be no server-side processing

--       unless the grid configuration is saved (persisted) to the database

l_config.grid_columns(1).column_name := 'ENAME';

l_config.grid_columns(1).display_name := 'Employee';

l_config.grid_columns(1).editable := jqgrid_pkg.g_true;



l_config.grid_columns(2).column_name := 'JOB';

l_config.grid_columns(2).display_name := 'Job Position';

l_config.grid_columns(2).editable := jqgrid_pkg.g_true;

l_config.grid_columns(2).edit_type := 'select';

l_config.grid_columns(2).list_of_values := 'STATIC:A:Master;B:Slave;C:Underslave';

l_config.grid_columns(2).data_length := 200;



l_config.grid_columns(3).column_name := 'HAS_MANAGER';

l_config.grid_columns(3).display_name := 'Manager?';

l_config.grid_columns(3).editable := jqgrid_pkg.g_true;

l_config.grid_columns(3).edit_type := 'checkbox';





-- required to include the necessary Javascript and CSS files

jqgrid_pkg.include_static_files_once (p_path => 'path_to_root_folder_for_static_files_on_web_server');



-- render the grid itself

jqgrid_pkg.render_grid (l_config.grid.grid_name, p_sql => 'select empno as row__id, ename, job, ''Y'' as has_manager from emp', p_grid_config => l_config);



end;

Static files

These are the static files (stylesheets and Javascript files) that must be included on a page for the grid to be rendered correctly.

You can include this HTML code either by

• including the HTML code below in your Apex page template (changing the path as appropriate to your web server)
  
• calling the procedure JQGRID_PKG.INCLUDE_STATIC_FILES_ONCE in a PL/SQL region (passing as a parameter to the procedure the path as appropriate to your web server).

Unknown end tag for 









Unknown end tag for 









Unknown end tag for 









Unknown end tag for 

Note that the above references a specific jQuery version. If you are already using a different (newer) version of jQuery on your web page, you should modify (remove) the references accordingly to avoid conflicts.

Known Issues

• None

Change History