[fpc-pascal] Re: [Firebird-devel] FreePascal as an embedded language in Firebird: possible and desirable?

Tony Whyman tony.whyman at mccallumwhyman.com
Wed Jul 27 13:11:52 CEST 2011


A very interesting proposal. Having recently ported IBX from
Delphi/InterBase to Free Pascal/Lazarus/Firebird, I know that both
Firebird and Free Pascal are great products and could and should work
well together for server side as well as client side functions. The
question is what benefit do you get from further integration beyond just
using Free Pascal to write a Firebird UDF?

Firstly, I would agree that if you are going to embed Pascal in Firebird
Stored Procedures (or even just to write UDFs)  then Free Pascal is the
right choice over Delphi. It's not just that it is Open Source. Free
Pascal targets far more processor architectures than Delphi and its
support libraries are available for many more platforms.

Currently, the main reason I do not make much use of UDFs is the extra
work involved in testing, configuring and deploying a shared library/DLL
separate from the database itself. Unless there is a big reason for
writing a server side UDF then it is often easier to find another way
using the Store Procedure Language or just to make it a client side
function. On the other hand, if you could write a Firebird Stored
Procedure where the procedure body was actually Pascal and that Pascal
code was initially syntax checked and then subject to a "just in time"
compilation when first used, it would make it much easier to write and
deploy server side functions - and to move existing (Pascal) client side
functions to server side. It would thus seem to be a valid requirement.

If you go down this path, then my understanding is that you are
proposing something like the following (with a lot of elaboration):

1. An extension to the CREATE/ALTER Procedure syntax to declare a
procedure body as being Pascal rather than the Firebird stored procedure

2. A means to declared "used" Pascal Library Units in this declaration.
It would also be useful to declare a reference to Firebird Transaction
and Session Variables, as well as local Pascal Variables.

3. A well defined mapping between Firebird data types and Pascal data
types for both input and output parameters. As Pascal is strongly typed
this should not be problem, even for Blobs, which map naturally on to a
TStream type.

4. Integration of Exception Handling (see other discussions).

5. Syntax checking, using the Free Pascal compiler when an embedded
Pascal Stored Procedure is defined.

6. Just in time compilation of the embedded procedure on first use
(after create/alter) into a shared library/DLL which is then effectively
a dynamically generated UDF library. A JIT approach is important because
the database can be moved between processor architectures/platforms and
it is important to be able to recompile automatically for the new platform.

I assume that you are not proposing embedded SQL in the Pascal source as
this would require a new set of preprocessors/support libraries and I
can't see the benefit of that unless it was also for client side use.

Otherwise, I can see the above enabling applications that could be done
with UDFs but require much less effort to develop and deploy with an
embedded approach i.e. improving further the usability of Firebird.


Tony Whyman

On 27/07/11 08:10, Jim wrote:
> Hi FreePascal and Firebird people,
> (Cross-posted to the FPC and Firebird Development mailing lists)
> 1. Background
> =============
> For the upcoming Firebird 3.0 database server release, developers are
> working on allowing external languages (at first Java) for writing
> stored procedures and functions.
> I found this, rather old, reference:
> http://web.firebirdsql.org/index.php?op=devel&sub=plugins&id=external_java
> FreePascal (FPC) is an object oriented variant of Pascal that looks very
> much like Delphi (in fact, there's a Delphi compatibility switch so you
> can port your existing code).
> Would it be possible and desirable to have FreePascal as an embedded
> stored procedure/trigger language in some future release of Firebird? (I
> can't recall this having been brought up before, so I just start the
> ball rolling).
> 2. End user considerations
> ==========================
> 2.1 A lot of developers that use Firebird program in Delphi. As
> FreePascal syntax is (almost) identical, these developers can easily
> port their code between the database and the application.
> 2.2 FreePascal may be easier to learn than Java for some people and
> would make a nice alternative as opposed to e.g. including C or C++
> 2.3 FreePascal might not run/is not supported for some platforms that
> Firebird 3 will be released for. Perhaps HP-UX? Firebird 2.5 runs on
> Windows (x86/x64),  Linux versions (x86/x64), Solaris (Sparc and Intel),
> HP-UX (PA-Risc) and Mac OSX.
> 3. Development considerations
> ========================
> I only know enough to make wild guesses... that doesn't stop me from
> making them, though.
> 3.1 Obviously, Firebird must allow running compiled code (as opposed to
> running code in a Java Virtual Machine), presumably in a form of dynamic
> library.
> 3.2 Firebird must be modified to include (a stripped down version of?)
> the FPC compiler (or store the location of the compiler) and call it
> whenever the FreePascal source code in a procedure is changed.
> This might not be necessary for production servers as long as embedded
> Pascal code is compiled on developer machines and the resulting object
> code is loaded into the production database.
> 3.3 FreePascal will need to have some way to access, manipulate and
> return the data in the database and call other Firebird stored
> procedures, functions, etc. FPC already has good database access,
> including Interbase/Firebird support, but some kind of interface unit
> for direct access will need to be added.
> 3.4 FreePascal will need to use the Firebird memory manager. Using
> various memory managers is fortunately already possible in FPC.
> 3.5 Most importantlY: it will probably require a lot of work on both
> ends to get this done. Fortunately, both Firebird and FPC have an active
> developer community with good communication and bug tracker systems, so
> this helps.
> Your thoughts?

More information about the fpc-pascal mailing list