Table of contents

f90SQL License terms

Copyrights

Preface

• The f90SQL Library
• Organization of this manual
• Intended Audience
• Typographic and lexical Conventions used in this manual
• About the Code Examples
• Recommended reading

Chapter 1 Introduction to DBMS and ODBC

• Introduction
• The Relational Database
• Structured Query Language (SQL)
  • Processing a SQL Statement
  • Embedded SQL
  • SQL Procedures and Modules
  • Call-Level Interfaces
• The ODBC Interface

Chapter 2 ODBC and f90SQL Architecture

• ODBC Architecture
  • Applications
  • The Driver Manager
  • Drivers
    • Driver conformance levels
    • ODBC API conformance level
      • Core functionality
      • Level 1 functionality
      • Level 2 functionality
    • SQL conformance levels
      • Minimum level SQL support
      • Core level SQL support
      • Extended level SQL support
  • Data Sources
    • Using data soruces
• f90SQL Architecture

Chapter 3 Introduction to the SQL Language

• Introduction
• Data Definition Language (DDL)
• Data Manipulation Language (DML)
  • The SELECT Statement
    • The basic query
    • Specifying the data source
    • Outer joins
    • Aliasing column names
    • The DISTINCT predicate
    • The TOP predicate
    • The WHERE predicate
    • The GROUP BY predicate
    • The HAVING predicate
    • The ORDER predicate
    • The UNION predicate
  • The INSERT Statement
  • The UPDATE statement
  • The DELETE Statement
• Escape Sequences in SQL and ODBC
  • Date, Time, and Timestamp Literals
  • Datetime Interval Literals
  • Scalar Function Calls
  • LIKE Predicate Escape Character
  • Outer Joins
  • Procedure Calls

Chapter 4 ODBC Fundamentals

• Introduction
• Data types. An introduction
• Handles
  • The Environment Handle
  • The Connection Handle
  • The Statement Handle
  • The Descriptor Handle
• State Transitions
• Buffers
  • Data buffers
  • Length/Indicator buffers
  • Data Length, Buffer Length, and Truncation
  • Character Data
• Data types revisited
  • SQL Data Types in ODBC
  • Fortran data types in ODBC
  • Using data type identifiers
  • Data type conversions
  • Pseudo Type Identifiers

Chapter 5 Basic Application Steps

• Introduction
• Step 1. Initializing of the ODBC environment and connecting to a data source
  • Allocating an environment handle
  • Setting ODBC version and environment attributes
  • Allocating a connection handle
  • Establishing a connection
    • Connecting with f90SQLConnect
    • Connecting with f90SQLDriverConnect
    • Connecting with f90SQLBrowseConnect
    • Connection pooling
• Step 2. Initializing statements
• Step 3. Building and executing SQL statements
  • Catalog functions
  • Queries with parameters
• Step 4a. Retrieving resultsets
  • Retrieving resultsets metadata
  • Binding resultset columns
  • Fetching data
    • Forward-only cursors
    • Block cursors
      • Column-wise binding
      • Row-wise binding
      • Selecting and changing the rowset size
      • Number of rows fetched and status
    • Scrollable cursors
      • Scrolling and fetching with scrollable cursors
  • Closing the Cursor
• Step4b. Updating data
  • Traditional UPDATE, DELETE AND INSERT SQL statements
  • Positioned update and delete statements
  • Updating data with f90SQLSetPos
• Step 5. Committing transactions
  • Transaction commit modes
    • Auto-commit mode
    • Manual-commit mode
  • Transaction support
    • Setting the commit mode
    • Committing and rolling back transactions
    • Effect of transactions on cursors and prepared statements
  • Transaction isolation
    • Transaction isolation levels
      • Read Uncommitted isolation level
      • Read Committed isolation level
      • Repeatable Read isolation level
      • Serializable isolation level
    • Selecting and setting the appropriate transaction isolation level
  • Concurrency control
  • A final word on concurrency control and isolation levels
• Step 6. Disconnecting from the data source

Chapter 6 Advanced Topics: Parameters

