Microsoft Programmer's Library 1.3

home *** CD-ROM | disk | FTP | other *** search

/ Microsoft Programmer's Library 1.3 / Microsoft_Programmers_Library.7z / MPL / net / sqlpgmr.txt < prev

Wrap

Text File | 2013-11-08 | 428.4 KB | 15,236 lines

Microsoft(R) SQL Server - Programmer's Reference - for C ──────────────────────────────────────────────────────────────────────────── Microsoft(R) SQL Server - Programmer's Reference - for C The SYBASE(R) SQL Server database for PC networks VERSION 1.1 ──────────────────────────────────────────────────────────────────────────── for the MS(R) OS/2 Operating System Microsoft Corporation Information in this document is subject to change without notice and does not represent a commitment on the part of Microsoft Corporation. The software described in this document is furnished under a license agreement or nondisclosure agreement. The software may be used or copied only in accordance with the terms of the agreement. It is against the law to copy the software on any medium except as specifically allowed in the license or nondisclosure agreement. No part of this manual may be reproduced or transmitted in any form or by any means, electronic or mechanical, including photocopying and recording, for any purpose without the express written permission of Microsoft Corporation. (C) 1990 Microsoft Corporation and SYBASE, Inc. All rights reserved. Printed in the USA. Microsoft, MS, MS-DOS, and the Microsoft logo are registered trademarks of Microsoft Corporation. Windows is a trademark of Microsoft Corporation. IBM is a registered trademark of International Business Machines Corporation. SYBASE is a registered trademark of SYBASE, Inc. TRANSACT-SQL and DB-LIBRARY are trademarks of SYBASE, Inc. Document Number: SY10232-0290 OEM-D/0788-1Z Table of Contents ──────────────────────────────────────────────────────────────────────────── Before You Begin Manual Overview How to Use This Guide Notational Conventions Finding Further Information Chapter 1 Overview of DB-LIBRARY Introduction TRANSACT-SQL Applications for DB-LIBRARY Comparing the Library Approach to Embedded SQL Communicating with SQL Server DB-LIBRARY Programming DB-LIBRARY Datatypes Finding SQL Servers on the Network DB-LIBRARY Functions and Macros Initialization Command Processing Results Processing Error and Message Handling Information Retrieval Browse Mode Text and Image Handling Datatype Conversion Cleanup Miscellaneous Functions Bulk Copy Special Library Two-Phase Commit Service Special Library Chapter 2 Functions and Macros Introduction dbadata dbadlen dbaltbind dbaltcolid dbaltlen dbaltop dbalttype dbbind dbbylist dbcancel dbcanquery dbchange dbclose dbclrbuf dbclropt dbcmd DBCMDROW dbcolbrowse dbcollen dbcolname dbcolsource dbcoltype dbconvert DBCOUNT DBCURCMD DBCURROW dbdata dbdataready dbdatlen DBDEAD dberrhandle dbexit dbfcmd DBFIRSTROW dbfreebuf dbfreelogin dbfreequal dbgetchar dbgetmaxprocs dbgetoff dbgetrow DBGETTIME DBISAVAIL dbinit dbisopt DBLASTROW DBLOCKLIB (Windows only) dblogin DBMORECMDS dbmoretext dbmsghandle dbname dbnextrow dbnumalts dbnumcols dbnumcompute DBNUMORDERS dbopen dbordercol dbprhead dbprrow dbprtype dbqual DBRBUF dbreadpage dbresults DBROWS DBROWTYPE dbsetavail DBSETLAPP DBSETLHOST dbsetlogintime DBSETLPWD DBSETLUSER dbsetmaxprocs dbsetnull dbsetopt dbsettime dbsqlexec dbsqlok dbsqlsend dbstrcpy dbstrlen dbtabbrowse dbtabcount dbtabname dbtabsource dbtsnewlen dbtsnewval dbtsput dbtxptr dbtxtimestamp dbtxtsnewval dbtxtsput DBUNLOCKLIB (Windows only) dbuse dbwillconvert dbwinexit (Windows only) dbwritepage dbwritetext Chapter 3 Example Programs Introduction Examples Sending Queries in a Command Batch Working with Data Files Binding Aggregate and Compute Results Using Row Buffering Converting Data with dbconvert Querying and Updating the Database Using Browse-Mode Functions Chapter 4 Bulk Copy Special Library Introduction bcp_batch bcp_bind bcp_colfmt bcp_collen bcp_colptr bcp_columns bcp_control bcp_done bcp_exec bcp_init bcp_moretext bcp_sendrow BCP_SETL Chapter 5 Two-Phase Commit Special Library Programming Distributed Transactions The Commit Service and the Application Program The Probe Process The Two-Phase Commit Functions Specifying the Commit Server Two-Phase Commit Example Program abort_xact build_xact_string close_commit commit_xact open_commit remove_xact scan_xact start_xact stat_xact Appendix A Functions and Macros DB-LIBRARY Functions and Macros Bulk Copy Functions Two-Phase Commit Functions Appendix B DB-LIBRARY Options Introduction Options Appendix C DB-LIBRARY Datatypes Introduction Datatypes SQL Type Definitions Appendix D Error Messages Introduction Errors Error Severity Levels Appendix E Building Applications Building MS-DOS Applications Terminate and Stay Resident Utility Libraries Include Files Required System Libraries Compiling and Linking Considerations Increasing the Number of SQL Server Connections Sample Program Building MS OS/2 Applications Libraries Include Files Special Considerations Compiling and Linking Considerations Sample Programs Building an Application for Windows Libraries Include Files Special Windows DB-LIBRARY Functions Special Considerations for Windows DB-LIBRARY Applications Increasing the Number of Allowable Open Files Sample Program Appendix F Finding SQL Servers on the Network Appendix G Relative Sizes of Library Routines Static Link Library Memory Costs DB-LIBRARY Core Routines Relative Memory Sizes Setting Up the LOGINREC Establishing an SQL Server Connection Setting the Current Database Building a Command Batch Accessing a Command Batch Executing a Command Batch Setting and Clearing Command Options Setting Up the Results Getting Result Data Accessing Result Rows Canceling Results Setting Response Time for Results Error and Message Handling Regular Result Column Information Compute Result Column Information Row Buffer Information Command State Information Miscellaneous Information Browse Mode Text and Image Handling Datatype Conversion Cleanup Miscellaneous Functions Bulk Copy Two-Phase Commit Modularity Index Before You Begin ──────────────────────────────────────────────────────────────────────────── Manual Overview This manual describes DB-LIBRARY(tm), a set of C routines and macros that allows your application to interact with SQL Server. The manual is written for programmers who are developing SQL Server applications. Before using this manual, you should be familiar with TRANSACT-SQL(tm), which is described in SQL Server Learning TRANSACT-SQL and in the SQL Server Language Reference. How to Use This Guide The following topics are covered in this manual: ──────────────────────────────────────────────────────────────────────────── Chapter 1 A summary of all DB-LIBRARY functions and macros grouped by category Chapter 2 DB-LIBRARY functions and macros Chapter 3 Sample programs Chapter 4 Bulk copy functions Chapter 5 Two-phase commit service functions Appendix A An alphabetical listing of functions and macros Appendix B DB-LIBRARY options Appendix C DB-LIBRARY datatypes and definitions Appendix D DB-LIBRARY errors and error severity levels Appendix E Building applications Appendix F Finding SQL Servers on the network Appendix G Relative sizes of DB-LIBRARY routines ──────────────────────────────────────────────────────────────────────────── Notational Conventions Throughout this manual, the following conventions are used to distinguish elements of text: ╓┌─────────────────────────────────┌───────────────────────────────────────── Convention Purpose ──────────────────────────────────────────────────────────────────────────── UPPERCASE Represents statement and clause names, Convention Purpose ──────────────────────────────────────────────────────────────────────────── UPPERCASE Represents statement and clause names, functions, macros, and any other portions of syntax that must appear exactly as shown. SMALL CAPS Represent key names such as CTRL. bold Represents stored procedures, system procedures, triggers, defaults, rules, utility programs, and commands. italic Represents database names, table names, view names, column names, datatypes, index names, pathnames, filenames, and variables that appear in text. monospace Represents examples, screen output, program code, and error messages. Convention Purpose ──────────────────────────────────────────────────────────────────────────── [brackets] Enclose optional items. Type only the information within the brackets, not the brackets themselves. {braces} Enclose required items. Type only the information within the braces, not the braces themselves. | (vertical bar) Separates items inside a set of braces or brackets. The vertical bar means you must choose one and only one item. ... (ellipsis) Means that you can repeat the previous item as many times as you like. <execute> Executes one or more SQL statements. (In the SQL Server Administration facility, SQL statements are executed by pressing Convention Purpose ──────────────────────────────────────────────────────────────────────────── SQL statements are executed by pressing the CONTROL+E keys. In the isql program, SQL statements are executed with the go command.) ──────────────────────────────────────────────────────────────────────────── Finding Further Information The following manuals describe SQL Server and are included in the standard documentation set: ──────────────────────────────────────────────────────────────────────────── SQL Server Installation Guide A guide to installing and setting up SQL Server SQL Server Learning TRANSACT-SQL A guide to learning and using TRANSACT-SQL SQL Server System Administrator's Guide A guide to managing SQL Server and to using the SQL Server Administration Facility (SAF) for database queries SQL Server Language Reference A reference to the syntax of all TRANSACT-SQL statements, commands, procedures, and utilities SQL Server Quick Reference A quick reference guide to TRANSACT-SQL ──────────────────────────────────────────────────────────────────────────── Chapter 1 Overview of DB-LIBRARY ──────────────────────────────────────────────────────────────────────────── Introduction DB-LIBRARY is a set of C functions and macros that allows your application to interact with SQL Server. It includes functions that send TRANSACT-SQL statements to SQL Server and functions that process the results of those statements. Other functions handle errors, perform data conversion, and provide a variety of information about the interaction with SQL Server. TRANSACT-SQL TRANSACT-SQL is the enhanced SQL Server version of the Structured Query Language (SQL) database language. The application uses TRANSACT-SQL to communicate with SQL Server. TRANSACT-SQL provides statements for creating and manipulating database objects, as well as for inserting, updating, and selecting data. The TRANSACT-SQL enhancements include data integrity features and stored procedures. Stored procedures allow much of an application's processing logic to be shifted from the front-end application to SQL Server. They can contain most TRANSACT-SQL statements, including TRANSACT-SQL control-of-flow statements. Furthermore, stored procedures are precompiled, so the statements don't have to be parsed each time the procedure is executed. Applications for DB-LIBRARY DB-LIBRARY enables the database to become an integral part of an application. TRANSACT-SQL statements can be incorporated into the application, allowing the application to retrieve and update values from a database. Through DB-LIBRARY, values from the database can be placed in program variables for manipulation by the application. Conversely, values in program variables can be inserted into the database. Although DB-LIBRARY contains a large number of functions, giving the application much control over its interaction with SQL Server, most applications require only a few of the functions. The actual process of connecting with SQL Server, sending TRANSACT-SQL statements to SQL Server, and processing the resulting data is simple and straightforward. Comparing the Library Approach to Embedded SQL The DB-LIBRARY approach is distinctly different from the "embedded SQL" type of language interface that some other database management systems provide. In contrast to an embedded SQL interface, the DB-LIBRARY interface does not require a language precompiler. Avoiding preprocessing in this way makes applications involving a database more straightforward to write and to debug. The DB-LIBRARY functions are C functions and are not preprocessed into some intermediate form. Communicating with SQL Server DB-LIBRARY functions communicate with SQL Server through the DBPROCESS structure. The dbopen function allocates and initializes a DBPROCESS when it logs on to SQL Server. The DBPROCESS serves as the connection between the application and SQL Server. Most DB-LIBRARY functions require a DBPROCESS as the first parameter. An application may have more than one DBPROCESS if, for instance, it needs to perform updates to a database while it is still processing the results of a previous query. Each DBPROCESS is completely independent of any other DBPROCESS. The DBPROCESS structure points to a command buffer that contains TRANSACT-SQL statements for transmission to SQL Server. It also points to result rows returned from SQL Server─either single or multiple rows or a buffer of rows if buffering has been specified. The DBPROCESS also contains information on various aspects of SQL Server interaction. Many of the DB-LIBRARY functions extract information from the DBPROCESS. Applications access and manipulate components of the DBPROCESS structure only through DB-LIBRARY functions, not directly. One other structure of importance is the LOGINREC. This is the login record that contains information the dbopen function uses to log on to SQL Server. It contains typical login information, such as the username and the password. This information can be specified through DB-LIBRARY functions. ──────────────────────────────────────────────────────────────────────────── NOTE Under Microsoft(R) Operating System/2 (MS(R) OS/2), each DB process is a separate execution thread. This thread is spawned when the connection to SQL Server is established and terminated when the connection is closed. ──────────────────────────────────────────────────────────────────────────── DB-LIBRARY Programming Programming with DB-LIBRARY typically involves a few basic steps: 1. Logging on to SQL Server. 2. Placing TRANSACT-SQL statements into a buffer and sending them to SQL Server. 3. Processing the results, if any, returned from SQL Server, one statement at a time and one row at a time. The results can be placed in program variables, where the application can manipulate them. 4. Handling DB-LIBRARY errors and SQL Server messages. 5. Closing the connection with SQL Server. The following example shows the basic framework of many DB-LIBRARY applications. The program opens a connection to SQL Server, sends a TRANSACT-SQL SELECT statement to SQL Server, and processes the set of rows resulting from the SELECT. For brevity's sake, this program does not include the error or message handling functions; those functions are illustrated in the examples in Chapter 3, "Example Programs." For information about defining the target operating system prior to compiling your application, see Appendix E, "Building Applications." #include <sqlfront.h> #include <sqldb.h> /* Forward declarations of the error handler and message handler. */ int err_handler(); int msg_handler(); main() { DBPROCESS *dbproc; /* The connection with SQL Server. */ LOGINREC *login; /* The login information. */ DBCHAR name[40]; DBCHAR city[20]; /* ** Install user-supplied error- and message-handling functions. ** The code for these is omitted from this example for conciseness. */ dberrhandle(err_handler); dbmsghandle(msg_handler); /* Get a LOGINREC. */ login = dblogin(); DBSETLUSER(login, "user"); DBSETLPWD(login, "my_passwd"); DBSETLAPP(login, "example"); /* Get a DBPROCESS structure for communication with SQL Server. */ dbproc = dbopen(login, "my_server"); /* ** Retrieve some columns from the "authors" table in the ** "pubs" database. */ /* First, put the command into the command buffer. */ dbcmd(dbproc, "select au_lname, city from pubs..authors"); dbcmd(dbproc, " where state = 'CA' "); /* Send the command to SQL Server and start execution. */ dbsqlexec(dbproc); /* Process the command. */ if (dbresults(dbproc) == SUCCEED) { /* Bind results to program variables. */ dbbind(dbproc, 1, STRINGBIND, (DBINT) 0, name); dbbind(dbproc, 2, STRINGBIND, (DBINT) 0, city); /* Retrieve and print the result rows. */ while (dbnextrow(dbproc) != NO_MORE_ROWS) { printf ("%s: %s\n", name, city); } } /* Close the connection to SQL Server. */ dbexit(); } The example illustrates features common to most DB-LIBRARY applications. These features are header files Two header files, sqlfront.h and sqldb.h, are required in all source files that contain calls to DB-LIBRARY functions. The sqlfront.h file must appear first in the file. It defines symbolic constants, such as function return values, described in Chapter 2, "Functions and Macros," and the exit values STDEXIT and ERREXIT. These exit values can be used as the parameter for the C standard library function EXIT. Since they are defined appropriately for the operating system running the program, their use provides a system-independent approach to exiting the program. The sqlfront.h file also includes typedefs for datatypes that can be used in program variable declarations. These datatypes are described in Appendix C, "DB-LIBRARY Datatypes." The sqldb.h file contains additional definitions and typedefs, most of which are meant to be used only by the DB-LIBRARY functions. They should not be directly accessed by the program. To ensure compatibility with future releases of DB-LIBRARY, use the contents of sqldb.h only as documented in this manual. dberrhandle and dbmsghandle The first of these DB-LIBRARY functions, dberrhandle, installs a user-supplied error-handling function, which is called automatically whenever the application encounters a DB-LIBRARY error. Similarly, dbmsghandle installs a message-handling function, which is called in response to informational or error messages returned from SQL Server. The error- and message-handling functions are user-supplied. Sample handlers have not been supplied with the example provided at the beginning of this section but are included at the end of each example program in Chapter 3, "Example Programs." dblogin This function supplies a LOGINREC structure, which DB-LIBRARY uses to log in to SQL Server. Two macros set certain components of the LOGINREC. DBSETLPWD sets the password that DB-LIBRARY uses when logging in. DBSETLAPP sets the name of the application, which appears in SQL Server's sysprocess table. Functions are available for setting other aspects of the LOGINREC. The LOGINREC contains default values for each of the values they set. dbopen This function opens a connection between the application and SQL Server. It uses the LOGINREC supplied by dblogin to log in to the server. It returns a DBPROCESS structure, which serves as the conduit for information between the application and the server. After this function has been called, the application is connected with SQL Server and can send TRANSACT-SQL statements to SQL Server and process results. dbcmd This function fills the command buffer with TRANSACT-SQL statements, which can then be sent to SQL Server. Each succeeding call to dbcmd adds the supplied text to the end of any text already in the buffer. It is the programmer's responsibility to supply necessary blanks between words, such as the blank at the beginning of the text in the second dbcmd call in the example. Multiple statements can be included in the buffer. This example only shows how to send and process a single statement, but DB-LIBRARY allows an application to send multiple statements to SQL Server and process each statement's set of results separately. dbsqlexec This function executes the command buffer; that is, it sends the contents of the buffer to SQL Server, which parses and executes the commands. dbresults This function gets the results of the current TRANSACT-SQL statement ready for processing. In this example, the buffer contains only a single statement, so dbresults is called only once. If the buffer contained multiple statements, the program would need to call dbresults once for each statement in the buffer. dbbind This function binds result columns to program variables. In the example, the first call to dbbind binds the first result column to the name variable. In other words, when the program reads a result row by calling dbnextrow, the contents of the first column in the result row is placed in the name variable. The datatype of the binding is "STRINGBIND", one of several binding types available for character data. The second call binds the second result column to the city variable. dbnextrow This function reads a row and places the results in the program variables specified by the earlier dbbind calls. Each successive call to dbnextrow reads another result row until the last row has been read and NO_MORE_ROWS is returned. Processing of the results must take place inside the dbnextrow loop because each call to dbnextrow overwrites the previous values in the program variables. Even if you know that the query returns only one row, you still must call dbnextrow twice: once to get the result row and once to get the NO_MORE_ROWS indication. dbexit This function closes all SQL Server connections and frees all DBPROCESS structures created as a result of your program. It is usually the last DB-LIBRARY function in the program. Although DB-LIBRARY contains a great number of functions, much can be accomplished with just the functions shown in this example. DB-LIBRARY Datatypes DB-LIBRARY defines datatypes for SQL Server data. These datatypes begin with "SQL" (for example, SQLINT4, SQLCHAR, SQLMONEY). DB-LIBRARY also provides typedefs for use in program variable declarations. These typedefs begin with the prefix "DB" (for example, DBINT, DBCHAR, DBMONEY, and so on). By using them, you ensure that your program variables will be compatible with SQL Server datatypes. For a list of SQL Server datatypes and the DB-LIBRARY program variable types, see Appendix C, "DB-LIBRARY Datatypes." The dbconvert function provides a way to convert data from one SQL Server datatype to another. It supports conversion between most datatypes. Since the SQL Server datatypes correspond directly to the DB-LIBRARY datatypes, you can use dbconvert widely within your application. The functions that bind SQL Server result columns to program variables─dbbind and dbaltbind─also provide type conversion. Finding SQL Servers on the Network Information on all SQL Servers is contained in the workstation's LAN Manager table, which is updated whenever an SQL Server is added or deleted from the network. For information on locating an SQL Server on the network from an application program, see Appendix F, "Finding SQL Servers on the Network." DB-LIBRARY Functions and Macros DB-LIBRARY functions and macros handle a large variety of tasks. These tasks are divided into a number of categories: ■ Initialization ■ Command processing ■ Results processing ■ Error and message handling ■ Information retrieval ■ Browse mode ■ Text and image handling ■ Datatype conversion ■ Cleanup ■ Miscellaneous Chapter 2, "Functions and Macros" describes the functions and macros in individual reference pages. They all begin with the prefix "db". Functions are spelled with lowercase letters. Macros are spelled with uppercase letters. Macros are provided for compatibility with other SYBASE(R) platforms only. All macros map directly to functions in this release. In addition, DB-LIBRARY includes two special libraries: ■ Bulk Copy ■ Two-Phase Commit Service Chapter 4, "Bulk Copy Special Library," and Chapter 5, "Two-Phase Commit Special Library," describe the functions in these libraries. The bulk copy functions begin with the prefix "bcp_". The two-phase commit service functions have no standard prefix. Initialization The functions in this section set up and define the connection between the application program and SQL Server. They handle such tasks as allocating and defining a LOGINREC structure, opening a connection to SQL Server, and allocating a DBPROCESS structure. Only a few of the functions are absolutely necessary in every DB-LIBRARY program; in particular, an application requires dblogin and dbopen. The following sections specify the initialization functions. The sections are in the approximate order in which a program is likely to call them. Setting Up the LOGINREC These functions and macros allocate and define the LOGINREC structure, which can then be used to open a connection to SQL Server: ──────────────────────────────────────────────────────────────────────────── dbfreelogin Frees a LOGINREC. dblogin Allocates a LOGINREC for subsequent use by dbopen. DBSETLAPP Sets the application name in the LOGINREC. DBSETLHOST Sets the workstation name in the LOGINREC. DBSETLPWD Sets the SQL Server password in the LOGINREC. DBSETLUSER Sets the SQL Server username in the LOGINREC. ──────────────────────────────────────────────────────────────────────────── Establishing an SQL Server Connection These functions connect the application to a particular SQL Server: ──────────────────────────────────────────────────────────────────────────── dbopen Sets up communication with the network, logs in to SQL Server using the LOGINREC, initializes any options specified in the LOGINREC, and allocates a DBPROCESS. An application can open multiple connections to SQL Server, each with its own DBPROCESS. dbsetlogintime Sets the number of seconds DB-LIBRARY waits for SQL Server to respond to a request by dbopen for a DBPROCESS connection. ──────────────────────────────────────────────────────────────────────────── Setting the Current Database Once the application has been connected to an SQL Server, the database context can be changed. ──────────────────────────────────────────────────────────────────────────── dbuse Sets the current database. This function is equivalent to the TRANSACT-SQL USE statement and can be called repeatedly in an application. ──────────────────────────────────────────────────────────────────────────── Command Processing The application communicates with SQL Server through TRANSACT-SQL statements. It enters the statements into a command buffer, which the DBPROCESS points to. The application can place multiple statements in the command buffer; the set of statements in the buffer is known as the command batch. The application then sends the command batch to SQL Server, which executes the statements in the order entered in the buffer. Building the Command Batch These functions add statements to the buffer or clear the buffer: ──────────────────────────────────────────────────────────────────────────── dbcmd Adds text to the command buffer. It may be called repeatedly to add multiple statements or parts of statements. The text added with each successive call is concatenated to the previous text. dbfcmd Adds text to the command buffer using sprintf-type formatting. This function is the same as dbcmd except that it allows parameters to be substituted into the text. dbfreebuf Clears the command buffer. The command buffer is automatically cleared before a batch of statements is entered. To clear it at other times or when the DBNOAUTOFREE option has been set, use dbfreebuf. ──────────────────────────────────────────────────────────────────────────── Accessing the Command Batch These functions can be used to examine and copy parts of the command buffer: ──────────────────────────────────────────────────────────────────────────── dbgetchar Returns a pointer to a particular character in the command buffer. dbstrcpy Copies a portion of the command buffer to a program variable. This function is particularly valuable for debugging because it can tell you exactly what was sent to SQL Server. dbstrlen Returns the length of the contents of the command buffer. ──────────────────────────────────────────────────────────────────────────── Executing the Command Batch Once TRANSACT-SQL statements have been entered in the buffer, they can be sent to SQL Server for execution. ──────────────────────────────────────────────────────────────────────────── dbdataready Determines whether database command processing has completed. dbsqlexec Sends the contents of the command buffer to SQL Server for execution. Once dbsqlexec has returned SUCCEED, dbresults must be called to process the results. Calling dbsqlexec is equivalent to calling dbsqlsend followed by dbsqlok. dbsqlok Verifies the accuracy of a command batch sent to SQL Server. This function must be called after a successful return from dbsqlsend. Once dbsqlok has returned SUCCEED, dbresults must be called to process the results. dbsqlsend Sends the contents of the command buffer to SQL Server for execution. Unlike dbsqlexec, this function does not wait for a response from SQL Server. When dbsqlsend has returned SUCCEED, dbsqlok must be called to verify the accuracy of the command batch. ──────────────────────────────────────────────────────────────────────────── Setting and Clearing Command Options The application can set a number of SQL Server and DB-LIBRARY command options. Among them are DBPARSEONLY, which causes SQL Server to parse but not execute the command batch, and DBBUFFER, which provides buffering of result rows. For a list of all available options, see Appendix B, "DB-LIBRARY Options." ──────────────────────────────────────────────────────────────────────────── dbclropt Clears an option. dbisopt Determines whether a particular option is set. dbsetopt Sets an option. ──────────────────────────────────────────────────────────────────────────── Results Processing Once a command batch has been executed in SQL Server, indicated by dbsqlexec or dbsqlok returning SUCCEED, the application must process any results. The SELECT statement and EXECUTE statements that contain SELECT statements both return result rows. INSERT statements and most other TRANSACT-SQL statements also return data needed by the DB-LIBRARY. There are two types of result rows: regular rows and compute rows. Regular rows are generated from columns in a SELECT statement's select list; compute rows are generated from columns in a SELECT statement's COMPUTE clause. Since these two types of rows contain very different data, the application must process them separately. The result for each statement in a batch is returned to the application separately. Within each statement's set of results, the result rows are processed one at a time. Setting Up the Results The results for each statement in the batch must be set up separately with dbresults. ──────────────────────────────────────────────────────────────────────────── dbresults Sets up the results of the next statement in the batch. This function must be called once for each statement in the batch, whether or not the statement returns rows. The dbresults function must be called after dbsqlexec or dbsqlok has returned SUCCEED, but before calls to dbbind or dbnextrow. ──────────────────────────────────────────────────────────────────────────── Getting Result Data The simplest way to get result data is to bind result columns to program variables by using dbbind and dbaltbind. Then, when a result row is accessed (see the following section, "Accessing Result Rows"), DB-LIBRARY automatically places copies of the columns' data into the program variables to which they are bound. You can also access a result column's data directly with dbdata and dbadata, which return pointers to the data. These functions are frequently used in conjunction with dbdatlen and dbadlen, which return the length of the data and are described later. The dbdata and dbadata functions have the advantage of providing access to the actual data, not a copy of the data. Binding of columns to variables must take place after the call to dbresults but before the first call to dbnextrow. No such preliminary step is needed when results will be directly accessed with dbdata or dbadata. ──────────────────────────────────────────────────────────────────────────── dbadata Returns a pointer to the data for a compute row result column. dbaltbind Binds a compute row result column to a program variable. dbbind Binds a regular row result column to a program variable. dbdata Returns a pointer to the data for a regular row result column. ──────────────────────────────────────────────────────────────────────────── Accessing Result Rows Once dbresults has returned SUCCEED and any binding of columns to variables has been specified, the application is ready to process the results. The first step is to make the result rows available to the application. The dbnextrow function accomplishes this. Each call to dbnextrow reads the next row returned from SQL Server. The row is read directly from the network. Once a row has been read in by dbnextrow, the application can perform any processing desired on the data in the row. If the result columns have been bound to program variables, the data in the row will have been automatically copied into the variables. Alternatively, the data is accessible through dbdata or dbadata. Rows read in by dbnextrow can be automatically saved in a row buffer, if desired. This is accomplished by setting the DBBUFFER option. Without row buffering, each row must be processed as it is read in by dbnextrow because the next call to dbnextrow will overwrite the previously read row. If row buffering has been turned on, the rows are added to a row buffer as they are read in by dbnextrow. Row buffering allows the application to skip around in the buffer and return to previously read rows. After all result rows have been returned, the final call to dbnextrow returns the NO_MORE_ROWS indication. You must continue calling dbnextrow until it returns NO_MORE_ROWS, even if you know that the query produces only one result row. Functions are also available that print result rows in a default format. Because the format is predetermined, these functions are of limited usefulness and are appropriate primarily for debugging. (These are not available under Microsoft Windows(tm).) DB-LIBRARY processes results one statement at a time. When all the results for one statement have been read, dbresults must be called again to set up the results for the next statement in the command buffer. ──────────────────────────────────────────────────────────────────────────── dbclrbuf Drops rows from the row buffer. dbgetrow Reads the specified row in the row buffer. This function provides the application with access to buffered rows that have been previously read by dbnextrow. dbnextrow Reads in the next row. The return value from dbnextrow tells the application whether the row is a regular row or a compute row, whether the buffer is full, and whether the last result row has been read. dbprhead Prints default column headings for result rows. This function is used in conjunction with dbprrow. dbprrow Prints all the result rows in a default format. When this function is used, the program does not need to bind results or call dbnextrow. ──────────────────────────────────────────────────────────────────────────── Canceling Results The results of either an entire command batch or a single query can be canceled. ──────────────────────────────────────────────────────────────────────────── dbcancel Cancels results from the current command batch. This function cancels all the statements in the current batch. dbcanquery Cancels any rows pending from the most recently executed query. ──────────────────────────────────────────────────────────────────────────── Setting Response Time for Results The application can set a limit on SQL Server response time. ──────────────────────────────────────────────────────────────────────────── DBGETTIME Gets the number of seconds that DB-LIBRARY will wait for an SQL Server response. dbsettime Sets the number of seconds that DB-LIBRARY will wait for an SQL Server response. ──────────────────────────────────────────────────────────────────────────── Error and Message Handling DB-LIBRARY applications deal with two types of errors and messages: those from SQL Server (known as messages) and those generated by DB-LIBRARY functions (known as errors). SQL Server can return both informational and error messages to the application. In addition, DB-LIBRARY has its own set of possible warnings and errors. For a list of DB-LIBRARY errors, see Appendix D, "Error Messages." DB-LIBRARY provides an easy way to centralize error and message handling. Using dbmsghandle and dberrhandle, you can install your own message- and error-handling functions. When a message or error occurs, DB-LIBRARY automatically calls the appropriate user-supplied function, providing information to that function on the nature of the message or error. The error- and message-handling logic can thus be assigned to two functions in your application. ──────────────────────────────────────────────────────────────────────────── DBDEAD Determines whether a particular DBPROCESS is dead. When a DBPROCESS is dead, the current DB-LIBRARY function fails, causing the error handler to be called. dberrhandle Installs a user function to handle DB-LIBRARY error messages. dbmsghandle Installs a user function to handle SQL Server informational and error messages. ──────────────────────────────────────────────────────────────────────────── Information Retrieval Information covering several areas, including regular result columns, compute result columns, row buffers, and the command state, can be retrieved from the DBPROCESS structure. As mentioned earlier, regular result columns correspond to columns in the SELECT statement's select list, and compute result columns correspond to columns in the SELECT statement's optional COMPUTE clause. Regular Result Column Information These functions can be called after dbsqlexec or dbsqlok returns SUCCEED: ──────────────────────────────────────────────────────────────────────────── dbcollen Returns the maximum length for a regular column's data. dbcolname Returns the name of a regular result column. dbcoltype Returns the SQL Server datatype for a regular result column. dbdatlen Returns the actual length of a regular column's data. This function is often used in conjunction with dbdata. The value returned by dbdatlen may be different for each regular row read by dbnextrow. dbnumcols Determines the number of columns in the current set of results. ──────────────────────────────────────────────────────────────────────────── Compute Result Column Information These functions can be called after dbsqlexec or dbsqlok returns SUCCEED: ──────────────────────────────────────────────────────────────────────────── dbadlen Returns the actual length of a compute column's data. This function is often used in conjunction with dbadata. The value returned by dbadlen may be different for each compute row read by dbnextrow. dbaltcolid Returns the column ID for a compute column. dbaltlen Returns the maximum length for a compute column's data. dbaltop Returns the type of aggregate operator for a compute column. dbalttype Returns the datatype for a compute column. dbbylist Returns the bylist for a compute row. dbnumalts Returns the number of columns in a compute row. dbnumcompute Returns the number of COMPUTE clauses in the current set of results. ──────────────────────────────────────────────────────────────────────────── Row Buffer Information These macros return information that can be useful when manipulating result rows in buffers: ──────────────────────────────────────────────────────────────────────────── DBFIRSTROW Returns the number of the first row in the buffer. DBLASTROW Returns the number of the last row in the buffer. ──────────────────────────────────────────────────────────────────────────── Command State Information These functions and macros return information about the current state of the command batch. Several of them return information about the "current" statement─that is, the statement currently being processed by dbresults. ──────────────────────────────────────────────────────────────────────────── DBCMDROW Indicates whether the current statement is one that can return rows (that is, a SELECT or a stored procedure containing a SELECT). DBCOUNT Returns the number of rows affected by a statement. DBCURCMD Returns the number of the current statement in a batch. dbgetoff Checks for the existence of specified TRANSACT-SQL constructs in the command buffer. This function is used with the DBOFFSET option. DBMORECMDS Indicates whether there are more statements in the batch. DBNUMORDERS Returns the number of columns specified in a SELECT statement's ORDER BY clause. dbordercol Returns the ID of a column appearing in a SELECT statement's ORDER BY clause. DBROWS Indicates whether the current statement actually did return rows. ──────────────────────────────────────────────────────────────────────────── Miscellaneous Information The application can extract a variety of other information from the DBPROCESS structure. ──────────────────────────────────────────────────────────────────────────── dbchange Indicates whether a command batch has changed the current database. DBCURROW Returns the number of the row currently being read. dbgetmaxprocs Indicates the current maximum number of simultaneously open DBPROCESS structures. DBISAVAIL Indicates whether a DBPROCESS is available for general use. dbname Returns the name of the current database. DBROWTYPE Indicates whether the current result row is a regular row or a compute row. ──────────────────────────────────────────────────────────────────────────── Browse Mode Browse mode allows browsing through database rows and updating of their values a row at a time. From the standpoint of the program, the process involves several steps because each row must be transferred from the database into program variables before it can be browsed and updated. Since a row being browsed is not the actual row residing in the database, but a copy residing in program variables, the program must be able to ensure that changes to the variables' values can be used reliably to update the original database row. In particular, in multiuser situations, the program needs to ensure that updates made to the database by one user do not incorrectly overwrite updates recently made by another user. This becomes an issue because the application typically selects a number of rows from the database at once, but the application's users browse and update the database one row at a time. A timestamp column in database tables that can be browsed provides the information necessary to regulate this type of multiuser updating. Browse mode functions also allow an application to handle ad hoc queries. Several of them return information that an application can use to examine the structure of a complicated ad hoc query to update the underlying database tables. Conceptually, browse mode involves three steps: 1. Select result rows containing columns derived from one or more database tables. 2. Where appropriate, change values in columns of the result rows (not the actual database rows) one row at a time. 3. Update the original database tables, one row at a time, using the new values in the result rows. These steps are implemented in a program as follows: 1. Execute a SELECT statement, generating result rows containing result columns. The SELECT statement must include the FOR BROWSE option. 2. Copy the result column values into program variables, one row at a time. 3. If appropriate, change the variables' values (possibly in response to user input). 4. If appropriate, execute an UPDATE statement that updates the database row corresponding to the current result row. To handle multiuser updates, the WHERE clause of the UPDATE statement must reference the timestamp column. Such a WHERE clause can be obtained with the dbqual function. 5. Repeat the second, third, and fourth steps for each result row. To use browse mode, the following conditions must be true: ■ The SELECT statement must end with the keywords FOR BROWSE. ■ The table(s) to be updated must have a unique index and a timestamp column. ■ The result columns to be used in the updates must be derived from tables the can be browsed and cannot be the result of SQL expressions, such as "max(colname)". In other words, there must be a valid correspondence between the result column and the database column to be updated. In addition, browse mode usually requires two DBPROCESS structures: one for selecting the data and another for performing updates based on the selected data. For examples of browse-mode programming, see Chapter 3, "Example Programs." The browse-mode functions are as follows: ──────────────────────────────────────────────────────────────────────────── dbcolbrowse Indicates whether the source of a result column can be updated through browse mode. dbcolsource Returns a pointer to the name of the database column from which the specified result column was derived. dbfreequal Frees memory allocated by the dbqual function. dbqual Returns a pointer to a WHERE clause suitable for use in updating the current row in a table that can be browsed. dbtabbrowse Indicates whether a particular table can be updated through browse mode. dbtabcount Returns the number of tables involved in the current SELECT statement. dbtabname Returns the name of a table based on its number. dbtabsource Returns the name and number of the table from which a particular result column was derived. dbtsnewlen Returns the length of the new value of the timestamp column after a browse-mode update. dbtsnewval Returns the new value of the timestamp column after a browse mode update. dbtsput Puts the new value of the timestamp column into the given table's current row in the DBPROCESS. ──────────────────────────────────────────────────────────────────────────── Text and Image Handling Since text and image values can be quite large, several functions are available to facilitate the process of updating text and image columns in database tables: ──────────────────────────────────────────────────────────────────────────── dbmoretext Sends part of a text or image value to SQL Server. dbtxptr Returns the text pointer for a column in the current results row. dbtxtimestamp Returns the value of the text timestamp for a column in the current results row. dbtxtsnewval Returns the new value of a text timestamp after a call to dbwritetext. dbtxtsput Puts the new value of a text timestamp into the specified column of the current row in the DBPROCESS. dbwritetext Sends a text or image value to SQL Server. ──────────────────────────────────────────────────────────────────────────── Datatype Conversion DB-LIBRARY supports conversions between most SQL Server datatypes. For information on SQL Server datatypes, see Appendix C, "DB-LIBRARY Datatypes." ──────────────────────────────────────────────────────────────────────────── dbconvert Converts data from one SQL Server datatype to another. dbwillconvert Indicates whether a specified datatype conversion is supported. ──────────────────────────────────────────────────────────────────────────── Cleanup These functions break the connection between the application and SQL Server: ──────────────────────────────────────────────────────────────────────────── dbclose Closes and frees a single DBPROCESS structure. dbexit Closes and frees all DBPROCESS structures. ──────────────────────────────────────────────────────────────────────────── Miscellaneous Functions These functions and macros provide the application with various capabilities: ──────────────────────────────────────────────────────────────────────────── DBLOCKLIB Locks DB-LIBRARY segments (for Windows only). dbprtype Converts an SQL Server token value into a readable string. Token values are returned by various functions such as dbcoltype and dbaltop. dbreadpage Reads in a page of binary data from SQL Server. dbsetavail Marks a DBPROCESS as being available for general use. dbsetmaxprocs Sets the maximum number of simultaneously open DBPROCESS structures. dbsetnull Defines substitution values to be used when binding null values. DBUNLOCKLIB Unlocks DB-LIBRARY segments (for Windows only). dbwritepage Writes a page of binary data to SQL Server. ──────────────────────────────────────────────────────────────────────────── Bulk Copy Special Library The functions and macro in this library allow an application to bulk copy data in and out of SQL Server. They provide a facility for high-speed loading of data into SQL Server from either files or program variables. They also allow you to copy data out of SQL Server into files using a predefined format. The bulk copy functions are similar in function to the bcp program, described in the SQL Server System Administrator's Guide. ──────────────────────────────────────────────────────────────────────────── bcp_batch Saves any preceding rows in SQL Server. bcp_bind Binds data from a program variable to an SQL Server table. bcp_colfmt Specifies the format of an operating system file for bulk copy purposes. bcp_collen Sets the program variable data length for the current copy in. bcp_colptr Sets the program variable data address for the current copy in. bcp_columns Sets the total number of columns found in the operating system file. bcp_control Changes various control parameter default settings. bcp_done Ends a bulk copy from program variables into SQL Server. bcp_exec Executes a bulk copy of data between a database table and an operating system file. bcp_init Initializes bulk copy. bcp_moretext Sends part of a text or image value to SQL Server. bcp_sendrow Sends a row of data from program variables to SQL Server. BCP_SETL Sets the LOGINREC for bulk copy operations. ──────────────────────────────────────────────────────────────────────────── Two-Phase Commit Service Special Library The functions in this library allow an application to coordinate updates among two or more SQL Servers: ──────────────────────────────────────────────────────────────────────────── abort_xact Marks a distributed transaction as being aborted. build_xact_string Builds a name for a distributed transaction. close_commit Ends a connection with the commit service. commit_xact Marks a distributed transaction as being committed. open_commit Establishes a connection with the commit service. remove_xact Decrements the count of sites still active in the distributed transaction. scan_xact Prints commit service record for distributed transactions. start_xact Starts a distributed transaction using the commit service. stat_xact Returns the current status of a distributed transaction. ──────────────────────────────────────────────────────────────────────────── Chapter 2 Functions and Macros ──────────────────────────────────────────────────────────────────────────── Introduction This chapter contains syntax and usage information about each function and macro contained in DB-LIBRARY. Functions and macros are arranged in alphabetical order. dbadata ──────────────────────────────────────────────────────────────────────────── Function Returns a pointer to the data for a compute column. Syntax BYTE *dbadata(dbproc, computeid, column) DBPROCESS *dbproc; int computeid; int column; Comments After each call to dbnextrow that returns a value, you can use dbadata to obtain a pointer to the data for a particular column in a compute. The data is not null-terminated. You can use dbadlen to get the length of the data. When a column of integer data is summed or averaged, SQL Server always returns a 4-byte integer, regardless of the size of the column. Therefore, be sure that the variable that is to contain the result from such a compute is declared as DBINT. The following program fragment illustrates the use of dbadata: DBPROCESS *dbproc; int rowinfo; DBINT sum; /* First, put the commands into the command buffer. */ dbcmd(dbproc, "select db_name(dbid), dbid, size from sysusages"); dbcmd(dbproc, " order by dbid"); dbcmd(dbproc, " compute sum(size) by dbid"); /* Send the commands to SQL Server and start execution. */ dbsqlexec(dbproc); /* Process the command. */ dbresults(dbproc); /* Examine the results of the COMPUTE clause. */ while((rowinfo = dbnextrow(dbproc)) != NO_MORE_ROWS) { if (rowinfo == REG_ROW) printf("regular row returned.\n"); else { /* This row is the result of a COMPUTE clause, * and "rowinfo" is the computeid of this COMPUTE * clause. */ sum = *(DBINT *)(dbadata(dbproc, rowinfo, 1)); printf("sum = %ld\n", sum); } } The dbaltbind function automatically binds data to your program variables. It is somewhat easier to use than dbadata and dbadlen but is less efficient since it copies the data into your variable. Parameters dbproc The DBPROCESS structure that is the handle for a particular workstation/SQL Server process. It contains all the information that DB-LIBRARY uses to manage communications and data between the workstation and SQL Server. computeid The ID that identifies the compute. A SELECT statement can have multiple COMPUTE clauses, each of which can have a different number of aggregate operators and aggregate targets. The computeid is returned by dbnextrow or dbgetrow. column The number of the column. The first column returned is number 1. Note that the order in which compute columns are returned is determined by the order of the corresponding columns in the select list, not by the order in which the compute columns were originally specified. For example, in the following query the result of "sum(price)" is referenced by giving column a value of 1, not 2: select price, advance from titles compute sum(advance), sum(price) The relative order of compute columns in the select list, rather than their absolute position, determines the value of column. For instance, given the following variation of the previous SELECT, the column for "sum(price)" still has a value of 1 and not 2 because the "title_id" column in the select list is not a compute column and therefore is ignored when determining the compute column's number: select title_id, price, advance from titles compute sum(advance), sum(price) Returns A BYTE pointer to the data for a particular column in a compute. A BYTE pointer to NULL is returned if there is no such column or compute or if the data has a null value. The data space pointed to is allocated and freed by DB-LIBRARY. You should be careful not to overwrite the space. See Also dbadlen, dbaltbind, dbaltlen, dbalttype, dbgetrow, dbnextrow, dbnumalts dbadlen ──────────────────────────────────────────────────────────────────────────── Function Returns the actual length of the data for a compute column. Syntax DBINT dbadlen(dbproc, computeid, column) DBPROCESS *dbproc; int computeid; int column; Comments This function returns the length of the data for a particular column in a COMPUTE clause. You can get a pointer to the actual data using dbadata. The following program fragment illustrates the use of dbadlen: DBPROCESS *dbproc; char biggest_name[MAXNAME+1]; DBINT namelen; STATUS rowinfo; /* Put the command into the command buffer. */ dbcmd(dbproc, "select name from sysobjects"); dbcmd(dbproc, " order by name"); dbcmd(dbproc, " compute max(name)"); /* Send the command to SQL Server and start execution. */ dbsqlexec(dbproc); /* Process the command. */ dbresults(dbproc); /* Examine each row returned by the command. */ while ((rowinfo = dbnextrow(dbproc)) != NO_MORE_ROWS) { if (rowinfo == REG_ROW) printf("regular row returned.\n"); else { /* This row is the result of a COMPUTE clause, * and "rowinfo" is the computeid of this COMPUTE * clause. */ namelen = dbadlen(dbproc, rowinfo, 1); strncpy(biggest_name,(char *)dbadata(dbproc, rowinfo, 1), (int)namelen); /* Data pointed to by dbadata() is not null-terminated. */ biggest_name[namelen] = '\0'; printf("biggest name = %s\n", biggest_name); } } Parameters dbproc The DBPROCESS structure that is the handle for a particular workstation/SQL Server process. It contains all the information that DB-LIBRARY uses to manage communications and data between the workstation and SQL Server. computeid The ID that identifies the compute. A SELECT statement can have multiple COMPUTE clauses, each of which can have a different number of aggregate operators and aggregate targets. The computeid is returned by dbnextrow or dbgetrow. column The number of the column. The first column is number 1. Returns The length, in bytes, of the data for a particular column in a particular COMPUTE clause. If there is no such column or COMPUTE clause, -1 is returned. If the data has a null value, 0 is returned. See Also dbadata, dbaltlen, dbalttype, dbgetrow, dbnextrow, dbnumalts dbaltbind ──────────────────────────────────────────────────────────────────────────── Function Binds a compute result column to a program variable. Syntax RETCODE dbaltbind(dbproc, computeid, column, vartype, varlen, varaddr) DBPROCESS *dbproc; int computeid; int column; int vartype; DBINT varlen; BYTE *varaddr; Comments This function directs DB-LIBRARY to copy compute column data returned by SQL Server into a program variable. (A compute column results from the COMPUTE clause of a TRANSACT-SQL SELECT statement.) When each new row containing compute data is read by dbnextrow or dbgetrow, the data from the designated column in that compute row is copied into the program variable with the address varaddr. There must be a separate dbaltbind call for each compute column that is to be copied. It is not necessary to bind every compute column to a program variable. SQL Server can return two types of rows: regular rows containing data from columns designated by a SELECT statement's select list, and compute rows resulting from the COMPUTE clause. The dbaltbind function binds data from compute rows. Use dbbind for binding data from regular rows. The calls to dbaltbind must be made after a call to dbresults and before the first call to dbnextrow. The typical sequence of calls is DBCHAR name[20]; DBINT namecount; /* Read the query into the command buffer. */ dbcmd(dbproc, "select name from employee compute count(name)"); /* Send the query to SQL Server. */ dbsqlexec(dbproc); /* Get ready to process the results of the query. */ dbresults(dbproc); /* Bind the regular row data - name. */ dbbind(dbproc, 1, STRINGBIND, (DBINT) 0, name); /* Bind the compute column data - count of name. */ dbaltbind(dbproc, 1, 1, INTBIND, (DBINT) 0, (BYTE *) &namecount); /* Now process each row. */ while (dbnextrow(dbproc) != NO_MORE_ROWS) { /*C-code to print or process row data*/ } There is some overhead incurred by using dbaltbind because it always copies the row data into the designated program variable. To avoid this copying, the returned data can be more directly accessed using the dbadlen and dbadata functions. Since null values can be returned from SQL Server, there is a set of default values, one for each datatype, that will be substituted when binding null values. You can explicitly set your own values to be substituted for the default null values with the dbsetnull function. (For a list of the default substitution values, see "dbsetnull.") Parameters dbproc The DBPROCESS structure that is the handle for a particular workstation/SQL Server process. It contains all the information that DB-LIBRARY uses to manage communications and data between the workstation and SQL Server. computeid The ID of the COMPUTE clause to which the dbaltbind function refers. Since a SELECT statement can have more than one COMPUTE clause, the computeid is necessary to distinguish between them. The computeid of the first COMPUTE clause of a SELECT statement is 1. column The column number of the row data that is to be copied to a program variable. The first column is column number 1. Note that the order in which compute columns are returned is determined by the order of the corresponding columns in the select list, not by the order in which the compute columns were originally specified. For example, in the following query, the result of "sum(price)" is referenced by giving column a value of 1, not 2: select price, advance from titles compute sum(advance), sum(price) The relative order of compute columns in the select list, rather than their absolute position, determines the value of column. For instance, given the following variation of the previous SELECT, the column for "sum(price)" still has a value of 1 and not 2 because the "title_id" column in the select list is not a compute column and therefore is ignored when determining the compute column's number: select title_id, price, advance from titles compute sum(advance), sum(price) vartype A description of the binding's datatype. As shown in the following table, it corresponds to the datatype of the program variable that receives the copy of the data from the DBPROCESS. The dbaltbind function supports a wide range of type conversions, so this type can be different from the type returned by the SQL query. For instance, an SQLMONEY result can be bound to a DBFLT8 program variable, using FLT8BIND, and the appropriate data conversion will happen automatically. For a list of the data conversions provided by DB-LIBRARY, see "dbwillconvert." For a list of the typedefs used by DB-LIBRARY, see Appendix C, "DB-LIBRARY Datatypes." The following table lists legal vartypes recognized by dbaltbind and the program variable and SQL Server types that each one refers to. ╓┌──────────────┌──────────────────────┌─────────────────────────────────────╖ Vartype Program Variable Type SQL Server Type ──────────────────────────────────────────────────────────────────────────── CHARBIND DBCHAR SQLCHAR STRINGBIND DBCHAR SQLCHAR NTBSTRINGBIND DBCHAR SQLCHAR VARYCHARBIND DBVARYCHAR SQLCHAR Vartype Program Variable Type SQL Server Type ──────────────────────────────────────────────────────────────────────────── BINARYBIND DBBINARY SQLBINARY VARYBINBIND DBVARYBIN SQLBINARY TINYBIND DBTINYINT SQLINT1 SMALLBIND DBSMALLINT SQLINT2 INTBIND DBINT SQLINT4 FLT8BIND DBFLT8 SQLFLT8 BITBIND DBBIT SQLBIT DATETIMEBIND DBDATETIME SQLDATETIME MONEYBIND DBMONEY SQLMONEY Vartype Program Variable Type SQL Server Type ──────────────────────────────────────────────────────────────────────────── ──────────────────────────────────────────────────────────────────────────── Since SQLTEXT and SQLIMAGE data are never returned through a compute row, those datatypes are not included in the preceding table. Note that the SQL Server type is listed merely for your information. The vartype you specify does not necessarily have to correspond to a particular SQL Server type because, as mentioned earlier, dbaltbind converts SQL Server data into the specified vartype. The following table shows that four representations for character data are available. They differ according to whether the data is padded with blanks or is null-terminated: ╓┌──────────────┌─────────────┌────────┌─────────────────────────────────────╖ Vartype Program Type Padding Terminator ──────────────────────────────────────────────────────────────────────────── Vartype Program Type Padding Terminator ──────────────────────────────────────────────────────────────────────────── CHARBIND DBCHAR blanks none STRINGBIND DBCHAR blanks \0 NTBSTRINGBIND DBCHAR none \0 VARYCHARBIND DBVARYCHAR none none ──────────────────────────────────────────────────────────────────────────── Note that "\0" is the null terminator character. Similarly, binary data can be stored in two different ways: ╓┌────────────┌─────────────┌────────────────────────────────────────────────╖ Vartype Program Type Padding ──────────────────────────────────────────────────────────────────────────── BINARYBIND DBBINARY nulls Vartype Program Type Padding ──────────────────────────────────────────────────────────────────────────── BINARYBIND DBBINARY nulls VARYBINBIND DBVARBINARY none ──────────────────────────────────────────────────────────────────────────── When a column of integer data is summed or averaged, SQL Server always returns a 4-byte integer, regardless of the size of the column. Therefore, be sure that the variable that is to contain the result from such a compute is declared as DBINT and that the vartype of the binding is INTBIND. varlen The length of the program variable in bytes. For fixed-length vartypes, such as MONEYBIND or FLT8BIND, this length is ignored. For character and binary types, varlen must describe the total length of the available destination buffer space, including any space that can be required for special terminating bytes, such as a null terminator. If varlen is 0, the total number of bytes available is copied into the program variable. (For char and binary SQL Server data, the total number of bytes available is equal to the defined length of the database column, including any blank padding. For varchar and varbinary data, the total number of bytes available is equal to the actual data contained in the column.) Therefore, if you are sure that your program variable is large enough to handle the results, you can just set the varlen to 0. The varlen is ignored for VARYCHARBIND and VARYBINBIND data. varaddr The address of the program variable to which the data is copied. Returns SUCCEED or FAIL. The dbaltbind function returns FAIL if the column number given isn't valid, if the vartype isn't compatible with the SQL Server type being returned, or if varaddr is NULL. See Also dbadata, dbadlen, dbbind, dbconvert, dbgetrow, dbnextrow, dbresults, dbsetnull, dbwillconvert, Appendix C dbaltcolid ──────────────────────────────────────────────────────────────────────────── Function Returns the operand column ID for a compute column. Syntax int dbaltcolid(dbproc, computeid, column) DBPROCESS *dbproc; int computeid; int column; Comments This function returns the ID of the column to which the aggregate operator applies for a particular column in a compute. For example, the function dbaltcolid(dbproc, 1, 1) returns 2, since the COMPUTE COUNT clause in the following example refers to the second column in the select list: select dept, name from employee order by dept, name compute count(name) by dept Parameters dbproc The DBPROCESS structure that is the handle for a particular workstation/SQL Server process. It contains all the information that DB-LIBRARY uses to manage communications and data between the workstation and SQL Server. computeid The ID that identifies the compute. A SELECT statement can have multiple COMPUTE clauses, each of which can have a different number of aggregate operators and aggregate targets. The computeid is returned by dbnextrow or dbgetrow. column The number of the compute column. The first column is number 1. Returns The column ID that the aggregate in the compute applies to. The first column is number 1. If either the computeid or the column are invalid, then -1 is returned. See Also dbadata, dbadlen, dbaltlen, dbgetrow, dbnextrow, dbnumalts, dbprtype dbaltlen ──────────────────────────────────────────────────────────────────────────── Function Returns the maximum length of the data for a compute column. Syntax DBINT dbaltlen(dbproc, computeid, column) DBPROCESS *dbproc; int computeid; int column; Comments This function returns the maximum length, in bytes, for a particular column in a compute row. In the case of variable length data, this is not necessarily the actual length of the data, but rather the maximum length. For the actual data length, use dbadlen. In the following example, dbaltlen(dbproc, 1, 1) returns 4 because counts are of SQLINT4 type, which is 4 bytes long: select dept, name from employee order by dept, name compute count(name) by dept Parameters dbproc The DBPROCESS structure that is the handle for a particular workstation/SQL Server process. It contains all the information that DB-LIBRARY uses to manage communications and data between the workstation and SQL Server. computeid The ID that identifies the compute. A SELECT statement can have multiple COMPUTE clauses, each of which can have a different number of aggregate operators and aggregate targets. The computeid is returned by dbnextrow or dbgetrow. column The number of the column. The first column is number 1. Returns The maximum number of bytes the data for a particular column in a compute can be. If there is no such column or compute, -1 is returned. See Also dbadata, dbadlen, dbalttype, dbgetrow, dbnextrow, dbnumalts dbaltop ──────────────────────────────────────────────────────────────────────────── Function Returns the type of aggregate function for a compute column. Syntax int dbaltop(dbproc, computeid, column) DBPROCESS *dbproc; int computeid; int column; Comments This function returns the type of aggregate function for a particular column in a compute. In the following example, dbaltop(dbproc, 1, 1) returns the type for count since the first aggregate operator in the first COMPUTE clause is COUNT: select dept, name from employee order by dept, name compute count(name) by dept You can convert the type to a readable string using dbprtype. Parameters dbproc The DBPROCESS structure that is the handle for a particular workstation/SQL Server process. It contains all the information that DB-LIBRARY uses to manage communications and data between the workstation and SQL Server. computeid The ID that identifies the compute. A SELECT statement can have multiple COMPUTE clauses, each of which can have a different number of aggregate operators and aggregate targets. The computeid is returned by dbnextrow or dbgetrow. column The number of the column. The first column is number 1. Returns The type of aggregate operator for the particular column in the compute. The types are defined as follows: ╓┌─────────────────────────────────┌─────────────────────────────────────────╖ Type Aggregate Operator ──────────────────────────────────────────────────────────────────────────── SQLAOPSUM sum SQLAOPAVG avg SQLAOPCNT count SQLAOPMIN min Type Aggregate Operator ──────────────────────────────────────────────────────────────────────────── SQLAOPMIN min SQLAOPMAX max ──────────────────────────────────────────────────────────────────────────── If the computeid or column are not valid, -1 is returned. See Also dbadata, dbadlen, dbaltlen, dbgetrow, dbnextrow, dbnumalts, dbprtype dbalttype ──────────────────────────────────────────────────────────────────────────── Function Returns the datatype for a compute column. Syntax int dbalttype(dbproc, computeid, column) DBPROCESS *dbproc; int computeid; int column; Comments This function returns the type of the data for a particular column in a compute. In the following example, dbalttype(dbproc, 1, 1) returns the type for SQLINT4 since counts are of SQLINT4 type. select dept, name from employee order by dept, name compute count(name) by dept You can convert the type to a readable string using dbprtype. Parameters dbproc The DBPROCESS structure that is the handle for a particular workstation/SQL Server process. It contains all the information that DB-LIBRARY uses to manage communications and data between the workstation and SQL Server. computeid The ID that identifies the compute. A SELECT statement can have multiple COMPUTE clauses, each of which can have a different number of aggregate operators and aggregate targets. The computeid is returned by dbnextrow or dbgetrow. column The number of the column. The first column is number 1. Returns The type of the data for the particular column in the compute. ╓┌─────────────────────────────────┌───────────────────────────────────────── Column Return ──────────────────────────────────────────────────────────────────────────── SQLVARCHAR SQLCHAR SQLVARBINARY SQLBINARY SQLDATETIME SQLDATETIME SQLMONEY SQLMONEY SQLFLT8 SQLFLT8 SQLCHAR SQLCHAR Column Return ──────────────────────────────────────────────────────────────────────────── SQLCHAR SQLCHAR SQLBINARY SQLBINARY SQLINTn SQLINT1, SQLINT2, or SQLINT4 depending on the actual type of the SQLINTn -1 If either the computeid or the column are invalid See Also dbadata, dbadlen, dbaltlen, dbgetrow, dbnextrow, dbnumalts, dbprtype, Appendix C dbbind ──────────────────────────────────────────────────────────────────────────── Function Binds a regular result column to a program variable. Syntax RETCODE dbbind(dbproc, column, vartype, varlen, varaddr) DBPROCESS *dbproc; int column; int vartype; DBINT varlen; BYTE *varaddr; Comments Data comes back from SQL Server one row at a time. This function directs DB-LIBRARY to copy the data for a regular column (designated in a SELECT statement's select list) into a program variable. When each new row containing regular (not compute) data is read using dbnextrow or dbgetrow, the data from the designated column in that row is copied into the program variable with the address varaddr. There must be a separate dbbind call for each regular column that is to be copied. It is not necessary to bind every column to a program variable. A result column can be bound to only one program variable. The SQL Server can return two types of rows: regular rows and compute rows resulting from the COMPUTE clause of a SELECT statement. The dbbind function binds data from regular rows. Use dbaltbind for binding data from compute rows. The calls to dbbind must be made after a call to dbresults and before the first call to dbnextrow. The typical sequence of calls is DBINT xvariable; DBCHAR yvariable[10]; /* Read the query into the command buffer. */ dbcmd(dbproc, "select x = 100, y = 'hello'"); /* Send the query to SQL Server. */ dbsqlexec(dbproc); /* Get ready to process the results of the query. */ dbresults(dbproc); /* Bind column data to program variables. */ dbbind(dbproc, 1, INTBIND, (DBINT) 0, (BYTE *) &xvariable); dbbind(dbproc, 2, STRINGBIND, (DBINT) 0, yvariable); /* Now process each row. */ while (dbnextrow(dbproc) != NO_MORE_ROWS) { /* C-code to print or process row data*/ } There is some overhead incurred by using dbbind since it copies the row data into the designated program variable. To avoid this copying, the returned data can be more directly accessed using the dbdatlen and dbdata functions. Since null values can be returned from SQL Server, there is a set of default values, one for each datatype, that will be substituted when binding null values. You can explicitly set your own values to be substituted for the default null value with the dbsetnull function. (See "dbsetnull" for a list of the default substitution values.) Parameters dbproc The DBPROCESS structure that is the handle for a particular workstation/SQL Server process. It contains all the information that DB-LIBRARY uses to manage communications and data between the workstation and SQL Server. column The column number of the row data that is to be copied to a program variable. The first column is number 1. vartype A description of the binding's datatype. As shown in the following table, it corresponds to the datatype of the program variable that receives the copy of the data from the DBPROCESS. The dbbind function supports a wide range of type conversions, so this type can be different from the type returned by the SQL query. For instance, an SQLMONEY result can be bound to a DBFLT8 program variable, using FLT8BIND, and the appropriate data conversion happens automatically. For a list of the data conversions provided by DB-LIBRARY, see "dbwillconvert." For a list of the typedefs used by DB-LIBRARY, see Appendix C, "DB-LIBRARY Datatypes." The following table lists legal vartypes recognized by dbbind and the program variable and SQL Server types that each one refers to: ╓┌──────────────┌──────────────────────┌─────────────────────────────────────╖ Vartype Program Variable Type SQL Server Type ──────────────────────────────────────────────────────────────────────────── CHARBIND DBCHAR SQLCHAR or SQLTEXT STRINGBIND DBCHAR SQLCHAR or SQLTEXT Vartype Program Variable Type SQL Server Type ──────────────────────────────────────────────────────────────────────────── NTBSTRINGBIND DBCHAR SQLCHAR or SQLTEXT VARYCHARBIND DBVARYCHAR SQLCHAR or SQLTEXT BINARYBIND DBBINARY SQLBINARY or SQLIMAGE VARYBINBIND DBVARYBIN SQLBINARY or SQLIMAGE TINYBIND DBTINYINT SQLINT1 SMALLBIND DBSMALLINT SQLINT2 INTBIND DBINT SQLINT4 FLT8BIND DBFLT8 SQLFLT8 BITBIND DBBIT SQLBIT Vartype Program Variable Type SQL Server Type ──────────────────────────────────────────────────────────────────────────── DATETIMEBIND DBDATETIME SQLDATETIME MONEYBIND DBMONEY SQLMONEY ──────────────────────────────────────────────────────────────────────────── Note that the SQL Server type in the preceding table is listed merely for your information. The vartype you specify does not necessarily have to correspond to a particular SQL Server type because as mentioned earlier, dbbind converts SQL Server data into the specified vartype. The following table shows that four representations for character and text data are available. They differ according to whether the data is blank-padded or null-terminated: ╓┌──────────────┌─────────────┌────────┌─────────────────────────────────────╖ Vartype Program Type Padding Terminator ──────────────────────────────────────────────────────────────────────────── CHARBIND DBCHAR blanks none STRINGBIND DBCHAR blanks \0 NTBSTRINGBIND DBCHAR none \0 VARYCHARBIND DBVARYCHAR none none ──────────────────────────────────────────────────────────────────────────── Note that "\0" is the null terminator character. Similarly, binary and image data can be stored in two different ways: ╓┌────────────┌─────────────┌────────────────────────────────────────────────╖ Vartype Program Type Padding ──────────────────────────────────────────────────────────────────────────── BINARYBIND DBBINARY nulls Vartype Program Type Padding ──────────────────────────────────────────────────────────────────────────── BINARYBIND DBBINARY nulls VARYBINBIND DBVARBINARY none ──────────────────────────────────────────────────────────────────────────── varlen The length of the program variable in bytes. For fixed-length vartypes, such as MONEYBIND or FLT8BIND, this length is ignored. For character, text, binary, and image types, varlen must describe the total length of the available destination buffer space, including any space that can be required for special terminating bytes, such as a null terminator. If varlen is 0, the number of bytes available is copied into the program variable. (For char and binary SQL Server data, the number of bytes available is equal to the defined length of the database column, including any blank padding. For varchar, varbinary, text, and image data, the number of bytes available is equal to the actual data contained in the column.) Therefore, if you are sure that your program variable is large enough to handle the results, you can set varlen to 0. In some cases, DB-LIBRARY issues a message indicating that data conversion resulted in an overflow. This is usually caused by a varlen specification being too small for the data being received from SQL Server. For example, if varlen is set to 5, vartype is set to VARYCHARBIND, and the SQL Server column being bound is of type VARCHAR with a length of 20. When the bind occurs (using dbnextrow), the overflow message is issued. Note however that 5 bytes of data will be bound. Other types of binds can also cause the overflow message to be issued. For information on datatype conversions, see "dbconvert." varaddr The address of the program variable to which the data is copied. Calling dbbind with a NULL varaddr parameter breaks a previously set binding. Returns SUCCEED or FAIL. The dbbind function returns FAIL if the column number given isn't valid, if the vartype isn't compatible with the SQL Server type being returned, or if varaddr is NULL. See Also dbaltbind, dbconvert, dbdata, dbdatlen, dbgetrow, dbnextrow, dbresults, dbsetnull, dbwillconvert, Appendix C dbbylist ──────────────────────────────────────────────────────────────────────────── Function Returns the bylist for a compute row. Syntax BYTE *dbbylist(dbproc, computeid, size) DBPROCESS *dbproc; int computeid; int *size; Comments The dbbylist function returns the bylist for a compute row. (A SELECT statement's COMPUTE clause can contain the keyword BY, followed by a list of columns. This list, known as the bylist, divides the results into subgroups based on changing values in the specified columns. The COMPUTE clause's row aggregate is applied to each subgroup, generating a compute row for each subgroup.) This function should be called after dbresults has returned SUCCEED. Assume the following command has been executed: select dept, name, year, sales from employee order by dept, name, year compute count(name) by dept,name The call dbbylist(dbproc, 1, &size) sets size to 2 because there are two items in the bylist. It returns a pointer to an array of two BYTES, which contain the values 1 and 2, indicating that the bylist is composed of columns 1 and 2 from the select list. Parameters dbproc The DBPROCESS structure that is the handle for a particular workstation/SQL Server process. It contains all the information that DB-LIBRARY uses to manage communications and data between the workstation and SQL Server. computeid The ID that identifies the compute. A SELECT statement can have multiple COMPUTE clauses, each of which can have a different number of aggregate operators and targets. The computeid is returned by dbnextrow or dbgetrow. size A pointer to an integer, which dbbylist sets to the number of elements in the bylist. Returns A pointer to an array of BYTES containing the numbers of the columns that compose the bylist for the specified compute. The names of the columns are available by calling dbcolname. If the computeid is out of range, NULL is returned. The size of the array is returned in the size parameter. A size of 0 indicates that either there is no bylist for this particular compute or the computeid is out of range. See Also dbadata, dbadlen, dbaltlen, dbalttype, dbcolname, dbgetrow, dbnextrow, dbresults dbcancel ──────────────────────────────────────────────────────────────────────────── Function Cancels the current command batch. Syntax RETCODE dbcancel(dbproc) DBPROCESS *dbproc; Comments This function can be called after calling dbsqlexec, dbsqlsend, dbsqlok, dbresults, or dbnextrow to cancel execution of the current command batch on SQL Server and flush any pending results. When called, SQL Server is interrupted and ceases the execution of the command batch associated with the dbproc. Any pending results are read and discarded. Note that dbcancel cancels all the commands in the current command batch. It cannot be used to cancel only the current command in a multiple-command batch. Parameters dbproc The DBPROCESS structure that is the handle for a particular workstation/SQL Server process. It contains all the information that DB-LIBRARY uses to manage communications and data between the workstation and SQL Server. Returns SUCCEED or FAIL. See Also dbnextrow, dbresults, dbsetinterrupt, dbsqlexec, dbsqlok, dbsqlsend dbcanquery ──────────────────────────────────────────────────────────────────────────── Function Cancels any rows pending from the most recently executed query. Syntax RETCODE dbcanquery(dbproc) DBPROCESS *dbproc; Comments This function is an efficient way to throw away any unread rows that result from the most recently executed SQL query. Calling dbcanquery is similar to calling dbnextrow until it returns NO_MORE_ROWS, except for the following difference: data that was bound when the application called dbcanquery will continue to be bound when the function returns. If you want to ignore all of the results from all of the commands in the current command batch, you should call dbcancel. Parameters dbproc The DBPROCESS structure that is the handle for a particular workstation/SQL Server process. It contains all the information that DB-LIBRARY uses to manage communications and data between the workstation and SQL Server. Returns SUCCEED or FAIL. See Also dbcancel, dbnextrow, dbresults, dbsetinterrupt, dbsqlexec dbchange ──────────────────────────────────────────────────────────────────────────── Function Determines whether a command batch has changed the current database. Syntax char *dbchange(dbproc) DBPROCESS *dbproc; Comments The dbchange function informs the program of a change in the current database by catching any instance of the TRANSACT-SQL USE statement. Database changes made by a USE statement do not take effect until the end of the batch. The dbchange function is therefore useful only in determining whether the current command batch has changed the database for subsequent command batches. The internal DBPROCESS flag that dbchange monitors to determine whether the database has changed is cleared when the program executes a new command batch by calling either dbsqlexec or dbsqlsend. Therefore, the simplest way to keep track of database changes is to call dbchange when dbresults returns NO_MORE_RESULTS at the end of each command batch. Alternatively, you can always get the name of the current database by calling dbname. Parameters dbproc The DBPROCESS structure that is the handle for a particular workstation/SQL Server process. It contains all the information that DB-LIBRARY uses to manage communications and data between the workstation and SQL Server. Returns A pointer to the null-terminated name of the new database, if any. If the database has not changed, NULL is returned. See Also dbname, dbresults, dbsqlexec, dbsqlsend dbclose ──────────────────────────────────────────────────────────────────────────── Function Closes and frees a single DBPROCESS structure. Syntax void dbclose(dbproc) DBPROCESS *dbproc; Comments The dbclose function is the inverse of dbopen. It cleans up any activity associated with one DBPROCESS structure and frees the memory. It also closes the corresponding network connection. Use dbexit to close every open DBPROCESS structure. Calling dbclose with a parameter not returned by dbopen causes an error. Parameters dbproc The DBPROCESS structure that is the handle for a particular workstation/SQL Server process. It contains all the information that DB-LIBRARY uses to manage communications and data between the workstation and SQL Server. Returns None. See Also dbexit, dbopen dbclrbuf ──────────────────────────────────────────────────────────────────────────── Function Drops rows from the row buffer. Syntax void dbclrbuf(dbproc, n) DBPROCESS *dbproc; DBINT n; Comments DB-LIBRARY provides a service that allows rows to be buffered as they are returned from SQL Server. You can turn row buffering on by calling dbsetopt(dbproc, DBBUFFER, "n"), where n is the number of rows you would like DB-LIBRARY to buffer. If buffering is on, you can then randomly refer to rows that have been read from SQL Server using dbgetrow. The row buffer can become full if SQL Server has returned more than the n rows that you said you wanted buffered. The row buffer is full when dbnextrow returns BUF_FULL. When this happens, dbnextrow is refusing to read in the next row from SQL Server. Once the row buffer is full, dbnextrow will continue to return BUF_FULL until at least one row is freed by calling dbclrbuf. The dbclrbuf function always frees the oldest row in the buffer first. Parameters dbproc The DBPROCESS structure that is the handle for a particular workstation/SQL Server process. It contains all the information that DB-LIBRARY uses to manage communications and data between the workstation and SQL Server. n The number of rows you want cleared from the row buffer. If n is equal to or greater than the number of rows in the buffer, all but the newest row will be removed. If n is less than 1, the call is ignored. Returns None. See Also dbgetrow, dbnextrow, dbsetopt, Appendix B dbclropt ──────────────────────────────────────────────────────────────────────────── Function Clears an option set by dbsetopt. Syntax RETCODE dbclropt(dbproc, option, param) DBPROCESS *dbproc; int option; char *param; Comments This function clears SQL Server and DB-LIBRARY options that have been set with dbsetopt. Although SQL Server options can be set and cleared directly through SQL, the application should use dbsetopt and dbclropt to set and clear options. This provides a uniform interface for setting both SQL Server and DB-LIBRARY options. It also allows the application to use the dbisopt function to check the status of an option. This function does not immediately clear the options specified. With the exception of DBBUFFER and DBNOAUTOFREE, they are not cleared until the command buffer is sent to SQL Server (by invoking the dbsqlexec function). An additional result is returned, using the dbresults function. (For information on results returned, see "dbresult.") For a complete list of options, see Appendix B, "DB-LIBRARY Options." Parameters dbproc The DBPROCESS structure that is the handle for a particular workstation/SQL Server process. It contains all the information that DB-LIBRARY uses to manage communications and data between the workstation and SQL Server. If dbproc is NULL, the option is cleared for all active DBPROCESS structures. option The option that is to be turned off. param Certain options take parameters. The DBOFFSET option, for example, takes as a parameter the SQL construct for which offsets are to be returned. For a list of options that take parameters, see Appendix B, "DB-LIBRARY Options." If an option does not take a parameter, param must be NULL. If the option you are clearing takes a parameter but there can be only one instance of the option, dbclropt ignores the param parameter. For example, dbclropt ignores the value of param when clearing the DBBUFFER option because row buffering can have only one setting at a time. On the other hand, the DBOFFSET option can have several settings, each with a different parameter. It can have been set twice─to look for offsets to SELECT statements and offsets to ORDER BY clauses. In that case, dbclropt needs the param parameter to determine whether to clear the SELECT offset or the ORDER BY offset. The only other option for which dbclropt requires a param is DBSTAT. Note that the command string generated by this function is not immediately sent to SQL Server. Instead, it is buffered within DB-LIBRARY and sent the next time dbsqlexec is invoked; therefore, any options requested by this function do not go into effect until then. Also, the results of the command generated by this function are not returned until the command is transferred to SQL Server. Therefore, the application should be expecting the results returned from the command string generated by this function. If an invalid parameter is specified, this is not known until the command is sent to SQL Server and the results for that command are returned using dbresult. Returns SUCCEED or FAIL. See Also dbisopt, dbresults, dbsetopt, dbsqlexec, Appendix B dbcmd ──────────────────────────────────────────────────────────────────────────── Function Adds text to the DBPROCESS command buffer. Syntax RETCODE dbcmd(dbproc, cmdstring) DBPROCESS *dbproc; char *cmdstring; Comments This function adds text to the command buffer in the DBPROCESS structure. It adds to the existing command buffer; it doesn't delete or overwrite the current contents except after the buffer has been sent to SQL Server. (See the following comment.) The user can call dbcmd repeatedly. Note that sequential calls are concatenated together; it is the application's responsibility to ensure that any necessary blanks appear between the end of one line and the beginning of the next. After a call to dbsqlexec or dbsqlsend, the first call to either dbcmd or dbfcmd automatically clears the command buffer before the new text is entered. If this situation is undesirable, set the DBNOAUTOFREE option. When DBNOAUTOFREE is set, the command buffer is cleared only by an explicit call to dbfreebuf. The following example shows how to use dbcmd to build up a multiple-line SQL statement: DBPROCESS *dbproc; dbcmd(dbproc, "Select name from sysobjects"); dbcmd(dbproc, " where id < 5"); dbcmd(dbproc, " and type='S'"); The dbfcmd function is a related function. Unlike dbcmd, dbfcmd takes additional parameters and interprets the cmdstring as a format string that is passed to sprintf along with any additional parameters. Parameters dbproc The DBPROCESS structure that is the handle for a particular workstation/SQL Server process. It contains all the information that DB-LIBRARY uses to manage communications and data between the workstation and SQL Server. cmdstring A null-terminated character string that is to be copied into the command buffer. Returns SUCCEED or FAIL. See Also dbfcmd, dbfreebuf, dbsqlexec, dbsqlsend, Appendix B DBCMDROW ──────────────────────────────────────────────────────────────────────────── Function Determines whether the current command can return rows. Syntax RETCODE DBCMDROW(dbproc) DBPROCESS *dbproc; Comments This macro determines whether the current command is one that can return rows, that is, a TRANSACT-SQL SELECT statement or an EXECUTE on a stored procedure containing a SELECT. It should be called after dbresults returns SUCCEED. Even if this macro returns SUCCEED, the statement will not return any rows if none have qualified. To determine whether any rows are actually being returned, use the DBROWS macro. Parameters dbproc The DBPROCESS structure that is the handle for a particular workstation/SQL Server process. It contains all the information that DB-LIBRARY uses to manage communications and data between the workstation and SQL Server. Returns SUCCEED or FAIL to indicate whether this statement can return rows. See Also dbnextrow, dbresults, DBROWS, DBROWTYPE dbcolbrowse ──────────────────────────────────────────────────────────────────────────── Function Determines whether the source of a result column can be updated with the DB-LIBRARY browse-mode facilities. Syntax DBBOOL dbcolbrowse(dbproc, colnum) DBPROCESS *dbproc; int colnum; Comments The dbcolbrowse function is one of the DB-LIBRARY browse-mode functions. For a detailed discussion of browse mode, see Chapter 1, "Overview of DB-LIBRARY." The dbcolbrowse function provides a way to determine whether the database column that's the source of a result column in a select list can be updated with the DB-LIBRARY browse-mode facilities. This function is useful in examining ad hoc queries. If the query has been hardcoded into the program, dbcolbrowse is unnecessary. Only a column derived from a table that has a unique index and a timestamp column can be updated. It cannot be the result of a TRANSACT-SQL expression. For example, in the following select list, result columns 1 and 2 (title and category) can be updated, but column 3 (wholesale) cannot because it is the result of an expression: select title, category=type, wholesale=(price * 0.6) from inventory for browse The dbcolbrowse function can be called any time after dbresults. To determine the name of the source column given the name of the result column, use dbcolsource. Parameters dbproc The DBPROCESS structure that is the handle for a particular workstation/SQL Server process. It contains all the information that DB-LIBRARY uses to manage communications and data between the workstation and SQL Server. colnum The number of the result column. Column numbers start at 1. Returns TRUE or FALSE. See Also dbcolsource, dbqual, dbresults, dbtabbrowse, dbtabcount, dbtabname, dbtabsource, dbtsnewlen, dbtsnewval, dbtsput dbcollen ──────────────────────────────────────────────────────────────────────────── Function Returns the maximum length, in bytes, of the data for a column. Syntax DBINT dbcollen(dbproc, column) DBPROCESS *dbproc; int column; Comments This function returns the maximum length, in bytes, of the data for a particular column. In the case of variable length data, this is not necessarily the actual length of the data, but rather the maximum length that the data can be. For the actual data length, use dbdatlen. The following program fragment uses dbcollen: DBPROCESS *dbproc; int colnum; DBINT column_length; /* Put the command into the command buffer. */ dbcmd(dbproc, "select name, id, type from sysobjects"); /* Send the command to SQL Server and begin execution. */ dbsqlexec(dbproc); /* Process the command results. */ dbresults(dbproc); /* Examine the column lengths. */ for (colnum = 1; colnum < 4; colnum++) { column_length = dbcollen(dbproc, colnum); printf("column %d, length is %ld.\n", colnum, column_length); } Parameters dbproc The DBPROCESS structure that is the handle for a particular workstation/SQL Server process. It contains all the information that DB-LIBRARY uses to manage communications and data between the workstation and SQL Server. column The number of the column. The first column is number 1. Returns The maximum length of the data for the particular column. If the column number is not in range, -1 is returned. See Also dbcoltype, dbcolname, dbdata, dbdatlen, dbnumcols dbcolname ──────────────────────────────────────────────────────────────────────────── Function Returns the name of a particular result column. Syntax char *dbcolname(dbproc, column) DBPROCESS *dbproc; int column; Comments This function returns a pointer to the null-terminated name of a particular column. The following program fragment uses dbcolname: DBPROCESS *dbproc; /* Put the command into the command buffer. */ dbcmd(dbproc, "select name, id, type from sysobjects"); /* Send the command to SQL Server and begin execution. */ dbsqlexec(dbproc); /* Process the command results. */ dbresults(dbproc); /* Examine the column names. */ printf("first column name is %s\n", dbcolname(dbproc, 1)); printf("second column name is %s\n", dbcolname(dbproc, 2)); printf("third column name is %s\n", dbcolname(dbproc, 3)); Parameters dbproc The DBPROCESS structure that is the handle for a particular workstation/SQL Server process. It contains all the information that DB-LIBRARY uses to manage communications and data between the workstation and SQL Server. column The number of the column. The first column is number 1. Returns A char pointer to the null-terminated name of the particular column. If the column number is not in range, NULL is returned. See Also dbcollen, dbcoltype, dbdata, dbdatlen, dbnumcols dbcolsource ──────────────────────────────────────────────────────────────────────────── Function Returns a pointer to the name of the database column from which the specified result column was derived. Syntax char *dbcolsource(dbproc, colnum) DBPROCESS *dbproc; int colnum; Comments The dbcolsource function is one of the DB-LIBRARY browse-mode functions. For a detailed discussion of browse mode, see Chapter 1, "Overview of DB-LIBRARY." The dbcolsource function provides an application with information it needs to update a database column, based on an ad hoc query. SELECT statements can optionally specify header names for result columns: select author = au_lname from authors When updating a table, you must use the database column name, not the header name (in this example, au_lname, not author). You can use the dbcolsource function to get the underlying database column name: dbcolsource(dbproc, 1) This call returns a pointer to the string au_lname. The dbcolsource function is useful for ad hoc queries. If the query has been hardcoded into the program, this function is unnecessary. The dbcolsource function can be called any time after dbresults. Parameters dbproc The DBPROCESS structure that is the handle for a particular workstation/SQL Server process. It contains all the information that DB-LIBRARY uses to manage communications and data between the workstation and SQL Server. colnum The number of the result column. Column numbers start at 1. Returns A pointer to a null-terminated column name. This pointer will be NULL if the column number is out of range or if the column is the result of a TRANSACT-SQL expression, such as "max(colname)". See Also dbcolbrowse, dbqual, dbresults, dbtabbrowse, dbtabcount, dbtabname, dbtabsource, dbtsnewlen, dbtsnewval, dbtsput dbcoltype ──────────────────────────────────────────────────────────────────────────── Function Returns the datatype for a column. Syntax int dbcoltype(dbproc, column) DBPROCESS *dbproc; int column; Comments This function returns the type of the data for a particular column. For a list of SQL Server types, see Appendix C, "DB-LIBRARY Datatypes." The dbcoltype function returns an integer value for the type. Use dbprtype to convert the type value into a readable string. The following program fragment uses dbcoltype: DBPROCESS *dbproc; int colnum; int coltype; /* Put the command into the command buffer. */ dbcmd(dbproc, "select name, id, type from sysobjects"); /* Send the command to SQL Server and begin execution. */ dbsqlexec(dbproc); /* Process the command results. */ dbresults(dbproc); /* Examine the column types. */ for (colnum = 1; colnum < 4; colnum++) { coltype = dbcoltype(dbproc, colnum); printf("column %d, type is %s.\n", colnum, dbprtype(coltype)); } Parameters dbproc The DBPROCESS structure that is the handle for a particular workstation/SQL Server process. It contains all the information that DB-LIBRARY uses to manage communications and data between the workstation and SQL Server. column The number of the column. The first column is number 1. Returns A value for the datatype for the particular column. If the column is not in range, -1 is returned. ╓┌─────────────────────────────────┌───────────────────────────────────────── Column Return ──────────────────────────────────────────────────────────────────────────── SQLVARCHAR SQLCHAR SQLVARBINARY SQLBINARY SQLDATETIME SQLDATETIME SQLMONEY SQLMONEY SQLFLT8 SQLFLT8 SQLCHAR SQLCHAR SQLBINARY SQLBINARY SQLTEXT SQLTEXT Column Return ──────────────────────────────────────────────────────────────────────────── SQLIMAGE SQLIMAGE SQLINTn SQLINT1, SQLINT2, or SQLINT4, depending on the actual type of the SQLINTn See Also dbcollen, dbcolname, dbdata, dbdatlen, dbnumcols, dbprtype, Appendix C, "DB-LIBRARY Datatypes" dbconvert ──────────────────────────────────────────────────────────────────────────── Function Converts data from one datatype to another. Syntax int dbconvert(dbproc, srctype, src, srclen, desttype, dest, destlen) DBPROCESS *dbproc; int srctype; BYTE *src; DBINT srclen; int desttype; BYTE *dest; DBINT destlen; Comments This function allows the program to convert data from one representation to another. To determine whether a particular conversion is permitted, the program can call dbwillconvert before attempting a conversion. The dbconvert function can convert data stored in any of the SQL Server datatypes (although not all conversions are legal.) The following table shows the program variable type you must provide to receive the results from dbconvert for each SQL Server datatype. ╓┌─────────────────────────────────┌─────────────────────────────────────────╖ SQL Server Type Program Variable Type ──────────────────────────────────────────────────────────────────────────── SQLCHAR DBCHAR SQLTEXT DBCHAR SQLBINARY DBBINARY SQLIMAGE DBBINARY SQLINT1 DBTINYINT SQLINT2 DBSMALLINT SQL Server Type Program Variable Type ──────────────────────────────────────────────────────────────────────────── SQLINT2 DBSMALLINT SQLINT4 DBINT SQLFLT8 DBFLT8 SQLBIT DBBIT SQLMONEY DBMONEY SQLDATETIME DBDATETIME The following table lists the datatype conversions that dbconvert supports. The source datatypes are listed down the leftmost column, and the destination datatypes are listed along the top row of the table. (For brevity, the prefix "SQL" has been eliminated from each datatype.) T (TRUE) indicates that the conversion is supported; F (FALSE) indicates that the conversion is not supported. ╓┌─────────┌───────┌───────┌───────┌───────┌───────┌───────┌───────┌───────┌─ ──────────────────────────────────────────────────────────────────────────── To: char text binary image int1 int2 int4 flt8 bit From: char T T T T T T T T T text T T T T T T T T T binary T T T T T T T T T image T T T T T T T T T int1 T T T T T T T T T int2 T T T T T T T T T int4 T T T T T T T T T ──────────────────────────────────────────────────────────────────────────── int4 T T T T T T T T T flt8 T T T T T T T T T bit T T T T T T T T T money T T T T T T T T T datetime T T T T F F F F F ──────────────────────────────────────────────────────────────────────────── A conversion to or from the datatypes SQLBINARY and SQLIMAGE is a straight bit-copy except when the conversion involves SQLCHAR or SQLTEXT. When converting SQLCHAR or SQLTEXT data to SQLBINARY or SQLIMAGE, dbconvert interprets the SQLCHAR or SQLTEXT string as hexadecimal, whether or not the string contains a leading "0x". When converting SQLBINARY or SQLIMAGE data to SQLCHAR or SQLTEXT, dbconvert creates a hexadecimal string without a leading "0x". Converting an SQLMONEY, SQLCHAR, or SQLTEXT value to SQLFLT8 can result in some loss of precision. Converting an SQLFLT8 value to SQLCHAR or SQLTEXT can also result in some loss of precision. Converting an SQLFLT8 value to SQLMONEY can result in overflow because the maximum value for SQLMONEY is $922,337,203,685,477.58. If overflow occurs when converting integer or float data to SQLCHAR or SQLTEXT, the first character of the resulting value will contain an asterisk ("*") to indicate the error. The following example illustrates how to convert SQL Server data obtained with dbdata: DBCHAR title[81]; DBCHAR price[9]; /* Read the query into the command buffer. */ dbcmd(dbproc, "select title, price, royalty from pubs..titles"); /* Send the query to SQL Server. */ dbsqlexec(dbproc); /* Get ready to process the results of the query. */ dbresults(dbproc); /* Process each row. */ while (dbnextrow(dbproc) != NO_MORE_ROWS) { /* The first dbconvert() adds a null terminator to the string. */ dbconvert(dbproc, SQLCHAR, (dbdata(dbproc,1)), (dbdatlen(dbproc,1)), SQLCHAR, title, (DBINT)-1); /* The second dbconvert() converts money to string. */ dbconvert(dbproc, SQLMONEY, (dbdata(dbproc,2)), (DBINT)-1, SQLCHAR, price, (DBINT)-1); if (dbdatlen(dbproc,3) != 0) printf ("%s\n $%s %ld\n", title, price, *((DBINT *)dbdata(dbproc,3))); } If you're binding data to variables rather than accessing the data directly, you can use dbbind to perform the conversions rather than dbconvert. Example 5 in Chapter 3, "Example Programs," illustrates several more types of conversions using dbconvert. Parameters dbproc The DBPROCESS structure that is the handle for a particular workstation/SQL Server process. It contains all the information that DB-LIBRARY uses to manage communications and data between the workstation and SQL Server. In dbconvert, the DBPROCESS is used only to supply any custom null values that the program specified through dbsetnull. If dbproc is NULL, dbconvert uses the default values for null value data conversions. srctype The datatype of the data to be converted. This parameter can be any of the SQL Server datatypes. You can use dbcoltype to get the SQL Server datatype for a particular column. src A pointer to the data to be converted. If this pointer is NULL, dbconvert will place an appropriate null value in the destination variable. You can use dbdata to get the SQL Server data. srclen The length, in bytes, of the data to be converted. If the srclen is 0, the source data is assumed to be null, and dbconvert places an appropriate null value in the destination variable. Otherwise, this length is ignored for all datatypes except char, text, binary, and image. For SQLCHAR data, a length of -1 indicates that the string is null-terminated. desttype The datatype that the source data is to be converted into. This parameter can be any of the SQL Server datatypes. dest A pointer to the destination variable that will receive the converted data. If this pointer is NULL, dbconvert will call the user-supplied error handler (if any) and return -1. destlen The length, in bytes, of the destination variable. The destlen is ignored for fixed-length datatypes. For an SQLCHAR destination, the value of destlen must be the total length of the destination buffer space. A length of -1 for an SQLCHAR or SQLBINARY destination means that there is sufficient space available. Note that for an SQLCHAR destination, a length of -1 causes the character string to be given a terminating null. You can use dbdatlen to get the length of SQL Server data. Returns The length of the converted data, in bytes, if the datatype conversion succeeds. If the conversion fails, -1 is returned. If dbconvert fails, it first calls a user-supplied error handler (if any). This routine may fail for one of these reasons: ■ The requested conversion was unavailable. ■ The conversion resulted in truncation, overflow, or loss of precision in the destination variable. ■ A syntax error occurred in converting a character string to some numeric type. See Also dbaltbind, dbbind, dbcoltype, dbdata, dbdatlen, dberrhandle, dbsetnull, dbwillconvert, Appendix C, Appendix D DBCOUNT ──────────────────────────────────────────────────────────────────────────── Function Returns the number of rows affected by a TRANSACT-SQL statement. Syntax DBINT DBCOUNT(dbproc) DBPROCESS *dbproc; Comments Once the results of a statement have been processed, you can call DBCOUNT to find out how many rows were affected by the statement. For example, if a SELECT statement was sent to SQL Server and you have read all the rows by calling dbnextrow until it returned NO_MORE_ROWS, you can call this macro to find out how many rows were retrieved. If the current statement doesn't immediately return rows (for example, a DELETE), you can call DBCOUNT after dbresults. Parameters dbproc The DBPROCESS structure that is the handle for a particular workstation/SQL Server process. It contains all the information that DB-LIBRARY uses to manage communications and data between the workstation and SQL Server. Returns The number of rows affected. See Also dbnextrow, dbresults DBCURCMD ──────────────────────────────────────────────────────────────────────────── Function Returns the number of the current command. Syntax int DBCURCMD(dbproc) DBPROCESS *dbproc; Comments This macro returns the number of the command whose results are currently being processed. The first command in a batch is number 1. The command number is incremented every time dbresults returns SUCCEED or FAIL. (Unsuccessful commands are counted.) The command number is reset by each call to dbsqlexec or dbsqlsend. Parameters dbproc The DBPROCESS structure that is the handle for a particular workstation/SQL Server process. It contains all the information that DB-LIBRARY uses to manage communications and data between the workstation and SQL Server. Returns The number of the current command. The first command in a batch is number 1. See Also DBCMDROW, DBMORECMDS, DBROWS, dbsqlexec, dbsqlsend DBCURROW ──────────────────────────────────────────────────────────────────────────── Function Returns the number of the row currently being read. Syntax DBINT DBCURROW(dbproc) DBPROCESS *dbproc; Comments This macro returns the number of the current row being read. The first row returned from SQL Server is number 1. The row number is changed every time dbnextrow or dbgetrow returns SUCCEED. The row number is reset to 0 by each new call to dbresults. Parameters dbproc The DBPROCESS structure that is the handle for a particular workstation/SQL Server process. It contains all the information that DB-LIBRARY uses to manage communications and data between the workstation and SQL Server. Returns The number of the current row. The first row returned from SQL Server is number 1. See Also dbclrbuf, DBFIRSTROW, dbgetrow, DBLASTROW, dbnextrow, dbresults, dbsetopt, Appendix B dbdata ──────────────────────────────────────────────────────────────────────────── Function Returns a pointer to the data for a result column. Syntax BYTE *dbdata(dbproc, column) DBPROCESS *dbproc; int column; Comments This function returns a pointer to the data for a particular column. The data is not null-terminated. You can use dbdatlen to get the length of the data. The dbbind function automatically binds data to your program variables. It is sometimes easier to use than dbdata, but it makes a copy of the data. The following program fragment uses dbdata: DBPROCESS *dbproc; DBINT row_number = 0; DBINT object_id; /* Put the command into the command buffer. */ dbcmd(dbproc, "select id from sysobjects"); /* Send the command to SQL Server and begin execution. */ dbsqlexec(dbproc); /* Process the command results. */ dbresults(dbproc); /* Examine the data in each row. */ while (dbnextrow(dbproc) != NO_MORE_ROWS) { row_number++; object_id = *((DBINT *)dbdata(dbproc, 1)); printf("row %ld, object id is %ld.\n", row_number, object_id); } Parameters dbproc The DBPROCESS structure that is the handle for a particular workstation/SQL Server process. It contains all the information that DB-LIBRARY uses to manage communications and data between the workstation and SQL Server. column The number of the column. The first column is number 1. Returns A BYTE pointer to the data for the column. A NULL BYTE pointer is returned if there is no such column or if the data has a null value. To make sure that the data is really a null value, check for a return of 0 from dbdatlen. See Also dbbind, dbcollen, dbcolname, dbcoltype, dbdatlen, dbnumcols dbdataready ──────────────────────────────────────────────────────────────────────────── Function Determines whether database command processing has completed. Syntax BOOL dbdataready(dbproc) DBPROCESS *dbproc; Comments This function allows an application to continue processing while SQL Server is actually performing the database operation. The dbdataready function is ordinarily used after a call to dbsqlsend and before a call to dbsqlok. After dbsqlsend, SQL Server begins executing the commands in the command buffer. When dbsqlok is called, DB-LIBRARY waits for SQL Server processing to be completed before returning control to the application program. The dbdataready function provides a way to determine when command processing has been completed. It should be called repeatedly until it returns a nonzero value. At that point, the application can call dbsqlok. ──────────────────────────────────────────────────────────────────────────── NOTE It is possible for dbdataready to return FALSE forever if another process has a conflicting lock or if the connection is broken. It is the responsibility of the calling program to provide a time-out mechanism. ──────────────────────────────────────────────────────────────────────────── Parameters dbproc The DBPROCESS structure that is the handle for a particular workstation/SQL Server process. It contains all the information that DB-LIBRARY uses to manage communications and data between the workstation and SQL Server. Returns TRUE if data is available to be read; FALSE otherwise. See Also dbresults, dbsqlok, dbsqlsend dbdatlen ──────────────────────────────────────────────────────────────────────────── Function Returns the actual length, in bytes, of the data for a column. Syntax DBINT dbdatlen(dbproc, column) DBPROCESS *dbproc; int column; Comments This function returns the actual length, in bytes, of the data for a particular column. The maximum possible length for the data can be determined with the dbcollen function. The data itself is available by calling dbdata. The following program fragment uses dbdatlen: DBPROCESS *dbproc; DBINT row_number = 0; DBINT data_length; /* Put the command into the command buffer. */ dbcmd(dbproc, "select name from sysobjects"); /* Send the command to SQL Server and begin execution. */ dbsqlexec(dbproc); /* Process the command results. */ dbresults(dbproc); /* Examine the data lengths of each row. */ while (dbnextrow(dbproc) != NO_MORE_ROWS) { row_number++; data_length = dbdatlen(dbproc, 1); printf("row %ld, data length is %ld.\n", row_number, data_length); } Parameters dbproc The DBPROCESS structure that is the handle for a particular workstation/SQL Server process. It contains all the information that DB-LIBRARY uses to manage communications and data between the workstation and SQL Server. column The number of the column. The first column is number 1. Returns The actual length of the data for the particular column. If the data has a null value, 0 is returned. If the column number is not in range, -1 is returned. See Also dbcollen, dbcolname, dbcoltype, dbdata, dbnumcols DBDEAD ──────────────────────────────────────────────────────────────────────────── Function Determines whether a particular DBPROCESS is dead. Syntax DBBOOL DBDEAD(dbproc) DBPROCESS *dbproc; Comments This macro indicates whether the specified DBPROCESS is dead. It is particularly useful in user-supplied error handlers. If a DBPROCESS is dead, then almost every DB-LIBRARY function that receives it as a parameter immediately fails, calling the user-supplied error handler. Parameters dbproc The DBPROCESS structure that is the handle for a particular workstation/SQL Server process. It contains all the information that DB-LIBRARY uses to manage communications and data between the workstation and SQL Server. Returns TRUE or FALSE. See Also dberrhandle, Appendix D dberrhandle ──────────────────────────────────────────────────────────────────────────── Function Supplies a user function to handle DB-LIBRARY errors. Syntax int (*dberrhandle(handler)) () int (*handler) (); Comments This function installs an error-handling function that you supply. When a DB-LIBRARY error occurs, DB-LIBRARY immediately calls this error handler. You must install an error handler to handle DB-LIBRARY errors properly. Since the error handler is a call-back function, special consideration is required when compiling these functions under Windows or MS OS/2. For more information, see Appendix E, "Building Applications." The user-supplied error handler completely determines the response of DB-LIBRARY to any error that occurs. It must tell DB-LIBRARY whether to ■ Abort the program ■ Return an error code ■ Keep trying (in the case of a timeout error) If the user does not supply an error handler (or passes a NULL pointer to dberrhandle), DB-LIBRARY exhibits its default error-handling behavior: It returns FAIL from the DB-LIBRARY function that caused the error; program execution continues. Parameters handler A pointer to the user function that is called whenever DB-LIBRARY determines that an error has occurred. DB-LIBRARY calls this function with six parameters: dbproc The affected DBPROCESS. If there is no DBPROCESS associated with this error, this parameter is NULL. severity The severity of the error (datatype int). Error severities are defined in sqlfront.h. dberr The identifying number of the error (datatype int). Error numbers are defined in sqlfront.h. oserr The operating-system-specific error number that describes the cause of the error (datatype int). If there is no relevant operating system error, the value of this parameter is DBNOERR. dberrstr A printable description of dberr (datatype char *). oserrstr A printable description of oserr (datatype char *). The error handler must return one of the following three values, directing DB-LIBRARY to perform particular actions: ╓┌─────────────────────────────────┌───────────────────────────────────────── Action Value ──────────────────────────────────────────────────────────────────────────── INT_EXIT Prints an error message and aborts the program. DB-LIBRARY also returns an error to the operating system. Under Windows, this value is considered an error and is treated as an INT_CANCEL. INT_CANCEL Returns FAIL from the DB-LIBRARY function that caused the error. Action Value ──────────────────────────────────────────────────────────────────────────── INT_CONTINUE Continues to wait for one additional timeout period. At the end of that period, calls the error handler again. This return value is meaningful only for timeout errors (SQLETIME). In any other case, this value is considered an error and is treated as an INT_CANCEL. ──────────────────────────────────────────────────────────────────────────── If the error handler returns any value besides these three, the program continues. The following example shows a typical error-handling function: #include <sqlfront.h> #include <sqldb.h> int err_handler(dbproc, severity, dberr, oserr, dberrstr, oserrstr) DBPROCESS *dbproc; int severity; int dberr; int oserr; char *dberrstr; char *oserrstr; { if ((dbproc == NULL) || (DBDEAD(dbproc))) return(INT_EXIT); else { printf("DB-LIBRARY error:\n\t%s\n", dberrstr); if (oserr != DBNOERR) printf("Operating-system error:\n\t%s\n", oserrstr); return(INT_CANCEL); } } ──────────────────────────────────────────────────────────────────────────── NOTE Do not call any DB-LIBRARY functions from within the error handler, as this may result in infinite recursive calls to the error handler. You may, however, call DB-LIBRARY macros; they have been coded to avoid recursion. ──────────────────────────────────────────────────────────────────────────── Returns A pointer to the previously installed error handler. This can be NULL. See Also dbmsghandle, Appendix D dbexit ──────────────────────────────────────────────────────────────────────────── Function Closes and frees all DBPROCESS structures created as a result of your program. Syntax void dbexit(void) Comments The dbexit function calls dbclose repeatedly for all allocated DBPROCESS structures. The dbclose function cleans up any activity associated with a DBPROCESS structure and frees the space. You can use dbclose directly to close just a single DBPROCESS structure. Returns None. See Also dbclose, dbopen dbfcmd ──────────────────────────────────────────────────────────────────────────── Function Adds text to the DBPROCESS command buffer using C run-time library sprintf-type formatting. Syntax RETCODE dbfcmd(dbproc, cmdstring, params...) DBPROCESS *dbproc; char *cmdstring; long params...; Comments This function adds text to the command buffer in the DBPROCESS structure. The dbfcmd function works just like the C language Standard I/O library sprintf function. To include a percent character (%) in the command string, you must encode it as two percent characters (%%). This is because dbfcmd calls sprintf, which treats the % character as a format specification. In addition, don't use variables containing strings with apostrophes or single quotation marks. If you don't need any of the formatting capability of sprintf, you can use dbcmd. The dbfcmd function manages the space allocation for the command buffer. It adds to the existing command buffer; it doesn't delete or overwrite the current contents except after the buffer has been sent to SQL Server. (See the following comment.) The user can call dbfcmd repeatedly. Note that sequential calls are concatenated together: it is the program's responsibility to ensure that any necessary blanks appear between the end of one line and the beginning of the next. After a call to dbsqlexec or dbsqlsend, the first call to either dbcmd or dbfcmd automatically clears the command buffer before the new text is entered. If this situation is undesirable, set the DBNOAUTOFREE option. When DBNOAUTOFREE is set, the command buffer is cleared only by a call to dbfreebuf. The following program fragment uses dbfcmd to build up a multiple-line SQL command: char *column_name; DBPROCESS *dbproc; int low_id; char *object_type; char *tablename; dbfcmd(dbproc, "Select %s from %s", column_name, tablename); dbfcmd(dbproc, " where id > %d", low_id); dbfcmd(dbproc, " and type='%s'", object_type); Do not pass dbfcmd null pointers contained in variables. Parameters dbproc The DBPROCESS structure that is the handle for a particular workstation/SQL Server process. It contains all the information that DB-LIBRARY uses to manage communications and data between the workstation and SQL Server. cmdstring A format string of the form used by the sprintf function. params... Optional parameters to dbfcmd. The number of parameters required depends on the number indicated in the cmdstring parameter. The parameters are passed directly to the sprintf function. Returns SUCCEED or FAIL. Limitations This function allocates its working buffer dynamically. The size it picks to allocate space is the maximum of a defined constant (1024) and the string length of cmdstring * 2. If the arguments are very big in comparison to the size of cmdstring, it may not be possible to allocate enough space. See Also dbcmd, dbfreebuf, Appendix B DBFIRSTROW ──────────────────────────────────────────────────────────────────────────── Function Returns the number of the first row in the row buffer. Syntax DBINT DBFIRSTROW(dbproc) DBPROCESS *dbproc; Comments This macro returns the number of the first row in the row buffer. If you aren't buffering rows, DBFIRSTROW, DBCURROW, and DBLASTROW always have the same value: the current row number in the current batch. If you enable buffering by setting the DBBUFFER option, DBFIRSTROW returns the number of the row that is the first row in the row buffer. Note that the first row returned from SQL Server (whose value is 1) is not necessarily the first row sitting in the row buffer. The rows in the row buffer are dependent on manipulation by the application program. Parameters dbproc The DBPROCESS structure that is the handle for a particular workstation/SQL Server process. It contains all the information that DB-LIBRARY uses to manage communications and data between the workstation and SQL Server. Returns The number of the first row in the row buffer. The first row returned from SQL Server is number 1. See Also dbclrbuf, DBCURROW, dbgetrow, DBLASTROW, dbnextrow, dbsetopt, Appendix B dbfreebuf ──────────────────────────────────────────────────────────────────────────── Function Sets the command buffer in a DBPROCESS structure to NULL. Syntax void dbfreebuf(dbproc) DBPROCESS *dbproc; Comments This function frees any space allocated to the command buffer of the DBPROCESS structure. The command buffer is then set to NULL. Commands for SQL Server are added to the command buffer with the dbcmd or dbfcmd function. After a call to dbsqlexec or dbsqlsend, the first call to either dbcmd or dbfcmd automatically calls dbfreebuf to clear the command buffer before the new text is entered. If this situation is undesirable, set the DBNOAUTOFREE option. When DBNOAUTOFREE is set, the command buffer is cleared only by a call to dbfreebuf. The contents of the command buffer can be accessed through the dbgetchar, dbstrlen, and dbstrcpy functions. Parameters dbproc The DBPROCESS structure that is the handle for a particular workstation/SQL Server process. It contains all the information that DB-LIBRARY uses to manage communications and data between the workstation and SQL Server. Returns None. See Also dbcmd, dbfcmd, dbgetchar, dbsqlexec, dbsqlsend, dbstrcpy, dbstrlen, Appendix B dbfreelogin ──────────────────────────────────────────────────────────────────────────── Function Frees a login record. Syntax void dbfreelogin(login) LOGINREC *login; Comments This function frees up the memory allocated by the dblogin function. It can be called immediately after a call to dbopen; however, you can use the same login record for multiple calls to dbopen. Call dbfreelogin when you are completely finished with the login record. The following program fragment uses dbfreelogin: DBPROCESS *dbproc; LOGINREC *loginrec; loginrec = dblogin(); DBSETLUSER(login, "user"); DBSETLPWD(loginrec, "my_password"); DBSETLAPP(loginrec, "my_program"); dbproc = dbopen(loginrec, "my_server"); dbfreelogin (loginrec); Parameters login The pointer to a LOGINREC structure returned from the dblogin function. Returns None. See Also dblogin, dbopen dbfreequal ──────────────────────────────────────────────────────────────────────────── Function Frees memory allocated by the dbqual function. Syntax void dbfreequal(ptr) char *ptr; Comments Whenever a successful call to dbqual has been issued, a buffer is dynamically allocated by dbqual. The buffer remains allocated to the application. When the application is finished using the buffer, it should be freed by invoking the dbfreequal function. Parameters ptr A pointer to the memory allocated by the dbqual function. Returns None. See Also dbqual dbgetchar ──────────────────────────────────────────────────────────────────────────── Function Returns a pointer to a character in the command buffer. Syntax char *dbgetchar(dbproc, n) DBPROCESS *dbproc; int n; Comments The dbgetchar function can be used to find a particular character in the command buffer. It returns a pointer to the nth character in the command buffer. Internally, the command buffer is a linked list of non-null-terminated text strings. Parts of the command buffer can be located and copied using the dbgetchar, dbstrcpy, and dbstrlen functions. Since the command buffer is not just one large text string, but rather a linked list of text strings, you must use dbgetchar to index through the buffer. If you just get a pointer using dbgetchar and then increment it yourself, it will probably fall off the end of a string (and cause a segmentation fault under MS OS/2). Parameters dbproc The DBPROCESS structure that is the handle for a particular workstation/SQL Server process. It contains all the information that DB-LIBRARY uses to manage communications and data between the workstation and SQL Server. n The character to find in the command buffer. The first character is the 0th character. Returns A pointer to the nth character in the command buffer. If n is not in range, NULL is returned. See Also dbcmd, dbfcmd, dbfreebuf, dbstrcpy, dbstrlen dbgetmaxprocs ──────────────────────────────────────────────────────────────────────────── Function Determines the current maximum number of simultaneously open DBPROCESS structures. Syntax int dbgetmaxprocs() Comments A DB-LIBRARY program has a maximum number of simultaneously open DBPROCESS structures. By default, this number is 25. The application program can change this limit by calling dbsetmaxprocs. Returns An integer representing the current limit on the number of simultaneously open DBPROCESS structures. See Also dbopen, dbsetmaxprocs dbgetoff ──────────────────────────────────────────────────────────────────────────── Function Checks for the existence of TRANSACT-SQL statements in the command buffer. Syntax int dbgetoff(dbproc, offtype, startfrom) DBPROCESS *dbproc; DBUSMALLINT offtype; int startfrom; Comments If the DBOFFSET option has been set (see Appendix B, "DB-LIBRARY Options"), this function can check for the location of certain TRANSACT-SQL statements in the command buffer. As a simple example, assume that the program doesn't know the contents of the command buffer but needs to know where the SQL keyword SELECT appears: int select_offset[10]; int last_offset; int i; /* Set the offset option. */ dbsetopt(dbproc, DBOFFSET, "select"); /* Assume the command buffer contains the following SELECTs: */ dbcmd(dbproc, "select x = 100 select y = 5"); /* Send the query to SQL Server. */ dbsqlexec(dbproc); /* Get all the offsets to the SELECT keyword. */ for (i = 0, last_offset = 0; last_offset != -1; i++) if ((last_offset = dbgetoff(dbproc, OFF_SELECT, last_offset))!= -1) select_offset[i] = last_offset++; In this example, select_offset[0] = 0 and select_offset[1] = 15. Parameters dbproc The DBPROCESS structure that is the handle for a particular workstation/SQL Server process. It contains all the information that DB-LIBRARY uses to manage communications and data between the workstation and SQL Server. offtype The type of offset you want to find. The types (defined in the header file sqlfront.h) are: OFF_SELECT OFF_FROM OFF_ORDER OFF_COMPUTE OFF_TABLE OFF_PROCEDURE OFF_STATEMENT OFF_PARAM OFF_EXEC For details, see Appendix B, "DB-LIBRARY Options." startfrom The point in the buffer from which to start looking. The command buffer begins at 0. Returns The character offset into the command buffer for the specified offset. If the offset is not found, -1 is returned. See Also dbcmd, dbgetchar, dbsetopt, dbstrcpy, dbstrlen, Appendix B dbgetrow ──────────────────────────────────────────────────────────────────────────── Function Reads the specified row in the row buffer. Syntax STATUS dbgetrow(dbproc, row) DBPROCESS *dbproc; DBINT row; Comments The dbgetrow function sets the current row to a specific row and reads it. This function only works if the DBBUFFER option is on. Any specified binding of row data to program variables takes effect. When buffering is not turned on, normally each row is processed, in turn, by repeatedly calling dbnextrow until it returns NO_MORE_ROWS. When buffering is turned on, dbgetrow allows the user to jump to any row that has already been read using dbnextrow and is still in the row buffer. Calls to dbnextrow after a dbgetrow call return rows in order, following the row read by dbgetrow. Parameters dbproc The DBPROCESS structure that is the handle for a particular workstation/SQL Server process. It contains all the information that DB-LIBRARY uses to manage communications and data between the workstation and SQL Server. row The number of the row to read. The first row returned from SQL Server is number 1. Returns One of four different types of values: ■ If the current row is a regular row, REG_ROW is returned. ■ If the current row is a compute row, the computeid of the row is returned. (See "dbaltbind" for information on the computeid.) ■ If the row is not in the row buffer, NO_MORE_ROWS is returned. ■ If the function was unsuccessful, FAIL is returned. See Also dbaltbind, dbbind, dbclrbuf, dbnextrow, Appendix B DBGETTIME ──────────────────────────────────────────────────────────────────────────── Function Returns the number of seconds that DB-LIBRARY will wait for an SQL Server response to a TRANSACT-SQL statement. Syntax int DBGETTIME() Comments This macro returns the length of time in seconds that DB-LIBRARY will wait for an SQL Server response during calls to dbsqlexec, dbsqlok, dbresults, and dbnextrow. The default timeout value is 0, which represents an infinite timeout period. The program can call dbsettime to change the timeout value. Returns The timeout value─the number of seconds that DB-LIBRARY waits for an SQL Server response before timing out. A timeout value of 0 represents an infinite timeout period. See Also dbnextrow, dbresults, dbsettime, dbsqlexec, dbsqlok DBISAVAIL ──────────────────────────────────────────────────────────────────────────── Function Determines whether a DBPROCESS is available for general use. Syntax DBBOOL DBISAVAIL(dbproc) DBPROCESS *dbproc; Comments This macro indicates whether the specified DBPROCESS is available for general use. When a DBPROCESS is first opened, it is marked as being available until some use is made of it. Many DB-LIBRARY functions automatically set the DBPROCESS to "not available," but only dbsetavail resets it to "available." This facility is useful when several parts of a program are attempting to share a single DBPROCESS. Parameters dbproc The DBPROCESS structure that is the handle for a particular workstation/SQL Server process. It contains all the information that DB-LIBRARY uses to manage communications and data between the workstation and SQL Server. Returns TRUE if the DBPROCESS is available for general use; FALSE otherwise. See Also dbsetavail dbinit ──────────────────────────────────────────────────────────────────────────── Function Initializes DB-LIBRARY. Syntax CHAR *dbinit(void) Comments This function behaves differently under each supported operating system. Under Windows, you must call dbinit before calling any other DB-LIBRARY functions. ■ Under MS OS/2, dbinit returns a pointer to the version string. Calling dbinit is optional. ■ Under Windows, dbinit causes DB-LIBRARY to "register" the calling application to prevent conflicts with other applications calling DB-LIBRARY concurrently. (See dbwinexit.) You must call dbinit under Windows. ■ Under MS-DOS(R), dbinit tests for the presence of the DB-LIBRARY communications TSR (Terminate-Stay-Resident) utility. If the function returns a NULL pointer (memory model dependent pointer type), the TSR is not loaded. A call to dbopen also checks for the presence of the TSR and returns a null DBPROCESS pointer if it is not loaded. Parameters None. Returns If initialization is successful, the function returns a pointer to a string containing the version number of the library. Otherwise, dbinit returns NULL. See Also dbopen, dbwinexit dbisopt ──────────────────────────────────────────────────────────────────────────── Function Checks the status of an SQL Server or DB-LIBRARY option. Syntax DBBOOL dbisopt(dbproc, option, param) DBPROCESS *dbproc; int option; char *param; Comments This function checks the status of SQL Server and DB-LIBRARY options. Although SQL Server options can be set and cleared directly through SQL, the application should use dbsetopt and dbclropt to set and clear options. This provides a uniform interface for setting both SQL Server and DB-LIBRARY options. It also allows the application to use the dbisopt function to check the status of an option. For a list of each option and its default status, see Appendix B, "DB-LIBRARY Options." Parameters dbproc The DBPROCESS structure that is the handle for a particular workstation/SQL Server process. It contains all the information that DB-LIBRARY uses to manage communications and data between the workstation and SQL Server. Unlike the functions dbsetopt and dbclropt, dbproc cannot be NULL here. option The option to be checked. param Certain options take parameters. The DBOFFSET option, for example, takes as a parameter the SQL construct for which offsets are to be returned. Appendix B, "DB-LIBRARY Options," lists those options that take parameters. If an option does not take a parameter, param must be NULL. If the option you are checking takes a parameter but there can be only one instance of the option, dbisopt ignores the param parameter. For example, dbisopt ignores the value of param when checking the DBBUFFER option because row buffering can have only one setting at a time. On the other hand, the DBOFFSET option can have several settings, each with a different parameter. It can have been set twice─to look for offsets to SELECT statements and offsets to ORDER BY clauses. In that case, dbisopt needs the param parameter to determine whether to check the SELECT offset or the ORDER BY offset. The only other option for which dbisopt requires a param is DBSTAT. Returns TRUE or FALSE. See Also dbclropt, dbsetopt, Appendix B DBLASTROW ──────────────────────────────────────────────────────────────────────────── Function Returns the number of the last row in the row buffer. Syntax DBINT DBLASTROW(dbproc) DBPROCESS *dbproc; Comments This macro returns the number of the last row in the row buffer. The first row returned from SQL Server is number 1. If you aren't buffering rows, DBFIRSTROW, DBCURROW, and DBLASTROW always have the same value. If you have enabled buffering by setting the DBBUFFER option, DBLASTROW returns the number of the row that is the last row in the row buffer, which may not be the last row available from the server. Parameters dbproc The DBPROCESS structure that is the handle for a particular workstation/SQL Server process. It contains all the information that DB-LIBRARY uses to manage communications and data between the workstation and SQL Server. Returns The number of the last row in the row buffer. The first row returned from SQL Server is number 1. See Also dbclrbuf, DBCURROW, DBFIRSTROW, dbgetrow, dbnextrow, dbsetopt, Appendix B DBLOCKLIB (Windows only) ──────────────────────────────────────────────────────────────────────────── Function Locks DB-LIBRARY segments. Syntax DBLOCKLIB() Comments This macro applies only to Microsoft Windows application programs. It is not available in the MS-DOS or MS OS/2 DB-LIBRARY. This macro is necessary because segments in Windows libraries move around. If an application (or DB-LIBRARY function) is relying on a far pointer for later use, the segments must remain stable. For example, if a far pointer value is retrieved from the dbname function, DB-LIBRARY must remain locked until the pointer value is no longer used by the application. The DBLOCKLIB macro locks the DB-LIBRARY segments. It should be called prior to calling any DB-LIBRARY functions. The DBUNLOCKLIB macro should be called after returning from a DB-LIBRARY function and only after the application uses any pointer information returned from a function call. The DBLOCKLIB macro can be called once, then many DB-LIBRARY functions can be called, and then the DB-LIBRARY segments can be unlocked with the DBUNLOCKLIB macro. Note that DB-LIBRARY can be unlocked after a call to dbopen or dblogin because the value returned from dbopen or dblogin is a near pointer only used within DB-LIBRARY. The following program fragment calls DBLOCKLIB: DBPROCESS *dbproc; LOGINREC *login; char far *name; DBLOCKLIB(); login = dblogin(); DBSETLUSER(login, "user"); DBSETLPWD(login, "my_password"); DBSETLAPP(login, "my program"); dbproc = dbopen(login, "my_server"); DBUNLOCKLIB(); . . . DBLOCKLIB(); name = dbname(dbproc); /* use name */ . . . /* name no longer required */ . . . DBUNLOCKLIB(); . . . Parameters None. Returns None. See Also dblogin, dbname, dbopen, DBUNLOCKLIB dblogin ──────────────────────────────────────────────────────────────────────────── Function Allocates a login record for use in dbopen. Syntax LOGINREC *dblogin() Comments This function allocates a LOGINREC structure for use with dbopen. There are various functions available to supply components of the LOGINREC. The program can supply the workstation name, user name, user password, and application name. It is generally only necessary for the program to supply the user password (and even this can be eliminated if the password is a null value). All components in the LOGINREC are initially set to NULL. Returns A pointer to a LOGINREC structure. NULL is returned if the structure could not be allocated. See Also dbfreelogin, dbopen, DBSETLAPP, DBSETLHOST, DBSETLPWD, DBSETLUSER DBMORECMDS ──────────────────────────────────────────────────────────────────────────── Function Indicates whether there are more commands to be processed. Syntax RETCODE DBMORECMDS(dbproc) DBPROCESS *dbproc; Comments This macro determines whether there are more results to process. It should be called after dbnextrow returns NO_MORE_ROWS. If you know that the current command is returning no rows, you can call DBMORECMDS after dbresults returns. You can get the same information by calling dbresults until it returns NO_MORE_RESULTS. Parameters dbproc The DBPROCESS structure that is the handle for a particular workstation/SQL Server process. It contains all the information that DB-LIBRARY uses to manage communications and data between the workstation and SQL Server. Returns SUCCEED or FAIL to indicate whether there are more results in the command batch. See Also DBCMDROW, dbnextrow, dbresults, DBROWS, DBROWTYPE dbmoretext ──────────────────────────────────────────────────────────────────────────── Function Sends part of a text or image value to SQL Server. Syntax RETCODE dbmoretext(dbproc, size, text) DBPROCESS *dbproc; DBINT size; BYTE *text; Comments This function is used in conjunction with dbwritetext to send a large SQLTEXT or SQLIMAGE value to SQL Server in the form of a number of smaller chunks. This is particularly useful with operating systems unable to allocate extremely long data buffers. A text/image fragment cannot be longer than 64K bytes. DB-LIBRARY does not support huge pointers, only near (for medium model MS-DOS) and far (for large model MS-DOS, Windows, and MS OS/2.) The dbmoretext and dbwritetext functions are used in updates only to replace the TRANSACT-SQL UPDATE statement. See "dbwritetext" for an example of dbmoretext. Parameters dbproc The DBPROCESS structure that is the handle for a particular workstation/SQL Server process. It contains all the information that DB-LIBRARY uses to manage communications and data between the workstation and SQL Server. size The size, in bytes, of this particular part of the text or image value being sent to SQL Server. It is an error to send more text or image bytes to SQL Server than were specified in the call to dbwritetext. text A pointer to the text or image portion to be written. Returns SUCCEED or FAIL. See Also dbtxptr, dbtxtimestamp, dbwritetext dbmsghandle ──────────────────────────────────────────────────────────────────────────── Function Supplies a user function to handle SQL Server messages. Syntax int (*dbmsghandle(handler))() int (*handler)(); Comments This function installs a message-handling function that you supply. When DB-LIBRARY receives an SQL Server error or informational message, it immediately calls this message handler. You must install a message handler to handle SQL Server messages properly. Since the message handler is a call-back function, special considerations are required when compiling these functions under Windows or MS OS/2. For more information, see Appendix E, "Building Applications." The message-handling function should not call any DB-LIBRARY functions. Since calls to DB-LIBRARY functions can themselves generate messages, calls from within a message handler could result in infinite recursion. If your message handler must call a DB-LIBRARY function, it should set the message-handler to a null value, and then restore it when it exits. The following code fragment illustrates this technique: int msg_handler (dbproc, . . . { /* Set the message handler to NULL to prevent infinite recursion. */ dbmsghandle(NULL); /* Call other DB_LIBRARY functions as necessary. */ . . . /* Reset the message handler to this function. */ dbmsghandle(msg_handler); return(. . . } Parameters handler A pointer to the user function that is called whenever DB-LIBRARY receives an error or informational message from SQL Server. DB-LIBRARY calls this function with five parameters: dbproc The affected DBPROCESS. msgno The current message's number (datatype DBINT). These numbers are documented in the SQL Server System Administrator's Guide. msgstate The current message's error state number (datatype int). These numbers provide information about the context of the error. severity The current message's information class or error severity (datatype int). These numbers are documented in the SQL Server System Administrator's Guide. msgtext The null-terminated text of the current message (datatype char *). The message handler must return a value of 0 to DB-LIBRARY. The following example shows a typical message-handling function: #include <sqlfront.h> #include <sqldb.h> int msg_handler(dbproc, msgno, msgstate, severity, msgtext) DBPROCESS *dbproc; DBINT msgno; DBSMALLINT msgstate; DBSMALLINT severity; char *msgtext; { printf("SQL Server message %ld, state %d, " "severity %d:\n\t%s\n", msgno, msgstate, severity, msgtext); return(0); } Returns A pointer to the previously installed message handler. This can be NULL. See Also dberrhandle dbname ──────────────────────────────────────────────────────────────────────────── Function Returns the name of the current database. Syntax char *dbname(dbproc) DBPROCESS *dbproc; Comments If you need to keep track of when the database changes, use dbchange. Parameters dbproc The DBPROCESS structure that is the handle for a particular workstation/SQL Server process. It contains all the information that DB-LIBRARY uses to manage communications and data between the workstation and SQL Server. Returns A pointer to the null-terminated name of the current database. See Also dbchange dbnextrow ──────────────────────────────────────────────────────────────────────────── Function Reads in the next row. Syntax STATUS dbnextrow(dbproc) DBPROCESS *dbproc; Comments The dbnextrow function causes the next data row to be made available through the dbproc. If the DBBUFFER option is turned on and rows have been read out of order by calling dbgetrow, the next data row is read from the linked list of buffered rows. Any specified binding of row data to program variables takes effect. The dbresults function must be called and must have returned SUCCEED before you make any calls to dbnextrow. To determine whether a particular statement is one that returns rows and needs results processing with dbnextrow, call DBROWS after dbresults. Normally, each row is processed in turn by repeatedly calling dbnextrow. If row buffering is enabled and the row buffer has been cleared by the dbclrbuf function, the discarded rows are no longer available (even if dbgetrow tries to position to a discarded row). When row buffering is disabled, the last row is cleared when dbnextrow returns no more rows. The typical sequence of calls is DBINT xvariable; DBCHAR yvariable[10]; /* Read the query into the command buffer. */ dbcmd(dbproc, "select x = 100, y = 'hello'"); /* Send the query to SQL Server. */ dbsqlexec(dbproc); /* Get ready to process the results of the query. */ dbresults(dbproc); /* Bind column data to program variables. */ dbbind(dbproc, 1, INTBIND, (DBINT) 0, (BYTE *)&xvariable); dbbind(dbproc, 2, STRINGBIND, (DBINT) 0, yvariable); /* Now process each row. */ while (dbnextrow(dbproc) != NO_MORE_ROWS) { /* C-code to print or process row data */ } Note that if you are not using row buffering, you must continue calling dbnextrow until it returns NO_MORE_ROWS. This is true even if you are sure that your query only generates one results row. The while loop in the preceding example illustrates the correct way to use dbnextrow. The SQL Server can return two types of rows: ■ Regular rows containing data from columns designated by a SELECT statement's select list. ■ Compute rows resulting from the COMPUTE clause. To facilitate the processing of data rows from SQL Server, dbnextrow returns different values according to the type of row. For details, see "Returns." If you want data returned from SQL Server to be displayed on the default output device, use dbprrow instead of dbnextrow (except under Windows.) Parameters dbproc The DBPROCESS structure that is the handle for a particular workstation/SQL Server process. It contains all the information that DB-LIBRARY uses to manage communications and data between the workstation and SQL Server. Returns One of five different types of values: ■ If a regular row is read, REG_ROW is returned. ■ If a compute row is read, the computeid of the row is returned. (For information on the computeid, see "dbaltbind.") ■ If there are no more rows to be read or the statement didn't return any rows, NO_MORE_ROWS is returned. ■ If buffering is turned on and reading the next row would cause the buffer to be exceeded, BUF_FULL is returned. In this case, no row will have been read. To read any more rows, at least one row must first be cleared from the top of the row buffer. You can clear the row buffer by calling dbclrbuf. ■ If the function was unsuccessful, FAIL is returned. See Also dbaltbind, dbbind, dbclrbuf, dbgetrow, dbprrow, dbresults, Appendix B dbnumalts ──────────────────────────────────────────────────────────────────────────── Function Returns the number of columns in a compute row. Syntax int dbnumalts(dbproc, computeid) DBPROCESS *dbproc; int computeid; Comments The dbnumalts function returns the number of columns in a compute row. This function should be called after dbresults has returned SUCCEED. In this example, dbnumalts(dbproc, 1) returns 3: select dept, year, sales from employee order by dept, year compute avg(sales), min(sales), max(sales) by dept Parameters dbproc The DBPROCESS structure that is the handle for a particular workstation/SQL Server process. It contains all the information that DB-LIBRARY uses to manage communications and data between the workstation and SQL Server. computeid The ID that identifies the compute. A SELECT statement can have multiple COMPUTE clauses, each of which can have a different number of aggregate operators and aggregate targets. The computeid is returned by dbnextrow or dbgetrow. Returns The number of columns for the particular computeid. If computeid is invalid, -1 is returned. See Also dbadata, dbadlen, dbaltlen, dbalttype, dbgetrow, dbnextrow, dbresults dbnumcols ──────────────────────────────────────────────────────────────────────────── Function Determines the number of columns for the current set of results. Syntax int dbnumcols(dbproc) DBPROCESS *dbproc; Comments The following program fragment illustrates the use of dbnumcols: int column_count; DBPROCESS *dbproc; /* put the commands into the command buffer */ dbcmd(dbproc, "select name, id, type from sysobjects"); dbcmd(dbproc, " select name from sysobjects"); /* send the commands to SQL Server and start execution */ dbsqlexec(dbproc); /* process each command until there are no more */ while (dbresults(dbproc) != NO_MORE_RESULTS) { column_count = dbnumcols(dbproc); printf("%d columns in this SQL Server result.\n", column_count); while (dbnextrow(dbproc) != NO_MORE_ROWS) printf("row received.\n"); } Parameters dbproc The DBPROCESS structure that is the handle for a particular workstation/SQL Server process. It contains all the information that DB-LIBRARY uses to manage communications and data between the workstation and SQL Server. Returns The number of columns in the current set of results. If there are no columns, 0 is returned. See Also dbcollen, dbcolname dbnumcompute ──────────────────────────────────────────────────────────────────────────── Function Returns the number of COMPUTE clauses in the current set of results. Syntax int dbnumcompute(dbproc) DBPROCESS *dbproc; Comments This function should be called after dbresults has returned SUCCEED. In this example, dbnumcompute(dbproc) returns 2 since there are two COMPUTE clauses in the SELECT statement: select dept, name from employee order by dept, name compute count(name) by dept compute count(name) Parameters dbproc The DBPROCESS structure that is the handle for a particular workstation/SQL Server process. It contains all the information that DB-LIBRARY uses to manage communications and data between the workstation and SQL Server. Returns The number of COMPUTE clauses in the current set of results. See Also dbnumalts, dbresults DBNUMORDERS ──────────────────────────────────────────────────────────────────────────── Function Returns the number of columns specified in a TRANSACT-SQL SELECT statement's ORDER BY clause. Syntax int DBNUMORDERS(dbproc) DBPROCESS *dbproc; Comments Once a SELECT statement has been executed and dbresults is called, you can call DBNUMORDERS to find out how many columns were specified in the current statement's ORDER BY clause. Parameters dbproc The DBPROCESS structure that is the handle for a particular workstation/SQL Server process. It contains all the information that DB-LIBRARY uses to manage communications and data between the workstation and SQL Server. Returns The number of ORDER BY columns. See Also dbordercol dbresults dbopen ──────────────────────────────────────────────────────────────────────────── Function Allocates and initializes a DBPROCESS structure. Syntax DBPROCESS *dbopen(login, servername) LOGINREC *login; char *servername; Comments The DBPROCESS structure is the basic data structure that DB-LIBRARY uses to communicate with SQL Server. The program needs a DBPROCESS structure to talk to SQL Server. It is the first parameter in almost every DB-LIBRARY call. Besides allocating the DBPROCESS structure, this function sets up communication with the network, logs in to SQL Server, and initializes any default options. The following program fragment uses dbopen: DBPROCESS *dbproc; LOGINREC *loginrec; loginrec = dblogin(); DBSETLUSER(loginrec, "user"); DBSETLPWD(loginrec, "my_password"); DBSETLAPP(loginrec, "my_program"); dbproc = dbopen(loginrec, "my_server"); Parameters login A pointer to a LOGINREC structure. This pointer is passed as a parameter to dbopen. You can get one by calling dblogin. servername The name of the SQL server that you want to connect to. If this is a null string, dbopen uses a local SQL server if one is available. Returns A DBPROCESS pointer if everything is correct. Ordinarily, NULL is returned if a DBPROCESS structure couldn't be created or initialized, or if your login to SQL Server failed. When NULL is returned, the user-supplied error handler is called to indicate the error. ──────────────────────────────────────────────────────────────────────────── NOTE If there's an unexpected communications failure during the SQL Server login process and an error handler has not been installed, the function returns NULL. ──────────────────────────────────────────────────────────────────────────── Errors The dbopen function returns NULL if any of the following are true: ╓┌─────────────────────────────────┌───────────────────────────────────────── Error Code Description ──────────────────────────────────────────────────────────────────────────── SQLEMEM Unable to allocate sufficient memory. Error Code Description ──────────────────────────────────────────────────────────────────────────── SQLEMEM Unable to allocate sufficient memory. SQLEDBPS Maximum number of DBPROCESS structures already allocated. SQLESOCK Unable to establish communication with SQL Server. SQLEUHST Unknown workstation name. SQLECONN Unable to connect: SQL Server is unavailable or does not exist. SQLEPWD Login incorrect. See Also dbclose, dbexit, dblogin dbordercol ──────────────────────────────────────────────────────────────────────────── Function Returns the ID of a column appearing in the most recently executed query's ORDER BY clause. Syntax int dbordercol(dbproc, order) DBPROCESS *dbproc; int order; Comments This function returns the ID of the column that appears in a specified location within the ORDER BY clause of a TRANSACT-SQL SELECT statement. In this example, dbordercol(dbproc, 1) returns 3 since the first column named in the ORDER BY clause refers to the third column in the SELECT clause: select dept, name, salary from employee order by salary, name Parameters dbproc The DBPROCESS structure that is the handle for a particular workstation/SQL Server process. It contains all the information that DB-LIBRARY uses to manage communications and data between the workstation and SQL Server. order The ID that identifies the particular ORDER BY column. The first column named within the ORDER BY clause is number 1. Returns The column ID (based on the column's position in the select list) for the column in the specified place in the ORDER BY clause. If the order is invalid, then -1 is returned. See Also DBNUMORDERS dbprhead ──────────────────────────────────────────────────────────────────────────── Function Prints the column headings for rows returned from SQL Server. Syntax void dbprhead(dbproc) DBPROCESS *dbproc; Comments This function displays the column headings for a set of query results on the default output device in a format similar to that used by isql.exe. The format is compatible with the format used by dbprrow. The output width is set at 80 columns. The dbprhead function can be called after dbresults has succeeded. This function is useful for debugging. This function is not supported in the Windows environment. Parameters dbproc The DBPROCESS structure that is the handle for a particular workstation/SQL Server process. It contains all the information that DB-LIBRARY uses to manage communications and data between the workstation and SQL Server. Returns None. See Also dbbind, dbnextrow, dbprrow, dbresults dbprrow ──────────────────────────────────────────────────────────────────────────── Function Prints all rows returned from SQL Server. Syntax RETCODE dbprrow(dbproc) DBPROCESS *dbproc; Comments This function displays the rows for a set of query results on the default output device in a format similar to that used by isql.exe. This function reads and prints all the rows. It saves the trouble of allocating program variables to store the data and calling dbbind, but the format is predetermined. This function can be called after dbresults has succeeded. When using this function, it is not necessary to call dbnextrow to loop through the rows. The dbprrow function is useful for debugging. The output line width is set at 80 columns. This function is not supported in the Windows environment. Parameters dbproc The DBPROCESS structure that is the handle for a particular workstation/SQL Server process. It contains all the information that DB-LIBRARY uses to manage communications and data between the workstation and SQL Server. Returns SUCCEED or FAIL. See Also dbbind, dbnextrow, dbprhead, dbresults dbprtype ──────────────────────────────────────────────────────────────────────────── Function Converts an SQL Server token value to a readable string. Syntax char *dbprtype(token) int token; Comments Functions like dbcoltype and dbalttype return SQL Server token values. If you want to print out what the token value means, dbprtype will provide the string. The following token values are used by dbprtype: ╓┌─────────────────────────────────┌─────────────────────────────────────────╖ Token Value Datatype ──────────────────────────────────────────────────────────────────────────── SQLINT1 tinyint SQLINT2 smallint SQLINT4 int Token Value Datatype ──────────────────────────────────────────────────────────────────────────── SQLINT4 int SQLMONEY money SLQFLT8 float SQLDATETIME datetime SQLBIT bit SQLCHAR char SQLVARCHAR varchar SQLTEXT text SQLBINARY binary SQLVARBINARY varbinary Token Value Datatype ──────────────────────────────────────────────────────────────────────────── SQLVARBINARY varbinary SQLIMAGE image SQLINTN integer-null SQLDATETIMN datetime-null SQLMONEYN money-null SQLFLTN float-null SQLAOPSUM sum SQLAOPAVG avg SQLAOPCNT count SQLAOPMIN min Token Value Datatype ──────────────────────────────────────────────────────────────────────────── SQLAOPMIN min SQLAOPMAX max ──────────────────────────────────────────────────────────────────────────── Parameters token The SQL Server token value. Returns A pointer to a null-terminated string that is the readable translation of the SQL Server token value. The pointer points to space that is never overwritten, so it is safe to call this function more than once in the same statement. If the token is unknown, the function returns a pointer to an empty string. The strings correspond to SQL Server datatype names. See Also dbaltop, dbalttype, dbcoltype, Appendix C, SQL Server Language Reference dbqual ──────────────────────────────────────────────────────────────────────────── Function Returns a pointer to a WHERE clause suitable for use in updating the current row in a table that can be browsed. Syntax char *dbqual(dbproc, tabnum, tabname) DBPROCESS *dbproc; int tabnum; char *tabname; Comments The dbqual function is one of the DB-LIBRARY browse-mode functions. For a detailed discussion of browse mode, see Chapter 1, "Overview of DB-LIBRARY." The dbqual function provides a WHERE clause that the application can use to update a single row in a browsable table. Columns from this row must have been previously retrieved into the application through a browse-mode SELECT query. The WHERE clause produced by dbqual begins with the keyword WHERE and contains references to the row's unique index and timestamp column. The application appends the WHERE clause to an UPDATE or DELETE statement; it does not need to examine it or manipulate it in any way. The timestamp column indicates the time that the particular row was last updated. An update on a browsable table fails if the timestamp column in the dbqual-generated WHERE clause is different from the timestamp column in the table. Such a condition, which provokes SQL Server error message 532, indicates that another user updated the row between the time this application selected it for browsing and the time it tried to update it. The application itself must provide the logic for handling the update failure. The following program fragment illustrates one approach: /* This code fragment illustrates a technique for handling the case where ** a browse-mode update fails because the row has already been updated ** by another user. In this example, we simply retrieve the entire row ** again, allow the user to examine and modify it, and try the update ** again. ** ** Note that "q_dbproc" is the DBPROCESS used to query the database, and ** "u_dbproc" is the DBPROCESS used to update the database. */ /* First, find out which employee record the user wants to update. */ employee_id = which_employee(); while (1) { /* Retrieve that employee record from the database. We'll ** assume that "empid" is a unique index, so this query will ** return only one row. */ dbfcmd (q_dbproc, "select * from employees where empid = %d for browse", employee_id); dbsqlexec(q_dbproc); dbresults(q_dbproc); dbnextrow(q_dbproc); /* Now, let the user examine or edit the employee's ** data, first placing the data into the program variables. */ extract_employee_data(q_dbproc, employee_struct); examine_and_edit(employee_struct, &edit_flag); if (edit_flag == FALSE) { /* The user didn't edit this record, ** so we're done. */ break; } else { /* The user edited this record, so we'll use the edited ** data to update the corresponding row in the database. */ qualptr = dbqual(q_dbproc, -1, "employees"); dbcmd(u_dbproc, "update employees"); dbfcmd (u_dbproc, " set address = '%s', salary = %d %s", employee_struct->address, employee_struct->salary, qualptr); dbfreequal(qualptr); if ((dbsqlexec(u_dbproc) == FAIL) || (dbresults(u_dbproc) == FAIL)) { /* Our update failed. In a real program, it ** would be necessary to examine the messages ** returned from the SQL Server to determine ** why it failed. In this example, we'll ** assume that the update failed because ** someone else has already updated this ** row, thereby changing the timestamp. ** ** To cope with this situation, we'll just ** repeat the loop, retrieving the changed ** row for our user to examine and edit. ** This will give our user the opportunity ** to decide whether to overwrite the change ** made by the other user. */ continue; } else { /* The update succeeded, so we're done. */ break; } } } The dbqual function can only construct WHERE clauses for tables that can be browsed. You can use dbtabbrowse to determine whether a table can be browsed. The dbqual function is usually called after dbnextrow. Parameters dbproc The DBPROCESS structure that is the handle for a particular workstation/SQL Server process. It contains all the information that DB-LIBRARY uses to manage communications and data between the workstation and SQL Server. tabnum The number of the table, as specified in the SELECT statement's FROM clause. Table numbers start at 1. If tabnum is -1, the tabname parameter is used to identify the table. tabname A pointer to the null-terminated name of a table specified in the SELECT statement's FROM clause. If tabname is NULL, the tabnum parameter is used to identify the table. Returns A pointer to a null-terminated WHERE clause for the current row in the specified table. This buffer is dynamically allocated, and the application must free it by calling dbfreequal. If dbqual is asked to construct a WHERE clause for a table that cannot be browsed, it will return a NULL pointer. For details, see "dbtabbrowse." See Also dbcolbrowse, dbcolsource, dbfreequal, dbnextrow, dbqual, dbtabbrowse, dbtabcount, dbtabname, dbtabsource, dbtsnewlen, dbtsnewval, dbtsput DBRBUF ──────────────────────────────────────────────────────────────────────────── Function Determines whether database command processing has completed. Syntax BOOL DBRBUF(dbproc) DBPROCESS *dbproc; Comments This macro allows an application to continue processing while SQL Server is actually performing the database operation. The DBRBUF macro is ordinarily used after a call to dbsqlsend and before a call to dbsqlok. After dbsqlsend, SQL Server begins executing the commands in the command buffer. When dbsqlok is called, DB-LIBRARY waits for SQL Server processing to be completed before returning control to the application program. The DBRBUF macro provides a method for determining when command processing has completed. It should be called repeatedly until it returns a nonzero value. At that point, dbsqlok can be called. ──────────────────────────────────────────────────────────────────────────── NOTE The DBRBUF macro is provided for compatibility with other SYBASE platforms. It has been replaced by the dbdataready function. DBRBUF now maps directly to a call to dbdataready. ──────────────────────────────────────────────────────────────────────────── Parameters dbproc The DBPROCESS structure that is the handle for a particular workstation/SQL Server process. It contains all the information that DB-LIBRARY uses to manage communications and data between the workstation and SQL Server. Returns TRUE if data is available; FALSE otherwise. See Also dbdataready, dbresults, dbsqlok, dbsqlsend dbreadpage ──────────────────────────────────────────────────────────────────────────── Function Reads in a page of binary data from SQL Server. Syntax DBINT dbreadpage(dbproc, dbname, pageno, buf) DBPROCESS *dbproc; char *dbname; DBINT pageno; BYTE buf[]; Comments This function is primarily useful for examining and repairing damaged database pages. After calling dbreadpage, the DBPROCESS can contain some error or informational messages from SQL Server. These messages can be accessed through a user-supplied message handler. Parameters dbproc The DBPROCESS structure that is the handle for a particular workstation/SQL Server process. It contains all the information that DB-LIBRARY uses to manage communications and data between the workstation and SQL Server. dbname The name of the database. pageno The number of the database page to be read. buf A pointer to a buffer to hold the received page data. SQL Server pages are currently 2048 bytes long. Returns The number of bytes read from SQL Server. If the operation was unsuccessful, dbreadpage returns -1. Limitations Alters the contents of the DBPROCESS command buffer. See Also dbmsghandle, dbwritepage dbresults ──────────────────────────────────────────────────────────────────────────── Function Sets up the results of the next query. Syntax RETCODE dbresults(dbproc) DBPROCESS *dbproc; Comments This function sets up the next statement in the command batch for processing. It is called after dbsqlexec or dbsqlok returns SUCCEED. It is guaranteed that dbresults will return SUCCEED or NO_MORE_RESULTS on the first call if dbsqlexec or dbsqlok has returned SUCCEED, unless a network error or out-of-memory error has occurred. The user typically processes rows, if any, using dbnextrow, once dbresults returns SUCCEED. The dbresults function must be called for each statement in the command batch, whether or not the statement returns any rows. If the application code doesn't know how many statements are in the batch, dbresults can be called until it returns NO_MORE_RESULTS. The dbresults function must also ordinarily be called once for any stored procedure in the command batch. However, if the stored procedure contains more than one SQL SELECT statement, then dbresults must be called once for each SELECT. The easiest way to do this is to continue to call dbresults until it returns NO_MORE_RESULTS. To determine whether a particular statement is one that returns rows and needs results processing with dbnextrow, call DBROWS. The typical sequence of calls for using dbresults with dbsqlexec is DBINT xvariable; DBCHAR yvariable[10]; /* Read the query into the command buffer. */ dbcmd(dbproc, "select x = 100, y = 'hello'"); /* Send the query to SQL Server. */ dbsqlexec(dbproc); /* Get ready to process the results of the query. */ dbresults(dbproc); /* Bind column data to program variables. */ dbbind(dbproc, 1, INTBIND, (DBINT) 0, (BYTE *) &xvariable); dbbind(dbproc, 2, STRINGBIND, (DBINT) 0, yvariable); /* Now process each row. */ while (dbnextrow(dbproc) != NO_MORE_ROWS) { /* C-code to print or process row data */ } Parameters dbproc The DBPROCESS structure that is the handle for a particular workstation/SQL Server process. It contains all the information that DB-LIBRARY uses to manage communications and data between the workstation and SQL Server. Returns SUCCEED, FAIL, or NO_MORE_RESULTS. The most common reason for failing is a NULL or dead dbproc. NO_MORE_RESULTS is returned if there are no more results to be processed. See Also dbbind, dbnextrow, DBROWS, dbsqlexec, dbsqlok DBROWS ──────────────────────────────────────────────────────────────────────────── Function Indicates whether the current statement returned rows. Syntax RETCODE DBROWS(dbproc) DBPROCESS *dbproc; Comments This macro should be called after dbresults returns SUCCEED. Note that DBROWS should be called before dbnextrow; otherwise, DBROWS returns an incorrect value. You can use the DBCMDROW macro to determine whether the current statement is one that can return rows (that is, a TRANSACT-SQL SELECT statement or an EXECUTE on a stored procedure containing a SELECT). Parameters dbproc The DBPROCESS structure that is the handle for a particular workstation/SQL Server process. It contains all the information that DB-LIBRARY uses to manage communications and data between the workstation and SQL Server. Returns SUCCEED or FAIL, indicating whether the current statement will return rows. See Also DBCMDROW, dbnextrow, dbresults, DBROWTYPE DBROWTYPE ──────────────────────────────────────────────────────────────────────────── Function Returns the type of the current row. Syntax STATUS DBROWTYPE(dbproc) DBPROCESS *dbproc; Comments This macro tells you the type (regular or compute) of the current row. Usually you already know this since dbnextrow returns the row type. Parameters dbproc The DBPROCESS structure that is the handle for a particular workstation/SQL Server process. It contains all the information that DB-LIBRARY uses to manage communications and data between the workstation and SQL Server. Returns One of four different types of values: ■ If the current row is a regular row, REG_ROW is returned. ■ If the current row is a compute row, the computeid of the row is returned. (For information on the computeid, see "dbaltbind.") ■ If no rows have been read, NO_MORE_ROWS is returned. ■ If the macro was unsuccessful, FAIL is returned. See Also dbaltbind, dbnextrow dbsetavail ──────────────────────────────────────────────────────────────────────────── Function Marks a DBPROCESS as being available for general use. Syntax void dbsetavail(dbproc) DBPROCESS *dbproc; Comments Any subsequent call to DBISAVAIL returns TRUE until some use is made of the DBPROCESS. Many DB-LIBRARY functions automatically set the DBPROCESS to "not available." This is useful when many different parts of a program are attempting to share a single DBPROCESS. Parameters dbproc The DBPROCESS structure that is the handle for a particular workstation/SQL Server process. It contains all the information that DB-LIBRARY uses to manage communications and data between the workstation and SQL Server. Returns None. See Also DBISAVAIL DBSETLAPP ──────────────────────────────────────────────────────────────────────────── Function Sets the application name in the LOGINREC structure. Syntax RETCODE DBSETLAPP(loginrec, application) LOGINREC *loginrec; char *application; Comments This macro sets the application field in the LOGINREC structure. For it to have any effect, it must be called before dbopen. This macro is optional. If not called, the application name is set to NULL. SQL Server uses the application name in its sysprocesses table to help identify your process. If you set the application name, you will see it if you query the sysprocesses table in the master database. Parameters loginrec A pointer to a LOGINREC structure, which will be passed as a parameter to dbopen. You can get one by calling dblogin. application The application name that will be sent to SQL Server. It must be a null-terminated character string. The maximum length of the string is 30 characters, not including the null-terminating character. Returns SUCCEED or FAIL. See Also dblogin, dbopen, DBSETLHOST, DBSETLPWD, DBSETLUSER DBSETLHOST ──────────────────────────────────────────────────────────────────────────── Function Sets the workstation name in the LOGINREC structure. Syntax RETCODE DBSETLHOST(loginrec, workstation) LOGINREC *loginrec; char *workstation; Comments This macro sets the workstation name in the LOGINREC structure. For it to have any effect, it must be called before dbopen. The workstation name shows up in the sysprocesses table in the master database, or if you issue an sp_who command. It is not necessary to call this macro. If it is not called, DB-LIBRARY sets the workstation name to NULL. Parameters loginrec A pointer to a LOGINREC structure, which is passed as a parameter to dbopen. You can get one by calling dblogin. workstation The workstation name that will be sent to SQL Server. It must be a null-terminated character string. The maximum length of the string is 30 characters, not including the null-terminating character. Returns SUCCEED or FAIL. See Also dblogin, dbopen, DBSETLAPP, DBSETLPWD, DBSETLUSER dbsetlogintime ──────────────────────────────────────────────────────────────────────────── Function Sets the number of seconds that DB-LIBRARY waits for an SQL Server response to a request for a DBPROCESS connection. Syntax RETCODE dbsetlogintime(seconds) int seconds; Comments This function sets the length of time in seconds that DB-LIBRARY will wait for a login response after calling dbopen. The default timeout value is 60 seconds. Parameters seconds The timeout value─the number of seconds that DB-LIBRARY waits for a login response before timing out. A timeout value of 0 represents an infinite timeout period. Returns SUCCEED or FAIL. See Also dberrhandle, dbopen, dbsettime DBSETLPWD ──────────────────────────────────────────────────────────────────────────── Function Sets the user SQL Server password in the LOGINREC structure. Syntax RETCODE DBSETLPWD(loginrec, password) LOGINREC *loginrec; char *password; Comments For this macro to have any effect, it must be called before dbopen. You do not need to call this macro if the password is a null value. Parameters loginrec A pointer to a LOGINREC structure, which is passed as a parameter to dbopen. You can get one by calling dblogin. password The SQL Server password that is sent to SQL Server. It must be a null-terminated character string. The maximum length of the string is 30 characters, not including the null-terminating character. Returns SUCCEED or FAIL. See Also dblogin, dbopen, DBSETLAPP, DBSETLHOST, DBSETLUSER DBSETLUSER ──────────────────────────────────────────────────────────────────────────── Function Sets the username in the LOGINREC structure. Syntax RETCODE DBSETLUSER(loginrec, username) LOGINREC *loginrec; char *username; Comments For this macro to have any effect, it must be called before dbopen. Parameters loginrec A pointer to a LOGINREC structure, which is passed as a parameter to dbopen. You can get one by calling dblogin. username The username that is sent to SQL Server. It must be a null-terminated character string. The maximum length of the string is 30 characters, not including the null-terminating character. SQL Server uses it to determine who is attempting the connection. The SQL Server usernames are defined in the syslogins table in the master database. Returns SUCCEED or FAIL. See Also dblogin, dbopen, DBSETLAPP, DBSETLHOST, DBSETLPWD dbsetmaxprocs ──────────────────────────────────────────────────────────────────────────── Function Sets the maximum number of simultaneously open DBPROCESS structures. Syntax RETCODE dbsetmaxprocs(maxprocs) int maxprocs; Comments A DB-LIBRARY program has a maximum number of simultaneously open DBPROCESS structures. By default, this number is 25. The program can change this limit by calling dbsetmaxprocs. The program can find out what the current limit is by calling dbgetmaxprocs. Parameters maxprocs The new limit on simultaneously open DBPROCESS structures for this particular program. Returns SUCCEED if the function call is successful, or else FAIL (that is, maxprocs See Also dbgetmaxprocs, dbopen dbsetnull ──────────────────────────────────────────────────────────────────────────── Function Defines substitution values to be used when binding null values. Syntax RETCODE dbsetnull(dbproc, bindtype, bindlen, bindval) DBPROCESS *dbproc; int bindtype; int bindlen; BYTE *bindval; Comments The dbbind and dbaltbind functions are used to bind returned SQL Server column values to your program variables. Since a null value can be returned, there is a mechanism for defining what values should be substituted for the null value when doing automatic copying of column data to program variables. Associated with each DBPROCESS is a list of substitute values for each of the binding types. The default substitution values are as follows: ╓┌─────────────────────────────────┌───────────────────────────────────────── Binding Type Default Substitution Value ──────────────────────────────────────────────────────────────────────────── TINYBIND 0 SMALLBIND 0 Binding Type Default Substitution Value ──────────────────────────────────────────────────────────────────────────── INTBIND 0 BITBIND 0 CHARBIND Empty string (padded with blanks) STRINGBIND Empty string (padded with blanks, null-terminated) NTBSTRINGBIND Empty string (null-terminated) VARYCHARBIND Empty string BINARYBIND Empty array (padded with zeros) VARYBINBIND Empty array DATETIMEBIND 8 bytes of zeros Binding Type Default Substitution Value ──────────────────────────────────────────────────────────────────────────── DATETIMEBIND 8 bytes of zeros MONEYBIND $0.00 FLT8BIND 0.0 ──────────────────────────────────────────────────────────────────────────── You can provide your own null substitution values by calling dbsetnull. Any time you call dbsetnull to change a particular null substitution value, the new value remains in force for that DBPROCESS until you change it with another call to dbsetnull. The dbconvert function also uses the current null substitution values when it needs to set a destination variable to null. Parameters dbproc The DBPROCESS structure that is the handle for a particular workstation/SQL Server process. It contains all the information that DB-LIBRARY uses to manage communications and data between the workstation and SQL Server. bindtype The type of variable binding to which the substitute value will apply. (See "dbbind" for more information about the different bindtypes.) bindlen The length, in bytes, of the substitute value you are supplying. It is ignored in all cases except CHARBIND and BINARYBIND. All the other types are either fixed-length or have a special terminator or embedded byte count that provides the length of the data. bindval A generic BYTE pointer to the value you want to use as a null value substitution. The dbsetnull function always makes a copy of this data, so you can free this pointer any time after this call. Returns SUCCEED or FAIL. The dbsetnull function fails if you give it a null bindval or if the length is smaller than 1 when CHARBIND and BINARYBIND types are used. See Also dbaltbind, dbbind, dbconvert, Appendix C dbsetopt ──────────────────────────────────────────────────────────────────────────── Function Sets an SQL Server or DB-LIBRARY option. Syntax RETCODE dbsetopt(dbproc, option, param) DBPROCESS *dbproc; int option; char *param; Comments Although SQL Server options can be set and cleared directly through SQL, the application should use dbsetopt and dbclropt to set and clear options. This provides a uniform interface for setting both SQL Server and DB-LIBRARY options. It also allows the application to use the dbisopt function to check the status of an option. This function does not immediately set the options specified. They are not set until the command buffer is sent to SQL Server (by invoking the dbsqlexec function). An additional result for each option set is returned through the dbresults function. (See "dbresults" for information on results returned.) Note that the command string generated by this function is not immediately sent to SQL Server. Instead, it is buffered within DB-LIBRARY and sent the next time dbsqlexec is invoked; therefore, any options requested by this function do not go into effect until then. Also, the results of the command generated by this function are not returned until the command is transferred to SQL Server. Therefore, the application should be expecting the results returned from the command string generated by this function. If an invalid parameter is specified, this is not known until the command is sent to SQL Server and the results for that command are returned through dbresult. DB-LIBRARY does not detect these types of errors. For a list of each option and its default status, see Appendix B, "DB-LIBRARY Options." Parameters dbproc The DBPROCESS structure that is the handle for a particular workstation/SQL Server process. It contains all the information that DB-LIBRARY uses to manage communications and data between the workstation and SQL Server. If dbproc is NULL, the option will be set for all active DBPROCESS structures. option The option that is to be set. param Certain options take parameters. For example, the DBOFFSET option takes as its parameter the construct for which offsets are to be returned: dbsetopt(dbproc, DBOFFSET, "compute") The DBBUFFER option takes as its parameter the number of rows to be buffered. A parameter of -1 selects the default (currently 100 rows). The syntax is: dbsetopt(dbproc, DBBUFFER, "500") ──────────────────────────────────────────────────────────────────────────── NOTE The param variable must always be a character string enclosed in quotes, even in the case of a numeric value, as in the DBBUFFER example. ──────────────────────────────────────────────────────────────────────────── If the option takes no parameters, param must be NULL. Returns SUCCEED or FAIL. See Also dbclropt, dbisopt, dbresults, dbsqlexec, Appendix B dbsettime ──────────────────────────────────────────────────────────────────────────── Function Sets the number of seconds that DB-LIBRARY will wait for an SQL Server response to a TRANSACT-SQL statement. Syntax RETCODE dbsettime(seconds) int seconds; Comments This function sets the length of time, in seconds, that DB-LIBRARY will wait for an SQL Server response during calls to dbsqlexec, dbsqlok, dbresults, and dbnextrow. The default timeout value is 0, which represents an infinite timeout period. The dbsettime function can be called at any time during the application─before or after a call to dbopen. It takes effect immediately upon being called. To set a timeout value for calls to dbopen, use dbsetlogintime. Note that if an application sends a command to SQL Server using dbsqlexec, control is not returned to the calling application until SQL Server completes the processing of the command. If the application program needs to continue execution while SQL Server is processing the command, it should send the command with dbsqlsend, continue its processing, and then, when it is ready to retrieve the results, call dbsqlok. The program can call DBGETTIME to learn the current timeout value. Parameters seconds The timeout value─the number of seconds that DB-LIBRARY waits for an SQL Server response before timing out. A timeout value of 0 represents an infinite timeout period. Returns SUCCEED or FAIL. See Also dberrhandle, DBGETTIME, dbnextrow, dbresults, dbsetlogintime, dbsqlexec, dbsqlok, dbsqlsend dbsqlexec ──────────────────────────────────────────────────────────────────────────── Function Sends a command batch to SQL Server. Syntax RETCODE dbsqlexec(dbproc) DBPROCESS *dbproc; Comments This function sends SQL statements, stored in the command buffer of the DBPROCESS, to SQL Server. Statements can be added to the DBPROCESS structure by calling dbcmd or dbfcmd. Once dbsqlexec has returned SUCCEED, dbresults must be called to process the results. The typical sequence of calls is DBINT xvariable; DBCHAR yvariable[10]; /* Place the query into the command buffer */ dbcmd(dbproc, "select x = 100, y = 'hello'"); /* send the command buffer to SQL Server */ dbsqlexec(dbproc); /* get ready to process the results of the query */ dbresults(dbproc); /* bind column data to program variables */ dbbind(dbproc, 1, INTBIND, (DBINT) 0, (BYTE *) &xvariable); dbbind(dbproc, 2, STRINGBIND, (DBINT) 0, yvariable); /* now process each row */ while (dbnextrow(dbproc) != NO_MORE_ROWS) { /* C-code to print or process row data */ } The dbsqlexec function is equivalent to dbsqlsend followed by dbsqlok. Parameters dbproc The DBPROCESS structure that is the handle for a particular workstation/SQL Server process. It contains all the information that DB-LIBRARY uses to manage communications and data between the workstation and SQL Server. Returns SUCCEED or FAIL. The most common reason for failing is a TRANSACT-SQL syntax error or SQL Server permission violation. Other reasons include incorrect column or table names. The dbsqlexec function also fails if previous results were not processed, or if no statement was specified. Note that if a series of commands is sent to SQL Server and if one or more of the commands contains syntax errors, SQL Server processes none of the commands, and dbsqlexec returns FAIL. See Also dbcmd, dbfcmd, dbnextrow, dbresults, dbsettime, dbsqlok, dbsqlsend dbsqlok ──────────────────────────────────────────────────────────────────────────── Function Verifies the correctness of a command batch. Syntax RETCODE dbsqlok(dbproc) DBPROCESS *dbproc; Comments This function must be called after dbsqlsend returns SUCCEED. If dbsqlok returns SUCCEED, then dbresults can be called to process the results. The dbsqlok function is also useful in text update operations. When chunks of text are sent to SQL Server using dbwritetext and dbmoretext, dbsqlok must be called before the first call to dbmoretext and after the last call to dbmoretext. For an example of its use in this context, see "dbwritetext." Parameters dbproc The DBPROCESS structure that is the handle for a particular workstation/SQL Server process. It contains all the information that DB-LIBRARY uses to manage communications and data between the workstation and SQL Server. Returns SUCCEED or FAIL. The most common reason for failing is an SQL syntax error. See Also dbcmd, dbfcmd, dbmoretext, dbnextrow, dbresults, dbsettime, dbsqlexec, dbsqlsend, dbwritetext dbsqlsend ──────────────────────────────────────────────────────────────────────────── Function Sends a command batch to SQL Server and does not wait for a response. Syntax RETCODE dbsqlsend(dbproc) DBPROCESS *dbproc; Comments This function sends SQL statements, stored in the command buffer of the DBPROCESS, to SQL Server. Statements can be added to the command buffer by calling dbcmd or dbfcmd. Once dbsqlsend returns SUCCEED, dbsqlok must be called to verify the accuracy of the command batch. Then dbresults can be called to process the results. Parameters dbproc The DBPROCESS structure that is the handle for a particular workstation/SQL Server process. It contains all the information that DB-LIBRARY uses to manage communications and data between the workstation and SQL Server. Returns SUCCEED or FAIL. See Also dbcmd, dbfcmd, dbnextrow, dbresults, dbsettime, dbsqlexec, dbsqlok dbstrcpy ──────────────────────────────────────────────────────────────────────────── Function Copies a portion of the command buffer in the DBPROCESS structure to a specified memory location. Syntax RETCODE dbstrcpy(dbproc, start, numbytes, dest) DBPROCESS *dbproc; int start; int numbytes; char *dest; Comments The copy is null-terminated. The dbstrcpy function assumes that the destination string is large enough to receive the source string. If not, a segmentation fault is likely. Internally, the command buffer is a linked list of nonterminated text strings. Parts of the command buffer can be located and copied using the dbgetchar, dbstrcpy, and dbstrlen functions. Parameters dbproc The DBPROCESS structure that is the handle for a particular workstation/SQL Server process. It contains all the information that DB-LIBRARY uses to manage communications and data between the workstation and SQL Server. start The character position in the command buffer to start copying from. The first character position is the 0th character. If start is greater than the length of the command buffer, a null terminator is inserted at dest[0]. numbytes The number of characters to copy. If numbytes is less than 0, dbstrcpy copies the entire command buffer. It is legal to copy 0 bytes. In this case, a null terminator is inserted at dest[0]. If numbytes are not available to copy, dbstrcpy copies the number of bytes available and returns SUCCEED. dest A pointer to the memory location to copy the source string into. Returns SUCCEED or FAIL. FAIL is returned if start is negative. See Also dbcmd, dbfcmd, dbfreebuf, dbgetchar, dbstrlen dbstrlen ──────────────────────────────────────────────────────────────────────────── Function Returns the length, in characters, of the command buffer. Syntax int dbstrlen(dbproc) DBPROCESS *dbproc; Comments Internally, the command buffer is a linked list of nonterminated text strings. Parts of the command buffer can be located and copied using the dbgetchar, dbstrcpy, and dbstrlen functions. Parameters dbproc The DBPROCESS structure that is the handle for a particular workstation/SQL Server process. It contains all the information that DB-LIBRARY uses to manage communications and data between the workstation and SQL Server. Returns The length, in characters, of the command buffer. See Also dbcmd, dbfcmd, dbfreebuf, dbgetchar, dbstrcpy dbtabbrowse ──────────────────────────────────────────────────────────────────────────── Function Determines whether the specified table can be updated with the DB-LIBRARY browse-mode facilities. Syntax DBBOOL dbtabbrowse(dbproc, tabnum) DBPROCESS *dbproc; int tabnum; Comments The dbtabbrowse function is one of the DB-LIBRARY browse-mode functions. See Chapter 1, "Overview of DB-LIBRARY," for a detailed discussion of browse mode. The dbtabbrowse function provides a way to identify tables that can be browsed. It is useful when examining ad hoc queries prior to performing browse-mode updates based on them. If the query has been hardcoded into the program, this function is unnecessary. For a table to be browsed it must have a unique index and a timestamp column. The dbtabbrowse function can be called any time after dbresults. Parameters dbproc The DBPROCESS structure that is the handle for a particular workstation/SQL Server process. It contains all the information that DB-LIBRARY uses to manage communications and data between the workstation and SQL Server. tabnum The number of the table, as specified in the SELECT statement's FROM clause. Table numbers start at 1. Returns TRUE or FALSE. If you drop the unique index of a table while browsing, dbtabbrowse continues to return TRUE. See Also dbcolbrowse, dbcolsource, dbqual, dbresults, dbtabcount, dbtabname, dbtabsource, dbtsnewlen, dbtsnewval, dbtsput dbtabcount ──────────────────────────────────────────────────────────────────────────── Function Returns the number of tables involved in the current SELECT statement. Syntax int dbtabcount(dbproc) DBPROCESS *dbproc; Comments The dbtabcount function is one of the DB-LIBRARY browse-mode functions. See Chapter 1, "Overview of DB-LIBRARY," for a detailed discussion of browse mode. A SELECT statement can generate a set of result rows whose columns are derived from several database tables. To perform browse-mode updates of columns in a statement's select list, the application must know how many tables were involved in the query, because each table requires a separate UPDATE statement. The dbtabcount function can provide this information for ad hoc queries. If the query has been hardcoded into the program, this function is unnecessary. The count returned by this function includes any SQL Server "work tables" used in processing the query. The SQL Server sometimes creates temporary, internal work tables to process a query. It deletes these work tables by the time it finishes processing the statement. Work tables cannot be updated and are not available to the application. Therefore, before using a table number, the application must make sure that it does not belong to a work table. The dbtabname function can be used to determine whether a particular table number refers to a work table. The dbtabcount function can be called any time after dbresults. Parameters dbproc The DBPROCESS structure that is the handle for a particular workstation/SQL Server process. It contains all the information that DB-LIBRARY uses to manage communications and data between the workstation and SQL Server. Returns The number of tables, including SQL Server work tables, involved in the current set of row results. A value of -1 is returned if an invalid dbproc value is sent to dbtabcount. See Also dbcolbrowse, dbcolsource, dbqual, dbresults, dbtabbrowse, dbtabname, dbtabsource, dbtsnewlen, dbtsnewval, dbtsput dbtabname ──────────────────────────────────────────────────────────────────────────── Function Returns the name of a table based on its number. Syntax char *dbtabname(dbproc, tabnum) DBPROCESS *dbproc; int tabnum; Comments The dbtabname function is one of the DB-LIBRARY browse-mode functions. See Chapter 1, "Overview of DB-LIBRARY," for a detailed discussion of browse mode. A SELECT statement can generate a set of result rows whose columns are derived from several database tables. The database tables are specified by the FROM clause. The dbtabname function provides a way for an application to determine the name of each table involved in an ad hoc query. If the query has been hardcoded into the program, this function is unnecessary. The dbtabname function can be called any time after dbresults. Parameters dbproc The DBPROCESS structure that is the handle for a particular workstation/SQL Server process. It contains all the information that DB-LIBRARY uses to manage communications and data between the workstation and SQL Server. tabnum The number of the table. Table numbers start with 1. Use dbtabcount to find out the total number of tables involved in a particular statement. Returns A pointer to the null-terminated name of the specified table. This pointer is NULL if the table number is out of range or if the specified table is an SQL Server work table. See "dbtabcount" for a description of work tables. See Also dbcolbrowse, dbcolsource, dbqual, dbresults, dbtabbrowse, dbtabcount, dbtabsource, dbtsnewlen, dbtsnewval, dbtsput dbtabsource ──────────────────────────────────────────────────────────────────────────── Function Returns the name and number of the table from which a particular result column was derived. Syntax char *dbtabsource(dbproc, colnum, tabnum) DBPROCESS *dbproc; int colnum; int *tabnum; Comments The dbtabsource function is one of the DB-LIBRARY browse-mode functions. See Chapter 1, "Overview of DB-LIBRARY," for a detailed discussion of browse mode. The dbtabsource function allows an application to determine which tables provided the columns in the current set of result rows. This information is valuable when using dbqual to construct WHERE clauses for UPDATE and DELETE statements based on ad hoc queries. If the query has been hardcoded into the program, this function is unnecessary. The dbtabsource function can be called any time after dbresults. Parameters dbproc The DBPROCESS structure that is the handle for a particular workstation/SQL Server process. It contains all the information that DB-LIBRARY uses to manage communications and data between the workstation and SQL Server. colnum The number of the result column. Column numbers start at 1. tabnum A pointer to an integer, which is filled in with the table's number. Many DB-LIBRARY functions that deal with browse mode accept either a table name or a table number. If dbtabsource returns NULL (see "Returns"), tabnum will be set to -1. Returns A pointer to the name of the table from which this result column was derived. A NULL return value can mean one of the following: ■ The DBPROCESS is dead or not enabled. This is an error that will cause an application's error handler to be invoked. ■ The column number is out of range. ■ The column is the result of an expression, such as "max(colname)". See Also dbcolbrowse, dbcolsource, dbqual, dbresults, dbtabbrowse, dbtabcount, dbtabname, dbtsnewlen, dbtsnewval, dbtsput dbtsnewlen ──────────────────────────────────────────────────────────────────────────── Function Returns the length of the new value of the timestamp column after a browse-mode update. Syntax int dbtsnewlen(dbproc) DBPROCESS *dbproc; Comments The dbtsnewlen function is one of the DB-LIBRARY browse-mode functions. See Chapter 1, "Overview of DB-LIBRARY," for a detailed discussion of browse mode. The dbtsnewlen function provides information about the timestamp column. The WHERE clause returned by dbqual contains a call to the TSEQUAL built-in function. When such a WHERE clause is used in an UPDATE statement, the TSEQUAL function places a new value in the updated row's timestamp column and returns the new timestamp value to the application (if the update is successful). The dbtsnewlen function allows the application to save the length of the new timestamp value, possibly for use with dbtsput. Parameters dbproc The DBPROCESS structure that is the handle for a particular workstation/SQL Server process. It contains all the information that DB-LIBRARY uses to manage communications and data between the workstation and SQL Server. Returns The length, in bytes, of the updated row's new timestamp value. If no timestamp was returned to the application (because the update was unsuccessful or because the UPDATE statement did not contain the TSEQUAL built-in function), the function returns -1. See Also dbcolbrowse, dbcolsource, dbqual, dbtabbrowse, dbtabcount, dbtabname, dbtabsource, dbtsnewval, dbtsput dbtsnewval ──────────────────────────────────────────────────────────────────────────── Function Returns the new value of the timestamp column after a browse-mode update. Syntax DBBINARY *dbtsnewval(dbproc) DBPROCESS *dbproc; Comments The dbtsnewval function is one of the DB-LIBRARY browse-mode functions. See Chapter 1, "Overview of DB-LIBRARY," for a detailed discussion of browse mode. The dbtsnewval function provides information about the timestamp column. The WHERE clause returned by dbqual contains a call to the TSEQUAL built-in function. When such a WHERE clause is used in an UPDATE statement, the TSEQUAL function places a new value in the updated row's timestamp column and returns the new timestamp value to the application (if the update is successful). This function allows the application to save the new timestamp value, possibly for use with dbtsput. Parameters dbproc The DBPROCESS structure that is the handle for a particular workstation/SQL Server process. It contains all the information that DB-LIBRARY uses to manage communications and data between the workstation and SQL Server. Returns A pointer to the updated row's new timestamp value. If no timestamp was returned to the application (possibly because the update was unsuccessful or because the UPDATE statement did not contain the TSEQUAL built-in function), the pointer is NULL. See Also dbcolbrowse, dbcolsource, dbqual, dbtabbrowse, dbtabcount, dbtabname, dbtabsource, dbtsnewlen, dbtsput dbtsput ──────────────────────────────────────────────────────────────────────────── Function Puts the new value of the timestamp column into the given table's current row in the DBPROCESS. Syntax RETCODE dbtsput(dbproc, newts, newtslen, tabnum, tabname) DBPROCESS *dbproc; DBBINARY *newts; int newtslen; int tabnum; char *tabname; Comments The dbtsput function is one of the DB-LIBRARY browse-mode functions. See Chapter 1, "Overview of DB-LIBRARY," for a detailed discussion of browse mode. The dbtsput function manipulates the timestamp column. The WHERE clause returned by dbqual contains a call to the TSEQUAL built-in function. When such a WHERE clause is used in an UPDATE statement, the TSEQUAL function places a new value in the updated row's timestamp column and returns the new timestamp value to the application (if the update is successful). If the same row is updated a second time, the UPDATE statement's WHERE clause must use the latest timestamp value. This function updates the timestamp in the DBPROCESS for the row currently being browsed. Then, if the application needs to update the row a second time, it can call dbqual to formulate a new WHERE clause that uses the new timestamp. Parameters dbproc The DBPROCESS structure that is the handle for a particular workstation/SQL Server process. It contains all the information that DB-LIBRARY uses to manage communications and data between the workstation and SQL Server. This must be the DBPROCESS used to perform the original SELECT query. newts A pointer to the new timestamp value. It is returned by dbtsnewval. newtslen The length of the new timestamp value. It is returned by dbtsnewlen. tabnum The number of the updated table. Table numbers start at 1. The tabnum must refer to a table that can be browsed. Use dbtabbrowse to determine whether it can be browsed. If this value is -1, the tabname parameter will be used to identify the table. tabname A pointer to a null-terminated table name. The tabname must refer to a table that can be browsed. Set this pointer to NULL if the tabnum parameter is used to identify the table. Returns SUCCEED or FAIL. The following situations cause this function to return FAIL: ■ The application tries to update the timestamp of a nonexistent row. ■ The application tries to update the timestamp, using NULL as the new timestamp value (newts). ■ The specified table cannot be browsed. See Also dbcolbrowse, dbcolsource, dbqual, dbtabbrowse, dbtabcount, dbtabname, dbtabsource, dbtsnewlen, dbtsnewval dbtxptr ──────────────────────────────────────────────────────────────────────────── Function Returns the value of the text pointer for a column in the current row. Syntax DBBINARY *dbtxptr(dbproc, column) DBPROCESS *dbproc; int column; Comments Every database column of type SQLTEXT or SQLIMAGE has an associated text pointer, which uniquely identifies the text or image value. This text pointer is useful in conjunction with the dbwritetext function. Text pointers are of fixed length and can be NULL in the case of a null text or image value. Parameters dbproc The DBPROCESS structure that is the handle for a particular workstation/SQL Server process. It contains all the information that DB-LIBRARY uses to manage communications and data between the workstation and SQL Server. column The number of the column. The first column in a table is number 1. Returns A DBBINARY pointer to the text pointer for the column. This pointer can be NULL. See Also dbtxtimestamp, dbwritetext dbtxtimestamp ──────────────────────────────────────────────────────────────────────────── Function Returns the value of the text timestamp for a column in the current row. Syntax DBBINARY *dbtxtimestamp(dbproc, column) DBPROCESS *dbproc; int column; Comments Every database column of type SQLTEXT or SQLIMAGE has an associated text timestamp, which marks the time of the column's last modification. The text timestamp is useful in conjunction with the dbwritetext function to ensure that two competing users do not inadvertently overwrite each other's modifications to the same value in the database. It is returned to the DBPROCESS when a TRANSACT-SQL SELECT is performed on an SQLTEXT or SQLIMAGE column. The length of a non-NULL text timestamp is always DBTXTSLEN (currently defined as eight bytes). Parameters dbproc The DBPROCESS structure that is the handle for a particular workstation/SQL Server process. It contains all the information that DB-LIBRARY uses to manage communications and data between the workstation and SQL Server. column The number of the column. The first column in a table is number 1. Returns A DBBINARY pointer to the text timestamp for the column. This pointer can be NULL. See Also dbtxptr, dbwritetext dbtxtsnewval ──────────────────────────────────────────────────────────────────────────── Function Returns the new value of a text timestamp after a call to dbwritetext. Syntax DBBINARY *dbtxtsnewval(dbproc) DBPROCESS *dbproc; Comments Every database column of type SQLTEXT or SQLIMAGE has an associated text timestamp, which is updated whenever the column's value is changed. The text timestamp is useful in conjunction with the dbwritetext function to ensure that two competing users do not inadvertently overwrite each other's modifications to the same value in the database. It is returned to the DBPROCESS when a TRANSACT-SQL SELECT is performed on an SQLTEXT or SQLIMAGE column and can be examined by calling dbtxtimestamp. After each successful dbwritetext operation (which can include a number of calls to dbmoretext), SQL Server sends the updated text timestamp value back to DB-LIBRARY. The dbtxtsnewval function provides a way for the application to get this new timestamp value. The application can then use dbtxtsput to put the new timestamp value in the DBPROCESS for future access through dbtxtimestamp. The application can use dbtxtsnewval in two ways. First, the return from dbtxtsnewval can be used as the timestamp parameter of a dbwritetext call. Second, dbtxtsnewval and dbtxtsput can be used together to put the new timestamp value into the DBPROCESS row buffer for future access using dbtxtimestamp. This is particularly useful when the application is buffering result rows and does not need the new timestamp immediately. Parameters dbproc The DBPROCESS structure that is the handle for a particular workstation/SQL Server process. It contains all the information that DB-LIBRARY uses to manage communications and data between the workstation and SQL Server. Returns A pointer to the new text timestamp value for the SQLTEXT or SQLIMAGE value modified by a dbwritetext operation. This pointer can be NULL. See Also dbmoretext, dbtxtimestamp, dbtxtsput, dbwritetext dbtxtsput ──────────────────────────────────────────────────────────────────────────── Function Puts the new value of a text timestamp into the specified column of the current row in the DBPROCESS. Syntax RETCODE dbtxtsput(dbproc, newtxts, colnum) DBPROCESS *dbproc; DBBINARY *newtxts; int colnum; Comments Every database column of type SQLTEXT or SQLIMAGE has an associated text timestamp, which is updated whenever the column's value is changed. The text timestamp is useful in conjunction with the dbwritetext function to ensure that two competing users do not inadvertently overwrite each other's modifications to the same value in the database. It is returned to the DBPROCESS when a TRANSACT-SQL SELECT is performed on an SQLTEXT or SQLIMAGE column and can be examined by calling dbtxtimestamp. After each successful dbwritetext operation (which can include a number of calls to dbmoretext), SQL Server will send the updated text timestamp value back to DB-LIBRARY. The dbtxtsnewval function allows the application to get this new timestamp value. The application can then use dbtxtsput to put the new timestamp value in the DBPROCESS row buffer for future access through dbtxtimestamp. This is particularly useful when the application is buffering result rows and does not need the new timestamp immediately. Parameters dbproc The DBPROCESS structure that is the handle for a particular workstation/SQL Server process. It contains all the information that DB-LIBRARY uses to manage communications and data between the workstation and SQL Server. newtxts A pointer to the new text timestamp value. It is returned by dbtxtsnewval. colnum The number of the column associated with this text timestamp. Column numbers start at 1. Returns SUCCEED or FAIL. See Also dbmoretext, dbtxtimestamp, dbtxtsnewval, dbwritetext DBUNLOCKLIB (Windows only) ──────────────────────────────────────────────────────────────────────────── Function Unlocks DB-LIBRARY segments. Syntax DBUNLOCKLIB() Comments This macro applies only to Windows application programs. It is not available in the MS-DOS or MS OS/2 DB-LIBRARY. This macro is the counterpart of the DBLOCKLIB macro. This macro is necessary because segments in Windows libraries move around. If an application (or DB-LIBRARY function) is relying on a far pointer for later use, the segments must remain stable. For example, if a far pointer value is retrieved from the dbname function, DB-LIBRARY must remain locked until the pointer value is no longer used by the application. The DBLOCKLIB macro locks the DB-LIBRARY segments. It should be called prior to calling any DB-LIBRARY functions. The DBUNLOCKLIB macro should be called after returning from a DB-LIBRARY function and only after the application uses any pointer information returned from a function call. The DBLOCKLIB macro can be called once, then many DB-LIBRARY functions can be called, and then the DB-LIBRARY segments can be unlocked with the DBUNLOCKLIB macro. Note that DB-LIBRARY can be unlocked after a call to dbopen because the value returned from dbopen is a near pointer used only within DB-LIBRARY. Here's a program fragment that calls DBLOCKLIB: DBPROCESS *dbproc; LOGINREC *login; char far *name; DBLOCKLIB(); login = dblogin(); DBSETLUSER(login, "user"); DBSETLPWD(login, "my_password"); DBSETLAPP(login, "my program"); dbproc = dbopen(login, "my_server"); DBUNLOCKLIB(); . . . DBLOCKLIB(); name = dbname(dbproc); /* use name */ . . . /* name no longer required */ . . . DBUNLOCKLIB(); . . . Parameters None. Returns None. See Also DBLOCKLIB, dbname, dbopen dbuse ──────────────────────────────────────────────────────────────────────────── Function Uses a particular database. Syntax RETCODE dbuse(dbproc, dbname) DBPROCESS *dbproc; char *dbname; Comments This function executes a TRANSACT-SQL USE statement for the specified database for a particular DBPROCESS. It sets up the statement and calls dbsqlexec and dbresults. If the USE statement fails because the requested database has not yet completed a recovery process, dbuse continues to send USE statements at one second intervals until it either succeeds or encounters some other error. The function uses the dbproc provided by the caller and its command buffer. Any existing statements in the buffer are lost, and the command buffer is cleared by dbuse when it is finished. Parameters dbproc The DBPROCESS structure that is the handle for a particular workstation/SQL Server process. It contains all the information that DB-LIBRARY uses to manage communications and data between the workstation and SQL Server. dbname A character pointer to a null-terminated string to be used as the database name. Returns SUCCEED or FAIL. See Also dbresults, dbsqlexec dbwillconvert ──────────────────────────────────────────────────────────────────────────── Function Determines whether a specific datatype conversion is available within DB-LIBRARY. Syntax DBBOOL dbwillconvert(srctype, desttype) int srctype; int desttype; Comments When dbconvert is asked to perform a conversion that it doesn't support, it calls a user-supplied error handler (if any) and returns -1. The dbconvert function can convert data stored in any of the SQL Server datatypes (not all conversions are allowable): ╓┌─────────────────────────────────┌─────────────────────────────────────────╖ SQL Server Datatype Program Variable Type ──────────────────────────────────────────────────────────────────────────── SQLCHAR DBCHAR SQLTEXT DBCHAR SQLBINARY DBBINARY SQLIMAGE DBBINARY SQL Server Datatype Program Variable Type ──────────────────────────────────────────────────────────────────────────── SQLINT1 DBTINYINT SQLINT2 DBSMALLINT SQLINT4 DBINT SQLFLT8 DBFLT8 SQLBIT DBBIT SQLMONEY DBMONEY SQLDATETIME DBDATETIME ──────────────────────────────────────────────────────────────────────────── The following table lists the datatype conversions that dbconvert supports. The source datatypes are listed down the leftmost column and the destination datatypes are listed along the top row of the table. (For brevity, the prefix "SQL" has been eliminated from each datatype.) ╓┌─────────┌───────┌───────┌───────┌───────┌───────┌───────┌───────┌───────┌─ ──────────────────────────────────────────────────────────────────────────── TO: char text binary image int1 int2 int4 flt8 bit FROM: char T T T T T T T T T text T T T T T T T T T binary T T T T T T T T T image T T T T T T T T T int1 T T T T T T T T T ──────────────────────────────────────────────────────────────────────────── int2 T T T T T T T T T int4 T T T T T T T T T flt8 T T T T T T T T T bit T T T T T T T T T money T T T T T T T T T datetime T T T T F F F F F ──────────────────────────────────────────────────────────────────────────── See "dbconvert" for more information on datatype conversions. Parameters srctype The datatype of the data which is to be converted. This parameter can be any of the SQL Server datatypes. desttype The datatype that the source data is to be converted into. This parameter can be any of the SQL Server datatypes. Returns TRUE if the datatype conversion is supported; FALSE if the conversion is not supported. See Also dbaltbind, dbbind, dbconvert, Appendix C dbwinexit (Windows only) ──────────────────────────────────────────────────────────────────────────── Function Under Windows, informs DB-LIBRARY that the application is about to exit. Syntax void dbwinexit() Comments This function informs DB-LIBRARY that the Windows application calling it is about to exit. When running under Windows, DB-LIBRARY maintains information about each application that has referenced it. DB-LIBRARY creates the information when a library application calls dbinit; it does this in order to prevent conflicts between applications that utilize DB-LIBRARY concurrently. In order for DB-LIBRARY to release this information, the application must call dbwinexit just before it exits. You should put the call to dbwinexit within the message handling code for the WM_DESTROY message. For example: case WM_DESTROY: dbwinexit(); break; This call releases the memory DB-LIBRARY allocated to keep track of this application and makes that memory available to other applications. It is not necessary to call DBLOCKLIB before calling dbwinexit. ──────────────────────────────────────────────────────────────────────────── NOTE Once your application has called dbwinexit, it cannot call any other DB-LIBRARY function except DBUNLOCKLIB. If you have called dbwinexit and then need to issue one or more DB-LIBRARY calls, you must again call dbinit to re-register your application. ──────────────────────────────────────────────────────────────────────────── Parameters None. Returns None. See Also dbinit, DBUNLOCKLIB dbwritepage ──────────────────────────────────────────────────────────────────────────── Function Writes a page of binary data to SQL Server. Syntax RETCODE dbwritepage(dbproc, dbname, pageno, size, buf) DBPROCESS *dbproc; char *dbname; DBINT pageno; DBINT size; BYTE buf[]; Comments The dbwritepage function writes a page of binary data to SQL Server. This function is primarily useful for examining and repairing damaged database pages. After calling dbwritepage, the DBPROCESS may contain some error or informational messages from SQL Server. These messages can be accessed through a user-supplied message handler. Parameters dbproc The DBPROCESS structure that is the handle for a particular workstation/SQL Server process. It contains all the information that DB-LIBRARY uses to manage communications and data between the workstation and SQL Server. dbname The name of the database. pageno The number of the database page to be written. size The number of bytes to be written to SQL Server. Currently, SQL Server database pages are 2048 bytes long. buf A pointer to a buffer that holds the data to be written. Returns SUCCEED or FAIL. See Also dbmsghandle, dbreadpage dbwritetext ──────────────────────────────────────────────────────────────────────────── Function Sends a text or image value to SQL Server. Syntax RETCODE dbwritetext(dbproc, objname, textptr, textptrlen, timestamp, log, size, text) DBPROCESS *dbproc; char *objname; DBBINARY *textptr; DBTINYINT textptrlen; DBBINARY *timestamp; DBBOOL log; DBINT size; BYTE *text; Comments This function is used to update text and image values, allowing the application to send long values to SQL Server without having to copy them into a TRANSACT-SQL UPDATE statement. In addition, this function gives applications access to the text timestamp mechanism, which can be used to ensure that two competing users do not inadvertently overwrite each other's modifications to the same value in the database. The dbwritetext function succeeds only if its timestamp parameter, usually obtained when the column's value was originally retrieved, matches the text column's timestamp in the database. If a match does occur, dbwritetext updates the text column, and at the same time updates the column's timestamp. This has the effect of governing updates by competing applications─an application's dbwritetext call fails if a second application updated the text column between the time the first application retrieved the column and the time it made its dbwritetext call. The dbwritetext function is similar to the TRANSACT-SQL WRITETEXT statement. It is usually more efficient to call dbwritetext than to send a WRITETEXT statement through the command buffer. For information on WRITETEXT, see the SQL Server Language Reference. The dbwritetext function can be invoked with or without logging, according to the value of the log parameter. To use dbwritetext with logging turned off, the database option select into/bulkcopy must be set to true, as shown in the following: sp_dboption 'mbdb', 'select into/bulk', 'true' This function, in conjunction with the dbmoretext function, also allows the application to send a large text or image value to SQL Server in the form of a number of smaller chunks. This is particularly useful with operating systems unable to allocate extremely long data buffers. No single block of text can be longer than 64K bytes; DB-LIBRARY does not support huge pointers. If the text parameter is a non-null value, dbwritetext executes the data transfer from start to finish, including any necessary calls to dbsqlok and dbresults. Here's a code fragment that illustrates this simple use of dbwritetext: LOGINREC *login; DBPROCESS *q_dbproc; DBPROCESS *u_dbproc; DBCHAR abstract_var[512]; /* Open separate DBPROCESSes for querying and updating. ** This is not strictly necessary in this example, which ** retrieves only one row. However, this approach becomes ** essential when performing updates on multiple rows of ** retrieved data. */ login = dblogin(); DBSETLUSER(login, "user"); DBSETLPWD(login, "my_passwd"); DBSETLAPP(login, "example1"); q_dbproc = dbopen(login, "my_server"); u_dbproc = dbopen(login, "my_server"); /* The database column "abstract" is a text column. Retrieve the ** value of one of its rows. */ dbcmd(q_dbproc, "select abstract from articles where article_id = 10"); dbsqlexec(q_dbproc); dbresults(q_dbproc); dbbind(q_dbproc, 1, STRINGBIND, (DBINT)0, abstract_var); /* For simplicity, we'll assume that just one row is returned. */ dbnextrow(q_dbproc); /* Here we can change the value of "abstract_var". For instance ... */ strcpy(abstract_var, "A brand new value."); /* Update the text column. */ dbwritetext (u_dbproc, "articles.abstract", dbtxptr(q_dbproc, 1), DBTXPLEN, dbtxtimestamp(q_dbproc, 1), TRUE, (DBINT)strlen(abstract_var), abstract_var); /* We're all done. */ dbexit(); To send chunks of text or image, rather than the whole value at once, set the text parameter to NULL. Then, dbwritetext will return control to the application immediately after notifying SQL Server that a text transfer is about to begin. The actual text is sent to SQL Server with dbmoretext, which can be called multiple times, once for each chunk. Here's a code fragment that illustrates the use of dbwritetext with dbmoretext: LOGINREC *login; DBPROCESS *q_dbproc; DBPROCESS *u_dbproc; DBCHAR part1[512]; static DBCHAR part2[512] = " This adds another sentence to the text."; login = dblogin(); DBSETLUSER(login, "user"); DBSETLPWD(login, "my_passwd"); DBSETLAPP(login, "example2"); q_dbproc = dbopen(login, "my_server"); u_dbproc = dbopen(login, "my_server"); dbcmd(q_dbproc, "select abstract from articles where article_id = 10"); dbsqlexec(q_dbproc); dbresults(q_dbproc); dbbind(q_dbproc, 1, STRINGBIND, (DBINT)0, part1); /* For simplicity, we'll assume that just one row is returned. */ dbnextrow(q_dbproc); /* ** Here we can change the value of the part of the text column. In ** this example, we will merely add a sentence to the end of the ** existing text. */ /* Update the text column. */ dbwritetext(u_dbproc, "articles.abstract", dbtxptr(q_dbproc, 1), DBTXPLEN, dbtxtimestamp(q_dbproc, 1), TRUE, (DBINT)(strlen(part1) + strlen(part2)), NULL); dbsqlok(u_dbproc); dbresults(u_dbproc); /* Send the update value in chunks. */ dbmoretext(u_dbproc, (DBINT)strlen(part1), part1); dbmoretext(u_dbproc, (DBINT)strlen(part2), part2); dbsqlok(u_dbproc); dbresults(u_dbproc); dbexit(); ──────────────────────────────────────────────────────────────────────────── NOTE Notice the required calls to dbsqlok and dbresults, between the call to dbwritetext and the first call to dbmoretext and after the final call to dbmoretext. ──────────────────────────────────────────────────────────────────────────── When dbwritetext is used with dbmoretext, it locks the specified database text column. The lock is not released until the final dbmoretext has sent its data. This ensures that a second application does not read or update the text column in the midst of the first application's update. Parameters dbproc The DBPROCESS structure that is the handle for a particular workstation/SQL Server process. It contains all the information that DB-LIBRARY uses to manage communications and data between the workstation and SQL Server. objname The database table and column name. The table and the column should be separated by a period. textptr The text pointer of the text or image value to be modified. This can be obtained by calling dbtxptr. textptrlen This parameter is included for future compatibility. For now, its value must be the defined constant DBTXPLEN. timestamp The text timestamp of the text or image value to be modified. This can be obtained by calling dbtxtimestamp. This value changes whenever the text or image value itself is changed. log A boolean value, specifying whether this dbwritetext operation should be recorded in the transaction log. size The total size, in bytes, of the text or image value to be written. text A pointer to the text or image to be written. If this pointer is NULL, DB-LIBRARY expects the application to call dbmoretext one or more times until all size bytes of data have been sent to SQL Server. No single data block can be larger than 64K bytes. DB-LIBRARY does not support huge pointers. Returns SUCCEED or FAIL. See Also dbmoretext, dbresults, dbsqlok, dbtxptr, dbtxtimestamp, dbtxtsnewval, dbtxtsput Chapter 3 Example Programs ──────────────────────────────────────────────────────────────────────────── Introduction The examples in this chapter illustrate typical uses for DB-LIBRARY functions in C programs. Note the following: ■ The header files sqlfront.h and sqldb.h are required and must be included in that order. ■ Before any calls to SQL Server are made, the application must connect to SQL Server with a call to dbopen, which returns a pointer to the DBPROCESS structure. ■ Normally, the first part of the program sends a set of SQL statements to SQL Server to retrieve some data. Then the information statements returned from SQL Server are bound or copied to program variables. ■ After all processing is complete, the application must call dbclose or dbexit to close the connection to SQL Server and free the DBPROCESS structures. The DB-LIBRARY sample code and a small data file are supplied with your DB-LIBRARY release in the sql\dblib\samples directory. You can copy the sample files into one of your directories to run them. Your system should have access to an SQL Server that includes the pubs sample database. See your System Administrator for instructions on creating the sample database if you do not have access to it. When you type these examples, replace the string "user" with your username, "my_password" with your SQL Server password, and the string "my_server" with the name of your server. Example 2 expects a file named datafile (supplied) and assumes that you have CREATE DATABASE permission on your server as well as enough space to create a small database. For information on compiling and linking the example programs, see Appendix E, "Building Applications." Note that the examples in this chapter apply only to the MS-DOS and MS OS/2 environments and will not work properly in the Windows environment. See Appendix E, "Building Applications," for information on building DB-LIBRARY Windows applications. Examples Sending Queries in a Command Batch /* ** This example illustrates how to send two queries to ** SQL Server in a command batch. It binds each set ** of results and prints the rows. */ #define DBMSOS2 #include <stdio.h> #include <sqlfront.h> #include <sqldb.h> #define DATELEN 26 #define TYPELEN 2 #define STDEXIT 0 /* Forward declarations of the error handler and message handler. */ int err_handler(); int msg_handler(); main(argc, argv) int argc; char *argv[]; { DBPROCESS *dbproc; /* Our connection with SQL Server. */ LOGINREC *login; /* Our login information. */ /* These are the variables used to store the returning data. */ DBCHAR crdate[DATELEN+1]; DBINT id; DBCHAR name[MAXNAME+1]; /* MAXNAME is defined in * "sqldb.h" as the maximum * length for names of database * objects, such as tables, * columns, and procedures. */ DBCHAR type[TYPELEN+1]; RETCODE result_code; /* Install the user-supplied error-handling and message-handling * functions. They are defined at the bottom of this source file. */ dberrhandle(err_handler); dbmsghandle(msg_handler); /* ** Get a LOGINREC structure and fill it with the necessary ** login information. */ login = dblogin(); DBSETLUSER(login, "user"); DBSETLPWD(login, "my_passwd"); DBSETLAPP(login, "example1"); /* ** Get a DBPROCESS structure for communicating with SQL Server. */ dbproc = dbopen(login, "my_server"); /* ** We are going to retrieve some information from a table ** named "sysobjects", regarding names of system tables and ** stored procedures. ** We will submit two queries. The first finds all the rows ** that describe system tables. The second finds all the rows ** that describe stored procedures. The program will only look ** at the first 10 rows that describe stored procedures. */ /* First, put the commands into the command buffer. */ dbcmd(dbproc, "select name, type, id, crdate from sysobjects"); dbcmd(dbproc, " where type = 'S' "); dbcmd(dbproc, "select name, type, id, crdate from sysobjects"); dbcmd(dbproc, " where type = 'P' "); /* ** SQL Server processes the command batch in the following ** order: ** ** 1) It checks for syntax errors (i.e., "use database pubs" ** is syntactically incorrect; it should be "use pubs"). ** 2) The second check is a semantic check (i.e., "select * from ** titels" is incorrect because the spelling should be ** "titles".) ** 3) The third check occurs in the actual execution phase. This ** check involves issues like permissions or memory problems. ** ** In the execution phase, dbsqlexec() and dbresults() can return ** the value "SUCCEED", which means there are more commands in the ** batch to process and that command was successful. A value ** of "FAIL" means that the query failed but there may be more ** commands in the batch to process. A value of "NO_MORE_RESULTS" ** means that there are no more commands in the batch to process. ** Therefore, the programmer must check the return values after ** dbsqlexec() and dbresults(), as illustrated below. ** */ /* Send the commands to SQL Server and start execution. */ dbsqlexec(dbproc); /* Process each command until there are no more. */ while ((result_code = dbresults(dbproc)) != NO_MORE_RESULTS) { if (result_code == SUCCEED) { /* Bind program variables. */ dbbind(dbproc, 1, NTBSTRINGBIND, (DBINT) 0, name); dbbind(dbproc, 2, NTBSTRINGBIND, (DBINT) 0, type); dbbind(dbproc, 3, INTBIND, (DBINT) 0, (BYTE *) &id); dbbind(dbproc, 4, NTBSTRINGBIND, (DBINT) 0, crdate); /* Print appropriate header for the type of * data coming back. */ printf("\n %s Objects: \n\n", DBCURCMD(dbproc) == 1 ? "System Table": "Procedure"); /* Now print the rows. */ while (dbnextrow(dbproc) != NO_MORE_ROWS) { /* ** If this is the 2nd command and ** 10th row, flush the rest of the ** rows for that command. */ if ((DBCURCMD(dbproc) == 2) && (DBCURROW(dbproc) 10)) continue; printf ("%s %s %ld %s\n", name, type, id, crdate); } } } /* Close our connection and exit the program. */ dbexit(); exit(STDEXIT); } int err_handler(dbproc, severity, dberr, oserr, dberrstr, oserrstr) DBPROCESS *dbproc; int severity; int dberr; int oserr; char *dberrstr; char *oserrstr; { if ((dbproc == NULL) || (DBDEAD(dbproc))) return(INT_EXIT); else { printf("DB-LIBRARY error:\n\t%s\n", dberrstr); if (oserr != DBNOERR) printf("Operating-system error:\n\t%s\n", oserrstr); return(INT_CANCEL); } } int msg_handler(dbproc, msgno, msgstate, severity, msgtext) DBPROCESS *dbproc; DBINT msgno; int msgstate; int severity; char *msgtext; { printf ("SQL Server message %ld, state %d, severity %d:\n\t%s\n", msgno, msgstate, severity, msgtext); return(0); } Working with Data Files /* ** This example opens a data file, inserts data from the file ** into a newly created table containing several of the ** SQL Server datatypes, and binds and prints the results. ** You must have a file named datafile and CREATE DATABASE ** permission in your login database. Also you must have enough room ** in your default device to create a new database (minimum 2Mb). */ #define DBMSOS2 #include <stdio.h> #include <sqlfront.h> #include <sqldb.h> #define STDEXIT 0 #define BUFLEN 2048 #define HEXLEN 510 #define PLEN 25 /* Forward declarations of the error-handling and message-handling functions. */ int err_handler(); int msg_handler(); main(argc,argv) int argc; char *argv[]; { LOGINREC *login; DBPROCESS *dbproc; RETCODE return_code; DBTINYINT age; DBSMALLINT userid; DBINT royalty; DBCHAR name[PLEN+1]; DBBINARY title_id[PLEN+1]; DBBIT us_citizen; DBFLT8 account; DBCHAR title[PLEN+1]; /* string */ DBCHAR manager[PLEN+1]; /* ntbstring */ DBCHAR id_buffer[HEXLEN+1]; static char cmdbuf[BUFLEN]; FILE *infile; /* Install the user-supplied error-handling and message-handling * functions. They are defined at the bottom of this source file. */ dberrhandle(err_handler); dbmsghandle(msg_handler); /* Allocate and initialize the LOGINREC structure to be used * to open a connection to SQL Server. */ login = dblogin(); DBSETLUSER(login, "user"); DBSETLPWD(login, "my_passwd"); DBSETLAPP(login, "example2"); dbproc = dbopen(login, "my_server"); printf("Creating the 'test' database.\n"); dbcmd(dbproc,"create database test "); dbsqlexec(dbproc); dbresults(dbproc); dbuse(dbproc,"test"); printf("Creating the 'alltypes' table.\n"); /* Create a table that contains several SQL Server datatypes. */ dbcmd(dbproc,"create table alltypes "); dbcmd(dbproc,"(age tinyint,"); dbcmd(dbproc,"userid smallint,"); dbcmd(dbproc,"royalty int,"); dbcmd(dbproc,"name char(25),"); dbcmd(dbproc,"title_id varbinary(20),"); dbcmd(dbproc,"us_citizen bit,"); dbcmd(dbproc,"account float,"); dbcmd(dbproc,"title varchar(20),"); dbcmd(dbproc,"manager char(25))"); dbsqlexec(dbproc); dbresults(dbproc); /* Insert rows of data into the newly created table "alltypes". * We will read in the contents of the file and form an * INSERT statement. */ if ((infile=fopen("datafile","r")) == NULL) { printf("Unable to open file 'datafile'.\n"); exit(STDEXIT); } printf("Inserting rows into the 'alltypes' table.\n"); while ((fgets(cmdbuf,BUFLEN,infile)) != NULL) { dbfcmd(dbproc,"insert into alltypes \n"); dbfcmd(dbproc,"values(%s) \n",cmdbuf); } dbsqlexec(dbproc); /* Process the results of each of the INSERT statements. */ while ((return_code = dbresults(dbproc)) != NO_MORE_RESULTS) { if (return_code == FAIL) printf("One of the insert statements failed.\n"); } printf("Selecting rows from the 'alltypes' table:\n"); dbcmd(dbproc,"select * from alltypes"); dbsqlexec(dbproc); while ((return_code = dbresults(dbproc)) != NO_MORE_RESULTS) { if (return_code == SUCCEED) { dbbind(dbproc,1,TINYBIND, (DBINT) 0,(BYTE *) &age); dbbind(dbproc,2,SMALLBIND, (DBINT) 0,(BYTE *) &userid); dbbind(dbproc,3,INTBIND, (DBINT) 0,(BYTE *) &royalty); dbbind(dbproc,4,CHARBIND, (DBINT) 0,name); dbbind(dbproc,5,BINARYBIND, (DBINT) 0,title_id); dbbind(dbproc,6,BITBIND, (DBINT) 0,(BYTE *) &us_citizen); dbbind(dbproc,7,FLT8BIND, (DBINT) 0,(BYTE *) &account); dbbind(dbproc,8,STRINGBIND, (DBINT) 0,title); dbbind(dbproc,9,NTBSTRINGBIND, (DBINT) 0,manager); /* ** Initialize null terminator in "name" array, ** since CHARBIND does not add one. */ name[PLEN] = '\0'; while (dbnextrow(dbproc) != NO_MORE_ROWS) { dbconvert (dbproc, SQLBINARY, title_id, dbdatlen(dbproc, 5), SQLCHAR, id_buffer, (DBINT)-1); printf ("%d %d %ld %s 0x%s\n", age, userid, royalty, name, id_buffer); printf ("%d %8.2f %s %s\n", us_citizen, account, title, manager); } } } dbexit(); exit(STDEXIT); } int err_handler(dbproc, severity, dberr, oserr, dberrstr, oserrstr) DBPROCESS *dbproc; int severity; int dberr; int oserr; char *dberrstr; char *oserrstr; { if ((dbproc == NULL) || (DBDEAD(dbproc))) return(INT_EXIT); else { printf("DB-LIBRARY error:\n\t%s\n", dberrstr); if (oserr != DBNOERR) printf("Operating-system error:\n\t%s\n", oserrstr); return(INT_CANCEL); } } int msg_handler(dbproc, msgno, msgstate, severity, msgtext) DBPROCESS *dbproc; DBINT msgno; int msgstate; int severity; char *msgtext; { printf ("SQL Server message %ld, state %d, severity %d:\n\t%s\n", msgno, msgstate, severity, msgtext); return(0); } Binding Aggregate and Compute Results /* ** This example selects some information from the "pubs" database. ** It illustrates binding of both aggregate and compute results. ** ** Note that this example only work if the "pubs" database exists ** on your SQL Server. */ #define DBMSOS2 #include <stdio.h> #include <sqlfront.h> #include <sqldb.h> #define STDEXIT 0 #define PLEN 4 #define DATEPRINT 26 #define MONEYPRINT 12 /* Forward declarations of the error handler and message handler. */ int err_handler(); int msg_handler(); main(argc,argv) int argc; char *argv[]; { LOGINREC *login; DBPROCESS *dbproc; /* Declare the datatypes for the columns in the table "titles". */ DBINT pcount; DBINT sales; DBINT salesavg; DBINT sumsale; DBCHAR date[DATEPRINT+1]; DBCHAR price[MONEYPRINT+1]; DBCHAR priceavg[MONEYPRINT+1]; DBCHAR pubid[PLEN+1]; RETCODE result_code; /* to hold the results of dbresults() */ STATUS row_code; /* to hold the results of dbnextrow() */ /* Install the user-supplied error-handling and message-handling * functions. They are defined at the bottom of this source file. */ dberrhandle(err_handler); dbmsghandle(msg_handler); /* Set up the login information. */ login = dblogin(); DBSETLUSER(login, "user"); DBSETLPWD(login, "my_passwd"); DBSETLAPP(login, "example3"); dbproc = dbopen(login, "my_server"); /* Send a "use database" command. */ dbuse(dbproc,"pubs"); /* Put the SQL statement into the command buffer. */ dbcmd(dbproc, "select pub_id, pubdate, price, avg(price), ytd_sales,"); dbcmd(dbproc, " avg(ytd_sales), sum(ytd_sales) from titles"); dbcmd(dbproc, " group by pub_id"); dbcmd(dbproc, " order by pub_id"); dbcmd(dbproc, " compute count(pub_id) by pub_id"); /* Send the command buffer to SQL Server for execution. */ dbsqlexec(dbproc); /* ** Using the aggregates "sum" and "avg" with the COMPUTE clause ** necessitates special handling when binding the results. Since each ** aggregate creates a new column, this is accounted for in the bind. ** Notice that avg(price) is the fourth column in the select list ** and is also specified as the fourth column in the dbbind() function. ** ** The COMPUTE clause creates a compute row, which requires a ** special bind function called dbaltbind(). */ while ((result_code = dbresults(dbproc)) != NO_MORE_RESULTS) { if (result_code == SUCCEED) { dbbind(dbproc,1,NTBSTRINGBIND, (DBINT) 0,pubid); dbbind(dbproc,2,NTBSTRINGBIND, (DBINT) 0,date); dbbind(dbproc,3,NTBSTRINGBIND, (DBINT) 0,price); dbbind(dbproc,4,NTBSTRINGBIND, (DBINT) 0,priceavg); dbbind(dbproc,5,INTBIND, (DBINT) 0, (BYTE *) &sales); dbbind(dbproc,6,INTBIND, (DBINT) 0, (BYTE *) &salesavg); dbbind(dbproc,7,INTBIND, (DBINT) 0, (BYTE *) &sumsale); /* dbaltbind() binds compute columns. */ dbaltbind(dbproc,1,1,INTBIND,(DBINT) 0,(BYTE *)&pcount); printf("\nAccounts:\n"); printf("---------\n\n"); printf ("%-5s %-19s %-6s %-10s %-5s %-10s %-10s\n\n", "pubid", "date", "price", "avg(price)", "sales", "avg(sales)", "sum(sales)"); /* ** Print out each result row, using different statements ** depending on whether the row is a regular row or a ** compute row. */ while ((row_code = dbnextrow(dbproc)) != NO_MORE_ROWS) { if (row_code == REG_ROW) { printf("%5s %19s %6s %10s %5ld %10ld %10ld\n", pubid, date, price, priceavg, sales, salesavg, sumsale); } else printf("title count: %ld\n\n",pcount); } } } dbexit(); exit(STDEXIT); } int err_handler(dbproc, severity, dberr, oserr, dberrstr, oserrstr) DBPROCESS *dbproc; int severity; int dberr; int oserr; char *dberrstr; char *oserrstr; { if ((dbproc == NULL) || (DBDEAD(dbproc))) return(INT_EXIT); else { printf("DB-LIBRARY error:\n\t%s\n", dberrstr); if (oserr != DBNOERR) printf("Operating-system error:\n\t%s\n", oserrstr); return(INT_CANCEL); } } int msg_handler(dbproc, msgno, msgstate, severity, msgtext) DBPROCESS *dbproc; DBINT msgno; int msgstate; int severity; char *msgtext; { printf ("SQL Server message %ld, state %d, severity %d:\n\t%s\n", msgno, msgstate, severity, msgtext); return(0); } Using Row Buffering /* ** This example accesses the data within each row without using dbbind() ** and illustrates the use of row buffering. ** ** It runs a query, saves all of the returned rows (up to a maximum ** of 100) using DB-LIBRARY row buffering, and allows the user to ** examine data rows at random. */ #define DBMSOS2 #include <stdio.h> #include <sqlfront.h> #include <sqldb.h> extern char *malloc(); DBPROCESS *dbproc; /* Our connection with SQL Server. */ LOGINREC *login; /* Our login information. */ #define STDEXIT 0 #define TYPELEN 2 #define DATELEN 25 /* Forward declarations of the error handler and message handler. */ int err_handler(); int msg_handler(); main(argc, argv) int argc; char *argv[]; { /* Here are the variables which will be used to store the * data being examined. */ DBCHAR name[MAXNAME+1]; /* MAXNAME is defined in * "sqldb.h" as the maximum * length for names of database * objects, such as tables, * columns, and procedures. */ DBCHAR type[TYPELEN+1]; DBINT id; DBCHAR datebuf[64]; DBINT len; char numstring[32]; int quitflag = 0; RETCODE row_code; DBINT rownum; /* Install the user-supplied error-handling and message-handling * functions. They are defined at the bottom of this source file. */ dberrhandle(err_handler); dbmsghandle(msg_handler); /* ** Get a LOGINREC structure and fill it with the necessary ** login information. */ login = dblogin(); DBSETLUSER (login, "user"); DBSETLPWD(login, "my_passwd"); DBSETLAPP(login, "example4"); dbproc = dbopen(login, "my_server"); dbcmd(dbproc, "select name, type, id, crdate from sysobjects"); dbcmd(dbproc, " where type = 'S'"); /* ** Set row buffering to 100 rows, via dbsetopt(). ** Note that this parameter must be passed as an ASCII string. */ dbsetopt(dbproc, DBBUFFER, "100"); dbsqlexec(dbproc); if (dbresults(dbproc) == SUCCEED) { /* Read all of the rows into DB-LIBRARY's buffer */ while ((row_code = dbnextrow(dbproc)) != NO_MORE_ROWS) { /* If DB-LIBRARY's row buffer is full, throw * away the oldest row to allow the newest * row to be read in. */ if (row_code == BUF_FULL) dbclrbuf(dbproc, (DBINT) 1); } /* Print out the column headers. */ printf (" %-20s %10s %25s %15s \n", " NAME ", "TYPE", " DATE ", "ID"); printf (" %20s %10s %25s %15s \n", "--------------------", "----", "----------------------", "----"); /* Let the user view any row in the table. */ printf("Type the number of the row you want to see.\n"); printf("The first row is number 1.\n"); printf("Asking for row 0 will terminate the program.\n"); while (quitflag == 0) { printf("Row number: "); gets(numstring); rownum = atoi(numstring); if (rownum == 0) quitflag = 1; else { /* Print out the requested row. */ if (dbgetrow(dbproc, rownum) == NO_MORE_ROWS) printf ("That row is not in the table.\n"); else { /* Copy variable-length character data * (colname). */ strncpy (name, (DBCHAR *)dbdata(dbproc, 1), (len = dbdatlen(dbproc, 1))); /* String needs terminating null. */ name[len] = '\0'; /* Copy fixed-length character data. */ strncpy (type, (DBCHAR *)dbdata(dbproc, 2), (len = dbdatlen(dbproc, 2))); type[len] = '\0'; /* Copy integer data. */ id = *((DBINT *)dbdata(dbproc, 3)); /* Convert datetime data to a printable * string. */ dbconvert(dbproc, SQLDATETIME, (dbdata(dbproc, 4)), (DBINT)-1, SQLCHAR, datebuf, (DBINT)-1); printf ("%20s %10s %25s %15ld \n", name, type, datebuf, id); } } } } dbexit(); exit(STDEXIT); } int err_handler(dbproc, severity, dberr, oserr, dberrstr, oserrstr) DBPROCESS *dbproc; int severity; int dberr; int oserr; char *dberrstr; char *oserrstr; { if ((dbproc == NULL) || (DBDEAD(dbproc))) return(INT_EXIT); else { printf("DB-LIBRARY error:\n\t%s\n", dberrstr); if (oserr != DBNOERR) printf("Operating-system error:\n\t%s\n", oserrstr); return(INT_CANCEL); } } int msg_handler(dbproc, msgno, msgstate, severity, msgtext) DBPROCESS *dbproc; DBINT msgno; int msgstate; int severity; char *msgtext; { printf ("SQL Server message %ld, state %d, severity %d:\n\t%s\n", msgno, msgstate, severity, msgtext); return(0); } Converting Data with dbconvert /* ** This example illustrates dbconvert. It converts a ** number of constants to strings, a number of strings ** to numerical or binary quantities, and a number of ** numerical quantities to other numerical types. ** */ #define DBMSOS2 #include <stdio.h> #include <sqlfront.h> #include <sqldb.h> #define STDEXIT 0 #define ARRAY_LEN 20 /* Forward declarations of the error handler and message handler. */ int err_handler(); int msg_handler(); main(argc, argv) int argc; char *argv[]; { /* These variables hold the results of data conversions. */ static DBBINARY my_binary_array[ARRAY_LEN] = {0x12, 0x34, 0x56, 0x78, 0x9a, 0xbc, 0xde, 0xf0}; DBFLT8 my_flt8; DBINT my_int4; DBMONEY my_money; DBCHAR my_string[ARRAY_LEN]; /* Install the user-supplied error-handling and message-handling * functions. They are defined at the bottom of this source file. */ dberrhandle(err_handler); dbmsghandle(msg_handler); /* Convert numerical and binary constants to strings. */ dbconvert ((DBPROCESS *)NULL, SQLBINARY, my_binary_array, (DBINT)8, SQLCHAR, my_string,(DBINT)-1); printf("Binary constant 0x123456789abcdef0 converted to string"); printf("\"%s\".\n\n", my_string); my_flt8 = 55.555; dbconvert ((DBPROCESS *)NULL, SQLFLT8, (BYTE *) &my_flt8, (DBINT)-1, SQLCHAR, my_string, (DBINT)-1); printf ("Floating-pt constant 55.555 converted to string \"%s\".\n\n", my_string); /* Convert string constants to numerical and binary quantities. */ dbconvert ((DBPROCESS *)NULL, SQLCHAR, "123", (DBINT)-1, SQLINT4, (BYTE *) &my_int4, (DBINT)-1); printf ("String constant \"123\" converted to 4-byte integer %ld.\n\n", my_int4); dbconvert ((DBPROCESS *)NULL, SQLCHAR, "0xfedc", (DBINT)-1, SQLBINARY, my_binary_array, (DBINT)ARRAY_LEN); printf("String constant \"0xfedc\" converted to binary sequence "); printf("%x.\n\n", *((int *)my_binary_array)); dbconvert ((DBPROCESS *)NULL, SQLCHAR, "123.456", (DBINT)-1, SQLFLT8, (BYTE *) &my_flt8, (DBINT)-1); printf("String constant \"123.456\" converted to "); printf("floating-pt number %f.\n\n", my_flt8); /* Convert numerical types to other numerical types. */ my_flt8 = 98.76; dbconvert ((DBPROCESS *)NULL, SQLFLT8, (BYTE *) &my_flt8, (DBINT)-1, SQLMONEY, (BYTE *) &my_money, (DBINT)-1); dbconvert ((DBPROCESS *)NULL, SQLMONEY, (BYTE *) &my_money, (DBINT)-1, SQLCHAR, my_string, (DBINT)-1); printf ("floating-pt number %f converted to money value %s.\n\n", my_flt8, my_string); dbconvert ((DBPROCESS *)NULL, SQLMONEY, (BYTE *) &my_money, (DBINT)-1, SQLFLT8, (BYTE *) &my_flt8, (DBINT)-1); printf ("money value %s converted to floating-pt value %f.\n\n", my_string, my_flt8); } int err_handler(dbproc, severity, dberr, oserr, dberrstr, oserrstr) DBPROCESS *dbproc; int severity; int dberr; int oserr; char *dberrstr; char *oserrstr; { if ((dbproc == NULL) || (DBDEAD(dbproc))) return(INT_EXIT); else { printf("DB-LIBRARY error:\n\t%s\n", dberrstr); if (oserr != DBNOERR) printf("Operating-system error:\n\t%s\n", oserrstr); return(INT_CANCEL); } } int msg_handler(dbproc, msgno, msgstate, severity, msgtext) DBPROCESS *dbproc; DBINT msgno; int msgstate; int severity; char *msgtext; { printf ("SQL Server message %ld, state %d, severity %d:\n\t%s\n", msgno, msgstate, severity, msgtext); return(0); } Querying and Updating the Database /* ** This example illustrates opening a data file, inserting data ** from the file into a newly created table containing several ** SQL Server datatypes, and updating the table using browse mode ** techniques. */ #define DBMSOS2 #include <stdio.h> #include <sqlfront.h> #include <sqldb.h> #define STDEXIT 0 #define BUFLEN 2048 /* Forward declarations of the error-handling and message-handing functions. */ int err_handler(); int msg_handler(); main() { LOGINREC *login; DBPROCESS *q_dbproc; /* This DBPROCESS is used to * query the database. */ DBPROCESS *u_dbproc; /* This DBPROCESS is used to * simultaneously update the database. */ char *qualptr; /* This points to the WHERE clause * appropriate for updating q_dbproc's current data row. */ RETCODE return_code; DBTINYINT age; static char cmdbuf[BUFLEN]; FILE *infile; /* Install the user-supplied error-handling and message-handling * functions. They are defined at the bottom of this source file. */ dberrhandle(err_handler); dbmsghandle(msg_handler); /* Allocate and initialize the LOGINREC structure to be used * to open a connection to SQL Server. */ login = dblogin(); DBSETLUSER(login, "user"); DBSETLPWD(login, "my_passwd"); DBSETLAPP(login, "example6"); q_dbproc = dbopen(login, "my_server"); u_dbproc = dbopen(login, "my_server"); printf("Creating the 'alltypes' table.\n"); /* Create a table that contains several SQL Server datatypes. */ dbcmd(q_dbproc,"create table alltypes "); dbcmd(q_dbproc,"(age tinyint,"); dbcmd(q_dbproc,"userid smallint,"); dbcmd(q_dbproc,"royalty int,"); dbcmd(q_dbproc,"name char(25),"); dbcmd(q_dbproc,"title_id varbinary(20),"); dbcmd(q_dbproc,"us_citizen bit,"); dbcmd(q_dbproc,"account float,"); dbcmd(q_dbproc,"title varchar(20),"); dbcmd(q_dbproc,"manager char(25),"); dbcmd(q_dbproc,"timestamp)"); dbcmd(q_dbproc, "create unique index index1 on alltypes(userid)"); dbsqlexec(q_dbproc); while (dbresults(q_dbproc) != NO_MORE_RESULTS) continue; /* Insert rows of data into the newly created table "alltypes". * We will read in the contents of the file and form an * INSERT statement. */ if ((infile=fopen("datafile","r")) == NULL) { printf("Unable to open file 'datafile'.\n"); exit(STDEXIT); } printf("Inserting rows into the 'alltypes' table...\n"); while ((fgets(cmdbuf,BUFLEN,infile)) != NULL) { dbfcmd(q_dbproc,"insert into alltypes \n"); dbfcmd(q_dbproc,"values(%s, null) \n",cmdbuf); } dbsqlexec(q_dbproc); /* Process the results of each of the INSERT statements. */ while ((return_code = dbresults(q_dbproc)) != NO_MORE_RESULTS) { if (return_code == FAIL) printf("One of the insert statements failed.\n"); } /* Using DB-LIBRARY's browse mode facilities, we'll increment * the age of every person in the table. */ printf("Updating rows in the 'alltypes' table...\n"); dbcmd(q_dbproc,"select * from alltypes for browse"); dbsqlexec(q_dbproc); while ((return_code = dbresults(q_dbproc)) != NO_MORE_RESULTS) { if (return_code == SUCCEED) { while (dbnextrow(q_dbproc) != NO_MORE_ROWS) { age = *((DBTINYINT *)(dbdata(q_dbproc, 1))); qualptr = dbqual(q_dbproc, -1, "alltypes"); dbcmd(u_dbproc, "update alltypes"); dbfcmd (u_dbproc, " set age = %d %s", age+1, qualptr); dbsqlexec(u_dbproc); dbresults(u_dbproc); dbfreequal(qualptr); } } } /* Now, we'll look at the updated contents of the table to * verify that the ages were properly incremented. */ printf("Selecting rows from the 'alltypes' table:\n"); dbcmd(q_dbproc, "select * from alltypes"); dbsqlexec(q_dbproc); dbresults(q_dbproc); dbprrow(q_dbproc); dbexit(); exit(STDEXIT); } int err_handler(dbproc, severity, dberr, oserr, dberrstr, oserrstr) DBPROCESS *dbproc; int severity; int dberr; int oserr; char *dberrstr; char *oserrstr; { if ((dbproc == NULL) || (DBDEAD(dbproc))) return(INT_EXIT); else { printf("DB-LIBRARY error:\n\t%s\n", dberrstr); if (oserr != DBNOERR) printf("Operating-system error:\n\t%s\n", oserrstr); return(INT_CANCEL); } } int msg_handler(dbproc, msgno, msgstate, severity, msgtext) DBPROCESS *dbproc; DBINT msgno; int msgstate; int severity; char *msgtext; { printf ("SQL Server message %ld, state %d, severity %d:\n\t%s\n", msgno, msgstate, severity, msgtext); return(0); } Using Browse-Mode Functions /* ** This example illustrates the use of browse mode functions to ** determine the source of result columns from ad hoc queries. */ #define DBMSOS2 #include <stdio.h> #include <sqlfront.h> #include <sqldb.h> #define STDEXIT 0 void examine_results(); void send_command(); /* Forward declarations of the error-handling and message-handling functions. */ int err_handler(); int msg_handler(); main() { LOGINREC *login; DBPROCESS *dbproc; int command_count = 0; RETCODE retcode; /* Install the user-supplied error-handling and message-handling * functions. They are defined at the bottom of this source file. */ dberrhandle(err_handler); dbmsghandle(msg_handler); /* Allocate and initialize the LOGINREC structure to be used * to open a connection to SQL Server. */ login = dblogin(); DBSETLUSER(login, "user"); DBSETLPWD(login, "my_passwd"); DBSETLAPP(login, "example7"); dbproc = dbopen(login, "my_server"); /* Allow the user to type in a series of queries. This program * is terminated by the word "quit" appearing at the * beginning of the line. */ while (1) { /* Send a user-generated query to SQL Server. */ send_command(dbproc); /* Now, examine the results of any queries the user has * typed in. */ command_count = 1; while ((retcode = dbresults(dbproc)) != NO_MORE_RESULTS) { command_count++ ; if (retcode == FAIL) printf("Command %d failed.\n", command_count); else { if (!(DBCMDROW(dbproc))) printf ("Command %d returned no rows.\n", command_count); else { /* This is a command that can return * rows. Let's take a closer look at it. */ printf("Command %d:\n", command_count); examine_results(dbproc); /* Throw away all data rows. */ dbcanquery(dbproc); } } } } } void examine_results(dbproc) DBPROCESS *dbproc; { int colcount; int colnum; char fullsource[128]; char *sourcecolname; int tabcount; char *tabname; int tabnum; /* Determine which tables were used to generate the query results.*/ tabcount = dbtabcount(dbproc); printf ("The following tables were used to generate these query results:\n"); for (tabnum = 1; tabnum <= tabcount; tabnum++) { if ((tabname = dbtabname(dbproc, tabnum)) != NULL) printf ("\t%s (%s)\n", tabname, (dbtabbrowse(dbproc, tabnum) ? "browsable" : "not browsable")); } /* Determine which tables were used to generate each result column.*/ colcount = dbnumcols(dbproc); printf("Here are the columns of the target list and their sources:\n"); printf ("\t%-20s %-30s %s\n\n", "Result column:", "Source:", "Browsable?"); for (colnum = 1; colnum colcount; colnum++) { tabname = dbtabsource(dbproc, colnum, NULL); sourcecolname = dbcolsource(dbproc, colnum); if (tabname == NULL) strcpy(fullsource, "(result of expression)"); else sprintf(fullsource, "%s.%s", tabname, sourcecolname); printf ("\t%-20s %-30s %s\n", dbcolname(dbproc, colnum), fullsource, (dbcolbrowse(dbproc, colnum) ? "yes" : "no")); } } void send_command(dbproc) DBPROCESS *dbproc; { static char cmdbuf[2048]; /* Allow the user to type in an ad hoc query. This query * is terminated by the word "go" appearing at the * beginning of the line. * * If the user types the word "quit" at the beginning of a line, * we'll quit the program. */ printf("Enter SQL query:\n"); while (1) { printf(" "); gets(cmdbuf); if (strcmp(cmdbuf, "go") == 0) { dbsqlexec(dbproc); break; } else if (strcmp(cmdbuf, "quit") == 0) { exit(STDEXIT); } else { /* Keep reading SQL commands. */ dbcmd(dbproc, cmdbuf); } } } int err_handler(dbproc, severity, dberr, oserr, dberrstr, oserrstr) DBPROCESS *dbproc; int severity; int dberr; int oserr; char *dberrstr; char *oserrstr; { if ((dbproc == NULL) || (DBDEAD(dbproc))) return(INT_EXIT); else { printf("DB-LIBRARY error:\n\t%s\n", dberrstr); if (oserr != DBNOERR) printf("Operating-system error:\n\t%s\n", oserrstr); return(INT_CANCEL); } } int msg_handler(dbproc, msgno, msgstate, severity, msgtext) DBPROCESS *dbproc; DBINT msgno; int msgstate; int severity; char *msgtext; { printf ("SQL Server message %ld, state %d, severity %d:\n\t%s\n", msgno, msgstate, severity, msgtext); return(0); } Chapter 4 Bulk Copy Special Library ──────────────────────────────────────────────────────────────────────────── Introduction This chapter contains syntax and usage information about the functions contained in the Bulk Copy Special Library. The information is arranged in alphabetical order by function name. For more information about the bcp utility, see the SQL Server System Administrator's Guide. bcp_batch ──────────────────────────────────────────────────────────────────────────── Function Saves any preceding rows in SQL Server. Syntax DBINT bcp_batch(dbproc) DBPROCESS *dbproc; Comments When an application uses bcp_bind and bcp_sendrow to bulk copy rows from program variables to SQL Server tables, the rows are permanently saved in SQL Server only when the program calls bcp_batch or bcp_done. You can call bcp_batch once every n rows or when there is a lull between periods of incoming data (as in a telemetry application). Of course, you can choose some other criteria, or can decide not to call bcp_batch at all. If bcp_batch is not called, the rows are permanently saved in SQL Server when bcp_done is called. Parameters dbproc The DBPROCESS structure that is the handle for a particular workstation/SQL Server process. It contains all the information that DB-LIBRARY uses to manage communications and data between the workstation and SQL Server. Returns The number of rows saved since the last call to bcp_batch, or -1 in case of error. See Also bcp_bind, bcp_done, bcp_sendrow bcp_bind ──────────────────────────────────────────────────────────────────────────── Function Binds data from a program variable to an SQL Server table. Syntax RETCODE bcp_bind (dbproc, varaddr, prefixlen, varlen, terminator, termlen, type, table_column) DBPROCESS *dbproc; BYTE *varaddr; int prefixlen; DBINT varlen; BYTE *terminator; int termlen; int type; int table_column; Comments There can be times when you want to copy data directly from a program variable into a table in SQL Server without having to first place the data in an operating system file or use the SQL INSERT statement. The bcp_bind function is a fast and efficient way to do this. You must call bcp_init before calling this or any other bulk copy functions. There must be a separate bcp_bind call for every column in the SQL Server table into which you want to copy. After the necessary bcp_bind calls have been made, you then call bcp_sendrow to send a row of data from your program variables to SQL Server. The table to be copied into is set by calling bcp_init. Whenever you want SQL Server to checkpoint the rows already received, call bcp_batch. For example, you can call bcp_batch once for every 1000 rows inserted or at any other interval. When there are no more rows to be inserted, call bcp_done. Failure to do so results in an error. When using bcp_bind, the operating system filename parameter, hfile, used in the call to bcp_init, must be set to NULL, and the direction parameter, direction, must be set to DB_IN. Control parameter settings, specified with bcp_control, have no effect on bcp_bind row transfers. It is an error to call bcp_columns when using bcp_bind. The following program fragment illustrates bcp_bind: LOGINREC *login; DBPROCESS *dbproc; char co_name[MAXNAME]; DBINT co_id; DBINT rows_sent; DBBOOL more_data; char *terminator = "\t\t"; /* Install error-handler and message-handler. */ dberrhandle(err_handler); dbmsghandle(msg_handler); /* Open a DBPROCESS. */ login = dblogin(); DBSETLUSER(login, "user"); DBSETLPWD(login, "my_passwd"); DBSETLAPP(login, "example"); BCP_SETL(login, TRUE); dbproc = dbopen(login, "my_server"); /* Initialize bcp. */ if (bcp_init(dbproc, "comdb..accounts_info", (BYTE *)NULL, (BYTE *)NULL, DB_IN) == FAIL) exit(ERREXIT); /* Bind program variables to table columns. */ if (bcp_bind(dbproc, (BYTE *)&co_id, 0, (DBINT)-1, (BYTE *)NULL, 0, 0, 1) == FAIL) { fprintf(stderr, "bcp_bind, column 1, failed.\n"); exit(ERREXIT); } if (bcp_bind (dbproc, co_name, 0, (DBINT)-1, terminator, strlen(terminator), 0, 2) == FAIL) { fprintf(stderr, "bcp_bind, column 2, failed.\n"); exit(ERREXIT); } while (TRUE) { /* Process/retrieve program data. */ more_data = getdata(&co_id, co_name); if (more_data == FALSE) break; /* Send the data. */ if (bcp_sendrow(dbproc) == FAIL) exit(ERREXIT); } /* Terminate the bulk copy operation. */ if ((rows_sent = bcp_done(dbproc)) == -1) printf("Bulk-copy unsuccessful.\n"); else printf("%ld rows copied.\n", rows_sent); Parameters dbproc The DBPROCESS structure that is the handle for a particular workstation/SQL Server process. It contains all the information that DB-LIBRARY uses to manage communications and data between the workstation and SQL Server. varaddr The address of the program variable from which the data is copied. If type is SQLTEXT or SQLIMAGE, varaddr can be NULL. A NULL varaddr indicates that text and image values will be sent to SQL Server in chunks by bcp_moretext, rather than all at once by bcp_sendrow. prefixlen The length, in bytes, of any length prefix this column can have; valid length prefixes are 0, 1, 2, or 4 bytes. For example, strings in some non-C programming languages are made up of a 1-byte length prefix, followed by the string data itself. If the data does not have a length prefix, set prefixlen to 0. varlen The length of the data in the program variable, not including the length of any length prefix and/or terminator. Setting varlen to 0 signifies that the data is NULL. Setting varlen to -1 indicates that the system should ignore this parameter. For fixed-length datatypes, such as integers, the datatype itself indicates to the system the length of the data. Therefore, for fixed-length datatypes, varlen must always be -1 except when the data is NULL, in which case varlen must be 0. For character, text, binary, and image data, varlen can be -1, 0, or some positive value. If varlen is -1, the system uses either a length prefix or a terminator sequence to determine the length. (If both are supplied, the system uses the one that results in the shortest amount of data being copied.) If varlen is -1 and neither a prefix length nor a terminator sequence is specified, the system returns an error message. If varlen is 0, the system assumes the data is NULL. If varlen is some positive value, the system uses varlen as the data length. However, if, in addition to a positive varlen, a prefix length and/or terminator sequence is provided, the system determines the data length by using the method that results in the shortest amount of data being copied. terminator A pointer to the byte pattern, if any, that marks the end of this program variable. For example, C strings usually have a 1-byte terminator whose value is 0. If there is no terminator for the variable, set terminator to NULL. If you want to designate the C null terminator as the program variable terminator, the simplest way is to use an empty string (" ") as terminator and set termlen to 1, since the null terminator constitutes a single byte. For instance, the second bcp_bind call in the previous example uses two tabs as the program variable terminator. It could be rewritten to use a C null terminator instead, as follows: (bcp_bind (dbproc, co_name, 0, -1, "", 1, 0, 2) termlen The length of this program variable's terminator, if any. If there is no terminator for the variable, set termlen to 0. type The datatype of your program variable. The data in the program variable is automatically converted to the type of the database column. If this parameter is 0, no conversion is performed. See "dbconvert" for a list of supported conversions. See Appendix C, "DB-LIBRARY Datatypes," for the list of valid SQL Server datatypes. table_column The column in the database table to which the data is copied. Column numbers start at 1. Returns SUCCEED or FAIL. See Also bcp_batch, bcp_colfmt, bcp_collen, bcp_colptr, bcp_columns, bcp_control, bcp_done, bcp_exec, bcp_init, bcp_moretext, bcp_sendrow, dbconvert, Appendix C bcp_colfmt ──────────────────────────────────────────────────────────────────────────── Function Specifies the format of an operating system file for bulk copy. Syntax RETCODE bcp_colfmt (dbproc, file_column, file_type, file_prefixlen, file_collen, file_term, file_termlen, table_column) DBPROCESS *dbproc; int file_column; BYTE file_type; int file_prefixlen; DBINT file_collen; BYTE *file_term; int file_termlen; int table_column; Comments The bcp_colfmt function allows you to specify the operating system file format for bulk copies. For bulk copy, a format contains the following parts: ■ A mapping from operating system file columns to database columns. ■ The datatype of each operating system file column. ■ The length of the optional length prefix of each column. ■ The maximum length of the operating system file column's data. ■ The optional terminating byte sequence for each column. ■ The length of this optional terminating byte sequence. Each call to bcp_colfmt specifies the format for one operating system file column. For example, if you have a table with five columns and want to change the default settings for three of those columns, you should first call bcp_columns(5), and then call bcp_colfmt five times, with three of those calls setting your custom format. The remaining two calls should have their file_type set to 0 and their file_prefixlen, file_collen, and file_termlen parameters set to -1. The result of this would be to copy all five columns─three with your customized format and two with the default format. The bcp_columns function must be called before any calls to bcp_colfmt. You must call bcp_colfmt for every column in the operating system file, regardless of whether some of those columns use the default format or are skipped. To skip a column, set the table_column parameter to 0. Parameters dbproc The DBPROCESS structure that is the handle for a particular workstation/SQL Server process. It contains all the information that DB-LIBRARY uses to manage communications and data between the workstation and SQL Server. file_column The column in the operating system file whose format is being specified. The first column is number 1. file_type The datatype of this column in the operating system file. If it is different from the datatype of the corresponding column in the database table (table_column), the conversion is performed automatically. See "dbconvert" for a table of allowable data conversions. If you want to specify the same datatype as in the corresponding column of the database table (table_column), this parameter should be set to 0. file_prefixlen The length of the length prefix for this column in the operating system file. Legal prefix lengths are 1, 2, and 4 bytes. To avoid using a length prefix, this parameter should be set to 0. To let bcp decide whether to use a length prefix, this parameter should be set to -1. In such a case, bcp uses a length prefix (of whatever length is necessary) if the database column length is variable. If more than one means of specifying an operating system file column length is used (such as a length prefix and a maximum column length, or a length prefix and a terminator sequence), bcp looks at all of them and uses the one that results in the shortest amount of data being copied. One valuable use for length prefixes is to simplify the specifying of null data values in an operating system file. For instance, assume you have a 1-byte length prefix for a 4-byte integer column. Ordinarily, the length prefix contains a value of 4 to indicate that a 4-byte value follows. However, if the value of the column is NULL, the length prefix can be set to 0 to indicate that 0 bytes follow for the column. file_collen The maximum length of this column's data in the operating system file, not including the length of any length prefix and/or terminator. Setting file_collen to 0 signifies that the data is NULL. Setting file_collen to -1 indicates that the system should ignore this parameter (that is, there is no default maximum length). For fixed-length datatypes, such as integers, the length of the data is constant, except for the special case of null values. Therefore, for fixed-length datatypes, file_collen must always be -1, except when the data is NULL, in which case file_collen must be 0. For character, text, binary, and image data, file_collen can be -1, 0, or some positive value. If file_collen is -1, the system uses either a length prefix or a terminator sequence to determine the length of the data. (If both are supplied, the system uses the one that results in the shortest amount of data being copied.) If file_collen is -1 and neither a prefix length nor a terminator sequence is specified, the system returns an error message. If file_collen is 0, the system assumes the data is NULL. If file_collen is some positive value, the system uses file_collen as the maximum data length. However, if, in addition to a positive file_collen, a prefix length and/or terminator sequence is provided, the system determines the data length by using the method that results in the shortest amount of data being copied. file_term The terminator sequence to be used for this column. This parameter is mainly useful for character, text, binary, and image datatypes because all other types are of fixed length. To avoid using a terminator, set this parameter to NULL. To set the terminator to NULL, set file_term to "\0". To make the tab character the terminator, set file_term to "\t". To make the newline character the terminator, set file_term to "\n". If more than one means of specifying an operating system file column length is used (such as a terminator and a length prefix, or a terminator and a maximum column length), bcp looks at all of them and uses the one that results in the shortest amount of data being copied. file_termlen The length, in bytes, of the terminator sequence to be used for this column. To avoid using a terminator, set this value to -1. table_column The corresponding column in the database table. If this value is 0, this column will not be copied. The first column is column 1. Returns SUCCEED or FAIL. See Also bcp_batch, bcp_bind, bcp_collen, bcp_colptr, bcp_columns, bcp_control, bcp_done, bcp_exec, bcp_init, bcp_sendrow, dbconvert bcp_collen ──────────────────────────────────────────────────────────────────────────── Function Sets the program variable data length for the current copy IN. Syntax RETCODE bcp_collen(dbproc, varlen, table_column) DBPROCESS *dbproc; DBINT varlen; int table_column; Comments The bcp_collen function allows you to change the program variable data length for a particular column while running a copy IN through calls to bcp_bind. Initially, the program variable data length is determined when bcp_bind is called. If the program variable data length changes between calls to bcp_sendrow and no length prefix or terminator is being used, you can call bcp_collen to reset the length. The next call to bcp_sendrow uses the length you just set. There must be a separate bcp_collen call for every column in the table whose data length you want to modify. Parameters dbproc The DBPROCESS structure that is the handle for a particular workstation/SQL Server process. It contains all the information that DB-LIBRARY uses to manage communications and data between the workstation and SQL Server. varlen The length of the program variable, which does not include the length of the length prefix or terminator. Setting varlen to 0 signifies that the data is NULL. Setting it to -1 signifies that the data is variable length and that the length is determined by the length prefix or terminator. If both a length prefix and a terminator exist, bcp uses the one that results in the shortest amount of data being copied. table_column The column in the SQL Server table to which the data is copied. Column numbers start at 1. Returns SUCCEED or FAIL. See Also bcp_bind, bcp_colptr, bcp_sendrow bcp_colptr ──────────────────────────────────────────────────────────────────────────── Function Sets the program variable data address for the current copy IN. Syntax RETCODE bcp_colptr(dbproc, colptr, table_column) DBPROCESS *dbproc; BYTE *colptr; int table_column; Comments The bcp_colptr function allows you to change the program variable data address for a particular column while running a copy IN through calls to bcp_bind. Initially, the program variable data address is determined when bcp_bind is called. If the program variable data address changes between calls to bcp_sendrow, you can call bcp_colptr to reset the address of the data. The next call to bcp_sendrow uses the data at the address you just set. There must be a separate bcp_colptr call for every column in the table whose data address you want to modify. Parameters dbproc The DBPROCESS structure that is the handle for a particular workstation/SQL Server process. It contains all the information that DB-LIBRARY uses to manage communications and data between the workstation and SQL Server. colptr The address of the program variable. table_column The column in the SQL Server table to which the data is copied. Column numbers start at 1. Returns SUCCEED or FAIL. See Also bcp_bind, bcp_collen, bcp_sendrow bcp_columns ──────────────────────────────────────────────────────────────────────────── Function Sets the total number of columns found in the operating system file for use with bulk copy. Syntax RETCODE bcp_columns(dbproc, file_colcount) DBPROCESS *dbproc; int file_colcount; Comments This function can be called only after bcp_init has been called with a valid filename. You should call this function only if you intend to use an operating system file format that differs from the default. See "bcp_init" for a description of the default operating system file format. After calling bcp_columns, you must call bcp_colfmt file_colcount times because you are defining a completely custom file format. Parameters dbproc The DBPROCESS structure that is the handle for a particular workstation/SQL Server process. It contains all the information that DB-LIBRARY uses to manage communications and data between the workstation and SQL Server. file_colcount The total number of columns in the operating system file. Even if you are preparing to bulk copy data from the operating system file to an SQL Server table and do not intend to copy all columns in the operating system file, you must still set file_colcount to the total number of operating system file columns. Returns SUCCEED or FAIL. See Also bcp_colfmt, bcp_init bcp_control ──────────────────────────────────────────────────────────────────────────── Function Changes various control parameter default settings. Syntax RETCODE bcp_control(dbproc, field, value) DBPROCESS *dbproc; int field; DBINT value; Comments This function sets various control parameters for bulk copy operations, including the number of errors allowed before aborting a bulk copy, the numbers of the first and last rows to copy, and the batch size. These control parameters are only meaningful when copying between an operating system file and an SQL Server table. Control parameter settings have no effect on bcp_bind row transfers. The following program fragment illustrates bcp_control: LOGINREC *login; DBPROCESS *dbproc; DBINT rowsread; /* Install error-handler and message-handler. */ dberrhandle(err_handler); dbmsghandle(msg_handler); /* Open a DBPROCESS. */ login = dblogin(); BCP_SETL(login, TRUE); DBSETLUSER(login, "user"); DBSETLPWD(login, "my_passwd"); DBSETLAPP(login, "example"); dbproc = dbopen(login, "my_server"); /* Initialize bcp. */ if (bcp_init(dbproc, "comdb..address", "address.add", "addr.err", DB_IN) == FAIL) exit(ERREXIT); /* Set the number of rows per batch. */ if (bcp_control(dbproc, BCPBATCH, (DBINT) 1000) == FAIL) { printf("bcp_control failed to set batching behavior.\n"); exit(ERREXIT); } /* Set file column count. */ if (bcp_columns(dbproc, 1) == FAIL) { printf("bcp_columns failed.\n"); exit(ERREXIT); } /* Set the file format. */ if (bcp_colfmt(dbproc, 1, 0, 0, (DBINT)-1, "\n", 1, 1) == FAIL) { printf("bcp_colformat failed.\n"); exit(ERREXIT); } /* Now, execute the bulk copy. */ if (bcp_exec(dbproc, &rowsread) == FAIL) { printf("Incomplete bulk copy. Only %ld row%c copied.\n", rowsread, (rowsread == 1) ? ' ': 's'); exit(ERREXIT); } Parameters dbproc The DBPROCESS structure that is the handle for a particular workstation/SQL Server process. It contains all the information that DB-LIBRARY uses to manage communications and data between the workstation and SQL Server. field One of the following: BCPMAXERRS The number of errors allowed before giving up. The default is 10. Providing a value less than 1 to this field resets it to its default value. BCPFIRST The first row to copy. The default is 1. Providing a value less than 1 to this field resets it to its default value. BCPLAST The last row to copy. The default is to copy all rows. Providing a value less than 1 to this field resets it to its default value. BCPBATCH The number of rows per batch. The default is 0. Providing a value less than 1 to this field resets it to its default value. value The value for the specified field. Returns SUCCEED or FAIL. See Also bcp_batch, bcp_bind, bcp_colfmt, bcp_collen, bcp_colptr, bcp_columns, bcp_done, bcp_exec, bcp_init bcp_done ──────────────────────────────────────────────────────────────────────────── Function Ends a bulk copy from program variables into SQL Server. Syntax DBINT bcp_done(dbproc) DBPROCESS *dbproc; Comments The bcp_done function ends a bulk copy performed with bcp_bind and bcp_sendrow. It should be called after the last call to bcp_sendrow or bcp_moretext. Failure to call bcp_done after you have completed copying in all your data results in unpredictable errors. Parameters dbproc The DBPROCESS structure that is the handle for a particular workstation/SQL Server process. It contains all the information that DB-LIBRARY uses to manage communications and data between the workstation and SQL Server. Returns The number of rows permanently saved since the last call to bcp_batch, or -1 in case of error. See Also bcp_batch, bcp_bind, bcp_moretext, bcp_sendrow bcp_exec ──────────────────────────────────────────────────────────────────────────── Function Executes a bulk copy of data between a database table and an operating system file. Syntax RETCODE bcp_exec(dbproc, rows_copied) DBPROCESS *dbproc; DBINT *rows_copied; Comments This function copies data from an operating system file to a database table or vice versa, depending on the value of the direction parameter in bcp_init. Before calling this function you must call bcp_init with a valid operating system filename. Failure to do so results in an error. The following program fragment illustrates bcp_exec: LOGINREC *login; DBPROCESS *dbproc; DBINT rowsread; /* Install error-handler and message-handler. */ dberrhandle(err_handler); dbmsghandle(msg_handler); /* Open a DBPROCESS. */ login = dblogin(); DBSETLUSER(login, "user"); DBSETLPWD(login, "my_passwd"); DBSETLAPP(login, "example"); BCP_SETL(login, TRUE); dbproc = dbopen(login, "my_server"); /* Initialize bcp. */ if (bcp_init(dbproc, "pubs..authors", "authors.sav", (BYTE *)NULL, DB_OUT) == FAIL) exit(ERREXIT); /* Now, execute the bulk copy. */ if (bcp_exec(dbproc, &rowsread) == FAIL) printf("Incomplete bulk copy. Only %ld row%s copied.\n", rowsread, (rowsread == 1) ? "": "s"); Parameters dbproc The DBPROCESS structure that is the handle for a particular workstation/SQL Server process. It contains all the information that DB-LIBRARY uses to manage communications and data between the workstation and SQL Server. rows_copied A pointer to a DBINT. The bcp_exec function fills this DBINT with the number of rows successfully copied. If set to NULL, this parameter is not filled in by bcp_exec. Returns SUCCEED or FAIL. The bcp_exec function returns SUCCEED if all rows are copied. If a partial or complete failure occurs, bcp_exec returns FAIL. Check the rows_copied parameter for the number of rows successfully copied. See Also bcp_batch, bcp_bind, bcp_colfmt, bcp_collen, bcp_colptr, bcp_columns, bcp_control, bcp_done, bcp_init, bcp_sendrow bcp_init ──────────────────────────────────────────────────────────────────────────── Function Initializes bulk copy. Syntax RETCODE bcp_init(dbproc, tblname, hfile, errfile, direction) DBPROCESS *dbproc; char *tblname; char *hfile; char *errfile; int direction; Comments The bcp_init function performs the necessary initializations for a bulk copy of data between the workstation and SQL Server. It sets the default operating system file data formats and examines the structure of the database table. If an operating system file is being used (see the description of the hfile parameter), the default data formats are as follows: ■ The order, type, length, and number of the columns in the operating system file are assumed to be identical to the order, type, and number of the columns in the database table. ■ If a given database column's data is fixed length, then the operating system file's data column will also be fixed length. If a given database column's data is variable length or can contain NULL values, the operating system file's data column is prefixed by a 4-byte length value for SQLTEXT and SQLIMAGE datatypes and a 1-byte length value for all other types. ■ There are no terminators of any kind between operating system file columns. Any of these defaults can be overridden by calling bcp_columns and bcp_colfmt. To use the bulk copy functions to copy data to a database table, you must do the following: ■ Call BCP_SETL to make the DBPROCESS structure usable for bulk copy purposes: login = dblogin(); BCP_SETL(login, TRUE); ■ If the table has no indexes, set the database option select into/bulkcopy to "true": sp_dboption 'mydb', 'select into/bulkcopy', 'true' If no operating system file is being used, it's necessary to call bcp_bind to specify the format and location in memory of each column's data value. The bcp_init function must be called before any other bulk copy functions. Failure to do so results in an error. Parameters dbproc The DBPROCESS structure that is the handle for a particular workstation/SQL Server process. It contains all the information that DB-LIBRARY uses to manage communications and data between the workstation and SQL Server. tblname The name of the database table to be copied in or out. This name can also include the database name or the Database Owner name. For example, "pubs.gracie.titles", "pubs..titles", "gracie.titles", and "titles" are all legal table names. hfile The name of the operating system file to be copied in or out. If no operating system file is involved (the situation when data is being copied directly from variables), hfile should be NULL. errfile The name of the error file to be used. This error file will be filled with progress messages, error messages, and copies of any rows that, for any reason, could not be copied from an operating system file to an SQL Server table. If NULL is passed as errfile, no error file is used. direction The direction of the copy. It must be one of two values: DB_IN or DB_OUT. DB_IN indicates a copy from the operating system file into the database table, while DB_OUT indicates a copy from the database table into the operating system file. It is illegal to request a bulk copy from the database table (DB_OUT) without supplying an operating system filename. Returns SUCCEED or FAIL. See Also bcp_batch, bcp_bind, bcp_colfmt, bcp_collen, bcp_colptr, bcp_columns, bcp_control, bcp_done, bcp_exec, bcp_sendrow bcp_moretext ──────────────────────────────────────────────────────────────────────────── Function Sends part of a text or image value to SQL Server. Syntax RETCODE bcp_moretext(dbproc, size, text) DBPROCESS *dbproc; DBINT size; BYTE *text; Comments This function is used in conjunction with bcp_bind and bcp_sendrow to send a large SQLTEXT or SQLIMAGE value to SQL Server in the form of a number of smaller chunks. This is particularly useful with operating systems unable to allocate extremely long data buffers. If bcp_bind is called with a type parameter of SQLTEXT or SQLIMAGE and a non-NULL varaddr parameter, bcp_sendrow sends the entire text or image data value, just as it does for all other datatypes. If, however, bcp_bind has a NULL varaddr parameter, bcp_sendrow returns control to the application immediately after all nontext or image columns are sent to SQL Server. The application can then call bcp_moretext repeatedly to send the text and image columns to SQL Server, a chunk at a time. Here's an example that illustrates how to use bcp_moretext with bcp_bind and bcp_sendrow: LOGINREC *login; DBPROCESS *dbproc; DBINT id = 5; char *part1 = "This text value isn't very long,"; char *part2 = " but it's broken up into three parts"; char *part3 = " anyhow."; /* Install error handler and message handler. */ dberrhandle(err_handler); dbmsghandle(msg_handler); /* Open a DBPROCESS */ login = dblogin(); BCP_SETL(login, TRUE); DBSETLUSER(login, "user"); DBSETLPWD(login, "my_passwd"); DBSETLAPP(login, "example"); dbproc = dbopen(login, "my_server"); /* Initialize bcp. */ if (bcp_init(dbproc, "comdb..articles", (BYTE *)NULL, (BYTE *)NULL, DB_IN) == FAIL) exit(ERREXIT); /* Bind program variables to table columns. */ if (bcp_bind(dbproc, (BYTE *)&id, 0, (DBINT)-1, (BYTE *)NULL, 0, SQLINT4, 1) == FAIL) { fprintf(stderr, "bcp_bind, column 1, failed.\n"); exit(ERREXIT); } if (bcp_bind (dbproc, (BYTE *)NULL, 0, (DBINT)(strlen(part1) + strlen(part2) + strlen(part3)), (BYTE *)NULL, 0, SQLTEXT, 2) == FAIL) { fprintf(stderr, "bcp_bind, column 2, failed.\n"); exit(ERREXIT); } /* Now send this row, with the text value broken into three chunks. */ if (bcp_sendrow(dbproc) == FAIL) exit(ERREXIT); if (bcp_moretext(dbproc, (DBINT)strlen(part1), part1) == FAIL) exit(ERREXIT); if (bcp_moretext(dbproc, (DBINT)strlen(part2), part2) == FAIL) exit(ERREXIT); if (bcp_moretext(dbproc, (DBINT)strlen(part3), part3) == FAIL) exit(ERREXIT); /* We're all done. */ bcp_done(dbproc); dbclose(dbproc); If you use bcp_moretext to send one text or image column in the row, you must also use it to send all other text and image columns in the row. If the row contains more than one text or image column, bcp_moretext first sends its data to the lowest numbered (that is, leftmost) text or image column, followed by the next lowest numbered column, and so on. An application normally calls bcp_sendrow and bcp_moretext within loops to send a number of rows of data. Here's an outline of how to do this for a table containing two text columns: while (there are still rows to send) { bcp_sendrow(...); for (all the data in the first text column) bcp_moretext(...); for (all the data in the second text column) bcp_moretext(...); } Parameters dbproc The DBPROCESS structure that is the handle for a particular workstation/SQL Server process. It contains all the information that DB-LIBRARY uses to manage communications and data between the workstation and SQL Server. size The size of this particular part of the text or image value being sent to SQL Server. It is an error to send more text or image bytes to SQL Server than were specified in the call to bcp_bind or bcp_collen. text A pointer to the text or image portion to be written. Returns SUCCEED or FAIL. See Also bcp_bind, bcp_collen, bcp_sendrow, dbwritetext bcp_sendrow ──────────────────────────────────────────────────────────────────────────── Function Sends a row of data from program variables to SQL Server. Syntax RETCODE bcp_sendrow(dbproc) DBPROCESS *dbproc; Comments The bcp_sendrow function builds a row from program variables and sends it to SQL Server. Before calling bcp_sendrow, you must make calls to bcp_bind to specify the program variables to be used. If bcp_bind is called with a type parameter of SQLTEXT or SQLIMAGE and a non-NULL varaddr parameter, bcp_sendrow sends the entire text or image data value, just as it does for all other datatypes. If, however, bcp_bind has a NULL varaddr parameter, bcp_sendrow returns control to the application immediately after all nontext or image columns are sent to SQL Server. The application can then call bcp_moretext repeatedly to send the text and image columns to SQL Server, a chunk at a time. For an example, see "bcp_moretext." After the last call to bcp_sendrow, you must call bcp_done to ensure proper internal cleanup. When bcp_sendrow is used to bulk copy rows from program variables into SQL Server tables, rows are permanently saved in SQL Server only when the user calls bcp_batch or bcp_done. The user can choose to call bcp_batch once every n rows or when there is a lull between periods of incoming data (as in a telemetry application). Of course, the user can choose some other criteria or decide not to call bcp_batch at all. If bcp_batch is never called, the rows are permanently saved in SQL Server when bcp_done is called. Parameters dbproc The DBPROCESS structure that is the handle for a particular workstation/SQL Server process. It contains all the information that DB-LIBRARY uses to manage communications and data between the workstation and SQL Server. Returns SUCCEED or FAIL. See Also bcp, bcp_batch, bcp_bind, bcp_colfmt, bcp_collen, bcp_colptr, bcp_columns, bcp_control, bcp_done, bcp_exec, bcp_init, bcp_moretext BCP_SETL ──────────────────────────────────────────────────────────────────────────── Function Sets the LOGINREC for bulk copy operations. Syntax RETCODE BCP_SETL(loginrec, enable) LOGINREC *loginrec; DBBOOL enable; Comments This macro sets a field in the LOGINREC structure that tells SQL Server that the DBPROCESS connection can be used for bulk copy operations. For it to have any effect, it must be called before dbopen, the function that actually allocates the DBPROCESS structure. Applications that allow users to make ad hoc queries may want to avoid calling this macro (or call it with the enable parameter set to FALSE) to keep users from initiating a bulk copy sequence with SQL statements. Once a bulk copy sequence has begun, it cannot be stopped with an ordinary SQL statement. Parameters loginrec A pointer to a LOGINREC structure, which is passed as a parameter to dbopen. You can get one by calling dblogin. enable A boolean value (TRUE or FALSE) that specifies whether or not to enable bulk copy operations for the resulting DBPROCESS. By default, DBPROCESS structures are not enabled for bulk copy operations. Returns SUCCEED or FAIL. See Also bcp_init, dblogin, dbopen, DBSETLAPP, DBSETLHOST, DBSETLPWD, DBSETLUSER Chapter 5 Two-Phase Commit Special Library ──────────────────────────────────────────────────────────────────────────── Programming Distributed Transactions The two-phase commit service allows an application to coordinate updates among multiple SQL Servers. This initial implementation of distributed transactions treats separate transactions (which may be on separate SQL Servers) as if they were a single transaction. The service uses one SQL Server, the "commit server," as a record keeper that helps the application determine whether to commit or to roll back transactions in case of failure. Thus, the two-phase commit guarantees that either all the databases on the participating servers are updated or that none of them are. A distributed transaction is performed by submitting TRANSACT-SQL statements to SQL Servers through DB-LIBRARY functions and macros. An application program opens a session with each server, executes the update commands, and then prepares to commit the transaction. Through DB-LIBRARY, the application executes the following to each participating server: ■ A BEGIN TRANSACTION statement with identifying information on the application, the transaction, and the commit server. ■ The TRANSACT-SQL update statements. ■ A PREPARE TRANSACTION statement that indicates that the updates have been performed and that the server is prepared to commit. This statement cannot be used outside of the context of DB-LIBRARY. After the updates have been performed on all servers participating in the distributed transaction, the two-phase commit begins. In the first phase, all servers agree that they are ready to commit. In the second phase, they all commit─a COMMIT TRANSACTION is executed to all servers. The application then tells the commit service that the transaction is complete, and the connections are closed. If an error occurs between phase one and two, all servers coordinate with the commit service to see if the transaction should be committed or aborted. The Commit Service and the Application Program The role of the commit service is to be a single place of record that helps the application decide whether the transaction should be committed or aborted. If all SQL Servers are prepared to commit, the application notifies the commit service to mark the transaction as committed. Once this happens, the transaction is committed despite any failures that might subsequently happen. If any SQL Server or the application program fails before the PREPARE TRANSACTION statement, the SQL Server rolls back the transaction. If any SQL Server or the application program fails after the PREPARE but before the COMMIT, SQL Server communicates with the server functioning as the commit service and asks it whether to roll back or commit. The role of the application program is to deliver the TRANSACT-SQL statements to the SQL Servers in the proper order, using the proper DB-LIBRARY functions and macros. The role of the commit service is to provide a single place where the commit/rollback status is maintained. The SQL Servers communicate with the commit service only if a failure happens during the two-phase commit. The commit service needs its own DBPROCESS, separate from the DBPROCESS structures used for the distributed transaction, to perform its record-keeping. Note, however, that the server handling the commit service can also be one of the servers participating in the transaction, as long as the commit service has its own DBPROCESS. In fact, all the servers involved in the transaction can be one and the same. The Probe Process If any server must recover the transaction, it initiates a process, probe, that determines the last known status of the transaction. After it returns the status of that transaction to the commit service, the probe process dies. The probe process makes use of the same status-checking function, stat_xact, that the commit service uses to check on the progress of a distributed transaction. The Two-Phase Commit Functions The functions that make up the two-phase commit service are as follows: open_commit Opens a connection with the commit service. The function is given the login ID of the user initiating the session and the name of the commit service. It returns a pointer to a DBPROCESS structure used in subsequent commit service calls. start_xact Records the start of a distributed transaction and stores initial information about the transaction (DBPROCESS ID, application name, transaction name, and number of sites participating) in a lookup table on the commit server. It returns the commid identifying number for the transaction. build_xact_string Builds a name string for use by each participating SQL Server for its BEGIN TRANSACTION and PREPARE TRANSACTION statements. This string encodes the application's transaction name, the commit service name, and the commid. commit_xact Tells the commit service to commit the transaction. abort_xact Tells the commit service to abort the transaction. remove_xact Decreases the number of servers still participating in the transaction. close_commit Closes the connection with the commit service. Two additional functions are used for ongoing status reports: scan_xact Returns the status of a single transaction or all distributed transactions. stat_xact Returns the completion status of a distributed transaction. During the course of a session, the diagnostic functions, scan_xact and stat_xact, are used to check that the commit service carried out the request. The scan_xact function uses the commit service lookup table, spt_committab, which holds the values of the following: ■ Transaction ID ■ Time the transaction started ■ Last time the row was updated ■ Number of servers initially involved in the transaction ■ Number of servers that still have not completed ■ Status ("a"-abort, "c"-commit, "b"-begin) ■ Application name ■ Transaction name The two-phase commit functions call internal stored procedures (for example, sp_start_xact) that are created in each server's master database. The instos2.sql script creates the commit service lookup table and stored procedures in each server's master database for use whenever that server becomes a commit server. Specifying the Commit Server The name of the commit server is used as a parameter in a call to the open_commit function. The commit server name must be the same on all machines participating in the transaction. Two-Phase Commit Example Program The example program illustrates the two-phase commit service. An online version of the example source code, twophase.c, is included in the DB-LIBRARY sample code directory. This directory is described in Chapter 3, "Example Programs." To compile and link the example, follow the instructions given in Chapter 3, "Example Programs," for compiling and linking programs under your operating system. This example assumes that you have two servers running, "sql_svr1" and "sql_svr2". If you don't have a second server available, contact your System Administrator. If your servers are named differently, you will need to replace "sql_svr1" and "sql_svr2" in the source code with the actual names of your servers. /* Demo of Two-Phase Commit Service ** ** This example uses the two-phase commit service ** to perform a simultaneous update on two servers. ** In this example, one of the servers participating ** in the distributed transaction also functions as ** the commit service. ** ** In this particular example, the same update is ** performed on both servers. You can, however, use ** the commit server to perform completely different ** updates on each server. ** */ #include <stdio.h> #include <sqlfront.h> #include <sqldb.h> char cmdbuf[256]; char xact_string[128]; /* Forward declarations of the error handler and message handler. */ int err_handler(); int msg_handler(); main(argv,argc) int argc; char *argv[]; { DBPROCESS *dbproc_server1; DBPROCESS *dbproc_server2; DBPROCESS *dbproc_commit; LOGINREC *login; DBINT commid; RETCODE ret_server1; RETCODE ret_server2; dberrhandle(err_handler); dbmsghandle(msg_handler); printf("Demo of Two Phase Commit\n"); /* Open connections with the servers and the commit service. */ login = dblogin(); DBSETLUSER(login, "user"); DBSETLPWD(login, "my_passwd"); DBSETLAPP(login, "example"); dbproc_server1 = dbopen (login, "sql_svr1"); dbproc_server2 = dbopen (login, "sql_svr2"); dbproc_commit = open_commit (login, "sql_svr1"); if (dbproc_server1 == NULL || dbproc_server2 == NULL || dbproc_commit == NULL) { printf (" Connections failed!\n"); exit (ERREXIT); } /* Use the "pubs" database. */ dbuse(dbproc_server1, "pubs"); dbuse(dbproc_server2, "pubs"); /* Start the distributed transaction on the commit service. */ commid = start_xact(dbproc_commit, "demo", "test", 2); /* Build the transaction name. */ build_xact_string ("test", "SERVICE", commid, xact_string); /* Build the first command buffer. */ sprintf(cmdbuf, "BEGIN TRANSACTION %s", xact_string); /* Begin the transactions on the different servers. */ dbcmd(dbproc_server1, cmdbuf); dbsqlexec(dbproc_server1); dbcmd(dbproc_server2, cmdbuf); dbsqlexec(dbproc_server2); /* Do various updates. */ sprintf(cmdbuf, " update titles set price = $1.50 where"); strcat(cmdbuf, " title_id = 'BU1032'"); dbcmd(dbproc_server1, cmdbuf); ret_server1 = dbsqlexec(dbproc_server1); dbcmd(dbproc_server2, cmdbuf); ret_server2 =dbsqlexec(dbproc_server2); if (ret_server1 == FAIL || ret_server2 == FAIL) { /* Some part of the transaction failed. */ printf(" Transaction aborted -- dbsqlexec failed\n"); abortall(dbproc_server1, dbproc_server2, dbproc_commit, commid); } /* Find out if all servers can commit the transaction. */ sprintf(cmdbuf, "PREPARE TRANSACTION"); dbcmd(dbproc_server1, cmdbuf); dbcmd(dbproc_server2, cmdbuf); ret_server1 = dbsqlexec(dbproc_server1); ret_server2 = dbsqlexec(dbproc_server2); if (ret_server1 == FAIL || ret_server2 == FAIL) { /* One or both of the servers failed to prepare. */ printf(" Transaction aborted -- PREPARE failed\n"); abortall(dbproc_server1, dbproc_server2, dbproc_commit, commid); } /* Commit the transaction. */ if (commit_xact(dbproc_commit, commid) == FAIL) { /* The commit server failed to record the commit. */ printf( " Transaction aborted -- commit_xact failed\n"); abortall(dbproc_server1, dbproc_server2, dbproc_commit, commid); exit(ERREXIT); } /* The transaction has successfully committed. Inform the servers. */ sprintf(cmdbuf, "COMMIT TRANSACTION"); dbcmd(dbproc_server1, cmdbuf); if (dbsqlexec(dbproc_server1) != FAIL) remove_xact(dbproc_commit, commid, 1); dbcmd(dbproc_server2, cmdbuf); if (dbsqlexec(dbproc_server2) != FAIL) remove_xact(dbproc_commit, commid, 1); /* Close the connection to the commit server. */ close_commit(dbproc_commit); printf( "We made it!\n"); dbexit(); exit(STDEXIT); } /* Function to abort the distributed transaction. */ abortall( dbproc_server1, dbproc_server2, dbproc_commit, commid ) DBPROCESS *dbproc_server1; DBPROCESS *dbproc_server2; DBPROCESS *dbproc_commit; DBINT commid; { /* Some part of the transaction failed. */ /* Inform the commit server of the failure. */ abort_xact(dbproc_commit, commid); /* Roll back the transactions on the different servers. */ sprintf(cmdbuf, "ROLLBACK TRANSACTION"); dbcmd(dbproc_server1, cmdbuf); if (dbsqlexec(dbproc_server1) != FAIL) remove_xact(dbproc_commit, commid, 1); dbcmd(dbproc_server2, cmdbuf); if (dbsqlexec(dbproc_server2) != FAIL) remove_xact(dbproc_commit, commid, 1); dbexit(); exit(ERREXIT); } /* Message and error-handling functions. */ int msg_handler(dbproc,msgno,msgstate, severity, msgtext) DBPROCESS *dbproc; DBINT msgno; int msgstate; int severity; char *msgtext; { /* Msg 5701 is just a USE DATABASE message, so skip it. */ if (msgno == 5701) return (0); /* Print any severity 0 message as is, without extra stuff. */ if (severity == 0) { printf ("%s\n",msgtext); return (0); } printf("SQL Server message %ld, severity %d:\n\t%s\n", msgno, severity, msgtext); if (severity >= 16) { printf("Program Terminated! Fatal SQL Server error.\n"); exit(ERREXIT); } return (0); } int err_handler(dbproc, severity, dberr, oserr, dberrstr, oserrstr) DBPROCESS *dbproc; int severity; int dberr; int oserr; char *dberrstr; char *oserrstr; { if ((dbproc == NULL) || (DBDEAD(dbproc))) return (INT_EXIT); else { printf ("DB-LIBRARY error: \n\t%s\n", dberrstr); if (oserr != DBNOERR) printf ("Operating system error:\n\t%s\n", oserrstr); } return (INT_CANCEL); } abort_xact ──────────────────────────────────────────────────────────────────────────── Function Marks a distributed transaction as being aborted. Syntax RETCODE abort_xact(connect, commid) DBPROCESS *connect; DBINT commid; Comments This function informs the commit service that the status of a distributed transaction should be changed from "begin" to "abort." Parameters connect The DBPROCESS used to communicate with the commit service. commid The number used by the commit service to identify the transaction. Returns SUCCEED or FAIL. See Also commit_xact, remove_xact, scan_xact, start_xact, stat_xact build_xact_string ──────────────────────────────────────────────────────────────────────────── Function Builds a name for a distributed transaction. Syntax void build_xact_string(xact_name, service_name, commid, result) char *xact_name; char *service_name; DBINT commid; char *result; Comments This function builds a name string for use in the BEGIN TRANSACTION and PREPARE TRANSACTION of the SQL Server transaction. If SQL Server has to recover the transaction, it uses information encoded in the name to determine which commit service to contact and which transaction in that service to inquire about. The application should execute a TRANSACT-SQL BEGIN TRANSACTION statement using the string built by build_xact_string. Parameters xact_name The application or user name for the transaction. This name is encoded in the name string but is not used by the commit service or SQL Server. It serves to identify the transaction for debugging purposes. service_name The name SQL Server will use to contact the commit service, should it be necessary to recover the transaction. If service_name is NULL, the name DSCOMMIT is used. commid The number used by the commit service to identify the transaction. The commid is the number returned by the call to start_xact. result The address of the buffer where the string should be built. The space must be allocated by the caller. Returns None. See Also commit_xact, start_xact close_commit ──────────────────────────────────────────────────────────────────────────── Function Ends a connection with the commit service. Syntax void close_commit(connect) DBPROCESS *connect; Comments This function calls dbclose to end a connection with the commit service. When the application is through with the commit service, a call to close_commit should be made to free resources. Parameters connect The pointer to the DBPROCESS structure that was originally returned by open_commit. Returns None. See Also dbclose, open_commit commit_xact ──────────────────────────────────────────────────────────────────────────── Function Marks a distributed transaction as being committed. Syntax RETCODE commit_xact(connect, commid) DBPROCESS *connect; DBINT commid; Comments This function informs the commit service that the status of a distributed transaction should be changed from "begin" to "commit." Parameters connect The DBPROCESS used to communicate with the commit service. commid The number used by the commit service to identify the transaction. Returns SUCCEED or FAIL. See Also abort_xact, remove_xact, scan_xact, start_xact, stat_xact open_commit ──────────────────────────────────────────────────────────────────────────── Function Establishes a connection with the commit service. Syntax DBPROCESS *open_commit(login, servername) LOGINREC *login; char *servername; Comments This function calls dbopen to establish a connection with the commit service. A call to open_commit must precede any calls to other commit service functions, such as start_xact, commit_xact, abort_xact, remove_xact, and scan_xact. A session with the commit service is closed by calling close_commit. Parameters login A LOGINREC containing information about the user initiating the session, such as login name, password, or options desired. The LOGINREC must have been obtained from a prior call to the DB-LIBRARY function dblogin. The caller may want to initialize fields in the LOGINREC. See "dblogin" for more details. servername The name of the commit service, for example, "my_server". Returns A pointer to a DBPROCESS structure used in subsequent commit service calls or other DB-LIBRARY calls, or NULL if the open failed. See Also abort_xact, close_commit, commit_xact, dblogin, dbopen, remove_xact, scan_xact, start_xact remove_xact ──────────────────────────────────────────────────────────────────────────── Function Decrements the count of sites still active in the distributed transaction. Syntax RETCODE remove_xact(connect, commid, n) DBPROCESS *connect; DBINT commid; int n; Comments The commit service keeps a count of the number of sites participating in a distributed transaction. This function informs the commit service that one or more sites has done a local commit or abort on the transaction and hence is no longer participating. The commit service removes the sites from the transaction by decrementing the count of sites. The transaction record is deleted entirely if the count drops to zero. Parameters connect The DBPROCESS used to communicate with the commit service. commid The number used by the commit service to identify the transaction. n The number of sites to remove from the transaction. Returns SUCCEED or FAIL. See Also abort_xact, commit_xact, scan_xact, start_xact, stat_xact scan_xact ──────────────────────────────────────────────────────────────────────────── Function Prints the commit service record for distributed transactions. Syntax RETCODE scan_xact(connect, commid) DBPROCESS *connect; DBINT commid; Comments This function displays the commit service record for a specific distributed transaction or for all distributed transactions known to the commit service or the default output device. This function is not supported under Microsoft Windows. Parameters connect The DBPROCESS used to communicate with the commit service. commid The number used by the commit service to identify the transaction. If commid is -1, all commit service records are displayed. Returns SUCCEED or FAIL. See Also abort_xact, commit_xact, remove_xact, start_xact, stat_xact start_xact ──────────────────────────────────────────────────────────────────────────── Function Starts a distributed transaction using the commit service. Syntax DBINT start_xact(connect, application_name, xact_name, site_count) DBPROCESS *connect; char *application_name; char *xact_name; int site_count; Comments This function records the start of a distributed transaction with the commit service. A record is placed in the commit service containing the commid, which is a number that the caller subsequently uses to identify the transaction to the commit service. Parameters connect The DBPROCESS used to communicate with the commit service. application_name The name of the application. This name can be anything the application chooses. It appears in the table maintained by the commit service but is not used by the commit service or the SQL Server recovery system. xact_name The name of the transaction. This name appears in the table maintained by the commit service and must be supplied as part of the transaction name string built by build_xact_string. The name cannot contain a period (.) or a colon (:). site_count The number of sites participating in the transaction. Returns An integer called commid. This number is used to identify the transaction in subsequent calls to the commit service. In case of error, this function returns 0. See Also abort_xact, build_xact_string, commit_xact, remove_xact, scan_xact, stat_xact stat_xact ──────────────────────────────────────────────────────────────────────────── Function Returns the current status of a distributed transaction. Syntax int stat_xact(connect, commid) DBPROCESS *connect; DBINT commid; Comments This function returns the transaction status for the specified distributed transaction. Parameters connect The DBPROCESS used to communicate with the commit service. commid The number used by the commit service to identify the transaction. If commid is -1, all commit service records are displayed. Returns A character code: "a" (abort), "b" (begin), "c" (commit), "u" (unknown), or -1 (request failed). See Also abort_xact, commit_xact, remove_xact, scan_xact, start_xact Appendix A Functions and Macros ──────────────────────────────────────────────────────────────────────────── DB-LIBRARY Functions and Macros ──────────────────────────────────────────────────────────────────────────── dbadata Returns a pointer to the data for a compute column. dbadlen Returns the actual length of the data for a compute column. dbaltbind Binds a compute result column to a program variable. dbaltcolid Returns the operand column ID for a compute column. dbaltlen Returns the maximum length of the data for a compute column. dbaltop Returns the type of aggregate operator for a compute column. dbalttype Returns the datatype for a compute column. dbbind Binds a regular result column to a program variable. dbbylist Returns the bylist for a compute row. dbcancel Cancels the current command batch. dbcanquery Cancels any rows pending from the most recently executed query. dbchange Determines whether a command batch has changed the current database. dbclose Closes and frees a single DBPROCESS structure. dbclrbuf Drops rows from the row buffer. dbclropt Clears an option set by dbsetopt. dbcmd Adds text to the DBPROCESS command buffer. DBCMDROW Determines whether the current command can return rows. dbcolbrowse Determines whether the source of a result column can be updated with the DB-LIBRARY browse-mode facilities. dbcollen Returns the maximum length, in bytes, of the data for a column. dbcolname Returns the name of a particular result column. dbcolsource Returns a pointer to the name of the database column from which the specified result column was derived. dbcoltype Returns the datatype for a column. dbconvert Converts data from one datatype to another. DBCOUNT Returns the number of rows affected by a TRANSACT-SQL statement. DBCURCMD Returns the number of the current command. DBCURROW Returns the number of the row currently being read. dbdata Returns a pointer to the data for a result column. dbdataready Determines whether database command processing has completed. dbdatlen Returns the actual length, in bytes, of the data for a column. DBDEAD Determines whether a particular DBPROCESS is dead. dberrhandle Supplies a user function to handle DB-LIBRARY errors. dbexit Closes and frees all DBPROCESS structures created as a result of your program. dbfcmd Adds text to the DBPROCESS command buffer using C run-time library sprintf-type formatting. DBFIRSTROW Returns the number of the first row in the row buffer. dbfreebuf Sets the command buffer in a DBPROCESS structure to NULL. dbfreelogin Frees a login record. dbfreequal Frees memory allocated by the dbqual function. dbgetchar Returns a pointer to a character in the command buffer. dbgetmaxprocs Determines the current maximum number of simultaneously open DBPROCESS structures. dbgetoff Checks for the existence of TRANSACT-SQL constructs in the command buffer. dbgetrow Reads the specified row in the row buffer. DBGETTIME Returns the number of seconds that DB-LIBRARY will wait for an SQL Server response to a TRANSACT-SQL statement. dbinit Initializes the DB-LIBRARY application under Windows. DBISAVAIL Determines whether a DBPROCESS is available for general use. dbisopt Checks the status of an SQL Server or DB-LIBRARY option. DBLASTROW Returns the number of the last row in the row buffer. DBLOCKLIB Locks DB-LIBRARY segments under Windows. dblogin Allocates a login record for use in dbopen. DBMORECMDS Indicates whether there are more commands to be processed. dbmoretext Sends part of a text or image value to SQL Server. dbmsghandle Supplies a user function to handle SQL Server messages. dbname Returns the name of the current database. dbnextrow Reads in the next row. dbnumalts Returns the number of columns in a particular compute row. dbnumcols Determines the number of columns for the current set of results. dbnumcompute Returns the number of COMPUTE clauses in the current set of results. DBNUMORDERS Returns the number of columns specified in a TRANSACT-SQL SELECT statement's ORDER BY clause. dbopen Allocates and initializes a DBPROCESS structure. dbordercol Returns the ID of a column appearing in the most recently executed query's ORDER BY clause. dbprhead Prints the column headings for rows returned from SQL Server. dbprrow Prints all rows returned from SQL Server. dbprtype Converts an SQL Server type token to a readable string. dbqual Returns a pointer to a WHERE clause suitable for use in updating the current row in a table that can be browsed. DBRBUF Determines whether database command processing has completed. dbreadpage Reads in a page of binary data from SQL Server. dbresults Sets up the results of the next query. DBROWS Indicates whether the current statement actually returned rows. DBROWTYPE Returns the type of the current row. dbsetavail Marks a DBPROCESS as being available for general use. DBSETLAPP Sets the application name in the LOGINREC structure. DBSETLHOST Sets the workstation name in the LOGINREC structure. dbsetlogintime Sets the number of seconds that DB-LIBRARY waits for an SQL Server response to a request for a DBPROCESS connection. DBSETLPWD Sets the user SQL Server password in the LOGINREC structure. DBSETLUSER Sets the username in the LOGINREC structure. dbsetmaxprocs Sets the maximum number of simultaneously open DBPROCESS structures. dbsetnull Defines substitution values to be used when binding null values. dbsetopt Sets an SQL Server or DB-LIBRARY option. dbsettime Sets the number of seconds that DB-LIBRARY will wait for an SQL Server response to a TRANSACT-SQL statement. dbsqlexec Sends a command batch to SQL Server. dbsqlok Verifies the correctness of a command batch. dbsqlsend Sends a command batch to SQL Server and does not wait for a response. dbstrcpy Copies a portion of the command buffer. dbstrlen Returns the length, in characters, of the command buffer. dbtabbrowse Determines whether the specified table can be updated with the DB-LIBRARY browse-mode facilities. dbtabcount Returns the number of tables involved in the current SELECT statement. dbtabname Returns the name of a table based on its number. dbtabsource Returns the name and number of the table from which a particular result column was derived. dbtsnewlen Returns the length of the new value of the timestamp column after a browse-mode update. dbtsnewval Returns the new value of the timestamp column after a browse mode update. dbtsput Puts the new value of the timestamp column into the given table's current row in the DBPROCESS. dbtxptr Returns the value of the text pointer for a column in the current row. dbtxtimestamp Returns the value of the text timestamp for a column in the current row. dbtxtsnewval Returns the new value of a text timestamp after a call to dbwritetext. dbtxtsput Puts the new value of a text timestamp into the specified column of the current row in the DBPROCESS. DBUNLOCKLIB Unlocks DB-LIBRARY segments under Windows. dbuse Uses a particular database. dbwillconvert Determines whether a specific data conversion is available within DB-LIBRARY. dbwinexit De-registers an application from the DB-LIBRARY in the Windows environment. dbwritepage Writes a page of binary data to SQL Server. dbwritetext Sends a text or image value to SQL Server. ──────────────────────────────────────────────────────────────────────────── Bulk Copy Functions ──────────────────────────────────────────────────────────────────────────── bcp_batch Saves any preceding rows in SQL Server. bcp_bind Binds data from a program variable to an SQL Server table. bcp_colfmt Specifies the format of an operating system file for bulk copy purposes. bcp_collen Sets the program variable data length for the current copy IN. bcp_colptr Sets the program variable data address for the current copy IN. bcp_columns Sets the total number of columns found in the operating system file. bcp_control Changes various control parameter default settings. bcp_done Ends a bulk copy from program variables into SQL Server. bcp_exec Executes a bulk copy of data between a database table and an operating system file. bcp_init Initializes bulk copy. bcp_moretext Sends part of a text or image value to SQL Server. bcp_sendrow Sends a row of data from program variables to SQL Server. BCP_SETL Sets the LOGINREC for bulk copy operations. ──────────────────────────────────────────────────────────────────────────── Two-Phase Commit Functions ──────────────────────────────────────────────────────────────────────────── abort_xact Marks a distributed transaction as being aborted. build_xact_string Builds a name for a distributed transaction. close_commit Ends a connection with the commit service. commit_xact Marks a distributed transaction as being committed. open_commit Establishes a connection with the commit service. remove_xact Decrements the count of sites still active in the distributed transaction. scan_xact Prints the commit service record for distributed transactions. start_xact Starts a distributed transaction using the commit service. stat_xact Returns the current status of a distributed transaction. ──────────────────────────────────────────────────────────────────────────── Appendix B DB-LIBRARY Options ──────────────────────────────────────────────────────────────────────────── Introduction This appendix describes the DB-LIBRARY options. To use these options, you must include the sqlfront.h and sqldb.h header files in your program. Options The following options are available (all options are off by default): ──────────────────────────────────────────────────────────────────────────── DBARITHABORT When an arithmetic exception occurs during execution, aborts the query. DBARITHIGNORE When an arithmetic exception occurs during query execution, substitutes null values for selected or updated values. No warning message is returned. If neither DBARITHABORT nor DBARITHIGNORE is set, SQL Server substitutes null values and prints a warning message after the query has been executed. DBBUFFER Buffers result rows. It is required if the dbgetrow function is used. This option is handled locally by DB-LIBRARY and is not an SQL Server option. When the option is set, you supply a parameter for the number of rows you want buffered. If you use a value less than 0 as the number of rows to buffer, the buffer will be set to the default size (currently 100). Buffering of only one row is not allowed. (See "dbnextrow," "dbgetrow," and "dbclrbuf" for more information about buffered rows.) DBNOAUTOFREE Causes the command buffer to be cleared only by a call to dbfreebuf. When DBNOAUTOFREE is not set, after a call to dbsqlexec or dbsqlsend, the first call to either dbcmd or dbfcmd automatically clears the command buffer before the new text is entered. This is not an SQL Server option. DBNOCOUNT Stops sending back information about the number of rows affected by each SQL statement. The application can otherwise obtain this information by calling DBCOUNT. DBNOEXEC Processes the query through the compile step but does not execute it. This could be used in conjunction with DBSHOWPLAN. DBOFFSET Indicates where SQL Server should return offsets to certain constructs in the query. This option takes a parameter that specifies the particular construct. The valid parameters for this option are SELECT, FROM, TABLE, ORDER, COMPUTE, STATEMENT, PROCEDURE, EXECUTE, and PARAM. (Note that PARAM refers to parameters of stored procedures.) Calls to functions such as dbsetopt can specify these option parameters in either lowercase or uppercase. For the internal types that correspond to the offsets, see "dbgetoff." Offsets are returned only if the batch contains no syntax errors. DBPARSEONLY Checks the syntax of the query and returns error messages to the workstation. Offsets are returned if the DBOFFSET option is set and there are no errors. DBROWCOUNT Specifies a maximum number of regular rows to be returned on SELECT statements. It does not limit the number of compute rows returned. DBROWCOUNT works somewhat differently from most options. It is always on, never off. Setting DBROWCOUNT to 0 sets it back to the default, returning all the rows generated by a SELECT statement. Therefore, the way to turn DBROWCOUNT off is to turn it on with a count of 0. DBSHOWPLAN Generates a description of the processing plan after compilation and continues executing the query. DBSTAT Determines when performance statistics (CPU time, elapsed time, I/O, and so on) will be returned to the workstation after each query. This option takes a parameter with two possible values: IO (for statistics about SQL Server internal I/O) and TIME (for information about SQL Server's parsing, compilation, and execution times). DB-LIBRARY receives these statistics in the form of informational messages, and application programs can access them through the user-supplied message handler. DBSTORPROCID Sends the stored procedure ID to the workstation before sending rows generated by the stored procedure. DBTEXTLIMIT Causes DB-LIBRARY to limit the size of returned text or image values. When setting this option, you supply a parameter that is the length, in bytes, of the longest text or image value that your program can handle. DB-LIBRARY reads but ignores any part of a text or image value that goes over this limit. In the case of huge text values, it may take some time for the entire text value to be returned over the network. To keep SQL Server from sending this extra text in the first place, use the DBTEXTSIZE option instead. DBTEXTSIZE Limits the size of returned text or image values. When setting this option, you supply a parameter that is the length, in bytes, of the longest text or image value that SQL Server should return. Note that in programs that allow users to make ad hoc queries, the user can override this option with the SET TEXTSIZE statement. To set a text limit that the user cannot override, use the DBTEXTLIMIT option instead. ──────────────────────────────────────────────────────────────────────────── As mentioned in the preceding descriptions, certain options take parameters: ──────────────────────────────────────────────────────────────────────────── DBBUFFER 0 to 32,767 DBOFFSET SELECT, FROM, TABLE, ORDER, COMPUTE, STATEMENT, PROCEDURE, EXECUTE, or PARAM DBROWCOUNT 0 to 2,147,483,647 DBSTAT IO or TIME DBTEXTLIMIT 0 to 2,147,483,647 DBTEXTSIZE 0 to 2,147,483,647 ──────────────────────────────────────────────────────────────────────────── The dbsetopt function requires that an option parameter be specified when setting any option from the preceding list. The dbclropt and dbisopt functions require that the parameter be specified only for DBOFFSET and DBSTAT. This is because DBOFFSET and DBSTAT are the only options that can have multiple settings at a time; thus, they require further definition before being cleared or checked. Note that parameters specified in calls to dbsetopt, dbclropt, and dbisopt are always passed as character strings and must be surrounded by quotation marks, even if they are numeric values. See "dbsetopt" for more information. Appendix C DB-LIBRARY Datatypes ──────────────────────────────────────────────────────────────────────────── Introduction This appendix contains a list of SQL Server datatypes. To use these datatypes, include the sqlfront.h and sqldb.h header files in your program. For a detailed description of SQL Server datatypes, refer to the SQL Server Language Reference. Datatypes The following list describes the SQL Server datatypes. The dbconvert and dbwillconvert functions use these types. In addition, the dbcoltype function returns one of these types. ╓┌─────────────────────────────────┌─────────────────────────────────────────╖ Datatype Description ──────────────────────────────────────────────────────────────────────────── SQLBINARY binary type Datatype Description ──────────────────────────────────────────────────────────────────────────── SQLBIT bit type SQLCHAR char type SQLDATETIME datetime type SQLFLT8 8-byte float type SQLIMAGE image type SQLINT1 1-byte integer SQLINT2 2-byte integer SQLINT4 4-byte integer SQLMONEY money type Datatype Description ──────────────────────────────────────────────────────────────────────────── SQLTEXT text type SQL Type Definitions The following type definitions are used by DB-LIBRARY functions. The DB-LIBRARY user will find it convenient to use these when defining C program variables, particularly those used in dbbind, dbaltbind, and dbconvert. typedef char DBCHAR; /* char and text type */ typedef unsigned char DBBINARY; /* binary and image type */ typedef unsigned char DBTINYINT; /* 1-byte integer */ typedef short DBSMALLINT; /* 2-byte integer */ typedef unsigned short DBUSMALLINT;/* unsigned 2-byte integer */ typedef long DBINT; /* 4-byte integer */ typedef double DBFLT8; /* 8-byte float type */ typedef unsigned char DBBIT; /* bit type */ typedef int RETCODE; /* SUCCEED or FAIL */ typedef int STATUS; /* OK or condition code */ typedef unsigned char DBBOOL; /* TRUE or FALSE */ typedef unsigned char BYTE; /* A single byte */ typedef struct money /* money type */ { long mnyhigh; long mnylow; } DBMONEY; typedef struct datetime /* datetime type */ { long dtdays; /* number of days since 1/1/1900 */ unsigned long dttime; /* 300ths of a second since midnight */ } DBDATETIME; typedef struct dbvarychar /* Pascal-type string. */ { DBSMALLINT len; /* character count */ DBCHAR str[DBMAXCHAR]; /* non-terminated string */ } DBVARYCHAR; typedef struct dbvarybin /* Pascal-type binary array. */ { DBSMALLINT len; /* byte count */ BYTE array[DBMAXCHAR]; /* non-terminated array */ } DBVARYBIN; typedef DBSMALLINT DBINDICATOR; /* Used by DB-LIBRARY for indicator variables. */ Appendix D Error Messages ──────────────────────────────────────────────────────────────────────────── Introduction This appendix contains the complete range of possible DB-LIBRARY errors and error severity levels. The errors are listed alphabetically, and the error severity levels are listed by increasing severity. These values are passed to the currently installed, user-supplied error handler. For information on creating an error handler function for your application, see "dberrhandle" in Chapter 2, "Functions and Macros." To access these error definitions, include the sqlfront.h and sqldb.h header files in your program. Errors These error values are defined in the header file sqlfront.h. ──────────────────────────────────────────────────────────────────────────── SQLEAAMT User attempted a dbaltbind with mismatched column and variable types. SQLEABMT User attempted a dbbind with mismatched column and variable types. SQLEABNC Attempt to bind to a non-existent column. SQLEABNP Attempt to bind using NULL pointers. SQLEASEC Attempt to send an empty command buffer to the SQL Server. SQLEASNL Attempt to set fields in a null loginrec. SQLEASUL Attempt to set unknown loginrec field. SQLEAUTN Attempt to update the timestamp of a table which has no timestamp column. SQLEBBCI Batch successfully bulk-copied to SQL Server. SQLEBCBC bcp_columns() must be called before bcp_colfmt(). SQLEBCFO Bcp host-files must contain at least one column. SQLEBCNN Attempt to bulk-copy a NULL value into a Server column which does not accept NULL values. SQLEBCNT Attempt to use Bulk Copy with a non-existent Server table. SQLEBCOR Attempt to bulk-copy an oversized row to the SQL Server. SQLEBCPB bcp_bind() may NOT be used after bcp_init() has been passed a non-NULL input file name. SQLEBCPI bcp_init() must be called before any other bcp routines. SQLEBCPN bcp_bind(), bcp_collen() and bcp_colptr() may be used only after bcp_init() has been called with the copy direction set to DB_IN. SQLEBCSI Host-file columns may be skipped only when copying INto the Server. SQLEBCUO Bcp: Unable to open host data-file. SQLEBCVH bcp_exec() may be called only after bcp_init() has been passed a valid host file. SQLEBCWE I/O error while writing bcp data-file. SQLEBDIO Bad bulk-copy direction. Must be either IN or OUT. SQLEBEOF Unexpected EOF encountered in BCP data-file. SQLEBIVI bcp_columns() and bcp_colfmt() may be used only after bcp_init() has been passed a valid input file. SQLEBNCR Attempt to bind user variable to a non-existent compute row. SQLEBRFF I/O error while reading bcp format-file. SQLEBSKERR Cannot seek in data file. SQLEBTMT Attempt to send too much TEXT data via the bcp_moretext() call. SQLEBTOK Bad token from SQL Server: Datastream processing out of sync. SQLEBTYP Unknown bind type passed to DB-LIBRARY function. SQLEBUOE Bcp: Unable to open error-file. SQLEBWEF I/O error while writing bcp error-file. SQLECLOS Error in closing network connection. SQLECNOR Column number out of range. SQLECNULL NULL destination variable not allowed. SQLECOFL Data-conversion resulted in overflow. SQLECONN Unable to connect: SQL Server is unavailable or does not exist. SQLECSYN Attempt to convert data stopped by syntax error in source field. SQLEDBOP Invalid or out of range dbn option parameter. SQLEDBPS Maximum number of DBPROCESSes already allocated. SQLEDDNE DBPROCESS is dead or not enabled. SQLEDNTI Attempt to use dbtxtsput to put a new text-timestamp into a column whose datatype is neither SQLTEXT nor SQLIMAGE. SQLEICN Invalid computeid or compute column number. SQLEIFNB Illegal field number passed to bcp_control(). SQLEIICL Illegal integer column length returned by SQL Server. Legal integer lengths are 1, 2, and 4 bytes. SQLEIOPT Attempt to use invalid DBOPTION. SQLEITIM Illegal timeout value specified. SQLEKBCI 1000 rows sent to SQL Server. SQLEKBCO 1000 rows successfully bulk-copied to host-file. SQLEMEM Unable to allocate sufficient memory. SQLEMODE Network connection not in correct mode - invalid SQL Server connection. SQLENLOG NULL loginrec pointer encountered. SQLENONET DB-LIBRARY network communications layer not loaded. SQLENPRM NULL parameter not allowed for this dboption. SQLENSIP Negative starting index passed to dbstrcpy. SQLENTLL Name too long for loginrec field. SQLENTXT Attempt to get text point / timestamp from a non-text column. SQLENULL NULL DBPROCESS pointer encountered. SQLENXID The Server did not grant us a distributed-transaction ID. SQLEOOB Error in sending out-of-band data to SQL Server. SQLEPARM Invalid parameter in DB-LIBRARY function reference. SQLEPNUL NULL program pointer encountered. SQLEPWD Login incorrect. SQLERDCN Requested data-conversion does not exist. SQLEREAD Read from SQL Server failed. SQLERPND Attempt to initiate a new SQL Server operation with results pending. SQLESEOF Unexpected EOF from SQL Server. SQLESMSG General SQL Server error: Check messages from the SQL Server. SQLETIME SQL Server connection timed out. SQLETMTD Attempt to send too much TEXT data via the dbmoretext call. SQLETSIT Attempt to call dbtsput with an invalid timestamp. SQLEUDTY Unknown datatype encountered. SQLEVDPT For bulk copy, all variable-length data must have either a length-prefix or a terminator specified. SQLEWRIT Write to SQL Server failed. This error number is unused. SQLNULLO Attempt to login with null loginrec. ──────────────────────────────────────────────────────────────────────────── Error Severity Levels The following error severity levels are defined in the sqlfront.h header file. Your program must include sqlfront.h if it refers to these severity levels. ──────────────────────────────────────────────────────────────────────────── EXINFO Informational, non-error. EXUSER User error. EXNONFATAL Non-fatal error. EXCONVERSION Error in DB-LIBRARY data conversion. EXSERVER Server returned an error flag. EXTIME Exceeded timeout period while waiting for a response from the Server - the DBPROCESS is still alive. EXPROGRAM Coding error in user program. EXRESOURCE Running out of resources - the DBPROCESS may be dead. EXCOMM Failure in communication with Server - the DBPROCESS is dead. EXFATAL Fatal error - the DBPROCESS is dead. EXCONSISTENCY Internal software error - notify customer support. ──────────────────────────────────────────────────────────────────────────── Appendix E Building Applications ──────────────────────────────────────────────────────────────────────────── Building MS-DOS Applications This section describes how to build DB-LIBRARY applications to run in the MS-DOS environment. Terminate and Stay Resident Utility The DB-LIBRARY modules use a terminate and stay resident utility (TSR) for all network communications; the TSR is called dbnmpipe.exe. Normally, you will execute the TSR from your autoexec.bat file whenever you boot your system. If this is the case, make sure that you load the network before running the TSR. You can also load and execute the TSR just before you run your SQL Server application. In that case, simply type the name of the TSR on the command line, exactly as you would any other program: dbnmpipe If the directory in which the TSR resides is not named in your PATH variable, you must type in the exact path to the TSR. Whether you load the TSR through autoexec.bat or from the command line, you can unload it and recover the memory that it uses with the enddblib.exe program. The following example illustrates this: enddblib This program checks to make sure that the TSR is loaded and then unloads it. If dbnmpipe was not the last TSR loaded, it is dangerous to unload it. In this case, enddblib will prompt you if you want to remove it anyway. Do it at your own risk. You can supress prompting and force enddblib to unload the TSR by using the /q (quiet) command line switch as follows: enddblib /q Again, it is dangerous to unload a TSR if it was not the last one loaded. Libraries Two memory models are supported: the "medium" and "large" models. The following table shows the libraries for each model. ╓┌─────────────────────────────────┌─────────────────────────────────────────╖ Memory Model Library File ──────────────────────────────────────────────────────────────────────────── Large rldblib.lib Medium rmdblib.lib ──────────────────────────────────────────────────────────────────────────── At program link time, the LIB environment variable must be set to point to the directory in which the library files reside. Include Files The following DB-LIBRARY include files are provided: ╓┌─────────────────────────────────┌───────────────────────────────────────── Include File Contents ──────────────────────────────────────────────────────────────────────────── Include File Contents ──────────────────────────────────────────────────────────────────────────── sqlfront.h Error codes and severity levels; miscellaneous definitions and typedefs sqldb.h Function prototypes for all DB-LIBRARY functions ──────────────────────────────────────────────────────────────────────────── At compile time, you can set the INCLUDE environment variable to point to the directory in which the include files reside. Alternatively, you can use the /I compile line switch to point to the include file directory. Since your application must always include the sqldb.h file, you need not define the DB-LIBRARY functions you use. These functions and their proper declarations are already defined in the include file. You must define the application program's target operating system before your program includes the DB-LIBRARY header files. Use the following statements at the beginning of all SQL Server applications that will run under MS-DOS: #define DBMSDOS #include <sqlfront.h> #include <sqldb.h> Required System Libraries DB-LIBRARY does not require any special system libraries, except for the standard C run-time libraries; the TSR handles all network communication for the application. If your program needs any special libraries, including any that provide direct access to the LAN Manager, you must load them yourself in the normal manner. Compiling and Linking Considerations The following command illustrates a typical compilation with automatic linkage: cl /Lr /AL foo.c rldblib.lib The name foo.c is the name of your application. The large model (/AL) and real mode (/Lr) were used. Notice that the large model library, rldblib.lib, is specified. Increasing the Number of SQL Server Connections The C 5.10 run-time functions have a limitation of 20 open file handles at any one time. Because of this, a workstation can establish a maximum of 15 SQL Server connections at any one time. A new module is provided that allows applications running on a workstation to have up to 45 connections to an SQL Server. The module is crt0dat.obj and is designed to be used in the MS-DOS environment (versions 3.3 or later). If your application needs to establish more than 15 connections to SQL Servers, perform the following steps: 1. Explicitly link the crt0dat.obj module with your application: cl /Lr /AL foo.c crt0dat.obj RLDBLIB.LIB /link /NOE 2. This example assumes that the crt0dat.obj object module resides within the current directory. Note the additional option "/link /NOE" appended to the end of the compilation command line. This option is passed to the linker, instructing it not to search its extended dictionary. It is required because of the redefinition of symbols contained within the crt0dat.obj module. 3. Change or add the "FILES=" statement in the config.sys file to "FILES=50" and reboot the system. Most applications need only one connection to SQL Server. Applications using BROWSE mode require two connections. If you need more than two connections, reexamine your design to see if it can be improved. If the limit of 50 isn't satisfactory, refer to the readme.doc file supplied with the C 5.10 package. It explains how to create your own crt0dat.obj file with whatever limit you need. Sample Program The sqltestr.c sample program is included. This program establishes a connection with an SQL Server and then performs a simple query. It assumes that an SQL Server is attached to the network and has the pubs database installed. For example, if your SQL Server is named sql_svr, enter the name sql_svr in response to the "Enter LAN Manager Network Computer name of SQL SERVER:" prompt. Next, enter "Dull" to the "Enter author's last name to retrieve:" prompt. You will see information displayed that is associated with the name entered. A make file that builds the sample program is included. Be sure that your LIB and INCLUDE environment variables are set properly, and then enter the following command: make sqltestr Building MS OS/2 Applications This section describes how to build DB-LIBRARY applications to run in the MS OS/2 environment. Libraries The DB-LIBRARY functions are stored in a dynamic link library, pdblib.dll; the named pipe functions are stored in dbnmpp.dll. The LIBPATH command found in your config.sys file has to be modified to point to the directory in which pdblib.dll and dbnmpp.dll reside. Another file called pdblib.lib contains the "import" definitions that will be used by your application. The LIB environment variable also must be set to point to the directory in which pdblib.lib resides. Include Files The following DB-LIBRARY include files are provided: ╓┌─────────────────────────────────┌───────────────────────────────────────── Include File Contents ──────────────────────────────────────────────────────────────────────────── sqlfront.h Error codes and severity levels; miscellaneous definitions and typedefs sqldb.h Function prototypes for all DB-LIBRARY functions ──────────────────────────────────────────────────────────────────────────── You can set the INCLUDE environment variable to point to the directory in which the include files reside. Alternatively, you can use the /I compile line switch to point to the include file directory. Since your application must always include the sqldb.h file, you need not define the DB-LIBRARY functions you use. These functions and their proper declarations are already defined in the include file. You must define the application program's target operating system before your program includes the DB-LIBRARY header files. Use the following statements at the beginning of all SQL Server applications that will run under MS OS/2: #define DBMSOS2 #include <sqlfront.h> #include <sqldb.h> Special Considerations If your application is sending or receiving pointer information to and from DB-LIBRARY, the pointers must be defined as "far pointers." Normally the only time that this is a concern is when you are compiling your application with the small (/AS) or medium (/AM) compiler options set. For example, the following code declarations: . . . main() { DBPROCESS *dbproc; LOGINREC *login; . . . must be changed to: . . . main() { DBPROCESS far *dbproc; LOGINREC far *login; . . . Compiling and Linking Considerations The following command illustrates a typical compilation with automatic linkage: cl /Lp /AL /Au /Gs foo.c pdblib.lib The name foo.c is the name of the application. The large model (/AL) and protected mode (/Lp) options were used. The pdblib.lib file contains all of the DB-LIBRARY import definitions. Note that the /Au and /Gs options are specified. These options are required to force the DS register to be loaded with the proper data segment and to disable stack checking when a user-supplied function is invoked by DB-LIBRARY. For example, if a user function is supplied through the dbmsghandle function, then the message-handling function will require the /Au and /Gs options during compilation. Sample Programs The sqltestp.c sample program is included. This program simply establishes a connection with an SQL Server and then allows a simple query to be performed. It is assumed that an SQL Server is attached to the network and has the pubs database installed. For example, if your SQL Server is named sql_svr, enter the name sql_svr in response to the "Enter LAN Manager Network Computer name of SQL SERVER:" prompt. Next, enter "Dull" to the "Enter author's last name to retrieve:" prompt. You will see information displayed that is associated with the name entered. A make file that builds the sample program is included. Make sure that your LIB and INCLUDE environment variables are properly set, and then enter the following command: make sqltestp A Presentation Manager sample program is also included in the file sqlpm.c A make file that builds the sample program is included. Make sure that your LIB and INCLUDE environment variables are properly set, and then enter the following command: make sqlpm Building an Application for Windows This section describes how to build DB-LIBRARY applications to run in the Windows environment. Libraries The DB-LIBRARY functions for Windows are stored in two dynamic link libraries, wdblib.exe for Windows 2.1 and w3dblib.exe for Windows 3.0. In addition, the named pipe interface is stored in dbnmp3.dll for Windows 3.0. The PATH environment variable should be set to point to the directory in which the libraries reside. Two other files, called wdblib.lib for Windows 2.1 and w3dblib.lib for Windows 3.0, contain the "import" definitions that will be used by your application. The LIB environment variable must be set to point to the directory in which wdblib.lib and w3dblib.lib reside. Include Files The following DB-LIBRARY include files are provided: ╓┌─────────────────────────────────┌───────────────────────────────────────── Include File Contents ──────────────────────────────────────────────────────────────────────────── sqlfront.h Error codes and severity levels; miscellaneous definitions and typedefs sqldb.h Function prototypes for all DB-LIBRARY functions ──────────────────────────────────────────────────────────────────────────── You can set the INCLUDE environment variable to point to the directory in which the include files reside. Alternatively, you can use the /I compile line switch to point to the include file directory. Since your application must always include the sqldb.h file, you need not define the DB-LIBRARY functions you use. These functions and their proper declarations are already defined in the include file. You must define the application program's target operating system before your program includes the DB-LIBRARY header files. Use the following statements at the beginning of all SQL Server applications that will run under Windows: #define DBMSWIN #include <sqlfront.h> #include <sqldb.h> Special Windows DB-LIBRARY Functions The DB-LIBRARY macros DBLOCKLIB and DBUNLOCKLIB have been added to the Windows version of DB-LIBRARY. These two macros are necessary because segments in Windows libraries move around. If an application (or DB-LIBRARY function) is relying on a far pointer for later use, the segments must not move. Your Windows-based application must call dbinit before calling any other DB-LIBRARY function. In addition, the dbwinexit function has also been added to the Windows version of DB-LIBRARY. Your application must call this function immediately before exiting, to de-register as a Windows application. Special Considerations for Windows DB-LIBRARY Applications Error handling and message handling for DB-LIBRARY under Windows is installed differently than for MS-DOS or MS OS/2. Because the error and message handlers are callback functions and Windows dynamic link libraries may be moving around in memory between calls to the dynamic link library, the error handler and message handler must be installed exported callback functions. When Windows executes a callback function, the data segment register is not set to the application's data segment, even though the error handler is part of the application. Making the error handler and message handler functions into procedure instances and exporting them in the .def file causes Windows to set up the DS register correctly so that your handlers can correctly access the application's data. To create an error handler or message handler, you first must make the function into a procedure instance. To do so, you must call MakeProcInstance, in your initialization code or in the application message loop. The following example illustrates the call in the application's message loop: long FAR PASCAL MySqlWndProc(hWnd, message, wParam, lParam) HWND hWnd; /* window handle */ unsigned message; /* type of message */ WORD wParam; /* additional information */ LONG lParam; /* additional information */ { . . . static FARPROC lpMyMessageHandler; /* pointer to message handler */ static FARPROC lpMyErrorHandler; /* pointer to error handler */ int cdecl FAR MyMessageHandler(DBPROCESS *,DBINT, DBSMALLINT, DBSMALLINT, LPSTR); int cdecl FAR MyErrorHandler(DBPROCESS *, int, int, int, LPSTR, LPSTR); switch (message) { . . . case WM_CREATE: /* message: window being created */ /* Make the message and error handler instances. */ lpMyMessageHandler = MakeProcInstance((FARPROC)MyMessageHandler, hInst); lpMyErrorHandler = MakeProcInstance((FARPROC)MyErrorHandler, hInst); /* Install the instances into dblib. */ dbmsghandle(lpMyMessageHandler); dberrhandle(lpMyErrorHandler); . . . Making the FARPROC variables static allows you to free them during WM_DESTROY message processing: . . . case WM_DESTROY: /* message: window being destroyed */ FreeProcInstance(lpMyMessageHandler); /* release handlers */ FreeProcInstance(lpMyErrorHandler); PostQuitMessage(0); dbwinexit(); break; . . . Note that callback functions in Windows are normally declared FAR PASCAL, but not with these two callback functions for DB-LIBRARY. DB-LIBRARY expects all function calls to be cdecl FAR: consequently, your handlers must also be cdecl FAR, as shown in the following example: int FAR cdecl MyErrorHandler(dbproc, severity, errno, oserr, dberrstr, oserrstr); Because this function declaration type is contrary to the FARPROC declaration type in MakeProcInstance, you must cast the function name, when passed as a parameter to MakeProcInstance, as FARPROC; otherwise the compiler generates an error message. Also, you must pass the FARPROC pointers returned from MakeProcInstance to dberrhandle and dbmsghandle to properly install the handlers. Passing the addresses of the handler functions instead of the procedure instances will cause unpredictable results. Finally, you must export the functions so that Windows will correctly fix up the segment references. In your .def file, add the following lines to the EXPORTS section: _MyMessageHandler @x ; message handler _MyErrorHandler @y ; error handler Note that the function names begin with an underscore, this is because with the cdecl, the compiler automatically adds the underscore to the function name. The "@x" and "@y" are replaced with "@numbers" for the number of your exported functions. See the sample Windows application for examples of installing error and message handlers. Increasing the Number of Allowable Open Files The C 5.10 run-time functions have a limitation of 20 open file handles at any one time. Five files are already opened by the system and are unavailable. Therefore, a maximum of 15 SQL Server connections can be established from a workstation at any one time. A startup module is provided that allows applications running on a workstation to have up to 45 user-opened files and connections to an SQL Server (providing there is enough memory available within the dynamic link library's data segment). The crt0dat.obj module is designed for the MS-DOS environment (versions 3.3 or later) running Windows versions 2.1 or later. If your application needs to open more than 15 files or connections to SQL Servers, perform the following steps: 1. Explicitly link the crt0dat.obj module with your application using the /NOE switch with the linker invocation command. 2. Change or add the "FILES=" statement in the config.sys file to "FILES=50" and reboot the system. If the limit of 50 isn't satisfactory, refer to the readme.doc file supplied with the C 5.10 package. It explains how to create your own crt0dat.obj file with whatever limit you need. The application determines the allowable number of open handles; however, this number also depends on the memory available within the dynamic link library's data segment. By using the supplied crt0dat.obj module, you can increase the number of files or connections that your application can open. Sample Program The sqltestw.c sample program is included. This program simply establishes a connection with an SQL Server and then performs a simple query. It is assumed that an SQL Server is attached to the network and has the pubs database installed. A make file that builds the sample program is included. Make sure that your LIB and INCLUDE environment variables are properly set, and then enter the following command: make sqltestw Appendix F Finding SQL Servers on the Network ──────────────────────────────────────────────────────────────────────────── This appendix describes how to locate an SQL Server on the network from an application program. Information on all SQL Servers is contained in the workstation's LAN Manager table, which is updated whenever an SQL Server is added or deleted from the network. To gain access to the information in this table, call NetServerEnum, a LAN Manager Applications Program Interface (API) function. (See the Microsoft LAN Manager Programmer's Reference for information on this function.) NetServerEnum should be called with a level 1 or level 2 request. If the call is successful, it returns a value of 0. Then the program checks the sv_type variable to determine if the SV_TYPE_SQLSERVER bit is set. If the bit is set, the server is an SQL Server. The following example prints out all SQL Servers found on the network: #include <stdio.h> /* usually located in ..\c\include */ #include <netcons.h> /* usually located in ..\lanman\netsrc\h */ #include <server.h> /* usually located in ..\lanman\netsrc\h */ /* Define server_info_1 buffer area. It is defined here to avoid requiring an excess amount of stack area. */ struct server_info_1 svr[100]; main () { int i; /* use as loop counter */ int ret; /* return variable for NetServerEnum */ unsigned short entriesread; /* entries read by NetServerEnum */ unsigned short totalentries; /* total number of entries available */ /* Now make an API call to NetServerEnum. The following LAN Manager libraries are required. - NETAPI.LIB (usually found in ..\lanman\netsrc\lib) - NETOEM.LIB (usually found in ..\lanman\netsrc\lib) The "svr" buffer must be large enough to hold all entries. In this example, it is set to hold 100 entries. Note that a "level 1" call (the second parameter) is made to retrieve the information needed. */ ret = NetServerEnum (NULL, 1, (char *)svr, sizeof (svr), &entriesread, &totalentries); if (!ret) /* if successful call to NetServerEnum */ { printf ("\nThe folowing SQL Servers were found on "); printf ("the network:\n"); printf (" Server Name Server Comment\n"); printf (" ------------------------------------\n"); for (i=0; i < entriesread; i++) /* Look for all entries with the SV_TYPE_SQLSERVER bit set */ if (svr[i].sv1_type & SV_TYPE_SQLSERVER) printf (" %-20s %s\n", svr[i].sv1_name,svr[i].sv1_comment); } else { printf ("Error in call to NetSeverEnum, "); printf ("error value = %d\n", ret); } } Appendix G Relative Sizes of Library Routines ──────────────────────────────────────────────────────────────────────────── This appendix documents the relative sizes of the DB-LIBRARY routines. Size estimates for DB-LIBRARY are for the static link MS-DOS libraries only. The dynamic link libraries for MS OS/2 and Windows require no program memory; however, they do consume system memory. Under MS OS/2 this is not usually a problem; under Windows, memory constraints can result in a segment load failure if multiple data locks are present and memory is highly fragmented when you attempt to load a code segment. The dynamic nature of memory swapping precludes accurate estimates of memory requirements for a given DB-LIBRARY function under Windows, but the static link library memory cost estimates may be used as a rough guideline. Static Link Library Memory Costs A number of functions in DB-LIBRARY are considered to be "core" functions. These functions are used by almost every application that communicates with the SQL Server. Core functions are grouped together in the object modules of DB-LIBRARY. In addition, a number of support routines are loaded with an application during linking with these core routines. However, once an application references a single core routine, it incurs little or no additional cost when it references other core routines. Many of the non-core routines may reference core routines. In this case, the core routines will be loaded during link time, making these non-core routines costly. Since the core routines are almost always loaded, the estimates for non-core routines assume that the core routines have already been loaded. DB-LIBRARY Core Routines The following DB-LIBRARY functions comprise the core routines ■ dbclose ■ dbcmd ■ dbdataready ■ dberrhandle ■ dbexit ■ dbfreelogin ■ dbinit ■ dblogin ■ dbmsghandle ■ dbnextrow ■ dbopen ■ dbresults ■ dbsetlogintime ■ dbsettime ■ dbsqlexec ■ dbsqlok ■ dbsqlsend Relative Memory Sizes Core functions contribute about 1/2 to 3/5 of the library code and data size. (Note that this is not the physical size of the libraries; typically, a library's physical size is up to 30% larger than its memory resident code and data size.) In the tables that follow, relative memory sizes are rated on a scale of 1 to 10, with 1 being the smallest module and 10 being the largest. These estimates include the necessary C run-time support routines. Setting Up the LOGINREC Function Relative Size ──────────────────────────────────────────────────────────────────────────── dbfreelogin Core dblogin Core DBSETLAPP 4 DBSETLHOST 4 DBSETLPWD 4 DBSETLUSER 4 Establishing an SQL Server Connection Function Relative Size ──────────────────────────────────────────────────────────────────────────── dbopen Core dbsetllogintime Core Setting the Current Database Function Relative Size ──────────────────────────────────────────────────────────────────────────── dbuse 2 Building a Command Batch Function Relative Size ──────────────────────────────────────────────────────────────────────────── dbcmd Core dbfcmd 10 dbfreebuf 4 Accessing a Command Batch Function Relative Size ──────────────────────────────────────────────────────────────────────────── dbgetchar 3 dbstrcpy 3 dbstrlen 3 Executing a Command Batch Function Relative Size ──────────────────────────────────────────────────────────────────────────── DBRBUF Core dbsqlexec Core dbsqlok Core dbsqlsend Core Setting and Clearing Command Options Function Relative Size ──────────────────────────────────────────────────────────────────────────── dbclropt 4 dbisopt 4 dbsetopt 4 Setting Up the Results Function Relative Size ──────────────────────────────────────────────────────────────────────────── dbresults Core Getting Result Data Function Relative Size ──────────────────────────────────────────────────────────────────────────── dbadata 4 dbaltbind 10 dbbind 10 dbdata 4 Accessing Result Rows Function Relative Size ──────────────────────────────────────────────────────────────────────────── dbclrbuf 1 dbgetrow 1 dbnextrow Core dbprhead 6 dbprrow 6 Canceling Results Function Relative Size ──────────────────────────────────────────────────────────────────────────── dbcancel 3 dbcanquery 3 Setting Response Time for Results Function Relative Size ──────────────────────────────────────────────────────────────────────────── DBGETTIME 3 dbsettime Core Error and Message Handling Function Relative Size ──────────────────────────────────────────────────────────────────────────── DBDEAD 3 dberrhandle Core dbmsghandle Core Regular Result Column Information Function Relative Size ──────────────────────────────────────────────────────────────────────────── dbcollen 4 dbcolname 4 dbcoltype 4 dbdatlen 4 dbnumcols 4 Compute Result Column Information Function Relative Size ──────────────────────────────────────────────────────────────────────────── dbadlen 5 dbaltcolid 5 dbaltlen 5 dbaltop 5 dbalttype 5 dbbylist 5 dbnumalts 5 dbnumcompute 5 Row Buffer Information Function Relative Size ──────────────────────────────────────────────────────────────────────────── DBFIRSTROW 3 DBLASTROW 3 Command State Information Function Relative Size ──────────────────────────────────────────────────────────────────────────── DBCMDROW 3 DBCOUNT 3 DBCURCMD 3 dbgetoff 1 DBMORECMDS 3 DBNUMORDERS 3 dbordercol 4 DBROWS 3 Miscellaneous Information Function Relative Size ──────────────────────────────────────────────────────────────────────────── dbchange 3 DBCURROW 3 dbgetmaxprocs 3 DBISAVAIL 3 dbname 3 DBROWTYPE 3 Browse Mode Function Relative Size ──────────────────────────────────────────────────────────────────────────── dbcolbrowse 8 dbcolsource 8 dbqual 8 dbtabbrowse 8 dbtabcount 8 dbtabname 8 dbtabsource 8 dbtsnewlen 8 dbtsnewval 8 dbtsput 8 Text and Image Handling Function Relative Size ──────────────────────────────────────────────────────────────────────────── dbmoretext 7 dbtsptr 7 dbtxtimestamp 7 dbtxtsnewval 7 dbtxtsput 7 dbwritetext 7 Datatype Conversion Function Relative Size ──────────────────────────────────────────────────────────────────────────── dbwillconvert 10 dbconvert 10 Cleanup Function Relative Size ──────────────────────────────────────────────────────────────────────────── dbclose Core dbexit Core Miscellaneous Functions Function Relative Size ──────────────────────────────────────────────────────────────────────────── dbprtype 1 dbreadpage 2 dbsetavail3 dbsetmaxprocs 3 dbsetnull 3 dbwritepage 2 Bulk Copy Function Relative Size ──────────────────────────────────────────────────────────────────────────── bcp_batch 10 bcp_bind 10 bcp_colfmt 10 bcp_collen 10 bcp_colptr 10 bcp_columns 10 bcp_control 10 bcp_done 10 bcp_exec 10 bcp_init 10 bcp_moretext 10 bcp_sendrow 10 BCP_SETL 10 Two-Phase Commit Function Relative Size ──────────────────────────────────────────────────────────────────────────── abort_xact 6 build_xact_string 6 close_commit 6 commit_xact 6 open_commit 6 remove_xact 6 scan_xact 6 start_xact 6 stat_xact 6 Modularity DB-LIBRARY static link libraries contain a number of object modules. Each module may have one or more DB-LIBRARY APIs or support routines within it. Whenever an application references any of the APIs contained within a module, the entire module and all of the APIs that it contains will be linked with the application. If an application calls any one of the APIs in a module, it can call any of the other APIs in that same module without incurring additional memory overhead. ■ Core Routines ■ dbdataready, dberrhandle, dbmsghandle, dblogin, dbfreelogin, dbresults, dbnextrow, dbclose, dbopen, dbcmd, dbsqlexec, dbsqlok, dbsqlsend, dbsettime, dbsetlogintime, dbexit, dbinit ■ Other standard routines ■ dbfcmd ■ dbsetopt, dbisopt, dbclropt, dbfreebuf ■ dbstrlen, dbstrcpy, dbgetchar ■ dbcanquery, dbcancel ■ dbgetrow, dbclrbuf ■ dbconvert, dbwillconvert ■ dbcollen, dbcolname, dbcoltype, dbdata, dbdatlen, dbnumcols, dbordercol ■ dbcolbrowse, dbtabname, dbcolsource, dbtabbrowse, dbtabsource, dbtabcount, dbtsnewval, dbtsnewlen, dbtsput, dbqual, dbfreequal ■ dbprhead, dbprrow ■ dbtxptr, dbtxtimestamp, dbtxtsnewval, dbtxtsput, dbwritetext, dbmoretext ■ dbuse ■ dbbind─uses dbconvert ■ dbsetnull ■ dbaltbind─uses dbconvert ■ dbaltlen, dbalttype, dbadata, dbadlen, dbnumalts, dbaltcolid, dbbylist, dbaltop, dbnumcompute ■ dbgetoff ■ dbprtype ■ dbsetmaxprocs, dbgetmaxprocs, dbchange, dbname, dbsetavail ■ dbreadpage, dbwritepage INDEX ────────────────────────────────────────────────────────────────────────── A abort_xact Ad hoc query, process Aggregate function Application build DB-LIBRARY B Batch command bcp_batch bcp_bind bcp_colfmt bcp_collen bcp_colptr bcp_columns bcp_control bcp_done bcp_exec bcp_init bcp_moretext bcp_sendrow BCP_SETL Binary data read write Bind aggregate and compute results program example Browse mode functions program example Browse mode steps update column WHERE clause Buffer clear row command message row information row row, number of first build_xact_string Bulk copy functions bcp_batch bcp_bind bcp_colfmt bcp_collen bcp_colptr bcp_columns bcp_control bcp_done bcp_exec bcp_init bcp_moretext bcp_sendrow Bulk copy macro BCP_SETL Bulk Copy special library Bulk copy data format end from program variable execute initialize special library Bylist, compute row C C functions and macros Cancel command batch result row Cleanup functions close_commit Column bind to program variable datatype get data in ORDER BY clause length for data in length of data number in compute row number in results row ORDER BY clause print headings updatable using browse mode update Command batch access build cancel determine changes to database execute program example set up next statement verify Command buffer add text character in check for TRANSACT-SQL statements clear copy portion length Command processing functions Command state information functions Command complete process current number determine whether returns rows more to process option Commit server specify Commit service end connection establish connection lookup table commit_xact COMPUTE clause, number Compute column aggregate function bind to program variable datatype length of data maximum data length operand column ID pointer Compute row bylist determine number of columns Connections, number of SQL Server Constant, symbolic Control parameter, set Convert data program example core functions dbclose dbcmd dberrhandle dbexit dbfreelogin dbinit dblogin dbmsghandle dbnextrow dbopen dbresults dbsetlogintime dbsettime dbsqlexec dbsqlok dbsqlsend Sizes D Data files program example Data bind high-speed load length in column length in compute column maximum length result Database current name determine whether command batch has changed query and update program example set Datatype column compute column conversion function convert Datatypes, DB-LIBRARY DBBINARY DBBIT DBBOOL DBCHAR DBDATETIME DBFLT8 DBINT DBMONEY DBSMALLINT DBTINYINT DBUSMALLINT DBVARYBIN DBVARYCHAR RETCODE STATUS Datatypes, SQL Server SQLBINARY SQLBIT SQLCHAR SQLDATETIME SQLFLT8 SQLIMAGE SQLINT1 SQLINT2 SQLINT4 SQLMONEY SQLTEXT DB-LIBRARY options DBARITHABORT DBARITHIGNORE DBBUFFER DBNOAUTOFREE DBNOCOUNT DBNOEXEC DBOFFSET DBPARSEONLY DBROWCOUNT DBSHOWPLAN DBSTAT DBSTORPROCID DBTEXTLIMIT DBTEXTSIZE DB-LIBRARY application datatypes. See Datatypes, DB-LIBRARY error severity error function categories lock segment option overview program framework program with unlock Windows functions dbadata dbadlen dbaltbind vartype dbaltcolid dbaltlen dbaltop dbalttype DBARITHABORT option DBARITHIGNORE option dbbind DBBUFFER option dbbylist dbcancel dbcanquery dbchange dbclose dbclrbuf dbclropt dbcmd DBCMDROW dbcolbrowse dbcollen dbcolname dbcolsource dbcoltype dbconvert program example DBCOUNT DBCURCMD DBCURROW dbdata dbdatarady dbdataready dbdatlen DBDEAD dberrhandle dbexit dbfcmd DBFIRSTROW dbfreebuf dbfreelogin dbfreequal dbgetchar dbgetmaxprocs dbgetoff dbgetrow DBGETTIME dbinit DBISAVAIL DBISAVIAL dbisopt DBLASTROW DBLOCKLIB (Windows only) DBLOCKLIB dblogin DBMORECMDS dbmoretext dbmsghandle dbname dbnextrow DBNOAUTOFREE option DBNOCOUNT option DBNOEXEC option dbnumalts dbnumcols dbnumcompute DBNUMORDERS DBOFFSET option dbopen dbordercol DBPARSEONLY option dbprhead DBPROCESS allocate structure close structure close dead determine if available determine maximum structures mark set maximum structures structure dbprrow dbprtype dbqual DBRBUF dbreadpage dbresults DBROWCOUNT option DBROWS DBROWTYPE dbrwitepage dbsetavail DBSETLAPP DBSETLHOST dbsetlogintime DBSETLPWD DBSETLUSER dbsetmaxprocs dbsetnull dbsetopt dbsettime DBSHOWPLAN option dbsqlexec dbsqlok dbsqlsend DBSTAT option DBSTORPROCID option dbstrcpy dbstrlen dbtabbrowse dbtabcount dbtabname dbtabsource DBTEXTLIMIT option DBTEXTSIZE option dbtsnewlen dbtsnewval dbtsput dbtxptr dbtxtimestamp dbtxtsnewval dbtxtsput function dbtxtsput DBUNLOCKLIB (Windows only) DBUNLOCKLIB dbuse dbwillconvert dbwinexit dbwritepage dbwritetext Debug Dynamic link library Dynamic Link Library size of E Environment MS OS/2 MS-DOS ERREXIT exit value Error handling functions Error severity DB-LIBRARY Error DB-LIBRARY handling install handler SQL Server Example programs Exit value F File header include Functions abort_xact alphabetical list bcp_batch bcp_bind bcp_colfmt bcp_collen bcp_colptr bcp_columns bcp_control bcp_done bcp_exec bcp_init bcp_moretext bcp_sendrow browse mode program example browse mode build_xact_string C categories cleanup close_commit command processing command state information commit_xact datatype conversion dbadata dbadlen dbaltbind dbaltcolid dbaltlen dbaltop dbalttype dbbind dbbylist dbcancel dbcanquery dbchange dbclose dbclrbuf dbclropt dbcmd dbcolbrowse dbcollen dbcolname dbcolsource dbcoltype dbconvert dbdata dbdatarady dbdataready dbdatlen dberrhandle dbexit dbfcmd dbfreebuf dbfreelogin dbfreequal dbgetchar dbgetmaxprocs dbgetoff dbgetrow dbinit dbisopt dblogin dbmoretext dbmsghandle dbname dbnextrow dbnumalts dbnumcols dbnumcompute dbopen dbordercol dbprhead dbprrow dbprtype dbqual dbreadpage dbresults dbsetavail dbsetlogintime dbsetmaxprocs dbsetnull dbsetopt dbsettime dbsqlexec dbsqlok dbsqlsend dbstrcpy dbstrlen dbtabbrowse dbtabcount dbtabname dbtabsource dbtsnewlen dbtsnewval dbtsput dbtxptr dbtxtimestamp dbtxtsnewval dbtxtsput dbuse dbwillconvert dbwinexit dbwritepage dbwritetext error-handling information retrieval initialization message-handling miscellaneous information miscellaneous open_commit remove_xact results processing scan_xact start_xact stat_xact text- and image-handling TSEQUAL built-in I Image handling functions limit value size send value to SQL Server update value Include files Microsoft Windows applications MS OS/2 applications MS-DOS applications Information retrieval functions Initialization functions L LAN Manager Library bulk copy Microsoft Windows application MS OS/2 application MS-DOS required two-phase commit service Lock, DB-LIBRARY segment Log in to SQL Server LOGINREC allocate free set application name set for bulk copy set password set up set username in set workstation name in structure M Macros alphabetical list BCP_SETL C DBCMDROW DBCOUNT DBCURCMD DBCURROW DBDEAD DBFIRSTROW DBGETTIME DBISAVAIL DBLASTROW DBLOCKLIB DBMORECMDS DBNUMORDERS DBRBUF DBROWS DBROWTYPE DBSETLAPP DBSETLHOST DBSETLPWD DBSETLUSER DBUNLOCKLIB memory constraints Microsoft Windows Message buffer handling functions handling install handler SQL Server Microsoft Windows include files Miscellaneous information functions MS OS/2 include files MS-DOS include files increasing SQL Server connections library required system library sample program N Name database result column set application set workstation Network close connection find SQL Server on Notational conventions Null value bind define substitution value O Offsets, types of open_commit Operand column ID Operating system file set columns specify format Option check status clear command DB-LIBRARY set ORDER BY clause columns in number of columns in OS/2 dynamic-link library sample program P Password, set Pointer data for compute column text Print column headings row Probe process Program example binding aggregate and compute results converting data with dbconvert querying and updating the database sending queries in a command batch two-phase commit service using browse mode functions using row buffering working with data files Program variable bind compute result column to bind result column dbaltbind send row to SQL Server set data address set data length Program examples with DB-LIBRARY Q Query database program example Query ad hoc example in command batch R Record, login Relative size abort_xact bcp_batch bcp_bind bcp_colfmt bcp_collen bcp_colptr bcp_columns bcp_control bcp_done bcp_exec bcp_init bcp_moretext bcp_sendrow BCP_SETL build_xact close_commit commit_xact dbadata dbadlen dbaltbind dbaltcolid dbaltlen dbaltop dbalttype dbbind dbbylist dbcancel dbcanquery dbchange dbclose dbclrbuf dbclropt dbcmd DBCMDROW dbcolbrowse dbcollen dbcolname dbcolsource dbcoltype dbconvert DBCOUNT DBCURCMD DBCURROW dbdata dbdataready dbdatlen dberrhandle dbexit dbfcmd DBFIRSTROW dbfreebuf dbfreelogin dbgetchar dbgetmaxprocs dbgetoff dbgetrow DBGETTIME DBISAVAIL dbisopt DBLASTROW dblogin DBMORECMDS dbmoretext dbmsghandle dbname dbnextrow dbnumalts dbnumcols dbnumcompute DBNUMORDERS dbopen dbordercol dbprhead dbprrow dbprtype dbqual DBREAD dbreadpage dbresults DBROWS DBROWTYPE dbsetavail DBSETLAPP DBSETLHOST dbsetlogintime DBSETLPWD DBSETLUSER dbsetmaxprocs dbsetopt dbsettime dbsetull dbsqlexec dbsqlok dbsqlsend dbstrcpy dbstrlen dbtabbrowse dbtabcount dbtabname dbtabsource dbtsnewlen dbtsnewval dbtsptr dbtsput dbtxtsnewval dbtxtsput dbtxttimestamp dbuse dbwillconvert dbwritepage dbwritetext library routines open_commit remove_xact scan_xact start_xact stat_xact remove_xact Result access data directly access row bind data to variable cancel row cancel column name data database column name more to process number of columns read row row set up stored procedure Results processing functions Row buffer clear information last row number number of first row program example read Row compute current number determine if statement returns determine type determine number affected by statement print column headings print read data read regular result save set maximum number type S Sample program Microsoft Windows MS OS/2 MS-DOS scan_xact Sizes core functions Dynamic link libraries Relative SQL Server response time DB-LIBRARY will wait for SQL Server communicate with datatypes. See Datatypes, SQL Server error establish connection find on network increase number of connections log in message sqltestp.c sample program sqltestr.c sample program sqltestw.c sample program start_xact Statement, process Statements check command buffer for stat_xact STDEXIT exit value Symbolic constant T Table browsable name number work Text handling functions limit value size pointer value pointer put timestamp value in DBPROCESS send value to SQL Server timestamp update value Time response to connection request SQL Server response Timestamp column length column value put column value in DBPROCESS put text value in DBPROCESS text Token value, convert to string TRANSACT-SQL Transaction, distributed build name decrement active sites mark as aborted mark as committed print record start status TSEQUAL built-in function Two-phase commit functions abort_xact build_xact_string close_commit commit_xact open_commit remove_xact scan_xact start_xact stat_xact Two-phase commit service special library U Username set W WHERE clause update browsable table Windows application Dynamic link library memory constraints sample program special DB-LIBRARY functions Work table