SEARCH Borland Online/Developer Support
Tech Notes Index
Working with SQL Databases: triggers, rights, procs, etc. 1)Differences between Interbase server and other SQL servers. (a)Triggers (b)Rights (c)Stored Proc Result Sets (d)Unique row identifiers (e)Exceptions (f)Isolation Levels 2)BDE Isolation level support by driver (a)INTERBASE (b)SYBASE/MSSQL (c)ORACLE (d)INFORMIX (e)DB2 3)TDatabase (a)Special considerations working with specific SQL servers I)Specific params that might affect a connection II)Add params and then change the alias BDE vs. ODBC differences. 4)SQL server specific speed tips (a)ALL DRIVERS (b)SPECIFIC DRIVERS 5)Stored Procedures, Triggers, Generators, Constraints Syntax Differences among the supported SQL servers 6)SQL EXAMPLES (a)INTERBASE 1 simple Interbase stored proc that returns a result set: 1 simple Interbase exception example: (b)INFORMIX 3 Informix stored procedures: 1 user defined Informix exception: (c)SYBASE 2 simple Sybase stored procs Proc (w/raiserror) (d)MSSQL 1 simple MSSQL stored proc: (e)ORACLE 2 simple oracle stored procs 1 proc, package, package body to return a result set (BDE/SQL Links 4.0) (f)DB/2 1)Differences between Interbase server and other SQL servers. (a)Triggers In addition to INSERT, DELETE, and UPDATE Interbase Server offers BEFORE and AFTER options for the standard trigger events. (b)Rights Interbase server assumes that users have rights to all user defined objects unless specified otherwise. (c)Stored Proc Result Sets (for loop to retrieve multiple rows see below) (d)Unique row identifiers Interbase uses Generators to generate unique values where other servers have column types that assign sequential values to a table. An Interbase database can contain any number of generators. Generators are global to the database, and can be used and updated in any transaction. InterBase server will not assign duplicate generator values that span transactions. (e)Exceptions Interbase exceptions are either generated by the server or called from within an sql statement (trigger or stored procedure, for example). A user generated exception is first created using CREATE EXCEPTION and then called by name; EXCEPTION MYCUSTOM_EXCEPT. (f)Isolation levels Snapshot (Repeatable Read) and Read Commited are supported by Interbase Server. Read Commited provides NO RECORD_VERSION and RECORD_VERSION as two optional parameters. RECORD_VERSION reads the latest commited version of a row, even if more recent uncommited version also resides on disk. The BDE supports Repeatable Read, Read Committed, and Dirty Read. If the BDE driver’s SQLPASSTHRU MODE is set to one of the AUTOCOMMIT options then the BDE’s default isolation level is Read Committed and data modifications are auto-commited. These defualts can be overridden by using explicit BDE database transactions. 2)BDE Isolation level support by driver: (a) InterBase Supports Repeatable Read and Read Committed. The wait mode is NO WAIT. (b) Sybase/MSSQL Supports only the server default, Read Committed. (c) Oracle Supports Read Committed and Repeatable Read. A Repeatable Read transaction is always READ ONLY. (d) Informix The BDE supports Repeatable Read, Commited Read, and Dirty Read. Informix also supports a row level isolation level called Cursor Stability which does not correspond to any BDE supported isolation levels. In some cases, when connecting with your Informix database, Your BDE application may override the current Informix transaction isolation settings when connecting to your Informix database. The table below shows how the server default are overridden based on the servers database type. (please note that an Informix database must be either ANSI or WITH LOG to control transactions and the only available isolation level for a non logged database is Dirty Read.) Table 1 Informix Database Isolation Level defaults Database Default isolation level Default isolation level Informix SQL Link ANSI Repeatable Read Commited Read Logged Commited Read Commited Read Non-logged Dirty Read Dirty Read (e) IBM DB2 Supports all BDE transaction isolation levels. Any DB2 isolation levels not supported by the BDE are converted to Read Committed. 3)TDatabase (a)Special considerations working with specific SQL servers During the development cycle it is sometimes easy to forget that Database ALIAS parameters have been set using the Database editor (easily accessed by double clicking on your TDatabase component). I)Specific params that might affect a connection A successful connection to your database server can depend heavily on the following settings (where appropriate to the SQL LINKS driver being used): DATABASE SERVER USER NAME PASSWORD If any database parameters have been set using the Database Editor then these settings will override those set in your database ALIAS and/or DRIVER. Of course if your creating your database ALIAS’ as needed in your application it is only necessary to check the parameters set in your ALIAS and, if not overridden, in the settings for your ALIAS’ driver using the BDE Configuration utility. II)Add params and then change the alias BDE vs. ODBC differences. If you are working with multiple database servers and drivers then keep in mind that not all drivers share the same database parameters. 4)SQL server specific speed tips (a)ALL DRIVERS (b)SPECIFIC DRIVERS (a)ALL DRIVERS (1)ENABLE SCHEMA CACHE, SCHEMA CACHE DIR Enabling and setting the SCHEMA CACHE/SCHEMA CACHE DIR BDE driver parameters can improve application performance by reducing the time spent by the BDE and SQL Links requesting database and table schema info from your server. It is important that the remote schema remain static because the BDE does not "refresh" the local schema info for obvious reasons. (2)BATCH COUNT: The BATCH COUNT controls the number of rows processed in a single ransaction in a BatchMove operation. Adjusting this parameter will also affect the performance of the Data Migration tool. (3)Explicit Transaction control: Using explicit Begin Transaction and End Transaction in your application can be much faster than relying on the BDE SQL Links to control transactions (AUTOCOMMIT). For explicit transaction control use the BDE Configuration utility to set the SQLPASSTHRU MODE parameter to SHARED NOAUTOCOMMIT for your Database Alias and control transactions explicitly in your application by using BEGINTRANSACTION, COMMIT and ROLLBACK. (4)LOCAL SHARE: Set LOCAL SHARE = False on the System page in the BDE configuration utility. This option needs to be set to TRUE only if both BDE and non BDE applications are accessing dBASE or Paradox tables simultaneously. All Borland products use the BDE to access Paradox and dBASE tables, so this option should usually be FALSE. (5)MSSQL and SYBASE drivers (6)TDS PACKET SIZE: Verify that this parameter is set to at least 4k (4096). Increasing this setting can further improve performance. Additional information on setting the TDS PACKET SIZE parameter can be found in the SQLLNK32.HLP file. (7)DRIVER FLAGS: Check to make sure that the DRIVER FLAGS parameter is set to 0 (on driver page of BDE Configuration utility). If it is 2048, queries will execute in Asynchronous mode, which can be slow. (b)SPECIFIC DRIVERS (1)ORACLE, DB/2 and BDE ODBC Socket Tuning the ROWSET SIZE parameter for the alias or the driver in the BDE Configuration utility will specify how many rows are fetched or inserted in a single Server operation. 5)Stored Procedures, Triggers, Generators, Constraints Syntax Differences among the supported SQL servers (a)INFORMIX Informix stored procedures do not have an OUTPUT type. Use open in place of exec to retrieve RETURN values. If you are controlling transactions from within your remote stored procedures and using open for any TQueries or TStoredprocs it is important to set your database ALIAS SQLPASSTHRU MODE to NOT SHARED. The Informix SERIAL column type assigns a sequential integer when a row is inserted. Only one serial column can be defined in a table’s structure and is not automatically unique unless a unique index on the serial column is created. (b)SYBASE Multiple result sets are not supported by the BDE (SYBASE and MSSQL). For Sybase defaults use create default and sp_binddefault. For Sybase rules use create rule and sp_bindrule. Unique, sequential values can be generated by both Sybase and MSSQL servers using the IDENTITY column type (NUMERIC with a scale of 0). The BDE does not support the naming of transactions for Sybase and MSSQL servers via the TDatabase component but remote transactions can coexist with BDE transactions if the remote transactions (controlled from within triggers and/or stored procedures, for example) are named. (c)MSSQL The syntax for triggers, procedures and constraints are basically the same for both the Sybase and MSSQL servers. Please see your specific server documentation for syntax, capabilities and examples. (d)ORACLE Procedures require a cursor type to retrieve multiple rows from a stored procedure (via a package, etc - need to check syntax and test using a build of Delphi 3 that supports the Oracle cursor output param) (e)DB/2 IBM’s DB/2 web site contains specific specfic documentation on creating triggers and stored procs (usually written in C, Cobol, etc.) (f)GENERAL Delphi uses colons to specify parameters in sql passthrough string (Tqueries). Some servers also use this character to specify variable types, reference objects, etc. This might, at first, appear to cause problems with passthrough statements sent via Delphi’s Tquery component. The solution in this situation is to use two colons (for example, ::varname) together instead of just one in your sql statement. 6)SQL EXAMPLES Please see your specific server documentation for syntax, capabilities and examples. (a)INTERBASE 1 simple Interbase stored proc that returns a result set: CREATE PROCEDURE RETURNROWS RETURNS(COMPANY_NAME CHAR(35)) AS BEGIN FOR SELECT COMPANY_NAME FROM CUSTOMER INTO :COMPANY_NAME DO SUSPEND; END The results of this Interbase Stored Procedure can be retrieved by sending SELECT * FROM RETURNROWS via SQL (DbiQExecdirect or a Delphi TQuery, for example). 1 simple Interbase exception example: First create the exception: CREATE EXCEPTION MYCUSTOM_EXCEPT "Error from trigger or procedure example" 1 simple procedure that simply raises an exception: CREATE PROCEDURE ERRORPROC AS BEGIN EXCEPTION MYCUSTOM_EXCEPT; END (b)INFORMIX 3 Informix stored procedures: one input param and one return value 1) create procedure GetFloatVal (InParam1 CHAR) returning float; define OutParam1 float; select floatcol5 into OutParam1 from testidxtable where char2col1 = InParam1; return OutParam1; end procedure; To execute the proc via passthrough sql and pass the string ‘0’: EXECUTE PROCEDURE GetFloatVal('0') Returning a result from an Informix Stored procedure. 2) Procedure that returns one set of results CREATE PROCEDURE my_proc_one (InP FLOAT) RETURNING FLOAT, CHAR(30); DEFINE c_num FLOAT; DEFINE c_name CHAR(30); SELECT customer_no, name INTO c_num, c_name FROM customer WHERE customer_no = InP; RETURN c_num, c_name; END PROCEDURE; Procedure that can return multiple rows 3) CREATE PROCEDURE my_proc_many(InP FLOAT) RETURNING FLOAT, CHAR(30); DEFINE c_num FLOAT; DEFINE c_name CHAR(30); FOREACH SELECT customer_no, name INTO c_num, c_name FROM customer WHERE customer_no < InP RETURN c_num, c_name WITH RESUME; END FOREACH; END PROCEDURE; Important Points to note about the two previous examples To get multiple rows back you need 1) a FOREACH & END FOREACH loop must be wrapped around the SQL statement. 2) The RETURN statement needs to end with WITH RESUME. 3) Notice no semi-colon between the end of the SELECT statement and the RETURN clause. for more information on Informix and stored procedure usage with the Borland Database Engine see the BDE Online Reference (search for Informix Driver) Table declaration used for this procedure CREATE TABLE customer ( customer_no FLOAT, name VARCHAR(30,0), street VARCHAR(30,0), city VARCHAR(15,0), state VARCHAR(20,0), zip VARCHAR(10,0), country VARCHAR(20,0), phone VARCHAR(15,0), first_contact DATE ) quote from Informix reference guide on Returning More Than One Set of Values from a Procedure: "If your procedure performs a select that can return more than one row from the database or if you return values from inside a loop, you must use the WITH RESUME keywords in the RETURN statement. Using a RETURN...WITH RESUME STATEMENT causes the value or values to be returned to the calling program or procedure. After the calling program receives the values, execution returns to the statement immediately following the RETURN...WITH RESUME statement." For Delphi and C++ Builder you can only have one of these multiple row result sets per stored procedure. JK 5.13.98 1 user defined Informix exception: RAISE EXCEPTION 12345,0,’my exception’; The exception can then be trapped by an Informix ON EXCEPTION statement. You can also use your Delphi/BDE application to trap the defined error code. (c)SYBASE 3 simple Sybase stored procs: Proc 1 (w/float input and output params) CREATE PROCEDURE RETGETFL @INVAR float, @OUTVAR float OUTPUT AS SELECT @OUTVAR=@INVAR+99 Proc 2 (w/float input and output params) CREATE PROCEDURE RETGETCHAR @INVAR varchar(20), @OUTVAR varchar(20) OUTPUT AS SELECT @OUTVAR=@INVAR+' plus output info' Proc 3 (w/raiserror) CREATE PROCEDURE RAISE AS RAISERROR 33333 'I am a raised error' The raiserror code and message can be trapped in a Delphi exception handler (d)MSSQL 1 simple MSSQL stored proc: Proc 1 (w/raiserror) CREATE PROCEDURE RAISETEST AS BEGIN RAISERROR ('ERROR has been raised',16,-1) END (e)ORACLE 2 simple oracle stored procs: 1 proc, package, package body to return a result set Proc 1) In Oracle's SQL Plus program you would do the following to create, and execute the Oracle stored proc: (Notice the distinction and difference between Oracle PL/SQL statements issued in Oracle's SQL Plus vs. sql PASSTHROUGH statements) CREATE OR REPLACE PROCEDURE XYZ (X IN NUMBER, C IN VARCHAR2) 2 AS 3 BEGIN 4 UPDATE DELPHI1.CLIENT 5 SET FLD2 = C 6 WHERE FLD1=X; 7 END XYZ; EXECUTE XYZ(2,'efg'); Using an sql PASSTHROUGH statement via the BDE you would execute the Oracle stored proc by entering the following either into the TQuery or the DBD SQL editor: BEGIN XYZ(2,'efg); END; Proc 2) create procedure xyz (x out varchar2) as begin x:='abcde'; end xyz; execute the proc via sqlplus: sql>variable x varchar2(20); sql>execute xyz(:x); sql>print x Proc 3 [w/package, package body]) ORACLE STORED PROCEDURE RESULT SETS BDE/SQL LINKS 4.0 is required Test Oracle package, package body and stored procedure used to return a cursor and data to the client app: create or replace package test_sp10_pack is type rctl is ref cursor return CUSTOMER%rowtype; procedure test_sp10_pack_sp ( rc1 in out rctl ); end ; create or replace package body test_sp10_pack as procedure test_sp10_pack_sp ( rc1 in out rctl ) as begin open rc1 for select * from CUSTOMER ; end; end; create or replace procedure test_sp10 ( rc1 in out test_sp10_pack.rctl) as begin open rc1 for select * from CUSTOMER; end; test_sp10: Use params editor to ADD the parameter- Parameter type INPUT/OUTPUT Data type CURSOR =================================================================== ERRORS: =================================================================== Delphi StoredProc cursor parameter type must be specified as INPUT/OUTPUT. If the StoredProc's cursor parameter is set to OUTPUT then an error similar to the following will come up when the StoredProc is opened: Project myproj.exe raised exception class EDBEngineError with message 'General SQL error. ORA-01001: invalid cusrsor ORA-06512: at "DELPHI1.TEST_SP10", line 3 ORA-06512: at line1' Process stopped. Use Step or Run to continue. (f)DB/2 IBM’s DB/2 web site contains specific specfic documentation on creating triggers and stored procs (usually written in C, Cobol, etc.) PUB ALL LINK TECH BC 5/14/98 (updated)
1)Differences between Interbase server and other SQL servers. (a)Triggers (b)Rights (c)Stored Proc Result Sets (d)Unique row identifiers (e)Exceptions (f)Isolation Levels 2)BDE Isolation level support by driver (a)INTERBASE (b)SYBASE/MSSQL (c)ORACLE (d)INFORMIX (e)DB2 3)TDatabase (a)Special considerations working with specific SQL servers I)Specific params that might affect a connection II)Add params and then change the alias BDE vs. ODBC differences. 4)SQL server specific speed tips (a)ALL DRIVERS (b)SPECIFIC DRIVERS 5)Stored Procedures, Triggers, Generators, Constraints Syntax Differences among the supported SQL servers 6)SQL EXAMPLES (a)INTERBASE 1 simple Interbase stored proc that returns a result set: 1 simple Interbase exception example: (b)INFORMIX 3 Informix stored procedures: 1 user defined Informix exception: (c)SYBASE 2 simple Sybase stored procs Proc (w/raiserror) (d)MSSQL 1 simple MSSQL stored proc: (e)ORACLE 2 simple oracle stored procs 1 proc, package, package body to return a result set (BDE/SQL Links 4.0) (f)DB/2 1)Differences between Interbase server and other SQL servers. (a)Triggers In addition to INSERT, DELETE, and UPDATE Interbase Server offers BEFORE and AFTER options for the standard trigger events. (b)Rights Interbase server assumes that users have rights to all user defined objects unless specified otherwise. (c)Stored Proc Result Sets (for loop to retrieve multiple rows see below) (d)Unique row identifiers Interbase uses Generators to generate unique values where other servers have column types that assign sequential values to a table. An Interbase database can contain any number of generators. Generators are global to the database, and can be used and updated in any transaction. InterBase server will not assign duplicate generator values that span transactions. (e)Exceptions Interbase exceptions are either generated by the server or called from within an sql statement (trigger or stored procedure, for example). A user generated exception is first created using CREATE EXCEPTION and then called by name; EXCEPTION MYCUSTOM_EXCEPT. (f)Isolation levels Snapshot (Repeatable Read) and Read Commited are supported by Interbase Server. Read Commited provides NO RECORD_VERSION and RECORD_VERSION as two optional parameters. RECORD_VERSION reads the latest commited version of a row, even if more recent uncommited version also resides on disk. The BDE supports Repeatable Read, Read Committed, and Dirty Read. If the BDE driver’s SQLPASSTHRU MODE is set to one of the AUTOCOMMIT options then the BDE’s default isolation level is Read Committed and data modifications are auto-commited. These defualts can be overridden by using explicit BDE database transactions. 2)BDE Isolation level support by driver: (a) InterBase Supports Repeatable Read and Read Committed. The wait mode is NO WAIT. (b) Sybase/MSSQL Supports only the server default, Read Committed. (c) Oracle Supports Read Committed and Repeatable Read. A Repeatable Read transaction is always READ ONLY. (d) Informix The BDE supports Repeatable Read, Commited Read, and Dirty Read. Informix also supports a row level isolation level called Cursor Stability which does not correspond to any BDE supported isolation levels. In some cases, when connecting with your Informix database, Your BDE application may override the current Informix transaction isolation settings when connecting to your Informix database. The table below shows how the server default are overridden based on the servers database type. (please note that an Informix database must be either ANSI or WITH LOG to control transactions and the only available isolation level for a non logged database is Dirty Read.) Table 1 Informix Database Isolation Level defaults Database Default isolation level Default isolation level Informix SQL Link ANSI Repeatable Read Commited Read Logged Commited Read Commited Read Non-logged Dirty Read Dirty Read (e) IBM DB2 Supports all BDE transaction isolation levels. Any DB2 isolation levels not supported by the BDE are converted to Read Committed. 3)TDatabase (a)Special considerations working with specific SQL servers During the development cycle it is sometimes easy to forget that Database ALIAS parameters have been set using the Database editor (easily accessed by double clicking on your TDatabase component). I)Specific params that might affect a connection A successful connection to your database server can depend heavily on the following settings (where appropriate to the SQL LINKS driver being used): DATABASE SERVER USER NAME PASSWORD If any database parameters have been set using the Database Editor then these settings will override those set in your database ALIAS and/or DRIVER. Of course if your creating your database ALIAS’ as needed in your application it is only necessary to check the parameters set in your ALIAS and, if not overridden, in the settings for your ALIAS’ driver using the BDE Configuration utility. II)Add params and then change the alias BDE vs. ODBC differences. If you are working with multiple database servers and drivers then keep in mind that not all drivers share the same database parameters. 4)SQL server specific speed tips (a)ALL DRIVERS (b)SPECIFIC DRIVERS (a)ALL DRIVERS (1)ENABLE SCHEMA CACHE, SCHEMA CACHE DIR Enabling and setting the SCHEMA CACHE/SCHEMA CACHE DIR BDE driver parameters can improve application performance by reducing the time spent by the BDE and SQL Links requesting database and table schema info from your server. It is important that the remote schema remain static because the BDE does not "refresh" the local schema info for obvious reasons. (2)BATCH COUNT: The BATCH COUNT controls the number of rows processed in a single ransaction in a BatchMove operation. Adjusting this parameter will also affect the performance of the Data Migration tool. (3)Explicit Transaction control: Using explicit Begin Transaction and End Transaction in your application can be much faster than relying on the BDE SQL Links to control transactions (AUTOCOMMIT). For explicit transaction control use the BDE Configuration utility to set the SQLPASSTHRU MODE parameter to SHARED NOAUTOCOMMIT for your Database Alias and control transactions explicitly in your application by using BEGINTRANSACTION, COMMIT and ROLLBACK. (4)LOCAL SHARE: Set LOCAL SHARE = False on the System page in the BDE configuration utility. This option needs to be set to TRUE only if both BDE and non BDE applications are accessing dBASE or Paradox tables simultaneously. All Borland products use the BDE to access Paradox and dBASE tables, so this option should usually be FALSE. (5)MSSQL and SYBASE drivers (6)TDS PACKET SIZE: Verify that this parameter is set to at least 4k (4096). Increasing this setting can further improve performance. Additional information on setting the TDS PACKET SIZE parameter can be found in the SQLLNK32.HLP file. (7)DRIVER FLAGS: Check to make sure that the DRIVER FLAGS parameter is set to 0 (on driver page of BDE Configuration utility). If it is 2048, queries will execute in Asynchronous mode, which can be slow. (b)SPECIFIC DRIVERS (1)ORACLE, DB/2 and BDE ODBC Socket Tuning the ROWSET SIZE parameter for the alias or the driver in the BDE Configuration utility will specify how many rows are fetched or inserted in a single Server operation. 5)Stored Procedures, Triggers, Generators, Constraints Syntax Differences among the supported SQL servers (a)INFORMIX Informix stored procedures do not have an OUTPUT type. Use open in place of exec to retrieve RETURN values. If you are controlling transactions from within your remote stored procedures and using open for any TQueries or TStoredprocs it is important to set your database ALIAS SQLPASSTHRU MODE to NOT SHARED. The Informix SERIAL column type assigns a sequential integer when a row is inserted. Only one serial column can be defined in a table’s structure and is not automatically unique unless a unique index on the serial column is created. (b)SYBASE Multiple result sets are not supported by the BDE (SYBASE and MSSQL). For Sybase defaults use create default and sp_binddefault. For Sybase rules use create rule and sp_bindrule. Unique, sequential values can be generated by both Sybase and MSSQL servers using the IDENTITY column type (NUMERIC with a scale of 0). The BDE does not support the naming of transactions for Sybase and MSSQL servers via the TDatabase component but remote transactions can coexist with BDE transactions if the remote transactions (controlled from within triggers and/or stored procedures, for example) are named. (c)MSSQL The syntax for triggers, procedures and constraints are basically the same for both the Sybase and MSSQL servers. Please see your specific server documentation for syntax, capabilities and examples. (d)ORACLE Procedures require a cursor type to retrieve multiple rows from a stored procedure (via a package, etc - need to check syntax and test using a build of Delphi 3 that supports the Oracle cursor output param) (e)DB/2 IBM’s DB/2 web site contains specific specfic documentation on creating triggers and stored procs (usually written in C, Cobol, etc.) (f)GENERAL Delphi uses colons to specify parameters in sql passthrough string (Tqueries). Some servers also use this character to specify variable types, reference objects, etc. This might, at first, appear to cause problems with passthrough statements sent via Delphi’s Tquery component. The solution in this situation is to use two colons (for example, ::varname) together instead of just one in your sql statement. 6)SQL EXAMPLES Please see your specific server documentation for syntax, capabilities and examples. (a)INTERBASE 1 simple Interbase stored proc that returns a result set: CREATE PROCEDURE RETURNROWS RETURNS(COMPANY_NAME CHAR(35)) AS BEGIN FOR SELECT COMPANY_NAME FROM CUSTOMER INTO :COMPANY_NAME DO SUSPEND; END The results of this Interbase Stored Procedure can be retrieved by sending SELECT * FROM RETURNROWS via SQL (DbiQExecdirect or a Delphi TQuery, for example). 1 simple Interbase exception example: First create the exception: CREATE EXCEPTION MYCUSTOM_EXCEPT "Error from trigger or procedure example" 1 simple procedure that simply raises an exception: CREATE PROCEDURE ERRORPROC AS BEGIN EXCEPTION MYCUSTOM_EXCEPT; END (b)INFORMIX 3 Informix stored procedures: one input param and one return value 1) create procedure GetFloatVal (InParam1 CHAR) returning float; define OutParam1 float; select floatcol5 into OutParam1 from testidxtable where char2col1 = InParam1; return OutParam1; end procedure; To execute the proc via passthrough sql and pass the string ‘0’: EXECUTE PROCEDURE GetFloatVal('0') Returning a result from an Informix Stored procedure. 2) Procedure that returns one set of results CREATE PROCEDURE my_proc_one (InP FLOAT) RETURNING FLOAT, CHAR(30); DEFINE c_num FLOAT; DEFINE c_name CHAR(30); SELECT customer_no, name INTO c_num, c_name FROM customer WHERE customer_no = InP; RETURN c_num, c_name; END PROCEDURE; Procedure that can return multiple rows 3) CREATE PROCEDURE my_proc_many(InP FLOAT) RETURNING FLOAT, CHAR(30); DEFINE c_num FLOAT; DEFINE c_name CHAR(30); FOREACH SELECT customer_no, name INTO c_num, c_name FROM customer WHERE customer_no < InP RETURN c_num, c_name WITH RESUME; END FOREACH; END PROCEDURE; Important Points to note about the two previous examples To get multiple rows back you need 1) a FOREACH & END FOREACH loop must be wrapped around the SQL statement. 2) The RETURN statement needs to end with WITH RESUME. 3) Notice no semi-colon between the end of the SELECT statement and the RETURN clause. for more information on Informix and stored procedure usage with the Borland Database Engine see the BDE Online Reference (search for Informix Driver) Table declaration used for this procedure CREATE TABLE customer ( customer_no FLOAT, name VARCHAR(30,0), street VARCHAR(30,0), city VARCHAR(15,0), state VARCHAR(20,0), zip VARCHAR(10,0), country VARCHAR(20,0), phone VARCHAR(15,0), first_contact DATE ) quote from Informix reference guide on Returning More Than One Set of Values from a Procedure: "If your procedure performs a select that can return more than one row from the database or if you return values from inside a loop, you must use the WITH RESUME keywords in the RETURN statement. Using a RETURN...WITH RESUME STATEMENT causes the value or values to be returned to the calling program or procedure. After the calling program receives the values, execution returns to the statement immediately following the RETURN...WITH RESUME statement." For Delphi and C++ Builder you can only have one of these multiple row result sets per stored procedure. JK 5.13.98 1 user defined Informix exception: RAISE EXCEPTION 12345,0,’my exception’; The exception can then be trapped by an Informix ON EXCEPTION statement. You can also use your Delphi/BDE application to trap the defined error code. (c)SYBASE 3 simple Sybase stored procs: Proc 1 (w/float input and output params) CREATE PROCEDURE RETGETFL @INVAR float, @OUTVAR float OUTPUT AS SELECT @OUTVAR=@INVAR+99 Proc 2 (w/float input and output params) CREATE PROCEDURE RETGETCHAR @INVAR varchar(20), @OUTVAR varchar(20) OUTPUT AS SELECT @OUTVAR=@INVAR+' plus output info' Proc 3 (w/raiserror) CREATE PROCEDURE RAISE AS RAISERROR 33333 'I am a raised error' The raiserror code and message can be trapped in a Delphi exception handler (d)MSSQL 1 simple MSSQL stored proc: Proc 1 (w/raiserror) CREATE PROCEDURE RAISETEST AS BEGIN RAISERROR ('ERROR has been raised',16,-1) END (e)ORACLE 2 simple oracle stored procs: 1 proc, package, package body to return a result set Proc 1) In Oracle's SQL Plus program you would do the following to create, and execute the Oracle stored proc: (Notice the distinction and difference between Oracle PL/SQL statements issued in Oracle's SQL Plus vs. sql PASSTHROUGH statements) CREATE OR REPLACE PROCEDURE XYZ (X IN NUMBER, C IN VARCHAR2) 2 AS 3 BEGIN 4 UPDATE DELPHI1.CLIENT 5 SET FLD2 = C 6 WHERE FLD1=X; 7 END XYZ; EXECUTE XYZ(2,'efg'); Using an sql PASSTHROUGH statement via the BDE you would execute the Oracle stored proc by entering the following either into the TQuery or the DBD SQL editor: BEGIN XYZ(2,'efg); END; Proc 2) create procedure xyz (x out varchar2) as begin x:='abcde'; end xyz; execute the proc via sqlplus: sql>variable x varchar2(20); sql>execute xyz(:x); sql>print x Proc 3 [w/package, package body]) ORACLE STORED PROCEDURE RESULT SETS BDE/SQL LINKS 4.0 is required Test Oracle package, package body and stored procedure used to return a cursor and data to the client app: create or replace package test_sp10_pack is type rctl is ref cursor return CUSTOMER%rowtype; procedure test_sp10_pack_sp ( rc1 in out rctl ); end ; create or replace package body test_sp10_pack as procedure test_sp10_pack_sp ( rc1 in out rctl ) as begin open rc1 for select * from CUSTOMER ; end; end; create or replace procedure test_sp10 ( rc1 in out test_sp10_pack.rctl) as begin open rc1 for select * from CUSTOMER; end; test_sp10: Use params editor to ADD the parameter- Parameter type INPUT/OUTPUT Data type CURSOR =================================================================== ERRORS: =================================================================== Delphi StoredProc cursor parameter type must be specified as INPUT/OUTPUT. If the StoredProc's cursor parameter is set to OUTPUT then an error similar to the following will come up when the StoredProc is opened: Project myproj.exe raised exception class EDBEngineError with message 'General SQL error. ORA-01001: invalid cusrsor ORA-06512: at "DELPHI1.TEST_SP10", line 3 ORA-06512: at line1' Process stopped. Use Step or Run to continue. (f)DB/2 IBM’s DB/2 web site contains specific specfic documentation on creating triggers and stored procs (usually written in C, Cobol, etc.) PUB ALL LINK TECH BC 5/14/98 (updated)