• Introduction
• Named parameters
• Offsetting bound parameters
• Long Data as parameters (data-at-execution)
• Procedure Parameters
• Retrieving Output Parameters by SQLGetData
• Arrays of parameters
  • Column-Wise Binding
  • Row-Wise Binding

Chapter 7 Advanced Topics: Execution, Fetching and Updating

• Asynchronous execution
• Executing SQL Batches
  • Explicit batches
  • Procedures
  • Arrays of parameters
• Multiple resultsets
• Freeing a Statement Handle
• Updating and fetching data with f90SQLBulkOperations
  • Updating Rows by Bookmark with f90SQLBulkOperations
  • Deleting Rows by Bookmark with f90SQLBulkOperations
  • Inserting Rows with f90SQLBulkOperations
  • Fetching Rows with f90SQLBulkOperations

Chapter 8 Advanced Topics: Descriptors

• Introduction
• Types of descriptors in ODBC
• The structure of descriptors
  • The SQL_DESC_COUNT header field
  • Bound descriptor records
  • Deferred fields
  • Consistency check
• Working with descriptors
  • Implicitly allocated descriptors
  • Explicitly allocated descriptors
  • Obtaining and setting descriptor information
  • Automatic Population of the IPD
  • Freeing Descriptors
  • Copying Descriptors

Chapter 9 Advanced Topics: Diagnostics

• Introduction
• ODBC Return Codes
• ODBC Diagnostic records
  • Header records
  • Status records
    • Sequence of status records
    • SQLSTATEs
• Using f90SQLGetDiagRec and f90SQLGetDiagField

Chapter 10 Catalog Subroutines

• Introduction
• Data returned by the catalog subroutines
• Arguments used in catalog subroutines
  • Ordinary Parameters
  • Pattern Value Parameters
  • Identifier Parameters
  • Value List Parameters
• Schema views
• Retrieving information about stored procedures
• Calling Catalog subroutines
• f90SQL Catalog Structures

Chapter 11 f90SQL/ODBC Reference

Introduction

Connecting to data sources

• f90SQLAllocHandle (SQLAllocHandle)
• f90SQLConnect (SQLConnect)
• f90SQLDriverConnect (SQLDriverConnect)
• f90SQLBrowseConnect (SQLBrowseConnect)

Obtaining information about drivers and data sources

• f90SQLDataSources (SQLDataSources)
• f90SQLDrivers (SQLDrivers)
• f90SQLGetInfo (SQLGetInfo)
• f90SQLGetFunctions (SQLGetFunctions)
• f90SQLGetTypeInfo (SQLGetTypeInfo)

Setting and retrieving driver attributes

• f90SQLSetConnectAttr (SQLSetConnectAttr)
• f90SQLGetConnectAttr (SQLGetConnectAttr)
• f90SQLSetEnvAttr (SQLSetEnvAttr)
• f90SQLGetEnvAttr (SQLGetEnvAttr)
• f90SQLSetStmtAttr (SQLSetStmtAttr)
• f90SQLGetStmtAttr (SQLGetStmtAttr)

Setting and retrieving descriptor fields

• f90SQLGetDescField (SQLGetDescField)
• f90SQLGetDescRec (SQLGetDescRec)
• f90SQLSetDescField (SQLSetDescField)
• f90SQLSetDescRec (SQLSetDescRec)
• f90SQLCopyDesc (SQLCopyDesc)

Preparing SQL requests

• f90SQLPrepare (SQLPrepare)
• f90SQLBindParameter (SQLBindParameter)
• f90SQLGetCursorName (SQLGetCursorName)
• f90SQLSetCursorName (SQLSetCursorName)

Submitting requests

• f90SQLExecute (SQLExecute)
• f90SQLExecDirect (SQLExecDirect)
• f90SQLNativeSql (SQLNativeSql)
• f90SQLDescribeParam (SQLDescribeParam)
• f90SQLNumParams (SQLNumParams)
• f90SQLParamData (SQLParamData)
• f90SQLPutData (SQLPutData)

Retrieving results and information about results

