Re: Gnatcoll SQL questions

[joakimds@kth.se]

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