Copyright: ASA Version 7.0.2 Copyright © 2000 Sybase, Inc. All rights reserved.


Programming Interfaces Guide
ASA

Version 7.0.2

Document ID:  MC0059

November 2000

 Copyright page

 About This Manual

 CHAPTER 1.  Programming Interface Overview

 CHAPTER 2.  The Embedded SQL Interface

 CHAPTER 3.  The Database Tools Interface

 CHAPTER 4.  ODBC Programming

 CHAPTER 5.  The OLE DB and ADO Programming Interfaces

 CHAPTER 6.  The Open Client Interface


Copyright page
  ASA Programming Interfaces Guide

 Copyright page

Copyright © 2000 Sybase, Inc. All rights reserved.

No part of this publication may be reproduced, transmitted, or translated in any form or by any means, electronic, mechanical, manual, optical, or otherwise, without the prior written permission of Sybase, Inc.

Sybase, SYBASE (logo), ADA Workbench, Adaptable Windowing Environment, Adaptive Component Architecture, Adaptive Server, Adaptive Server Anywhere, Adaptive Server Enterprise, Adaptive Server Enterprise Monitor, Adaptive Server Enterprise Replication, Adaptive Server Everywhere, Adaptive Server IQ, Adaptive Warehouse, AnswerBase, Anywhere Studio, Application Manager, AppModeler, APT-Build, APT-Edit, APT-Execute, APT-FORMS, APT-Library, APT-Translator, APT Workbench, ASEP, Backup Server, BayCam, Bit-Wise, Certified PowerBuilder Developer, Certified SYBASE Professional, Certified SYBASE Professional (logo), ClearConnect, Client Services, Client-Library, CodeBank, Cohesion, Column Design, ComponentPack, Connection Manager, CSP, Data Pipeline, Data Workbench, DataArchitect, Database Analyzer, DataExpress, DataServer, DataWindow, DB-Library, dbQueue, Developers Workbench, Direct Connect Anywhere, DirectConnect, Distribution Director, Dynamo, E-Anywhere, E-Whatever, Electronic Case Management, Embedded SQL, EMS, Enterprise Application Server, Enterprise Application Studio, Enterprise Client/Server, Enterprise Connect, Enterprise Data Studio, Enterprise Manager, Enterprise SQL Server Manager, Enterprise Work Architecture, Enterprise Work Designer, Enterprise Work Modeler, EWA, Financial Fusion, First Impression, Formula One, Gateway Manager, GeoPoint, ImpactNow, InfoMaker, Information Anywhere, Information Everywhere, InformationConnect, InstaHelp, Intellidex, InternetBuilder, iScript, Jaguar CTS, jConnect for JDBC, KnowledgeBase, Logical Memory Manager, MainframeConnect, Maintenance Express, MAP, MDI Access Server, MDI Database Gateway, media.splash, MetaWorks, MethodSet, MobiCATS, MySupport, Net-Gateway, Net-Library, NetImpact, Next Generation Learning, Next Generation Learning Studio, O DEVICE, OASiS, OASiS (logo), ObjectConnect, ObjectCycle, OmniConnect, OmniSQL Access Module, OmniSQL Toolkit, Open Client, Open Client/Server, Open Client/Server Interfaces, Open ClientConnect, Open Gateway, Open Server, Open ServerConnect, Open Solutions, Optima++, Partnerships that Work, PB-Gen, PC APT Execute, PC DB-Net, PC Net Library, PhysicalArchitect, Power Through Knowledge, Power++, power.stop, PowerAMC, PowerBuilder, PowerBuilder Foundation Class Library, PowerDesigner, PowerDimensions, PowerDynamo, PowerJ, PowerScript, PowerSite, PowerSocket, Powersoft, Powersoft Portfolio, Powersoft Professional, PowerStage, PowerStudio, PowerTips, PowerWare Desktop, PowerWare Enterprise, ProcessAnalyst, Relational Beans, Replication Agent, Replication Driver, Replication Server, Replication Server Manager, Replication Toolkit, Report Workbench, Report-Execute, Resource Manager, RW-DisplayLib, RW-Library, S-Designor, S Designor, SAFE, SAFE/PRO, SDF, Secure SQL Server, Secure SQL Toolset, Security Guardian, SKILS, smart.partners, smart.parts, smart.script, SQL Advantage, SQL Anywhere, SQL Anywhere Studio, SQL Code Checker, SQL Debug, SQL Edit, SQL Edit/TPU, SQL Everywhere, SQL Modeler, SQL Remote, SQL Server, SQL Server Manager, SQL Server SNMP SubAgent, SQL Server/CFT, SQL Server/DBM, SQL SMART, SQL Station, SQL Toolset, SQLJ, Startup.Com, STEP, SupportNow, Sybase Central, Sybase Client/Server Interfaces, Sybase Development Framework, Sybase Financial Server, Sybase Gateways, Sybase Learning Connection, Sybase SQL Desktop, Sybase SQL Lifecycle, Sybase SQL Workgroup, Sybase Synergy Program, Sybase User Workbench, Sybase Virtual Server Architecture, Sybase MPP, SybaseWare, Syber Financial, SyberAssist, SyBooks, System XI (logo), System 10, System 11, SystemTools, Tabular Data Stream, The Enterprise Client/Server Company, The Extensible Software Platform, The Future Is Wide Open, The Learning Connection, The Model For Client/Server Solutions, The Online Information Center, Transact-SQL, Translation Toolkit, Turning Imagination Into Reality, UltraLite, UNIBOM, Unilib, Uninull, Unisep, Unistring, URK Runtime Kit for UniCode, Viewer, Visual Components, VisualSpeller, VisualWriter, VQL, Warehouse Control Center, Warehouse Studio, Warehouse WORKS, WarehouseArchitect, Watcom, Watcom SQL Server, Watcom SQL, Web.PB, Web.SQL, Web Deployment Kit, WebSights, WebViewer, WorkGroup SQL Server, XA-Library, XA-Server, and XP Server are trademarks of Sybase, Inc. or its subsidiaries.

All other trademarks are property of their respective owners.

Last modified: June 2000. Part Number: MC0059.

 Copyright page

 About This Manual

 CHAPTER 1.  Programming Interface Overview

 CHAPTER 2.  The Embedded SQL Interface

 CHAPTER 3.  The Database Tools Interface

 CHAPTER 4.  ODBC Programming

 CHAPTER 5.  The OLE DB and ADO Programming Interfaces

 CHAPTER 6.  The Open Client Interface


About This Manual
  ASA Programming Interfaces Guide

 About This Manual

 About This Manual

 Related documentation

 Documentation conventions

 The sample database


About This Manual
  ASA Programming Interfaces Guide

    About This Manual

 About This Manual

Subject

This manual describes the programming interfaces available for Adaptive Server Anywhere.

Audience

This manual is intended for application developers writing programs that work directly with one of the Adaptive Server Anywhere interfaces.

You do not need to read this manual if you are using a development tool, such as Powersoft PowerBuilder or Power++, which has its own database interface on top of ODBC.


Related documentation
  ASA Programming Interfaces Guide

    About This Manual

 Related documentation

Adaptive Server Anywhere is a part of SQL Anywhere Studio. For an overview of the different components of SQL Anywhere Studio, see Introducing SQL Anywhere Studio.

The Adaptive Server Anywhere documentation consists of the following books:

•	Getting Started    Intended for all users of Adaptive Server Anywhere, this book describes the following:

•	New features in Adaptive Server Anywhere

•	Behavior changes from previous releases

•	Upgrade procedures

•	Introductory material for beginning users.

•	User's Guide    A comprehensive guide to using SQL, administering databases, and using Adaptive Server Anywhere features.

•	Reference    A full reference to Adaptive Server Anywhere. This book describes the database server, the administration utilities, the SQL language, and error messages.

•	Quick Reference    A handy printed booklet with complete SQL syntax and other key reference material in a concise format.

•	Read Me First (UNIX only)    A separate booklet is provided with UNIX versions of Adaptive Server Anywhere, describing installation and adding some UNIX-specific notes.

The format of these books (printed or online) may depend on the product in which you obtained Adaptive Server Anywhere. Depending on which package you have purchased, you may have additional books describing other components of your product.


Documentation conventions
  ASA Programming Interfaces Guide

    About This Manual

 Documentation conventions

This section lists the typographic and graphical conventions used in this documentation.

 Syntax conventions

 Graphic icons

 Installed files


Syntax conventions
  ASA Programming Interfaces Guide

    About This Manual

      Documentation conventions

 Syntax conventions

The following conventions are used in the SQL syntax descriptions:

•	Keywords    All SQL keywords are shown in UPPER CASE. However, SQL keywords are case insensitive, so you can enter keywords in any case you wish; SELECT is the same as Select which is the same as select.

•	Placeholders    Items that must be replaced with appropriate identifiers or expressions are shown in italics.

•	Continuation    Lines beginning with ... are a continuation of the statements from the previous line.

•	Repeating items    Lists of repeating items are shown with an element of the list followed by an ellipsis (three dots). One or more list elements are allowed. If more than one is specified, they must be separated by commas.

•	Optional portions    Optional portions of a statement are enclosed by square brackets. For example, ...

RELEASE SAVEPOINT [ savepoint-name ]

... indicates that the savepoint-name is optional. The square brackets should not be typed.

•	Options    When none or only one of a list of items must be chosen, the items are separated by vertical bars and the list enclosed in square brackets.

For example, ...

[ ASC | DESC ]

... indicates that you can choose one of ASC, DESC, or neither. The square brackets should not be typed.

•	Alternatives    When precisely one of the options must be chosen, the alternatives are enclosed in curly braces. For example ...

QUOTES { ON | OFF }

... indicates that exactly one of ON or OFF must be provided. The braces should not be typed.


Graphic icons
  ASA Programming Interfaces Guide

    About This Manual

      Documentation conventions

 Graphic icons

The following icons are used in this documentation:

Icon	Meaning
	A client application. If the icon is used to represent a particular application or kind of application, the name is indicated in the bottom right corner.
	A database server, such as Sybase Adaptive Server Anywhere or Adaptive Server Enterprise. If the icon is used to represent a particular kind of database server, such as Adaptive Server Anywhere, the name is indicated in the bottom  right corner.
	An UltraLite application and database server. In UltraLite, the database server and the application are part of the same process.
	A database. In some high-level diagrams, the icon may be used to represent both the database and the database server that manages it.
	Replication or synchronization middleware. These pieces of software assist in sharing data among databases. Examples are the MobiLink Synchronization Server, the SQL Remote Message Agent, and the Replication Agent (Log Transfer Manager) for use with Replication Server. If the particular kind of middleware is not obvious from the context of the diagram, the name is indicated in the bottom  right hand corner.
	A Sybase Replication Server.


Installed files
  ASA Programming Interfaces Guide

    About This Manual

      Documentation conventions

 Installed files

The following terms are used throughout the manual:

•	Installation directory    The directory into which you install Adaptive Server Anywhere.

•	Executable directory    The executables and other files for each operating system are held in an executable subdirectory of the installation directory. This subdirectory has the following name:

•	Windows NT and Windows 95/98    win32

•	UNIX    bin

•	Windows 3.x    win

•	NetWare and Windows CE    The executables are held in the Adaptive Server Anywhere installation directory itself on these platforms.


The sample database
  ASA Programming Interfaces Guide

    About This Manual

 The sample database

There is a sample database included with Adaptive Server Anywhere. Many of the examples throughout the documentation use this sample database.

The sample database represents a small company. It contains internal information about the company (employees, departments, and financial data) as well as product information (products), sales information (sales orders, customers, and contacts), and financial information (fin_code, fin_data).

The following figure shows the tables in the sample database and how they are related to each other.

The sample database is held in a file named asademo.db, and is located in your installation directory.

 Copyright page

 About This Manual

 CHAPTER 1.  Programming Interface Overview

 CHAPTER 2.  The Embedded SQL Interface

 CHAPTER 3.  The Database Tools Interface

 CHAPTER 4.  ODBC Programming

 CHAPTER 5.  The OLE DB and ADO Programming Interfaces

 CHAPTER 6.  The Open Client Interface


CHAPTER 1.  Programming Interface Overview
  ASA Programming Interfaces Guide

 CHAPTER 1.  Programming Interface Overview

About this chapter

This chapter provides an introductory overview of each of the programming interfaces for Adaptive Server Anywhere. Any client application uses one of these interfaces to communicate with the database, and this chapter provides some guidelines to help you choose an appropriate interface for your application.

Before you begin

  For an introduction to some aspects of database programming, see Using SQL in Applications.

 The ODBC programming interface

 The Embedded SQL programming interface

 The JDBC programming interface

 The Open Client programming interface


The ODBC programming interface
  ASA Programming Interfaces Guide

    CHAPTER 1.  Programming Interface Overview

 The ODBC programming interface

ODBC (Open Database Connectivity) is a standard call level interface (CLI) developed by Microsoft. It is based on the SQL Access Group CLI specification. ODBC applications can run against any data source that provides an ODBC driver. You should use ODBC if you would like your application to be portable to other data sources that have ODBC drivers. Also, if you prefer working with an API, use ODBC.

ODBC is a low-level interface—about the same as Embedded SQL. Almost all the Adaptive Server Anywhere functionality is available with this interface. ODBC is available as a DLL under Windows NT, Windows 95, and Windows 3.x. It is provided as a library for UNIX.

The primary documentation for ODBC is the Microsoft ODBC Software Development Kit. The current book provides some additional notes specific to Adaptive Server Anywhere for ODBC developers.

  ODBC is described in ODBC Programming


The Embedded SQL programming interface
  ASA Programming Interfaces Guide

    CHAPTER 1.  Programming Interface Overview

 The Embedded SQL programming interface

Embedded SQL is a system in which SQL commands are embedded right in a C or C++ source file. A preprocessor translates these statements into calls to a runtime library. Embedded SQL is an ISO/ANSI and IBM standard.

Embedded SQL is portable to other databases and other environments, and is functionally equivalent in all operating environments. It provides all of the functionality available in the product. Embedded SQL is quite easy to work with, although it takes a little getting used to the idea of Embedded SQL statements (rather than function calls) in C code.

  Embedded SQL is described in The Embedded SQL Interface.


The JDBC programming interface
  ASA Programming Interfaces Guide

    CHAPTER 1.  Programming Interface Overview

 The JDBC programming interface

JDBC is a call-level interface for Java applications. Developed by Sun Microsystems, JDBC provides Java programmers with a uniform interface to a wide range of relational databases, and provides a common base on which higher level tools and interfaces can be built. JDBC is now a standard part of Java and is included in the JDK.

Sybase provides a JDBC driver, named Sybase jConnect. You can use jConnect to access Adaptive Server Anywhere databases. Sybase jConnect documentation is provided in Dynatext format.

In addition to using JDBC as a client side application programming interface, you can also use JDBC inside the database server to access data from Java in the database. For that reason JDBC is documented as part of the Java in the database documentation.

  JDBC is not described in this book. For a description of JDBC, see Data Access Using JDBC


The Open Client programming interface
  ASA Programming Interfaces Guide

    CHAPTER 1.  Programming Interface Overview

 The Open Client programming interface

Sybase Open Client provides customer applications, third-party products, and other Sybase products with the interfaces needed to communicate with Adaptive Server Anywhere and other Open Servers.

When to use Open Client

You should consider using the Open Client interface if you are concerned with Adaptive Server Enterprise compatibility or if you are using other Sybase products that support the Open Client interface, such as Replication Server.

  The Open Client interface is described in The Open Client Interface. Other information is presented in Adaptive Server Anywhere as an Open Server.

 Open Client architecture


Open Client architecture
  ASA Programming Interfaces Guide

    CHAPTER 1.  Programming Interface Overview

      The Open Client programming interface

 Open Client architecture

Open Client can be thought of as comprising two components: programming interfaces and network services.

Client Library and DB-Library

Open Client provides two core programming interfaces for writing client applications: Client-Library and DB-Library.

Open Client DB-Library provides support for older Open Client applications, and is a completely separate programming interface from Client-Library. DB-Library is documented in the Open Client DB-Library/C Reference Manual, provided with the Sybase Open Client product.

Client-Library programs also depend on CS-Library, which provides routines that are used in both Client-Library and Server-Library applications. Client-Library applications can also use routines from Bulk-Library to facilitate high-speed data transfer.

Both CS-Library and Bulk-Library are included in the Sybase Open Client, available separately.

Network services

Open Client network services include Sybase Net-Library, which provides support for specific network protocols such as TCP/IP and DECnet. The Net-Library interface is invisible to application programmers. However, on some platforms an application may need a different Net-Library driver for different system network configurations. Depending on your host platform, the Net-Library driver is specified either by the system's Sybase configuration, or when you compile and link your programs.

  Instructions for driver configuration can be found in the Open Client/Server Configuration Guide. Instructions for building Client-Library programs can be found in the Open Client/Server Programmer's Supplement.


CHAPTER 2.  The Embedded SQL Interface
  ASA Programming Interfaces Guide

 CHAPTER 2.  The Embedded SQL Interface

About this chapter

This chapter describes the Embedded SQL programming interface to Adaptive Server Anywhere.

 Application development using Embedded SQL

 Embedded SQL data types

 Using host variables

 The SQL Communication Area

 Fetching data

 Static and dynamic SQL

 The SQL descriptor area (SQLDA)

 Using stored procedures in Embedded SQL

 Library functions

 Embedded SQL commands

 Database examples

 The SQL preprocessor


Application development using Embedded SQL
  ASA Programming Interfaces Guide

    CHAPTER 2.  The Embedded SQL Interface

 Application development using Embedded SQL

Embedded SQL consists of SQL statements intermixed with C or C++ source code. These SQL statements are translated by a SQL preprocessor into C or C++ source code. The SQL preprocessor is run before compilation.

This code, together with the Adaptive Server Anywhere interface library communicates the appropriate information to the database server when the program is run. The interface library is a dynamic link library (DLL) or shared library on most platforms.

•	On Windows 95/98, Windows CE, and Windows NT/2000, the interface library is dblib7.dll.

•	On UNIX operating systems, the interface library is libdblib7.so, libdblib7.sl, or libdblib7.a, depending on the operating system.

 Supported compilers

 Development process overview

 Running the SQL preprocessor

 Embedded SQL header files

 Import libraries

 A simple example

 Structure of Embedded SQL programs

 Loading the interface library dynamically

 Building NetWare Loadable Modules


Supported compilers
  ASA Programming Interfaces Guide

    CHAPTER 2.  The Embedded SQL Interface

      Application development using Embedded SQL

 Supported compilers

The C language SQL preprocessor has been used in conjunction with the following compilers:

Operating system	Compiler	Version
Windows 95/98 and NT/2000	Watcom C/C++	9.5 and above
Windows 95/98 and NT/2000	Microsoft Visual C/C++	1.0 and above
Windows 95/98 and NT/2000	Borland C++	4.5
Windows CE	Microsoft Visual C/C++	5.0
UNIX	GNU or native compiler
NetWare	Watcom C/C++	10.6, 11
  For instructions on building NetWare NLMs, see Building NetWare Loadable Modules.


Development process overview
  ASA Programming Interfaces Guide

    CHAPTER 2.  The Embedded SQL Interface

      Application development using Embedded SQL

 Development process overview

Once the program has been successfully preprocessed and compiled, it is linked with the import library for the Adaptive Server Anywhere interface library to form an executable file. When the database is running, this executable file will use the Adaptive Server Anywhere DLL to interact with the database. The database does not have to be running when the program is preprocessed.

For Windows 95/98 and Windows NT/2000, there are separate import libraries for Watcom C/C++, for Microsoft Visual C++, and for Borland C++.

  Using import libraries is the standard development method for applications that call functions in DLLs. Adaptive Server Anywhere also provides an alternative, and recommended, method, which avoids the use of import libraries. For more information, see Loading the interface library dynamically.


Running the SQL preprocessor
  ASA Programming Interfaces Guide

    CHAPTER 2.  The Embedded SQL Interface

      Application development using Embedded SQL

 Running the SQL preprocessor

The SQL preprocessor is an executable named sqlpp.exe.

Command line

The SQLPP command line is as follows:

SQLPP [ switches ] sql-filename [output-filename]

The SQL preprocessor processes a C program with Embedded SQL before the C or C++ compiler is run. The preprocessor translates the SQL statements into C/C++ language source that is put into the output file. The normal extension for source programs with Embedded SQL is .sqc. The default output filename is the sql-filename with an extension of .c. If the sql-filename already has a .c extension, then the output filename extension is .cc by default.

  For a full listing of the command-line switches, see The SQL preprocessor.


Embedded SQL header files
  ASA Programming Interfaces Guide

    CHAPTER 2.  The Embedded SQL Interface

      Application development using Embedded SQL

 Embedded SQL header files

All header files are installed in the h subdirectory of your Adaptive Server Anywhere installation directory.

Filename	Description
sqlca.h	Main header file included in all Embedded SQL programs. This file includes the structure definition for the SQL Communication Area (SQLCA) and prototypes for all Embedded SQL database interface functions.
sqlda.h	SQL Descriptor Area structure definition included in Embedded SQL programs that use dynamic SQL.
sqldef.h	Definition of Embedded SQL interface data types. This file also contains structure definitions and return codes needed for starting the database server from a C program.
sqlerr.h	Definitions for error codes returned in the sqlcode field of the SQLCA.
sqlstate.h	Definitions for ANSI/ISO SQL standard error states returned in the sqlstate field of the SQLCA.
pshpk1.h, pshpk2.h, poppk.h	These headers ensure that structure packing is handled correctly. They support Watcom C/C++, Microsoft Visual C++, IBM Visual Age, and Borland C/C++ compilers.


Import libraries
  ASA Programming Interfaces Guide

    CHAPTER 2.  The Embedded SQL Interface

      Application development using Embedded SQL

 Import libraries

All import libraries are installed in the lib subdirectory, under the operating system subdirectory of the Adaptive Server Anywhere installation directory. For example, Windows 95/98 and Windows NT/2000 import libraries are stored in the win32\lib subdirectory.

Operating system	Compiler	Import library
Windows 95/98 and NT/2000	Watcom C/C++	dblibtw.lib
Windows 95/98 and NT/2000	Borland C++	dblibtb.lib
Windows 95/98 and NT/2000	Microsoft Visual C++	dblibtm.lib
Windows CE	Microsoft Visual C++	dblib7.lib
NetWare	Watcom C/C++	dblib7.lib
Solaris (unthreaded applications)	All compilers	libdblib7.so, libdbtasks7.so
Solaris (threaded applications)	All compilers	libdblib7_r.so, libdbtasks7_r.so
AIX (unthreaded applications)	All compilers	libdblib7.a, libdbtasks7.a
AIX (threaded applications)	All compilers	libdblib7_r.a libdbtasks7_r.a
HP-UX (unthreaded applications)	All compilers	libdblib7.sl libdbtasks7.sl
HP-UX (threaded applications)	All compilers	libdblib7_r.sl libdbtasks7_r.sl
The libdbtasks7 libraries are called by the libdblib7 library. Some compilers locate libdbtasks7 automatically, while for others you need to specify it explicitly.


A simple example
  ASA Programming Interfaces Guide

    CHAPTER 2.  The Embedded SQL Interface

      Application development using Embedded SQL

 A simple example

The following is a very simple example of an Embedded SQL program.

#include <stdio.h>

EXEC SQL INCLUDE SQLCA;

main()

{

db_init( &sqlca );

EXEC SQL WHENEVER SQLERROR GOTO error;

EXEC SQL CONNECT "dba" IDENTIFIED BY "sql";

EXEC SQL UPDATE employee

SET emp_lname = 	'Plankton'

WHERE emp_id = 195;

EXEC SQL COMMIT WORK;

EXEC SQL DISCONNECT;

db_fini( &sqlca );

return( 0 );

error:

printf( "update unsuccessful -- sqlcode = %ld.n",

sqlca.sqlcode );

db_fini( &sqlca );

return( -1 );

}

This example connects to the database, updates the surname of employee number 195, commits the change and exits. There is virtually no interaction between the SQL and C code. The only thing the C code is used for in this example is control flow. The WHENEVER statement is used for error checking. The error action (GOTO in this example) is executed after any SQL statement that causes an error.

Note that the first section of this chapter uses UPDATE and INSERT examples because they are simpler.

  For a description of fetching data, see Fetching data.


Structure of Embedded SQL programs
  ASA Programming Interfaces Guide

    CHAPTER 2.  The Embedded SQL Interface

      Application development using Embedded SQL

 Structure of Embedded SQL programs

SQL statements are placed (embedded) within regular C or C++ code. All Embedded SQL statements start with the words EXEC SQL and end with a semicolon (;). Normal C language comments are allowed in the middle of Embedded SQL statements.

Every C program using Embedded SQL must contain the following statement before any other Embedded SQL statements in the source file.

EXEC SQL INCLUDE SQLCA;

The first Embedded SQL statement executed by the C program must be a CONNECT statement. The CONNECT statement is used to establish a connection with the database server and to specify the user ID that is used for authorizing all statements executed during the connection.

The CONNECT statement must be the first Embedded SQL statement executed. Some Embedded SQL commands do not generate any C code, or do not involve communication with the database. These commands are thus allowed before the CONNECT statement. Most notable are the INCLUDE statement and the WHENEVER statement for specifying error processing.


Loading the interface library dynamically
  ASA Programming Interfaces Guide

    CHAPTER 2.  The Embedded SQL Interface

      Application development using Embedded SQL

 Loading the interface library dynamically

The usual practice for developing applications that use functions from DLLs is to link the application against an import library, which contains the required function definitions.

This section describes an alternative to using an import library for developing Adaptive Server Anywhere applications. The Adaptive Server Anywhere interface library can be loaded dynamically, without having to link against the import library, using the esqldll.c module in the src subdirectory of your installation directory. Using esqldll.c is recommended, because it is easier to use and more robust in its ability to locate the interface DLL.

♦ To load the interface DLL dynamically:

1	Your program must call db_init_dll to load the DLL, and must call db_fini_dll to free the DLL. The db_init_dll call must be before any function in the database interface, and no function in the interface can be called after db_fini_dll.

You must still call the db_init and db_fini library functions.

2	You must #include the esqldll.h header file before the EXEC SQL INCLUDE SQLCA statement or #include <sqlca.h> line in your Embedded SQL program.

3	A SQL OS macro must be defined. The header file sqlca.h, which is included by esqdll.c, attempts to determine the appropriate macro and defines it. However, certain combinations of platforms and compilers may cause this to fail. In this case, you must add a #define to the top of this file, or make the definition by using a compiler option.

Macro	Platforms
_SQL_OS_WINNT	Windows 95/98, Windows CE, and Windows NT/2000
_SQL_OS_UNIX	UNIX
_SQL_OS_NETWARE	NetWare
4	Compile esqldll.c.

5	Instead of linking against the imports library, link the object module esqldll.obj with your Embedded SQL application objects.

Example

The following example illustrates how to use esqldll.c and esqldll.h.

#include <stdio.h>

#include "esqldll.h"

EXEC SQL INCLUDE SQLCA;

#include "sqldef.h"

#include <windows.h>

EXEC SQL BEGIN DECLARE SECTION;

int	x;

a_sql_statement_number 	stat1;

EXEC SQL END DECLARE SECTION;

#define TRUE 1

#define FALSE 0

void printSQLError( void )

{

char	buffer[200];

sqlerror_message( &sqlca, buffer, sizeof(buffer) );

#ifdef _SQL_OS_WINDOWS

printf( "Error %ld -- %Fs\n", SQLCODE, buffer );

#else

printf( "Error %ld -- %s\n", SQLCODE, buffer );

#endif

}

char *dllpaths[] = { "c:\\dirname\\",

 NULL };

int main( void )

{

struct sqlda _fd_ *	sqlda1;

int	result;

char	string[200];

printf( "Initing DLL\n" );

result = db_init_dll( dllpaths );

switch( result ) {

case ESQLDLL_OK:

printf("OK\n");

break;

case ESQLDLL_DLL_NOT_FOUND:

printf("DLL NOT FOUND\n");

return( 10 );

case ESQLDLL_WRONG_VERSION:

printf("WRONG VERSION\n");

return( 10 );

}

if( !db_init( &sqlca ) ) {

printf("db_init failed.\n");

db_fini_dll();

return( 10 );

}

#ifdef _SQL_OS_WINNT

result = db_string_connect( &sqlca, "UID=dba;PWD=sql;DBF=d:\\asa7\\sample.db");

#elif defined( _SQL_OS_WINDOWS )

result = db_string_connect( &sqlca, "UID=dba;PWD=sql;DBF=c:\\wsql50\\sample.db");

if( ! result ) {

printf( "db_string_connect returned = %d\n", result );

printSQLError();

}

sqlda1 = alloc_descriptor( sqlcaptr, 20 );

EXEC SQL PREPARE :stat1 FROM

'select * from employee

WHERE empnum = 80921';

if( SQLCODE != 0 ) {

printSQLError();

}

EXEC SQL DECLARE curs CURSOR FOR :stat1;

if( SQLCODE != 0 ) {

printSQLError();

}

EXEC SQL OPEN curs;

if( SQLCODE != 0 ) {

printSQLError();

}

EXEC SQL DESCRIBE :stat1 INTO sqlda1;

if( SQLCODE != 0 ) {

printSQLError();

}

sqlda1->sqlvar[1].sqltype = 460;

fill_sqlda( sqlda1 );

EXEC SQL FETCH FIRST curs INTO DESCRIPTOR sqlda1;

if( SQLCODE != 0 ) {

printSQLError();

}

printf( "name = %Fs\n",

(char _fd_ *)sqlda1->sqlvar[1].sqldata );

x = sqlda1->sqld;

printf( "COUNT = %d\n", x );

free_filled_sqlda( sqlda1 );

db_string_disconnect( &sqlca, "" );

db_fini( &sqlca );

db_fini_dll();

return( 0 );

}


Building NetWare Loadable Modules
  ASA Programming Interfaces Guide

    CHAPTER 2.  The Embedded SQL Interface

      Application development using Embedded SQL

 Building NetWare Loadable Modules

You must use the Watcom C/C++ compiler, version 10.6 or 11.0, to compile Embedded SQL programs as NetWare Loadable Modules (NLM).

♦ To create and Embedded SQL NLM:

1	On a Windows-based machine, preprocess the ESQL file using the following command:

sqlpp -o NETWARE srcfile.sqc

This instruction creates a  file with .c extention.

2	Compile file.c using the Watcom compiler (10.6 or 11.0), using the /bt=netware option.

3	Link the resulting object file using the Watcom linker with the following options:

FORMAT NOVELL

MODULE dblib7

OPTION CASEEXACT

IMPORT @dblib7.imp

LIBRARY dblib7.lib

The files dblib7.imp and dblib7.lib are shipped with Adaptive Server Anywhere, in the nlm directory and the nlm\lib directories, respectively. The IMPORT and LIBRARY lines may require a full path.


Embedded SQL data types
  ASA Programming Interfaces Guide

    CHAPTER 2.  The Embedded SQL Interface

 Embedded SQL data types

To transfer information between a program and the database server, every piece of data must have a data type. The Embedded SQL data type constants are prefixed with DT_, and can be found in the sqldef.h header file. You can create a host variable of any one of the supported types. You can also use these types in a SQLDA structure for passing data to and from the database.

You can define variables of these data types using the DECL_ macros listed in sqlca.h. For example, a variable holding a BIGINT value could be declared with DECL_BIGINT.

The following data types are supported by the Embedded SQL programming interface:

•	DT_BIT    8-bit signed integer

•	DT_SMALLINT    16-bit, signed integer.

•	DT_UNSSMALLINT    16-bit unsigned integer

•	DT_TINYINT    8-bit signed integer

•	DT_BIGINT    64-bit signed integer

•	DT_INT    32-bit signed integer.

•	DT_UNSINT    16-bit unsigned integer

•	DT_FLOAT    4-byte floating point number.

•	DT_DOUBLE    8-byte floating point number.