• f90SQLRowCount (SQLRowCount)
• f90SQLNumResultCols (SQLNumResultCols)
• f90SQLDescribeCol (SQLDescribeCol)
• f90SQLColAttribute (SQLColAttribute)
• f90SQLBindCol (SQLBindCol)
• f90SQLFetch (SQLFetch)
• f90SQLFetchScroll (SQLFetchScroll)
• f90SQLGetData (SQLGetData)
• f90SQLSetPos (SQLSetPos)
• f90SQLBulkOperations (SQLBulkOperations)
• f90SQLMoreResults (SQLMoreResults)
• f90SQLGetDiagField (SQLGetDiagField)
• f90SQLGetDiagRec (SQLGetDiagRec)

Obtaining information about the data source's system tables (catalog subroutines)

• f90SQLColumnPrivileges (SQLColumnPrivileges)
• f90SQLColumns (SQLColumns)
• f90SQLForeignKeys (SQLForeignKeys)
• f90SQLPrimaryKeys (SQLPrimaryKeys)
• f90SQLProcedureColumns (SQLProcedureColumns)
• f90SQLProcedures (SQLProcedures)
• f90SQLSpecialColumns (SQLSpecialColumns)
• f90SQLStatistics (SQLStatistics)
• f90SQLTablePrivileges (SQLTablePrivileges)
• f90SQLTables (SQLTables)

Terminating statements and connections

• f90SQLFreeStmt (SQLFreeStmt)
• f90SQLCloseCursor (SQLCloseCursor)
• f90SQLCancel (SQLCancel)
• f90SQLEndTran (f90SQLEndTran)
• f90SQLDisconnect (SQLDisconnect)
• f90SQLFreeHandle (SQLFreeHandle)

f90SQL utility functions and subroutines

• f90SQLFunctionExists (SQL_FUNC_EXISTS)
• f90SQLLenBinaryAttr (SQL_LEN_BINARY_ATTR)
• f90SQLLenDataAtExec (SQL_LEN_DATA_AT_EXEC)
• f90SQLStrFormat
• f90SQLc2fStrFormat
• f90SQLf2cStrFormat
• f90GetStrPointer
• f90GetMaxBufferLength
• f90GetTrimBufferLength

Chapter 12 Installing and using f90SQL

• The f90SQL CD
• Automatic installation of f90SQL
• Manually installing the f90SQL library
  • Digital Visual Fortran
  • Absoft Pro-Fortran
  • Lahey Fortran 90
  • Lahey/Fujitsu Fortran 95
  • Salford Fortran 95
• Installing the online documentation
  • Manually installing the standard HTML documentation
  • Installing the HTML-Help documentation
• Installing the ODBC library and drivers
  • Checking the version of ODBC Driver Manager installed in your system
• Installing the sample database and example code
• Linking your application to f90SQL
  • An Example program
  • Compiling and linking with Digital Visual Fortran
  • Compiling and linking with Absoft Pro-Fortran
  • Compiling and linking with Lahey Fortran 90
  • Compiling and linking with Lahey/Fujitsu Fortran 95
  • Compiling and linking with Salford Fortran 95

Appendix 1 Information type descriptions

Appendix 2 Using f90SQL Pointer-Format subroutines

