Re: Call for review: Database API for the GNADE Project

Re: Call for review: Database API for the GNADE Project

[Nick Roberts]

My preference would be for Module SQL (92). Is your API intended to be at a
level below this?

If it is intended to be an alternative, what would be its advantages over
Module SQL?

--
Best wishes,
Nick Roberts

Re: Call for review: Database API for the GNADE Project

[Michael Erdmann]

Nick Roberts schrieb:

> My preference would be for Module SQL (92). Is your API intended to be at a
> level below this?

Please could you clarify this a little bit. The GNADE project already provides

and embedded SQL preprocessor, so i am not sure what you realy mean by
this.

Michael

Re: Call for review: Database API for the GNADE Project

[Nick Roberts]

"Michael Erdmann" <michael.erdmann@snafu.de> wrote in message
news:3C318524.98C9035A@snafu.de...

> > My preference would be for Module SQL (92). Is your API intended to be
at a
> > level below this?
>
> Please could you clarify this a little bit. The GNADE project already
provides
> and embedded SQL preprocessor, so i am not sure what you realy mean by
> this.

Simply that the choice I would personally prefer (usually) for
database-access support within Ada programs is Module SQL. I wouldn't mind
in the least if the same macro/pre-processor approach were taken to the SQL
module files, e.g. translating them into the equivalent Ada code.

Such Ada code would need to have a low-level method of accessing the
database(s); I was wondering if the API you are suggesting is intended for
this purpose (or is it for use directly by application software?).

The nice thing about Module SQL is that it does separate the SQL bit from
the Ada code (unlike Embedded SQL). This means that you could, for example,
rewrite a module in Ada (e.g. if the data was no longer coming from or going
into a database), and know that (provided it replicated the behaviour of the
module's procedures) the rest of the Ada program will be unaffected.

I must admit, I would like a way -- even if it were a bit yukky -- to add
datatypes beyond the standard SQL 92 canon. Of course lots of SQL3 features
would be handy, but I get the impression that's an issue (SQL3 has yet to be
convincingly accepted by the mainstream database companies).

--
Happy New Year,
Nick Roberts

Re: Call for review: Database API for the GNADE Project

[Michael Erdmann]

Nick Roberts schrieb:

> "Michael Erdmann" <michael.erdmann@snafu.de> wrote in message
> news:3C318524.98C9035A@snafu.de...
>
> > > My preference would be for Module SQL (92). Is your API intended to be
> at a
> > > level below this?
> >
> > Please could you clarify this a little bit. The GNADE project already
> provides
> > and embedded SQL preprocessor, so i am not sure what you realy mean by
> > this.
>
> Simply that the choice I would personally prefer (usually) for
> database-access support within Ada programs is Module SQL. I wouldn't mind
> in the least if the same macro/pre-processor approach were taken to the SQL
> module files, e.g. translating them into the equivalent Ada code.

Any how what is Module SQL, do you have link?

> Such Ada code would need to have a low-level method of accessing the
> database(s); I was wondering if the API you are suggesting is intended for
> this purpose (or is it for use directly by application software?).

It is intended for application development.  The idea is to provide a common
interface to access data sources (e.g. Microsoft ADO). In the moment the
proposal is just the smalles possible interface which i think which can be
supported
by most data base products,

> The nice thing about Module SQL is that it does separate the SQL bit from
> the Ada code (unlike Embedded SQL). This means that you could, for example,
> rewrite a module in Ada (e.g. if the data was no longer coming from or going
> into a database), and know that (provided it replicated the behaviour of the
> module's procedures) the rest of the Ada program will be unaffected.

I am still not sure what Module SQL is, but it comes quite near to the idea
of MS ADO, that by changing the query you might use a new data source
without  rewriting the the whole applicaition.

> I must admit, I would like a way -- even if it were a bit yukky -- to add
> datatypes beyond the standard SQL 92 canon.

I think this makes sense because most of the data bases are supporting
a wide variaty of data types. I will check on this!


> Of course lots of SQL3 features
> would be handy, but I get the impression that's an issue (SQL3 has yet to be
> convincingly accepted by the mainstream database companies).

This is to mutch for the first step!

>
>
> --
> Happy New Year,
> Nick Roberts

