GNADE User's Guide: GNADE, The GNat Ada Database Environment; Version 1.5.0; Document Revision $Revision: 1.42 $ | ||
---|---|---|
Prev | Chapter 14. Using the Ada 95 ODBC Bindings | Next |
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 14-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 14-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 14-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 14-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 14-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 14-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);