•	DT_DECIMAL    Packed decimal number.

typedef struct DECIMAL {

char array[1];

 } DECIMAL;

•	DT_STRING    NULL-terminated blank-padded character string.

•	DT_DATE    NULL-terminated character string that is a valid date.

•	DT_TIME    NULL-terminated character string that is a valid time.

•	DT_TIMESTAMP    NULL-terminated character string that is a valid timestamp.

•	DT_FIXCHAR    Fixed-length blank padded character string.

•	DT_VARCHAR    Varying length character string with a two byte length field. When supplying information to the database server, you must set the length field. When fetching information from the database server, the server sets the length field (not padded).

typedef struct VARCHAR {

   unsigned short int len;

   char array[1];

} VARCHAR;

•	DT_LONGVARCHAR    Long varying length character data. This type is used only for describing data, and is not used for supplying or retrieving values. LONG VARCHAR data should be fetched in pieces using the GET DATA statement.

  For more information, see GET DATA statement, and Retrieving a value.

•	DT_BINARY    Varying length binary data with a two byte length field. When supplying information to the database server, you must set the length field. When fetching information from the database server, the server sets the length field.

typedef struct BINARY {

  unsigned short int len;

  char array[1];

} BINARY;

•	DT_LONGBINARY    Long varying length binary data. This type is used only for describing data, and is not used for supplying or retrieving values. LONG BINARY data should be fetched in pieces using the GET DATA statement.

  For more information, see GET DATA statement, and Retrieving a value.

•	DT_TIMESTAMP_STRUCT    SQLDATETIME structure with fields for each part of a timestamp.

typedef struct sqldatetime {

unsigned short year; /* e.g. 1992 */

unsigned char month; /* 0-11 */

unsigned char day_of_week; /* 0-6 0=Sunday */

unsigned short day_of_year; /* 0-365 */

unsigned char day; /* 1-31 */

unsigned char hour; /* 0-23 */

unsigned char minute; /* 0-59 */

unsigned char second; /* 0-59 */

unsigned long microsecond; /* 0-999999 */

} SQLDATETIME;

The SQLDATETIME structure can be used to retrieve fields of DATE, TIME, and TIMESTAMP type (or anything that can be converted to one of these). Often, applications have their own formats and date manipulation code. Fetching data in this structure makes it easier for a programmer to manipulate this data. Note that DATE, TIME, and TIMESTAMP fields can also be fetched and updated with any character type.

If you use a SQLDATETIME structure to enter a date, time, or timestamp into the database via, the day_of_year and day_of_week members are ignored.

  For more information, see the DATE_FORMAT, TIME_FORMAT, TIMESTAMP_FORMAT, and DATE_ORDER database options in Database Options.

•	DT_VARIABLE    NULL-terminated character string. The character string must be the name of a SQL variable whose value is used by the database server. This data type is used only for supplying data to the database server. It cannot be used when fetching data from the database server.

The structures are defined in the sqlca.h file. The VARCHAR, BINARY, and DECIMAL types contain a one-character array and are thus not useful for declaring host variables but they are useful for allocating variables dynamically or typecasting other variables.

DATE and TIME database types

There are no corresponding Embedded SQL interface data types for the various DATE and TIME database types. These database types are all fetched and updated using either the SQLDATETIME structure or character strings.

  For more information see GET DATA statement and SET statement.


Using host variables
  ASA Programming Interfaces Guide

    CHAPTER 2.  The Embedded SQL Interface

 Using host variables

Host variables are C variables that are identified to the SQL preprocessor. Host variables can be used to send values to the database server or receive values from the database server.

Host variables are quite easy to use, but they have some restrictions. Dynamic SQL is a more general way of passing information to and from the database server using a structure known as the SQL Descriptor Area (SQLDA). Dynamic SQL is discussed in Static and dynamic SQL.

 Declaring host variables

 C host variable types

 Host variable usage

 Indicator variables


Declaring host variables
  ASA Programming Interfaces Guide

    CHAPTER 2.  The Embedded SQL Interface

      Using host variables

 Declaring host variables

Host variables are defined by putting them into a declaration section. According to the IBM SAA and ANSI Embedded SQL standards, host variables are defined by surrounding the normal C variable declarations with the following:

EXEC SQL BEGIN DECLARE SECTION;

/* C variable declarations */

EXEC SQL END DECLARE SECTION;

These host variables can then be used in place of value constants in any SQL statement. When the database server executes the command, the value of the host variable is used. Note that host variables cannot be used in place of table or column names; dynamic SQL is required for this. The variable name is prefixed with a colon (:) in a SQL statement to distinguish it from other identifiers allowed in the statement.

A standard SQL preprocessor does not scan C language code except inside a DECLARE SECTION. Thus, TYPEDEF types and structures are not allowed. Initializers on the variables are allowed inside a DECLARE SECTION.

Example

•	The following sample code illustrates the use of host variables on an INSERT command. The variables are filled in by the program and then inserted into the database:

EXEC SQL BEGIN DECLARE SECTION;

long employee_number;

char employee_name[50];

char employee_initials[8];

char employee_phone[15];

EXEC SQL END DECLARE SECTION;

/* program fills in variables with appropriate values

*/

EXEC SQL INSERT INTO Employee

VALUES (:employee_number, :employee_name,

:employee_initials, :employee_phone );

  For a more extensive example, see Static cursor example.


C host variable types
  ASA Programming Interfaces Guide

    CHAPTER 2.  The Embedded SQL Interface

      Using host variables

 C host variable types

Only a limited number of C data types are supported as host variables. Also, certain host variable types do not have a corresponding C type.

There are macros defined in the sqlca.h header file that can be used to declare a host variable of these types: VARCHAR, FIXCHAR, BINARY, PACKED DECIMAL, or SQLDATETIME structure. They are used as follows:

EXEC SQL BEGIN DECLARE SECTION;

DECL_VARCHAR( 10 ) v_varchar;

DECL_FIXCHAR( 10 ) v_fixchar;

DECL_BINARY( 4000 ) v_binary;

DECL_DECIMAL( 10, 2 ) v_packed_decimal;

DECL_DATETIME v_datetime;

EXEC SQL END DECLARE SECTION;

The preprocessor recognizes these macros within a declaration section and will treat the variable as the appropriate type.

The following table lists the C variable types that are allowed for host variables, and their corresponding Embedded SQL interface data types.

C Data Type	Embedded SQL Interface Type	Description
short i; short int i; unsigned short int i;	DT_SMALLINT	16-bit signed integer
long l; long int l; unsigned long int l;	DT_INT	32-bit signed integer
float f;	DT_FLOAT	4-byte floating point
double d;	DT_DOUBLE	8-byte floating point
DECL_DECIMAL(p,s)	DT_DECIMAL(p,s)	Packed decimal
char a; /*n=1*/ DECL_FIXCHAR(n) a; DECL_FIXCHAR a[n];	DT_FIXCHAR(n)	Fixed length character string blank padded
char a[n]; /*n>=1*/	DT_STRING(n)	NULL-terminated blank-padded string
char *a;	DT_STRING(32767)	NULL-terminated string
DECL_VARCHAR(n) a;	DT_VARCHAR(n)	Varying length character string with 2-byte length field. Not blank padded.
DECL_BINARY(n) a;	DT_BINARY(n)	Varying length binary data with 2-byte length field
DECL_DATETIME a;	DT_TIMESTAMP_STRUCT	SQLDATETIME structure
Pointers to char

A host variable declared as a pointer to char (char *a) is considered by the database interface to be 32 767 bytes long. Any host variable of type pointer to char used to retrieve information from the database must point to a buffer large enough to hold any value that could possibly come back from the database.

This is potentially quite dangerous, because somebody could change the definition of the column in the database to be larger than it was when the program was written. This could cause random memory corruption problems. If you are using a 16-bit compiler, requiring 32 767 bytes could make the program stack overflow. It is better to use a declared array, even as a parameter to a function where it is passed as a pointer to char. This lets the PREPARE statements know the size of the array.

Scope of host variables

A standard host-variable declaration section can appear anywhere that C variables can normally be declared. This includes the parameter declaration section of a C function. The C variables have their normal scope (available within the block in which they are defined). However, since the SQL preprocessor does not scan C code, it does not respect C blocks.

As far as the SQL preprocessor is concerned, host variables are global; two host variables cannot have the same name


Host variable usage
  ASA Programming Interfaces Guide

    CHAPTER 2.  The Embedded SQL Interface

      Using host variables

 Host variable usage

Host variables can be used in the following circumstances:

•	SELECT, INSERT, UPDATE and DELETE statements in any place where a number or string constant is allowed.

•	The INTO clause of SELECT and FETCH statements.

•	Host variables can also be used in place of a statement name, a cursor name, or an option name in commands specific to Embedded SQL.

•	For CONNECT, DISCONNECT, and SET CONNECT, a host variable can be used in place of a user ID, password, connection name, connection string, or database environment name.

•	For SET OPTION and GET OPTION, a host variable can be used in place of a user ID, option name or option value.

•	Host variables cannot be used in place of a table name or a column name in any statement.

Examples

•	The following is valid Embedded SQL:

INCLUDE SQLCA;

long SQLCODE;

sub1() {

char SQLSTATE[6];

exec sql CREATE TABLE ...

}

•	The following is not valid Embedded SQL:

INCLUDE SQLCA;

sub1() {

char SQLSTATE[6];

exec sql CREATE TABLE...

}

sub2() {

exec sql DROP TABLE...

// No SQLSTATE in scope of this statement

}

•	The case of SQLSTATE and SQLCODE is important, and the ISO/ANSI standard requires that their definitions be exactly as follows:

long SQLCODE;

char SQLSTATE[6];


Indicator variables
  ASA Programming Interfaces Guide

    CHAPTER 2.  The Embedded SQL Interface

      Using host variables

 Indicator variables

Indicator variables are C variables that hold supplementary information when you are fetching or putting data. There are several distinct uses for indicator variables:

•	NULL values    To enable applications to handle NULL values.

•	String truncation    To enable applications to handle cases when fetched values must be truncated to fit into host variables.

•	Conversion errors    To hold error information.

An indicator variable is a host variable of type short int that is placed immediately following a regular host variable in a SQL statement. For example, in the following INSERT statement, :ind_phone is an indicator variable:

EXEC SQL INSERT INTO Employee

VALUES (:employee_number, :employee_name,

:employee_initials, :employee_phone:ind_phone );

 Using indicator variables to handle NULL

 Using indicator variables for truncated values

 Using indicator values for conversion errors

 Summary of indicator variable values


Using indicator variables to handle NULL
  ASA Programming Interfaces Guide

    CHAPTER 2.  The Embedded SQL Interface

      Using host variables

        Indicator variables

 Using indicator variables to handle NULL

In SQL data, NULL represents either an unknown attribute or inapplicable information. The SQL concept of NULL is not to be confused with the C language constant by the same name (NULL). The C constant is used to represent a non-initialized or invalid pointer.

When NULL is used in the Adaptive Server Anywhere documentation, it refers to the SQL database meaning given above. The C language constant is referred to as the null pointer (lower case).

NULL is not the same as any value of the column's defined type. Thus, in order to pass NULL values to the database or receive NULL results back, something extra is required beyond regular host variables. Indicator variables are used for this purpose.

Using indicator variables when inserting NULL

An INSERT statement could include an indicator variable as follows:

EXEC SQL BEGIN DECLARE SECTION;

short int employee_number;

char employee_name[50];

char employee_initials[6];

char employee_phone[15];

short int ind_phone;

EXEC SQL END DECLARE SECTION;

/*

program fills in empnum, empname,

initials and homephone

*/

if( /* phone number is unknown */ ) {

ind_phone = -1;

} else {

ind_phone = 0;

}

EXEC SQL INSERT INTO Employee

VALUES (:employee_number, :employee_name,

:employee_initials, :employee_phone:ind_phone );

If the indicator variable has a value of -1, a NULL is written. If it has a value of 0, the actual value of employee_phone is written.

Using indicator variables when fetching NULL

Indicator variables are also used when receiving data from the database. They are used to indicate that a NULL value was fetched (indicator is negative). If a NULL value is fetched from the database and an indicator variable is not supplied, an error is generated (SQLE_NO_INDICATOR). Errors are explained in the next section.


Using indicator variables for truncated values
  ASA Programming Interfaces Guide

    CHAPTER 2.  The Embedded SQL Interface

      Using host variables

        Indicator variables

 Using indicator variables for truncated values

Indicator variables indicate whether any fetched values were truncated to fit into a host variable. This enables applications to handle truncation appropriately.

If a value is truncated on fetching, the indicator variable is set to a positive value, containing the actual length of the database value before truncation. If the length of the value is greater than 32 767, then the indicator variable contains 32 767.


Using indicator values for conversion errors
  ASA Programming Interfaces Guide

    CHAPTER 2.  The Embedded SQL Interface

      Using host variables

        Indicator variables

 Using indicator values for conversion errors

By default, the CONVERSION_ERROR database option is set to ON, and any data type conversion failure leads to an error, with no row returned.

You can use indicator variables to tell which column produced a data type conversion failure. If you set the database option CONVERSION_ERROR to OFF, any data type conversion failure gives a CANNOT_CONVERT warning, rather than an error. If the column that suffered the conversion error has an indicator variable, that variable is set to a value of -2.

If you set the CONVERSION_ERROR option to OFF when inserting data into the database, a value of NULL is inserted when a conversion failure occurs.


Summary of indicator variable values
  ASA Programming Interfaces Guide

    CHAPTER 2.  The Embedded SQL Interface

      Using host variables

        Indicator variables

 Summary of indicator variable values

The following table provides a summary of indicator variable usage.

Indicator Value	Supplying Value to database	Receiving value from database
> 0	Host variable value	Retrieved value was truncated — actual length in indicator variable
0	Host variable value	Fetch successful, or CONVERSION_ERROR set to ON.
-1	NULL value	NULL result
-2	NULL value	Conversion error (when CONVERSION_ERROR is set to OFF only). SQLCODE will indicate a CANNOT_CONVERT warning.
< -2	NULL value	NULL result
  For more information on retrieving long values, see GET DATA statement.


The SQL Communication Area
  ASA Programming Interfaces Guide

    CHAPTER 2.  The Embedded SQL Interface

 The SQL Communication Area

The SQL Communication Area (SQLCA) is an area of memory that is used on every database request for communicating statistics and errors from the application to the database server and back to the application. The SQLCA is used as a handle for the application-to-database communication link. It is passed in to all database library functions that need to communicate with the database server. It is implicitly passed on all Embedded SQL statements.

A global SQLCA variable is defined in the interface library. The preprocessor generates an external reference for the global SQLCA variable and an external reference for a pointer to it. The external reference is named sqlca and is of type SQLCA. The pointer is named sqlcaptr. The actual global variable is declared in the imports library.

The SQLCA is defined by the sqlca.h header file, included in the h subdirectory of your installation directory.

SQLCA provides error codes

You reference the SQLCA to test for a particular error code. The sqlcode and sqlstate fields contain error codes when a database request has an error (see below). Some C macros are defined for referencing the sqlcode field, the sqlstate field, and some other fields.

 Fields in the SQLCA

 SQLCA management for multi-threaded or reentrant code

 Using multiple SQLCAs


Fields in the SQLCA
  ASA Programming Interfaces Guide

    CHAPTER 2.  The Embedded SQL Interface

      The SQL Communication Area

 Fields in the SQLCA

The fields in the SQLCA have the following meanings:

•	sqlcaid    An 8-byte character field that contains the string SQLCA as an identification of the SQLCA structure. This field helps in debugging when you are looking at memory contents.

•	sqlcabc    A long integer that contains the length of the SQLCA structure (136 bytes).

•	sqlcode    A long integer that specifies the error code when the database detects an error on a request. Definitions for the error codes can be found in the header file sqlerr.h. The error code is 0 (zero) for a successful operation, positive for a warning and negative for an error.

  For a full listing of error codes, see Database Error Messages.

•	sqlerrml    The length of the information in the sqlerrmc field.

•	sqlerrmc    May contain one or more character strings to be inserted into an error message. Some error messages contain one or more placeholder strings (%1, %2, ...) which are replaced with the strings in this field.

For example, if a Table Not Found error is generated, sqlerrmc contains the table name, which is inserted into the error message at the appropriate place.

  For a full listing of error messages, see Database Error Messages.

•	sqlerrp    Reserved.

•	sqlerrd    A utility array of long integers.

•	sqlwarn    Reserved.

•	sqlstate    The SQLSTATE status value. The ANSI SQL standard (SQL-92) defines a new type of return value from a SQL statement in addition to the SQLCODE value in previous standards. The SQLSTATE value is always a five-character null-terminated string, divided into a two character class (the first two characters) and a three-character subclass. Each character can be a digit from 0 through 9 or an upper case alphabetic character A through Z.

Any class or subclass that begins with 0 through 4 or A through H is defined by the SQL standard; other classes and subclasses are implementation defined. The SQLSTATE value '00000' means that there has been no error or warning.

  Other SQLSTATE values are described in Database Error Messages.

sqlerror array

The sqlerror field array has the following elements.

•	sqlerrd[1] (SQLIOCOUNT)    The actual number of input/output operations that were required to complete a command.

The database does not start this number at zero for each command. Your program can set this variable to zero before executing a sequence of commands. After the last command, this number is the total number of input/output operations for the entire command sequence.

•	sqlerrd[2] (SQLCOUNT)    The value of this field depends on which  statement is being executed.

•	INSERT, UPDATE, PUT, and DELETE statements    The number of rows that were affected by the statement.

On a cursor OPEN, this field is filled in with either the actual number of rows in the cursor (a value greater than or equal to 0) or an estimate thereof (a negative number whose absolute value is the estimate). It is the actual number of rows if the database server can compute it without counting the rows. The database can also be configured to always return the actual number of rows using the ROW_COUNT option.

•	FETCH cursor statement    The SQLCOUNT field is filled if a SQLE_NOTFOUND warning is returned. It contains the number of rows by which a FETCH RELATIVE or FETCH ABSOLUTE statement goes outside the range of possible cursor positions (a cursor can be on a row, before the first row or after the last row). In the case of a wide fetch, SQLCOUNT is the number of rows actually fetched, and will be less than or equal to the number of rows requested. During a wide fetch, SQLE_NOTFOUND is not set.

  For more information on wide fetches, see Fetching more than one row at a time.

The value is 0 if the row was not found but the position is valid, for example, executing FETCH RELATIVE 1 when positioned on the last row of a cursor. The value is positive if the attempted fetch was beyond the end of the cursor, and negative if the attempted fetch was before the beginning of the cursor.

•	GET DATA statement    The SQLCOUNT field holds the actual length of the value.

•	DESCRIBE statement    In the WITH VARIABLE RESULT clause used to describe procedures that may have more than one result set, SQLCOUNT is set to one of the following values:

•	0    The result set may change: the procedure call should be described again following each OPEN statement.

•	1    The result set is fixed. No redescribing is required.

In the case of a syntax error, SQLE_SYNTAX_ERROR, this field contains the approximate character position within the command string where the error was detected.

•	sqlerrd[3] (SQLIOESTIMATE)    The estimated number of input/output operations that are to complete the command. This field is given a value on an OPEN or EXPLAIN command.


SQLCA management for multi-threaded or reentrant code
  ASA Programming Interfaces Guide

    CHAPTER 2.  The Embedded SQL Interface

      The SQL Communication Area

 SQLCA management for multi-threaded or reentrant code

You can use Embedded SQL statements in multi-threaded or reentrant code. However, if you use a single connection, you are restricted to one active request per connection. In a multi-threaded application, you should not use the same connection to the database on each thread unless you use a semaphore to control access.

There are no restrictions on using separate connections on each thread that wishes to use the database. The SQLCA is used by the runtime library to distinguish between the different thread contexts. Thus, each thread wishing to use the database must have its own SQLCA.

Any given database connection will only be accessible from one SQLCA, with the exception of the cancel instruction, which must be issued from a separate thread.

  For information on canceling requests, see Request management functions.


Using multiple SQLCAs
  ASA Programming Interfaces Guide

    CHAPTER 2.  The Embedded SQL Interface

      The SQL Communication Area

 Using multiple SQLCAs

♦ To manage multiple SQLCAs in your application:

1	You must use the command line switch on the SQL preprocessor that generates reentrant code (-r). The reentrant code is a little larger and a little slower because statically initialized global variables cannot be used. However, these effects are minimal.

2	Each SQLCA used in your program must be initialized with a call to db_init and cleaned up at the end with a call to db_fini.

Caution
Failure to call db_fini for each db_init on NetWare can cause the database server to fail, and the NetWare file server to fail.

3	The Embedded SQL statement SET SQLCA (SET SQLCA statement) is used to tell the SQL preprocessor to use a different SQLCA for database requests. Usually, a statement such as: EXEC SQL SET SQLCA 'task_data->sqlca'; is used at the top of your program or in a header file to set the SQLCA reference to point at task specific data. This statement does not generate any code and thus has no performance impact. It changes the state within the preprocessor so that any reference to the SQLCA will use the given string.

  For information about creating SQLCAs, see SET SQLCA statement.

 When to use multiple SQLCAs

 Connection management with multiple SQLCAs


When to use multiple SQLCAs
  ASA Programming Interfaces Guide

    CHAPTER 2.  The Embedded SQL Interface

      The SQL Communication Area

        Using multiple SQLCAs

 When to use multiple SQLCAs

You can use the multiple SQLCA support in any of the supported Embedded SQL environments, but it is only required in reentrant code.

The following list details the environments where multiple SQLCAs must be used:

•	Multi-threaded applications     If more than one thread uses the same SQLCA, a context switch can cause more than one thread to be using the SQLCA at the same time. Each thread must have its own SQLCA. This can also happen when you have a DLL that uses Embedded SQL and is called by more than one thread in your application.

•	Dynamic link libraries and shared libraries    A DLL has only one data segment. While the database server is processing a request from one application, it may yield to another application that makes a request to the database server. If your DLL uses the global SQLCA, both applications are using it at the same time. Each Windows application must have its own SQLCA.

•	A DLL with one data segment    A DLL can be created with only one data segment or one data segment for each application. If your DLL has only one data segment, you cannot use the global SQLCA for the same reason, that a DLL cannot use the global SQLCA. Each application must have its own SQLCA.


Connection management with multiple SQLCAs
  ASA Programming Interfaces Guide

    CHAPTER 2.  The Embedded SQL Interface

      The SQL Communication Area

        Using multiple SQLCAs

 Connection management with multiple SQLCAs

You do not need to use multiple SQLCAs to connect to more than one database or have more than one connection to a single database.

Each SQLCA can have one unnamed connection. Each SQLCA has an active or current connection (see SET CONNECTION statement). All operations on a given database connection must use the same SQLCA that was used when the connection was established.

Record locking
Operations on different connections are subject to the normal record locking mechanisms and may cause each other to block and possibly to deadlock. For information on locking, see the chapter Using Transactions and Isolation Levels.


Fetching data
  ASA Programming Interfaces Guide

    CHAPTER 2.  The Embedded SQL Interface

 Fetching data

Fetching data in Embedded SQL is done using the SELECT statement. There are two cases:

•	The SELECT statement returns at most one row.

•	The SELECT statement may return multiple rows.

 Embedded SELECT

 Cursors in Embedded SQL

 Fetching more than one row at a time


Embedded SELECT
  ASA Programming Interfaces Guide

    CHAPTER 2.  The Embedded SQL Interface

      Fetching data

 Embedded SELECT

A single row query retrieves at most one row from the database. A single-row query SELECT statement has an INTO clause following the select list and before the FROM clause. The INTO clause contains a list of host variables to receive the value for each select list item. There must be the same number of host variables as there are select list items. The host variables may be accompanied by indicator variables to indicate NULL results.

When the SELECT statement is executed, the database server retrieves the results and places them in the host variables. If the query results contain more than one row, the database server returns an error.

If the query results in no rows being selected, a Row Not Found warning is returned. Errors and warnings are returned in the SQLCA structure, as described in The SQL Communication Area.

Example

For example, the following code fragment returns 1 if a row from the employee table is successfully fetched, 0 if the row doesn't exist and -1 if an error occurs.

EXEC SQL BEGIN DECLARE SECTION;

long	emp_id;

char	name[41];

char	sex;

char	birthdate[15];

short int	ind_birthdate;

EXEC SQL END DECLARE SECTION;

. . .

int find_employee( long employee )

{

emp_id = employee;

EXEC SQL	SELECT emp_fname ||

' ' || emp_lname, sex, birth_date

INTO :name, :sex,

:birthdate:ind_birthdate

FROM "dba".employee

WHERE emp_id = :emp_id;

if( SQLCODE == SQLE_NOTFOUND ) {

return( 0 ); /* employee not found */

} else if( SQLCODE < 0 ) {

return( -1 ); /* error */

} else {

return( 1 ); /* found */

}

}


Cursors in Embedded SQL
  ASA Programming Interfaces Guide

    CHAPTER 2.  The Embedded SQL Interface

      Fetching data

 Cursors in Embedded SQL

A cursor is used to retrieve rows from a query that has multiple rows in its result set. A cursor is a handle or an identifier for the SQL query and a position within the result set.

  For an introduction to cursors, see Working with cursors.

♦ To manage a cursor in Embedded SQL:

1	Declare a cursor for a particular SELECT statement, using the DECLARE statement.

2	Open the cursor using the OPEN statement.

3	Retrieve results one row at a time from the cursor using the FETCH statement.

4	Fetch rows until the Row Not Found warning is returned.

Errors and warnings are returned in the SQLCA structure, described in The SQL Communication Area.

5	Close the cursor, using the CLOSE statement.

By default, cursors are automatically closed at the end of a transaction (on COMMIT or ROLLBACK). Cursors that are opened with a WITH HOLD clause are kept open for subsequent transactions until they are explicitly closed.

The following is a simple example of cursor usage:

void print_employees( void )

