The Table_CreateProcedures() function creates the stored procedures for the specified table on a data source.
Table_CreateProcedures(product_ID, table table_name)
• product_ID – An integer specifying the product ID of the dictionary containing the table for which stored procedures will be created.
• table table_name – The table for which stored procedures will be created.
An integer indicating whether stored procedures were created. The result is 0 if the stored procedures were created successfully. Any other value indicates stored procedures could not be created.
When you use Dexterity to create a table, Dexterity automatically generates several stored procedures for the table, which are used to optimize database performance. Typically, Dexterity generates these stored procedures the first time it opens the table. If you create a table by some means other than Dexterity, you must use this function to generate the stored procedures for the table.
Before you use the Table_CreateProcedures() function, the table must not have any stored procedures that have the same names as those created by Dexterity. If any stored procedures with these names exist for the table, the Table_CreateProcedures() function will fail. Names of stored procedures created by Dexterity have the form zDP_ followed by the table physical name and a code indicating the purpose of the stored procedure. The following table lists the various stored procedures created by Dexterity.
![[spacer]](1p.gif){kind=small}
|
Stored procedure |
Purpose code |
Quantity |
Comments |
|---|---|---|---|
|
Insert |
SI |
One per table |
Created only if the table doesn’t contain text or picture fields. |
|
Delete |
SD |
One per table |
Created only if active locking is not allowed for the table. |
|
Select |
SS |
One for each key |
|
|
First |
F |
One for each key |
|
|
Last |
L |
One for each key |
|
|
Next |
N |
One for each key |
|
|
Unpositioned next |
UN |
One for each non-unique key |
|
For example, the Seller_Data table has the physical name SELLDAT. The table has three keys, one of which is unique. Dexterity creates 13 stored procedures for this table. The stored procedures are listed in the following table.
|
Stored procedure |
Description |
|---|---|
|
zDP_SELLDATSI |
Insert stored procedure |
|
zDP_SELLDATSD |
Delete stored procedure |
|
zDP_SELLDATSS_1 zDP_SELLDATSS_2 zDP_SELLDATSS_3 |
Select stored procedures (one for each key) |
|
zDP_SELLDATF_1 zDP_SELLDATF_2 zDP_SELLDATF_3 |
Select first stored procedure (one for each key) |
|
zDP_SELLDATL_1 zDP_SELLDATL_2 zDP_SELLDATL_3 |
Select last stored procedure (one for each key) |
|
zDP_SELLDATN_1 zDP_SELLDATN_2 zDP_SELLDATN_3 |
Select next stored procedure (one for each key) |
|
zDP_SELLDATUN_2 zDP_SELLDATUN_3 |
Unpositioned next procedures (one for each non-unique key) |
As new versions of Dexterity are released, the auto-generated stored procedures often are enhanced to improve SQL performance. To take advantage of the updated auto-generated stored procedures, you need to update the stored procedures for your application. First, you must remove the existing stored procedures and use Dexterity to create new ones. You can use the Table_DropProcedures() function to drop the existing procedures, and then use the Table_CreateProcedures() function to create the new stored procedures.
|
After you’ve created the new stored procedures, be sure to grant privileges to the appropriate users. |