• Introduction
• f90SQLConnect (SQLConnect)
• f90SQLDriverConnect (SQLDriverConnect)
• f90SQLBrowseConnect (SQLBrowseConnect)
• f90SQLDataSources (SQLDataSources)
• f90SQLDrivers (SQLDrivers)
• f90SQLGetInfo (SQLGetInfo)
• f90SQLGetFunctions (SQLGetFunctions)
• f90SQLSetConnectAttr (SQLSetConnectAttr)
• f90SQLGetConnectAttr (SQLGetConnectAttr)
• f90SQLSetEnvAttr (SQLSetEnvAttr)
• f90SQLGetEnvAttr (SQLGetEnvAttr)
• f90SQLSetStmtAttr (SQLSetStmtAttr)
• f90SQLGetStmtAttr (SQLGetStmtAttr)
• f90SQLGetDescField (SQLGetDescField)
• f90SQLGetDescRec (SQLGetDescRec)
• f90SQLSetDescField (SQLSetDescField)
• f90SQLSetDescRec (SQLSetDescRec)
• f90SQLPrepare (SQLPrepare)
• f90SQLBindParameter (SQLBindParameter)
• f90SQLGetCursorName (SQLGetCursorName)
• f90SQLSetCursorName (SQLSetCursorName)
• f90SQLExecDirect (SQLExecDirect)
• f90SQLNativeSql (SQLNativeSql)
• f90SQLParamData (SQLParamData)
• f90SQLPutData (SQLPutData)
• f90SQLDescribeCol (SQLDescribeCol)
• f90SQLColAttribute (SQLColAttribute)
• f90SQLBindCol (SQLBindCol)
• f90SQLGetData (SQLGetData)
• f90SQLGetDiagField (SQLGetDiagField)
• f90SQLGetDiagRec (SQLGetDiagRec)
• f90SQLColumnPrivileges (SQLColumnPrivileges)
• f90SQLColumns (SQLColumns)
• f90SQLForeignKeys (SQLForeignKeys)
• f90SQLPrimaryKeys (SQLPrimaryKeys)
• f90SQLProcedureColumns (SQLProcedureColumns)
• f90SQLProcedures (SQLProcedures)
• f90SQLSpecialColumns (SQLSpecialColumns)
• f90SQLStatistics (SQLStatistics)
• f90SQLTablePrivileges (SQLTablePrivileges)
• f90SQLTables (SQLTables)

Appendix 3 Data conversions

• Converting data from Fortran types to SQL types
• Converting data from SQL types to Fortran types

Appendix 4 Interval and Numeric data types

• Interval data type
  • Fortran Interval Structure
  • Interval Data Type Precision
  • Interval Data Type Length
  • Interval Literals
  • Overriding Default Leading and Seconds Precision for Interval Data Types
  • Constraints of the Gregorian Calendar
  • Converting Fortran Time Intervals to SQL
  • Converting SQL Time Intervals to Fortran
• Numeric Data type
  • Overriding Default Precision and Scale for Numeric Data Types

Appendix 5 ODBC scalar functions

• Introduction
• String Functions
• Numeric Functions
• Time, Date, and Interval Functions
• System Functions
• Explicit Data Type Conversion

Appendix 6 Column Size, Decimal Digits, Transfer Octet Length, and Display Size

• Introduction
• Column Size
• Decimal Digits
• Transfer Octet Length
• Display Size

Appendix 7 Compiler-specific features and notes

• f90SQL named constants in Absoft, Salford  compilers
• f90SQL named constants in Lahey Fortran 90
• LOC function in Lahey Fortran 90

Index of Tables

• Typographical conventions (Preface)
• Table 4.1. Deferred buffers
• Table 4.2. SQL data type identifiers
• Table 4.3. Fortran data type identifiers
• Table 5.1. f90SQL Catalog subroutines
• Table 5.2. Values of status vector for block fetching
• Table 5.3. Isolation levels
• Table 7.1. Option to determine support of batch SQL
• Table 8.1. Descriptor types
• Table 9.1. ODBC return codes
• Table 9.2. Diagnostic header records
• Table 9.3. Predefined status record's fields
• Table 9.4. Diagnostic message elements
• Table 10.1. ODBC and f90SQL Catalog subroutines
• Table 10.3. f90SQL Catalog structures
• Table 10.2. Catalog subroutines and their arguments
• Table 12.1. f90SQL files

Index of Figures

• Figure 2.1. ODBC architecture
• Figure 2.2. f90SQL architecture
• Figure 4.1. Buffers
• Figure 5.1. Flowchart of an ODBC application
• Figure 5.2. Rowset, recordset and block cursors
• Figure 5.3. Column-wise binding of columns
• Figure 5.4. Row-wise binding of columns
• Figure 5.5. Rowsets and fetch orientation
• Figure 6.1.Column-wise binding of parameters
• Figure 6.2. Row-wise binding of parameters
• Figure 10.1. Simple sales order database

Index of Complete Examples

• Example 4.1
• Example 4.2
• Example 4.3
• Example 4.4
• Example 4.5
• Example 4.6
• Example 5.1
• Example 5.2
• Example 5.3
• Example 5.4
• Example 5.5
• Example 5.6
• Example 5.7
• Example 5.8
• Example 5.9
• Example 6.1
• Example 6.2
• Example 10.1