Re: Call for review: Database API for the GNADE Project

[Nick Roberts]

"Michael Erdmann" <michael.erdmann@snafu.de> wrote in message
news:3C3319B9.80386CE@snafu.de...

> ...
> Any how what is Module SQL, do you have link?

The 'module' language is a part of the 1992 SQL (standard International
Standard 9075:1992), and the definition of Embedded SQL is based upon it! A
useful link for you may be:

http://www.itl.nist.gov/div897/ctg/dm/contents_sql.htm

The work done and being done on the SQL/CLI (closely related to MS ODBC)
might be of great interest to you.

--
Best wishes,
Nick Roberts

Re: Call for review: Database API for the GNADE Project

[Kenneth Almquist]

Michael Erdmann <michael.erdmann@snafu.de> wrote:

>    X : Connection.Object;
>    Connect(X);
>    declare
>       q : Query.Object( X );
>    begin
>       Statement( q, "select * from employees where name = ?" );
>       Bind( q, 1, Name );
>       Execute( q );
>       .....
>    end ;

I wonder if replacing the various Bind procedures with generic
packages would be better:

    declare
        Statement : constant SQL_Statement
             := Prepare(Connection, "select * from employees where empno = ?");
        package Empno is new Set_Statement_Integer(Statement, 1);
    begin
        Empno.Set(Name);
        Result := Execute_Query(Statement);
        ...

Set_Statement_Integer is a generic package containing a single procedure
named Set.  We use a generic package rather than a generic procedure
so that we can perform validity checking when the package is instantiated,
rather than performing a check every time a new value is assigned to
empno.

I've changed Execute to Execute_Query because when we execute a query
we get back a set of results, whereas when we execute other types of
SQL statements we get back a status code.

>    result := Execute( statement );
>    while not End_of( result ) loop
>       row  := Fetch( result );
>       name := Get( row, 1 );
>       boss := Get( row, 2 );
>          .......
>    loop;

Is the row value valid after a subsequent fetch operation?  If not,
we could eliminate the row variable and get the fields of the current
row directly from the result variable.

Again, generics might be useful to get the row numbers out of the code:

    declare
        Row : SQL_Row;
    	function Get_Name is new Get_Row_String(1);
        function Has_Boss is new Get_Row_Is_Null(2);
        function Get_Boss is new Get_Row_Integer(2);
    begin
        ...
        Row := Fetch(Result);
        Name := Get_Name(Row);
        if Has_Boss(Row) then
            Boss := Get_Boss(Row);
        else
            Put_Line("The CEO does not have a boss");
        end if;
        ...

> Mode:  The fetch mode controls the behavior of the cursor in the
> result set.  Modes are:  Forward, Backward.

As far as I know, this is not supported by standard SQL, and I can't
think of a reason why anyone would need this.


> If the server drops the connection the procedure Disconnection_Event is
> called.  In order to implement specific behavior, the procedure may be
> overloaded.

If I understand correctly, Server_Error will be raised after this
procedure is called.  It seems like this procedure is unnecessary,
since anything done by this procedure could instead be done by an
exception handler.

I hope these random thoughts are helpful.
				Kenneth Almquist

Re: Call for review: Database API for the GNADE Project

[Michael Erdmann]

Kenneth Almquist schrieb:

> Michael Erdmann <michael.erdmann@snafu.de> wrote:
>
> >    X : Connection.Object;
> >    Connect(X);
> >    declare
> >       q : Query.Object( X );
> >    begin
> >       Statement( q, "select * from employees where name = ?" );
> >       Bind( q, 1, Name );
> >       Execute( q );
> >       .....
> >    end ;
>
> I wonder if replacing the various Bind procedures with generic
> packages would be better:
>
>     declare
>         Statement : constant SQL_Statement
>              := Prepare(Connection, "select * from employees where empno = ?");
>         package Empno is new Set_Statement_Integer(Statement, 1);
>     begin
>         Empno.Set(Name);
>         Result := Execute_Query(Statement);
>         ...

I agree on this.  The problem might be in the future that i have to handle the
null indicator in a save way which means there will be for each data type hidden
variables. Then the best aproach of doing it will be a generic package which
takes the Ada 95 type and provides a save encapsulation of these hidden
parameter. Such a package will have to provide the following procdures/funtions:

