Useful SQL Stored Procedures as part of standard firebird installation

classic Classic list List threaded Threaded
2 messages Options
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Useful SQL Stored Procedures as part of standard firebird installation

Slavomir Skopalik
Hi,

will be possible to extend firebird installation by SQL stored
procedures that will solve common problems?

It will be like UDF (let say in directory SQL), and every one can
include/use this SQL like fbudf.

Examples of useful:
SP that returns complete metadata for another SP.
SP that set NULL/NOT NULL for column and works on FB2.5 and FB3.
Some examples from our development:

-- Drop Primary Key for given table
CREATE OR ALTER PROCEDURE MASA$DropPrimaryKey(Relation RDB$Relation_Name)
AS
DECLARE VARIABLE cn VARCHAR(500)=NULL;
BEGIN
   SELECT TRIM(rc.rdb$constraint_name)
     FROM rdb$Relation_Constraints RC LEFT JOIN RDB$Indices I ON
RC.rdb$Index_Name=I.rdb$Index_Name
     WHERE I.rdb$Relation_Name=:Relation AND
RC.rdb$Constraint_Type='PRIMARY KEY'
     INTO :cn;
   IF (cn IS NOT NULL) THEN BEGIN
     SQL='ALTER TABLE ' || TRIM(Relation) ||' DROP CONSTRAINT '||cn||';';
     EXECUTE STATEMENT SQL;
   END
END

Slavek

--
Ing. Slavomir Skopalik
Executive Head
Elekt Labs s.r.o.
Collection and evaluation of data from machines and laboratories
by means of system MASA (http://www.elektlabs.cz/m2demo)
-----------------------------------------------------------------
Address:
Elekt Labs s.r.o.
Chaloupky 158
783 72 Velky Tynec
Czech Republic
---------------------------------------------------------------
Mobile: +420 724 207 851
icq:199 118 333
skype:skopaliks
e-mail:[hidden email]
http://www.elektlabs.cz



------------------------------------------------------------------------------
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot
Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Useful SQL Stored Procedures as part of standard firebird installation

Paul Reeves
On Tue, 1 Aug 2017 11:49:42 +0200 Slavomir Skopalik wrote

> Hi,
>
> will be possible to extend firebird installation by SQL stored
> procedures that will solve common problems?
>
> It will be like UDF (let say in directory SQL), and every one can
> include/use this SQL like fbudf.
>


It is a great idea. I'm sure I'm not the only one to have developed a
few SPs over the years that might be useful. However, it needs
someone to organise it, curate the contents and create QA tests to
verify that the stored procedures work correctly with each new release.
It is not too much work, but it does require commitment if the
intention is that these procedures should ship with Firebird. Bit rot
can set in very quickly if no-one is around to maintain code.

Perhaps the first step would be to create a github project to allow
people to contribute.


Paul

--
Paul Reeves
http://www.ibphoenix.com
Supporting users of Firebird
 

------------------------------------------------------------------------------
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot
Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Loading...