The Acu4GL for Microsoft SQL Server product checks for certain stored procedures when opening a file, and will use them in certain circumstances. These stored procedures are named:
xxx_insert
xxx_delete
xxx_read
xxx_update
xxx_startnnn
(where xxx is the name of the table being accessed, and nnn is the key number to start on)
The stored procedures must be in the same database as the table they reference.
xxx_delete is used to DELETE a record from the file. The parameters passed to the stored procedure are the values of the primary key, in the order they are listed in the XFD.
xxx_read is used to read a random record (READ, not READ NEXT or READ PREVIOUS). The parameters passed to the stored procedure are the values of the primary key, in the order they are listed in the XFD. The expected rowset is the columns in the first table (if secondary tables are used), or the columns of the table (if secondary tables are not necessary).
xxx_update is used to REWRITE a record from the file. The parameters passed to the stored procedure are the values of all the columns in the row, in the order of the columns in the database. The timestamp column and identity column (if present in the table) are not passed to the stored procedure.
xxx_startnnn is used to START a file. The nnn value is the key number to start on, and will be 0 filled. For example, the start procedure for the primary key for table mytab will be mytab_start000.)
Since there can be up to 119 alternate keys, the Acu4GL product does not search for a start procedure unless, or until, it is used. The parameters passed to the stored procedure are a 2-char mode (it is a varchar(2) field), with one of the following values: >,>=, =, <=, <. The rest of the parameters are the columns of the key used to start. Because the ANSI specification for START includes information about the size of the key being used (and in particular allows partial keys), the start procedure is only used if an entire key is given to the start verb. This procedure is also special in that it does not return data, but needs to raise an error condition if the start fails. The way to raise the error condition from within the stored procedure is to include something like the following:
raiserror 22006 "Record not found"
The code 22006 is very important. It is the code searched for in setting the error condition from within the Acu4GL product. If you use a different number, your starts may succeed when they should actually fail.
For example, given the following XFD (which is from newftest.cbl):
XFD,03,FTEST2-FILE,FTESTDAT # ftestdat.xfd - generated by ACUCOBOL-GT v4.2 Alpha 1 (8/22/99) # Generated Sun Aug 22 07:54:28 1999 00031,00031,003 01,0,004,00000 01 FTEST2-KEY 01,1,004,00004 02 FTEST2-KEY1-SEG1 FTEST2-KEY1-SEG2 01,0,004,00008 01 FTEST2-ALTKEY2 000 0006,00006 00000,00004,16,00004,+00,000,000,FTEST2-KEY 00004,00002,16,00002,+00,000,000,FTEST2-KEY1-SEG1 00006,00002,16,00002,+00,000,000,FTEST2-KEY1-SEG2 00008,00004,16,00004,+00,000,000,FTEST2-ALTKEY2 00012,00009,00,00009,+00,000,000,FTEST2-NUMBER 00021,00010,16,00010,+00,000,000,FTEST2-INFO
You might want to create the following stored procedures:
create procedure ftestdat_insert @ft2_key char(4), @ft2_key1_seg1 char(2), @ft2_key1_seg2 char(2), @ft2_altkey2 char(4), @ft2_number char(9), @ft2_info char(10) as insert into ftestdat (ftest2_key, ftest2_key1_seg1, ftest2_key1_seg2, ftest2_altkey2, ftest2_number, ftest2_info) values (@ft2_key, @ft2_key1_seg1, @ft2_key1_seg2, @ft2_altkey2, @ft2_number, @ft2_info) grant execute on ftestdat_insert to public create procedure ftestdat_delete @ft2_key char(4) as delete from ftestdat where ftest2_key = @ft2_key grant execute on ftestdat_delete to public create procedure ftestdat_update @ft2_key char(4), @ft2_key1_seg1 char(2), @ft2_key1_seg2 char(2), @ft2_altkey2 char(4), @ft2_number char(9), @ft2_info char(10) as update ftestdat set ftest2_key = @ft2_key, ftest2_key1_seg1 = @ft2_key1_seg1, ftest2_key1_seg2 = @ft2_key1_seg2, ftest2_altkey2 = @ft2_altkey2, ftest2_number = @ft2_number, ftest2_info = @ft2_info where ftest2_key = @ft2_key grant execute on ftestdat_update to public create procedure ftestdat_start001 @mode varchar(2), @ft2_key1_seg1 char(2), @ft2_key1_seg2 char(2) as if exists (select 1 from ftestdat where (ftest2_key1_seg1 = @ft2_key1_seg1 and ((@mode = ">=" and ftest2_key1_seg2 >=@ft2_key1_seg2) or (@mode = ">" and ftest2_key1_seg2 > @ft2_key1_seg2) or (@mode = "=" and ftest2_key1_seg2 = @ft2_key1_seg2) or (@mode = "<" and ftest2_key1_seg2 < @ft2_key1_seg2) or (@mode = "<=" and ftest2_key1_seg2 <= @ft2_key1_seg2)))) return if exists (select 1 from ftestdat where (((@mode = ">=" or @mode = ">") and ftest2_key1_seg1 > @ft2_key1_seg1) or ((@mode = "<=" or @mode = "<") and ftest2_key1_seg1 < @ft2_key1_seg1))) return raiserror 22006 "Record not found" grant execute on ftestdat_start001 to public
If the stored procedure sp_AcuInit exists in the master database, then it is executed when the connection is made to the server. This is a procedure you can set up to do customized initialization. For example, you can use this stored procedure to remove stale locks by calling sp_AcuRemoveUnusedLocks_1 (which is installed when all the other Acu4GL stored procedures are installed - see your Microsoft SQL Server manual for information). This stored procedure is not passed by any parameters, and is not expected to return any results. However, if it causes an error to occur, then the login will fail, and the user will be denied access.