Value   - Returns  or set the value
Is_Null -  Item is null

     x : integer

     package integer_vars is new package( type => integer );
     use package;

     .....
     Integer_Vars.Bind( x, 1, statement );


> Set_Statement_Integer is a generic package containing a single procedure
> named Set.  We use a generic package rather than a generic procedure
> so that we can perform validity checking when the package is instantiated,
> rather than performing a check every time a new value is assigned to
> empno.
>
> I've changed Execute to Execute_Query because when we execute a query
> we get back a set of results, whereas when we execute other types of
> SQL statements we get back a status code.
>
> >    result := Execute( statement );
> >    while not End_of( result ) loop
> >       row  := Fetch( result );
> >       name := Get( row, 1 );
> >       boss := Get( row, 2 );
> >          .......
> >    loop;
>
> Is the row value valid after a subsequent fetch operation?  If not,
> we could eliminate the row variable and get the fields of the current
> row directly from the result variable.
>
> Again, generics might be useful to get the row numbers out of the code:
>
>     declare
>         Row : SQL_Row;
>         function Get_Name is new Get_Row_String(1);
>         function Has_Boss is new Get_Row_Is_Null(2);
>         function Get_Boss is new Get_Row_Integer(2);
>     begin
>         ...
>         Row := Fetch(Result);
>         Name := Get_Name(Row);
>         if Has_Boss(Row) then
>             Boss := Get_Boss(Row);
>         else
>             Put_Line("The CEO does not have a boss");
>         end if;
>         ...

I have to admit, this realy looks better!

>
>
> > Mode:  The fetch mode controls the behavior of the cursor in the
> > result set.  Modes are:  Forward, Backward.

> As far as I know, this is not supported by standard SQL, and I can't
> think of a reason why anyone would need this.

There are somtimes applications which like to browse through
result sets. A lot of native data base bindings and ODBC support
this.

> > If the server drops the connection the procedure Disconnection_Event is
> > called.  In order to implement specific behavior, the procedure may be
> > overloaded.
>
> If I understand correctly, Server_Error will be raised after this
> procedure is called.  It seems like this procedure is unnecessary,
> since anything done by this procedure could instead be done by an
> exception handler.

Yes, this was just an option i wanted to provide. But you are right you can
do the same thing capturing the exception. I think i wil remove it because
i made the experience that with such procedure it is quite difficult to
communicate data into them unless you are using some kind of
global data areas!

>
>
> I hope these random thoughts are helpful.
>                                 Kenneth Almquist

Yes, I realy apreciate your effort. I will do the update today! Please keep
an eye on http://gnade.sourceforge.net/ to seen what happens.

Michael

Re: Call for review: Database API for the GNADE Project

[Julio Cano]

