Gnade/ODBC example - please help

Gnade/ODBC example - please help

[azdakiel]

Hi,
    I'm new here. I also just started my advanture with ADA.
    I'm building a portal using AWS (my project for university) and i
need to connect to a MySQL database. I using Gnade/ODBC interface.
    There is simple example, in the Gnade User's Guide, how to connect
and get some data from databese. And it works.

And now my problem: This example shows how to pass integer parameter
to the query and get string and float from the database.
I trying to pass to the query string and the boolean (...WHERE NAME =
name AND IsVisible = visible...) and I can't. I have no idea how to
rewrite this example.

Is there anyone who can tell me how to change this example?


---------------=========The Example========------------
with Ada.Characters.Handling;
with Ada.Command_Line;
with Ada.Strings.Fixed;        use Ada.Strings.Fixed;
with Ada.Text_IO;              use Ada.Text_IO;
with Ada.Exceptions;           use Ada.Exceptions;
with GNU.DB.SQLCLI;            use GNU.DB.SQLCLI;
with GNU.DB.SQLCLI.Bind;

with GNU.DB.SQLCLI.Info;       use GNU.DB.SQLCLI.Info;
with GNU.DB.SQLCLI.Info.Debug;

with GNU.DB.SQLCLI.Environment_Attribute;
use GNU.DB.SQLCLI.Environment_Attribute;
with GNU.DB.SQLCLI.Environment_Attribute.Debug;

with GNU.DB.SQLCLI.Connection_Attribute;
use  GNU.DB.SQLCLI.Connection_Attribute;
with GNU.DB.SQLCLI.Connection_Attribute.Debug;

use GNU.DB.SQLCLI;

with GNAT.Traceback.Symbolic;