{

EXEC SQL BEGIN DECLARE SECTION;

char name[50];

char sex;

char birthdate[15];

short int ind_birthdate;

EXEC SQL END DECLARE SECTION;

EXEC SQL DECLARE C1 CURSOR FOR

SELECT	emp_fname || ' ' || emp_lname,

sex, birth_date

FROM "dba".employee;

EXEC SQL OPEN C1;

for( ;; ) {

EXEC SQL FETCH C1 INTO :name, :sex, :birthdate:ind_birthdate;

if( SQLCODE == SQLE_NOTFOUND ) {

break;

} else if( SQLCODE < 0 ) {

break;

}

if( ind_birthdate < 0 ) {

strcpy( birthdate, "UNKNOWN" );

}

printf( "Name: %s Sex: %c Birthdate:

%s.n",name, sex, birthdate );

}

EXEC SQL CLOSE C1;

}

  For complete examples using cursors, see Static cursor example, and Dynamic cursor example.

Cursor positioning

A cursor is positioned in one of three places:

•	On a row

•	Before the first row

•	After the last row

When a cursor is opened, it is positioned before the first row. The cursor position can be moved, using the FETCH command (see FETCH statement ). It can be positioned to an absolute position either from the start or from the end of the query results. It can also be moved relative to the current cursor position.

There are special positioned versions of the UPDATE and DELETE statements that can be used to update or delete the row at the current position of the cursor. If the cursor is positioned before the first row or after the last row, a No Current Row of Cursor error is returned.

The PUT statement can be used to insert a row into a cursor.

Cursor positioning problems

Inserts and some updates to DYNAMIC SCROLL cursors can cause problems with cursor positioning. The database server does not put inserted rows at a predictable position within a cursor unless there is an ORDER BY clause on the SELECT statement. In some cases, the inserted row does not appear at all until the cursor is closed and opened again.

With Adaptive Server Anywhere, this occurs if a temporary table had to be created to open the cursor (see Temporary tables used in query processing for a description).

The UPDATE statement may cause a row to move in the cursor. This happens if the cursor has an ORDER BY clause that uses an existing index (a temporary table is not created).


Fetching more than one row at a time
  ASA Programming Interfaces Guide

    CHAPTER 2.  The Embedded SQL Interface

      Fetching data

 Fetching more than one row at a time

The FETCH statement can be modified to fetch more than one row at a time, which may improve performance. This is called a wide fetch or an array fetch.

  Adaptive Server Anywhere also supports wide puts and inserts. For information on these, see PUT statement and EXECUTE statement.

To use wide fetches in Embedded SQL, include the fetch statement in your code as follows:

EXEC SQL FETCH . . . ARRAY nnn

where ARRAY nnn is the last item of the FETCH statement. The fetch count nnn can be a host variable. The number of variables in the SQLDA must be the product of nnn  and the number of columns per row. The first row is placed in SQLDA variables 0 to (columns per row) - 1, and so on.

Each column must be of the same type in each row of the SQLDA, or a SQLDA_INCONSISTENT error is returned.

The server returns in SQLCOUNT the number of records that were fetched, which is always greater than zero unless there is an error or warning. On a wide fetch, a SQLCOUNT of one with no error condition indicates that one valid row has been fetched.

Example

The following example code illustrates the use of wide fetches. The example code is not compilable as it stands.

#include <stdio.h>

#include <stdlib.h>

#include <string.h>

#include "sqldef.h"

EXEC SQL INCLUDE SQLCA;

EXEC SQL WHENEVER SQLERROR { PrintSQLError();

     goto err; };

static void PrintSQLError()

/*************************/

{

    char buffer[200];

    printf( "SQL error %d -- %s\n",

    SQLCODE,

    sqlerror_message( &sqlca,

      buffer,

      sizeof( buffer ) ) );

}

static SQLDA * PrepareSQLDA(

a_sql_statement_number	stat0,

unsigned	width,

unsigned	*cols_per_row )

/*********************************************/

/* Allocate a SQLDA to be used for fetching from

   the statement identified by "stat0". "width"

   rows will be retrieved on each FETCH request.

   The number of columns per row is assigned to

   "cols_per_row". */

{

    int                     num_cols;

    unsigned                row, col, offset;

    SQLDA *                 sqlda;

    EXEC SQL BEGIN DECLARE SECTION;

    a_sql_statement_number  stat;

    EXEC SQL END DECLARE SECTION;

    stat = stat0;

    sqlda = alloc_sqlda( 100 );

    if( sqlda == NULL ) return( NULL );

    EXEC SQL DESCRIBE :stat INTO sqlda;

    *cols_per_row = num_cols = sqlda->sqld;

    if( num_cols * width > sqlda->sqln ) {

        free_sqlda( sqlda );

        sqlda = alloc_sqlda( num_cols * width );

        if( sqlda == NULL ) return( NULL );

        EXEC SQL DESCRIBE :stat INTO sqlda;

    }

    // copy first row in SQLDA setup by describe

    // to following (wide) rows

    sqlda->sqld = num_cols * width;

    offset = num_cols;

    for( row = 1; row < width; row++ ) {

        for( col = 0;

     col < num_cols;

             col++, offset++ ) {

            sqlda->sqlvar[offset].sqltype =

sqlda->sqlvar[col].sqltype;

            sqlda->sqlvar[offset].sqllen =

sqlda->sqlvar[col].sqllen;

    // optional: copy described column name

            memcpy( &sqlda->sqlvar[offset].sqlname,

                    &sqlda->sqlvar[col].sqlname,

                    sizeof( sqlda->sqlvar[0].sqlname )

);

        }

    }

    fill_s_sqlda( sqlda, 40 );

    return( sqlda );

err:

    return( NULL );

}

static void PrintFetchedRows( SQLDA * sqlda,

      unsigned cols_per_row )

/******************************************/

/* Print rows already wide fetched in the SQLDA */

{

    long                    rows_fetched;

    int	    row, col, offset;

    if( SQLCOUNT == 0 ) {

rows_fetched = 1;

    } else {

rows_fetched = SQLCOUNT;

    }

    printf( "Fetched %d Rows:\n", rows_fetched );

    for( row = 0; row < rows_fetched; row++ ) {

for( col = 0; col < cols_per_row; col++ ) {

    offset = row * cols_per_row + col;

    printf( " \"%s\"",

    (char *)sqlda->sqlvar[offset]

      .sqldata );

}

printf( "\n" );

    }

}

static int DoQuery( char * query_str0,

            unsigned fetch_width0 )

/*****************************************/

/* Wide Fetch "query_str0" select statement

 * using a width of "fetch_width0" rows" */

{

    SQLDA *                 sqlda;

    unsigned	    cols_per_row;

    EXEC SQL BEGIN DECLARE SECTION;

    a_sql_statement_number  stat;

    char *	    query_str;

    unsigned	    fetch_width;

    EXEC SQL END DECLARE SECTION;

    query_str = query_str0;

    fetch_width = fetch_width0;

    EXEC SQL PREPARE :stat FROM :query_str;

    EXEC SQL DECLARE QCURSOR CURSOR FOR :stat

FOR READ ONLY;

    EXEC SQL OPEN QCURSOR;

    sqlda = PrepareSQLDA( stat,

  fetch_width,

  &cols_per_row );

    if( sqlda == NULL ) {

        printf( "Error allocating SQLDA\n" );

        return( SQLE_NO_MEMORY );

    }

    for( ;; ) {

        EXEC SQL FETCH QCURSOR INTO DESCRIPTOR sqlda

    ARRAY :fetch_width;

        if( SQLCODE != SQLE_NOERROR ) break;

PrintFetchedRows( sqlda, cols_per_row );

    }

    EXEC SQL CLOSE QCURSOR;

    EXEC SQL DROP STATEMENT :stat;

    free_filled_sqlda( sqlda );

err:

    return( SQLCODE );

}

void main( int argc, char *argv[] )

/*********************************/

/* Optional first argument is a select statement,

 * optional second argument is the fetch width */

{

    char *query_str =

"select emp_fname, emp_lname from employee";

    unsigned fetch_width = 10;

    if( argc > 1 ) {

query_str = argv[1];

if( argc > 2 ) {

    fetch_width = atoi( argv[2] );

    if( fetch_width < 2 ) {

fetch_width = 2;

    }

}

    }

    db_init( &sqlca );

    EXEC SQL CONNECT "dba" IDENTIFIED BY "sql";

    DoQuery( query_str, fetch_width );

    EXEC SQL DISCONNECT;

err:

    db_fini( &sqlca );

}

Notes on using wide fetches

•	In the function PrepareSQLDA, the SQLDA memory is allocated using the alloc_sqlda function. This allows space for indicator variables, rather than using the alloc_sqlda_noind function.

•	If the number of rows fetched is fewer than the number requested, but is not zero (at the end of the cursor for example), the SQLDA items corresponding to the rows that were not fetched are returned as NULL by setting the indicator value. If no indicator variables are present, an error is generated (SQLE_NO_INDICATOR: no indicator variable for NULL result).

•	If a row being fetched has been updated, generating a SQLE_ROW_UPDATED_WARNING warning, the fetch stops on the row that caused the warning. The values for all rows processed to that point (including the row that caused the warning) are returned. SQLCOUNT contains the number of rows that were fetched, including the row that caused the warning. All remaining SQLDA items are marked as NULL.

•	If a row being fetched has been deleted or is locked, generating an SQLE_NO_CURRENT_ROW or SQLE_LOCKED error, SQLCOUNT contains the number of rows that were read prior to the error. This does not include the row that caused the error. The SQLDA does not contain values for any of the rows, since SQLDA values are not returned on errors. The SQLCOUNT value can be used to reposition the cursor, if necessary, to read the rows.


Static and dynamic SQL
  ASA Programming Interfaces Guide

    CHAPTER 2.  The Embedded SQL Interface

 Static and dynamic SQL

There are two ways to embed SQL statements into a C program:

•	Static statements

•	Dynamic statements

Until now, we have been discussing static SQL. This section compares static and dynamic SQL.

 Static SQL statements

 Dynamic SQL statements

 Dynamic SELECT statement


Static SQL statements
  ASA Programming Interfaces Guide

    CHAPTER 2.  The Embedded SQL Interface

      Static and dynamic SQL

 Static SQL statements

All standard SQL data manipulation and data definition statements can be embedded in a C program by prefixing them with EXEC SQL and suffixing the command with a semicolon (;). These statements are referred to as static statements.

Static statements can contain references to host variables, as described in Using host variables. All examples to this point have used static Embedded SQL statements.

Host variables can only be used in place of string or numeric constants. They cannot be used to substitute column names or table names; dynamic statements are required to do those operations.


Dynamic SQL statements
  ASA Programming Interfaces Guide

    CHAPTER 2.  The Embedded SQL Interface

      Static and dynamic SQL

 Dynamic SQL statements

In the C language, strings are stored in arrays of characters. Dynamic statements are constructed in C language strings. These statements can then be executed using the PREPARE and EXECUTE statements. These SQL statements cannot reference host variables in the same manner as static statements since the C language variables are not accessible by name when the C program is executing.

To pass information between the statements and the C language variables, a data structure called the SQL Descriptor Area (SQLDA) is used. This structure is set up for you by the SQL preprocessor if you specify a list of host variables on the EXECUTE command in the USING clause. These variables correspond by position to place holders in the appropriate positions of the prepared command string.

  For information on the SQLDA, see The SQL descriptor area (SQLDA).

A place holder is put in the statement to indicate where host variables are to be accessed. A place holder is either a question mark (?) or a host variable reference as in static statements (a host variable name preceded by a colon). In the latter case, the host variable name used in the actual text of the statement serves only as a place holder indicating a reference to the SQL descriptor area.

A host variable used to pass information to the database is called a bind variable.

Example

For example:

EXEC SQL BEGIN DECLARE SECTION;

char comm[200];

char address[30];

char city[20];

short int cityind;

long empnum;

EXEC SQL END DECLARE SECTION;

. . .

sprintf( comm, "update %s set address = :?,

city = :?"

" where employee_number = :?",

tablename );

EXEC SQL PREPARE S1 FROM :comm;

EXEC SQL EXECUTE S1 USING :address, :city:cityind, :empnum;

This method requires the programmer to know how many host variables there are in the statement. Usually, this is not the case. So, you can set up your own SQLDA structure and specify this SQLDA in the USING clause on the EXECUTE command.

The DESCRIBE BIND VARIABLES statement returns the host variable names of the bind variables that are found in a prepared statement. This makes it easier for a C program to manage the host variables. The general method is as follows:

EXEC SQL BEGIN DECLARE SECTION;

char comm[200];

EXEC SQL END DECLARE SECTION;

. . .

sprintf( comm, "update %s set address = :address,

 city = :city"

" where employee_number = :empnum",

tablename );

EXEC SQL PREPARE S1 FROM :comm;

/* Assume that there are no more than 10 host variables. See next example if you can't put

a limit on it */

sqlda = alloc_sqlda( 10 );

EXEC SQL DESCRIBE BIND VARIABLES FOR S1 USING DESCRIPTOR sqlda;

/* sqlda->sqld will tell you how many host variables there were. */

/* Fill in SQLDA_VARIABLE fields with values based on

name fields in sqlda */

. . .

EXEC SQL EXECUTE S1 USING DESCRIPTOR sqlda;

free_sqlda( sqlda );

SQLDA contents

The SQLDA consists of an array of variable descriptors. Each descriptor describes the attributes of the corresponding C program variable, or the location that the database stores data into or retrieves data from:

•	data type

•	length if type is a string type

•	precision and scale if type is a numeric type

•	memory address

•	indicator variable

  For a complete description of the SQLDA structure, see The SQL descriptor area (SQLDA)

Indicator variables and NULL

The indicator variable is used to pass a NULL value to the database or retrieve a NULL value from the database. The indicator variable is also used by the database server to indicate truncation conditions encountered during a database operation. The indicator variable is set to a positive value when not enough space was provided to receive a database value.

  For more information, see Indicator variables.


Dynamic SELECT statement
  ASA Programming Interfaces Guide

    CHAPTER 2.  The Embedded SQL Interface

      Static and dynamic SQL

 Dynamic SELECT statement

A SELECT statement that returns only a single row can be prepared dynamically, followed by an EXECUTE with an INTO clause to retrieve the one-row result. SELECT statements that return multiple rows, however, are managed using dynamic cursors.

With dynamic cursors, results are put into a host variable list or a SQLDA that is specified on the FETCH statement (FETCH INTO HOSTLIST and FETCH USING DESCRIPTOR SQLDA). Since the number of select list items is usually unknown to the C programmer, the SQLDA route is the most common. The DESCRIBE SELECT LIST statement sets up a SQLDA with the types of the select list items. Space is then allocated for the values using the fill_sqlda() function, and the information is retrieved by the FETCH USING DESCRIPTOR statement.

The typical scenario is as follows:

EXEC SQL BEGIN DECLARE SECTION;

char comm[200];

EXEC SQL END DECLARE SECTION;

int actual_size;

SQLDA * sqlda;

. . .

sprintf( comm, "select * from %s", table_name );

EXEC SQL PREPARE S1 FROM :comm;

/* Initial guess of 10 columns in result. If it is

wrong, it is corrected right after the first

DESCRIBE by reallocating sqlda and doing DESCRIBE 	again. */

sqlda = alloc_sqlda( 10 );

EXEC SQL DESCRIBE SELECT LIST FOR S1 USING DESCRIPTOR sqlda;

if( sqlda->sqld > sqlda->sqln ){

actual_size = sqlda->sqld;

free_sqlda( sqlda );

sqlda = alloc_sqlda( actual_size );

EXEC SQL DESCRIBE SELECT LIST FOR S1

USING DESCRIPTOR sqlda;

}

fill_sqlda( sqlda );

EXEC SQL DECLARE C1 CURSOR FOR S1;

EXEC SQL OPEN C1;

EXEC SQL WHENEVER NOTFOUND {break};

for( ;; ){

EXEC SQL FETCH C1 USING DESCRIPTOR sqlda;

/* do something with data */

}

EXEC SQL CLOSE C1;

EXEC SQL DROP STATEMENT S1;

Drop statements after use
To avoid consuming unnecessary resources, ensure that statements are dropped after use.

  For a complete example using cursors for a dynamic select statement, see Dynamic cursor example. For details of the functions mentioned above, see Library functions.


The SQL descriptor area (SQLDA)
  ASA Programming Interfaces Guide

    CHAPTER 2.  The Embedded SQL Interface

 The SQL descriptor area (SQLDA)

The SQLDA (SQL Descriptor Area) is an interface structure that is used for dynamic SQL statements. The structure passes information regarding host variables and SELECT statement results to and from the database. The SQLDA is defined in the header file sqlda.h.

  There are functions in the database interface library or DLL that you can use to manage SQLDAs. For descriptions, see SQLDA management functions.

When host variables are used with static SQL statements, the preprocessor constructs a SQLDA for those host variables. It is this SQLDA that is actually passed to and from the database server.

 The SQLDA header file

 SQLDA fields and their meanings

 Host variable descriptions in the SQLDA

 Length field values


The SQLDA header file
  ASA Programming Interfaces Guide

    CHAPTER 2.  The Embedded SQL Interface

      The SQL descriptor area (SQLDA)

 The SQLDA header file

The contents of sqlda.h are as follows:

#ifndef _SQLDA_H_INCLUDED

#define _SQLDA_H_INCLUDED

#define II_SQLDA

#include "sqlca.h"

#if defined( _SQL_PACK_STRUCTURES )

#include "pshpk1.h"

#endif

#define SQL_MAX_NAME_LEN  30

#define _sqldafar   _sqlfar

typedef	short int   a_sql_type;

struct sqlname {

    short int length; /* length of char data */

    char	data[ SQL_MAX_NAME_LEN ]; /* data */

};

struct sqlvar {    /* array of variable descriptors   */

    short int   sqltype; /* type of  host variable    */

    short int   sqllen; /* length of host variable    */

    void _sqldafar  *sqldata; /* address of variable  */

    short int _sqldafar *sqlind; /* indicator variable pointer */

    struct sqlname  sqlname;

};

struct sqlda{

    unsigned char  sqldaid[8]; /* eye catcher "SQLDA"*/

    a_sql_int32  sqldabc; /* length of sqlda structure*/

    short int  sqln; /* descriptor size in number of entries */

    short int  sqld; /* number of variables found by DESCRIBE*/

    struct sqlvar       sqlvar[1]; /* array of variable descriptors */

};

#define SCALE(sqllen)           ((sqllen)/256)

#define PRECISION(sqllen)       ((sqllen)&0xff)

#define SET_PRECISION_SCALE(sqllen,precision,scale)     \

                                sqllen = (scale)*256 + (precision)

#define DECIMALSTORAGE(sqllen)  (PRECISION(sqllen)/2 + 1)

typedef struct sqlda    SQLDA;

typedef struct sqlvar   SQLVAR, SQLDA_VARIABLE;

typedef struct sqlname  SQLNAME, SQLDA_NAME;

#ifndef SQLDASIZE

#define SQLDASIZE(n)    ( sizeof( struct sqlda ) +  \

                          (n-1) * sizeof( struct sqlvar) )

#endif

#if defined( _SQL_PACK_STRUCTURES )

#include "poppk.h"

#endif

#endif


SQLDA fields and their meanings
  ASA Programming Interfaces Guide

    CHAPTER 2.  The Embedded SQL Interface

      The SQL descriptor area (SQLDA)

 SQLDA fields and their meanings

The SQLDA fields have the following meanings:

Field	Description
sqldaid	An 8-byte character field that contains the string SQLDA as an identification of the SQLDA structure. This field helps in debugging, when you are looking at memory contents.
sqldabc	A long integer containing the length of the SQLDA structure.
sqln	The number of variable descriptors in the sqlvar array.
sqld	The number of variable descriptors which are valid (contain information describing a host variable). This field is set by the DESCRIBE statement, and sometimes by the programmer when supplying data to the database server.
sqlvar	An array of descriptors of type struct sqlvar, each describing a host variable.


Host variable descriptions in the SQLDA
  ASA Programming Interfaces Guide

    CHAPTER 2.  The Embedded SQL Interface

      The SQL descriptor area (SQLDA)

 Host variable descriptions in the SQLDA

Each sqlvar structure in the SQLDA describes a host variable. The fields of the sqlvar structure have the following meanings:

•	sqltype    The type of the variable that is described by this descriptor (see Embedded SQL data types).

The low order bit indicates whether NULL values are allowed. Valid types and constant definitions can be found in the sqldef.h header file.

This field is filled by the DESCRIBE statement. You can set this field to any type, when supplying data to the database server or retrieving data from the database server. Any necessary type conversion is done automatically.

•	sqllen    The length of the variable. What the length actually means depends upon the type information and how the SQLDA is being used.

For DECIMAL types, this field is divided into two 1-byte fields. The high byte is the precision and the low byte is the scale. The precision is the total number of digits. The scale is the number of digits that appear after the decimal point.

  For more information on the length field, see Length field values.

•	sqldata    A four-byte pointer to the memory occupied by this variable. This memory must correspond to the sqltype and sqllen fields.

  For storage formats, see Embedded SQL data types.

For UPDATE and INSERT commands, this variable will not be involved in the operation if the sqldata pointer is a null pointer. For a FETCH, no data is returned if the sqldata pointer is a null pointer. In other words, the column returned by the sqldata pointer is an unbound column.

If the DESCRIBE statement uses LONG NAMES, this field holds the long name of the result set column. If, in addition, the DESCRIBE statement is a DESCRIBE USER TYPES statement, then this field holds the long name of the user-defined data type, instead of the column. If the type is a base type, the field is empty.

•	sqlind    A pointer to the indicator value. An indicator value is a short int. A negative indicator value indicates a NULL value. A positive indicator value indicates that this variable has been truncated by a FETCH statement, and the indicator value contains the length of the data before truncation. A value of -2 indicates a conversion error, if the CONVERSION_ERROR database option is set to OFF.

  For more information, see Indicator variables.

If the sqlind pointer is the null pointer, no indicator variable pertains to this host variable.

The sqlind field is also used by the DESCRIBE statement to indicate parameter types. If the type is a user-defined data type, this field is set to DT_HAS_USERTYPE_INFO. In such a case, you may wish to carry out a DESCRIBE USER TYPES to obtain information on the user-defined data types.

•	sqlname    A VARCHAR structure that contains a length and character buffer. It is filled by a DESCRIBE statement and is not otherwise used. This field has a different meaning for the two formats of the DESCRIBE statement:

•	SELECT LIST    The name buffer is filled with the column heading of the corresponding item in the select list.

•	BIND VARIABLES    The name buffer is filled with the name of the host variable that was used as a bind variable, or "?" if an unnamed parameter marker is used.

On a DESCRIBE SELECT LIST command, any indicator variables present are filled with a flag indicating whether the select list item is updatable or not. More information on this flag can be found in the sqldef.h header file.

If the DESCRIBE statement is a DESCRIBE USER TYPES statement, then this field holds the long name of the user-defined data type, instead of the column. If the type is a base type, the field is empty.


Length field values
  ASA Programming Interfaces Guide

    CHAPTER 2.  The Embedded SQL Interface

      The SQL descriptor area (SQLDA)

 Length field values

The sqllen field length of the sqlvar structure in a SQLDA is used in three different kinds of interactions with the database server. The following tables detail each of these interactions. These tables list the interface constant types (the DT_ types) found in the sqldef.h header file. These constants would be placed in the SQLDA sqltype field. The types are described in Embedded SQL data types.

In static SQL, a SQLDA is still used but it is generated and completely filled in by the SQL preprocessor. In this static case, the tables give the correspondence between the static C language host variable types and the interface constants.

DESCRIBE

The following table indicates the values of the sqllen and sqltype structure members returned by the DESCRIBE command for the various database types (both SELECT LIST and BIND VARIABLE DESCRIBE statements). In the case of a user-defined database data type, the base type is described.

Your program can use the types and lengths returned from a DESCRIBE, or you may use another type. The database server will perform type conversions between any two types. The memory pointed to by the sqldata field must correspond to the sqltype and sqllen fields.

  For information on Embedded SQL data types, see Embedded SQL data types.

Database field type	Embedded SQL type returned	Length returned on describe
BIGINT	DT_BIGINT	8
BINARY(n)	DT_BINARY	n
BIT	DT_BIT	1
CHAR(n)	DT_FIXCHAR	n
DATE	DT_DATE	length of longest formatted string
DECIMAL(p,s)	DT_DECIMAL	high byte of length field in SQLDA set to p, and low byte set to s
DOUBLE	DT_DOUBLE	8
FLOAT	DT_FLOAT	4
INT	DT_INT	4
LONG BINARY	DT_LONGBINARY	32 767
LONG VARCHAR	DT_LONGVARCHAR	32 767
REAL	DT_FLOAT	4
SMALLINT	DT_SMALLINT	2
TIME	DT_TIME	length of longest formatted string
TIMESTAMP	DT_TIMESTAMP	length of longest formatted string
TINYINT	DT_TINYINT	1
UNSIGNED BIGINT	DT_UNSBIGINT	8
UNSIGNED INT	DT_UNSINT	4
UNSIGNED SMALLINT	DT_UNSSMALLINT	2
VARCHAR(n)	DT_VARCHAR	n
Supplying a value

The following table indicates how you specify lengths of values when you supply data to the database server in the SQLDA.

Only the data types shown in the table are allowed in this case. The DT_DATE, DT_TIME and DT_TIMESTAMP types are treated the same as DT_STRING when supplying information to the database; the value must be a NULL-terminated character string in an appropriate date format.

Embedded SQL Data Type	Program action to set the length
DT_BIGINT	No action required
DT_BINARY(n)	length taken from field in BINARY structure
DT_BIT	No action required
DT_DATE	length determined by terminating \0
DT_DECIMAL(p,s)	high byte of length field in SQLDA set to p, and low byte set to s
DT_DOUBLE	No action required
DT_FIXCHAR(n)	length field in SQLDA determines length of string
DT_FLOAT	No action required
DT_INT	No action required
DT_SMALLINT	No action required
DT_STRING	length determined by terminating \0
DT_TIME	length determined by terminating \0
DT_TIMESTAMP	length determined by terminating \0
DT_TIMESTAMP_STRUCT	No action required
DT_UNSBIGINT	No action required
DT_UNSINT	No action required
DT_UNSSMALLINT	No action required
DT_VARCHAR(n)	length taken from field in VARCHAR structure
DT_VARIABLE	length determined by terminating \0
Retrieving a value

The following table indicates the values of the length field when you retrieve data from the database using a SQLDA. The sqllen field is never modified when you retrieve data.

Only the interface data types shown in the table are allowed in this case. The DT_DATE, DT_TIME and DT_TIMESTAMP data types are treated the same as DT_STRING when you retrieve information from the database. The value is formatted as a character string in the current date format.

Embedded SQL Data Type	What the program must set length field to when receiving	How the database returns length information after fetching a value
DT_BIGINT	No action required	No action required
DT_BINARY(n)	maximum length of BINARY structure (n+2)	len field of BINARY structure set to actual length
DT_BIT	No action required	No action required
DT_DATE	length of buffer	\0 at end of string
DT_DECIMAL(p,s)	high byte set to p and low byte set to s	No action required
DT_DOUBLE	No action required	No action required
DT_FIXCHAR(n)	length of buffer	padded with blanks to length of buffer
DT_FLOAT	No action required	No action required
DT_INT	No action required	No action required
DT_SMALLINT	No action required	No action required
DT_STRING	length of buffer	\0 at end of string
DT_TIME	length of buffer	\0 at end of string
DT_TIMESTAMP	length of buffer	\0 at end of string
DT_TIMESTAMP_ STRUCT	No action required	No action required
DT_UNSBIGINT	No action required	No action required
DT_UNSINT	No action required	No action required
DT_UNSSMALLINT	No action required	No action required
DT_VARCHAR(n)	maximum length of VARCHAR structure (n+2)	len field of VARCHAR structure set to actual length


Using stored procedures in Embedded SQL
  ASA Programming Interfaces Guide

    CHAPTER 2.  The Embedded SQL Interface

 Using stored procedures in Embedded SQL

This section describes the use of SQL procedures in Embedded SQL.

 Simple procedures

 Procedures with result sets


Simple procedures
  ASA Programming Interfaces Guide

    CHAPTER 2.  The Embedded SQL Interface

      Using stored procedures in Embedded SQL

 Simple procedures

Database procedures can be both created and called from Embedded SQL. A CREATE PROCEDURE statement can be embedded just like any other data definition statement, such as CREATE TABLE. A CALL statement can also be embedded, or it can be prepared and executed. Here is a simple example of both creating and executing a stored procedure in Embedded SQL:

EXEC SQL CREATE PROCEDURE pettycash( IN amount DECIMAL(10,2) )

BEGIN

UPDATE account

SET balance = balance - amount

WHERE name = 'bank';

UPDATE account

SET balance = balance + amount

WHERE name = 'pettycash expense';

END;

EXEC SQL CALL pettycash( 10.72 );

If you wish to pass host variable values to a stored procedure, or retrieve the output variables, you prepare and execute a CALL statement. The example illustrates the use of host variables. Both the USING and INTO clauses are used on the EXECUTE statement.

EXEC SQL BEGIN DECLARE SECTION;

double	hv_expense;

double	hv_balance;

EXEC SQL END DECLARE SECTION;

EXEC SQL CREATE PROCEDURE pettycash(

IN expense 	DECIMAL(10,2),

OUT endbalance DECIMAL(10,2) )

BEGIN

UPDATE account

SET balance = balance - expense

WHERE name = 'bank';

UPDATE account

SET balance = balance + expense

WHERE name = 'pettycash expense';

SET endbalance = ( SELECT balance FROM account

 WHERE name = 'bank' );

END;

EXEC SQL PREPARE S1 FROM 'CALL pettycash( ?, ? )';

EXEC SQL EXECUTE S1 USING :hv_expense INTO :hv_balance;


Procedures with result sets
  ASA Programming Interfaces Guide

    CHAPTER 2.  The Embedded SQL Interface

      Using stored procedures in Embedded SQL

 Procedures with result sets

Database procedures can also contain SELECT statements. The procedure is declared using a RESULT clause to specify the number, name, and types of the columns in the result set. Result set columns are different from output parameters. For procedures with result sets, the CALL statement can be used in place of a SELECT statement in the cursor declaration:

EXEC SQL BEGIN DECLARE SECTION;

char	hv_name[100];

EXEC SQL END DECLARE SECTION;

EXEC SQL CREATE PROCEDURE female_employees()

RESULT( name char(50) )

BEGIN

SELECT emp_fname || emp_lname FROM employee

WHERE sex = 'f';

END;

EXEC SQL PREPARE S1 FROM 'CALL female_employees()';

EXEC SQL DECLARE C1 CURSOR FOR S1;

EXEC SQL OPEN C1;

for(;;) {

EXEC SQL FETCH C1 INTO :hv_name;

if( SQLCODE != SQLE_NOERROR ) break;

printf( "%s\\n", hv_name );

}

EXEC SQL CLOSE C1;

In this example, the procedure has been invoked with an OPEN statement rather than an EXECUTE statement. The OPEN statement causes the procedure to execute until it reaches a SELECT statement. At this point, C1 is a cursor for the SELECT statement within the database procedure. You can use all forms of the FETCH command (backward and forward scrolling) until you are finished with it. The CLOSE statement terminates execution of the procedure.

If there had been another statement following the SELECT in the procedure, it would not have been executed. In order to execute statements following a SELECT, use the RESUME cursor-name command. The RESUME command will either return the warning SQLE_PROCEDURE_COMPLETE, or it will return SQLE_NOERROR indicating that there is another cursor. The example illustrates a two-select procedure:

EXEC SQL CREATE PROCEDURE people()

RESULT( name char(50) )

BEGIN

SELECT emp_fname || emp_lname

FROM employee;

SELECT fname || lname

FROM customer;

END;

EXEC SQL PREPARE S1 FROM 'CALL female_employees()';

EXEC SQL DECLARE C1 CURSOR FOR S1;

EXEC SQL OPEN C1;

while( SQLCODE == SQLE_NOERROR ) {

for(;;) {

EXEC SQL FETCH C1 INTO :hv_name;

if( SQLCODE != SQLE_NOERROR ) break;

printf( "%s\\n", hv_name );

}

EXEC SQL RESUME C1;

}

EXEC SQL CLOSE C1;

Dynamic cursors for CALL statements

These examples have used static cursors. Full dynamic cursors can also be used for the CALL statement

  For a description of dynamic cursors, see Dynamic SELECT statement.

The DESCRIBE statement works fully for procedure calls. A DESCRIBE OUTPUT produces a SQLDA that has a description for each of the result set columns.

If the procedure does not have a result set, the SQLDA has a description for each INOUT or OUT parameter for the procedure. A DESCRIBE INPUT statement will produce a SQLDA having a description for each IN or INOUT parameter for the procedure.

DESCRIBE ALL

DESCRIBE ALL describes IN, INOUT, OUT and RESULT set parameters. DESCRIBE ALL uses the indicator variables in the SQLDA to provide additional information.

The DT_PROCEDURE_IN and DT_PROCEDURE_OUT bits are set in the indicator variable when a CALL statement is described. DT_PROCEDURE_IN indicates an IN or INOUT parameter and DT_PROCEDURE_OUT indicates an INOUT or OUT parameter. Procedure RESULT columns have both bits clear.

After a describe OUTPUT, these bits can be used to distinguish between statements that have result sets (need to use OPEN, FETCH, RESUME, CLOSE) and statements that do not (need to use EXECUTE).

  For a complete description, see DESCRIBE statement.

Multiple result sets

If you have a procedure that returns multiple result sets, you must redescribe after each RESUME statement if the result sets change shapes.

You need to describe the cursor, not the statement, to redescribe the current position of the cursor.


Library functions
  ASA Programming Interfaces Guide

    CHAPTER 2.  The Embedded SQL Interface

 Library functions

The SQL preprocessor generates calls to functions in the interface library or DLL. In addition to the calls generated by the SQL preprocessor, several routines are provided for the user to make database operations easier to perform. Prototypes for these functions are included by the EXEC SQL INCLUDE SQLCA command.

This section contains a detailed description of these various functions by category.

DLL entry points

The DLL entry points are the same except that the prototypes have a modifier appropriate for DLLs.

You can declare the entry points in a portable manner using _esqlentry_, which is defined in sqlca.h. It resolves to the following values:

•	Windows 95/98, Windows NT/2000, Windows CE:    __stdcall

All of the pointers that are passed as parameters to the Windows DLL entry points or returned by these functions are far pointers.

Example

For example, the first prototype listed below is db_init. For Windows, it would be:

unsigned short _esqlentry_ db_init( struct sqlca far *sqlca );

Passing null pointers
Care should be taken passing the null pointer as a parameter in Windows if your program is compiled in the small or medium memory models. You should use the _sql_ptrchk_() macro defined in sqlca.h for any pointer parameter that is a variable that might contain the null pointer. This macro converts a null near pointer into a null far pointer.

 Interface initialization functions

 Connection and server management functions

 SQLDA management functions

 Backup functions

 Request management functions

 Other functions


Interface initialization functions
  ASA Programming Interfaces Guide

    CHAPTER 2.  The Embedded SQL Interface

      Library functions

 Interface initialization functions

This section lists the functions that initialize and release the interface.

 db_init function

 db_fini function


db_init function
  ASA Programming Interfaces Guide

    CHAPTER 2.  The Embedded SQL Interface

      Library functions

        Interface initialization functions

 db_init function

Prototype

unsigned short _esqlentry_ db_init( struct sqlca *sqlca );

Description

This function initializes the database interface library. This function must be called before any other library call is made, and before any Embedded SQL command is executed. The resources the interface library requires for your program are allocated and initialized on this call.

Use db_fini to free the resources at the end of your program. If there are any errors during processing, they are returned in the SQLCA and 0 is returned. If there are no errors, a non-zero value is returned and you can begin using Embedded SQL commands and functions.

In most cases, this function should be called only once (passing the address of the global sqlca variable defined in the sqlca.h header file). If you are writing a DLL or an application that has multiple threads using Embedded SQL, call db_init once for each SQLCA that is being used (see SQLCA management for multi-threaded or reentrant code).

Caution
Failure to call db_fini for each db_init on NetWare can cause the database server to fail, and the NetWare file server to fail.


db_fini function
  ASA Programming Interfaces Guide

    CHAPTER 2.  The Embedded SQL Interface

      Library functions

        Interface initialization functions

 db_fini function

Prototype

unsigned short _esqlentry_ db_fini( struct sqlca *sqlca );

This function frees resources used by the database interface or DLL. You must not make any other library calls or execute any Embedded SQL commands after db_fini is called. If there are any errors during processing, they are returned in the SQLCA and 0 is returned. If there are no errors, a non-zero value is returned.

You need to call db_fini once for each SQLCA being used.

Caution
Failure to call db_fini for each db_init on NetWare can cause the database server to fail, and the NetWare file server to fail.


Connection and server management functions
  ASA Programming Interfaces Guide

    CHAPTER 2.  The Embedded SQL Interface

      Library functions

 Connection and server management functions

The following functions provide a means to start and stop the database server or, start or stop a database on an existing database server; and connect to or disconnect from a database.

