Gnatcoll SQL questions

Gnatcoll SQL questions

[NiGHTS]

I am moderately experienced with the gnatcoll SQL library in that I have used it with success and have studied some of its code and methods. Currently I am using it as an interface to SQLite but may use it in the future for PostgreSQL.

Here are my questions.

1. How do I delete data? I know this sounds simple but I can't delete any data for the life of me. Here is an example of my code.

procedure proc
is
        Query  : GNATCOLL.SQL.SQL_Query;
        
begin
        Query := SQL_Delete ( 
                From  => data_db.Profiles,
                Where => data_db.Profiles.Id_Profiles = 1
        );
                
        Database_Connection.Execute ( Query );
        Database_Connection.Commit;
        
end proc;

Note that Database_Connection has been instantiated, initialized and has a good connection to the database. I also know this because the program can SELECT, UPDATE and INSERT just fine. But this particular DELETE function does nothing at all to the database. I have also tried it omitting the entire "Where" line so it deletes all items in the Profiles table and yet no success.


2. I would like to use GNATCOLL.SQL.Exec.Insert_And_Get_PK but I can't figure out how to work with it. Previously I used Last_ID after my Insert's commit but it always returned -1, so I figured it would be more effective to use Insert_And_Get_PK instead. I can't find any examples anywhere (period) so it would be great if I can get some pointers on how to use it.


In conclusion, why does the internet have so little documentation on Gnatcoll? I've read through the docs.adacode.com documentation on the SQL interface too many times to count but it is merely a basic overview. Does anyone know of a good source of reference for Gnatcoll other than the source code itself? Also helpful would be some kind of example source code, something I can study from someone else's work.


Thank you in advance for your help.

Re: Gnatcoll SQL questions

[joakimds]

On Wednesday, July 8, 2015 at 10:21:02 PM UTC+2, NiGHTS wrote:
> I am moderately experienced with the gnatcoll SQL library in that I have used it with success and have studied some of its code and methods. Currently I am using it as an interface to SQLite but may use it in the future for PostgreSQL.
> 
> Here are my questions.
> 
> 1. How do I delete data? I know this sounds simple but I can't delete any data for the life of me. Here is an example of my code.
> 
> procedure proc
> is
>         Query  : GNATCOLL.SQL.SQL_Query;
>         
> begin
>         Query := SQL_Delete ( 
>                 From  => data_db.Profiles,
>                 Where => data_db.Profiles.Id_Profiles = 1
>         );
>                 
>         Database_Connection.Execute ( Query );
>         Database_Connection.Commit;
>         
> end proc;
> 
> Note that Database_Connection has been instantiated, initialized and has a good connection to the database. I also know this because the program can SELECT, UPDATE and INSERT just fine. But this particular DELETE function does nothing at all to the database. I have also tried it omitting the entire "Where" line so it deletes all items in the Profiles table and yet no success.
> 
> 
> 2. I would like to use GNATCOLL.SQL.Exec.Insert_And_Get_PK but I can't figure out how to work with it. Previously I used Last_ID after my Insert's commit but it always returned -1, so I figured it would be more effective to use Insert_And_Get_PK instead. I can't find any examples anywhere (period) so it would be great if I can get some pointers on how to use it.
> 
> 
> In conclusion, why does the internet have so little documentation on Gnatcoll? I've read through the docs.adacode.com documentation on the SQL interface too many times to count but it is merely a basic overview. Does anyone know of a good source of reference for Gnatcoll other than the source code itself? Also helpful would be some kind of example source code, something I can study from someone else's work.
> 
> 
> Thank you in advance for your help.

Dear mysterious NiGHTS,

I just read your comment in another thread that you were disappointed that you did not get feedbaack on this. Ada-Sweden has organized public meetups on Ada and one of them was on GNATCOLL SQL:
http://www.meetup.com/Ada-Stockholm/events/168383442/
You should have been there :-) Anyways, here is some feedback. When trying out the GNATCOLL SQL I wrote a small application to keep track on peoples birthdays and store them in a PostgreSQL database.

Here is the text-file describing the tables in the database:
| TABLE | birthdays     | birthday | | Contains the birthdays |
| id    | AUTOINCREMENT | PK       | | Auto-generated ID      |
| age   | INTEGER       |          | |                        |
| name  | TEXT          |          | |                        |

Here is an excerpt from the source code for the code that created and deleted the birthdays in the database:

   procedure Create_Birthday(This     : Database_API_Type;
                             Birthday : out Model.Basic.Birthday_Type;
                             Name     : Model.Atomic.Birthday_Name_Type;
                             Year     : Model.Atomic.Birthday_Year_Type;
                             Month    : Model.Atomic.Birthday_Month_Type;
                             Day      : Model.Atomic.Birthday_Day_Type)
   is
      Query_Insert : constant GNATCOLL.SQL.SQL_Query :=
        GNATCOLL.SQL.SQL_Insert(Values =>
                                  (Database_Implementation.Auto_Generated_API.Birthdays.Year = Integer(Year)) &
                                (Database_Implementation.Auto_Generated_API.Birthdays.Month = Integer(Month)) &
                                (Database_Implementation.Auto_Generated_API.Birthdays.Day = Integer(Day)) &
                                (Database_Implementation.Auto_Generated_API.Birthdays.Name = String(Name))
                               );
   begin
      This.Connection.Reset_Connection;

      declare
         Primary_Key : constant Integer := This.Connection.Insert_And_Get_PK(Query => Query_Insert,
                                                                             PK => Database_Implementation.Auto_Generated_API.Birthdays.Id);
         Birthday_Id : constant Model.Atomic.Birthday_Id_Type := Model.Atomic.Birthday_Id_Type(Primary_Key);
      begin
         This.Connection.Commit_Or_Rollback;

         if not This.Connection.Success then
            raise Database.Atomic.Database_Error with This.Connection.Error;
         end if;

         Birthday.Initialize(Id    => Birthday_Id,
                             Name  => Name,
                             Year  => Year,
                             Month => Month,
                             Day   => Day);
      end;
   end Create_Birthday;

   procedure Remove_Birthday(This : Database_API_Type;
                             Id : in Model.Atomic.Birthday_Id_Type)
   is
      Query_Delete : constant GNATCOLL.SQL.SQL_Query :=
        GNATCOLL.SQL.SQL_Delete(From  => Database_Implementation.Auto_Generated_API.Birthdays,
                                Where => Database_Implementation.Auto_Generated_API.Birthdays.Id = Integer(Id));
   begin
      This.Connection.Reset_Connection;
      This.Connection.Execute(Query => Query_Delete);
      This.Connection.Commit_Or_Rollback;
      if not This.Connection.Success then
         raise Database.Atomic.Database_Error with This.Connection.Error;
      end if;
   end Remove_Birthday;

The SQL query you have written for deleting a profile looks correct. Maybe you need to reset the connection before executing the SQL query like done in the Ada code above?

I think you've already solved these issues by now but the above feedback might be useful for someone else.

Best regards,
Joakim Strandberg