This is what I did time ago. It's not finished nor completed but it
works for little examples. The first version was made in C to access
PostgresQL so y called it "pgrecordset", but the table it returns is
not dynamc (can't be modified).
Actualy is in Ada and uses GNADE odbc.
It may look like MS DAO too, but if it's usefull....
The main ads is:

---------------------
with Ada.Strings.Unbounded;
use Ada.Strings.Unbounded;

with Data_Bind;
use Data_Bind;

generic
   Conninfo : String;
   Username : String := "";
   Passwd : String := "";
package PGRecordset is

   type Pgrecordset_ptr is private;
--   type String_Access is access all String;

   NO_DATA : exception;
   DATABASE_ERROR : exception;

   function Get_Bind_List (Recordset : Pgrecordset_Ptr) return
Base_Data;
   procedure Set_Bind_List (Recordset : in out Pgrecordset_Ptr; List :
Base_Data);

   procedure Registerdata (Recordset: Pgrecordset_Ptr; Str:
String_Access; Field : String);

   function First_Field (Recordset : Pgrecordset_Ptr) return Integer;
   function Last_Field (Recordset : Pgrecordset_Ptr) return Integer;

   procedure MoveFirst (Recordset : Pgrecordset_Ptr);
   procedure MoveNext (Recordset : Pgrecordset_Ptr);

   function Recordset_New(Conninfo : String; Username : String := "";
Passwd : String := "") return Pgrecordset_ptr;
   procedure recordset_Close(recordset : in out Pgrecordset_Ptr);
   -- Theese handle new connexions

   function Recordset_New return Pgrecordset_ptr;
   procedure recordset_Close;
   -- Theese handle default conx. so that new conx. doesn't have to be
created.

   function error (Recordset : Pgrecordset_Ptr) return Boolean;
   -- Returns true if there was an error.

   function Error_Msg (Recordset : Pgrecordset_ptr) return String;
   -- Returns a string explaining the error.

   procedure error_Msg (Recordset : Pgrecordset_Ptr; Msg : out
String);
   -- Sets Msg with the error string;

   procedure query (Recordset : in out Pgrecordset_Ptr; Query :
String);
   -- Sends the query to de database and returns the resulting table;

   procedure Exec (Recordset : in out Pgrecordset_Ptr; Cmd : String);
   -- Sends the command to the database but returns no result.


   function Getcolnums (Rs: Pgrecordset_Ptr) return Integer;
   -- Returns number of columns in query table.

   function Getcolname (Rs: Pgrecordset_Ptr; Num: integer) return
String;
   -- Returns the column name.

   function GetData (Rs: Pgrecordset_Ptr; Column : String) return
String;
   -- Returns data in the named column in actual register.

private

   type Pgrecordset;
   type Pgrecordset_Ptr is access all pgRecordset;

   procedure check_errors (conx : in out pgrecordset_ptr);
end PGRecordset;
---------------------

Data Binding....
-------------------

with Gnu.Db.Sqlcli;
use Gnu.Db.Sqlcli;
with Gnu.Db.Sqlcli.Bind;

with Ada.Strings.Unbounded;
use Ada.Strings.Unbounded;

package Data_Bind is

   type Data_List is tagged private;
   type Base_Data is private;
   procedure DataFix (Elem : access Data_List);
   procedure Binddata (Data : access Data_List; Stmt : Sqlhstmt);

   type String_Data is private;
   type String_Data_Ptr is private;
   procedure DataFix (Elem : access String_Data);
   procedure Binddata (Data : access String_Data; Stmt : Sqlhstmt);

   -- This limits string data lenght to 256!!!!!
   subtype sql_str is String(1..256);
   type sql_str_access is access all sql_str;


   function Name2col (Stmt   : Sqlhstmt; Column : Unbounded_String )
return Sql_Column_Number;



--   procedure DataListFix ( List : access Data_List'Class);
   procedure DataListFix ( List : Base_Data);

   procedure RegisterData (List : in out Base_Data; Data :
String_access; Column : String);
   
   procedure UnRegisterData (List : in out Base_Data; Column :
String);
   
   procedure UnRegisterAll (List : in out Base_Data);

   procedure Binddatalist (List : Base_Data; Stmt : Sqlhstmt);

private

   type Base_Data is access all Data_List'Class;
   type Data_List is tagged
      record
         Field : Unbounded_String;
         Next  : Base_Data;
      end record;



   package String_Binding is new Gnu.Db.Sqlcli.Bind (Sql_Str,
                                                     Sql_Str_Access);
   package Sb renames String_Binding;

   type String_Data is new Data_List with
      record
         Data        : String_access;
         Data_Length : aliased Sqlinteger;
         Binded : Sql_Str_Access;
      end record;
   type String_Data_Ptr is access all String_Data;



end Data_Bind;
-------------------------
Extending this data_bind package should let you bind another data
types.
I tried to make this a pgrecordset's child package but i didnt find
the way...

The next one is an example using the this package. Sorry for the
variable names in spanish... ;)
----------------

with Pgrecordset;
with Data_Bind;
with text_io;

procedure Lista4 is
   package Db renames Pgrecordset;


   Conx : Db.Pgrecordset_Ptr;

   nombre : aliased String(1..30);
   Direc : aliased String (1..40);
   Edad : aliased String(1..5);

   Edad2 : Integer;
begin

   Conx := Db.Recordset_New("gestion");

   db.registerdata (Conx, nombre'Unrestricted_access, "name");
   db.registerdata (Conx, direc'Unrestricted_access, "address");
   db.registerdata (Conx, edad'Unrestricted_access, "age");
   db.query (conx, "select * from customers");
   db.movefirst(conx);

   declare
      fin : boolean := false;
   begin
      loop
         text_io.put_line ("Nombre : " & nombre);
         text_io.put_line ("Direc : " & direc);
         text_io.put_line ("edad : " & edad);
         db.movenext(conx);
         exit when Fin;
      end loop;
   exception
      when db.no_data => fin := true;
      when others => text_io.put_line ("Exception no tratada.");
   end;

--   Edad2 := integer'value(Edad) + 10;
--   Db.Exec (Conx, "insert into customers values
('"&Nombre&"','"&direc&"','"&Integer'Image(Edad2)&"')");


   declare begin
      Db.Recordset_Close (Conx);
      exception when others => Text_Io.Put_Line ("!!!!!!!");
      end;

end Lista4;
----------------
Hope this is usefull. I could send it to anybody...

Bye.

Re: Call for review: Database API for the GNADE Project

[Michael Erdmann]

Since the GNADE projects  objective is to be also
a repository for different approaches to Ada 95
data base interfaces for Ada 95 it is very  usefull.
Please could you send me a copy, or even better
if you like we can present it on the  project pages
if you are willing to support the code in the future
as a member of the GNADE team?

Michael


Julio Cano schrieb:

> This is what I did time ago. It's not finished nor completed but it
> works for little examples. The first version was made in C to access
> PostgresQL so y called it "pgrecordset", but the table it returns is
> not dynamc (can't be modified).
> Actualy is in Ada and uses GNADE odbc.
> It may look like MS DAO too, but if it's usefull....
> The main ads is:
>
> ---------------------
> with Ada.Strings.Unbounded;
> use Ada.Strings.Unbounded;
>
> with Data_Bind;
> use Data_Bind;
>
> generic
>    Conninfo : String;
>    Username : String := "";
>    Passwd : String := "";
> package PGRecordset is
>
>    type Pgrecordset_ptr is private;
> --   type String_Access is access all String;
>
>    NO_DATA : exception;
>    DATABASE_ERROR : exception;
>
>    function Get_Bind_List (Recordset : Pgrecordset_Ptr) return
> Base_Data;
>    procedure Set_Bind_List (Recordset : in out Pgrecordset_Ptr; List :
> Base_Data);
>
>    procedure Registerdata (Recordset: Pgrecordset_Ptr; Str:
> String_Access; Field : String);
>
>    function First_Field (Recordset : Pgrecordset_Ptr) return Integer;
>    function Last_Field (Recordset : Pgrecordset_Ptr) return Integer;
>
>    procedure MoveFirst (Recordset : Pgrecordset_Ptr);
>    procedure MoveNext (Recordset : Pgrecordset_Ptr);
>
>    function Recordset_New(Conninfo : String; Username : String := "";
> Passwd : String := "") return Pgrecordset_ptr;
>    procedure recordset_Close(recordset : in out Pgrecordset_Ptr);
>    -- Theese handle new connexions
>
>    function Recordset_New return Pgrecordset_ptr;
>    procedure recordset_Close;
>    -- Theese handle default conx. so that new conx. doesn't have to be
> created.
>
>    function error (Recordset : Pgrecordset_Ptr) return Boolean;
>    -- Returns true if there was an error.
>
>    function Error_Msg (Recordset : Pgrecordset_ptr) return String;
>    -- Returns a string explaining the error.
>
>    procedure error_Msg (Recordset : Pgrecordset_Ptr; Msg : out
> String);
>    -- Sets Msg with the error string;
>
>    procedure query (Recordset : in out Pgrecordset_Ptr; Query :
> String);
>    -- Sends the query to de database and returns the resulting table;
>
>    procedure Exec (Recordset : in out Pgrecordset_Ptr; Cmd : String);
>    -- Sends the command to the database but returns no result.
>
>    function Getcolnums (Rs: Pgrecordset_Ptr) return Integer;
>    -- Returns number of columns in query table.
>
>    function Getcolname (Rs: Pgrecordset_Ptr; Num: integer) return
> String;
>    -- Returns the column name.
>
>    function GetData (Rs: Pgrecordset_Ptr; Column : String) return
> String;
>    -- Returns data in the named column in actual register.
>
> private
>
>    type Pgrecordset;
>    type Pgrecordset_Ptr is access all pgRecordset;
>
>    procedure check_errors (conx : in out pgrecordset_ptr);
> end PGRecordset;
> ---------------------
>
> Data Binding....
> -------------------
>
> with Gnu.Db.Sqlcli;
> use Gnu.Db.Sqlcli;
> with Gnu.Db.Sqlcli.Bind;
>
> with Ada.Strings.Unbounded;
> use Ada.Strings.Unbounded;
>
> package Data_Bind is
>
>    type Data_List is tagged private;
>    type Base_Data is private;
>    procedure DataFix (Elem : access Data_List);
>    procedure Binddata (Data : access Data_List; Stmt : Sqlhstmt);
>
>    type String_Data is private;
>    type String_Data_Ptr is private;
>    procedure DataFix (Elem : access String_Data);
>    procedure Binddata (Data : access String_Data; Stmt : Sqlhstmt);
>
>    -- This limits string data lenght to 256!!!!!
>    subtype sql_str is String(1..256);
>    type sql_str_access is access all sql_str;
>
>    function Name2col (Stmt   : Sqlhstmt; Column : Unbounded_String )
> return Sql_Column_Number;
>
> --   procedure DataListFix ( List : access Data_List'Class);
>    procedure DataListFix ( List : Base_Data);
>
>    procedure RegisterData (List : in out Base_Data; Data :
> String_access; Column : String);
>
>    procedure UnRegisterData (List : in out Base_Data; Column :
> String);
>
>    procedure UnRegisterAll (List : in out Base_Data);
>
>    procedure Binddatalist (List : Base_Data; Stmt : Sqlhstmt);
>
> private
>
>    type Base_Data is access all Data_List'Class;
>    type Data_List is tagged
>       record
>          Field : Unbounded_String;
>          Next  : Base_Data;
>       end record;
>
>    package String_Binding is new Gnu.Db.Sqlcli.Bind (Sql_Str,
>                                                      Sql_Str_Access);
>    package Sb renames String_Binding;
>
>    type String_Data is new Data_List with
>       record
>          Data        : String_access;
>          Data_Length : aliased Sqlinteger;
>          Binded : Sql_Str_Access;
>       end record;
>    type String_Data_Ptr is access all String_Data;
>
> end Data_Bind;
> -------------------------
> Extending this data_bind package should let you bind another data
> types.
> I tried to make this a pgrecordset's child package but i didnt find
> the way...
>
> The next one is an example using the this package. Sorry for the
> variable names in spanish... ;)
> ----------------
>
> with Pgrecordset;
> with Data_Bind;
> with text_io;
>
> procedure Lista4 is
>    package Db renames Pgrecordset;
>
>    Conx : Db.Pgrecordset_Ptr;
>
>    nombre : aliased String(1..30);
>    Direc : aliased String (1..40);
>    Edad : aliased String(1..5);
>
>    Edad2 : Integer;
> begin
>
>    Conx := Db.Recordset_New("gestion");
>
>    db.registerdata (Conx, nombre'Unrestricted_access, "name");
>    db.registerdata (Conx, direc'Unrestricted_access, "address");
>    db.registerdata (Conx, edad'Unrestricted_access, "age");
>    db.query (conx, "select * from customers");
>    db.movefirst(conx);
>
>    declare
>       fin : boolean := false;
>    begin
>       loop
>          text_io.put_line ("Nombre : " & nombre);
>          text_io.put_line ("Direc : " & direc);
>          text_io.put_line ("edad : " & edad);
>          db.movenext(conx);
>          exit when Fin;
>       end loop;
>    exception
>       when db.no_data => fin := true;
>       when others => text_io.put_line ("Exception no tratada.");
>    end;
>
> --   Edad2 := integer'value(Edad) + 10;
> --   Db.Exec (Conx, "insert into customers values
> ('"&Nombre&"','"&direc&"','"&Integer'Image(Edad2)&"')");
>
>    declare begin
>       Db.Recordset_Close (Conx);
>       exception when others => Text_Io.Put_Line ("!!!!!!!");
>       end;
>
> end Lista4;
> ----------------
> Hope this is usefull. I could send it to anybody...
>
> Bye.