All of these functions take a NULL-terminated string as the second argument. This string is a list of parameter settings of the form KEYWORD=value, delimited by semicolons. The number sign (#) is an alternative to the equals sign, and should be used when the equals sign is a syntax error, such as in environment variables on some platforms..

  For an available list of connection parameters, see Connection parameters.

Each function uses a subset of the available connection parameters, but every function will allow any parameter to be set. A sample connection parameter string is:

"UID=dba;PWD=sql;DBF=c:\asa7\asademo.db"

When included in Embedded SQL, the backslash character (\) must be escaped with a second backslash character in order to work:

"UID=dba;PWD=sql;DBF=c:\\asa7\\asademo.db"

 db_locate_servers function

 db_string_connect function

 db_string_disconnect function

 db_string_ping_server function

 db_start_engine function

 db_start_database function

 db_stop_database function

 db_stop_engine function

 db_find_engine function


db_locate_servers function
  ASA Programming Interfaces Guide

    CHAPTER 2.  The Embedded SQL Interface

      Library functions

        Connection and server management functions

 db_locate_servers function

Prototype

unsigned int db_locate_servers( SQLCA *sqlca,
SQL_CALLBACK_PARM callback_address,
void *callback_user_data );

Description

Provides programmatic access to the information displayed by the dblocate command-line utility, listing all the Adaptive Server Anywhere database servers on the local network that are listening on TCP/IP.

The callback function must have the following prototype:

int (*)( SQLCA *sqlca,
a_server_address *server_addr,
void *callback_user_data );

The callback function is called for each server found. If the callback function returns 0, db_locate_servers stops iterating through servers.

The sqlca and callback_user_data passed to the callback function are those passed into db_locate_servers. The second parameter is a pointer to an a_server_address structure. a_server_address is  defined in sqlca.h, with the following definition:

typedef struct a_server_address {

    a_sql_uint32	port_type;

    a_sql_uint32	port_num;

    char 	*name;

    char 	*address;

} a_server_address;

•	port_type    Is always PORT_TYPE_TCP at this time (defined to be 6 in sqlca.h).

•	port_num    Is the TCP port number on which this server is listening.

•	name    Points to a buffer containing the server name.

•	address    Points to a buffer containing the IP address of the server.

  For more information, see The Server Location utility.


db_string_connect function
  ASA Programming Interfaces Guide

    CHAPTER 2.  The Embedded SQL Interface

      Library functions

        Connection and server management functions

 db_string_connect function

Prototype

unsigned _esqlentry_ db_string_connect( struct sqlca * sqlca, char * parms );

Description

Provides extra functionality beyond the Embedded SQL CONNECT command. This function carries out a connection using the algorithm described in Troubleshooting connections.

The return value is true (non-zero) if a connection was successfully established and false (zero) otherwise. Error information for starting the server, starting the database, or connecting is returned in the SQLCA.


db_string_disconnect function
  ASA Programming Interfaces Guide

    CHAPTER 2.  The Embedded SQL Interface

      Library functions

        Connection and server management functions

 db_string_disconnect function

Prototype

unsigned _esqlentry_ db_string_disconnect( struct sqlca * sqlca, char * parms );

Description

This function disconnects the connection identified by the ConnectionName parameter. All other parameters are ignored.

If no ConnectionName parameter is specified in the string, the unnamed connection is disconnected. This is equivalent to the Embedded SQL DISCONNECT command. The boolean return value is true if a connection was successfully ended. Error information is returned in the SQLCA.

This function shuts down the database if it was started with the AutoStop=yes parameter and there are no other connections to the database. It also stops the server if it was started with the AutoStop=yes parameter and there are no other databases running.


db_string_ping_server function
  ASA Programming Interfaces Guide

    CHAPTER 2.  The Embedded SQL Interface

      Library functions

        Connection and server management functions

 db_string_ping_server function

Prototype

short _esqlentry_ db_string_ping_server(
SQLCA _fd_ * sqlca,
char _fd_ * connect_string,
unsigned int	connect_to_db );

Description

The connect_string is a normal connect string that may or may not contain server and database information.

If connect_to_db is non-zero (true), then the function attempts to connect to a database on a server. It returns a non-zero (true) value only if the connect string is sufficient to connect to a the named database on the named server.

If connect_to_db is zero, then the function only attempts to locate a server. It returns a non-zero value only if the connect string is sufficient to locate a server. It makes no attempt to connect to the database.


db_start_engine function
  ASA Programming Interfaces Guide

    CHAPTER 2.  The Embedded SQL Interface

      Library functions

        Connection and server management functions

 db_start_engine function

Prototype

unsigned _esqlentry_ db_start_engine( struct sqlca * sqlca,

char * parms );

Description

Start the database server if it is not running. The steps carried out by this function are those listed in Starting a personal server.

The return value is true if a database server was either found or successfully started. Error information is returned in the SQLCA.

The following call to db_start_engine starts the database server and names it asademo, but does not load the database, despite the DBF connection parameter:

db_start_engine( &sqlca, "DBF=c:\\asa7\\asademo.db; Start=dbeng7" );

If you wish to start a database as well as the server, include the database file in the START connection parameter:

db_start_engine( &sqlca,"ENG=eng_name;START=dbeng7 c:\\asa7\\asademo.db" );

This call starts the server, names it eng_name, and starts the asademo database on that server.

The db_start_engine function attempts to connect to a server before starting one, to avoid attempting to start a server that is already running.

The FORCESTART connection parameter is used only by the db_start_engine function. When set to YES, there is no attempt to connect to a server before trying to start one. This enables the following pair of commands to work as expected:

1	Start a database server named server_1

start dbeng7 -n server_1 asademo.db

2	Force a new server to start and connect to it:

db_start_engine( &sqlda, "START=dbeng7 -n server_2 asademo.db;ForceStart=YES" )

If FORCESTART was not used, and without an ENG parameter, the second command would have attempted to connect to server_1. The db_start_engine function does not pick up the server name from the -n switch of the START parameter.


db_start_database function
  ASA Programming Interfaces Guide

    CHAPTER 2.  The Embedded SQL Interface

      Library functions

        Connection and server management functions

 db_start_database function

Prototype

unsigned _esqlentry_ db_start_database( struct sqlca * sqlca, char * parms );

Description

Start a database on an existing server if the database is not already running. The steps carried out to start a database are described in Starting a personal server

The return value is true if the database was already running or successfully started. Error information is returned in the SQLCA.

If a user ID and password are supplied in the parameters, they are ignored.

  The permission required to start and stop a database is set on the server command line. For information, see The database server.


db_stop_database function
  ASA Programming Interfaces Guide

    CHAPTER 2.  The Embedded SQL Interface

      Library functions

        Connection and server management functions

 db_stop_database function

Prototype

unsigned int _esqlentry_ db_stop_database( struct sqlca * sqlca,

 char * parms );

Description

Stop the database identified by DatabaseName on the server identified by EngineName. If EngineName is not specified, the default server is used.

By default, this function does not stop a database that has existing connections. If Unconditional is yes, the database is stopped regardless of existing connections.

A return value of TRUE indicates that there were no errors.

  The permission required to start and stop a database is set on the server command line. For information, see The database server.


db_stop_engine function
  ASA Programming Interfaces Guide

    CHAPTER 2.  The Embedded SQL Interface

      Library functions

        Connection and server management functions

 db_stop_engine function

Prototype

unsigned int _esqlentry_ db_stop_engine( struct sqlca * sqlca,

 char * parms );

Description

Terminates execution of the database server. The steps carried out by this function are:

•	Look for a local database server that has a name that matches the EngineName parameter. If no EngineName is specified, look for the default local database server.

•	If no matching server is found, this function fails.

•	Send a request to the server to tell it to checkpoint and shut down all databases.

•	Unload the database server.

By default, this function will not stop a database server that has existing connections. If Unconditional is yes, the database server is stopped regardless of existing connections.

A C program can use this function instead of spawning DBSTOP. A return value of TRUE indicates that there were no errors.

The use of db_stop_ending is subject to the permissions set on the -gk database server command-line option. For more information, see -gk command-line option.


db_find_engine function
  ASA Programming Interfaces Guide

    CHAPTER 2.  The Embedded SQL Interface

      Library functions

        Connection and server management functions

 db_find_engine function

Prototype

unsigned short _esqlentry_ db_find_engine( struct sqlca *sqlca, char *name );

Description

Returns an unsigned short value, which indicates status information about the database server whose name is name. If no server can be found with the specified name, the return value is 0. A non-zero value indicates that the server is currently running.

Each bit in the return value conveys some information. Constants that represent the bits for the various pieces of information are defined in the sqldef.h header file. If a null pointer is specified for name, information is returned about the default database environment.


SQLDA management functions
  ASA Programming Interfaces Guide

    CHAPTER 2.  The Embedded SQL Interface

      Library functions

 SQLDA management functions

The following functions are used to manage SQL Descriptor Areas (SQLDAs).

  For a detailed description of the SQLDA, see The SQL descriptor area (SQLDA).

 alloc_sqlda_noind function

 alloc_sqlda function

 fill_sqlda function

 sqlda_string_length function

 sqlda_storage function

 fill_s_sqlda function

 free_filled_sqlda function

 free_sqlda_noind function

 free_sqlda function


alloc_sqlda_noind function
  ASA Programming Interfaces Guide

    CHAPTER 2.  The Embedded SQL Interface

      Library functions

        SQLDA management functions

 alloc_sqlda_noind function

Prototype

struct sqlda *alloc_sqlda_noind( unsigned numvar );

Description

Allocates a SQLDA with descriptors for numvar variables. The sqln field of the SQLDA is initialized to numvar. Space is not allocated for indicator variables; the indicator pointers are set to the null pointer. A null pointer is returned if memory cannot be allocated.


alloc_sqlda function
  ASA Programming Interfaces Guide

    CHAPTER 2.  The Embedded SQL Interface

      Library functions

        SQLDA management functions

 alloc_sqlda function

Prototype

struct sqlda *alloc_sqlda( unsigned numvar );

Description

Allocates a SQLDA with descriptors for numvar variables. The sqln field of the SQLDA is initialized to numvar. Space is allocated for the indicator variables, the indicator pointers are set to point to this space, and the indicator value is initialized to zero. A null pointer is returned if memory cannot be allocated. It is recommended that you use this function instead of alloc_sqlda_noind function.


fill_sqlda function
  ASA Programming Interfaces Guide

    CHAPTER 2.  The Embedded SQL Interface

      Library functions

        SQLDA management functions

 fill_sqlda function

Prototype

struct sqlda *fill_sqlda( struct sqlda *sqlda );

Description

Allocates space for each variable described in each descriptor of sqlda, and assigns the address of this memory to the sqldata field of the corresponding descriptor. Enough space is allocated for the database type and length indicated in the descriptor. Returns sqlda if successful and returns the null pointer if there is not enough memory available.


sqlda_string_length function
  ASA Programming Interfaces Guide

    CHAPTER 2.  The Embedded SQL Interface

      Library functions

        SQLDA management functions

 sqlda_string_length function

Prototype

unsigned long sqlda_string_length( struct sqlda *sqlda, int varno );

Description

Returns the length of the C string (type DT_STRING) that would be required to hold the variable sqlda->sqlvar[varno] (no matter what its type is).


sqlda_storage function
  ASA Programming Interfaces Guide

    CHAPTER 2.  The Embedded SQL Interface

      Library functions

        SQLDA management functions

 sqlda_storage function

Prototype

unsigned long _esqlentry_ sqlda_storage( struct sqlda *sqlda, int varno );

Description

Returns the amount of storage required to store any value for the variable described in sqlda->sqlvar[varno].


fill_s_sqlda function
  ASA Programming Interfaces Guide

    CHAPTER 2.  The Embedded SQL Interface

      Library functions

        SQLDA management functions

 fill_s_sqlda function

Prototype

struct sqlda *fill_s_sqlda( struct sqlda *sqlda, unsigned int maxlen );

Description

The same as fill_sqlda, except that it changes all the data types in sqlda to type DT_STRING.. Enough space is allocated to hold the string representation of the type originally specified by the SQLDA, up to a maximum of maxlen bytes. The length fields in the SQLDA (sqllen) are modified appropriately. Returns sqlda if successful and returns the null pointer if there is not enough memory available.


free_filled_sqlda function
  ASA Programming Interfaces Guide

    CHAPTER 2.  The Embedded SQL Interface

      Library functions

        SQLDA management functions

 free_filled_sqlda function

Prototype

void _esqlentry_ free_filled_sqlda( struct sqlda *sqlda );

Description

Free the memory allocated to each sqldata pointer, and the space allocated for the SQLDA itself. Any null pointer is not freed.

Calling this function causes free_sqlda to be called automatically, and so any descriptors allocated by alloc_sqlda are freed.


free_sqlda_noind function
  ASA Programming Interfaces Guide

    CHAPTER 2.  The Embedded SQL Interface

      Library functions

        SQLDA management functions

 free_sqlda_noind function

Prototype

void _esqlentry_ free_sqlda_noind( struct sqlda *sqlda );

Description

Free space allocated to this sqlda. Do not free the memory referenced by each sqldata pointer. The indicator variable pointers are ignored.


free_sqlda function
  ASA Programming Interfaces Guide

    CHAPTER 2.  The Embedded SQL Interface

      Library functions

        SQLDA management functions

 free_sqlda function

Prototype

void _esqlentry_ free_sqlda( struct sqlda *sqlda );

Description

Free space allocated to this sqlda, and free the indicator variable space, as allocated in fill_sqlda. Do not free the memory referenced by each sqldata pointer.


Backup functions
  ASA Programming Interfaces Guide

    CHAPTER 2.  The Embedded SQL Interface

      Library functions

 Backup functions

The db_backup function provides support for online backup. The Adaptive Server Anywhere backup utility makes use of this function. You should only need to write a program to use this function if your backup requirements are not satisfied by the Adaptive Server Anywhere backup utility.

  You can also access the backup utility directly using the Database Tools DBBackup function. For more information on this function, see DBBackup function.

Every database contains one or more files. Normally, a database contains two files: the main database file and the transaction log.

Each file is divided into fixed size pages, and the size of these pages is specified when the database is created.

Backup works by opening a file, and then making a copy of each page in the file. Backup performs a checkpoint on startup, and the database files are backed up as of this checkpoint. Any changes that are made while the backup is running are recorded in the transaction log, and are backed up with the transaction log. This is why the transaction log is always backed up last.

Authorization

You must be connected to a user ID with DBA authority or REMOTE DBA authority (SQL Remote) to use the backup functions.

 db_backup function

 db_delete_file function


db_backup function
  ASA Programming Interfaces Guide

    CHAPTER 2.  The Embedded SQL Interface

      Library functions

        Backup functions

 db_backup function

Prototype

void db_backup( struct sqlca * sqlca,
int op, int file_num,
unsigned long page_num,
struct sqlda * sqlda);

Authorization

Must be connected to a user ID with DBA authority or REMOTE DBA authority (SQL Remote).

Description

The action performed depends on the value of the op parameter:

•	DB_BACKUP_START    Must be called before a backup can start. Only one backup can be running at one time against any given database server. Database checkpoints are disabled until the backup is complete (db_backup is called with an op value of DB_BACKUP_END). If the backup cannot start, the SQLCODE is SQLE_BACKUP_NOT_STARTED. Otherwise, the SQLCOUNT field of the sqlca is set to the size of each database page. (Backups are processed one page at a time.)

The file_num, page_num and sqlda parameters are ignored.

•	DB_BACKUP_OPEN_FILE    Open the database file specified by file_num, which allows pages of the specified file to be backed up using DB_BACKUP_READ_PAGE. Valid file numbers are 0 through DB_BACKUP_MAX_FILE for the main database files, DB_BACKUP_TRANS_LOG_FILE for the transaction log file, and DB_BACKUP_WRITE_FILE for the database write file if it exists. If the specified file does not exist, the SQLCODE is SQLE_NOTFOUND. Otherwise, SQLCOUNT contains the number of pages in the file, SQLIOESTIMATE contains a 32-bit value (POSIX time_t) which identifies the time that the database file was created, and the operating system file name is in the sqlerrmc field of the SQLCA.

The page_num and sqlda parameters are ignored.

•	DB_BACKUP_READ_PAGE    Read one page of the database file specified by file_num. The page_num should be a value from 0 to one less than the number of pages returned in SQLCOUNT by a successful call to db_backup with the DB_BACKUP_OPEN_FILE operation. Otherwise, SQLCODE is set to SQLE_NOTFOUND. The sqlda descriptor should be set up with one variable of type DT_BINARY pointing to a buffer. The buffer should be large enough to hold binary data of the size returned in the SQLCOUNT field on the call to db_backup with the DB_BACKUP_START operation.

DT_BINARY data contains a two-byte length followed by the actual binary data, so the buffer must be two bytes longer than the page size.

Application must save buffer
This call makes a copy of the specified database page into the buffer, but it is up to the application to save the buffer on some backup media.

•	DB_BACKUP_READ_RENAME_LOG    This action is the same as DB_BACKUP_READ_PAGE, except that after the last page of the transaction log has been returned, the database server renames the transaction log and starts a new one.

If the database server is unable to rename the log at the current time (there are incomplete transactions), you will get the SQLE_BACKUP_CANNOT_RENAME_LOG_YET error. In this case, don't use the page returned, but instead reissue the request until you receive SQLE_NOERROR and then write the page. Continue reading the pages until you receive the SQLE_NOTFOUND condition.

The SQLE_BACKUP_CANNOT_RENAME_LOG_YET error may be returned multiple times and on multiple pages. In your retry loop, you should add a delay so as not to slow the server down with too many requests.

When you receive the SQLE_NOTFOUND condition, the transaction  log has been backed up successfully and the file has been  renamed. The name for the old transaction file is  returned in the sqlerrmc field of the SQLCA.

You should check the sqlda->sqlvar[0].sqlind value after a db_backup call. If this value is greater than zero, the last log page has been written and the log file has been renamed. The new name is still in sqlca.sqlerrmc, but the SQLCODE value is SQLE_NOERROR.

You should not call db_backup again after this, except to close files and finish the backup. If you do, you get a second copy of your backed up log file and you receive SQLE_NOTFOUND.

•	DB_BACKUP_CLOSE_FILE    Must be called when processing of one file is complete to close the database file specified by file_num.

The page_num and sqlda parameters are ignored.

•	DB_BACKUP_END    Must be called at the end of the backup. No other backup can start until this backup has ended. Checkpoints are enabled again.

The file_num, page_num and sqlda parameters are ignored.

The dbbackup program uses the following algorithm. Note that this is not C code, and does not include error checking.

db_backup( ... DB_BACKUP_START ... )

allocate page buffer based on page size in SQLCODE

sqlda = alloc_sqlda( 1 )

sqlda->sqld = 1;

sqlda->sqlvar[0].sqltype = DT_BINARY

sqlda->sqlvar[0].sqldata = allocated buffer

for file_num = 0 to DB_BACKUP_MAX_FILE

  db_backup( ... DB_BACKUP_OPEN_FILE, file_num ... )

  if SQLCODE == SQLE_NO_ERROR

    /* The file exists */

    num_pages = SQLCOUNT

    file_time = SQLE_IO_ESTIMATE

    open backup file with name from sqlca.sqlerrmc

    for page_num = 0 to num_pages - 1

      db_backup( ... DB_BACKUP_READ_PAGE,

                file_num, page_num, sqlda )

      write page buffer out to backup file

    next page_num

    close backup file

    db_backup( ... DB_BACKUP_CLOSE_FILE, file_num ... )

  end if

next file_num

backup up file DB_BACKUP_WRITE_FILE as above

backup up file DB_BACKUP_TRANS_LOG_FILE as above

free page buffer

db_backup( ... DB_BACKUP_END ... )


db_delete_file function
  ASA Programming Interfaces Guide

    CHAPTER 2.  The Embedded SQL Interface

      Library functions

        Backup functions

 db_delete_file function

Prototype

void db_delete_file( struct sqlca * sqlca,

char * filename );

Authorization

Must be connected to a user ID with DBA authority or REMOTE DBA authority (SQL Remote).

Description

The db_delete_file function requests the database server to delete filename. This can be used after backing up and renaming the transaction log (see DB_BACKUP_READ_RENAME_LOG above) to delete the old transaction log. You must be connected to a user ID with DBA authority.


Request management functions
  ASA Programming Interfaces Guide

    CHAPTER 2.  The Embedded SQL Interface

      Library functions

 Request management functions

The default behavior of the interface DLL is for applications to wait for completion of each database request before carrying out other functions. This behavior can be changed using request management functions. For example, when using Interactive SQL, the operating system is still active while Interactive SQL is waiting for a response from the database, and Interactive SQL carries out some tasks in that time.

You can achieve application activity while a database request is in progress by providing a callback function. In this callback function you must not do another database request except db_cancel_request. You can use the db_is_working function in your message handlers to determine if you have a database request in progress.

This callback function in your application is called repeatedly while the database server is busy processing a request. You can then process Windows messages by calling GetMessage or PeekMessage. (These function calls allow Windows to be active.) The dblib7w.dll continually calls this function until the response from the database server is received.

The db_register_a_callback function is used to register your application callback functions:

 db_cancel_request function

 db_is_working function

 db_register_a_callback function


db_cancel_request function
  ASA Programming Interfaces Guide

    CHAPTER 2.  The Embedded SQL Interface

      Library functions

        Request management functions

 db_cancel_request function

Prototype

int db_cancel_request( struct sqlca *sqlca );

Description

Cancels the currently active database server request. This function will check to make sure a database server request is active before sending the cancel request. If the function returns 1, then the cancel request was sent; if it returns 0, then no request was sent.

A non-zero return value does not mean that the request was canceled. There are a few critical timing cases where the cancel request and the response from the database or server "cross". In these cases, the cancel simply has no effect, even though the function still returns TRUE.

The db_cancel_request function can be called asynchronously. This function and db_is_working are the only functions in the database interface library that can be called asynchronously using an SQLCA that might be in use by another request.

If you cancel a request that is carrying out a cursor operation, the position of the cursor is indeterminate. You must locate the cursor by its absolute position, or close it, following the cancel.


db_is_working function
  ASA Programming Interfaces Guide

    CHAPTER 2.  The Embedded SQL Interface

      Library functions

        Request management functions

 db_is_working function

Prototype

unsigned db_is_working( struct sqlca *sqlca );

Description

Returns 1 if your application has a database request in progress that uses the given sqlca, and 0 if there is no request in progress that uses the given sqlca.

This function can be called asynchronously. This function and db_cancel_request are the only functions in the database interface library that can be called asynchronously using an SQLCA that might be in use by another request.


db_register_a_callback function
  ASA Programming Interfaces Guide

    CHAPTER 2.  The Embedded SQL Interface

      Library functions

        Request management functions

 db_register_a_callback function

Prototype

void SQL_CALLBACK db_register_a_callback(
struct sqlca *sqlca,
a_db_callback_index index,
(SQL_CALLBACK_PARM)callback );

Description

This function registers callback functions.

You should call MakeProcInstance with your function address and pass that to the db_register_a_callback function.

If you do not register any callback functions, the default action is to do nothing. Your application blocks, waiting for the database response, and Windows changes the cursor to an hourglass.

To remove a callback, pass a null pointer as the callback function.

The following values are allowed for the index parameter:

•	DB_CALLBACK_START    The prototype is as follows:

void SQL_CALLBACK db_start_request( struct sqlca *sqlca );

This function is called just before a database request is sent to the server. DB_CALLBACK_START is used only on Windows 95/98, Windows NT/2000, and Windows CE.

•	DB_CALLBACK_FINISH    The prototype is as follows:

void SQL_CALLBACK db_finish_request( struct sqlca *sqlca );

This function is called after the response to a database request has been received by the interface DLL. DB_CALLBACK_FINISH is used only on Windows 95/98, Windows NT/2000, and Windows CE.

•	DB_CALLBACK_CONN_DROPPED    The prototype is as follows:

void SQL_CALLBACK db_conn_dropped( SQLCA *sqlca,

                  char *conn_name );

This function is called when the database server is about to drop a connection because of a liveness timeout, or through a DROP CONNECTION statement, or because the database server is being shut down. The connection name conn_name is passed in to allow you to distinguish between connections. If the connection was not named, it has a value of NULL.

•	DB_CALLBACK_WAIT    The prototype is as follows:

void SQL_CALLBACK db_wait_request( struct sqlca *sqlca );

This function is called repeatedly by the interface DLL while the database server or client library is busy processing your database request.

The following is a sample DB_CALLBACK_WAIT callback function:

void SQL_CALLBACK db_wait_request( struct sqlca *sqlca )

{ MSG msg

  if( GetMessage( &msg, NULL, 0, 0 ) ) {

    if( !db_process_a_message( sqlca, &msg ) ) {

      if( !TranslateAccelerator( hWnd, hAccel, &msg ) ) {

        TranslateMessage( &msg );

        DispatchMessage( &msg );

      }

    }

  }

}

You would register this callback as follows:

db_register_a_callback( &sqlca,

   DBCALLBACK_MESSAGE,

   (SQL_CALLBACK_PARM)&db_wait_request );

•	DB_CALLBACK_DEBUG_MESSAGE    Display information that is placed in the client log file. The supplied function is called once for each debug message and is passed a null-terminated string containing the text of the message. The string usually has a new line character (\n) immediately before the terminating null character.

The callback prototype is as follows:

void SQL_CALLBACK print_debug_message( SQLCA * sqlca,

                  char *message_string);

•	DB_CALLBACK_MESSAGE    This is used to enable the application to handle messages received from the server during the processing of a request.

The callback prototype is as follows:

void SQL_CALLBACK message_callback( SQLCA* sqlca,

                 unsigned short msg_type,

                 an_sql_code code,

                 unsigned length,

                 char*  msg )

The msg_type parameter states how important the message is, and you may wish to handle different message types in different ways. The available message types are MESSAGE_TYPE_INFO, MESSAGE_TYPE_WARNING, MESSAGE_TYPE_ACTION and MESSAGE_TYPE_STATUS. These constants are defined in sqldef.h. The code field is an identifier. The length field tells you how long the message is. The message is not null-terminated.

For example, the Interactive SQL callback displays STATUS and INFO message in the message window, while messages of type ACTION and WARNING go to a dialog box. If an application does not register this callback, there is a default callback, which causes all messages to be written to the server logfile (if debugging is on and a logfile is specified). In addition, messages of type MESSAGE_TYPE_WARNING and MESSAGE_TYPE_ACTION are more prominently displayed, in an operating system-dependent manner.


Other functions
  ASA Programming Interfaces Guide

    CHAPTER 2.  The Embedded SQL Interface

      Library functions

 Other functions

The following functions are miscellaneous functions.

 sql_needs_quotes function

 sqlerror_message function


sql_needs_quotes function
  ASA Programming Interfaces Guide

    CHAPTER 2.  The Embedded SQL Interface

      Library functions

        Other functions

 sql_needs_quotes function

Prototype

unsigned int sql_needs_quotes( struct sqlca *sqlca, char *str );

Description

Returns a Boolean value that indicates whether the string requires double quotes around it when it is used as a SQL identifier. This function formulates a request to the database server to determine if quotes are needed. Relevant information is stored in the sqlcode field.

There are three cases of return value/code combinations:

•	return = FALSE, sqlcode = 0    In this case, the string definitely does not need quotes

•	return = TRUE    In this case, sqlcode is always SQLE_WARNING, and the string definitely does need quotes

•	return = FALSE    If sqlcode is something other than SQLE_WARNING, the test is inconclusive


sqlerror_message function
  ASA Programming Interfaces Guide

    CHAPTER 2.  The Embedded SQL Interface

      Library functions

        Other functions

 sqlerror_message function

Prototype

char *sqlerror_message( struct sqlca *sqlca, char * buffer, int max );

Description

Return a pointer to a string that contains an error message. The error message contains text for the error code in the SQLCA. If no error was indicated, a null pointer is returned. The error message is placed in the buffer supplied, truncated to length max if necessary.


Embedded SQL commands
  ASA Programming Interfaces Guide

    CHAPTER 2.  The Embedded SQL Interface

 Embedded SQL commands

EXEC SQL
  ALL Embedded SQL statements must be preceded with EXEC SQL and end with a semicolon.

There are two groups of Embedded SQL commands:

Standard SQL commands are used by simply placing them in a C program enclosed with EXEC SQL and a semi-colon. CONNECT, DELETE, SELECT, SET and UPDATE have additional formats only available in Embedded SQL. The additional formats fall into the second category of Embedded SQL specific commands.

  All commands are described in detail in SQL Statements.

Several SQL commands are specific to Embedded SQL and can only be used in a C program.

  These Embedded SQL commands are also described in SQL Language Elements.

The Embedded SQL commands include the following:

•	ALLOCATE DESCRIPTOR    Allocate memory for a descriptor

•	CLOSE    close a cursor

•	CONNECT    connect to the database

•	DEALLOCATE DESCRIPTOR    Reclaim memory for a descriptor

•	Declaration Section    declare host variables for database communication

•	DECLARE    declare a cursor

•	DELETE (positioned)    delete the row at the current position in a cursor

•	DESCRIBE    describe the host variables for a particular SQL statement

•	DISCONNECT    disconnect from database server

•	DROP STATEMENT    free resources used by a prepared statement

•	EXECUTE    execute a particular SQL statement

•	EXPLAIN    explain the optimization strategy for a particular cursor

•	FETCH    fetch a row from a cursor

•	GET OPTION    get the setting for a particular database option

•	INCLUDE    include a file for SQL preprocessing

•	OPEN    open a cursor

•	PREPARE    prepare a particular SQL statement

•	PUT    insert a row into a cursor

•	SET CONNECTION    change active connection

•	SET OPTION    change a database option value

•	SET SQLCA    use an SQLCA other than the default global one

•	UPDATE (positioned)    update the row at the current location of a cursor

•	WHENEVER    specify actions to occur on errors in SQL statements


Database examples
  ASA Programming Interfaces Guide

    CHAPTER 2.  The Embedded SQL Interface

 Database examples

Two Embedded SQL examples are included with the Adaptive Server Anywhere installation. The static cursor Embedded SQL example, cur.sqc, demonstrates the use of static SQL statements. The dynamic cursor Embedded SQL example, dcur.sqc, demonstrates the use of dynamic SQL statements. In addition to these examples, you may find other programs and source files as part of the installation of Adaptive Server Anywhere which demonstrate features available for particular platforms.

File locations

Source code for the examples is installed as part of the Adaptive Server Anywhere installation. They are placed in the cxmp subdirectory of your Adaptive Server Anywhere installation directory.

 Building the examples

 Running the example programs

 Static cursor example

 Dynamic cursor example

 Windows NT Service examples


Building the examples
  ASA Programming Interfaces Guide

    CHAPTER 2.  The Embedded SQL Interface

      Database examples

 Building the examples

Along with the sample program is a batch file, makeall.bat, that can be used to compile the sample program for the various environments and compilers supported by Adaptive Server Anywhere. For UNIX, use the shell script makeall. For Windows CE, use the dcur.dsp project file for Microsoft Visual C++.

The format of the command is as follows:

makeall {Example} {Platform} {Compiler}

The first parameter is the name of the example program that you want to compile. It is one of:

•	cur    static cursor example

•	dcur    dynamic cursor example

The second parameter is the platform in which the program is run. The platform can be one of the following:

•	WINNT    compile for Windows NT/2000, Windows 95, and Windows 98.

•	NETWARE    compile for Netware NLM.

The third parameter is the compiler to use to compile the program. The compiler can be one of:

•	WC    use Watcom C/C++

•	MC    use Microsoft C

•	BC    use Borland C


Running the example programs
  ASA Programming Interfaces Guide

    CHAPTER 2.  The Embedded SQL Interface

      Database examples

 Running the example programs

Each example program presents a console-type user interface where it prompts you for a command. The various commands manipulate a database cursor and print the query results on the screen. Simply type the letter of the command you wish to perform. Some systems may require you to press Enter after the letter.

The commands are similar to the following, depending on which program you run:

•	p    print current page

•	u    move up a page

•	d    move down a page

•	b    move to the bottom of the page

•	t    move to the top of the page

•	i    insert a row (dcur only)

•	n    new table (dcur only)

•	q    quit

•	h    help (this list of commands)

Windows and Windows NT examples

The Windows versions of the example programs are real Windows programs. However, to keep the user interface code relatively simple, some simplifications have been made. In particular, these applications do not repaint their Windows on WM_PAINT messages except to reprint the prompt.

To run these programs, execute them  using the curwin executable name.


Static cursor example
  ASA Programming Interfaces Guide

    CHAPTER 2.  The Embedded SQL Interface

      Database examples

 Static cursor example

This example demonstrates the use of cursors. The particular cursor used here retrieves certain information from the employee table in the sample database. The cursor is declared statically, meaning that the actual SQL statement to retrieve the information is "hard coded" into the source program. This is a good starting point for learning how cursors work. The next example (Dynamic cursor example) takes this first example and converts it to use dynamic SQL statements.

  For information on where the source code can be found and how to build this example program , see Database examples.

The C program source code is in the cxmp subdirectory of your Adaptive Server Anywhere installation directory. The program looks much like a standard C program except there are Embedded SQL instructions that begin with EXEC SQL.

To reduce the amount of code that is duplicated by the cur and dcur example programs (and the odbc example), the mainlines and the data printing functions have been placed into a separate file. This is mainch.c for character mode systems, and mainwin.c for windowing environments.

The example programs each supply the following three routines, which are called from the mainlines.

•	WSQLEX_Init    Connects to the database and opens the cursor

•	WSQLEX_Process_Command    Processes commands from the user, manipulating the cursor as necessary.

•	WSQLEX_Finish    Closes the cursor and disconnect from the database.

The function of the mainline is to:

1	Call the WSQLEX_Init routine

2	Loop, getting commands from the user and calling WSQL_Process_Command until the user quits

3	Call the WSQLEX_Finish routine

Connecting to the database is accomplished with the Embedded SQL CONNECT command supplying the appropriate user ID and password.

The open_cursor routine both declares a cursor for the specific SQL command and also opens the cursor.

Printing a page of information is accomplished by the print routine. It loops pagesize times fetching a single row from the cursor and printing it out. Note that the fetch routine checks for warning conditions (such as Row not found) and prints appropriate messages when they arise. In addition, the cursor is repositioned by this program to the row before the one that is displayed at the top of the current page of data.

The move, top and bottom routines use the appropriate form of the FETCH statement to position the cursor. Note that this form of the FETCH statement doesn't actually get the data — it only positions the cursor. Also, a general relative positioning routine, move, has been implemented to move in either direction depending on the sign of the parameter.

When the user quits, the cursor is closed and the database connection is also released. The cursor is closed by a ROLLBACK WORK statement, and the connection is release by a DISCONNECT.


Dynamic cursor example
  ASA Programming Interfaces Guide

    CHAPTER 2.  The Embedded SQL Interface

      Database examples

 Dynamic cursor example

This example demonstrates the use of cursors for a dynamic SQL SELECT statement. It is a slight modification of the previous example. If you have not yet looked at Static cursor example it would be helpful to do so before looking at this example.

  For information on where the source code can be found and how to build this example program Database examples.

The dcur program allows the user to select a table to look at with the n command. The program then presents as much information from that table as will fit on the screen. The SELECT statement is built up in a program array using the C library function rintf.

When this program is run, it prompts for a connection string of the form:

uid=dba;pwd=sql;dbf=c:\asa7\asademo.db

The C program with the Embedded SQL is held in the cxmp subdirectory of your Adaptive Server Anywhere installation directory. The program looks much like the previous example with the exception of the connect, open_cursor and print functions.

The connect function uses the Embedded SQL interface function db_string_connect to connect to the database. This function provides the extra functionality to support the connection string that is used to connect to the database.

The open_cursor routine first builds the SELECT statement

SELECT * FROM tablename

where tablename is a parameter passed to the routine. It then prepares a dynamic SQL statement using this string.

The Embedded SQL DESCRIBE command is used to fill in the SQLDA structure the results of the SELECT statement.

Size of the SQLDA
An initial guess is taken for the size of the SQLDA (3). If this is not big enough, the actual size of the select list returned by the database server is used to allocate a SQLDA of the right size.

The SQLDA structure is then filled with buffers to hold strings that represent the results of the query. The fill_s_sqlda routine converts all data types in the SQLDA to DT_STRING, and allocates buffers of the appropriate size.

A cursor is then declared and opened for this statement. The rest of the routines for moving and closing the cursor remain the same.

The fetch routine is slightly different: it puts the results into the SQLDA structure instead of into a list of host variables. The print routine has changed significantly to print results from the SQLDA structure up to the width of the screen. The print routine also uses the name fields of the SQLDA to print headings for each column.


Windows NT Service examples
  ASA Programming Interfaces Guide

    CHAPTER 2.  The Embedded SQL Interface

      Database examples

 Windows NT Service examples

The example programs cur.sqc and dcur.sqc, when compiled for Windows NT/2000, run optionally as services.

The two files containing the example code for NT services are the source file ntsvc.c and the header file ntsvc.h. The code allows a linked executable to be run either as a regular executable or as an NT service.

♦ To run one of the compiled examples as an NT service:

1	Start Sybase Central, and open the Services folder.

2	Select a service type of Sample Application, and click OK.

3	Enter a service name in the appropriate field.

4	Select the sample program (curwnt.exe or dcurwnt.exe) from the cxmp subdirectory of the installation directory.

5	Click OK to install the service.

6	Click Start on the main window to start the service.

When run as a service, the programs display the normal user interface if possible. They also write the output to the Application Event Log. If it is not possible to start the user interface, the programs print one page of data to the Application Event Log and stop.

These examples have been tested with the Watcom C/C++ 10.5 compiler and the Microsoft Visual C++ compiler.


The SQL preprocessor
  ASA Programming Interfaces Guide

    CHAPTER 2.  The Embedded SQL Interface

 The SQL preprocessor

The SQL preprocessor processes a C or C++ program containing embedded SQL, before the compiler is run.

Syntax

sqlpp [  switches  ] sql-filename [  output-filename  ]

Switch	Description
-c "keyword=value;..."	Supply reference database connection parameters [UltraLite]
-d	Favor data size
-e level	Flag non-conforming SQL syntax as an error
-f	Put the far keyword on generated static data
-h line-width	Limit the maximum line length of output
-k	Include user declaration of SQLCODE
-n	Line numbers
-o operating-sys	Target operating system: WIN32, WINNT, NETWARE, or UNIX
-p project	UltraLite project name
-q	Quiet mode—do not print banner
-r	Generate reentrant code
-s string-len	Maximum string length for the compiler
-w level	Flag non-conforming SQL syntax as a warning
-x	Change multibyte SQL strings to escape sequences.
-z sequence	Specify collation sequence
See also

Application development using Embedded SQL

Description

The SQL preprocessor processes a C or C++ program containing embedded SQL before the compiler is run. SQLPP translates the SQL statements in the input-file into C language source that is put into the output-file. The normal extension for source programs with embedded SQL is .sqc. The default output filename is the sql-filename with an extension of .c. If the sql-filename has a .c extension, the default output filename extension is .CC.

Switches

Reference database connection parameters (-c)    Required when preprocessing files that are part of an UltraLite application. The connection string must give the SQL preprocessor access to read and modify your reference database.

Favor data size (-d)    Generate code that reduces data space size. Data structures will be reused and initialized at execution time before use. This increases code size.

Flag SQL/92 errors (-e)    This option flags any Embedded SQL that is not part of a specified set of SQL/92 as an error.

The allowed values of level and their meanings are as follows:

•	e    flag syntax that is not entry-level SQL/92 syntax

•	i    flag syntax that is not intermediate-level SQL/92 syntax

•	f    flag syntax that is not full-SQL/92 syntax

•	t    flag non-standard host variable types

•	u    flag syntax that is not supported by UltraLite

•	w    allow all supported syntax

Add far keyword (-f)    Put the far keyword in front of preprocessor-generated data. This may be required in conjunction with the Borland C++ compiler for the large memory model. By default, all static data is put in the same segment. Adding the far keyword will force static data into different segments. (By default, Watcom C and Microsoft C place data objects bigger than a threshold size in their own segment.)

Limit maximum output line length (-h)    Limits the maximum length of lines output by sqlpp to num. The continuation character is a backslash (\), and the minimum value of num is ten.

User SQLCODE declaration (-k)    Notifies the preprocessor that the program to be compiled includes a user declaration of SQLCODE.

Generate line number information (-n)    Generate line number information in the C file. This consists of #line directives in the appropriate places in the generated C code. If the compiler that you are using supports the #line directive, this switch will make the compiler report errors on line numbers in the SQC file (the one with the Embedded SQL) as opposed to reporting errors on line numbers in the C file generated by the SQL preprocessor. Also, the #line directives will indirectly be used by the source level debugger so that you can debug while viewing the SQC source file.

Target operating system (-o)    Specify the target operating system. Note that this option must match the operating system where you will run the program. A reference to a special symbol will be generated in your program. This symbol is defined in the interface library. If you use the wrong operating system specification or the wrong library, an error will be detected by the linker. The supported operating systems are:

•	WIN32    32-bit Microsoft Windows (Windows 95/98, Windows CE)

•	WINNT    Microsoft Windows NT/2000

•	NETWARE    Novell NetWare

•	UNIX    UNIX

UltraLite project (-p)    Identifies the UltraLite project to which the embedded SQL files belong. Applies only when processing files that are part of an UltraLite application.

Operate quietly (-q)     Do not print the banner.

Generate re-entrant code (-r)    For more information on re-entrant code, see SQLCA management for multi-threaded or reentrant code.

Set maximum string size (-s)    Set the maximum size string that the preprocessor will put into the C file. Strings longer than this value will be initialized using a list of characters ('a','b','c', etc). Most C compilers have a limit on the size of string literal they can handle. This option is used to set that upper limit. The default value is 500.

Flag SQL/92 warnings (-w)    This option flags any Embedded SQL that is not part of a specified set of SQL/92 as a warning.

The allowed values of level and their meanings are as follows:

•	e    flag syntax that is not entry-level SQL/92 syntax

•	i    flag syntax that is not intermediate-level SQL/92 syntax

•	f    flag syntax that is not full-SQL/92 syntax

•	t    flag non-standard host variable types

•	u    flag syntax that is not supported by UltraLite

•	w    allow all supported syntax

Escape multibyte strings (-x)    Change multibyte strings to escape sequences so that they can pass through compilers.

Specify collation sequence (-z)    This option specifies the collation sequence. For a listing of available collation sequences, type dbinit -l at the command prompt.

The collation sequence is used to help the preprocessor understand the characters used in the source code of the program, for example, in identifying alphabetic characters suitable for use in identifiers. If -z is not specified, the preprocessor attempts to determine a reasonable collation to use, based on the operating system and SQLLOCALE environment variable.


CHAPTER 3.  The Database Tools Interface
  ASA Programming Interfaces Guide

 CHAPTER 3.  The Database Tools Interface

About this chapter

This chapter describes how to use the database tools library that is provided with Adaptive Server Anywhere to add database management features to C or C++ applications.

 Introduction to the database tools interface

 Using the database tools interface

 DBTools functions

 DBTools structures

 DBTools enumeration types


Introduction to the database tools interface
  ASA Programming Interfaces Guide

    CHAPTER 3.  The Database Tools Interface

 Introduction to the database tools interface

Sybase Adaptive Server Anywhere includes Sybase Central and a set of command-line utilities for managing databases. These database management utilities carry out tasks such as backing up databases, creating databases, translating transaction logs to SQL, and so on.

Supported platforms

All the database management utilities use a shared library called the database tools library. It is supplied for each of the Windows 95/98 and Windows NT operating systems. The name of this library is dbtool7.dll:

You can develop your own database management utilities, or incorporate database management features into your applications, by calling the database tools library. This chapter describes the interface to the database tools library. In this chapter, we assume you are familiar with how to call DLLs from the development environment you are using.

The database tools library has functions, or entry points, for each of the database management utilities. In addition, functions must be called before use of other database tools functions and when you have finished using other database tools functions.

The DBTOOLS.H header file

The DBTOOLS header file included with Adaptive Server Anywhere lists the entry points to the DBTOOLS library and also the structures used to pass information to and from the library. The dbtools.h file is installed into the h subdirectory under your installation directory. You should consult the dbtools.h file for the latest information about the entry points and structure members.

The DBTOOLS.H header file includes two other files:

•	sqlca.h    This is included for resolution of various macros, not for the SQLCA itself.

•	dllapi.h    Defines preprocessor macros for operating-system dependent and language-dependent macros.

Also, the sqldef.h header file includes error return values.


Using the database tools interface
  ASA Programming Interfaces Guide

    CHAPTER 3.  The Database Tools Interface

 Using the database tools interface

This section provides an overview of how to develop applications that use the DBTools interface for managing databases.

 Using the import libraries

 Starting and finishing the DBTools library

 Calling the DBTools functions

 Software component return codes

 Using callback functions

 Version numbers and compatibility

 Using bit fields

 A DBTools example


Using the import libraries
  ASA Programming Interfaces Guide

    CHAPTER 3.  The Database Tools Interface

      Using the database tools interface

 Using the import libraries

In order to use the DBTools functions, you must link your application against a DBTools import library which contains the required function definitions.

Supported platforms

Import libraries are operating system-specific and can be compiler-specific. Import libraries for the DBTools interface are provided with Adaptive Server Anywhere, and can be found in the lib subdirectory of each operating system directory, under your installation directory. The provided DBTools import libraries are as follows:

Operating system	Compiler	Library
Windows NT/95	Watcom	win32\dbtlstw.lib
Windows NT/95	Microsoft	win32\dbtlstM.lib
Windows NT/95	Borland	win32\dbtlstB.lib


Starting and finishing the DBTools library
  ASA Programming Interfaces Guide

    CHAPTER 3.  The Database Tools Interface

      Using the database tools interface

 Starting and finishing the DBTools library

Before using any other DBTools functions, you must call DBToolsInit. When you are finished using the DBTools DLL, you must call DBToolsFini.

The primary purpose of the DBToolsInit and DBToolsFini functions is to allow the DBTools dll to load the Adaptive Server Anywhere language DLL. The language DLL contains localized versions of all error messages and prompts that DBTools uses internally. If DBToolsFini is not called, the reference count of the language DLL is not decremented, and it will not be unloaded, so be careful to ensure there is a matched pair of DBToolsInit/DBToolsFini calls.

The following code fragment illustrates how to initialize and clean up DBTools:

// Declarations

a_dbtools_info	info;

short	ret;

//Initialize the a_dbtools_info structure

memset( &info, 0, sizeof( a_dbtools_info) );

info.errorrtn = (MSG_CALLBACK)MyErrorRtn;

// initialize DBTools

ret = DBToolsInit( &info );

if( ret != EXIT_OKAY ) {

// DLL initialization failed

...

}

// call some DBTools routines . . .

...

// cleanup the DBTools dll

DBToolsFini( &info );


Calling the DBTools functions
  ASA Programming Interfaces Guide

    CHAPTER 3.  The Database Tools Interface

      Using the database tools interface

 Calling the DBTools functions

All the tools are run by first filling out a structure, and then calling a function (or entry point) in the DBTools DLL. Each entry point takes a pointer to a single structure as argument.

The following example shows how to use the DBBackup function. The example is for either Windows 95 or Windows NT operating systems.

// Initialize the structure

a_backup_db backup_info;

memset( &backup_info, 0, sizeof( backup_info ) );

// Fill out the structure

backup_info.version = DB_TOOLS_VERSION_NUMBER;

backup_info.output_dir = "C:\BACKUP";

backup_info.connectparms ="uid=dba;pwd=sql;dbf=asademo.db";

backup_info.startline = "dbeng7.EXE";

backup_info.confirmrtn = (MSG_CALLBACK) ConfirmRtn ;

backup_info.errorrtn = (MSG_CALLBACK) ErrorRtn ;

backup_info.msgrtn = (MSG_CALLBACK) MessageRtn ;

backup_info.statusrtn = (MSG_CALLBACK) StatusRtn ;

backup_info.backup_database = TRUE;

// start the backup

DBBackup( &backup_info );

  For information about the members of the DBTools structures, see DBTools structures.


Software component return codes
  ASA Programming Interfaces Guide

    CHAPTER 3.  The Database Tools Interface

      Using the database tools interface

 Software component return codes

All database tools are provided as entry points in a dll. These entry points use the following return codes:

Code	Explanation
0	Success
1	General failure
2	Invalid file format
3	File not found, unable to open
4	Out of memory
5	Terminated by the user
6	Failed communications
7	Missing a required database name
8	Client/server protocol mismatch
9	Unable to connect to the database server
10	Database server not running
11	Database server not found
254	Reached stop time
255	Invalid parameters on the command line


Using callback functions
  ASA Programming Interfaces Guide

    CHAPTER 3.  The Database Tools Interface

      Using the database tools interface

 Using callback functions

Several elements in DBTools structures are of type MSG_CALLBACK. These are pointers to callback functions.

Uses of callback functions

Callback functions allow DBTools functions to return control of operation to the user's calling application. The DBTools library uses callback functions to handle messages sent to the user by the DBTools functions, for four purposes:

•	Confirmation     Called when an action needs to be confirmed by the user. For example, if the backup directory does not exist, the tools DLL asks if it needs to be created.

•	Error message    Called to handle a message when an error occurs, such as when an operation is out of disk space.

•	Information message     Called for the tools to display some message to the user (such as the name of the current table being backed up).

•	Status information    Called for the tools to display the status of an operation (such as the percentage done when unloading a table).

Assigning a callback function to a structure

You can directly assign a callback routine to the structure. The following statement is an example using a backup structure:

backup_info.errorrtn = (MSG_CALLBACK) MyFunction

MSG_CALLBACK is defined in the dllapi.h header file supplied with Adaptive Server Anywhere. Tools routines can call back to the Calling application with messages that should be displayed in the appropriate user interface, whether that be a windowing environment, standard output on a character-based systems, or other user interface.

Confirmation callback function example

The following example confirmation routine asks the user to answer YES or NO to a prompt, and returns the user's selection:

extern short _callback ConfirmRtn(

char far * question )

{

int ret;

if( question != NULL ) {

ret = MessageBox( HwndParent, question,

"Confirm", MB_ICONEXCLAMTION|MB_YESNO );

}

return( 0 );

}

Error callback function example

The following is an example of an error message handling routine, which displays the error message in a message box.

extern short _callback ErrorRtn(

char far * errorstr )

{

if( errorstr != NULL ) {

ret = MessageBox( HwndParent, errorstr,

"Backup Error", MB_ICONSTOP|MB_OK );

}

return( 0 );

}

Message callback function example

A common implementation of a message callback function outputs the message to the screen:

extern short _callback MessageRtn(

char far * errorstr )

{

if( messagestr != NULL ) {

OutputMessageToWindow( messagestr );

}

return( 0 );

}

Status callback function example

A status callback routine is called when the tools needs to display the status of an operation (like the percentage done unloading a table). Again, a common implementation would just output the message to the screen:

extern short _callback StatusRtn(

char far * statusstr )

{

if( statusstr == NULL ) {

return FALSE;

}

OutputMessageToWindow( statustr );

return TRUE;

}


Version numbers and compatibility
  ASA Programming Interfaces Guide

    CHAPTER 3.  The Database Tools Interface

      Using the database tools interface

 Version numbers and compatibility

Each structure has a member that indicates the version number. You should use this version member to hold the version of the DBTools library that your application was developed against. The current version of the DBTools  library is included as the constant in the dbtools.h header file.

♦ To assign the current version number to a structure:

•	Assign the version constant to the version member of the structure before calling the DBTools function. The following line assigns the current version to a backup structure:

backup_info.version = DB_TOOLS_VERSION_NUMBER;

Compatibility

The version number allows your application to continue working against newer versions of the DBTools library. The DBTools functions use the version number supplied by your application to allow the application to work, even if new members have been added to the DBTools structure.

Applications will not work against older versions of the DBTools library.


Using bit fields
  ASA Programming Interfaces Guide

    CHAPTER 3.  The Database Tools Interface

      Using the database tools interface

 Using bit fields

Many of the DBTools structures use bit fields to hold Boolean information in a compact manner. For example, the backup structure has the following bit fields:

char	backup_database	: 1;

char	backup_logfile	: 1;

char	backup_writefile: 1;

char	no_confirm	: 1;

char	quiet	: 1;

char	rename_log	: 1;

char	truncate_log : 1;

char	rename_local_log: 1;

Each bit field is one bit long, indicated by the 1 to the right of the colon in the structure declaration.

You assign an integer value of 0 or 1 to a bit field to pass Boolean information to the structure.


A DBTools example
  ASA Programming Interfaces Guide

    CHAPTER 3.  The Database Tools Interface

      Using the database tools interface

 A DBTools example

The following program illustrates the use of the DBTools library in the context of a backup program:

# define WINNT

#include <stdio.h>

#include "windows.h"

#include "string.h"

#include "dbtools.h"

extern short _callback ConfirmCallBack(char far * str){

if( MessageBox( NULL, str, "Backup",

 MB_YESNO|MB_ICONQUESTION ) == IDYES ) {

return 1;

}

return 0;

}

extern short _callback MessageCallBack( char far * str){

if( str != NULL ) {

fprintf( stdout, "%s", str );

fprintf( stdout, "\n" );

fflush( stdout );

}

return 0;

}

extern short _callback StatusCallBack( char far * str ){

if( str != NULL ) {

fprintf( stdout, "%s", str );

fprintf( stdout, "\n" );

fflush( stdout );

}

return 0;

}

extern short _callback ErrorCallBack( char far * str ){

if( str != NULL ) {

fprintf( stdout, "%s", str );

fprintf( stdout, "\n" );

fflush( stdout );

}

return 0;

}

// Main entry point into the program.

int main( int argc, char * argv[] ){

a_backup_db	backup_info;

a_dbtools_info	dbtinfo;

char	dir_name[ _MAX_PATH + 1];

char	connect[ 256 ];

HINSTANCE	hinst;

FARPROC	dbbackup;

FARPROC	dbtoolsinit;

FARPROC	dbtoolsfini;

// Always initialize to 0 so new versions

//of the structure will be compatible.

memset( &backup_info, 0, sizeof( a_backup_db ) );

backup_info.version = DB_TOOLS_VERSION_6_0_01;

backup_info.quiet = 0;

backup_info.no_confirm = 0;

backup_info.confirmrtn =

                        (MSG_CALLBACK)ConfirmCallBack;

backup_info.errorrtn = (MSG_CALLBACK)ErrorCallBack;

backup_info.msgrtn = (MSG_CALLBACK)MessageCallBack;

backup_info.statusrtn = (MSG_CALLBACK)StatusCallBack;

if( argc > 1 ) {

strncpy( dir_name, argv[1], _MAX_PATH );

} else {

// DBTools does not expect (or like) the

// trailing slash

strcpy( dir_name, "c:\\temp" );

}

backup_info.output_dir = dir_name;

if( argc > 2 ) {

strncpy( connect, argv[2], 255 );

} else {

// Assume that the engine is already running.

strcpy( connect, "UID=dba;PWD=sql;DBN=asademo" );

}

backup_info.connectparms = connect;

backup_info.startline = "";

backup_info.quiet = 0;

backup_info.no_confirm = 0;

backup_info.backup_database = 1;

backup_info.backup_logfile = 1;

backup_info.backup_writefile = 1;

backup_info.rename_log = 0;

backup_info.truncate_log = 0;

hinst = LoadLibrary( "dbtool6.dll" );

if( hinst == NULL ) {

// Failed

return 0;

}

dbtinfo.errorrtn = (MSG_CALLBACK)ErrorCallBack;

dbbackup = GetProcAddress( (HMODULE)hinst,

"_DBBackup@4" );

dbtoolsinit = GetProcAddress( (HMODULE)hinst,

"_DBToolsInit@4" );

dbtoolsfini = GetProcAddress( (HMODULE)hinst,

"_DBToolsFini@4" );

(*dbtoolsinit)( &dbtinfo );

(*dbbackup)( &backup_info );

(*dbtoolsfini)( &dbtinfo );

FreeLibrary( hinst );

return 0;

}


DBTools functions
  ASA Programming Interfaces Guide

    CHAPTER 3.  The Database Tools Interface

 DBTools functions

This section describes the functions available in the DBTools library. The functions are listed alphabetically.

 DBBackup function

 DBChangeLogName function

 DBChangeWriteFile function

 DBCollate function

 DBCompress function

 DBCreate function

 DBCreateWriteFile function

 DBErase function

 DBExpand function

 DBInfo function

 DBInfoDump function

 DBInfoFree function

 DBStatusWriteFile function

 DBSynchronizeLog function

 DBToolsFini function

 DBToolsInit function

 DBToolsVersion function

 DBTranslateLog function

 DBTruncateLog function

 DBUnload function

 DBUpgrade function

 DBValidate function


DBBackup function
  ASA Programming Interfaces Guide

    CHAPTER 3.  The Database Tools Interface

      DBTools functions

 DBBackup function

Function

Database backup function. This function is used by the DBBACKUP command-line utility.

Prototype

short DBBackup ( const backup_db * );

Parameters

Parameter	Description
backup_db	Pointer to a_backup_db structure
Return value

A return code, as listed in Software component return codes.

Usage

The DBBackup function manages all database backup tasks. For descriptions of these tasks, see The Backup utility.

See Also

a_backup_db structure


DBChangeLogName function
  ASA Programming Interfaces Guide

    CHAPTER 3.  The Database Tools Interface

      DBTools functions

 DBChangeLogName function

Function

Changes the name of the transaction log file. This function is used by the dblog command-line utility.

Prototype

short DBChangeLogName ( const change_log  * );

Parameters

Parameter	Description
change_log	Pointer to a_change_log structure
Return value

A return code, as listed in Software component return codes.

Usage

The -t option of the dblog command line utility changes the name of the transaction log. DBChangeLogName provides a programmatic interface to this function.

For descriptions of the dblog utility, see The Transaction Log utility.

See Also

a_change_log structure


DBChangeWriteFile function
  ASA Programming Interfaces Guide

    CHAPTER 3.  The Database Tools Interface

      DBTools functions

 DBChangeWriteFile function

Function

Changes a write file to refer to another database file. This function is used by the dbwrite command-line utility when the -d option is applied.

Prototype

short DBChangeWriteFile ( const writefile  * );

Parameters

Parameter	Description
writefile	Pointer to a_writefile structure
Return value

A return code, as listed in Software component return codes.

Usage

For information about the Write File utility and its features, see The Write File utility.

See Also

DBCreateWriteFile function

DBStatusWriteFile function

a_writefile structure


DBCollate function
  ASA Programming Interfaces Guide

    CHAPTER 3.  The Database Tools Interface

      DBTools functions

 DBCollate function

Function

Extracts a collation sequence from a database.

Prototype

short DBCollate ( const db_collation  * );

Parameters

Parameter	Description
db_collation	Pointer to a_db_collation structure
Return value

A return code, as listed in Software component return codes.

Usage

For information about the collation utility and its features, see The Collation utility

See Also

a_db_collation structure


DBCompress function
  ASA Programming Interfaces Guide

    CHAPTER 3.  The Database Tools Interface

      DBTools functions

 DBCompress function

Function

Compresses a database file. This function is used by the dbshrink command-line utility.

Prototype

short DBCompress ( const compress_db  * );

Parameters

Parameter	Description
compress_db	Pointer to a_compress_db structure
Return value

A return code, as listed in Software component return codes.

Usage

For information about the Compression utility and its features, see The Compression utility.

See Also

a_compress_db structure


DBCreate function
  ASA Programming Interfaces Guide

    CHAPTER 3.  The Database Tools Interface

      DBTools functions

 DBCreate function

Function

Creates a database. This function is used by the dbinit command-line utility.

Prototype

short DBCreate ( const create_db * );

Parameters

Parameter	Description
create_db	Pointer to a_create_db structure
Return value

A return code, as listed in Software component return codes.

Usage

For information about the initialization utility, see The Initialization utility.

See Also

a_create_db structure


DBCreateWriteFile function
  ASA Programming Interfaces Guide

    CHAPTER 3.  The Database Tools Interface

      DBTools functions

 DBCreateWriteFile function

Function

Creates a write file. This function is used by the dbwrite command-line utility when the -c option is applied.

Prototype

short DBCreateWriteFile ( const writefile  * );

Parameters

Parameter	Description
writefile	Pointer to a_writefile structure
Return value

A return code, as listed in Software component return codes.

Usage

For information about the Write File utility and its features, see The Write File utility.

See Also

DBChangeWriteFile function

DBStatusWriteFile function

a_writefile structure


DBErase function
  ASA Programming Interfaces Guide

    CHAPTER 3.  The Database Tools Interface

      DBTools functions

 DBErase function

Function

Erases a database file and/or transaction log file. This function is used by the dberase command-line utility.

Prototype

short DBErase ( const erase_db * );

Parameters

Parameter	Description
erase_db	Pointer to an_erase_db structure
Return value

A return code, as listed in Software component return codes.

Usage

For information about the Erase utility and its features, see The Erase utility.

See Also

an_erase_db structure


DBExpand function
  ASA Programming Interfaces Guide

    CHAPTER 3.  The Database Tools Interface

      DBTools functions

 DBExpand function

Function

Uncompresses a database file. This function is used by the dbexpand command-line utility.

Prototype

short DBExpand ( const expand_db * );

Parameters

Parameter	Description
&an_expand_db	Pointer to an_expand_db structure
Return value

A return code, as listed in Software component return codes.

Usage

For information about the Uncompression utility and its features, see The Uncompression utility.

See Also

an_expand_db structure


DBInfo function
  ASA Programming Interfaces Guide

    CHAPTER 3.  The Database Tools Interface

      DBTools functions

 DBInfo function

Function

Returns information about a database file. This function is used by the dbinfo command-line utility.

Prototype

short DBInfo ( const db_info * );

Parameters

Parameter	Description
db_info	Pointer to a_db_info structure
Return value

A return code, as listed in Software component return codes.

Usage

For information about the Information utility and its features, see The Information utility.

See Also

DBInfoDump function

DBInfoFree function

a_db_info structure


DBInfoDump function
  ASA Programming Interfaces Guide

    CHAPTER 3.  The Database Tools Interface

      DBTools functions

 DBInfoDump function

Function

Returns information about a database file. This function is used by the dbinfo command-line utility when the -u option is used.

Prototype

short DBInfoDump ( const db_info * );

Parameters

Parameter	Description
db_info	Pointer to a_db_info structure
Return value

A return code, as listed in Software component return codes.

Usage

For information about the Information utility and its features, see The Information utility.

See Also

DBInfo function

DBInfoFree function

a_db_info structure


DBInfoFree function
  ASA Programming Interfaces Guide

    CHAPTER 3.  The Database Tools Interface

      DBTools functions

 DBInfoFree function

Function

Called to free resources after the DBInfoDump function is called.

Prototype

short DBInfoFree ( const db_info * );

Parameters

Parameter	Description
db_info	Pointer to a_db_info structure
Return value

A return code, as listed in Software component return codes.

Usage

For information about the Information utility and its features, see The Information utility.

See Also

DBInfo function

DBInfoDump function

a_db_info structure


DBStatusWriteFile function
  ASA Programming Interfaces Guide

    CHAPTER 3.  The Database Tools Interface

      DBTools functions

 DBStatusWriteFile function

Function

Gets the status of a write file. This function is used by the dbwrite command-line utility when the -s option is applied.

Prototype

short DBStatusWriteFile ( const writefile  * );

Parameters

Parameter	Description
writefile	Pointer to a_writefile structure
Return value

A return code, as listed in Software component return codes.

Usage

For information about the Write File utility and its features, see The Write File utility.

See Also

DBChangeWriteFile function

DBCreateWriteFile function

a_writefile structure


DBSynchronizeLog function
  ASA Programming Interfaces Guide

    CHAPTER 3.  The Database Tools Interface

      DBTools functions

 DBSynchronizeLog function

Function

Synchronize a database with a MobiLink synchronization server.

Prototype

short DBSynchronizeLog( const a_sync_db * );

Parameters

Parameter	Description
a_sync_db	Pointer to a_sync_db structure
Return value

A return code, as listed in Software component return codes.

Usage

For information about the features you can access, see Initiating synchronization.


DBToolsFini function
  ASA Programming Interfaces Guide

    CHAPTER 3.  The Database Tools Interface

      DBTools functions

 DBToolsFini function

Function

Decrements the counter and frees resources when an application is finished with the DBTools library.

Prototype

short DBToolsFini ( const dbtools_info * );

Parameters

Parameter	Description
dbtools_info	Pointer to a_dbtools_info structure
Return value

A return code, as listed in Software component return codes.

Usage

The DBToolsFini function must be called at the end of any application that uses the DBTools interface. Failure to do so can lead to lost memory resources.

See Also

DBToolsInit function

a_dbtools_info structure


DBToolsInit function
  ASA Programming Interfaces Guide

    CHAPTER 3.  The Database Tools Interface

      DBTools functions

 DBToolsInit function

Function

Prepares the DBTools library for use.

Prototype

short DBToolsInit t( const dbtools_info * );

Parameters

Parameter	Description
dbtools_info	Pointer to a_dbtools_info structure
Return value

A return code, as listed in Software component return codes.

Usage

The primary purpose of the DBToolsInit function is to load the Adaptive Server Anywhere language DLL. The language DLL contains localized versions of error messages and prompts that DBTools uses internally.

The DBToolsInit function must be called at the start of any application that uses the DBTools interface, before any other DBTools functions.

Example

•	The following code sample illustrates how to initialize and clean up DBTools:

a_dbtools_info	info;

short	ret;

memset( &info, 0, sizeof( a_dbtools_info) );

info.errorrtn = (MSG_CALLBACK)MakeProcInstance( 	(FARPROC)MyErrorRtn, hInst );

// initialize DBTools

ret = DBToolsInit( &info );

if( ret != EXIT_OKAY ) {

// DLL initialization failed

...

}

// call some DBTools routines . . .

...

// cleanup the DBTools dll

DBToolsFini( &info );

See Also

DBToolsFini function

a_dbtools_info structure


DBToolsVersion function
  ASA Programming Interfaces Guide

    CHAPTER 3.  The Database Tools Interface

      DBTools functions

 DBToolsVersion function

Function

Returns the version number of the DBTools library.

Prototype

short DBToolsVersion ( void );

Return value

A short integer indicating the version number of the DBTools library.

Usage

Use the DBToolsVersion function to check that the DBTools library is not older than one against which your application is developed. While applications can run against newer versions of DBTools, they cannot run against older versions.

See Also

Version numbers and compatibility


DBTranslateLog function
  ASA Programming Interfaces Guide

    CHAPTER 3.  The Database Tools Interface

      DBTools functions

 DBTranslateLog function

Function

Translates a transaction log file to SQL. This function is used by the dbtran command-line utility.

Prototype

short DBTranslateLog ( const translate_log * );

Parameters

Parameter	Description
translate_log	Pointer to a_translate_log structure
Return value

A return code, as listed in Software component return codes.

Usage

For information about the log translation utility, see The Log Translation utility.

See Also

a_translate_log structure


DBTruncateLog function
  ASA Programming Interfaces Guide

    CHAPTER 3.  The Database Tools Interface

      DBTools functions

 DBTruncateLog function

Function

Truncates a transaction log file. This function is used by the dbbackup command-line utility.

Prototype

short DBTruncateLog ( const truncate_log  * );

Parameters

Parameter	Description
truncate_log	Pointer to a_truncate_log structure
Return value

A return code, as listed in Software component return codes.

Usage

For information about the backup utility, see The Backup utility

See Also

a_truncate_log structure


DBUnload function
  ASA Programming Interfaces Guide

    CHAPTER 3.  The Database Tools Interface

      DBTools functions

 DBUnload function

Function

Unloads a database. This function is used by the dbunload command-line utility and also by the dbxtract utility for SQL Remote.

Prototype

short DBUnload ( const unload_db  * );

Parameters

Parameter	Description
an_unload_db	Pointer to an_unload_db structure
Return value

A return code, as listed in Software component return codes.

Usage

For information about the Unload utility, see The Unload utility.

See Also

an_unload_db structure


DBUpgrade function
  ASA Programming Interfaces Guide

    CHAPTER 3.  The Database Tools Interface

      DBTools functions

 DBUpgrade function

Function

Upgrades a database file. This function is used by the dbupgrade command-line utility.

Prototype

short DBUpgrade ( const upgrade_db  * );

Parameters

Parameter	Description
upgrade_db	Pointer to an_upgrade_db structure
Return value

A return code, as listed in Software component return codes.

Usage

For information about the upgrade utility, see The Upgrade utility.

See Also

an_upgrade_db structure


DBValidate function
  ASA Programming Interfaces Guide

    CHAPTER 3.  The Database Tools Interface

      DBTools functions

 DBValidate function

Function

Validates all or part of a database. This function is used by the dbvalid command-line utility.

Prototype

short DBValidate ( const validate_db  * );

Parameters

Parameter	Description
validate_db	Pointer to a_validate_db structure
Return value

A return code, as listed in Software component return codes.

Usage

For information about the upgrade utility, see The Validation utility.

See Also

a_validate_db structure


DBTools structures
  ASA Programming Interfaces Guide

    CHAPTER 3.  The Database Tools Interface

 DBTools structures

This section lists the structures that are used to exchange information with the DBTools library. The structures are listed alphabetically.

Many of the structure elements correspond to command-line switches on the corresponding utility. For example, several structures have a member named quiet, which can take on values of 0 or 1. This member corresponds to the quiet operation (-q) command-line option used by many of the utilities.

 a_backup_db structure

 a_change_log structure

 a_compress_db structure

 a_compress_stats structure

 a_create_db structure

 a_db_collation structure

 a_db_info structure

 a_dbtools_info structure

 an_erase_db structure

 an_expand_db structure

 a_name structure

 a_stats_line structure

 a_sync_db structure

 a_sysinfo structure

 a_table_info structure

 a_translate_log structure

 a_truncate_log structure

 an_unload_db structure

 an_upgrade_db structure

 a_validate_db structure

 a_writefile structure


a_backup_db structure
  ASA Programming Interfaces Guide

    CHAPTER 3.  The Database Tools Interface

      DBTools structures

 a_backup_db structure

Function

Holds the information needed to carry out backup tasks using the DBTools library.

Syntax

typedef struct a_backup_db {

unsigned short	version;

const char *	output_dir;

const char *	connectparms;

const char * 	startline;

MSG_CALLBACK	confirmrtn;

MSG_CALLBACK	errorrtn;

MSG_CALLBACK	msgrtn;

MSG_CALLBACK	statusrtn;

char	backup_database	: 1;

char	backup_logfile	: 1;

char	backup_writefile: 1;

char	no_confirm	: 1;

char	quiet	: 1;

char	rename_log	: 1;

char	truncate_log	: 1;

char	rename_local_log: 1;

const char *	hotlog_filename;

} a_backup_db;

Parameters

Member	Description
version	DBTools version number.
output_dir	Path to the output directory. For example: "c:\backup"
connectparms	Parameters needed to connect to the database. They take the form of connection strings, such as the following: "UID=DBA;PWD=SQL;DBF=c:\asa6\asademo.db"   For the full range of connection string options, see Connection parameters
startline	Command line used to start the database engine. The following is an example start line: "c:\asa6\win32\dbeng7.exe" The default start line is used if this member is NULL.
confirmrtn	Callback routine for confirming an action
errorrtn	Callback routine for handling an error message
msgrtn	Callback routine for handling an information message
statusrtn	Callback routine for handling a status message
backup_database	Backup the database file (1) or not (0)
backup_logfile	Backup the transaction log file (1) or not (0)
backup_writefile	Backup the database write file (1) or not (0), if a write file is being used
no_confirm	Operate with (0) or without (1) confirmation
quiet	Operate without printing messages (1), or print messages (0)
rename_log	rename the transaction log
truncate_log	delete the transaction log
rename_local_log	rename the local backup of the transaction log
hotlog_filename	file name for the live backup file
See Also

DBBackup function

  For more information on callback functions, see Using callback functions.


a_change_log structure
  ASA Programming Interfaces Guide

    CHAPTER 3.  The Database Tools Interface

      DBTools structures

 a_change_log structure

Function

Holds the information needed to carry out dblog tasks using the DBTools library.

Syntax

typedef struct a_change_log {

unsigned short	version;

const char *	dbname;

const char *	logname;

MSG_CALLBACK	errorrtn;

MSG_CALLBACK	msgrtn;

char	query_only	: 1;

char	quiet	: 1;

char	mirrorname_present	: 1;

char	change_mirrorname	: 1;

char	change_logname	: 1;

char	ignore_ltm_trunc	: 1;

char	ignore_remote_trunc	: 1;

char	set_generation_number	: 1;

const char *	mirrorname;

unsigned short	generation_number;

} a_change_log;

Parameters

Member	Description
version	DBTools version number
dbname	Database file name
logname	The name of the transaction log. If set to NULL, there is no log
errorrtn	Callback routine for handling an error message
msgrtn	Callback routine for handling an information message
query_only	If 1, just display the name of the transaction log. If 0, permit changing of the log name
quiet	Operate without printing messages (1), or print messages (0)
mirrorname_present	Set to 1. Indicates that the version of DBTools is recent enough to support the mirrorname field
change_mirrorname	If 1, permit changing of the log mirror name
change_logname	If 1, permit changing of the transaction log name
ignore_ltm_trunc	When using the Log Transfer Manager, performs the same function as the dbcc settrunc( 'ltm', 'gen_id', n ) Replication Server function: For information on dbcc, see your Replication Server documentation
ignore_remote_trunc	For SQL Remote. Resets the offset kept for the purposes of the DELETE_OLD_LOGS option, allowing transaction logs to be deleted when they are no longer needed
set_generation_number	When using the Log Transfer Manager, used after a backup is restored to set the generation number
mirrorname	The new name of the transaction log mirror file
generation_number	The new generation number. Used together with set_generation_number
See Also

DBChangeLogName function

  For more information on callback functions, see Using callback functions.


a_compress_db structure
  ASA Programming Interfaces Guide

    CHAPTER 3.  The Database Tools Interface

      DBTools structures

 a_compress_db structure

Function

Holds the information needed to carry out database compression tasks using the DBTools library.

Syntax

typedef struct a_compress_db {

unsigned short	version;

const char *	dbname;

const char *	compress_name;

MSG_CALLBACK	errorrtn;

MSG_CALLBACK	msgrtn;

MSG_CALLBACK	statusrtn;

char	display_free_pages	: 1;

char	quiet	: 1;

char	record_unchanged	: 1;

a_compress_stats * stats;

MSG_CALLBACK	confirmrtn;

char	noconfirm	: 1;

} a_compress_db;

Parameters

Member	Description
version	DBTools version number.
dbname	The file name of the database to compress.
compress_name	The file name of the compressed database
errorrtn	Callback routine for handling an error message.
msgrtn	Callback routine for handling an information message.
statusrtn	Callback routine for handling a status message.
display_free_pages	Show the free page information.
quiet	Operate without printing messages (1), or print messages (0).
record_unchanged	Set to 1. Indicates that the a_compress_stats structure is recent enough to have an unchanged member.
a_compress_stats	Pointer to a structure of type a_compress_stats. This is filled in if the member is not NULL and display_free_pages is not zero.
confirmrtn	Callback routine for confirming an action.
noconfirm	Operate with (0) or without (1) confirmation.
See Also

DBCompress function

a_compress_stats structure

  For more information on callback functions, see Using callback functions.


a_compress_stats structure
  ASA Programming Interfaces Guide

    CHAPTER 3.  The Database Tools Interface

      DBTools structures

 a_compress_stats structure

Function

Holds information describing compressed database file statistics.

Syntax

typedef struct a_compress_stats {

a_stats_line	tables;

a_stats_line	indices;

a_stats_line	other;

a_stats_line	free;

a_stats_line	total;

long	free_pages;

long	unchanged;

} a_compress_stats;

Parameters

Member	Description
tables	Holds compression information regarding tables.
indices	Holds compression information regarding indexes
other	Holds other compression information
free	Holds information regarding free space.
total	Holds overall compression information.
free_pages	Holds information regarding free pages.
unchanged	The number of pages that the compression algorithm was unable to shrink.
See Also

DBCompress function

a_compress_db structure


a_create_db structure
  ASA Programming Interfaces Guide

    CHAPTER 3.  The Database Tools Interface

      DBTools structures

 a_create_db structure

Function

Holds the information needed to create a database using the DBTools library.

Syntax

typedef struct a_create_db {

unsigned short	version;

const char *	dbname;

const char *	logname;

const char *	startline;

short	page_size;

const char *	default_collation;

MSG_CALLBACK	errorrtn;

MSG_CALLBACK	msgrtn;

short	database_version;

char	verbose;

char	blank_pad	: 2;

char	respect_case	: 1;

char	encrypt	: 1;

char	debug	: 1;

char	dbo_avail	: 1;

char	mirrorname_present	: 1;

char	avoid_view_collisions	: 1;

short	collation_id;

const char *	dbo_username;

const char *	mirrorname;

} a_create_db;

Parameters

Member	Description
version	DBTools version number.
dbname	Database file name
logname	New transaction log name
startline	The command line used to start the database engine. The following is an example start line: "c:\asa6\win32\dbeng7.exe" The default start line is used if this member is NULL.
page_size	The page size of the database.
default_collation	The collation for the database.
errorrtn	Callback routine for handling an error message.
msgrtn	Callback routine for handling an information message.
database_version	The version number of the database.
verbose	Run in verbose mode.
blank_pad	Treat blanks as significant in string comparisons, and hold index information to reflect this.
respect_case	Make string comparisons case sensitive, and hold index information to reflect this.
encrypt	Encrypt the database.
debug	Reserved.
dbo_avail	Set to 1. The dbo user is available in this database.
mirrorname_present	Set to 1. Indicates that the version of DBTools is recent enough to support the mirrorname field.
avoid_view_collisions	Omit the generation of Watcom SQL compatibility views SYS.SYSCOLUMNS and SYS.SYSINDEXES.
collation_id	Collation identifier.
dbo_username	User name for the "dbo" user.
mirrorname	Transaction log mirror name.
See Also

DBCreate function

  For more information on callback functions, see Using callback functions.


a_db_collation structure
  ASA Programming Interfaces Guide

    CHAPTER 3.  The Database Tools Interface

      DBTools structures

 a_db_collation structure

Function

Holds the information needed to extract a collation sequence from a database using the DBTools library.

Syntax

typedef struct a_db_collation {

unsigned short	version;

const char *	connectparms;

const char *	startline;

const char *	collation_label;

const char *	filename;

MSG_CALLBACK	confirmrtn;

MSG_CALLBACK	errorrtn;

MSG_CALLBACK	msgrtn;

char	include_empty	: 1;

char	hex_for_extended	: 1;

char	replace	: 1;

char	quiet	: 1;

} a_db_collation;

Parameters

Member	Description
version	DBTools version number.
connectparms	The parameters needed to connect to the database. They take the form of connection strings, such as the following: "UID=DBA;PWD=SQL;DBF=c:\asa6\asademo.db"    For the full range of connection string options, see Connection parameters
startline	The command line used to start the database engine. The following is an example start line: "c:\asa6\win32\dbeng7.exe" The default start line is used if this member is NULL.
confirmrtn	Callback routine for confirming an action.
errorrtn	Callback routine for handling an error message.
msgrtn	Callback routine for handling an information message.
include_empty	Write empty mappings for gaps in the collations sequence.
hex_for_extended	Use two-digit hexadecimal numbers to represent high-value characters.
replace	Operate without confirming actions.
quiet	Operate without messages.
See Also

DBCollate function

  For more information on callback functions, see Using callback functions.


a_db_info structure
  ASA Programming Interfaces Guide

    CHAPTER 3.  The Database Tools Interface

      DBTools structures

 a_db_info structure

Function

Holds the information needed to return dbinfo information using the DBTools library.

Syntax

typedef struct a_db_info {

unsigned short  	version;

MSG_CALLBACK	errorrtn;

const char *	dbname;

unsigned short  	dbbufsize;

char *	dbnamebuffer;

unsigned short  	logbufsize;

char *	lognamebuffer;

unsigned short  	wrtbufsize;

char *	wrtnamebuffer;

char	quiet : 1;

char	mirrorname_present : 1;

a_sysinfo	sysinfo;

unsigned long   	free_pages;

unsigned char   	compressed : 1;

const char *	connectparms;

const char *	startline;

MSG_CALLBACK	msgrtn;

MSG_CALLBACK	statusrtn;

unsigned char   	page_usage : 1;

a_table_info * 	totals;

unsigned long   	file_size;

unsigned long   	unused_pages;

unsigned long   	other_pages;

unsigned short  	mirrorbufsize;

char *	mirrornamebuffer;

} a_db_info;

Parameters

Member	Description
version	DBTools version number.
errortrn	Callback routine for handling an error message.
dbname	Database file name.
dbbufsize	The length of the dbnamebuffer member.
dbnamebuffer	Database file name.
logbufsize	The length of the lognamebuffer member.
lognamebuffer	Transaction log file name.
wrtbufsize	The length of the wrtnamebuffer member.
wrtnamebuffer	The write file name.
quiet	Operate without confirming messages.
mirrorname_present	Set to 1. Indicates that the version of DBTools is recent enough to support the mirrorname field.
sysinfo	Pointer to a_sysinfo structure.
free_pages	Number of free pages.
compressed	1 if compressed, otherwise 0.
connectparms	The parameters needed to connect to the database. They take the form of connection strings, such as the following: "UID=DBA;PWD=SQL;DBF=c:\sa50\asademo.db"    For the full range of connection string options, see Connection parameters.
startline	The command line used to start the database engine. The following is an example start line: "c:\asa6\win32\dbeng7.exe" The default start line is used if this member is NULL.
msgrtn	Callback routine for handling an information message.
statusrtn	Callback routine for handling a status message.
page_usage	1 to report page usage statistics, otherwise 0.
totals	Pointer to a_table_info structure.
file_size	Size of database file.
unused_pages	Number of unused pages.
other_pages	Number of pages that are neither table nor index pages.
mirrorbufsize	The length of the mirrornamebuffer member.
mirrornamebuffer	The transaction log mirror name.
See Also

DBInfo function

  For more information on callback functions, see Using callback functions.


a_dbtools_info structure
  ASA Programming Interfaces Guide

    CHAPTER 3.  The Database Tools Interface

      DBTools structures

 a_dbtools_info structure

Function

Holds the information needed to start and finish working with the DBTools library.

Syntax

typedef struct a_dbtools_info {

MSG_CALLBACK 	errorrtn;

} a_dbtools_info;

Parameters

Member	Description
errorrtn	Callback routine for handling an error message.
See Also

DBToolsFini function

DBToolsInit function

  For more information on callback functions, see Using callback functions.


an_erase_db structure
  ASA Programming Interfaces Guide

    CHAPTER 3.  The Database Tools Interface

      DBTools structures

 an_erase_db structure

Function

Holds information needed to erase a database using the DBTools library.

Syntax

typedef struct an_erase_db {

unsigned short	version;

const char *	dbname;

MSG_CALLBACK	confirmrtn;

MSG_CALLBACK	errorrtn;

MSG_CALLBACK	msgrtn;

char	quiet : 1;

char	erase : 1;

} an_erase_db;

Parameters

Member	Description
version	DBTools version number.
dbname	Database file name to erase.
confirmrtn	Callback routine for confirming an action.
errorrtn	Callback routine for handling an error message.
msgrtn	Callback routine for handling an information message.
quiet	Operate without printing messages (1), or print messages (0).
erase	Erase without confirmation (1) or with confirmation (0).
See Also

DBErase function

  For more information on callback functions, see Using callback functions.


an_expand_db structure
  ASA Programming Interfaces Guide

    CHAPTER 3.  The Database Tools Interface

      DBTools structures

 an_expand_db structure

Function

Holds information needed for database expansion using the DBTools library.

Syntax

typedef struct an_expand_db {

unsigned short	version;

const char *	compress_name;

const char *	dbname;

MSG_CALLBACK	errorrtn;

MSG_CALLBACK	msgrtn;

MSG_CALLBACK	statusrtn;

char	quiet : 1;

MSG_CALLBACK	confirmrtn;

char	noconfirm	: 1;

} an_expand_db;

Parameters

Member	Description
version	DBTools version number.
compress_name	Name of compressed database file
dbname	Database file name
errorrtn	Callback routine for handling an error message.
msgrtn	Callback routine for handling an information message.
statusrtn	Callback routine for handling a status message.
quiet	Operate without printing messages (1), or print messages (0).
confirmrtn	Callback routine for confirming an action.
noconfirm	Operate with (0) or without (1) confirmation.
See Also

DBExpand function

  For more information on callback functions, see Using callback functions.


a_name structure
  ASA Programming Interfaces Guide

    CHAPTER 3.  The Database Tools Interface

      DBTools structures

 a_name structure

Function

Holds a linked list of names. This is used by other structures requiring lists of names.

Syntax

typedef struct a_name {

struct a_name *	next;

char	name[1];

} a_name, * p_name;

Parameters

Member	Description
next	Pointer to the next a_name structure in the list
name	The name.
p_name	Pointer to the previous a_name structure
See Also

a_translate_log structure

a_validate_db structure

an_unload_db structure


a_stats_line structure
  ASA Programming Interfaces Guide

    CHAPTER 3.  The Database Tools Interface

      DBTools structures

 a_stats_line structure

Function

Holds information needed for database compression and expansion using the DBTools library.

Syntax

typedef struct a_stats_line {

long	pages;

long	bytes;

long	compressed_bytes;

} a_stats_line;

Parameters

Member	Description
pages	Number of pages.
bytes	Number of bytes for uncompressed database.
compressed_bytes	Number of bytes for compressed database.
See Also

a_compress_stats structure


a_sync_db structure
  ASA Programming Interfaces Guide

    CHAPTER 3.  The Database Tools Interface

      DBTools structures

 a_sync_db structure

Function

Holds information needed for the dbmlsync utility using the DBTools library.

Syntax

typedef struct a_sync_db {

    unsigned short   version;

    char _fd_ *      connectparms;

    char _fd_ *      publication;

    const char _fd_ *   offline_dir;

    char _fd_ *      extended_options;

    char _fd_ *      script_full_path;

    const char _fd_ *   include_scan_range;

    const char _fd_ *   raw_file;

    MSG_CALLBACK   confirmrtn;

    MSG_CALLBACK   errorrtn;

    MSG_CALLBACK   msgrtn;

    MSG_CALLBACK   logrtn;

    a_sql_uint32   debug_dump_size;

    a_sql_uint32   dl_insert_width;

    a_bit_field      verbose         : 1;

    a_bit_field      debug         : 1;

    a_bit_field      debug_dump_hex      : 1;

    a_bit_field      debug_dump_char      : 1;

    a_bit_field      debug_page_offsets   : 1;

    a_bit_field      use_hex_offsets      : 1;

    a_bit_field      use_relative_offsets   : 1;

    a_bit_field      output_to_file      : 1;

    a_bit_field      output_to_mobile_link   : 1;

    a_bit_field      dl_use_put      : 1;

    a_bit_field      dl_use_upsert      : 1;

    a_bit_field      kill_other_connections   : 1;

    a_bit_field      retry_remote_behind   : 1;

    a_bit_field      ignore_debug_interrupt   : 1;

    SET_WINDOW_TITLE_CALLBACK set_window_title_rtn;

    char *      default_window_title;

    MSG_QUEUE_CALLBACK   msgqueuertn;

    MSG_CALLBACK   progress_msg_rtn;

    SET_PROGRESS_CALLBACK progress_index_rtn;

    char **      argv;

    char **      ce_argv;

    a_bit_field      connectparms_allocated  : 1;

    a_bit_field      entered_dialog      : 1;

    a_bit_field      used_dialog_allocation   : 1;

    a_bit_field      ignore_scheduling   : 1;

    a_bit_field      ignore_hook_errors   : 1;

    a_bit_field      changing_pwd      : 1;

    a_bit_field      prompt_again      : 1;

    a_bit_field      retry_remote_ahead    : 1;

    a_bit_field      rename_log      : 1;

    a_bit_field      hide_conn_str      : 1;

    a_bit_field      hide_ml_pwd      : 1;

    a_bit_field      delay_ml_disconn   : 1;

    a_sql_uint32   dlg_launch_focus;

    char _fd_ *      mlpassword;

    char _fd_ *      new_mlpassword;

    char _fd_ *      verify_mlpassword;

    a_sql_uint32   pub_name_cnt;

    char **      pub_name_list;

    USAGE_CALLBACK   usage_rtn;

} a_sync_db;

Parameters

The parameters correspond to features accessible from the dbmlsync command-line utility. For more information, see Adaptive Server Anywhere client synchronization utility.

See the dbtools.h header file for additional comments.

See also

DBSynchronizeLog function


a_sysinfo structure
  ASA Programming Interfaces Guide

    CHAPTER 3.  The Database Tools Interface

      DBTools structures

 a_sysinfo structure

Function

Holds information needed for dbinfo and dbunload utilities using the DBTools library.

Syntax

typedef struct a_sysinfo {

char	valid_data	: 1;

char	blank_padding	: 1;

char	case_sensitivity	: 1;

char	encryption	: 1;

char	default_collation[11];

unsigned short  page_size;

} a_sysinfo;

Parameters

Member	Description
valid_date	Bit-field indicating whether the following values are set.
blank_padding	1 if blank padding is used in this database, 0 otherwise.
case_sensitivity	1 if the database is case-sensitive, 0 otherwise.
encryption	1 if the database is encrypted, 0 otherwise.
default_collation	The collation sequence for the database.
page_size	The page size for the database.
See Also

a_db_info structure


a_table_info structure
  ASA Programming Interfaces Guide

    CHAPTER 3.  The Database Tools Interface

      DBTools structures

 a_table_info structure

Function

Holds information about a table needed as part of the a_db_info structure.

Syntax

typedef struct a_table_info {

struct a_table_info * next;

unsigned short  table_id;

unsigned long   table_pages;

unsigned long   index_pages;

unsigned long   table_used;

unsigned long   index_used;

char *	  table_name;

} a_table_info;

Parameters

Member	Description
next	Next table in the list
table_id	ID number for this table
table_pages	Number of table pages
index_pages	Number of index pages
table_used	Number of bytes used in table pages
index_used	Number of bytes used in index pages
table_name	Name of the table
See Also

a_db_info structure


a_translate_log structure
  ASA Programming Interfaces Guide

    CHAPTER 3.  The Database Tools Interface

      DBTools structures

 a_translate_log structure

Function

Holds information needed for transaction log translation using the DBTools library.

Syntax

typedef struct a_translate_log {

unsigned short	version;

const char *	logname;

const char *	sqlname;

p_name	userlist;

MSG_CALLBACK	confirmrtn;

MSG_CALLBACK	errorrtn;

MSG_CALLBACK	msgrtn;

char	userlisttype;

char	remove_rollback : 1;

char	ansi_sql	: 1;

char	since_checkpoint: 1;

char	omit_comments   : 1;

char	replace	: 1;

char	debug	: 1;

char	include_trigger_trans : 1;

char	comment_trigger_trans : 1;

unsigned long	since_time;

} a_translate_log;

Parameters

Member	Description
version	DBTools version number.
logname	The name of the transaction log file.
sqlname	The name of the SQL command file.
userlist	Pointer to a linked list of users.
confirmrtn	Callback routine for confirming an action.
errorrtn	Callback routine for handling an error message.
msgrtn	Callback routine for handling an information message.
userlisttype	A member of the dbtran_userlist_type enumeration.
remove_rollback	1 to remove changes that have been rolled back. 0 otherwise.
ansi_sql	If set to 0, generate UPDATE statements with a non-standard FIRST keyword. This is for cases where there is no primary key or unique index on a table, in case of duplicate rows. If set to 1, do not output this keyword.
since_checkpoint	If 1, translate entries since the last checkpoint only.
omit_comments	If 1, omit comments.
replace	Replace files without prompting for confirmation.
debug	Reserved.
include_trigger_trans	If 1, include trigger actions.
comment_trigger_trans	If 1, include trigger actions as comments in the SQL file.
since_time	Translate entries only since the specified time.
See Also

DBTranslateLog function

a_name structure

dbtran_userlist_type enumeration

  For more information on callback functions, see Using callback functions.


a_truncate_log structure
  ASA Programming Interfaces Guide

    CHAPTER 3.  The Database Tools Interface

      DBTools structures

 a_truncate_log structure

Function

Holds information needed for transaction log truncation using the DBTools library.

Syntax

typedef struct a_truncate_log {

unsigned short	version;

const char *	connectparms;

const char *	startline;

MSG_CALLBACK	errorrtn;

MSG_CALLBACK	msgrtn;

char	quiet	: 1;

} a_truncate_log;

Parameters

Member	Description
version	DBTools version number.
connectparms	The parameters needed to connect to the database. They take the form of connection strings, such as the following: "UID=DBA;PWD=SQL;DBF=c:\asa6\asademo.db"    For the full range of connection string options, see Connection parameters
startline	The command line used to start the database engine. The following is an example start line: "c:\asa6\win32\dbeng7.exe" The default start line is used if this member is NULL.
errorrtn	Callback routine for handling an error message.
msgrtn	Callback routine for handling an information message.
quiet	Operate without printing messages (1), or print messages (0).
See Also

DBTruncateLog function

  For more information on callback functions, see Using callback functions.


an_unload_db structure
  ASA Programming Interfaces Guide

    CHAPTER 3.  The Database Tools Interface

      DBTools structures

 an_unload_db structure

Function

Holds information needed to unload a database using the DBTools library or extract a remote database for SQL Remote. Those fields used by the dbxtract SQL Remote extraction utility are indicated.

Syntax

typedef struct an_unload_db {

unsigned short	version;

const char *	connectparms;

const char *	startline;

const char *	temp_dir;

const char *	reload_filename;

MSG_CALLBACK	errorrtn;

MSG_CALLBACK	msgrtn;

MSG_CALLBACK	statusrtn;

MSG_CALLBACK	confirmrtn;

char	unload_type;

char	verbose;

char	unordered : 1;

char	no_confirm : 1;

char	use_internal_unload : 1;

char	dbo_avail : 1;

char	extract : 1;

char	table_list_provided : 1;

char	exclude_tables : 1;

char	more_flag_bits_present : 1;

a_sysinfo	sysinfo;

const char *	remote_dir;

const char *	dbo_username;

const char *	subscriber_username;

const char *	publisher_address_type;

const char *	publisher_address;

unsigned short	isolation_level;

char	start_subscriptions : 1;

char	exclude_foreign_keys : 1;

char	exclude_procedures : 1;

char	exclude_triggers : 1;

char	exclude_views : 1;

char	isolation_set : 1;

char	include_where_subscribe : 1;

p_name	table_list;

unsigned short	escape_char_present : 1;

unsigned short	view_iterations_present : 1;

unsigned short	view_iterations;

char	escape_char;

} an_unload_db;

Parameters

Member	Description
version	DBTools version number.
connectparms	The parameters needed to connect to the database. They take the form of connection strings, such as the following: "UID=DBA;PWD=SQL;DBF=c:\asa6\asademo.db"    For the full range of connection string options, see Connection parameters.
startline	The command line used to start the database engine. The following is an example start line: "c:\asa6\win32\dbeng7.exe" The default start line is used if this member is NULL.
confirmrtn	Callback routine for confirming an action.
errorrtn	Callback routine for handling an error message.
msgrtn	Callback routine for handling an information message.
statusrtn	Callback routine for handling a status message.
unload_type	A member of the dbunload type enumeration
verbose	Operate in verbose mode.
unordered	If 1, output the data unordered.
no_confirm	If 1, operate without confirming actions.
use_internal_unload	If 1, use the UNLOAD TABLE unloading. Of 0, use the Interactive SQL OUTPUT statement.
dbo_avail	Set to 1. Indicates that the version of DBTools is recent enough to support the dbo_username field.
extract	If 1 carry out a SQL Remote extraction. Otherwise carry out an unload.
table_list_provided	If 1, a table list is provided. Unload data for those tables only.
exclude_tables	If 1, exclude data for the listed tables.
more_flag_bits_present	Set to 1. Indicates that the version of DBTools is recent enough to support the dbo_username field.
sysinfo	Pointer to a_sysinfo structure.
remote_dir	If extracting a database, the directory for remote users.
dbo_username	Username for the dbo user.
subscriber_username	If extracting a database, the user name of the subscriber.
publisher_address_type	If extracting a database, the message type for the publisher.
publisher_address	If extracting a database, the address for the publisher.
isolation_level	If extracting a database, perform the extraction at the specified isolation level.
start_subscriptions	If extracting a database, start the subscription.
exclude_foreign_keys	If extracting a database, exclude foreign key definitions.
exclude_procedures	If extracting a database, exclude procedures.
exclude_triggers	If extracting a database, exclude triggers.
exclude_views	If extracting a database, exclude views.
isolation_set	Set to 1 if the isolation level is set
include_where_subscribe	If extracting a database, extract fully qualified publications and subscriptions.
table_list	Pointer to a linked list of table names.
escape__present	Set to 1 if a non-default escape character is being specified.
view_iterations_present	Set to 1. Indicates that the version of DBTools is recent enough to support the view iterations option.
view_iterations	If your database contains view definitions that are dependent on each other, use this option to avoid failure in reloading the views into a database. The option causes view creation statements to be unloaded multiple times, as specified by the count entered.
escape_char	Escape character.
See Also

DBUnload function

a_name structure

dbunload type enumeration

  For more information on callback functions, see Using callback functions.


an_upgrade_db structure
  ASA Programming Interfaces Guide

    CHAPTER 3.  The Database Tools Interface

      DBTools structures

 an_upgrade_db structure

Function

Holds information needed to upgrade a database using the DBTools library.

Syntax

typedef struct an_upgrade_db {

unsigned short	version;

const char *	connectparms;

const char *	startline;

MSG_CALLBACK	errorrtn;

MSG_CALLBACK	msgrtn;

MSG_CALLBACK	statusrtn;

char	quiet : 1;

char	dbo_avail : 1;

const char *	dbo_username;

} an_upgrade_db;

Parameters

Member	Description
version	DBTools version number.
connectparms	The parameters needed to connect to the database. They take the form of connection strings, such as the following: "UID=DBA;PWD=SQL;DBF=c:\asa6\asademo.db"    For the full range of connection string options, see Connection parameters.
startline	The command line used to start the database engine. The following is an example start line: "c:\asa6\win32\dbeng7.exe" The default start line is used if this member is NULL.
errorrtn	Callback routine for handling an error message.
msgrtn	Callback routine for handling an information message.
statusrtn	Callback routine for handling a status message.
quiet	Operate without printing messages (1), or print messages (0).
dbo_avail	Set to 1. Indicates that the version of DBTools is recent enough to support the dbo_username field.
dbo_username	The name to use for the dbo.
See Also

DBUpgrade function

  For more information on callback functions, see Using callback functions.


a_validate_db structure
  ASA Programming Interfaces Guide

    CHAPTER 3.  The Database Tools Interface

      DBTools structures

 a_validate_db structure

Function

Holds information needed for database validation using the DBTools library.

Syntax

typedef struct a_validate_db {

unsigned short	version;

const char *	connectparms;

const char *	startline;

p_name	tables;

MSG_CALLBACK	errorrtn;

MSG_CALLBACK	msgrtn;

MSG_CALLBACK	statusrtn;

char	quiet : 1;

} a_validate_db;

Parameters

Member	Description
version	DBTools version number.
connectparms	The parameters needed to connect to the database. They take the form of connection strings, such as the following: "UID=DBA;PWD=SQL;DBF=c:\asa6\asademo.db"    For the full range of connection string options, see Connection parameters.
startline	The command line used to start the database engine. The following is an example start line: "c:\asa6\win32\dbeng7.exe" The default start line is used if this member is NULL.
tables	Pointer to a linked list of table names.
errorrtn	Callback routine for handling an error message.
msgrtn	Callback routine for handling an information message.
statusrtn	Callback routine for handling a status message.
quiet	Operate without printing messages (1), or print messages (0).
See Also

DBValidate function

a_name structure

  For more information on callback functions, see Using callback functions.


a_writefile structure
  ASA Programming Interfaces Guide

    CHAPTER 3.  The Database Tools Interface

      DBTools structures

 a_writefile structure

Function

Holds information needed for database write file management using the DBTools library.

Syntax

typedef struct a_writefile {

unsigned short	version;

const char *	writename;

const char *	wlogname;

const char *	dbname;

const char *	forcename;

MSG_CALLBACK	confirmrtn;

MSG_CALLBACK	errorrtn;

MSG_CALLBACK	msgrtn;

char	action;

char	quiet   : 1;

char	erase   : 1;

char	force   : 1;

char	mirrorname_present	: 1;

const char * 	wlogmirrorname;

} a_writefile;

Parameters

Member	Description
version	DBTools version number.
writename	Write file name.
wlogname	Used only when creating write files.
dbname	Used when changing and creating write files.
forcename	Forced file name reference.
confirmrtn	Callback routine for confirming an action. Only used when creating a write file.
errorrtn	Callback routine for handling an error message.
msgrtn	Callback routine for handling an information message.
action	Reserved for use by Sybase.
quiet	Operate without printing messages (1), or print messages (0).
erase	Used for creating write files only. Erase without confirmation (1) or with confirmation (0).
force	If 1, force the write file to point to a named file.
mirrorname_present	Used when creating only. Set to 1. Indicates that the version of DBTools is recent enough to support the mirrorname field.
wlogmirrorname	Name of the transaction log mirror.
See Also

DBChangeWriteFile function

DBCreateWriteFile function

DBStatusWriteFile function

  For more information on callback functions, see Using callback functions.


DBTools enumeration types
  ASA Programming Interfaces Guide

    CHAPTER 3.  The Database Tools Interface

 DBTools enumeration types

This section lists the enumeration types that are used by the DBTools library. The enumerations are listed alphabetically.

 Verbosity enumeration

 Blank padding enumeration

 dbtran_userlist_type enumeration

 dbunload type enumeration


Verbosity enumeration
  ASA Programming Interfaces Guide

    CHAPTER 3.  The Database Tools Interface

      DBTools enumeration types

 Verbosity enumeration

Function

Specifies the volume of output.

Syntax

enum {

VB_QUIET,

VB_NORMAL,

VB_VERBOSE

};

Parameters

Value	Description
VB_QUIET	No output
VB_NORMAL	Normal amount of output
VB_VERBOSE	Verbose output, useful for debugging.
See Also

a_create_db structure

an_unload_db structure


Blank padding enumeration
  ASA Programming Interfaces Guide

    CHAPTER 3.  The Database Tools Interface

      DBTools enumeration types

 Blank padding enumeration

Function

Used in the a_create_db structure, to specify the value of blank_pad.

Syntax

enum {

NO_BLANK_PADDING,

BLANK_PADDING

};

Parameters

Value	Description
NO_BLANK_PADDING	Does not use blank padding
BLANK_PADDING	Uses blank padding
See Also

a_create_db structure


dbtran_userlist_type enumeration
  ASA Programming Interfaces Guide

    CHAPTER 3.  The Database Tools Interface

      DBTools enumeration types

 dbtran_userlist_type enumeration

Function

The type of a user list, as used by an a_translate_log structure.

Syntax

typedef enum dbtran_userlist_type {

DBTRAN_INCLUDE_ALL,

DBTRAN_INCLUDE_SOME,

DBTRAN_EXCLUDE_SOME

} dbtran_userlist_type;

Value	Description
DBTRAN_INCLUDE_ALL	Include operations from all users
DBTRAN_INCLUDE_SOME	Include operations only from the users listed in the supplied user list.
DBTRAN_EXCLUDE_SOME	Exclude operations from the users listed in the supplied user list.
See Also

a_translate_log structure


dbunload type enumeration
  ASA Programming Interfaces Guide

    CHAPTER 3.  The Database Tools Interface

      DBTools enumeration types

 dbunload type enumeration

Function

The type of unload being performed, as used by the an_unload_db structure.

Syntax

enum {

UNLOAD_ALL,

UNLOAD_DATA_ONLY,

UNLOAD_NO_DATA

};

Value	Description
UNLOAD_ALL	Unload both data and schema.
UNLOAD_DATA_ONLY	Unload data. Do not unload schema.
UNLOAD_NO_DATA	Unload schema only.
See Also

an_unload_db structure


CHAPTER 4.  ODBC Programming
  ASA Programming Interfaces Guide

 CHAPTER 4.  ODBC Programming

About this chapter

The Open Database Connectivity (ODBC) interface is a programming language interface defined by Microsoft Corporation as a standard interface to database-management systems in the Windows and Windows NT environments.

This chapter presents information for those who use the ODBC interface directly. Users of application development systems that already have ODBC support do not need to read this chapter.

The primary documentation for ODBC application development is the ODBC SDK, available from Microsoft. This chapter describes features specific to Adaptive Server Anywhere, but is not an exhaustive guide to ODBC application programming.

 Introduction to ODBC programming

 ODBC fundamentals

 Error checking

 Using prepared statements

 Working with result sets

 Calling stored procedures

 A sample program

 Using ODBC without the driver manager

 Using ODBC on Windows CE


Introduction to ODBC programming
  ASA Programming Interfaces Guide

    CHAPTER 4.  ODBC Programming

 Introduction to ODBC programming

The ODBC interface is defined by a set of function calls, called the ODBC API (Application Programming Interface).

To write ODBC applications for Adaptive Server Anywhere, you need:

•	The Adaptive Server Anywhere software.

•	A C compiler capable of creating programs for your environment.

•	We also recommend that you obtain the Microsoft ODBC Software Development Kit. This is available on the Microsoft Developer Network, and provides documentation and additional tools for testing ODBC applications.

Applications that use the ODBC interface can work with many different database systems. Adaptive Server Anywhere supports the ODBC API on UNIX, in addition to the Windows and Windows NT environments. Having multi-platform ODBC support makes portable database application development much easier.

  For information on enlisting the ODBC driver in distributed transactions, see Three-tier Computing and Distributed Transactions.

 ODBC conformance


ODBC conformance
  ASA Programming Interfaces Guide

    CHAPTER 4.  ODBC Programming

      Introduction to ODBC programming

 ODBC conformance

Adaptive Server Anywhere provides support for ODBC 3.51 on Windows 95/98, Windows NT, and Windows CE. Adaptive Server Anywhere supports ODBC version 2.5 on Windows 3.1.

Levels of ODBC support

ODBC features are arranged according to a level of conformance. Features are either Core, Level 1, or Level 2, with level 2 being the most complete level of ODBC support. These features are listed in the ODBC Programmer's Reference, which is available from Microsoft Corporation as part of the ODBC software development kit or from the Microsoft Web site, at the following Web site:

http://www.microsoft.com/data/odbc

Features supported by Adaptive Server Anywhere

Adaptive Server Anywhere supports the ODBC 3.51 specification.

•	Core conformance    Adaptive Server Anywhere supports all Core level features.

•	Level 1 conformance    Adaptive Server Anywhere supports all Level 1 features, except for asynchronous execution of ODBC functions.

Adaptive Server Anywhere does support multiple threads sharing a single connection. The requests from the different threads are serialized by Adaptive Server Anywhere.

•	Level 2 conformance    Adaptive Server Anywhere supports all Level 2 features, except for the following:

•	Three part names of tables and views. This is not applicable for Adaptive Server Anywhere.

•	Asynchronous execution of ODBC functions for specified individual statements.

•	Ability to time out login request and SQL queries.

ODBC backwards compatibility

Applications developed using older versions of ODBC will continue to work with Adaptive Server Anywhere and the newer ODBC driver manager. The new ODBC features are not provided for older applications.

The ODBC driver manager

The ODBC driver manager is part of the ODBC software supplied with Adaptive Server Anywhere. The ODBC Version 3 driver manager has a new interface for configuring ODBC data sources.


ODBC fundamentals
  ASA Programming Interfaces Guide

    CHAPTER 4.  ODBC Programming

 ODBC fundamentals

Database access in ODBC is carried out using SQL statements passed as strings to ODBC functions.

The following fundamental objects are used for every ODBC program. Each object is referenced by a handle.

ODBC Version 3.x

In ODBC version 3.x, all objects are allocated using the function SQLAllocHandle. SQLAllocHandle takes three parameters: the object being allocated, the parent object, and a pointer to the location of the handle to be allocated. An environment handle may be allocated as follows:

MyEnvironment = SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &henv );

