How To...
Grid
Basing a Grid on a Stored Procedure

How to Base a Grid on a Stored Procedure

Description

The Grid component does not allow you to base a Grid on a stored procedure prior to V11. You must specify a SQL Select statement. You can however work around this limitation to a limited degree if your Grid is readonly.

Create a passive link table that is based on the stored procedure.
Create a grid using the native .dbf table (i.e. the passive link table)
In the Grid onGridInitialize event add code to refresh the passive link table.

dim tableName as c
tableName = "PathAlias.ADB_Path\pir_report.dbf"
pr =a5_RefreshPassiveLinkTable(tableName,.f.,.f.)

For a better solution, watch the videos below.

Customizing SQL Insert, Update and Delete Statements - Using Stored Procedure to Update your Database

In a Grid component that is based on a SQL database, the Insert, Update and Delete SQL statements that are executed when a user inserts, updates or deletes a record are automatically computed by Alpha Anywhere.

In some items, a user might want more control over how these SQL statements are executed, or might want to use stored procedures to insert, update or delete records.

The onBeforeSQLCommandExecute and onAfterSQLCommandExecute server side events allow you fully customize the SQL statements that the Grid executes in order to insert, update or delete records.

In addition, another common use for these events is to update some other table at the same time as the update is being performed in the tables the Grid is based on. For example, you might want to add a record to a log table.

When you perform updates against some other table as part of our update, insert or delete, you might want to wrap the entire operation into a single transaction. This is easily done by starting a transaction in the onBeforeSQLCommandExecute event and then either committing or rolling back the transaction in the onAfterSQLCommandExecute event.

These videos discuss these new events.