CONTENTS Title Page Copyright Page Preface Technical Changes and New Features 1 Overview 1.1 Introduction to SQL/Services 1.1.1 Client Components 1.1.2 Network Components 1.1.3 Server System Components 1.2 Preparing Programmers to Use SQL/Services 1.2.1 What Programmers Must Know to Write Applications 1.2.2 Reading Path for Programmers 1.3 Location of SQL/Services Error Documentation 1.4 What System Managers Must Know to Support SQL/Services 2 Tools for API Application Development 2.1 Introduction to Dynamic SQL 2.2 Overview of Dynamic SQL Statements 2.2.1 Execution Statements 2.2.2 Result Table Statements 2.3 Using Dynamic SQL 2.3.1 Parameter Markers 2.3.2 SELECT Statements 2.3.3 Unknown Statements 2.3.4 The SQL Descriptor Area (SQLDA) 2.3.5 The Extended SQL Descriptor Area (SQLDA2) 2.3.6 The SQL Communications Area (SQLCA) 2.4 Overview of API Routines 2.4.1 Association Routines 2.4.2 SQL Statement Routines 2.4.3 Result Table Routines 2.4.4 Utility Routines 2.4.5 Functional Interface Routines 2.5 Overview of Data Structures 2.6 Developing Applications with the Functional Interface Routines 2.6.1 Recommendations for API Application Development 2.6.2 Methods for Indirectly Accessing SQLCA and SQLDA Structures 2.7 Building SQL/Services Application Programs 2.7.1 Building Applications on the VMS Operating System 2.7.2 Building Applications on the MS-DOS Operating System 2.7.2.1 Building Applications on MS-DOS from the DOS Prompt 2.7.2.2 Building Applications on MS-DOS Windows 3.0 2.7.3 Building Applications on the ULTRIX or ULTRIX for RISC Operating System 2.7.4 Building Applications on the OS/2 Operating System 2.7.5 Building Applications on the Macintosh Operating System 2.7.5.1 Building Applications on the Macintosh Operating System for MPW 2.7.5.2 Building Applications on the Macintosh Operating System for THINK C 2.7.6 Building Applications on the SunOS Operating System 3 Programming Guidelines 3.1 The SQLSRV$DYNAMIC Sample Application 3.2 Building the Sample Application 3.2.1 Building the Sample Application on the Macintosh Operating System 3.2.1.1 Building the Sample Application on the Macintosh Operating System for MPW 3.2.1.2 Building the Sample Application on the Macintosh Operating System for THINK C 3.2.2 Building the Sample Application for MS-DOS 3.2.2.1 Building the Sample Application on the MS-DOS Operating System from the DOS Prompt 3.2.2.2 Building the Sample Application on the MS-DOS Operating System from Windows 3.0 3.2.3 Building the Sample Application on the OS/2 Operating System 3.2.4 Building the Sample Application on the SunOS Operating System 3.2.5 Building the Sample Application on the ULTRIX or ULTRIX for RISC Operating System 3.2.6 Building the Sample Application on the VMS Operating System 3.3 Running the Sample Application 3.4 The Driver Module 3.5 The Dynamic Module 3.5.1 Creating and Releasing an Association 3.5.2 Processing the Dynamic SQL Statement 3.5.2.1 Declaring and Allocating SQLDA_ID Identifiers 3.5.2.2 Testing for Parameter Markers 3.5.2.3 Allocating Indicator and Data Variables 3.5.2.4 Processing Parameter Markers 3.5.2.5 Executing Non-SELECT Statements 3.5.2.6 Testing for SELECT Statements 3.5.2.7 Processing a Result Table 3.5.2.8 Releasing Prepared Statements 3.5.2.9 Error Handling 4 Performance Guidelines 4.1 Batched Execution 4.2 Fetching Multiple Rows 5 Logging for Performance and Debugging 5.1 Enabling and Disabling Logging 5.2 Association Logging 5.3 Routine Logging 5.4 Message Protocol Logging 6 Managing an SQL/Services System 6.1 Process Pooling Overview 6.1.1 Process Pooling Components 6.1.2 How the Communication Server Process Functions 6.1.2.1 Reading the Default Configuration File 6.1.2.2 Modifying the Configuration File 6.1.3 Execution Server Processes 6.1.3.1 Choosing an Execution Server Process 6.1.3.2 Choosing Generic Class Execution Server Processes 6.1.3.3 Choosing Database Class Execution Server Processes 6.1.3.4 Using the IDLE Parameter to Control Execution Server Processes 6.1.4 Execution Server Process Reporting 6.2 Methods of Accessing the SQL/Services Server 6.2.1 How to Enable Server Access 6.2.1.1 Explicit Access 6.2.1.2 Proxy-Like Access 6.2.1.3 Default Account Access 6.2.2 Reinitializing Proxy and Default Access 6.3 Transport Guidelines 6.3.1 Network Errors 6.3.2 DECnet Transport Guidelines 6.3.3 TCP/IP Transport Guidelines 6.3.4 AppleTalk Transport Guidelines 6.4 Purging Server Log Files 7 API Routines 7.1 Documentation Format 7.1.1 Routine Name 7.1.2 Return Values 7.1.3 VAX Format Section 7.1.4 C Format Section 7.1.5 Parameters Section 7.1.5.1 Data Type Entry 7.1.5.2 Access Entry 7.1.5.3 Mechanism Entry 7.2 sqlsrv_abort-Disconnect Association 7.3 sqlsrv_allocate_sqlda_data-Allocate Variables 7.4 sqlsrv_associate-Create Client/Server Association 7.5 sqlsrv_close_cursor-Release Result Table 7.6 sqlsrv_declare_cursor-Declare a Cursor 7.7 sqlsrv_execute-Execute Prepared Statement 7.8 sqlsrv_execute_immediate-Prepare and Execute Statement 7.9 sqlsrv_fetch-Get Row from Result Table 7.10 sqlsrv_fetch_many-Get Multiple Rows from Result Table 7.11 sqlsrv_free_sqlda_data-Release Variables 7.12 sqlsrv_get_environment-Return Environment Variable Values 7.13 sqlsrv_open_cursor-Create Result Table 7.14 sqlsrv_prepare-Compile Statement and Initialize SQLDA 7.15 sqlsrv_release-Release Client/Server Association 7.16 sqlsrv_release_statement-Release Statement Resources 7.17 sqlsrv_set_environment-Set Environment Variable Values 7.18 sqlsrv_set_server_class-Select Server Class 7.19 sqlsrv_set_transport_type-Select Network Transport Type 7.20 sqlsrv_sqlca_error-Return Error Codes 7.21 sqlsrv_sqlca_error_text-Return Error Text 7.22 sqlsrv_sqlca_num_batch_rows-Return SQLCA.ERRD[1] 7.23 sqlsrv_sqlca_count-Return SQLCA.ERRD[2] 7.24 sqlsrv_sqlca_sqlerrd-Return Extended Information 7.25 sqlsrv_sqlda_sqld-Return Number of Active Columns 7.26 sqlsrv_sqlda_column_name-Copy Column Name 7.27 sqlsrv_sqlda_column_type-Return Column Type 7.28 sqlsrv_sqlda_bind_data-Bind User Buffers to SQLDA Variables 7.29 sqlsrv_sqlda_unbind_sqlda-Release SQLDA Variables 7.30 sqlsrv_sqlda_map_data-Return Column Information 7.31 sqlsrv_sqlda_unmap_data-Free Resources 7.32 sqlsrv_sqlda_ref_data-Return Column Information 7.33 sqlsrv_sqlda_unref_data-Free Resources 7.34 sqlsrv_sqlda_get_data-Copy Data from SQLDA 7.35 sqlsrv_sqlda_set_data-Copy Value of Column 8 Data Structures 8.1 Documentation Format 8.2 ASSOCIATE_STR-Association Structure 8.3 SQLCA-SQL Communications Area 8.4 SQLERRD-Part of SQLCA 8.5 SQLDA-SQL Descriptor Area 8.6 SQLVAR-Parameter Marker or Select List Item 8.7 SQLSRV_ENV_STR-Environment Variable Structure 9 Data Types 9.1 Data Types 9.2 SQLSRV_ASCII_STRING 9.3 SQLSRV_GENERALIZED_NUMBER 9.4 SQLSRV_GENERALIZED_DATE 9.5 SQLSRV_VARCHAR 9.6 SQLSRV_LIST_VARBYTE 9.7 SQLSRV_VARBYTE A Sample IVP Log File B Reporting Software Problems EXAMPLES 2-1 Linking an MS-DOS Program with DECnet and TCP/IP 2-2 Linking an MS-DOS Program with TCP/IP Disabled 2-3 Linking an MS-DOS Program with DECnet Disabled 6-1 Definition of the Default Generic Execution Server Processes 6-2 Sample Database Class Definition 6-3 Detached Process Server Information Report A-1 Full CLIENT.LOG Sample Log File FIGURES 1-1 Client/Server Model for SQL/Services 1-2 SQL/Services Architecture 3-1 Statement Execution Flow 6-1 Communication Server Handling Requests for Processing 6-2 Operational Phases of the Communication Server 6-3 Creation of the Default Generic Class 6-4 Components Affecting Database Class Servers 6-5 Methods for Server Access TABLES 2-1 SQL Statements That Can Be Dynamically Executed 2-2 SQL Statements That Cannot Be Dynamically Executed 2-3 LINK Command Parameter Values 2-4 MS-DOS API Libraries 6-1 Process Pooling Components 6-2 Configuration File Parameters 6-3 VMS Detached Process Data for Servers 6-4 Methods of Accessing the Server System 6-5 Available Transports for SQL/Services APIs 6-6 Error Code Files for DECnet 6-7 Error Code Files for TCP/IP 7-1 Sections in the Routine Template 7-2 API Return Values 7-3 API Parameter Data Types 7-4 Values of the execute_flag Parameter 8-1 Sections in the Data Structure Template 8-2 Values Placed in the SQLCA.SQLERRD[2] Field 9-1 Data Types