All access to these objects is through function calls; the application cannot directly access any information about the object from its handle. In the Windows and Windows NT environments, all the function calls are described in full detail in the ODBC API help file, which is part of the ODBC SDK.

ODBC Version 2.x

•	Environment    Every ODBC application must allocate exactly one environment upon starting and free it at the end. With ODBC version 2.x, environments are allocated using SQLAllocEnv, and freed using SQLFreeEnv.

•	Connections    An application can have several connections associated with its environment. Each connection must be allocated using SQLAllocConnect and freed using SQLFreeConnect. Your application can connect to a data source using either SQLConnect, SQLDriverConnect or SQLBrowseConnect, and disconnect using SQLDisconnect. A connection handle is of type HDBC.

•	Statements    Each connection can have several statements, each allocated using SQLAllocStmt and freed using SQLFreeStmt. Statements are used both for cursor operations (fetching data) and for single statement execution (e.g. INSERT, UPDATE and DELETE). A statement handle is of type HSTMT.

 Compiling and linking your application

 A first example

 Threads in ODBC applications


Compiling and linking your application
  ASA Programming Interfaces Guide

    CHAPTER 4.  ODBC Programming

      ODBC fundamentals

 Compiling and linking your application

Every C source file using ODBC functions must include one of the following lines:

•	Windows 95/98 and Windows NT    #include "ntodbc.h"