procedure odbc_mysql is


      package RIO is new Ada.Text_IO.Float_IO (SQLDOUBLE);

      EnvironmentHandle : SQLHENV;
      ConnectionHandle  : SQLHDBC;

      ServerName     : constant String := String'("test");
      UserName       : constant String := String'("test");
      Authentication : constant String := String'("test");

      Quoting_Character : Character := Character'Val (34);

      function  QuoteIdentifier (ID : String) return String;
      procedure Get_Identifier_Info;

      function QuoteIdentifier (ID : String) return String is
      begin
         return Quoting_Character & ID & Quoting_Character;
      end QuoteIdentifier;

      pragma Inline (QuoteIdentifier);
      procedure Get_Identifier_Info is
         QC : constant Driver_Info_String :=
           Driver_Info_String (SQLGetInfo
                               (ConnectionHandle,
SQL_IDENTIFIER_QUOTE_CHAR));

      begin
         if QC.Value'Length /= 1 then
            null;
         else
            Quoting_Character := QC.Value (QC.Value'First);
         end if;
      end Get_Identifier_Info;

   begin
      SQLAllocHandle (SQL_HANDLE_ENV, SQL_NULL_HANDLE,
EnvironmentHandle);
      SQLSetEnvAttr  (EnvironmentHandle,
Environment_Attribute_ODBC_Version'
          (Attribute => SQL_ATTR_ODBC_VERSION,
           Value     => SQL_OV_ODBC3));

      SQLAllocHandle (SQL_HANDLE_DBC, EnvironmentHandle,
ConnectionHandle);
      SQLConnect (ConnectionHandle => ConnectionHandle,
                  ServerName       => ServerName,
                  UserName         => UserName,
                  Authentication   => Authentication);

      Get_Identifier_Info;

      declare
         package Double_Binding is new
           GNU.DB.SQLCLI.FloatBinding (SQLDOUBLE);
         package DB renames Double_Binding;

         type ManagerID is new SQLINTEGER;
         type ManagerID_Ptr is access all ManagerID;
         package ManagerID_Binding is new
           GNU.DB.SQLCLI.Bind (ManagerID, ManagerID_Ptr);
         package MB renames ManagerID_Binding;

         StatementHandle : SQLHSTMT;
         Search_Manager  : aliased ManagerID := 2;
         Len             : aliased SQLINTEGER := 0;
         Name            : aliased String := 20 * '.';
         Firstname       : aliased String := 20 * '.';
         Len_Firstname   : aliased SQLINTEGER;
         Len_Name        : aliased SQLINTEGER;
         Len_Salary      : aliased SQLINTEGER;
         Salary          : aliased SQLDOUBLE;

      begin
         SQLAllocHandle (SQL_HANDLE_STMT, ConnectionHandle,
StatementHandle);
         SQLPrepare (StatementHandle,
                     "SELECT " & QuoteIdentifier ("NAME") & ", " &
                     QuoteIdentifier ("FIRSTNAME") & ", " &
                     QuoteIdentifier ("SALARY") &
                     " FROM " & QuoteIdentifier ("EMPLOYEES") & " " &
                     "WHERE " & QuoteIdentifier ("MANAGER") & " = ? "
&
                     "ORDER BY " & QuoteIdentifier ("NAME") & "," &
                     QuoteIdentifier ("FIRSTNAME"));
         MB.SQLBindParameter
           (StatementHandle  => StatementHandle,
            ParameterNumber  => 1,
            InputOutputType  => SQL_PARAM_INPUT,
            ValueType        => SQL_C_SLONG,
            ParameterType    => SQL_INTEGER,
            ColumnSize       => 0,
            DecimalDigits    => 0,
            Value            => Search_Manager'Access,
            BufferLength     => 0,
            StrLen_Or_IndPtr => Len'Access);

         SQLBindCol
           (StatementHandle, 1, Name'Access, Len_Name'Access);
         SQLBindCol (StatementHandle, 2, Firstname'Access,
Len_Firstname'Access);
         DB.SQLBindCol (StatementHandle, 3, Salary'Access,
Len_Salary'Access);
         SQLExecute (StatementHandle);

      begin
         loop
            SQLFetch (StatementHandle);
            SQLFixNTS (Name, Len_Name);
            SQLFixNTS (Firstname, Len_Firstname);
            Put (Name);
            Put (", ");
            Put (Firstname);
            Put (", ");
            RIO.Put (Item => Salary, Fore => 5, Aft => 2, Exp => 0);
            New_Line;
         end loop;
      exception
         when No_Data =>
            null;
      end;
   end;

   SQLCommit (ConnectionHandle);
   SQLDisconnect (ConnectionHandle);
   SQLFreeHandle (SQL_HANDLE_DBC, ConnectionHandle);
   SQLFreeHandle (SQL_HANDLE_ENV, EnvironmentHandle);

end odbc_mysql;
======================================================


Thanks,
Hubert Walter

Re: Gnade/ODBC example - please help

[Dmitry A. Kazakov]

On Fri, 1 Aug 2008 05:18:38 -0700 (PDT), azdakiel@gmail.com wrote:

> And now my problem: This example shows how to pass integer parameter
> to the query and get string and float from the database.
> I trying to pass to the query string and the boolean (...WHERE NAME =
> name AND IsVisible = visible...) and I can't. I have no idea how to
> rewrite this example.

Bind them as parameters. The relevant call is SQLBindParameter. In the
statement the parameters to bound are specified by the placeholder '?'.
Simple values like Boolean could also be specified as literals directly in
the statement. Unless you keep the prepared statement for several
executions with the parameters varying.

I also would recommend you to read about ODBC in order to understand how it
works + about the implementation of ODBC provided by the DBMS you are
working with.

-- 
Regards,
Dmitry A. Kazakov
http://www.dmitry-kazakov.de

Re: Gnade/ODBC example - please help

[Ludovic Brenta]

Hubert Walter wrote:
> And now my problem: This example shows how to pass integer parameter
> to the query and get string and float from the database.
> I trying to pass to the query string and the boolean (...WHERE NAME =
> name AND IsVisible = visible...) and I can't. I have no idea how to
> rewrite this example.

The example already covers getting Strings from the database. To bind
a String into a query, you simply call the procedure
GNU.DB.SQLCLI.SQLBindParameter where the Value parameter has type
"access String" (line 765 in gnu-db-sqlcli.ads). This would look like:

Some_String : aliased String := "this is the string I want to bind
into the prepared statement";
Length : aliased SQLINTEGER := Some_String'Length;

SQLPrepare (StatementHandle, "SELECT * FROM T WHERE NAME = ? AND
IS_VISIBLE = ?);
SQLBindParameter
  (StatementHandle,
   ParameterNumber => 1,
   Value => Some_String'Access,
   Length => Lengh'Access);

As for Booleans, I think the best option is to instantiate
GNU.DB.SQLCLI.EnumBinding (line 866 in gnu-db-sqlcli.ads) using
Boolean as the parameter, like so:

package Boolean_Binding is new GNU.DB.SQLCLI.EnumBinding (Enum =>
Boolean);
Indicator : aliased SQLINTEGER;

Boolean_Binding.SQLBindParameter
  (StatementHandle,
   ParameterNumber => 2,
   Value => Some_Boolean'Access,
   SQLIndicator => Indicator'Access);

Hope this helps

--
Ludovic Brenta.