Re: Ada & Postgresql

["Warren W. Gay VE3WWG" <ve3wwg@cogeco.ca>]

Wojtek Narczynski wrote:

> Hello,
> 
> We used APQ, with MySQL, but PostgreSQL was its primary database, and
> found it to be very high quality, with outstanding documentation.
> 
> Regards,
> Wojtek

One of the primary reasons for me to write APQ was to make it as easy
as it can be to use a database from Ada, without discarding strong
typing and full support for NULL values. APQ includes a number of
generic routines to preserve that strong typing between the database
types and your application.

APQ also now supports MySQL, and in the future (maybe this fall), it
will support Sybase as well (Sybase now allows free developer use of
Linux and Windows versions of the server, with no expiry).

With a little extra effort, you can also write your application to be
database neutral.

Thanks for the kind words for the documentation. I went to a lot
of trouble to make the documentation complete and easy to use.
Each function (or object) includes a code snippet, showing its use.
Exceptions are documented.

My biggest concern however is always application deployment. I can
make just about anything work for myself. Making an open sourced
package easy for someone else to install and use is more difficult.
This is the main reason I stayed away from UNIXODBC. This is less
of an issue on Windows, but I never really felt that ODBC was the
way to go anyway. APQ only requires GNAT, and the database software
client libraries (libpq or the mysql libraries). This makes it
much easier to deploy than GNADE for example.

Finally, if you are using Windows and want a point and click install,
there is a Windows install package too. Just click and point and
then you are ready to compile your Ada SQL application. It comes
with an uninstaller if you choose to use it.

For a sample of how easy it is, here are two Ada source bodies
that perform a most recent price lookup on a security (stock):

-- MAIN PROGRAM price_pg.adb

with Ada.Text_IO;
with APQ.PostgreSQL.Client;
with Prices;

use APQ, Prices, APQ.PostgreSQL.Client, Ada.Text_IO;

procedure Price_PG is
    C : Connection_Type;
    P : APQ_Double;
begin

    Set_DB_Name(C,"myuserid");
    Connect(C);

    Last_Price(C,"RHAT",P);
    Put_Line("RHAT $" & APQ_Double'Image(P));

    Disconnect(C);

end Price_PG;

--- Package prices.adb

package body Prices is

    -- CREATE TABLE PRICE_HIST (
    --    SECURITY   CHAR(10) NOT NULL,
    --    PRICE_DATE DATE NOT NULL,
    --    PRICE      REAL,
    --    PRIMARY KEY(SECURITY,PRICE_DATE)
    -- );

    procedure Last_Price(
       C :        in out Root_Connection_Type'Class;
       Security : in     String;
       Price :       out APQ_Double
    ) is
       function Value is new Float_Value(APQ_Double);

       Q : Root_Query_Type'Class := New_Query(C);
    begin

       Prepare(Q,     "SELECT SECURITY,PRICE_DATE,PRICE");
       Append_Line(Q, "FROM PRICE_HIST");
       Append(Q,      "WHERE SECURITY = ");
       Append_Quoted(Q,C,Security,Line_Feed);
       Append_Line(Q, "ORDER BY SECURITY,PRICE_DATE DESC");

       if Engine_Of(C) = Engine_MySQL then
          Append_Line(Q,"LIMIT 1");  -- Use this if the database is MySQL
       end if;

       Execute(Q,C);

       begin
          Fetch(Q);
       exception
          when No_Tuple =>
             raise;      -- APQ.No_Tuple indicates no price
       end;

       Price := Value(Q,3);

    end Last_Price;

The above program was written to be database agnostic, though
it does use PostgreSQL. This is chosen by the WITH statements
in the main program. Notice how the Last_Price function
adds "LIMIT 1" to the query if it is using MySQL. This helps
the performance of the query, since only 1 row is required
for this routine.

The Prices package is completely database agnostic. The
spec for it is written as follows:

    with APQ;
    use APQ;

    package Prices is

       procedure Last_Price(
          C :        in out Root_Connection_Type'Class;
          Security : in     String;
          Price :       out APQ_Double
       );

    end Prices;

So if database portability is also important to you, you might
consider APQ. All database access is object oriented in APQ.

A number of debug and trace facilities also help your development.
You can capture every SQL query performed to a trace file. This
saves a great deal of time when you need to troubleshoot.

Those are just some highlights. If you want to find out more,
visit my home page below and follow the APQ links.

Thanks for allowing me to bend your ear ;-)

Warren.

-- 
Warren W. Gay VE3WWG
http://home.cogeco.ca/~ve3wwg