These files all include the main ODBC include file odbc.h, which defines all of the functions, data types and constant definitions required to write an ODBC program. The file odbc.h and the environment-specific include files are installed in the h subdirectory of your Adaptive Server Anywhere installation directory.

Once your program has been compiled, you must link with the appropriate import library file to have access to the ODBC functions:

•	Windows 95/98 and Windows NT    wodbc32.lib, which defines the entry points into the Driver Manager odbc32.dll. If you connect to a database in Windows NT, odbc32.dll will load the ODBC driver, dbodbc7.dll.

•	UNIX    dbodbc.lib, which defines the entry points into the Adaptive Server Anywhere ODBC driver, dbodbc7.so.

•	Windows CE    There is no ODBC driver manager for Windows CE. ODBC applications should link against the supplied imports library (dbodbc7.lib for ASCII characters or dbuodbc6.lib for Unicode). The sample program (odbc.c) uses a File Data Source called ASA 7.0 Sample.dsn to connect to the database. You can create File Data Sources using the ODBC Driver Manager on your desktop system, then copy the file to the CE device. You can also edit File DSN's with a text editor.


A first example
  ASA Programming Interfaces Guide

    CHAPTER 4.  ODBC Programming

      ODBC fundamentals

 A first example

The following is a simple ODBC program:

