APQ

% APQ-2.2 Manual

\documentclass[english,letterpaper]{book}

\usepackage{times}

\usepackage[T1]{fontenc}

\usepackage[latin1]{inputenc}

\usepackage{longtable}

\usepackage{amsmath}

\usepackage{amssymb}

\usepackage{floatflt}

\usepackage{fancyhdr}

\pagestyle{fancy}

%\lhead{}

%\chead{}

\rhead{}

\lfoot{}

\cfoot{\thepage}

\rfoot{APQ 3.0}

\newcommand\Ref[1]{\textsection\ref{#1} (page~\pageref{#1})}

\usepackage{fancyvrb}

\usepackage{listings}

\usepackage{makeidx}

\makeindex

\IfFileExists{url.sty}{\usepackage{url}}

                      {\newcommand{\url}{\texttt}}

\makeatletter

\usepackage{babel}

\makeatother

%==========%

% HYPERREF %

%==========%

\usepackage[dvipdfm, bookmarks, colorlinks, breaklinks, pdftitle={APQ User Manual},

    pdfauthor={KOW Framework Project}]{hyperref}

\hypersetup{

	linkcolor=DarkSkyBlue,

	citecolor= DarkSkyBlue,

	filecolor= DarkSkyBlue,

	urlcolor= DarkSkyBlue

}

%========================%

% Listings Package Setup %

%========================%

\usepackage{xcolor}

\usepackage{listings}

% COLORS (Tango)

\definecolor{LightButter}{rgb}{0.98,0.91,0.31}

\definecolor{LightOrange}{rgb}{0.98,0.68,0.24}

\definecolor{LightChocolate}{rgb}{0.91,0.72,0.43}

\definecolor{LightChameleon}{rgb}{0.54,0.88,0.20}

\definecolor{LightSkyBlue}{rgb}{0.45,0.62,0.81}

\definecolor{LightPlum}{rgb}{0.68,0.50,0.66}

\definecolor{LightScarletRed}{rgb}{0.93,0.16,0.16}

\definecolor{Butter}{rgb}{0.93,0.86,0.25}

\definecolor{Orange}{rgb}{0.96,0.47,0.00}

\definecolor{Chocolate}{rgb}{0.75,0.49,0.07}

\definecolor{Chameleon}{rgb}{0.45,0.82,0.09}

\definecolor{SkyBlue}{rgb}{0.20,0.39,0.64}

\definecolor{Plum}{rgb}{0.46,0.31,0.48}

\definecolor{ScarletRed}{rgb}{0.80,0.00,0.00}

\definecolor{DarkButter}{rgb}{0.77,0.62,0.00}

\definecolor{DarkOrange}{rgb}{0.80,0.36,0.00}

\definecolor{DarkChocolate}{rgb}{0.56,0.35,0.01}

\definecolor{DarkChameleon}{rgb}{0.30,0.60,0.02}

\definecolor{DarkSkyBlue}{rgb}{0.12,0.29,0.53}

\definecolor{DarkPlum}{rgb}{0.36,0.21,0.40}

\definecolor{DarkScarletRed}{rgb}{0.64,0.00,0.00}

\definecolor{Aluminium1}{rgb}{0.93,0.93,0.92}

\definecolor{Aluminium2}{rgb}{0.82,0.84,0.81}

\definecolor{Aluminium3}{rgb}{0.73,0.74,0.71}

\definecolor{Aluminium4}{rgb}{0.53,0.54,0.52}

\definecolor{Aluminium5}{rgb}{0.33,0.34,0.32}

\definecolor{Aluminium6}{rgb}{0.18,0.20,0.21}

\lstset{

	keywordstyle=[1]{\color{DarkSkyBlue}},

	keywordstyle=[2]{\color{DarkScarletRed}},

	keywordstyle=[3]{\bfseries},

	keywordstyle=[4]{\color{DarkPlum}},

	keywordstyle=[5]{\color{SkyBlue}},

	commentstyle={\color{Aluminium4}\small},

	stringstyle={\color{Chocolate}},

	tabsize=4,

	breaklines=true,

	basicstyle={\ttfamily\small},

	xleftmargin=21pt,

	xrightmargin=11pt,

	frame=single,

	rulecolor=\color{black!30},

	captionpos=b,

	framesep=10pt,

	framexleftmargin=18pt,

	numbers=none,

	numberstyle={\tiny},

	stepnumber=1,

	numbersep=15pt

}

\lstdefinelanguage{Ada}{%

	morekeywords={alfa,and,array,begin,boolean,byte,case,char,const,div,%

	do,downto,else,end,false,file,for,function,get,goto,if,in,%

	integer,label,maxint,mod,new,not,of,or,pack,packed,page,program,%

	procedure,put,read,readln,real,record,repeat,reset,rewrite,set,%

	text,then,to,true,type,unpack,until,var,while,with,write,writeln},%

	sensitive=false,%

	morecomment=[s]{(*}{*)},%

	morecomment=[s]{\{}{\}},%

	morestring=[d]{'}%

}

\lstdefinelanguage{SQL}{%

	morekeywords={select,insert,table,from,into,create,delete,alfa,and,array,begin,boolean,byte,case,char,const,div,%

	do,downto,else,end,false,file,for,function,get,goto,if,in,%

	integer,label,maxint,mod,new,not,of,or,pack,packed,page,program,%

	procedure,put,read,readln,real,record,repeat,reset,rewrite,set,%

	text,then,to,true,type,unpack,until,var,while,with,write,writeln},%

	sensitive=false,%

	morecomment=[s]{(*}{*)},%

	morecomment=[s]{\{}{\}},%

	morestring=[d]{'},%

	caption=SQL%

}

\lstnewenvironment{SQL}{\lstset{language=SQL}}{}

\lstnewenvironment{Code}{\lstset{language=Ada}}{}

\lstnewenvironment{Example}{\lstset{language=Ada,title=Example}}{}

\lstnewenvironment{NumberedExample}{\lstset{language=Ada,label=Example}}{}

%\DefineVerbatimEnvironment{SQL}{Verbatim}%

%   {frame=single,fontsize=\small,label={SQL},labelposition=topline}

%\DefineVerbatimEnvironment{Code}{Verbatim}%

%   {frame=single,fontsize=\small}

%\DefineVerbatimEnvironment{Example}{Verbatim}%

%   {frame=single,fontsize=\small,label={Example},labelposition=topline}

%\DefineVerbatimEnvironment{NumberedExample}{Verbatim}%

%   {frame=single,numbers=left,fontsize=\small,label={Example},labelposition=topline,commandchars=\\\{\}}

%==============%

% The document %

%==============%

\begin{document}

\title{APQ Ada95 Database Binding}

\author{%

Copyright (c) 2002-2004, Warren W. Gay VE3WWG\\%

Copyright (c) 2007-2009, KOW Framework Project

}

\date{\today}

\maketitle

\tableofcontents{}

\listoftables

%\listoffigures

\chapter{Introduction}

\section{APQ Version 2.2}

This manual documents APQ Version 2.2, which is released under a dual

ACL\index{ACL} and GPL\index{GPL} (GNU Public License)\index{GNU Public

License} arrangement. The dual license arrangement is designed to give

both the \index{distributor} and user the necessary freedoms to enjoy

the fair use and distribution of the sources \index{distribution of

sources} contained in this project. See file COPYING\index{copying} for

more details.

\section{Supported Databases}

The APQ binding\index{binding} was initially created to satisfy the simple need to

allow Ada\index{Ada} programs to use a PostgreSQL\index{PostgreSQL} database. However, as open

sourced database technologies continue to advance, the need to allow

other databases to be used, becomes greater. Rather than write a unique

Ada binding for each one, it was conceptualized that a common API\index{API}

could emerge from the APQ framework\index{framework}. To this end, the APQ binding

has been reworked rather extensively for version 2.1, to permit increasing

levels of general support of other database technologies, including

MySQL\index{MySQL}.

In the fall of 2003 the foundation was laid for Sybase\index{Sybase} support in

the unreleased APQ 2.2 software using Sybase on a trial download\index{download} basis.

Sybase highlighted some new APQ design \index{design, APQ} wrinkles, which required a

few new adjustments and API additions. However, this development ground

to a halt for a spell, until September 9, 2004 when it was announced

on www.slashdot.org\index{www.slashdot.org} that ``Sybase Releases Free Enterprise Database

on Linux''\index{Linux}.

\begin{quote}

``Sybase announced today that they are releasing a free (as in

beer) version of their flagship database for Linux. The free version

is limited to 1 CPU, 2 GB of RAM, and 5 GB of data, which is more

than adequate for all but the most demanding applications. This release

provides a very attractive alternative to Microsoft SQL Server\index{Microsoft SQL Server}, and

gives developers and DBAs an extremely powerful argument to use against

the adoption of Microsoft-based solutions.''%

\footnote{Posted by ``Tassach'' (tassach@rapiertech.com)}

\end{quote}

Now there was greater reason to put enterprise class database support

into an Ada thick binding\index{thick binding} for databases. Sybase support in APQ would

allow developers to develop real applications in using Ada, without

trial software expiring. Once developed, these same Ada applications

could be deployed within the enterprise and on a large scale.

\begin{floatingtable}{

   \begin{tabular}{lccc}

   Database       &  As of APQ Version &  SQL   &  Blob \\

   \hline

   PostgreSQL     &  1.x               &  Yes   &  Yes  \\

   MySQL          &  2.x               &  Yes   &  No   \\

   Sybase 12.52   &  2.2               &  Yes   &  No   \\

   SQL Server     &  3.0               &  Yes   &  No   \\

   \end{tabular}}

   \caption{Database Product Support}\label{t:DBSupport}

\end{floatingtable}

Thanks in part to the generosity of Sybase to developers, APQ 3.0

now supports the following list of database\index{list of database technologies} technologies:

Table \ref{t:DBSupport} is further described as follows:

\begin{description}

   \item [As of APQ Version]is the version where the database was first supported.

   \item [SQL]indicates whether the common SQL functions are supported (sans blob).

   \item [Blob]indicates whether blob support is present.

\end{description}

As the reader can observe in the table above, the support for MySQL

is incomplete in APQ 2.1. The blob support\index{blob support} is lacking in APQ for MySQL,

because MySQL's blob interface is not as complete as provided by PostgreSQL.

Where PostgreSQL provides the facility for virtually limitless sized

blobs, a MySQL blob must fit within a ``column'', very much like

a text field. For this reason, the facility to perform stream oriented\index{stream oriented}

I/O is lacking on a blob in APQ for MySQL.\\

Also, there han't been time or interest in developing Blob support in Sybase and SQL Server

so far. This implementation should take quite a while to be done and for now the focus of 

the project is in providing a stable solution for database connectivity.

\subsection{The Future of Blob Support for MySQL}

Much investigation and research\index{research, APQ} is required to adequately resolve

the blob issue in APQ. Rather than hold back the binding from general

use, where blob functionality may have limited use anyway, it was

decided to release APQ 2.0 with the common API for the two databases,

and leaving the resolution of the blob API for a future release.

If you are a developer, who hopes to write portable database\index{portable database code} code,

then please be aware that the PostgreSQL\index{PostgreSQL} blob\index{blob} API is subject to future

revision. Potentially, this could be fairly extensively revised, but

every attempt will be made to leave a migration path open to the developer.

APQ 3.0 inhertis

\section{Generic Database Support}

One of the main goals of the APQ version 2.0 release, was to develop

a common API\index{API, common}, that does not discriminate based upon the database technology

being selected. The ideal was to allow a developer to write a procedure

that would accept a classwide\index{classwide} database objects, and perform database

operations without needing to be concerned whether the database being

used was PostgreSQL\index{PostgreSQL}, MySQL\index{MySQL} or Sybase\index{Sybase}.

To a large extent, the author believes that this goal has been achieved.

\subsection{Generic Limitations}

It must be admited however, there are some areas where the database

technologies were very different. Consequently, some exceptions and

work-arounds will be required by the programmer. An example of this

is that MySQL\index{MySQL} requires that all rows be fetched from a SELECT\index{SELECT} query.

A failure to do this, corrupts the communication between the server\index{communication, server}

and the client. Consequently, APQ works around this by defaulting

to use the C library call mysql\_store\_result()\index{mysql\_store\_result()} instead of the alternative,

which is mysql\_use\_result()\index{mysql\_use\_result()}. However, if the result set is large,\index{large result sets}

then receiving all of the rows into the clients memory\index{memory, client} is not a suitable

choice. Consequently, APQ does provide some MySQL specific ways to

manage this setting. 

The MySQL database software also provides the special ``LIMIT n''\index{LIMIT n}

extension, if the client program is only interested in the first n

rows of the result. If for example, you have a price file containing

stock price history, you may want to query the most recent price for

it. The simplest way to do this would be to perform a SELECT\index{SELECT} on the

table with a descending price date sort sequence (or index). But if

you only want the first (most recent) row \index{most recent row} returned, you do not want

to retrieve the entire price history into the memory of your client!

This is what mysql\_store\_result()\index{mysql\_store\_result()} implies (APQ default). So the

application programmer will need to plan for this, when MySQL is used.

He will need to do one of the following:

\begin{itemize}

   \item Cause mysql\_use\_result() to be used instead (change the APQ default),

         and then fetch all of the rows, one by one.

   \item Use the MySQL ``LIMIT 1''\index{LIMIT n} SQL extension to limit the results to

         1 row.

\end{itemize}

The problem of course, is that this type of handling must only be

done for MySQL\index{MySQL} databases. Consequently, APQ also provides an API so

that the application may query which database is being used.

\section{The APQ Database Binding}

This software represents a binding\index{binding to objects} to objects and procedures that

enable the Ada95\index{Ada95}%

\footnote{Hereafter, we'll just refer to the language as Ada, even though the

version of the language implied is Ada95.%

} programmer to manipulate or query a relational database.\index{relational database} This document

describes the design principles and goals\index{goals of APQ} of this APQ binding. It

also supplies reference documentation\index{reference documentation} to the programmer, enabling

the reader to write applications using the PostgreSQL\index{PostgreSQL}, MySQL\index{MySQL} 

or Sybase\index{Sybase}  databases,

in the Ada programming language.

The APQ binding\index{binding} was initially developed using GNAT

3.13p\index{GNAT} under FreeBSD 4.4\index{FreeBSD} release.

APQ version 2.0 was developed using Debian\index{Debian Linux} Linux\index{Linux} and GNAT 3.14p. The

examples presented will be tested under the same development environments.

The source code avoids any use of GNAT\index{GNAT} specific language extensions\index{language extensions}.

The possible exception to this rule is that the gnatprep\index{gnatprep} tool may

be used to precompile\index{precompile} optional support of optional databases\index{optional databases}. There

is some C\index{C language} language source\index{source code} code used, to facilitate Ada\index{Ada} and database

C language library linkages.

\begin{table}

   \begin{center}

   \begin{tabular}{ll}

   Library           &  Database\\

   \hline 

   libpq             &  PostgreSQL\\

   libmysqlclient    &  MySQL\\

   OCS-12\_5         &  Sybase\\

   \end{tabular}

   \end{center}

\caption{Client Libraries}\label{t:ClientLib}

\end{table}

Table \ref{t:ClientLib} 

lists the C language libraries\index{libraries, C} that are

used in addition to the APQ client\index{library, APQ client} library, while linking\index{linking} your

application:

GNAT specific features are avoided where possible. The exception

however, is the use of the GNAT\index{GNAT} specific pragma\index{pragma} statements of the form:

\index{Linker\_Options}

\begin{Code}

   pragma Linker_Options("-lapq");

\end{Code}

is used for example, to save the programmer from having to specify

linking\index{linking} arguments. Therefore those using non-ACT\index{non-ACT} vendor

supplied Ada compilers\index{Ada compilers} might be able to compile and use this binding\index{binding}

without a huge investment.

A 32-bit\index{32-bit Windows} Windows library for APQ can be built for use with the PostgreSQL\index{PostgreSQL},

MySQL\index{MySQL} or Sybase\index{Sybase} DLL\index{DLL} client libraries. APQ release 2.1 included the

win32 build instructions necessary, but was omitted in the 2.0 release.

\subsection{General Features}

This binding\index{binding} supports all of the normal database functions that a

programmer would want to use. Additionally blob\index{blob} support

is included%

\footnote{For PostgreSQL only, at release 2.0.%

}, and implemented using the Ada streams\index{streams, Ada} interface\index{interface}.

This provides the programmer with the Ada convenience and safety of

the streams interface\index{streams interface} when working with blobs.

This binding includes the following general features:

\begin{enumerate}

   \item Open and Close one or more concurrent database connections\index{connections}

   \item Create and Execute one or more concurrent SQL queries\index{queries, concurrent} on a selected

         database connection\index{connection, database}

   \item Begin work\index{begin work}, Commit work\index{commit work} or Rollback work\index{rollback work}

   \item Access error message\index{error messages} text

   \item Generic functions and procedures to support specialized application

         types

   \item The NULL indicator\index{NULL indicator} is supported

   \item Blob\index{blob} support using the Ada streams\index{streams, Ada} facility

   \item A wide range of native\index{native types} and builtin\index{builtin data types} data types are supported

   \item Database neutral API\index{neutral API, database} is now supported for most functions

\end{enumerate}

\subsection{Binding Type}

This library represents a thick Ada binding\index{thick binding} to the PostgreSQL C\index{C programmer}  programmer's

libpq\index{libpq} library %

\footnote{C++ programs can also make use of this library but there exists the

library libpq++ for C++ native support.%

}, and with version 2.0, MySQL's\index{MySQL} C programming library. As a thick

binding, there are consequently Ada objects\index{objects, Ada} and data types that are

tailored specifically to the Ada programmer. Some data types and objects

exist to mirror those used in the C language, while others are provided

to make the binding easier or safer to apply.

A thin binding\index{thin binding} would have required the Ada programmer to be continually

dealing with C language\index{C language data types} data type issues. Conversions to and from

various types and pointers\index{pointers} would be necessary making the use of the

binding rather tedious. Furthermore, the resulting Ada\index{Ada} program would

be much harder to read and understand.

A thick binding\index{thick binding} introduces new objects and types in order to provide

an API to the programmer. This approach however, fully insulates the

Ada\index{Ada} programmer from interfacing with C programs\index{C programs}, pointers\index{pointers} and strings\index{strings}.

The design goal\index{design goal} has additionally been to keep the number of new objects

and types to a minimum. This has been done without sacrificing convenience

and safety\index{safety}. Readability\index{readability} of the resulting Ada\index{Ada} program was also considered

to be important.

The objects and data types involved in the use of this binding can

be classified into the following main groups:

\begin{enumerate}

   \item Native\index{native data types} data types and objects

   \item Database \index{database objects} manipulation objects

   \item New database related objects and types for holding data

\end{enumerate}

Native data types need no explanation in this document. The database

manipulation objects will be described in 

\Ref{Database_Objects:Section}.

The following section will introduce the Ada types\index{Ada types} that are used to

hold data.

\section{Binding Data Types}

The PostgreSQL\index{PostgreSQL} database supports many standard SQL\index{SQL data types} data types as well

as a few exotic ones. This section documents the database base types\index{data types, database}

that are supported by the Ada\index{Ada binding} binding to the database. This list is

expected to grow with time as the Ada binding continues to mature

in its own software development.

The ``Data Type Name'' column in the following table refers to

a binding type\index{binding type} if the type name is prefixed with ``APQ\_''%

\footnote{Formerly, the PostgreSQL specific types had used a PG\_ prefix.%

}. These data types were designed to mimic common database data types

in use. They can be used as they are provided, or you may subtype

from them or even derive new types from them in typical Ada\index{Ada} fashion.

All other data types are references to native Ada data types (for

some of these, the package where they are defined are shown in the

``Notes'' column).

The column labelled ``Root Type''\index{root types} documents the data type that

the APQ\_ data type was derived\index{derived} from. Where they represent an Ada\index{subtype, Ada}

subtype, the column ``Subtype'' indicates a ``Y''. For type

derivations a ``N'' is shown in this column, indicating that the

APQ\_ type \index{APQ\_ types} listed is made ``unique''.

\subsection{PostgreSQL Data Types\label{PostgreSQL SQL Data Types}}

The ``Notes'' column of Table \ref{t:pqtypes} provides PostgreSQL notes, package names\index{package names} and

PostgreSQL\index{data types, PostgreSQL} data type names where the name is given in all capitals.

\begin{longtable}{|l|c|c|l|}

\hline 

Data Type Name    &  Root Type   &  Subtype  &  PostgreSQL Notes\\

\hline \hline 

Row\_ID\_Type     &  -           &  N        &  Used for blobs and rows\\

\hline 

String(<>)        &  -           &  -        &  Native Strings\\

\hline 

String(a..b)      &  -           &  -        &  Fixed length strings\\

\hline 

Unbounded\_String &  -           &  -        &  Ada.Strings.Unbounded\\

\hline 

Bounded\_String   &  -           &  -        &  Ada.Strings.Bounded\\

\hline 

APQ\_Smallint     &  -           &  N        &  SMALLINT\\

\hline 

APQ\_Integer      &  -           &  N        &  INTEGER\\

\hline 

APQ\_Bigint       &  -           &  N        &  BIGINT\\

\hline 

APQ\_Real         &  -           &  N        &  REAL\\

\hline 

APQ\_Double       &  -           &  N        &  DOUBLE PRECISION\\

\hline 

APQ\_Serial       &  -           &  N        &  SERIAL\\

\hline 

APQ\_Bigserial    &  -           &  N        &  BIGSERIAL\\

\hline 

APQ\_Boolean      &  Boolean     &  Y        &  BOOLEAN\\

\hline 

APQ\_Date         &  Ada.Calendar.Time & Y   &  DATE\\

\hline 

APQ\_Time         &  Ada.Calendar.Day\_Duration & Y & TIME\footnote{No timezone information}\\

\hline 

APQ\_Timestamp    &  Ada.Calendar.Time & N   & TIMESTAMP\footnote{No timezone information}\\

\hline 

APQ\_Timezone     &  Integer     &  N        &  range -23..23\\

\hline 

APQ\_Bitstring    &  -           &  N        &  BIT or BIT VARYING\\

\hline 

Decimal\_Type     &  -           &  -        &  PostgreSQL.Decimal\\

\hline 

range <>          &  -           &  -        &  Native Integers\\

\hline 

delta <>          &  -           &  -        &  Native Fixed Point\\

\hline 

digits <>         &  -           &  -        &  Native Floating Point\\

\hline 

delta <> digits <> & -           &  -        &  Native Decimal\\

\hline

\caption{PostgreSQL Data Types}\label{t:pqtypes}

\end{longtable}

The data type shown as ``Decimal\_Type''\index{Decimal\_Type}

is special, in that it is supported from a child package APQ\-.PostgreSQL\-.Decimal\index{APQ.PostgreSQL.Decimal}.

It represents a tagged\index{tagged type} type that provides an interface to the C routines\index{C routines}

used by the PostgreSQL database server, for arbitrary precision decimal\index{precision, arbitrary}

values.

\subsection{MySQL Data Types\label{MySQL Data Types}}

Table \ref{t:mytypes} summarizes the MySQL\index{data types, MySQL} specific data types and the

corresponding APQ data types.

\begin{longtable}{|l|c|c|l|}

\hline 

APQ Data Type        &  Ada Spec                   &  Subtype     &  Comments\\

\hline

\hline 

Row\_ID\_Type        &  unsigned 64 bits           &  N           &  For all databases\\

\hline 

APQ\_Smallint        &  signed 16 bits             &  N           &  SMALLINT\\

\hline 

APQ\_Integer         &  signed 32 bits             &  N           &  INTEGER\\

\hline 

APQ\_Bigint          &  signed 64 bits             &  N           &  BIGINT\\

\hline 

APQ\_Real            &  digits 6                   &  N           &  REAL\\

\hline 

APQ\_Double          &  digits 15                  &  N           &  DOUBLE PRECISION\\

\hline 

APQ\_Serial          &  range 1..2147483647        &  N           &  \emph{INTEGER}\\

\hline 

APQ\_Bigserial       &  range 1..2{*}{*}63         &  N           &  \emph{BIGINT}\\

\hline 

APQ\_Boolean         &  Boolean                    &  Y           &  BOOLEAN\\

\hline 

APQ\_Date            &  Ada.Calendar.Time          &  Y           &  DATE\\

\hline 

APQ\_Time            &  Ada.Calendar.Day\_Duration &  Y           &  TIME\\

\hline 

APQ\_Timestamp       &  Ada.Calendar.Time          &  N           &  TIMESTAMP\\

\hline 

APQ\_Timezone        &  range -23..23              &  N           &  \emph{Not in MySQL}\\

\hline 

APQ\_Bitstring       &  array(Positive) of APQ\_Boolean & N       &  \emph{Not in MySQL}\\

\hline

\caption{MySQL Data Types}\label{t:mytypes}

\end{longtable}

Notice the italicized SQL keywords\index{keywords, SQL} in the table. They identify the

SQL keywords that differ from PostgreSQL\index{PostgreSQL}. However, the programmer

only needs to be concerned with these SQL\index{keywords, SQL} keywords when creating new

tables or temporary tables. For example a column of type SERIAL\index{SERIAL} in

a PostgreSQL table, should be declared as a INTEGER\index{INTEGER} type in MySQL.

\section{Sybase Data Types}

The chart below outlines the mappings between APQ data types and Sybase

server data types\index{data types, Sybase}. The italicized SQL keywords

\index{keywords, SQL} in the table identify

the keywords that differ from PostgreSQL\index{PostgreSQL}. The following notes are

particular to Sybase\index{Sybase}:

\begin{itemize}	

   \item APQ\_Bigint is not completely range compatible with Sybase's \emph{NUMERIC}

         \index{APQ\_Bigint}\index{NUMERIC}\index{DECIMAL}

         (or \emph{DECIMAL}) server types. APQ\_Bigint will always accept Sybase server

         values, but the server will not be able to accept the full APQ\_Bigint range of values.

         To comply with the database server's range,

         the application designer should use:

\end{itemize}

\index{Sybase\_Bigint}

\begin{Code}

subtype Sybase_Bigint is APQ_Bigint

   range -10**38..10**38-1;

\end{Code}

\begin{itemize}

   \item APQ\_Bigserial\index{APQ\_Bigserial} is not supported.

   \item APQ\_Boolean\index{APQ\_Boolean}  maps to Sybase's data type \emph{BIT.}

   \item APQ\_Timezone\index{APQ\_Timezone} is not supported by any Sybase data type.

   \item APQ\_Bitstring\index{APQ\_Bitstring} is not supported.

\end{itemize}

Table \ref{t:sytypes} lists the APQ types supported for Sybase.

\begin{longtable}{|l|c|c|l|}

\hline 

APQ Data Type        &  Ada Spec          &  Subtype           &  Comments\\

\hline

\hline 

Row\_ID\_Type        &  unsigned 64 bits  &  N                 &  For all databases\\

\hline 

APQ\_Smallint        &  signed 16 bits    &  N                 &  SMALLINT\\

\hline 

APQ\_Integer         &  signed 32 bits    &  N                 &  INTEGER/INT\\

\hline 

APQ\_Bigint          &  signed 64 bits ($-10^{38}..$$10^{38}\textrm{-1}$) & N & \emph{NUMERIC/DECIMAL}\\

\hline 

APQ\_Real            &  digits 6          &  N                 &  REAL\\

\hline 

APQ\_Double          &  digits 15         &  N                 &  DOUBLE PRECISION\\

\hline 

APQ\_Serial          &  range 1..2147483647 & N                &  \emph{INTEGER}\\

\hline 

APQ\_Bigserial       &  range 1..$2^{63}$ &  N                 &  ???\\

\hline 

APQ\_Boolean         &  Boolean           &  Y                 &  \emph{BIT}\\

\hline 

APQ\_Date            &  Ada.Calendar.Time &  Y                 &  DATE\\

\hline 

APQ\_Time            &  Ada.Calendar.Day\_Duration & Y         &  TIME\\

\hline 

APQ\_Timestamp       &  Ada.Calendar.Time &  N                 &  \emph{DATETIME}\\

\hline 

APQ\_Timezone        &  range -23..23     &  N                 &  \emph{Not in Sybase}\\

\hline 

APQ\_Bitstring       &  array(Positive) of APQ\_Boolean & N    &  \emph{Not in Sybase}\\

\hline

\caption{Sybase Data Types}\label{t:sytypes}

\end{longtable}

\section{Database Objects\label{Database_Objects:Section}}

Much of the binding between Ada\index{Ada} and the database server is provided

through the use of tagged\index{tagged record} record types. Presently the APQ binding

operates through three object types\index{object types, APQ} listed in Table \ref{t:APQObj}.

\begin{table}

   \begin{center}

   \begin{tabular}{lll}

   Derived From            &  Object Type       &  Purpose\\

   \hline

   Root\_Connection\_Type  &  Connection\_Type  & Database connection\\

   Root\_Query\_Type       &  Query\_Type       & SQL interface\\

   N/A                     &  Blob\_Type        & Blob operations\\

   \end{tabular}

   \end{center}

   \caption{APQ Object Types}\label{t:APQObj}

\end{table}

The APQ objects from Table \ref{t:APQObj} are described more fully as follows:

\begin{description}

   \item[Connection\_Type] object is used with Query\_Type and Blob\_Type objects

         to perform SQL queries and blob operations respectively. This object

         maintains the connection to the database server.

   \item[Query\_Type] objects are used with one Connection\_Type object

         to perform SQL query operations. This object holds the text of the

         SQL query, some query state and result information. To execute a 

         query, it must be used in conjunction with a Connection\_Type object.

   \item[Blob\_Type] objects are used with one Connection\_Type object to 

         perform blob operations. This is currently only supported for PostgreSQL

         by APQ. All blob operations must occur within a transaction.

\end{description}

\begin{floatingtable}{

      \begin{tabular}{lc}

      Object            & Finalized \\

      \hline 

      Connection\_Type  & Yes \\

      Query\_Type       & Yes \\

      Blob\_Type        & No \\

      \end{tabular}}

\caption{Object Finalization Behavior}\label{t:Finalization}

\end{floatingtable}

Table \ref{t:Finalization} lists the three APQ objects and their finalization

behaviour.

While the Connection\_Type\index{Connection\_Type}

and Query\_Type\index{Query\_Type} objects are subject to

finalization, the Blob\_Type\index{Blob\_Type} is not.

This is because it represents an access type to

a Blob\_Object. This is similar in concept to an open a file, using

the File\_Type data type. This design approach was necessary

to support Ada\index{Ada} Streams\index{streams, Ada} oriented access for blobs.

\subsection{Object Hierarchy}

Before multiple database products were supported, the APQ object hierarchy\index{hierarchy}

was simple. To provide generic level support however, there are now

root\index{root objects} objects and derived objects\index{derived objects}. In most programming

contexts, the application writer does not need to be concerned with this fact.

However, if you frequently inspect the spec files\index{spec files} instead of the documentation\index{documentation},

you must be aware that primitives\index{primitives} for a given object may be declared

in multiple places. Examine Table \ref{t:pkghier} to review the APQ package hierarchy.

\begin{table}

   \begin{center}

      \begin{tabular}{ll}

         Package Name            & Description \\

         \hline 

         APQ                     & Root objects and primitives \\

         APQ.PostgreSQL          & Declarations and constants unique to PostgreSQL \\

         APQ.PostgreSQL.Client   & Derived objects and added primitives \\

         APQ.MySQL               & Declarations and constants unique to MySQL \\

         APQ.MySQL.Client        & Derived objects and added primitives \\

         APQ.Sybase              & Declarations and constants unique to Sybase \\

         APQ.Sybase.Client       & Derived objects and added primitves for Sybase \\

      \end{tabular}

   \end{center}

   \caption{APQ Package Hierarchy}\label{t:pkghier}

\end{table}

Table \ref{t:pkghier} illustrates that support for a given database is derived\index{derived}

from the APQ top level\index{top level package} package. Root objects\index{root level} are declared in APQ,

with common functionality. Some primitives must be overridden\index{overridden} by the

derived object in database specific packages. For example, APQ.Root\_Query\_Type

declares a primitive named Value (\emph{APQ.Value)} to return a string

column result. If this particular method is called, the exception

\emph{Is\_Abstract}\index{Is\_Abstract} will be raised to indicate that it must be overriden

with code to handle the specific database being used. Normally the

user would invoke APQ\-.MySQL\-.Client\-.Value when using the MySQL\index{MySQL} database,

for example. The programmer normally need not be aware of these details,

since object dispatching\index{dispatching} takes care of these details.

For this reason, the APQ\-.MySQL\-.Query\_Type object for example, is

derived from the APQ\-.Root\_Query\_Type\index{Root\_Query\_Type} object. This Query\_Type\index{Query\_Type} object

will provide its own implementation of the Value\index{Value} function to return

a column result, and will work as required.

Consequently, when looking for primitives\index{primitives} available to the Query\_Type

object, don't forget that many common primitives\index{common primitives} will be inherited\index{inherited}

from the APQ\-.Root\-\_Query\-\_Type object. The same is true for Connection\-\_Type\index{Connection\_Type}

objects. A number of common primitives are inherited from the APQ\-.Root\_Connection\_Type

object.

\chapter{Connecting to the Database}

Before any useful work can be accomplished by a database client program,

a connection must be established between the Ada program and the database

server. This chapter will demonstrate how to use the APQ binding\index{binding} to

enable a program to connect\index{connect} and disconnect\index{disconnect}

from the database server.

\section{The Connection\_Type}

The Connection\_Type\index{Connection\_Type} object holds everything that is needed to maintain

a connection\index{connection} to the database server. There are seven groups of primitive\index{primitive}

operations for this object:

\begin{enumerate}

   \item Context\index{Context} setting operations

   \item Connection\index{Connection} operations

   \item Connection Information functions\index{information functions}

   \item General Information operations

   \item Implicit operations (Finalization\index{Finalization})

   \item Trace Facilities\index{trace facilities}

   \item Generic Database Operations

\end{enumerate}

\section{Context Setting Operations}\label{Context_Setting_Operations}

These primitives ``configure''\index{configure} the connection\index{connection} that is

to be made later. When the object is initially created, it is in the

disconnected\index{disconnected state} state. While disconnected, configuration changes can be

made in to affect the next connection attempt. With the exception of the

database name\index{database name}, the application should not make configuration\index{configuration} changes

while the object is in the connected state\index{connected state}.

\footnote{This is not yet enforced by APQ.}

The configuration primitives\index{primitives} are listed in Table \ref{t:ctxops}.

\footnote{The items marked ``Root'' are primitives from APQ.Root\_Query\_Type.

The items marked {}``Derived'' are those overrides that are declared

on the APQ.{*}.Query\_Type object.}

\begin{table}

   \begin{center}

      \begin{tabular}{ccll}

      Type & Derivation & Name                  & Purpose \\

      \hline 

      proc & Root       & Set\_Host\_Name       & Set server host name \\

      proc & Root       & Set\_Host\_Address    & Set server host IP address \\

      proc & Root       & Set\_Port             & Set server port number \\

      proc & Root       & Set\_DB\_Name         & Set database name \\

      proc & Root       & Set\_User\_Password   & Set userid and password \\

      proc & Root       & Set\_Instance         & Set instance name to use \\

      proc & Root       & Set\_Options          & Set userid and password \\

      \end{tabular}

   \end{center}

\caption{Context Setting Primitives}\label{t:ctxops}

\end{table}

It would be nice if all database software products worked the same

way but the reality is very different. Table \ref{t:ctxdiffs} indicates how

the primitives apply by database product.\label{Set_Instance Support Chart}

In the table you can see that PostgreSQL\index{PostgreSQL} and MySQL\index{MySQL} establish their

connection by specifying the host, port and database name. Sybase

on the other hand, specifies this information in their \emph{interface}

file. To access the appropriate Sybase\index{Sybase interface entry} interface entry requires only

the \emph{instance}\index{instance} information supplied by the Set\_Instance\index{Set\_Instance} call.

\begin{table}

   \begin{center}

      \begin{tabular}{lccc}

      Primitive            & PostgreSQL   & MySQL  & Sybase \\

      \hline 

      Set\_Host\_Name      & Yes          & Yes    & Ignored \\

      Set\_Host\_Address   & Yes          & Yes    & Ignored \\

      Set\_Port            & Yes          & Yes    & Ignored \\

      Set\_DB\_Name        & Yes          & Yes    & See Note %

      \footnote{Set\_DB\_Name is useful after the connection is made.}\\

      Set\_User\_Password  & Yes          & Yes    & Yes \\

      Set\_Instance        & No           & No     & Yes \\

      Set\_Options         & Yes          & Yes    & Yes \\

      \end{tabular}

   \end{center}

\caption{Context Setting Differences}\label{t:ctxdiffs}

\end{table}

\subsection{PostgreSQL Defaults}

The PostgreSQL database defines certain environment\index{environment, PostgreSQL} variables that

can specify defaults. These and the fallback\index{fallback values} values are documented

in Table \ref{t:pqdef}.

\begin{table}

   \begin{center}

      \begin{tabular}{cclll}

      Type & Derivation    & Name               & Default      & Fallback \\

      \hline 

      proc & Root          & Set\_Host\_Name    & PGHOST       & localhost\\

      proc & Root          & Set\_Host\_Address & PGHOST       & localhost\\

      proc & Root          & Set\_Port          & PGPORT       & 5432\\

      proc & Root          & Set\_DB\_Name      & PGDATABASE   & LOGNAME\\

      proc & Root          & Set\_User\_Password &

         \begin{tabular}{l}

            PGUSER\\

            PGPASSWORD\\

         \end{tabular}

        & LOGNAME\\

      proc & Root          & Set\_Options       & PGOPTIONS    & ""\\

      \end{tabular}

   \end{center}

   \caption{PostgreSQL Context Defaults}\label{t:pqdef}

\end{table}

The capitalized names in the table for the ``Default'' and ``Fallback''

columns represent environment\index{environment} variable names.

When any of the environment\index{environment variables} variables are undefined in the ``Default''

column, the value used is determined by the ``Fallback'' value

listed. The fallback\index{fallback} variable name LOGNAME\index{LOGNAME}

is simply used to represent the current user's userid\index{userid}.%

\footnote{The PostgreSQL libpq library may in fact, completely ignore the LOGNAME

environment variable, and simply look up the userid in the /etc/password

file.%

} When no password\index{password} value is provided and no PGPASSWORD\index{PGPASSWORD}

environment variable exists, then no password is assumed.

\subsection{Procedure Set\_Host\_Name}

The Set\_Host\_Name\index{Set\_Host\_Name} procedure accepts the following arguments

\begin{Code}

procedure Set_Host_Name(

   C :         in out Connection_Type;

   Host_Name : in     String

);

\end{Code}

The following example configures the Connection\_Type object to connect

to host\index{host name} ``witherspoon'':

\begin{Example}

declare

   C : Connection_Type;

begin

   Set_Host_Name(C,"witherspoon");

\end{Example}

\begin{description}

\item [Note:]Sybase ignores this API call.

\end{description}

\subsection{Procedure Set\_Host\_Address}

The procedure takes two arguments, in the same fashion as Set\_Host\_Name\index{Set\_Host\_Address}:

\begin{Code}

procedure Set_Host_Address(

   C :            in out Connection_Type;

   Host_Address : in     String

);

\end{Code}

The following example configures the Connection\_Type object to connect

to IP address\index{IP address} 10.0.0.7:

\begin{Example}

declare

   C : Connection_Type;

begin

   Set_Host_Address(C,"10.0.0.7");

\end{Example}

\begin{description}

\item [Note:]Sybase ignores this API call.

\end{description}

\subsection{Procedure Set\_Port (IP)}

This procedure configures the port\index{port} where the database

server\index{server} is listening\index{listening} (when using TCP/IP\index{TCP/IP}

as the transport\index{transport}):

\begin{Code}

procedure Set_Port(

   C :           in out Connection_Type;

   Port_Number : in     Integer

);

\end{Code}

\begin{floatingtable}{

   \begin{tabular}{ccc}

   Database                &  Default  &  Port\\

   \hline 

   PostgreSQL              &  IP       &  5432\\

   MySQL                   &  UNIX     &  ?\\

   Sybase                  &  N/A      &  Ignored\\

   \end{tabular}}

   \caption{Default Database Connections}\label{t:defdb}

\end{floatingtable}

Table \ref{t:defdb} shows the connection defaults by database product. Some of these

are influenced by APQ. For example, APQ defaults to using a TCP/IP connection for the

PostgreSQL standard port of 5432.

The next example

shows how the port\index{port} number is configured.

The example configures APQ to use TCP/IP 

port number 5432\index{port 5432} to connect to

a PostgreSQL database.

\begin{Example}

declare

   C : Connection_Type;

begin

   Set_Port(C,5432);

\end{Example}

\begin{description}

\item [Note:]Sybase ignores this API call.

\end{description}

\subsection{Procedure Set\_Port (UNIX)}

To create a local UNIX socket connection to the database, there is

an overloaded version of Set\_Port\index{Set\_Port} that accepts a string\index{string} parameter

instead of an integer\index{port, integer} port number.

\begin{Code}

procedure Set_Port(

   C :           in out Connection_Type;

   Port_Number : in     String

);

\end{Code}

Specify a null string\index{null string} for the connection\index{connection} 

to use the local\index{local socket} UNIX\index{UNIX socket} socket default.

This parameter has changed somewhat as PostgreSQL\index{PostgreSQL} evolves. If you

are experiencing trouble getting a UNIX\index{UNIX socket} socket connection to work,

check the database documentation for PostgreSQL carefully. The following

example has been tested to work on PostgreSQL version 7.3.5.

\begin{Example}

declare

   C : Connection_Type;

begin

   Set_Port(C,"5432");

\end{Example}

See the troubleshooting chapter for tips.

\subsection{Procedure Set\_DB\_Name}

This procedure call configures the name of the database that the server

is to use \emph{prior} to the connection being established. Once

the connection has been established, calling Set\_DB\_Name\index{Set\_DB\_Name} switches the 

connection\index{connection} to use the named database.

The calling signature for this primitive is as follows:

\begin{Code}

procedure Set_DB_Name(

   C :       in out Connection_Type;

   DB_Name : in     String

);

\end{Code}

The following code fragment shows how the database name\index{database name} is configured

to be ``production'':

\begin{Example}

declare

   C : Connection_Type;

begin

   Set_DB_Name(C,"production");

\end{Example}

The Set\_DB\_Name \emph{always} acts as a configuration setting \emph{prior}

to connecting to the database server. Calling Set\_DB\_Name on a Connection\_Type\index{Connection\_Type}

object that has an open server connection to the database, can result

in hidden SQL commands for some databases.%

\footnote{For PostgreSQL and Sybase, a USE <database> command must be executed.%

} Table \ref{t:setdb} summarizes the behaviour according to database product.

\begin{table}

   \begin{center}

      \begin{tabular}{llll}

      Database Vendor   &  Before Connecting &  While Connecting           &  After Connected\\

      \hline 

      PostgreSQL        &  Configuration     &  uses config     &  ``USE'' Executed\\

      MySQL             &  Configuration     &  uses config.    &  MySQL Client Call\\

      Sybase            &  Pending SQL       &  ``USE'' executed   &  ``USE'' Executed\\

      \end{tabular}

   \end{center}

   \caption{Set\_DB\_Name Behaviour}\label{t:setdb}

\end{table}

From the table you can see that PostgreSQL\index{PostgreSQL} and MySQL\index{MySQL} configure the

database prior to connecting\index{connecting} (connecting also establishes the database

to be used). When Set\_\-DB\_\-Name is called after a connection has been

established, the database server will switch\index{switch database} to the requested database.

For some databases, this happens through the native\index{native client API} client API library\index{library}

(MySQL), but for others, a ``USE~<database>''\index{USE database} SQL\index{SQL} command must

be executed (internally within APQ).

\begin{floatingtable}{

   \begin{tabular}{ll}

   Exception Name    &  Reason\\

   \hline 

   Use\_Error        &  Unsuccessful doing a ``USE <database>''\\

   \end{tabular}}

   \caption{Set\_DB\_Name Exceptions}\label{t:sdbx}

\end{floatingtable}

Sybase\index{Sybase} is different again, because does not establish the database

at connect time\index{connect time} (Sybase will use the configured default database).

So a call to Set\_DB\_Name will queue a ``USE <database>''\index{USE database} SQL\index{SQL}

statement, to be executed by APQ, once the connection succeeds. If

another Set\_DB\_Name is performed while connected, new ``USE <database>''

SQL statements are executed on the Sybase server\index{Sybase server} connection provided.

The exceptions listed in Table \ref{t:sdbx}\index{exception} are possible when

using a new database name.

The case of the database name used is affected according to the case policy\index{case policy}

that is in effect for the connection\index{connection}. This is summarized in Table \ref{t:cpol}

for each database vendor product.

\begin{table}

   \begin{center}

      \begin{tabular}{llll}

      Database Product  &  Case Policy          &  Effect on DB\_Name      &  DB Name Sensitive\\

      \hline 

      PostgreSQL        &  Ignored              &  Case preserved          &  Yes\\

      MySQL             &  Ignored              &  Case preserved          &  Yes\\

      Sybase            &  Ignored              &  Case preserved          &  Yes\\

      \end{tabular}

   \end{center}

   \caption{Case Policy by Product}\label{t:cpol}

\end{table}

\subsection{Procedure Set\_User\_Password}

This procedure call configures both the userid\index{userid} and the password\index{password} together.

If there is no password, then supply the null string\index{null string}:

\begin{Code}

procedure Set_User_Password(

   C :             in out Connection_Type;

   User_Name :     in     String;

   User_Password : in     String

);

\end{Code}

The following code fragment illustrates how the userid and

password is configured:

\begin{Example}

declare

   C :   Connection_Type;

begin

   Set_User_Password(C,"myuserid","xyzzy");

\end{Example}

\subsection{Procedure Set\_Case}

One of the goals of APQ was to make writing of portable database\index{portable database code} code

possible. While this isn't completely possible, the goal remains to

reduce database differences. Until the introduction of Sybase\index{Sybase} within

APQ, there was little concern for the case of SQL query\index{SQL query code} code used. 

This APQ manual has always used examples where SQL code is uppercased\index{uppercased SQL code},

with Ada\index{Ada} code using normal Ada95\index{Ada95} conventions. This helps to make the

SQ\index{SQL}L code standout, and separate it from the other code. Sybase introduces

a problem however, since the Sybase server is case sensitive\index{case sensitive} when

referring to tables\index{tables} and column\index{column names} names. This feature cannot be disabled

for a Sybase server%

\footnote{MySQL databases can be configured to be caseless or case sensitive.%

}. Since users of Sybase\index{Sybase} tend to use lowercase\index{lowercase names} names, this creates

a bit of a problem for portable\index{portable SQL} SQL text within APQ.

The approach that APQ 2.2 uses for Sybase\index{Sybase} (and future databases where

this matters), all SQL\index{SQL} code is changed to lowercase\index{lowercase} before being

sent to the server. Before the reader panics thinking that this won't

work, it should be made clear that APQ does not change the case\index{case of SQL} of

any string\index{string} that is quoted\index{quoted} (using the Append\_Quoted\index{Append\_Quoted} functions for

example). A WHERE\index{WHERE clause} clause such as the following will preserve the text

within quotes\index{quotes}:

\begin{SQL}

   where part_no = "XZ98-307"

\end{SQL}

APQ keeps track of what parts of the SQL\index{SQL query} query were quoted\index{quoted} and which

parts were not. This is \emph{not} done by parsing\index{parsing} the SQL text. That

would be prone to error and would require intimate knowledge of every

SQL dialect supported in APQ. Instead, the string\index{string} fragments are marked

for ``preserve''\index{preserve case} case or ``not preserve''\index{preserved, not} as the Query\_Type\index{Query\_Type}

object collects text. When the full SQL text\index{SQL text} is require by a routine

like To\_String\index{To\_String}, then the standing case policy\index{case policy} is applied to each

string\index{string fragment} fragment that has ``not preserve''\index{preserved, not}.

You can change this APQ case policy\index{case policy} for Sybase (or any other database).

The Set\_Case\index{Set\_Case} function gives you complete control over what the policy

to use for SQL case\index{SQL case}. You can choose one of the SQL\_Case\_Type\label{SQL_Case_Type Choices}

\index{SQL\_Case\_Type} options, which are listed in Table~\ref{t:cpolicies}.

\begin{table}

   \begin{center}

      \begin{tabular}{ll}

         Case Policy    &  Description\\

         \hline 

         Preserve\_Case &  Do not make any case changes to the SQL text\\

         Lower\_Case    &  Force unquoted SQL text to lowercase\\

         Upper\_Case    &  Force unquoted SQL text to uppercase\\

      \end{tabular}

   \end{center}

   \caption{Case Policies for SQL Text}\label{t:cpolicies}

\end{table}

\begin{floatingtable}{

   \begin{tabular}{ll}

   Database       &  Default SQL Case Policy\\

   \hline 

   PostgreSQL     &  Upper\_Case\\

   MySQL          &  Lower\_Case\\

   Sybase         &  Lower\_Case\\

   \end{tabular}}

\caption{Default Case Policies}\label{t:dfcpol}

\end{floatingtable}

Table~\ref{t:dfcpol} summarizes the default case policies\index{case policies} used in APQ 2.2

for the different database vendor products supported.

MySQL\index{MySQL} joins Sybase\index{Sybase} in this case policy because by default, MySQL is

case sensitive. If you have configured your MySQL server to be case

insenstive, then any setting will do (Upper\_Case\index{Upper\_Case} is suggested for

the benefit of the trace log displays). Most users of Sybase\index{Sybase} will

likely prefer either the Lower\_Case\index{Lower\_Case} or Preserve\_Case\index{Preserve\_Case}

policies instead.

The Set\_Case\index{Set\_Case} API procedure for the Connection\_Type object is defined

as follows:

\begin{Code}

procedure Set_Case(

   C :        in out Connection_Type;

   SQL_Case : in     SQL_Case_Type

);

\end{Code}

You may also use the Get\_Case\index{Get\_Case} function primitive to find out what

the current policy in effect is. Please note that a change in policy

only affects the outcome of the next call to the following

pimitives of the Query\_Type\index{Query\_Type} object:

\begin{itemize}

   \item To\_String

   \item Execute

   \item Execute\_Checked

\end{itemize}

The Set\_Case\index{Set\_Case} call will not actually change the SQL text\index{SQL text} stored within

the Query\_Type object. It only sets the policy\index{case policy} mode.

The following example shows how to undo the Sybase default of lowercasing

the SQL text:

\begin{Example}

declare

   C : Connection_Type;

   Q : Query_Type;

begin

   ...

   Set_Case(C,Preserve_Case);

   ...

   Append(Q,"from table Mixed_Case");

   Execute(Q,C); -- SQL case is preserved here

\end{Example}

\subsubsection{The Viral Nature of Connection\_Type}

Note that APQ does permit altering the case policy\index{case policy} for Query\_Type\index{Query\_Type}

objects in addition to the Connection\_Type\index{Connection\_Type} object being described

here. It should be noted however, that the setting held by the Connection\_Type

is viral\index{viral nature} in nature. Whenever a Query\_Type object is used in conjunction

with a Connection\_Type object, in a \emph{Execute}\index{Execute} 

or \emph{Execute\_Checked}\index{Execute\_Checked}

call for example, the Query\_Type object will inherit the setting

held by the corresponding Connection\_Type object. For this reason,

the best application practice is to establish your application case

policy\index{case policy} in all Connection\_Type objects used by your application. It

is also best practice to use one Connection\_Type object wherever

possible, since many queries can share one connection\index{shared connection}.

The only reason you should consider applying a case policy\index{case policy} directly

to a Query\_Object, is perhaps for application specific uses of the

SQL text\index{SQL text}. For example, you could build a query\index{query, build a} in a Query\_Type object,

set the case policy to Upper\_Case\index{Upper\_Case}, and then use the To\_String\index{To\_String} function

primitive to extract out the SQL text for logging\index{logging} or user display\index{display}

purposes. Once however the Query\_Type is used with a Connection\_Type

however, (in \emph{Execute}) the Connection\_Type's setting will not

only prevail for the primitive, but will also force the Query\_Type's

policy to agree upon return from the call.

\subsection{Procedure Set\_Options\label{Procedure Set_Options}}

This procedure call permits the caller to specify any specialized

database server options. The options are specified in string form

with this API call. The specific options, and the format\index{format} of those

options\index{options} will vary according to the database being used. See the following

subsections for additional information about the database engine\index{engine, database} specifics.

The procedure Set\_Options\index{Set\_Options} is documented as follows:

\begin{Code}

procedure Set_Options(

   C :       in out Connection_Type;

   Options : in     String

);

\end{Code}

Exceptions\index{exceptions} can be raised if the options are being set on a connection

that is already connected. Table \ref{t:soopts} lists the exceptions that may be

raised by Set\_Options.

\begin{table}

   \begin{center}

      \begin{tabular}{ll}

      Exception Name    &  Reason\\

      \hline 

      Failed            &  The option is either unsupported or setting was rejected\\

      \end{tabular}

   \end{center}

   \caption{Set\_Options Exceptions}\label{t:soopts}

\end{table}

If the options are configured for an unconnected\index{unconnected} Connection\_Type

object, the exceptions if any, will be raised at the time of connection

(See the Connect primitive).

The following PostgreSQL\index{PostgreSQL} code fragment illustrates how two options\index{options}

may be configured:

\begin{Example}

declare

   C : Connection_Type;

begin

   Set_Options(C,"requiressl=1 dbname=test");

\end{Example}

Note that in this example, the option string has been used to declare

the database name to be used. Standard values should be set through

the primitive functions provided (ie. use Set\_DB\_Name\index{Set\_DB\_Name} instead).

Otherwise, when information primitives are added, you may not get

correct results. Any non-standard options like the ``requiressl''\index{requiressl}

option, should be configured as shown in this procedure call.

MySQL\index{MySQL} and Sybase\index{Sybase}, use a comma delimited list\index{delimited list} of options\index{options}.

The following example shows how to get Sybase I/O\index{statistics, Sybase I/O} 

and time statistics\index{statistics, time} recorded in your APQ trace\index{trace log} log:

\begin{Example}

declare

  C : Connection_Type;

begin

   Set_Options(C,"STATS_IO=TRUE,STATS_TIME=TRUE");

\end{Example}

Option parsing\index{parsing} is rather limited. Everything between the '=' sign

and the next comma is the value for the parameter. Different database

products will use different parameter types\index{paramater types}. For example, MySQL\index{MySQL} will

use 0 to represent False\index{false}, and 1 to represent True\index{true}. For Sybase\index{Sybase}, use

F or False, and T or True for Boolean\index{Boolean} values.

\subsubsection{PostgreSQL Options}

The documentation is not very clear about the format\index{format} of these options,

but it appears that keyword=value\index{keyword value pairs}  pairs\index{keyword=value pairs}

separated by \emph{spaces} for multiple options\index{options} are

accepted. If you must include spaces or other special characters within

the value component, then you must follow PostgreSQL\index{PostgreSQL} escaping rules\index{escaping rules}.

Refer to the database server documentation for these details.

\subsubsection{MySQL Options}

MySQL's\index{MySQL} C interface\index{C interface} is much different than PostgreSQL's C interface

for options. MySQL uses an enumerated value\index{enumerated value} and argument pair\index{argument pair} when

setting an option\index{option}.%

\footnote{Although, some options do not use the argument.%

} To keep the APQ interface friendly and consistent, APQ will accept

all options and arguments in a string\index{string} form as documented in

\Ref{Procedure Set_Options}. However, these string options\index{string options} must be

processed by APQ and digested into arguments usable by the MySQL C

client interface. Consequently, APQ must anticipate these options

and the option format in advance. For these reasons, the MySQL options

and their arguments will be partially documented here.

The format\index{format of option string} of the option string should be one or more option names

and arguments, separated by commas. Option names\index{option names} are treated as caseless\index{caseless}

(internally upcased\index{upcased}).

\begin{Example}

   Set\_Options(C,"CONNECT_TIMEOUT=3,COMPRESS,LOCAL_INFIL=1");

\end{Example}

Each option should be separated by a comma. APQ processes each option

in left to right fashion, making multiple MySQL C API calls for each

one. Table~\ref{t:myopts} lists the APQ supported options for MySQL.

\begin{table}

   \begin{center}

      \begin{tabular}{lll}

         Option Name          &  Argument Type           &  Comments\\

         \hline 

         CONNECT\_TIMEOUT     &  Unsigned                &  Seconds\\

         COMPRESS             &  None                    &  Compressed comm link\\

         NAMED\_PIPE          &  None                    &  Windows: use a named pipe\\

         INIT\_COMMAND        &  String                  &  Initialization command\\

         READ\_DEFAULT\_FILE  &  String                  &  See MySQL\\

         READ\_DEFAULT\_GROUP &  String                  &  See MySQL\\

         SET\_CHARSET\_DIR    &  String                  &  See MySQL\\

         SET\_CHARSET\_NAME   &  String                  &  See MySQL\\

         LOCAL\_INFIL         &  Boolean                 &  See MySQL\\

      \end{tabular}

   \end{center}

   \caption{MySQL Options}\label{t:myopts}

\end{table}

It is important to observe that any option that requires an argument\index{argument, option},

must have one. Any argument that requires an unsigned integer\index{unsigned}, must

have an unsigned integer (otherwise an exception\index{exception} is raised). A MySQL\index{MySQL}

Boolean\index{Boolean} argument should be the value 0 or 1. At the present time,

APQ gathers string\index{string} data up until the next comma or the end of the

string. Currently an option argument string cannot contain a comma

character.

\footnote{This needs to be corrected in a future release of APQ.}

\subsubsection{Sybase Options}

Sybase documents several options\index{options, Sybase} in their ``Open Client C Programmer's

Reference'' manual. All documented options are made available to

the APQ developer, although some options are not recommended. 

\begin{floatingtable}{

   \begin{tabular}{ll}

   Option Value   &  Meaning\\

   \hline 

   TRUE           &  True\\

   T              &  True\\

   FALSE          &  False\\

   F              &  False\\

   \end{tabular}}

   \caption{Boolean Option Values}\label{t:boolarg}

\end{floatingtable}

Each Sybase option value must conform to certain data type format\index{format, option}

and/or restrictions. Table~\ref{t:boolarg} lists

acceptable argument values for Boolean\index{Boolean} options, within APQ. The

case is not significant, but uppercase is recommended to make it stand out from

the surrounding Ada code.

The following is an example of a Boolean option setting for the Sybase option STATS\_IO:

\begin{Example}

   "STATS_IO=TRUE"

\end{Example}

Some Sybase options require an integer\index{unsigned integer} (actually unsigned). Those

options listed in the option table\index{option table} as requiring an Unsigned value,

should be simply an unsigned value. The following is an example:

\begin{Example}

   "ROWCOUNT=1"

\end{Example}

String value arguments\index{string value arguments} are simply textual values that are placed between

the equal sign and the next comma (or end of string). The following

example illustrates:

\begin{Example}

   "AUTHOFF=sa"

\end{Example}

Some options are identified as taking ``String/NULL''. These options

will take normal string values or simply the word NULL\index{NULL options}. The value

NULL allows the system default to be used. The following is an example:

\begin{Example}

   "CHARSET=NULL"

\end{Example}

\begin{table}

   \begin{center}

      \begin{tabular}{ll}

         Value             &  Sybase Option\\

         \hline 

         SUNDAY            &  CS\_OPT\_SUNDAY\\

         MONDAY            &  CS\_OPT\_MONDAY\\

         TUESDAY           &  CS\_OPT\_TUESDAY\\

         WEDNESDAY         &  CS\_OPT\_WEDNESDAY\\

         THURSDAY          &  CS\_OPT\_THURSDAY\\

         FRIDAY            &  CS\_OPT\_FRIDAY\\

         SATURDAY          &  CS\_OPT\_SATURDAY\\

      \end{tabular}

   \end{center}

   \caption{Weekday Option Argument Values}\label{t:wkday}

\end{table}

The DATEFIRST option\index{DATEFIRST option} accepts an argument Weekday argument. The valid

range of values are listed in Table~\ref{t:wkday}.

The following example shows how the DATEFIRST option is used:

\begin{Example}

   "DATEFIRST=SUNDAY"

\end{Example}

\begin{table}

   \begin{center}

      \begin{tabular}{ll}

      Value    &  Sybase Option\\

      \hline 

      MDY      &  CS\_OPT\_FMTMDY\\

      DMY      &  CS\_OPT\_FMTDMY\\

      YMD      &  CS\_OPT\_FMTYMD\\

      YDM      &  CS\_OPT\_FMTYDM\\

      MYD      &  CS\_OPT\_FMTMYD\\

      DYM      &  CS\_OPT\_FMTDYM\\

      \end{tabular}

   \end{center}

   \caption{Date Format Argument Values}\label{t:dtfmt}

\end{table}

One last category of Sybase option arguments is the DATEFORMAT argument\index{DATEFORMAT option}

type. The valid list of values for this type of argument are given in Table~\ref{t:dtfmt}.

The following is an example of such an option:

\begin{Example}

   "DATEFORMAT=YMD"

\end{Example}

Table~\ref{t:syonames} lists all of the options that APQ is able to recognize for Sybase.

Each of the option names\index{option names} listed is equivalent to the 

Sybase C macro\index{C macro, Sybase}

constant if the prefix CS\_OPT\_\index{CS\_OPT\_{*}} is added to the option name. Consult

the Sybase documentation for descriptions of the purpose of these

options and when to apply them.

\begin{longtable}{lll}

Option Name       &  Data Type            &  Notes\\

\hline 

ANSINULL          &  Boolean              &\\

ANSIPERM          &  Boolean              &\\

ARITHABORT        &  Boolean              &\\

ARITHIGNORE       &  Boolean              &\\

AUTHOFF           &  String               &\\

AUTHON            &  String               &\\

CHAINXACTS        &  Boolean              &\\

CHARSET           &  String/NULL          &  Use NULL for default\\

CURCLOSEONXACT    &  Boolean              &\\

DATEFIRST         &  Weekday              &\\

DATEFORMAT        &  YMD/MDY/etc.         &  May interfere with APQ\\

FIPSFLAG          &  Boolean              &\\

FORCEPLAN         &  Boolean              &\\

FORMATONLY        &  Boolean              &\\

GETDATA           &  Boolean              &\\

IDENTITYOFF       &  String               &\\

IDENTITYON        &  String               &\\

IDENTITYUPD\_OFF  &  String               &\\

IDENTITYUPD\_ON   &  String               &\\

ISOLATION         &  Unsigned             &  0, 1 or 3\\

NATLANG           &  String/NULL          &  Use NULL for default\\

NOCOUNT           &  Boolean              &\\

NOEXEC            &  Boolean              &\\

PARSEONLY         &  Boolean              &\\

QUOTED\_IDENT     &  Boolean              &\\

RESTREES          &  Boolean              &\\

ROWCOUNT          &  Unsigned             &\\

SHOWPLAN          &  Boolean              &  Use at your own risk\\

STATS\_IO         &  Boolean              &\\

STATS\_TIME       &  Boolean              &\\

STR\_RTRUNC       &  Boolean              &\\

TEXTSIZE          &  Unsigned             &\\

TRUNCIGNORE       &  Boolean              &\\

\caption{Sybase Option Names}\label{t:syonames}

\end{longtable}

Keep in mind that not all option settings will be compatible for use