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 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 2.3.5 The SQL Communications Area 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 Building SQL/Services Application Programs 2.6.1 Building Applications on the VMS Operating System 2.6.2 Building Applications on the MS-DOS Operating System 2.6.3 Building Applications on the ULTRIX or ULTRIX for RISC Operating System 2.6.4 Building Applications on the OS/2 Operating System 2.6.5 Building Applications on the Macintosh Operating System 3 Recommendations for API Development 3.1 A New Direction for API Application Development 3.2 API Changes in Rdb/VMS Version 4.0 3.2.1 Abstract Data Types 3.2.2 Allocating SQLCA and SQLDA Structures 3.2.3 Functional Interface Routines 4 Programming Guidelines 4.1 The SQLSRV$DYNAMIC Sample Application 4.2 Building the Sample Application 4.2.1 Building the Sample Application on the VMS Operating System 4.2.2 Building the Sample Application on the MS-DOS Operating System 4.2.3 Building the Sample Application on the ULTRIX or ULTRIX for RISC Operating System 4.2.4 Building the Sample Application on the OS/2 Operating System 4.2.5 Building the Sample Application on the Macintosh Operating System 4.3 Running the Sample Application 4.4 The Driver Module 4.5 The Dynamic Module 4.5.1 Creating and Releasing an Association 4.5.2 Processing the Dynamic SQL Statement 4.5.2.1 Declaring and Allocating SQLDA_ID Identifiers 4.5.2.2 Testing for Parameter Markers 4.5.2.3 Allocating Indicator and Data Variables 4.5.2.4 Processing Parameter Markers 4.5.2.5 Executing Non-SELECT Statements 4.5.2.6 Testing for SELECT Statements 4.5.2.7 Processing a Result Table 4.5.2.8 Releasing Prepared Statements 4.5.2.9 Error Handling 5 Performance Guidelines 5.1 Batched Execution 5.2 Fetching Multiple Rows 5.3 Filtering Result Tables 5.3.1 Elements of Filter Expressions 5.3.2 Constants 5.3.3 Placeholders 5.3.4 Mathematical Operators 5.3.5 Relational Operators 5.3.6 Logical Operators 5.3.7 String Operators 5.3.8 Precedence of Operators 6 Logging for Performance and Debugging 6.1 Enabling and Disabling Logging 6.2 Association Logging 6.3 Routine Logging 6.4 Message Protocol Logging 7 Process Pooling 7.1 Overview 7.2 Process Pooling Components 7.3 Communication Server Process 7.3.1 The Default Configuration File 7.3.2 Modifying the Configuration File 7.3.2.1 Procedures for Modifying the Configuration File 7.3.2.2 Rules for Modifying the Configuration File 7.4 Execution Server Processes 7.4.1 Choosing an Execution Server Process 7.4.2 Generic Class Execution Server Processes 7.4.3 Database Class Execution Server Processes 7.4.3.1 System Management Tasks: Setting Up Database Servers 7.4.3.2 Programmer Tasks: Setting Up Database Servers 7.4.4 The Dynamic Nature of Execution Servers 8 Accessing SQL/Services 8.1 Methods of Server Access 8.2 How to Enable Server Access 8.2.1 Explicit Access 8.2.2 SQL/Services Proxy Access 8.2.3 SQLSRV$SRV Default Account Access 8.3 Reinitializing Proxy and Default Access 9 API Routines 9.1 Documentation Format 9.1.1 Routine Name 9.1.2 Return Values 9.1.3 VAX Format Section 9.1.4 C Format Section 9.1.5 Parameters Section 9.1.5.1 Data Type Entry 9.1.5.2 Access Entry 9.1.5.3 Mechanism Entry 9.2 sqlsrv_abort-Disconnect Association 9.3 sqlsrv_allocate_sqlda_data-Allocate Variables 9.4 sqlsrv_associate-Create Client/Server Association 9.5 sqlsrv_close_cursor-Release Result Table 9.6 sqlsrv_declare_cursor-Declare a Cursor 9.7 sqlsrv_execute-Execute Prepared Statement 9.8 sqlsrv_execute_immediate-Prepare and Execute Statement 9.9 sqlsrv_fetch-Get Row from Result Table 9.10 sqlsrv_fetch_many-Get Multiple Rows from Result Table 9.11 sqlsrv_free_sqlda_data-Release Variables 9.12 sqlsrv_get_environment-Return Environment Variable Values 9.13 sqlsrv_open_cursor-Create Result Table 9.14 sqlsrv_prepare-Compile Statement and Initialize SQLDA 9.15 sqlsrv_release-Release Client/Server Association 9.16 sqlsrv_release_statement-Release Statement Resources 9.17 sqlsrv_set_environment-Set Environment Variable Values 9.18 sqlsrv_set_filter-Define Filter for Result Table 9.19 sqlsrv_set_server_class-Select Server Class 9.20 sqlsrv_sqlca_error-Return Error Codes 9.21 sqlsrv_sqlca_error_text-Return Error Text 9.22 sqlsrv_sqlca_num_batch_rows-Return SQLCA.ERRD[1] 9.23 sqlsrv_sqlca_count-Return SQLCA.ERRD[2] 9.24 sqlsrv_sqlda_sqld-Return Number of Active Columns 9.25 sqlsrv_sqlda_column_name-Copy Column Name 9.26 sqlsrv_sqlda_column_type-Return Column Type 9.27 sqlsrv_sqlda_bind_data-Allocate Data Returned in SQLDA 9.28 sqlsrv_sqlda_unbind_data-Release Variables 9.29 sqlsrv_sqlda_map_data-Return Column Information 9.30 sqlsrv_sqlda_unmap_data-Free Resources 9.31 sqlsrv_sqlda_get_data-Copy Data from SQLDA 9.32 sqlsrv_sqlda_set_data-Copy Value of Column 10 Data Structures 10.1 Documentation Format 10.2 ASSOCIATE_STR-Association Structure 10.3 SQLCA-SQL Communications Area 10.4 SQLERRD-Part of SQLCA 10.5 SQLDA-SQL Descriptor Area 10.6 SQLVAR-Parameter Marker or Select List Item 10.7 SQLSRV_ENV_STR-Environment Variable Structure 11 Data Types 11.1 Data Types 11.2 SQLSRV_ASCII_STRING 11.3 SQLSRV_GENERALIZED_NUMBER 11.4 SQLSRV_GENERALIZED_DATE 11.5 SQLSRV_VARCHAR 11.6 SQLSRV_LIST_VARBYTE 11.7 SQLSRV_VARBYTE A Filter Expression Functions A.1 ABS A.2 ACOS A.3 ASC A.4 ASIN A.5 AT A.6 ATAN A.7 ATN2 A.8 CDOW A.9 CEILING A.10 CHR A.11 CMONTH A.12 COS A.13 CTOD A.14 DAY A.15 DIFFERENCE A.16 DMY A.17 DOW A.18 DTOC A.19 DTOR A.20 DTOS A.21 EXP A.22 FLOOR A.23 INT A.24 LEFT A.25 LEN A.26 LOG A.27 LOG10 A.28 LOWER A.29 LTRIM A.30 MDY A.31 MOD A.32 MONTH A.33 PI A.34 RAND A.35 REPLICATE A.36 RIGHT A.37 ROUND A.38 RTOD A.39 RTRIM A.40 SIGN A.41 SIN A.42 SOUNDEX A.43 SPACE A.44 SQRT A.45 STR A.46 STUFF A.47 SUBSTR A.48 TAN A.49 TIME A.50 TRIM A.51 UPPER A.52 USER A.53 VAL A.54 YEAR B Environment Variables B.1 SQLSRV_ENV_DATE B.2 SQLSRV_ENV_CENTURY B.3 SQLSRV_ENV_SET_EXACT C SQL/Services Sample Application D Sample IVP Log Files EXAMPLES 7-1 Definition of Generic Execution Server Process 7-2 Definition of Database Execution Server Process C-1 The SQLSRV$DRIVER.C Module C-2 The SQLSRV$DYNAMIC.C Module D-1 Sample Association Level Log D-2 Sample Routine Level Log D-3 Sample Message Protocol Level Log FIGURES 1-1 Client/Server Model for SQL/Services 1-2 SQL/Services Architecture 3-1 Functional Interface Routines 4-1 Statement Execution Flow 5-1 Placeholders in Filter Expressions 7-1 Communication Server Handling Requests for Processing 7-2 Operational Phases of the Communication Server 7-3 Default Generic Class Subpool 7-4 Components Affecting Database Class Servers 8-1 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 MS-DOS API Libraries 2-4 OS/2 API Libraries 7-1 Process Pooling Components 7-2 Configuration File Parameters 8-1 Methods of Accessing the Server System 9-1 Sections in the Routine Template 9-2 API Return Values 9-3 API Parameter Data Types 9-4 Values of the execute_flag Parameter 10-1 Sections in the Data Structure Template 10-2 Error Code Files 10-3 Values Placed in the SQLCA.SQLERRD[2] Field 11-1 Data Types B-1 Settings for the SQLSRV_ENV_DATE Variable B-2 Settings for the SQLSRV_ENV_CENTURY Variable B-3 Settings for the SQLSRV_ENV_SET_EXACT Variable