{

HENV	env;

HDBC	dbc;

HSTMT	stmt;

SQLAllocEnv( &env );

SQLAllocConnect( env, &dbc );

SQLConnect( dbc, "asademo", SQL_NTS,

"dba", SQL_NTS, "sql", SQL_NTS );

SQLSetConnectOption( dbc, SQL_AUTOCOMMIT, FALSE );

SQLAllocStmt( dbc, &stmt );

/* Delete all the order items for order 2015 */

SQLExecDirect( stmt,

"DELETE FROM sales_order_items WHERE id=2015",

 SQL_NTS );

/* Use rollback to undo the delete */

SQLTransact( env, dbc, SQL_ROLLBACK );

SQLFreeStmt( stmt, SQL_DROP );

SQLDisconnect( dbc );

SQLFreeConnect( dbc );

SQLFreeEnv( env );

}

Notes

•	SQL_NTS    Every string passed to ODBC has a corresponding length. If the length is unknown, you can pass SQL_NTS indicating that it is a Null Terminated String whose end is marked by the null character (\0).

•	SQLSetConnectOption    By default, ODBC operates in auto-commit mode. This mode is turned off by setting SQL_AUTOCOMMIT to false.

•	SQLExecDirect    Executes the SQL statement specified in the parameter.

•	SQLTransact    Used to perform COMMIT and ROLLBACK statements marking the end of a transaction. You should not use SQLExecDirect to perform COMMIT or ROLLBACK.


Threads in ODBC applications
  ASA Programming Interfaces Guide

    CHAPTER 4.  ODBC Programming

      ODBC fundamentals

 Threads in ODBC applications

You can develop multi-threaded ODBC applications for Adaptive Server Anywhere. It is recommended that you use a separate connection for each thread.

You can use a single connection for multiple threads. However, the database server does not allow more than one active request for any one connection at a time. If one thread executes a statement that takes a long time, all other threads must wait until the request is complete.


Error checking
  ASA Programming Interfaces Guide

    CHAPTER 4.  ODBC Programming

 Error checking

The previous example did not check for any errors. Errors in ODBC are reported using the return value from each of the ODBC API function calls and the SQLError function.

Every ODBC API function returns a RETCODE, which is one of the following status codes:

•	SQL_SUCCESS    No error.

•	SQL_SUCCESS_WITH_INFO    The function completed, but a call to SQLError will indicate a warning. The most common case for this status is that a value being returned is too long for the buffer provided by the application.

•	SQL_ERROR    The function did not complete due to an error. You can call SQLError to get more information on the problem.

•	SQL_INVALID_HANDLE    An invalid environment, connection, or statement handle was passed as a parameter. This often happens if a handle is used after it has been freed, or if the handle is the null pointer.

•	SQL_NO_DATA_FOUND    There is no information available. The most common use for this status is when fetching from a cursor; it indicates that there are no more rows in the cursor.

•	SQL_NEED_DATA    Data is needed for a parameter. This is an advanced feature described in the help file under SQLParamData and SQLPutData.

Every environment, connection, and statement handle can have one or more errors or warnings associated with it. Each call to SQLError returns the information for one error and removes the information for that error. If you do not call SQLError to remove all errors, the errors are removed on the next function call that passes the same handle as a parameter.

Example

The following program fragment uses SQLError and return codes:

HDBC dbc;

HSTMT stmt;

RETCODE retcode;

UCHAR errmsg[100];

. . .

retcode = SQLAllocStmt( dbc, &stmt );

if( retcode == SQL_ERROR ) {

SQLError( env, dbc, SQL_NULL_HSTMT, NULL, NULL,

errmsg, sizeof(errmsg), NULL );

/* Assume that print_error is defined */

print_error( "Failed SQLAllocStmt", errmsg );

return;

}

/* Delete items for order 2015 */

