I'm working on a few tools to save DBA's time building Stored Procedures for
getting generator values before inserts, etc.

I've got it to a stage that I think should handle most cases.

If the table requires a generator, i.e., has a number-based primary key,
name the generator as follows:
table name: tbl_testtable
gen name: gen_tbl_testtable

If naming is done like that, the generated stored procedure will just bolt
on and run.
Run it using:

out sp_tbl_testtable_ins.sql;
select * from sp_autoproc('tbl_testtable','insert');
exit;

If you have a chance, please try it out, and let me know!

PROCEDURE SOURCE
==================


/* This is autoproc, the Stored Procedure maker
COPYRIGHT (c) Nigel Weeks
nigel@...
No Duplication of any amount of this code is allowed
Simply pass it a table name, and the type of procedure you want, and it'll
output a ROUGH VERSION of it. No promises on being correct straight away ;-)
Type:
tinyinsert:
Provides input parameters to satisfy only the not-null fields
Also uses generator named to convention
Returns success

insert:
Provides input parameters for all table fields
Also uses generator named to convention
Returns Success

tinyupdate:
Provides input parameters to satisfy only the not-null fields
Returns Success

update:
Provides input parameters for all table fields
Returns Success

search:
Provides input parameters for all fields that have 'SRCH' in name
Returns all fields

delete:
Provides input parameters for all primary key fields
Returns Success
*/

declare external function rtrim
cstring(31000)
returns cstring(31000) free_it
entry_point 'IB_UDF_rtrim' MODULE_NAME 'ib_udf';

declare external function ltrim
cstring(31000)
returns cstring(31000) free_it
entry_point 'IB_UDF_ltrim' MODULE_NAME 'ib_udf';


set term !! ;
CREATE PROCEDURE sp_autoproc (
str_tablename VARCHAR(32),
str_ptype VARCHAR(40)
) RETURNS (
str_proc VARCHAR(31000)
)
AS
declare variable str_pre1 VARCHAR(1000);
declare variable str_pre2 VARCHAR(1000);
declare variable str_pre3 VARCHAR(1000);
declare variable str_pre4 VARCHAR(1000);
declare variable str_pre5 VARCHAR(1000);
declare variable str_post1 VARCHAR(1000);
declare variable str_post2 VARCHAR(1000);
declare variable str_post3 VARCHAR(1000);
declare variable str_post4 VARCHAR(1000);
declare variable str_post5 VARCHAR(1000);
declare variable str_genfield VARCHAR(50);
declare variable str_name VARCHAR(32);
declare variable str_type VARCHAR(32);
declare variable str_size VARCHAR(32);
declare variable str_scale VARCHAR(32);
declare variable str_def VARCHAR(32);
declare variable str_null VARCHAR(32);
BEGIN
if(str_tablename = '' or str_tablename IS NULL)THEN
BEGIN
str_proc = 'Table name required.\n Usage: select * from
sp_autoproc(''table name'',''input'')';
SUSPEND;
EXIT;
END

str_tablename = UPPER(str_tablename);
str_ptype = UPPER(str_ptype);
str_proc = '';
if(str_ptype = 'INSERT')THEN
BEGIN
str_pre1='SET TERM !! ;\nCREATE PROCEDURE SP_'||:str_tablename||'_INS (\n';
str_pre2=') RETURNS (\n INT_SUCCESS INTEGER, \n';
str_pre3='\n)\nAS BEGIN\n /* Get the Generator Value */\n';
str_pre4 = '\n /* Now insert the value */\nINSERT INTO '||:str_TABLENAME
|| '(\n ';
str_pre5 = '\n) VALUES (\n ';
str_post1 = '\n);\n /* Send up some results */\n INT_SUCCESS = 0;\n
SUSPEND;\nEND !!\nSET TERM ; !!\n';
/* Get info about the table */
for select rtrim(a.rdb$field_name), rtrim(a.rdb$null_flag),
rtrim(c.rdb$type_name), rtrim(b.rdb$field_length), rtrim(b.rdb$field_scale)
from rdb$relation_fields a, rdb$fields b, rdb$types c
where c.rdb$field_name = 'RDB$FIELD_TYPE'
AND c.rdb$type=b.rdb$field_type
AND b.rdb$field_name = a.rdb$field_source
AND a.rdb$relation_name = :str_tablename
/*AND a.rdb$null_flag = 1*/
ORDER BY a.rdb$field_id
INTO :str_name, :str_null, :str_type, :str_size, :str_scale
DO
BEGIN
if(str_name is null)THEN
str_name = '';
if(str_type is null)THEN
str_type = '';
if(str_size is null)THEN
str_size = '';
if(str_scale is null)THEN
str_scale = '';
if(str_type = 'VARYING')THEN
BEGIN
str_type = 'VARCHAR('||:str_size||')';
END
if(str_type = 'LONG')THEN
str_type = 'INTEGER';
if(str_genfield is null or str_genfield = '')THEN
BEGIN
str_genfield = rtrim(:str_name) || 'IN ';
str_pre2 = :str_pre2 ||' '||rtrim(:str_genfield) ||' '||
rtrim(:str_type);
if(str_type in ('INTEGER','INT64')) THEN
str_pre3 = :str_pre3 || ' '||rtrim(:str_genfield) || ' =
GEN_ID(GEN_'||:str_tablename||',1);\n';
END

str_pre1 = :str_pre1 || ' '||rtrim(:str_name) ||' '|| rtrim(:str_type) ||
',\n';
str_pre4 = :str_pre4 || rtrim(:str_name)||',';

if((rtrim(str_name)||'IN') = str_genfield)THEN
str_pre5 = :str_pre5 || ':'|| rtrim(:str_genfield)||',';
else
str_pre5 = :str_pre5 || ':'|| rtrim(:str_name)||',';
END
str_proc =
:str_pre1||:str_pre2||:str_pre3||:str_pre4||:str_pre5||:str_post1;
str_proc = ltrim(str_proc);
str_proc = rtrim(str_proc);
SUSPEND;
END

END !!
SET TERM ; !!



==================
END PROCEDURE SOURCE

The output of this procedure needs a tiny bit of cleaning up.
Here's a simple script to do it.

CLEANUP SCRIPT
==================
#!/usr/local/bin/php
<?php
if($argv[1] == ""){
echo "Usage: clean.php <autoproc out file>\n";
exit;
} else {
// Connect to the file
$fh = fopen($argv[1],"r");
while($l = fgets($fh)){
// Process this line
$text = str_replace("\\n","\n",$l);
$text = str_replace(", )",")",$text);
$text = str_replace(",\n)","\n)",$text);
if((substr_count($text,"STR_PROC ") == 0) || (substr_count($text," ")
== 0) ) {
if(substr_count($text,"===")){echo "\n";}
else {echo trim($text);}
}
}
}
?>
================
END CLEANUP SCRIPT

--------------------------------------------------------
Nigel Weeks
E-Easy
15 Wellington St. Launceston Tas 7250
Ph. 61 3 6334 6664
Fax. 61 3 6331 7032
Email. nigel@...
Web: www.e-easy.com.au
--------------------------------------------------------