A minimal odbc example

A code fragments of minimal ODBC program are shown below. The code fragment consists of three basic sections, the initialization code, the connections to the data base and the query it self (the source code is found in the samples/odbc directory).

Example 18-1. Preparing data of the ODBC driver


    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);

       

This section connects to the data base. In this case named by the name "gnade" with the password "gnade".

Example 18-2. Connecting to the data base via ODBC

    SQLConnect (ConnectionHandle => ConnectionHandle,
               ServerName       => "DEMO_DB",
               UserName         => "gnade",
               Authentication   => "gnade");
       

After the connection has been established, the query has to be done. Let us assume a query like:

 
          SELECT name, firstname  
          FROM employees 
          WHERE manager = :name;
       

Assuming this query, the query will be sent to the dbcs by means of the SQLPrepare method. This will not create any result set, but it binds the command to the previously allocated statement handle.

Example 18-3. Preparing the Query via ODBC

    declare
       ......
       Name, Firstname : aliased Name_String;
       Len_Firstname, Len_Name : aliased SQLINTEGER;
    begin
       SQLAllocHandle (SQL_HANDLE_STMT, ConnectionHandle, StatementHandle);
       SQLPrepare (StatementHandle,
                  "SELECT " & QuoteIdentifier ("name") & ", " &
                  QuoteIdentifier ("firstname") &
                  " FROM " & QuoteIdentifier ("employees") & " " &
                  "WHERE " & QuoteIdentifier ("manager") & " = ? " &
                  "ORDER BY " & QuoteIdentifier ("name") & "," &
                  QuoteIdentifier ("firstname"));
       

Example 18-4. Using host variable with ODBC

The host variable :name is substituted by a '?' sign in the query and the Ada 95 variable "Search_Manager".

The columns name and first name of the query are bound the the Ada 95 host variable Name and Firstname.

       MB.SQLBindParameter (StatementHandle, 1, SQL_PARAM_INPUT,
                           SQL_C_SLONG, SQL_INTEGER, 0,
                           0, Search_Manager'Access,
                           0, Len'Access);

       SB.SQLBindCol (StatementHandle, 1, SQL_C_CHAR,
                     Name'Access, Name'Length, Len_Name'Access);
       SB.SQLBindCol (StatementHandle, 2, SQL_C_CHAR,
                     Firstname'Access, Firstname'Length,
                     Len_Firstname'Access);
    

Example 18-5. Creating the result set for a query

Finally the result set is created by executing the query at the data base.

       SQLExecute (StatementHandle);
       

Example 18-6. Fetching data of the result set via ODBC

The following section reads in one result tuple after the other by means of the SQLFetch method. The result is stored in the host variable which have been specified in the SQLBindCol methods in the previous steps.

       declare
          EndFlag         : Boolean := False;
       begin
          loop
            exit when EndFlag;
            SQLFetch (StatementHandle);
            SQLFixNTS (String (Name), Len_Name);
            SQLFixNTS (String (Firstname), Len_Firstname);
            Put (String (Name (1 .. Integer (Len_Name))));
            Put (", ");
            Put (String (Firstname (1 .. Integer (Len_Firstname))));
            New_Line;

          end loop;
       exception
          when No_Data => EndFlag := True;
       end;
    end;
    

After the result set has been processed, the we disconnect from the data base and return all held resources to the odbc driver.

    SQLCommit (ConnectionHandle);
    SQLDisconnect (ConnectionHandle);

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