retcode = SQLExecDirect( stmt,

"delete from sales_order_items

where id=2015", SQL_NTS );

if( retcode == SQL_ERROR ) {

SQLError( env, dbc, stmt, NULL, NULL,

errmsg, sizeof(errmsg), NULL );

/* Assume that print_error is defined */

print_error( "Failed to delete items", errmsg );

return;

}

. . .

Each call to SQLError passes three handles for an environment, connection, and statement. The first call uses SQL_NULL_HSTMT to get the error associated with a connection. Similarly, a call with both SQL_NULL_DBC and SQL_NULL_HSTMT will get any error associated with the environment handle.

The return value from SQLError may seem confusing. It returns SQL_SUCCESS if there is an error to report (not SQL_ERROR), and SQL_NO_DATA_FOUND if there are no more errors to report.

The examples pass the null pointer for some of the parameters to SQLError. The help file contains a full description of SQLError and all its parameters.


Using prepared statements
  ASA Programming Interfaces Guide

    CHAPTER 4.  ODBC Programming

 Using prepared statements

Prepared statements provide performance advantages for statements that are used repeatedly. ODBC provides a full set of functions for using prepared statements.

  For an introduction to prepared statements, see Preparing statements.

♦ To execute a prepared statement:

1	You prepare the statement using SQLPrepare. The following code fragment illustrates how to prepare an INSERT statement:

SQLRETURN   retcode;

SQLHSTMT    hstmt;

retcode = SQLPrepare(hstmt,

            "INSERT

             INTO department

             (dept_id, dept_name, dept_head_id )

             VALUES (?, ?, ?,)",

          SQL_NTS);

In this example:

•	retcode    Holds a return code that should be tested for success or failure of the operation.

•	hstmt    Provides a handle to the statement, so that it can be referenced later.

•	?    The question marks are placeholders for statement parameters.

2	You set statement parameter values using SQLBindParameter. For example, the following function call sets the value of the dept_id variable:

SQLBindParameter(hstmt,

                 1,

                 SQL_PARAM_INPUT,

                 SQL_C_SSHORT,

                 SQL_INTEGER,

                 0,

                 0,

                 &sDeptID,

                 0,

                 &cbDeptID);

In this example:

•	hstmt is the statement handle

•	1 indicates that this call sets the value of the first placeholder.

•	SQL_PARAM_INPUT indicates that the parameter is an input statement.

•	SQL_C_SHORT and SQL_INTEGER indicate the C data type being used in the application and the SQL type being used in the database.

•	The next two parameters indicate the column precision and the number of decimal digits: both zero for integers.

•	&sDeptID is a pointer to a buffer for the parameter value.

•	The following zero indicates the length of the buffer, in bytes.

•	&cbDeptID is a pointer to a buffer for the length of the parameter value.

•	Bind the other two parameters and assign values to sDeptId:

•	Execute the statement:

retcode = SQLExecute(hstmt);

This step can be carried out multiple times.

•	Drop the statement. This frees resources associated with the statement itself. You drop statements using SQLFreeStmt.

  For more information, see the ODBC SDK documentation.


Working with result sets
  ASA Programming Interfaces Guide

    CHAPTER 4.  ODBC Programming

 Working with result sets

ODBC provides extensive support for different kinds of cursor and cursor operations.

  For an introduction to cursors, see Working with cursors.

A cursor is opened using SQLExecute or SQLExecDirect, rows are fetched using SQLFetch or SQLExtendedFetch and the cursor is closed using SQLFreeStmt.

To fetch values from a cursor, the application can use either SQLBindCol or SQLGetData. If you use SQLBindCol, values are automatically retrieved on each fetch. If you use SQLGetData, you must call it for each column after each fetch.

The following code fragment opens and reads a cursor. Error checking has been omitted to make the example easier to read.

. . .

HDBC dbc;

HSTMT stmt;

RETCODE retcode;

long emp_id;

char emp_lname[20];

SQLAllocStmt( dbc, &stmt );

SQLExecDirect( stmt,

"select emp_id,emp_lname

from employee", SQL_NTS );

SQLBindCol( stmt, 1, SQL_C_LONG, &emp_id,

sizeof(emp_id), NULL );

SQLBindCol( stmt, 2, SQL_C_CHAR, &emp_lname,

sizeof(emp_lname), NULL );

for(;;) {

retcode = SQLFetch( stmt );

if( retcode == SQL_NO_DATA_FOUND ) break;

print_employee( emp_id, emp_lname);

}

/* Using SQL_CLOSE closes the cursor

but does not free the statement */

SQLFreeStmt( stmt, SQL_CLOSE );

. . .

Notes

ODBC documentation suggests that you use SELECT... FOR UPDATE to indicate that a query is updateable using positioned operations. You do not need to use the FOR UPDATE clause in Adaptive Server Anywhere; SELECT statements are automatically updateable as long as the underlying query supports updates. That is to say, as long as a data modification statement on the columns in the result is meaningful, then positioned data modification statements can be carried out on the cursor.

ODBC version 3.0 and later provides a cursor type called a block cursor. When you use a block cursor, you can use SQLFetch to fetch a block of rows, rather than a single row.

There are two alternatives for carrying out positioned updates and deletes in ODBC. You can send positioned UPDATE and DELETE statements using SQLExecute or you can use SQLSetPos to carry out the operation. Depending on the parameters supplied (SQL_POSITION, SQL_REFRESH, SQL_UPDATE, SQL_DELETE) SQLSetPos sets the cursor position and allows an application to refresh data, or update or delete data in the result set. With Adaptive Server Anywhere, you should use SQLSetPos to carry out positioned operations on cursors.

The number of row positions you can fetch in a cursor is governed by the size of an integer. You can fetch rows numbered up to number 2147483646, which is one less than the value that can be held in an integer. When using negative numbers (rows from the end) you can fetch down to one more than the largest negative value that can be held in an integer.

Bookmarks and cursors

ODBC provides bookmarks, which are values used to identify rows in a cursor. Adaptive Server Anywhere supports bookmarks for all kinds of cursor except dynamic cursors.

Before ODBC 3.0, a database could specify only whether it supports bookmarks or not: there was no interface to provide this information for each cursor type. There was no way for a database server to indicate for what kind of cursor bookmarks were supported. For ODBC 2 applications, Adaptive Server Anywhere returns that it does support bookmarks. There is therefore nothing to prevent you from trying to use bookmarks with dynamic cursors; however, you should not use this combination.


Calling stored procedures
  ASA Programming Interfaces Guide

    CHAPTER 4.  ODBC Programming

 Calling stored procedures

This section describes how to call stored procedures and process the results from an ODBC application.

  For a full description of stored procedures and triggers, see Using Procedures, Triggers, and Batches.

Procedures and result sets

There are two types of procedures: those that return result sets and those that do not. You can use SQLNumResultCols to tell the difference: the number of result columns is zero if the procedure does not return a result set. If there is a result set, you can fetch the values using SQLFetch or SQLExtendedFetch just like any other cursor.

Parameters to procedures should be passed using parameter markers (question marks). Use SQLBindParameter to assign a storage area for each parameter marker, whether it is an INPUT, OUTPUT or INOUT parameter.

In order to handle multiple result sets, ODBC must describe the currently executing cursor, not the procedure defined result set. Therefore, ODBC does not always describe column names as defined in the RESULT clause of the stored procedure definition. To avoid this problem, you can use column aliases in your procedure result set cursor.

An example with no result set

The following example creates and calls a procedure. The procedure takes one INOUT parameter, and increments its value. In the example, the variable num_col will have the value zero, since the procedure does not return a result set. Error checking has been omitted, to make the example easier to read.

HDBC dbc;

HSTMT stmt;

long i;

SWORD num_col;

/* Create a procedure */

SQLAllocStmt( dbc, &stmt );

SQLExecDirect( stmt,

"CREATE PROCEDURE Increment( INOUT a INT )" \

" BEGIN" \

" SET a = a + 1" \

" END", SQL_NTS );

/* Call the procedure to increment 'i' */

i = 1;

SQLBindParameter( stmt, 1, SQL_C_LONG, SQL_INTEGER, 0,

0, &i, NULL );

SQLExecDirect( stmt, "CALL Increment( ? )",

SQL_NTS );

SQLNumResultCols( stmt, &num_col );

do_something( i );

An example with a result set

The following example calls a procedure that returns a result set. In the example, the variable num_col will have the value 2, since the procedure returns a result set with two columns. Again, error checking has been omitted, to make the example easier to read.

HDBC dbc;

HSTMT stmt;

SWORD num_col;

RETCODE retcode;

char emp_id[ 10 ];

char emp_lame[ 20 ];

/* Create the procedure */

SQLExecDirect( stmt,

"CREATE PROCEDURE employees()" \

" RESULT( emp_id CHAR(10), emp_lname CHAR(20))"\

" BEGIN" \

" SELECT emp_id, emp_lame FROM employee" \

" END", SQL_NTS );

/* Call the procedure - print the results */

SQLExecDirect( stmt, "CALL employees()", SQL_NTS );

SQLNumResultCols( stmt, &num_col );

SQLBindCol( stmt, 1, SQL_C_CHAR, &emp_id,

sizeof(emp_id), NULL );

SQLBindCol( stmt, 2, SQL_C_CHAR, &emp_lname,

sizeof(emp_lname), NULL );

for( ;; ) {

retcode = SQLFetch( stmt );

if( retcode == SQL_NO_DATA_FOUND ) {

retcode = SQLMoreResults( stmt );

if( retcode == SQL_NO_DATA_FOUND ) break;

} 	else {

do_something( emp_id, emp_lname );

}

}


A sample program
  ASA Programming Interfaces Guide

    CHAPTER 4.  ODBC Programming

 A sample program

A sample ODBC program, odbc.c, is supplied in the cxmp subdirectory of the Adaptive Server Anywhere installation directory. The program performs the same actions as the Embedded SQL dynamic cursor example program.

 For a description of the associated Embedded SQL program, see Database examples.

Building the sample program

Along with the sample program is a batch file, makeall.bat, that can be used to compile the sample program for the various environments and compilers supported by Adaptive Server Anywhere. For UNIX, use the shell script makeall. The format of the command is as follows:

makeall odbc {Platform} {Compiler}

The first parameter is odbc, meaning compile the ODBC example. The same batch file also compiles the Embedded SQL programs.

The second parameter is the platform in which the program will be run. The platform must be WINNT, to compile for Windows NT or Windows 95/98.

The third parameter is the compiler to use to compile the program. The compiler can be one of:

•	WC    use Watcom C or Watcom C/32

•	MC    use Microsoft C

•	BC    use Borland C

Building the sample program as an NT service

The example program odbc.c, when compiled for Windows NT, runs optionally as a service.

The two files containing the example code for NT services are the source file ntsvc.c and the header file ntsvc.h. The code allows the linked executable to be run either as a regular executable or as an NT service.

♦ To run the compiled example as a Windows NT service:

1	Start Sybase Central and open the Services folder.

2	Click Add Service. Follow the instructions for adding the sample program as a service.

3	Right-click the service icon and click Start to start the service.

When run as a service, the program displays the normal user interface if possible. It also writes the output to the Application Event Log. If it is not possible to start the user interface, the program prints one page of data to the Application Event Log and stops.

This example has been tested with the Watcom C/C++ 10.5 compiler and the Microsoft Visual C++ 2.0 compiler.


Using ODBC without the driver manager
  ASA Programming Interfaces Guide

    CHAPTER 4.  ODBC Programming

 Using ODBC without the driver manager

ODBC applications do not generally link directly to the ODBC driver. Instead, they link to the ODBC driver manager, which in turn handles the driver or drivers that may be required.

On Windows operating systems, the driver manager is supplied by Microsoft and ships with Adaptive Server Anywhere. On other operating systems, other vendors do supply ODBC driver managers; for example, Intersolv and Visigenic supply ODBC driver managers for UNIX. No driver managers for non-Windows systems, however, ship with Adaptive Server Anywhere.

It is also possible to develop applications that link directly to the ODBC driver. This is particularly useful on operating systems such as UNIX, where your end users may not have access to an ODBC driver manager.

You develop an application that uses the ODBC driver directly by carrying out the following steps:

•	Use the supplied import library as usual.

  For descriptions of the import libraries, see Compiling and linking your application.

•	Specify the DRIVER= parameter in the szConnStrIn argument to the SQLDriverConnect function. For example:

szConnStrIn = "driver=ospath/dbodbc7.dll;dbf=c:\asademo.db"

where ospath is the operating system subdirectory of your Adaptive Server Anywhere installation directory.

 Developing ODBC applications on UNIX


Developing ODBC applications on UNIX
  ASA Programming Interfaces Guide

    CHAPTER 4.  ODBC Programming

      Using ODBC without the driver manager

 Developing ODBC applications on UNIX

An ODBC driver manager is not included with Adaptive Server Anywhere, but there are third party driver managers available. An ODBC driver manager includes the following files:

Operating system	Files
Solaris/Sparc, AIX	libodbc.so (libodbc.so.1) libodbcinst.so (libodbcinst.so.1)
HP-UX	libodbc.sl (libodbc.sl.1) libodbcinst.sl (libodbcinst.sl.1)
The Adaptive Server Anywhere ODBC driver is the following file:

Operating system	ODBC driver
Solaris/Sparc, AIX	dbodbc7.so (dbodbc7.so.1) dbodbc_r.so (dbodbc_r.so.1)
HP-UX	dbodbc.sl dbodbc_r.sl (dbodbc_r.sl.1)
The libraries with the _r are for use with multi-threaded applications. If you are developing single-threaded applications, use the library without the _r.

If you want to use the Adaptive Server Anywhere ODBC driver without an ODBC driver manager you can do so, but you can then access only Adaptive Server Anywhere data sources. The libraries are installed as symbolic links to the shared library with a version number (in parentheses).

♦ To use Adaptive Server Anywhere without an ODBC driver manager:

1	If you are creating a custom ODBC application, you can link directly to the Adaptive Server Anywhere ODBC driver.

2	If your application requires an ODBC driver manager, create symbolic links for both the libodbc and libodbcinst shared libraries to the Adaptive Server Anywhere ODBC driver.

Data source information

If an ODBC driver manager is present, Adaptive Server Anywhere queries the driver manager for data source information. If Adaptive Server Anywhere does not detect the presence of an ODBC driver manager, it uses ~/.odbc.ini for data source information.


Using ODBC on Windows CE
  ASA Programming Interfaces Guide

    CHAPTER 4.  ODBC Programming

 Using ODBC on Windows CE

Adaptive Server Anywhere for Windows CE provides an ODBC driver, dbodbc7.dll.

  For a list of supported versions of Windows CE, see Adaptive Server Anywhere system requirements.

No ODBC driver manager on Windows CE

There is no ODBC driver manager on Windows CE. ODBC applications should link against the supplied imports library dbodbc7.lib to use the ODBC driver. The sample program (odbc.c) uses a File data source (FileDSN connection parameter) called ASA 7.0 Sample.dsn. You can create File data sources on your desktop system from the ODBC driver manager, and copy them to your Windows CE device.

Windows CE and Unicode

Adaptive Server Anywhere uses an encoding known as UTF-8, a multi-byte character encoding which can be used to encode Unicode.

The Adaptive Server Anywhere ODBC driver supports either ASCII (8-bit) strings or Unicode code (wide character) strings. The UNICODE macro controls whether ODBC functions expect ASCII or Unicode strings. If your application must be built with the UNICODE macro defined, but you want to use the ASCII ODBC functions, then the SQL_NOUNICODEMAP macro must also be defined.

The odbc.c example file illustrates how to use the Unicode ODBC features.

Earlier releases

In version 6.0.1, a separate Unicode ODBC driver was supplied. This is no longer required, as the ODBC driver supports both Unicode and ASCII strings. Applications using the 6.0.1 ODBC driver should continue to supply that driver alongside the application. Otherwise, you should modify your application to use the newer ODBC driver.


CHAPTER 5.  The OLE DB and ADO Programming Interfaces
  ASA Programming Interfaces Guide

 CHAPTER 5.  The OLE DB and ADO Programming Interfaces

About this chapter

This chapter describes how to use the OLE DB interface to Adaptive Server Anywhere.

Many applications that use the OLE DB interface do so through the Microsoft ActiveX Data Objects (ADO) programming model, rather than directly. This chapter describes ADO programming with Adaptive Server Anywhere.

 Introduction to OLE DB

 ADO programming with Adaptive Server Anywhere

 Supported OLE DB interfaces


Introduction to OLE DB
  ASA Programming Interfaces Guide

    CHAPTER 5.  The OLE DB and ADO Programming Interfaces

 Introduction to OLE DB

OLE DB is a data access model from Microsoft. It uses the Component Object Model (COM) interfaces and, unlike ODBC, OLE DB does not assume that the data source uses a SQL query processor.

Adaptive Server Anywhere includes an OLE DB provider named ASAProv. This provider is available for current Windows platforms as well as the upcoming version of Windows CE that will support OLE DB.

You can also access Adaptive Server Anywhere using the Microsoft OLE DB Provider for ODBC (MSDASQL), together with the Adaptive Server Anywhere ODBC driver.

Using the Adaptive Server Anywhere OLE DB provider brings several benefits:

•	Some features, such as updating through a cursor, are not available using the OLE DB/ODBC bridge.

•	If you use the Adaptive Server Anywhere OLE DB provider, ODBC is not required in your deployment.

•	MSDASQL allows OLE DB clients to work with any ODBC driver but does not guarantee that you can use the full range of functionality of each ODBC driver. By using the Adaptive Server Anywhere provider, you can get full access to Adaptive Server Anywhere features from OLE DB programming environments.

 Supported platforms

 Distributed transactions


Supported platforms
  ASA Programming Interfaces Guide

    CHAPTER 5.  The OLE DB and ADO Programming Interfaces

      Introduction to OLE DB

 Supported platforms

The Adaptive Server Anywhere OLE DB provider is designed to work with OLE DB 2.5 and later. For Windows CE and its successors, the OLE DB provider is designed for ADOCE 3.0 and later.

ADOCE is the Microsoft ADO for Windows CE SDK, and provides database functionality for applications developed with the Windows CE Toolkits for Visual Basic 5.0 and Visual Basic 6.0.

Adaptive Server Anywhere supports Windows CE version 2.11. It does not support Windows CE version 2.0. Windows CE 2.11 includes a version of ADOCE lower than 3.0, which is considerably more restricted, and is not supported by the Adaptive Server Anywhere OLE DB driver.

At the time of printing, the next releases of Windows CE will be named Windows for Pocket PC (code named Rapier) and Windows for Handheld PC. Pocket PC devices are expected to include ADOCE 3.0. The Adaptive Server Anywhere OLE DB driver has been tested with prerelease versions of Windows for Pocket PC.

In future, Jupiter class Windows CE 2.11 devices may be able to use ADOCE 3.1. At such time, it is expected that you will be able to use the Adaptive Server Anywhere OLE DB driver on these devices.


Distributed transactions
  ASA Programming Interfaces Guide

    CHAPTER 5.  The OLE DB and ADO Programming Interfaces

      Introduction to OLE DB

 Distributed transactions

The OLE DB driver can be used as a resource manager in a distributed transaction environment. For more information, see Three-tier Computing and Distributed Transactions.


ADO programming with Adaptive Server Anywhere
  ASA Programming Interfaces Guide

    CHAPTER 5.  The OLE DB and ADO Programming Interfaces

 ADO programming with Adaptive Server Anywhere

ADO (ActiveX Data Objects) is a data access object model exposed through an Automation interface, which allows client applications to discover the methods and properties of objects at runtime without any prior knowledge of the object. Automation allows scripting languages like Visual Basic to use a standard data access object model. ADO uses OLE DB to provide data access.

By using the Adaptive Server Anywhere OLE DB provider, you get full access to Adaptive Server Anywhere features from an ADO programming environment.

This section describes how to carry out basic tasks while using ADO from Visual Basic. It is not a complete guide to programming using ADO.

  For information on programming in ADO, see your development tool documentation.

 Connecting to a database with the Connection object

 Executing statements with the Command object

 Querying the database with the Recordset object

 Working with Recordset object

 Updating data through a cursor

 Using transactions


Connecting to a database with the Connection object
  ASA Programming Interfaces Guide

    CHAPTER 5.  The OLE DB and ADO Programming Interfaces

      ADO programming with Adaptive Server Anywhere

 Connecting to a database with the Connection object

This section describes a simple Visual Basic routine that connects to a database.

Sample code

You can try this routine by placing a command button named Command1 on a form, and pasting the routine into its Click event. Run the program and click the button to connect and then disconnect.

Private Sub Command1_Click()

    ' Declare variables

    Dim myConn As New ADODB.Connection

    ' Establish the connection

    myConn.Provider = "ASAProv"

    myConn.ConnectionString = _

      "Data Source=ASA 7.0 Sample"

    myConn.Open

    'Close the connection

    myConn.Close

End Sub

Notes

The sample carries out the following tasks:

•	It declares the variables used in the routine.

•	It establishes a connection, using the Adaptive Server Anywhere OLE DB provider, to the sample database.

•	It uses a Command object to execute a simple statement, which displays a message on the database server window.

•	It closes the connection.

When the ASAProv provider is installed, it registers itself. This registration process includes making registry entries in the COM section of the registry, so that ADO can locate the DLL when the ASAProv provider is called. If you change the location of your DLL, you must reregister it.

♦ To register the OLE DB provider:

1	Open a command prompt.

2	Change to the directory where the OLE DB provider is installed.

3	Enter the following command to register the provider:

regsvr32 dboledb7.dll

  For more information about connecting to a database using OLE DB, see Connecting to a database using OLE DB.


Executing statements with the Command object
  ASA Programming Interfaces Guide

    CHAPTER 5.  The OLE DB and ADO Programming Interfaces

      ADO programming with Adaptive Server Anywhere

 Executing statements with the Command object

This section describes a simple routine that sends a simple SQL statement to the database.

Sample code

You can try this routine by placing a command button named Command2 on a form, and pasting the routine into its Click event. Run the program and click the button to connect, display a message on the database server window, and then disconnect.

Private Sub Command2_Click()

    ' Declare variables

    Dim myConn As New ADODB.Connection

    Dim myCommand As New ADODB.Command

    Dim cAffected As Long

    ' Establish the connection

    myConn.Provider = "ASAProv"

    myConn.ConnectionString = _

      "Data Source=ASA 7.0 Sample"

    myConn.Open

    'Execute a command

    myCommand.CommandText = _

    "update customer set fname='Liz' where id=102"

    Set myCommand.ActiveConnection = myConn

    myCommand.Execute cAffected

    MsgBox CStr(cAffected) +

" rows affected.", vbInformation

    myConn.Close

End Sub

Notes

After establishing a connection, the example code creates a Command object, sets its CommandText property to an update statement and its ActiveConnection property to the current connection. It then executes the update statement, and displays the number of rows affected by the update in a message box.

In this example, the update is sent to the database and committed as soon as it is executed. For information on using transactions within ADO, see Using transactions.

You can also carry out updates through a cursor. See Updating data through a cursor.


Querying the database with the Recordset object
  ASA Programming Interfaces Guide

    CHAPTER 5.  The OLE DB and ADO Programming Interfaces

      ADO programming with Adaptive Server Anywhere

 Querying the database with the Recordset object

The ADO Recordset object represents the result set of a query. You can use it to view data from a database.

Sample code

You can try this routine by placing a command button named Command3 on a form, and pasting the routine into its Click event. Run the program and click the button to connect, display a message on the database server window, execute a query and display the first few rows in message boxes, and then disconnect.

Private Sub Command3_Click()

    ' Declare variables

    Dim myConn As New ADODB.Connection

    Dim myCommand As New ADODB.Command

    Dim myRS As New ADODB.Recordset

    ' Establish the connection

    myConn.Provider = "ASAProv"

    myConn.ConnectionString = _

      "Data Source=ASA 7.0 Sample"

    myConn.Open

    'Execute a command

    myCommand.CommandText = "Select * from customer"

    Set myCommand.ActiveConnection = myConn

    Set myRS = myCommand.Execute

    'Scroll through the first few results

    myRS.MoveFirst

    For i = 1 To 5

      MsgBox myRS.Fields("company_name"), vbInformation

      myRS.MoveNext

    Next

    myRS.Close

    myConn.Close

End Sub

Notes

The Recordset object in this example holds the results from a query on the Customer table. The For loop scrolls through the first several rows and displays the company_name value for each row.

This is a simple example of using a cursor from ADO. For more advanced examples, see Working with Recordset object.


Working with Recordset object
  ASA Programming Interfaces Guide

    CHAPTER 5.  The OLE DB and ADO Programming Interfaces

      ADO programming with Adaptive Server Anywhere

 Working with Recordset object

When working with Adaptive Server Anywhere, the ADO Recordset represents a cursor. You can choose the type of cursor by declaring a CursorType property of the Recordset object, before you open the Recordset. The choice of cursor type controls the actions you can take on the Recordset and has performance implications.

Cursor types

The set of cursor types supported by Adaptive Server Anywhere is described in Types of cursor. ADO has its own naming convention for cursor types.

The available cursor types, the corresponding cursor type constants, and the Adaptive Server Anywhere types they are equivalent to, are as follows:

ADO cursor type	ADO constant	Adaptive Server Anywhere type
Dynamic cursor	adOpenDynamic	Dynamic scroll cursor
Keyset cursor	adOpenKeyset	Scroll cursor
Static cursor	adOpenStatic	Insensitive cursor
Forward only	adOpenForwardOnly	No-scroll cursor
  For information on choosing a cursor type that is suitable for your application, see Choosing a cursor type — dynamic vs scroll cursors.

Sample code

The following code sets the cursor type for an ADO Recordset object:

Dim myRS As New ADODB.Recordset

myRS.CursorType = adOpenDynamic


Updating data through a cursor
  ASA Programming Interfaces Guide

    CHAPTER 5.  The OLE DB and ADO Programming Interfaces

      ADO programming with Adaptive Server Anywhere

 Updating data through a cursor

The Adaptive Server Anywhere OLE DB provider lets you update a result set through a cursor. This capability is not available through the MSDASQL provider.

Updating record sets

You can update the database through a record set.

Private Sub Command6_Click()

    Dim myConn As New ADODB.Connection

    Dim myRS As New ADODB.Recordset

    Dim SQLString As String

    ' Connect

    myConn.Provider = "ASAProv"

    myConn.ConnectionString = _

        "Data Source=ASA 7.0 Sample"

    myConn.Open

    myConn.BeginTrans

    SQLString = "Select * from customer"

    myRS.Open SQLString, _

myConn, adOpenDynamic, adLockBatchOptimistic

    If myRS.BOF And myRS.EOF Then

        MsgBox "Recordset is empty!", _

        16, "Empty Recordset"

    Else

        MsgBox "Cursor type: " + _

        CStr(myRS.CursorType), vbInformation

        myRS.MoveFirst

        For i = 1 To 3

            MsgBox "Row: " + CStr(myRS.Fields("id")), _

             vbInformation

            If i = 2 Then

                myRS.Update "City", "Toronto"

                myRS.UpdateBatch

            End If

            myRS.MoveNext

        Next i

'        myRS.MovePrevious

        myRS.Close

    End If

    myConn.CommitTrans

    myConn.Close

End Sub

Notes

If you use the adLockBatchOptimistic setting on the recordset, the myRS.Update method does not make any changes to the database itself. Instead, it updates a local copy of the Recordset.

The myRS.UpdateBatch method makes the update to the database server, but does not commit it, because it is inside a transaction. If an UpdateBatch method was invoked outside a transaction, the change would be committed.

The myConn.CommitTrans method commits the changes. The Recordset object has been closed by this time, so there is no issue of whether the local copy of the data is changed or not.


Using transactions
  ASA Programming Interfaces Guide

    CHAPTER 5.  The OLE DB and ADO Programming Interfaces

      ADO programming with Adaptive Server Anywhere

 Using transactions

By default, any change you make to the database using ADO is committed as soon as it is executed. This includes explicit updates, as well as the UpdateBatch method on a Recordset. However, the previous section illustrated that you can use the BeginTrans and RollbackTrans or CommitTrans methods on the Connection object to use transactions.

Transaction isolation level is set as a property of the Connection object. The IsolationLevel property can take on one of the following values:

ADO isolation level	Constant	ASA level
Unspecified	adXactUnspecified	Not applicable. Set to 0
Chaos	adXactChaos	Unsupported. Set to 0
Browse	adXactBrowse	0
Read uncommitted	adXactReadUncommitted	0
Cursor stability	adXactCursorStability	1
Read committed	adXactReadCommitted	1
Repeatable read	adXactRepeatableRead	2
Isolated	adXactIsolated	3
Serializable	adXactSerializable	3
  For more information on isolation levels, see Isolation levels and consistency.


Supported OLE DB interfaces
  ASA Programming Interfaces Guide

    CHAPTER 5.  The OLE DB and ADO Programming Interfaces

 Supported OLE DB interfaces

The OLE DB API consists of a set of interfaces. The following table describes the support for each interface in the Adaptive Server Anywhere OLE DB driver.

Interface	Purpose	Limitations
IAccessor	Define bindings between client memory and data store values.	DBACCESSOR_PASSBYREF not supported. DBACCESSOR_OPTIMIZED not supported
IAlterIndex IAlterTable	Alter tables, indexes, and columns.	Not supported.
IChapteredRowset	A chaptered rowset allows rows of a rowset to be accessed in separate chapters.	Not supported. Adaptive Server Anywhere does not support chaptered rowsets.
IColumnsInfo	Get simple information about the columns of a rowset.	Not on CE.
IColumnsRowset	Get information about optional metadata columns in a rowset, and get a rowset of column metadata.	Not on CE.
ICommand	Execute SQL commands.	Does not support calling ICommandProperties::GetProperties with DBPROPSET_PROPERTIESINERROR to find properties that could not have been set.
ICommandPersist	Persist the state of a command object (but not any active rowsets). These persistent command objects can subsequently be enumerated using the PROCEDURES or VIEWS rowset.	Not on CE.
ICommandPrepare	Prepare commands.	Not on CE.
ICommandProperties	Set Rowset properties for rowsets created by a command. Most commonly used to specify the interfaces the rowset should support.	Supported.
ICommandText	Set the SQL command text for ICommand.	Only the DBGUID_DEFAULT SQL dialect is supported.
ICommandWithParameters	Set or get parameter information for a command.	No support for parameters stored as vectors of scalar values. No support for BLOB parameters. Not on CE.
IConvertType		Supported. Limited on CE.
IDBAsynchNotify IDBAsyncStatus	Asynchronous processing. Notify client of events in the asynchronous processing of data source initialization, populating rowsets, and so on.	Not supported.
IDBCreateCommand	Create commands from a session.	Supported.
IDBCreateSession	Create a session from a data source object	Supported..
IDBDataSourceAdmin	Create/destroy/modify data source objects, which are COM objects used by clients. This interface is not used to manage data stores (databases).	Not supported.
IDBInfo	Find information about keywords unique to this provider (that is, to find non-standard SQL keywords). Also, find information about literals, special characters used in text matching queries, and other literal information.	Not on CE.
IDBInitialize	Initialize data source objects and enumerators.	Not on CE.
IDBProperties	Manage properties on a data source object or enumerator.	Not on CE.
IDBSchemaRowset	Get information about system tables, in a standard form (a rowset).	Not on CE.
IErrorInfo IErrorLookup IErrorRecords	ActiveX error object support.	Not on CE.
IGetDataSource	Returns an interface pointer to the session's data source object.	Supported,
IIndexDefinition	Create or drop indexes in the data store.	Not supported.
IMultipleResults	Retrieve multiple results (rowsets or row counts) from a command.	Supported.
IOpenRowset	Non-SQL way to access a database table by its name.	Supported. Opening a table by its name is supported, not by a GUID.
IParentRowset	Access chaptered/hierarchical rowsets.	Not supported.
IRowset	Access rowsets.	Supported.
IRowsetChange	Allow changes to rowset data, reflected back to the data store. InsertRow/SetData for blobs not yet implemented	Not on CE.
IRowsetChapterMember	Access chaptered/hierarchical rowsets.	Not supported.
IRowsetCurrentIndex	Dynamically change the index for a rowset.	Not supported.
IRowsetFind	Find a row within a rowset matching a specified value.	Not supported.
IRowsetIdentity	Compare row handles.	Not supported.
IRowsetIndex	Access database indexes.	Not supported.
IRowsetInfo	Find information about a rowset properties, or to find the object that created the rowset.	Not on CE.
IRowsetLocate	Position on rows of a rowset, using bookmarks.	Not on CE.
IRowsetNotify	Provides a COM callback interface for rowset events.	Supported.
IRowsetRefresh	Get the latest value of data that is visible to a transaction.	Not supported.
IRowsetResynch	Old OLEDB 1.x interface, superseded by IRowsetRefresh.	Not supported.
IRowsetScroll	Scroll through rowset to fetch row data.	Not supported.
IRowsetUpdate	Delay changes to rowset data until Update is called.	Supported. Not on CE.
IRowsetView	Use views on an existing rowset.	Not supported.
ISequentialStream	Retrieve a blob column.	Supported for reading only. No support for SetData with this interface. Not on CE.
ISessionProperties	Get session property information.	Supported.
ISourcesRowset	Get a rowset of data source objects and enumerators.	Not on CE.
ISQLErrorInfo ISupportErrorInfo	ActiveX error object support.	Optional on CE.
ITableDefinition ITableDefinitionWithConstraints	Create, drop, and alter tables, with constraints.	Not on CE.
ITransaction	Commit or abort transactions.	Not all the flags are supported. Not on CE.
ITransactionJoin	Support distributed transactions.	Not all the flags are supported. Not on CE.
ITransactionLocal	Handle transactions on a session. Not all the flags are supported.	Not on CE.
ITransactionOptions	Get or set options on a transaction.	Not on CE.
IViewChapter	Work with views on an existing rowset, specifically to apply post-processing filters/sorting on rows.	Not supported.
IViewFilter	Restrict contents of a rowset to rows matching a set of conditions.	Not supported.
IViewRowset	Restrict contents of a rowset to rows matching a set of conditions, when opening a rowset.	Not supported.
IViewSort	Apply sort order to a view.	Not supported.


CHAPTER 6.  The Open Client Interface
  ASA Programming Interfaces Guide

 CHAPTER 6.  The Open Client Interface

About this chapter

This chapter describes the Open Client programming interface for Adaptive Server Anywhere.

The primary documentation for Open Client application development is the Open Client documentation, available from Sybase. This chapter describes features specific to Adaptive Server Anywhere, but it is not an exhaustive guide to Open Client application programming.

 What you need to build Open Client applications

 Data type mappings

 Using SQL in Open Client applications

 Known Open Client limitations of Adaptive Server Anywhere


What you need to build Open Client applications
  ASA Programming Interfaces Guide

    CHAPTER 6.  The Open Client Interface

 What you need to build Open Client applications

To run Open Client applications, you must install and configure Open Client components on the machine where the application is running. You may have these components present as part of your installation of other Sybase products, or you can optionally install these libraries with Adaptive Server Anywhere, subject to the terms of your license agreement.

Open Client applications do not need any Open Client components on the machine where the database server is running.

To build Open Client applications, you need the development version of Open Client, available from Sybase.

By default, Adaptive Server Anywhere databases are created as case-insensitive, while Adaptive Server Enterprise databases are case sensitive.

  For more information on running Open Client applications with Adaptive Server Anywhere, see Adaptive Server Anywhere as an Open Server.


Data type mappings
  ASA Programming Interfaces Guide

    CHAPTER 6.  The Open Client Interface

 Data type mappings

Open Client has its own internal data types, which differ in some details from those available in Adaptive Server Anywhere. For this reason, Adaptive Server Anywhere internally maps some data types between those used by Open Client applications and those available in Adaptive Server Anywhere.

To build Open Client applications, you need the development version of Open Client. To use Open Client applications, the Open Client runtimes must be installed and configured on the computer where the application runs.

The Adaptive Server Anywhere server does not require any external communications runtime in order to support Open Client applications.

Each Open Client data type is mapped onto the equivalent Adaptive Server Anywhere data type. All Open Client data types are supported

Adaptive Server Anywhere data types with no direct counterpart in Open Client

The following table lists the mappings of data types supported in Adaptive Server Anywhere that have no direct counterpart in Open Client.

ASA data type	Open Client data type
unsigned short	int
unsigned int	bigint
unsigned bigint	bigint
date	smalldatetime
time	smalldatetime
serialization	longbinary
java	longbinary
string	varchar
timestamp struct	datetime
 Range limitations in data type mapping


Range limitations in data type mapping
  ASA Programming Interfaces Guide

    CHAPTER 6.  The Open Client Interface

      Data type mappings

 Range limitations in data type mapping

Some data types have different ranges in Adaptive Server Anywhere than in Open Client. In such cases, overflow errors can occur during retrieval or insertion of data.

The following table lists Open Client application data types that can be mapped to Adaptive Server Anywhere data types, but with some restriction in the range of possible values.

In most cases, the Open Client data type is mapped to a Adaptive Server Anywhere data type that has a greater range of possible values. As a result, it is possible to pass a value to Adaptive Server Anywhere that will be accepted and stored in a database, but one that is too large to be fetched by an Open Client application.

Data type	Open Client lower range	Open Client upper range	ASA lower range	ASA upper range
MONEY	-922 377 203 685 477.5808	922 377 203 685 477.5807	-1e15 + 0.0001	1e15 - 0.0001
SMALLMONEY	-214 748.3648	214 748.3647	-214 748.3648	214 748.3647
DATETIME	Jan 1, 1753	Dec 31, 9999	Jan 1, 0001	Dec 31, 9999
SMALLDATETIME	Jan 1, 1900	June 6, 2079	March 1, 1600	Dec 31, 7910
Example

For example, the Open Client MONEY and SMALLMONEY data types do not span the entire numeric range of their underlying Adaptive Server Anywhere implementations. Therefore it is possible to have a value in a Adaptive Server Anywhere column which exceeds the boundaries of the Open Client data type MONEY. When the client fetches any such offending values via Adaptive Server Anywhere, an error is generated.

Timestamps

The Adaptive Server Anywhere implementation of the Open Client TIMESTAMP data type, when such a value is passed in Adaptive Server Anywhere is different from that of Adaptive Server Enterprise. In Adaptive Server Anywhere the value is mapped to the Adaptive Server Anywhere DATETIME data type. The default value is NULL in Adaptive Server Anywhere, and no guarantee is made of its uniqueness. By contrast, Adaptive Server Enterprise ensures that the value is monotonically increasing in value, and so is unique.

By contrast, the Adaptive Server Anywhere TIMESTAMP data type contains year, month, day, hour, minute, second, and fraction of second information. In addition, the DATETIME data type has a greater range of possible values than the Open Client data types that are mapped to it by Adaptive Server Anywhere.


Using SQL in Open Client applications
  ASA Programming Interfaces Guide

    CHAPTER 6.  The Open Client Interface

 Using SQL in Open Client applications

This section provides a very brief introduction to using SQL in Open Client applications, with a particular focus on Adaptive Server Anywhere-specific issues.

  For an introduction to the concepts, see Using SQL in Applications. For a complete description, see your Open Client documentation.

 Executing SQL statements

 Using prepared statements

 Using cursors

 Describing query results in Open Client


Executing SQL statements
  ASA Programming Interfaces Guide

    CHAPTER 6.  The Open Client Interface

      Using SQL in Open Client applications

 Executing SQL statements

You send SQL statements to a database by including them in Client Library function calls. For example, the following pair of calls executes a DELETE statement:

ret = ct_command(cmd, CS_LANG_CMD,

                  "DELETE FROM employee

                   WHERE emp_id=105"

                 CS_NULLTERM,

                 CS_UNUSED);

ret = ct_send(cmd);

The ct_command function is used for a wide range of purposes.


Using prepared statements
  ASA Programming Interfaces Guide

    CHAPTER 6.  The Open Client Interface

      Using SQL in Open Client applications

 Using prepared statements

The ct_dynamic function is used to manage prepared statements. This function takes a type parameter which describes the action you are taking.

♦ To use a prepared statement in Open Client:

1	Prepare the statement using the ct_dynamic function, with a CS_PREPARE type parameter.

2	Set statement parameters using ct_param.

3	Execute the statement using ct_dynamic with a CS_EXECUTE type parameter.

4	Free the resources associated with the statement using ct_dynamic with a CS_DEALLOC type parameter.

  For more information on using prepared statements in Open Client, see your Open Client documentation


Using cursors
  ASA Programming Interfaces Guide

    CHAPTER 6.  The Open Client Interface

      Using SQL in Open Client applications

 Using cursors

The ct_cursor function is used to manage cursors. This function takes a type parameter which describes the action you are taking.

Supported cursor types

Not all the types of cursor that Adaptive Server Anywhere supports are available through the Open Client interface. You cannot use scroll cursors, dynamic scroll cursors, and insensitive cursors through Open Client.

Uniqueness and updateability are two properties of cursors. Cursors can be unique (each row carries primary key or uniqueness information, regardless of whether it is used by the application) or not. Cursors can be read only or updateable. If a cursor is updateable and not unique, performance may suffer, as no prefetching of rows is done in this case, regardless of the CS_CURSOR_ROWS setting (see below).

The steps in using cursors

In contrast to some other interfaces, such as Embedded SQL, Open Client associates a cursor with a SQL statement expressed as a string. Embedded SQL first prepares a statement, and then the cursor is declared using the statement handle.

♦ To use cursors in Open Client:

1	To declare a cursor in Open Client, you use ct_cursor with CS_CURSOR_DECLARE as the type parameter.

2	After declaring a cursor, you can control how many rows are prefetched to the client side each time a row is fetched from the server by using ct_cursor with CS_CURSOR_ROWS as the type parameter.

Storing prefetched rows at the client side cuts down the number of calls to the server, and this improves overall throughput as well as turnaround time. Prefetched rows are not immediately passed on to the application, they are stored in a buffer at the client side ready for use.

The setting of the PREFETCH database option controls prefetching of rows for other interfaces. It is ignored by Open Client connections. The CS_CURSOR_ROWS setting is ignored for non-unique, updateable cursors.

3	To open a cursor in Open Client, you use ct_cursor with CS_CURSOR_OPEN as the type parameter.

4	To fetch each row in to the application, you use ct_fetch.

5	To close a cursor, you use ct_cursor with CS_CURSOR_CLOSE.

6	In Open Client, you also need to deallocate the resources associated with a cursor. You do this using ct_cursor with CS_CURSOR_DEALLOC. You can also use CS_CURSOR_CLOSE with the additional parameter CS_DEALLOC to carry out these operations in a single step.

 Modifying rows through a cursor


Modifying rows through a cursor
  ASA Programming Interfaces Guide

    CHAPTER 6.  The Open Client Interface

      Using SQL in Open Client applications

        Using cursors

 Modifying rows through a cursor

With Open Client, you can delete or update rows in a cursor, as long as the cursor is for a single table. The user must have permissions to update the table and the cursor must be marked for update.

♦ To modify rows through a cursor:

•	Instead of carrying out a fetch, you can delete or update the current row of the cursor using ct_cursor with CS_CURSOR_DELETE or CS_CURSOR_UPDATE, respectively.

You cannot insert rows through a cursor in Open Client applications.


Describing query results in Open Client
  ASA Programming Interfaces Guide

    CHAPTER 6.  The Open Client Interface

      Using SQL in Open Client applications

 Describing query results in Open Client

Open Client handles result sets in a different way from some other Adaptive Server Anywhere interfaces.

In Embedded SQL and ODBC, you describe a query or stored procedure in order to set up the proper number and types of variables to receive the results. The description is done on the statement itself.

In Open Client, you do not need to describe a statement. Instead, each row returned from the server can carry a description of its contents. If you use ct_command and ct_send to execute statements, you can use the ct_results function to handle all aspects of rows returned in queries.

If you do not wish to use this row-by-row method of handling result sets, you can use ct_dynamic to prepare a SQL statement, and use ct_describe to describe its result set. This corresponds more closely to the describing of SQL statements in other interfaces.


Known Open Client limitations of Adaptive Server Anywhere
  ASA Programming Interfaces Guide

    CHAPTER 6.  The Open Client Interface

 Known Open Client limitations of Adaptive Server Anywhere

Using the Open Client interface, you can use an Adaptive Server Anywhere database in much the same way as you would an Adaptive Server Enterprise database. There are some limitations, including the following:

•	Commit Service    Adaptive Server Anywhere does not support the Adaptive Server Enterprise Commit Service.

•	Capabilities    A client/server connection's capabilities determine the types of client requests and server responses permitted for that connection. The following capabilities are not supported:

•	CS_REG_NOTIF

•	CS_CSR_ABS

•	CS_CSR_FIRST

•	CS_CSR_LAST

•	CS_CSR_PREV

•	CS_CSR_REL

•	CS_DATA_BOUNDARY

•	CS_DATA_SENSITIVITY

•	CS_PROTO_DYNPROC

•	CS_REQ_BCP

•	Security options, such as SSL and encrypted passwords, are not supported.

•	Open Client applications may connect to Adaptive Server Anywhere using TCP/IP, or using local machine NamedPipes protocol, where available.

  For more information on capabilities, see the Open Server Server-Library C Reference Manual.