Embarcadero INTERBASE XE7 Update 1 Developer's Guide

Embarcadero® InterBase XE7

Update 1

Developer’s Guide

Published ReleaseDate: March, 2015

all other Embarcadero Technologies product or service names are trademarks or registered trademarks

of Embarcadero Technologies, Inc. All other trademarks are property of their respective owners.

This software/documentation contains proprietary information of Embarcadero Technologies, Inc.;

it is provided under a license agreement containing restrictions on use and disclosure and is also

protected by copyright law. Reverse engineering of the software is prohibited.

Embarcadero Technologies, Inc. is a leading provider of award-winning tools for application

developers and database professionals so they can design systems right, build them faster and

run them better, regardless of their platform or programming language. Ninety of the Fortune 100

and an active community of more than three million users worldwide rely on Embarcadero

products to increase productivity, reduce costs, simplify change management and compliance,

and accelerate innovation. Founded in 1993, Embarcadero is headquartered in San Francisco,

with offices located around the world. To learn more, please visit http://www.embarcadero.com.

March 2, 2015

ix

Contents ix

Tables ix

Figures ix

Chapter 1

Using the InterBase

Developer’s Guide

Topics covered in this book . . . . . . . . . . .1-1

Chapter 2

Client/Server Concepts

Definition of a Client. . . . . . . . . . . . . . .2-1

The InterBase Client Library. . . . . . . . . . .2-2

Definition of a Server . . . . . . . . . . . . . .2-3

Application Development . . . . . . . . . . . .2-4

Client Tools Applications . . . . . . . . . . .2-4

InterBase Express (IBX) for Delphi and C++

Builder. . . . . . . . . . . . . . . . . .2-4

dbExpress (DBX) . . . . . . . . . . . . .2-4

ADO.NET Provider for InterBase (64-bit) .2-5

Developing and Deploying the InterBase ToGo

Edition . . . . . . . . . . . . . . . . . . .2-7

Developing with the ToGo Edition . . .2-8

Deploying with the ToGo Edition . . . .2-8

Embedded Applications . . . . . . . . . . 2-10

Predefined Database Queries . . . . . 2-10

Dynamic Applications . . . . . . . . . . 2-10

API Applications . . . . . . . . . . . . . . 2-11

Advantages of Using the InterBase API 2-11

API Function Categories . . . . . . . . 2-11

The Install API and the Licensing API . 2-11

Multi-database Applications . . . . . . . . 2-12

Chapter 3

Programming Applications with

Delphi or C++ Builder

Optimizing the InterBase SQL Links Driver . . .3-1

Setting the Driver Flags . . . . . . . . . . .3-2

Setting the SQL Pass-through Mode. . . . . 3-2

Setting the SQL Query Mode . . . . . . . .3-2

Working with TQuery . . . . . . . . . . . . . .3-3

Why Not to Use TTable. . . . . . . . . . . .3-3

Setting TQuery Properties and Methods. . .3-3

Using Generators . . . . . . . . . . . . . . . . 3-4

Chapter 4

Programming with JDBC

Installing InterClient Classes into JBuilder . . . 4-1

Database Application Basics . . . . . . . . 4-1

Using JDBC URLs. . . . . . . . . . . . . . 4-3

JDBC URL Argument . . . . . . . . . . . . 4-4

Log Writer File Property . . . . . . . . . . . 4-4

SSL File Properties . . . . . . . . . . . . . 4-5

Programming with InterClient . . . . . . . . . . 4-6

InterClient Architecture . . . . . . . . . . . 4-6

InterClient Communication . . . . . . . . . 4-7

Developing InterClient Programs . . . . . . . . 4-8

Using the JDBC Interfaces . . . . . . . . . 4-8

Importing the InterClient Classes. . . . . 4-9

The DriverManager Class . . . . . . . . 4-9

The Driver Class . . . . . . . . . . . . . 4-9

The Connection Class . . . . . . . . . 4-10

About InterClient Drivers. . . . . . . . . . 4-10

Loading the InterClient Driver . . . . . 4-11

Explicitly Creating the InterClient Driver 4-11

Accessing InterClient Extensions to the JDBC .

4-12

Opening a Database Connection . . . . . 4-13

Using the DriverManager to Get a

Connection . . . . . . . . . . . . . . 4-13

Using InterClient Driver Object to Get a

Connection . . . . . . . . . . . . . . 4-13

Choosing between the Driver and

DriverManager Methods . . . . . . . 4-14

Defining Connection Parameters. . . . 4-14

Security. . . . . . . . . . . . . . . . . 4-15

Executing SQL Statements . . . . . . . . 4-15

The Statement Class . . . . . . . . . . 4-15

Querying Data . . . . . . . . . . . . . 4-16

Selecting Data with PreparedStatement 4-17

Finalizing Objects. . . . . . . . . . . . 4-18

Modifying Data . . . . . . . . . . . . . 4-18

Executing Stored Procedures . . . . . . . 4-20

Statement Example. . . . . . . . . . . 4-20

PreparedStatement Example. . . . . . 4-21

Troubleshooting InterClient Programs . . . . 4-21

Handling Installation Problems . . . . . . 4-21

Debugging your Application . . . . . . . . 4-22

Deploying InterClient Programs. . . . . . . . 4-23

Contents

x

Deploying InterClient Programs as Applets 4-23

Required Software for Applets . . . . . 4-24

Pros and Cons of Applet Deployment. . 4-24

Deploying InterClient Programs as Applications

4-24

Required Software for Applications . . . 4-25

InterClient/JDBC Compliance Specifications . 4-25

InterClient Extensions to the JDBC API . . 4-26

JDBC Features Not Implemented in InterClient

4-26

InterClient Implementation of JDBC Features .

4-28

InterBase Features Not Available through

InterClient or JDBC. . . . . . . . . . . . 4-28

Java SQL Datatype Support . . . . . . . . 4-29

SQL-to-Java Type Conversions . . . . . . 4-30

Java-to-SQL Type Conversion . . . . . . . 4-31

InterClient Class References. . . . . . . . 4-31

InterClient Data Source Properties for InterBase .

4-32Standard properties . . . . . . . . . . . 4-32

Extended Properties . . . . . . . . . . 4-33

InterClient Connection Pooling. . . . . . . 4-35

InterClient Scrollability. . . . . . . . . . . . . 4-36

The Connection Class . . . . . . . . . . . 4-36

The ResultSet Class . . . . . . . . . . . . 4-37

Additional Functions . . . . . . . . . . . . 4-37

Batch Updates. . . . . . . . . . . . . . . . . 4-38

Methods for the Statement and

PreparedStatement Classes . . . . . . . 4-38

The BatchUpdateException Class . . . . . 4-39

The DatabaseMetaData.supportsBatchUpdates

Function . . . . . . . . . . . . . . . . . 4-40

Code Examples . . . . . . . . . . . . . . 4-40

Implementation of Blob, Clob, and Other Related

API’s . . . . . . . . . . . . . . . . . . . . . 4-41

Chapter 5

Programming Applications

with ODBC

Overview of ODBC . . . . . . . . . . . . . . .5-1

Configuring an ODBC Driver. . . . . . . . .5-2

Configuring and Using ODBC Data Sources . .5-2

Configuring Data Sources . . . . . . . . . .5-2

Connecting from Delphi Using the ODBC Data

Source . . . . . . . . . . . . . . . . . . .5-3

Chapter 6

Working with UDFs and Blob

Filters

UDF Overview . . . . . . . . . . . . . . . . . 6-2

Writing a Function Module . . . . . . . . . . . 6-2

Writing a UDF . . . . . . . . . . . . . . . . 6-2

Specifying Parameters . . . . . . . . . . 6-3

Specifying a Return Value . . . . . . . . 6-3

Character Datatypes . . . . . . . . . . . 6-3

Calling Conventions . . . . . . . . . . . 6-4

Thread-safe UDFs . . . . . . . . . . . . . . 6-4

Compiling and Linking a Function Module . . . 6-6

Creating a UDF Library . . . . . . . . . . . 6-7

Modifying a UDF Library. . . . . . . . . . . 6-7

Declaring a UDF to a Database. . . . . . . . . 6-7

Defining a Sample UDF with a Descriptor

Parameter . . . . . . . . . . . . . . . . . 6-9

Declaring UDFs with FREE_IT . . . . . . 6-12

UDF Library Placement . . . . . . . . . . 6-13

Calling a UDF. . . . . . . . . . . . . . . . . 6-14

Calling a UDF with SELECT. . . . . . . . 6-14

Calling a UDF with INSERT . . . . . . . . 6-15

Calling a UDF with UPDATE. . . . . . . . 6-15

Calling a UDF with DELETE. . . . . . . . 6-15

Writing a Blob UDF . . . . . . . . . . . . . . 6-15

Creating a Blob Control Structure . . . . . 6-15

Declaring a Blob UDF . . . . . . . . . . . 6-16

A Blob UDF Example . . . . . . . . . . . 6-17

The InterBase UDF Library . . . . . . . . . . 6-18

abs. . . . . . . . . . . . . . . . . . . . . 6-20

acos . . . . . . . . . . . . . . . . . . . . 6-20

ascii_char . . . . . . . . . . . . . . . . . 6-20

ascii_val . . . . . . . . . . . . . . . . . . 6-20

asin . . . . . . . . . . . . . . . . . . . . 6-21

atan . . . . . . . . . . . . . . . . . . . . 6-21

atan2. . . . . . . . . . . . . . . . . . . . 6-21

bin_and . . . . . . . . . . . . . . . . . . 6-21

bin_or . . . . . . . . . . . . . . . . . . . 6-21

bin_xor. . . . . . . . . . . . . . . . . . . 6-21

ceiling . . . . . . . . . . . . . . . . . . . 6-22

cos. . . . . . . . . . . . . . . . . . . . . 6-22

cosh . . . . . . . . . . . . . . . . . . . . 6-22

cot . . . . . . . . . . . . . . . . . . . . . 6-22

div . . . . . . . . . . . . . . . . . . . . . 6-22

floor . . . . . . . . . . . . . . . . . . . . 6-23

ln. . . . . . . . . . . . . . . . . . . . . . 6-23

log . . . . . . . . . . . . . . . . . . . . . 6-23

log10. . . . . . . . . . . . . . . . . . . . 6-23

lower. . . . . . . . . . . . . . . . . . . . 6-23

xi

ltrim. . . . . . . . . . . . . . . . . . . . . 6-23

mod . . . . . . . . . . . . . . . . . . . . 6-24

pi . . . . . . . . . . . . . . . . . . . . . . 6-24

rand . . . . . . . . . . . . . . . . . . . . 6-24

rtrim . . . . . . . . . . . . . . . . . . . . 6-24

sign. . . . . . . . . . . . . . . . . . . . . 6-24

sin . . . . . . . . . . . . . . . . . . . . . 6-25

sinh. . . . . . . . . . . . . . . . . . . . . 6-25

sqrt . . . . . . . . . . . . . . . . . . . . . 6-25

strlen . . . . . . . . . . . . . . . . . . . . 6-25

substr. . . . . . . . . . . . . . . . . . . . 6-25

tan . . . . . . . . . . . . . . . . . . . . . 6-26

tanh. . . . . . . . . . . . . . . . . . . . . 6-26

Declaring Blob Filters . . . . . . . . . . . . . 6-26

Chapter 7

Designing Database Applications

Using InterBase Databases . . . . . . . . . . .7-2

Local Databases . . . . . . . . . . . . . . . 7-2

Remote Database Servers. . . . . . . . . .7-2

Database Security . . . . . . . . . . . . . .7-3

Transactions . . . . . . . . . . . . . . . . .7-3

The Data Dictionary . . . . . . . . . . . . .7-4

Referential Integrity, Stored Procedures, and

Triggers. . . . . . . . . . . . . . . . . . .7-5

Database Architecture. . . . . . . . . . . . . . 7-6

Planning for Scalability. . . . . . . . . . . .7-6

Single-tiered Database Applications . . . . .7-8

Two-tiered Database Applications . . . . . .7-8

Multi-tiered Database Applications. . . . . .7-9

Designing the User Interface . . . . . . . . . 7-10

Displaying a Single Record . . . . . . . . 7-11

Displaying Multiple Records . . . . . . . . 7-11

Analyzing Data. . . . . . . . . . . . . . . 7-12

Selecting What Data to Show . . . . . . . 7-12

Chapter 8

Building Multi-

tiered Applications

Understanding Databases and Datasets . . . . 8-1

Using Transactions. . . . . . . . . . . . . .8-2

Using a Transaction Component . . . . .8-3

Caching Updates. . . . . . . . . . . . . . .8-4

Creating and Restructuring Database Tables 8-4

Using the Briefcase Model . . . . . . . . . .8-5

Scaling Up to a Three-tiered Application . . . .8-6

Creating Multi-tiered Applications . . . . . . . .8-6

Chapter 9

Introduction to IBX

Chapter 10

Connecting to Databases

Persistent and Temporary Database Components .

10-1

Using Temporary Database Components . 10-2

Creating Database Components at Design Time

10-2

Controlling Connections . . . . . . . . . . . 10-2

Controlling Server Login . . . . . . . . . . 10-2

Connecting to a Database Server . . . . . 10-3

Working with Network Protocols. . . . . . 10-4

Using ODBC. . . . . . . . . . . . . . . . 10-4

Disconnecting from a Database Server . . 10-4

Iterating Through a Database Component’s

Datasets . . . . . . . . . . . . . . . . . 10-5

Requesting Information about an Attachment 10-5

Database Characteristics . . . . . . . . . 10-5

Environmental Characteristics. . . . . . . 10-6

Performance Statistics. . . . . . . . . . . 10-7

Database Operation Counts . . . . . . . . 10-7

Requesting Database Information . . . . . 10-8

Chapter 11

Importing and Exporting Data

Exporting and Importing Raw Data . . . . . . 11-1

Exporting Raw Data . . . . . . . . . . . . 11-2

Importing Raw Data . . . . . . . . . . . . 11-2

Exporting and Importing Delimited Data . . . 11-3

Exporting Delimited Data . . . . . . . . . 11-3

Importing Delimited Data . . . . . . . . . 11-4

Chapter 12

Working with InterBase Services

Overview of the InterBase Service Components . .

12-1

About the Services Manager . . . . . . . 12-2

Service Component Hierarchy. . . . . . . 12-2

Attaching to a Service Manager . . . . . . 12-3

Detaching from a Service Manager . . . . 12-3

Setting Database Properties . . . . . . . . . 12-3

Bringing a Database Online . . . . . . . . 12-4

Shutting Down a Database . . . . . . . . 12-4

Setting the Sweep Interval. . . . . . . . . 12-5

Setting the Async Mode . . . . . . . . . . 12-5

Setting the Page Buffers. . . . . . . . . . 12-6

xii

Setting the Access Mode. . . . . . . . . . 12-6

Setting the Database Reserve Space . . . 12-6

Activating the Database Shadow. . . . . . 12-7

Adding and Removing Journal Files . . . . 12-7

Backing up and Restoring Databases. . . . . 12-7

Setting Common Backup and Restore

Properties. . . . . . . . . . . . . . . . . 12-8

Backing Up Databases. . . . . . . . . . . 12-8

Setting the Backup Options. . . . . . . 12-8

Displaying Backup Output . . . . . . . 12-9

Setting Up a Backup Component . . . . 12-9

Backing Up a Database to Multiple Files . .

12-10

Restoring Databases. . . . . . . . . . . 12-11

Setting the Database Cache Size. . . 12-11

Setting the Page Size. . . . . . . . . 12-11

Setting the Restore Options . . . . . 12-11

Displaying Restore Output . . . . . . 12-12

Setting up a Restore Component . . . 12-12

Restoring a Database from Multiple Backup

Files . . . . . . . . . . . . . . . . . 12-13

Restoring a Database to Multiple Files 12-14

Performing Database Maintenance . . . . . 12-15

Validating a Database . . . . . . . . . . 12-15

Displaying Limbo Transaction Information 12-16

Resolving Limbo Transactions . . . . . . 12-16

Requesting Database and Server Status Reports.

12-17

Requesting Database Statistics . . . . . 12-17

Using the Log Service. . . . . . . . . . . . 12-19

Configuring Users . . . . . . . . . . . . . . 12-19

Adding a User to the Security Database . 12-20

Listing Users in the Security Database. . 12-21

Displaying Information for a Single User . .

12-21

Displaying Information for All Users. . 12-21

Removing a User from the Security Database.

12-21

Modifying a User in the Security Database12-22

Displaying Server Properties . . . . . . . . 12-22

Displaying the Database Information. . . 12-22

Displaying InterBase Configuration Parameters

12-23

Displaying the Server Version . . . . . . 12-24

Chapter 13

Programming with

Database Events

Setting up Event Alerts . . . . . . . . . . . . 13-2

Writing an Event Handler . . . . . . . . . 13-2

Chapter 14

Working with Cached Updates

Deciding When to Use Cached Updates . . . 14-1

Using Cached Updates . . . . . . . . . . . . 14-2

Enabling and Disabling Cached Updates . 14-3

Fetching Records . . . . . . . . . . . . . 14-4

Applying Cached Updates . . . . . . . . . 14-4

Applying Cached Updates with a Database

Component Method. . . . . . . . . . 14-5

Applying Cached Updates with a Dataset

Component Methods . . . . . . . . . 14-6

Applying Updates for Master/detail Tables . .

14-6

Canceling Pending Cached Updates . . . 14-7

Cancelling Pending Updates and Disabling

Further Cached Updates . . . . . . . 14-8

Canceling Pending Cached Updates. . 14-8

Canceling Updates to the Current Record . .

14-8

Undeleting Cached Records. . . . . . . . 14-8

Specifying Visible Records in the Cache . 14-9

Checking Update Status. . . . . . . . . .14-10

Using Update Objects to Update a Dataset. .14-11

Specifying the UpdateObject Property for a

Dataset. . . . . . . . . . . . . . . . . .14-12

Using a Single Update Object . . . . .14-13

Using Multiple Update Objects . . . . .14-13

Creating SQL Statements for Update

Components . . . . . . . . . . . . . . .14-14

Creating SQL Statements at Design Time . .

14-14

Understanding Parameter Substitution in

Update SQL Statements . . . . . . .14-15

Composing Update SQL Statements. .14-16

Using an Update Component’s Query

Property. . . . . . . . . . . . . . . .14-18

Using the DeleteSQL, InsertSQL,

ModifySQL, and RefreshSQL Properties . .

14-19

Executing Update Statements . . . . . . .14-19

Calling the Apply Method. . . . . . . .14-19

Calling the SetParams Method. . . . .14-20

Calling the ExecSQL Method. . . . . .14-21

Using Dataset Components to Update a Dataset

14-22

Updating a Read-only Result Set. . . . . . .14-23

Controlling the Update Process. . . . . . . .14-23

xiii

Determining if you Need to Control the Updating

Process. . . . . . . . . . . . . . . . . 14-24

Creating an OnUpdateRecord Event Handler .

14-24

Handling Cached Update Errors . . . . . . 14-25

Referencing the Dataset to Which to Apply

Updates. . . . . . . . . . . . . . . . . 14-26

Indicating the Type of Update that Generated an

Error . . . . . . . . . . . . . . . . . . 14-26

Specifying the Action to Take . . . . . . 14-27

Chapter 15

Understanding Datasets

What is TDataSet? . . . . . . . . . . . . . . 15-2

Opening and Closing Datasets . . . . . . . . 15-2

Determining and Setting Dataset States . . . 15-3

Deactivating a Dataset . . . . . . . . . . . 15-5

Browsing a Dataset . . . . . . . . . . . . 15-6

Enabling Dataset Editing. . . . . . . . . . 15-6

Enabling Insertion of New Records . . . . 15-7

Calculating Fields . . . . . . . . . . . . . 15-8

Updating Records . . . . . . . . . . . . . 15-8

Navigating Datasets . . . . . . . . . . . . . . 15-8

Searching Datasets . . . . . . . . . . . . . . 15-9

Modifying Data . . . . . . . . . . . . . . . . 15-9

Using Dataset Events . . . . . . . . . . . . . 15-9

Aborting a Method . . . . . . . . . . . . . 15-9

Using OnCalcFields . . . . . . . . . . . 15-10

Using Cached Updates . . . . . . . . . . . 15-10

Chapter 16

Working with Queries

Queries for desktop developers . . . . . . . . 16-1

Queries for server developers. . . . . . . . . 16-2

When to use TIBDataSet, TIBQuery, and TIBSQL

16-3

Using a query component: an overview. . . . 16-3

Specifying the SQL statement to execute . . . 16-4

Specifying the SQL property at design time 16-5

Specifying a SQL statement at runtime . . 16-6

Setting the SQL property directly . . . . 16-6

Loading the SQL property from a file . . 16-7

Loading the SQL property from string list

object . . . . . . . . . . . . . . . . . 16-7

Setting parameters . . . . . . . . . . . . . . 16-7

Supplying parameters at design time . . . 16-8

Supplying parameters at runtime. . . . . . 16-9

Using a data source to bind parameters . 16-10

Executing a query . . . . . . . . . . . . . . 16-12

Executing a query at design time . . . . .16-12

Executing a query at runtime . . . . . . .16-12

Executing a query that returns a result set . .

16-13

Executing a query without a result set .16-13

Preparing a query . . . . . . . . . . . . . .16-13

Unpreparing a query to release resources . .16-14

Improving query performance. . . . . . . . .16-14

Disabling bi-directional cursors . . . . . .16-14

Working with result sets. . . . . . . . . . . .16-15

Updating a read-only result set . . . . . .16-15

Chapter 17

Working with Tables

Using table components . . . . . . . . . . . 17-1

Setting up a table component. . . . . . . . . 17-1

Specifying a table name . . . . . . . . . . 17-2

Opening and closing a table . . . . . . . . 17-3

Controlling read/write access to a table. . . . 17-4

Searching for records. . . . . . . . . . . . . 17-4

Sorting records . . . . . . . . . . . . . . . . 17-4

Retrieving a list of available indexes with

GetIndexNames . . . . . . . . . . . . . 17-5

Specifying an alternative index with IndexName

17-5

Specifying sort order for SQL tables. . . . 17-5

Specifying fields with IndexFieldNames . . . 17-5

Examining the field list for an index . . . . 17-6

Working with a subset of data. . . . . . . . . 17-6

Deleting all records in a table . . . . . . . . . 17-6

Deleting a table . . . . . . . . . . . . . . . . 17-7

Renaming a table . . . . . . . . . . . . . . . 17-7

Creating a table . . . . . . . . . . . . . . . . 17-7

Synchronizing tables linked to the same database

table . . . . . . . . . . . . . . . . . . . . . 17-9

Creating master/detail forms . . . . . . . . .17-10

Building an example master/detail form . .17-10

Chapter 18

Working with Stored Procedures

When Should You use Stored Procedures? . 18-2

Using a Stored Procedure . . . . . . . . . . 18-3

Creating a Stored Procedure Component . 18-3

Creating a Stored Procedure . . . . . . . 18-4

Preparing and Executing a Stored Procedure . .

18-5

Using Stored Procedures that Return Result

Sets . . . . . . . . . . . . . . . . . . . 18-6

Retrieving a Result Set with a TIBQuery 18-6

xiv

Using Stored Procedures that Return Data

Using Parameters . . . . . . . . . . . . 18-7

Retrieving Iindividual Values with a TIBQuery

18-7

Retrieving Individual Values with a

TIBStoredProc. . . . . . . . . . . . . 18-8

Using Stored Procedures that Perform Actions

on Data . . . . . . . . . . . . . . . . . . 18-8

Executing an Action Stored Procedure with a

TIBQuery . . . . . . . . . . . . . . . 18-9

Executing an action stored procedure with a

TIBStoredProc. . . . . . . . . . . . . 18-9

Understanding Stored Procedure Parameters18-10

Using Input Parameters . . . . . . . . . 18-11

Using Output Parameters . . . . . . . . 18-11

Using Input/output Parameters. . . . . . 18-12

Using the Result Parameter . . . . . . . 18-13

Accessing Parameters at Design Time . 18-13

Setting Parameter Information at Design Time

18-13

Creating Parameters at Runtime. . . . . 18-14

Viewing Parameter Information at Design Time . .

18-15

Chapter 19

Debugging with SQL Monitor

Building a Simple Monitoring Application . . . 19-1

Chapter 20

Writing Installation Wizards

Installing . . . . . . . . . . . . . . . . . . . 20-1

Defining the Installation Component. . . . 20-1

Setting the Installation Options . . . . . 20-2

Setting Up the Source and Destination

Directories . . . . . . . . . . . . . . 20-3

Setting Up the Installation Progress

Components . . . . . . . . . . . . . 20-3

Defining the Uninstall Component . . . . . . 20-4

Index 1

ix

1.1 Chapters in the Developer’s Guide.. . . .1-1

2.1 Run Directory Files . . . . . . . . . . . .2-9

4.1 Pros and cons of applet development . 4-24

4.2 InterClient extensions to JDBC . . . . . 4-26

4.3 Unsupported JDBC features . . . . . . 4-27

4.4 InterClient implementation of JDBC features

4-28

4.5 InterBase features not supported by

InterClient . . . . . . . . . . . . . . . . 4-29

4.6 Java SQL datatype support. . . . . . . 4-30

4.7 SQL to Java type conversions . . . . . 4-30

4.8 Java-to-SQL type conversions . . . . . 4-31

4.9 Data Source standard properties . . . . 4-32

4.10 Data Source Extended properties. . . . 4-33

4.11 Methods for the Statement and

PreparedStatement classes. . . . . . . 4-38

4.12 Methods and constructors for the new

BatchUpdateException class . . . . . . 4-39

4.13 Blob, Clob, and other related API Interfaces

4-41

6.1 Microsoft C compiler options . . . . . . .6-6

6.2 Arguments to DECLARE EXTERNAL

FUNCTION . . . . . . . . . . . . . . . .6-8

6.3 Explanation of the structure fields. . . . 6-10

6.4 Explanation of dsc_type . . . . . . . . 6-11

6.5 Explanation of dsc_flags . . . . . . . . 6-12

6.6 Fields in the Blob struct. . . . . . . . . 6-16

6.7 Function declarations for the InterBase UDF

library . . . . . . . . . . . . . . . . . . 6-18

7.1 Data Dictionary interface . . . . . . . . . 7-4

10.1 TIBDatabaseInfo database characteristic

properties. . . . . . . . . . . . . . . . 10-6

10.2 TIBDatabaseInfo environmental

characteristic properties . . . . . . . . 10-6

10.3 TIBDataBaseInfo performance properties . .

10-7

10.4 TIBDatabaseInfo database operation count

properties. . . . . . . . . . . . . . . . 10-8

12.1 Database shutdown modes. . . . . . . 12-4

12.2 Common backup and restore properties 12-8

12.3 TIBBackupService options . . . . . . . 12-9

12.4 TIBRestoreService options . . . . . . .12-12

12.5 TIBValidationService options . . . . . .12-15

12.6 TIBValidationService actions . . . . . .12-17

12.7 TIBStatisticalService options . . . . . .12-18

12.8 TIBSecurityService properties . . . . .12-20

14.1 TIBUpdateRecordType values . . . . . 14-9

14.2 Return values for UpdateStatus . . . .14-11

14.3 UpdateKind values . . . . . . . . . . .14-26

14.4 UpdateAction values . . . . . . . . . .14-27

15.1 Values for the dataset State property. . 15-3

15.2 Dataset events . . . . . . . . . . . . . 15-9

15.3 Properties, events, and methods for cached

updates. . . . . . . . . . . . . . . . .15-11

20.1 TIBInstall properties . . . . . . . . . . 20-1

20.2 TIBInstall options . . . . . . . . . . . . 20-2

Tables

x

ix

2.1 Basic client/server relationship . . . . . .2-2

2.2 Role of the InterBase client library. . . . .2-3

2.3 InterBase ToGo embedded in a database

application . . . . . . . . . . . . . . . . .2-7

4.1 Connection dialog . . . . . . . . . . . . .4-2

4.2 InterClient architecture. . . . . . . . . . .4-7

4.3 JDBC interfaces . . . . . . . . . . . . . .4-9

4.4 Using applets to access InterBase. . . . 4-23

4.5 Using standalone Java applications to access

InterBase . . . . . . . . . . . . . . . . 4-25

7.1 User-interface to dataset connections in all

database applications . . . . . . . . . . .7-7

7.2 Single-tiered database application

architecture . . . . . . . . . . . . . . . .7-8

7.3 Two-tiered database application architecture .

7-8

7.4 Multi-tiered database architectures . . . . 7-9

12.1 InterBase service component hierarchy. 12-2

15.1 InterBase database component dataset

hierarchy . . . . . . . . . . . . . . . . 15-1

15.2 Relationship of Inactive and Browse states . .

15-5

15.3 Relationship of Browse to other dataset states

15-6

16.1 Sample master/detail query form and data

module at design time. . . . . . . . . .16-11

Figures

x

Chapter 1 Using the InterBase Developer’s Guide 1-1

Chapter

Chapter 1Using the InterBase

Developer’s Guide

The InterBase Developer’s Guide focuses on the needs of developers who use the

the development tools: Delphi, C++ Builder, and JBuilder. It assumes a general

familiarity with SQL, data definition, data manipulation, and programming practice.

Note For additional information and support on Embarcadero’s products, please refer to

the Embarcadero web site at http://www.embarcadero.com.

Topics covered in this book

The following table lists the chapters in the Developer’s Guide and provides a brief

description of each one:

Table 1.1 Chapters in the Developer’s Guide.

Chapter Description

Chapter 1, “Using the InterBase

Developer’s Guide” Intended audience and coverage

Chapter 2, “Client/Server Concepts” Architecture of client/server systems using

InterBase, including the definition of client and

server, and options for application development

Chapter 3, “Programming

Applications with Delphi or C++

Builder”

Programming InterBase applications using the

Database Engine (BDE) with Delphi and C++

Builder

Chapter 4, “Programming with JDBC” Building InterBase applications using InterClient,

InterServer, and JBuilder

Chapter 1 Using the InterBase Developer’s Guide 1-2

Topics covered in this book

Chapter 5: “Programming

Applications with ODBC” Programming InterBase applications with ODBC

and OLE DB; programming with the ODBC

driver and configuring and using ODBC data

sources

Chapter 6, “Working with UDFs

and Blob Filters” Working with UDFs: UDF libraries, declaring the

functions to the database, calling the functions;

working with Blob filters

Chapter 7: “Designing Database

Applications” Designing a database application, including the

decisions involved in designing a user interface,

how to use databases, and database

architecture

Chapter 8: “Building Multi-

tiered Applications” Understanding the elements of a database

application, including datasets and transactions

Chapter 9: “Introduction to IBX” Using InterBase Express (IBX) data access

components

Chapter 10: “Connecting to

Databases” Database components and how to manipulate

database connections

Chapter 11: “Importing and Exporting

Data” Using IBX components to migrate data to and

from a database

Chapter 12: “Working with InterBase

Services” Building InterBase services into your

applications

Chapter 13: “Programming with

Database Events” Using an IBX TIBEvents component to manage

InterBase server events

Chapter 14: “Working with Cached

Updates” Working with cached updates, and using the

TIBUpdateSQL component to update datasets

Chapter 15: “Understanding

Datasets” Working with datasets and TDataSet

Chapter 17: “Working with Tables” Working with tables and TIBTable

Chapter 16: “Working with Queries” Building queries with TIBDataSet and

TIBQuery

Chapter 18: “Working with Stored

Procedures” Using InterBase stored procedures in your

database applications

Chapter 19: “Debugging with SQL

Monitor” Using TIBSQLMonitor to monitor the dynamic

SQL passing through the InterBase server

Chapter 20: “Writing Installation

Wizards” Using TIBSetup, TIBInstall, and TIBUninstall to

build an InterBase installation program

Table 1.1 Chapters in the Developer’s Guide.

Chapter Description

Chapter 2 Client/Server Concepts 2-1

Chapter

Chapter 2Client/Server Concepts

This chapter describes the architecture of client/server systems using InterBase.

The chapter covers topics including the definition of and InterBase client and

server, and options for application development.

Definition of a Client

An InterBase client is an application, typically written in C, C++, Delphi or Java,

that accesses data in an InterBase database.

In the more general case, an InterBase client is any application process that uses

the InterBase client library, directly or via a middleware interface, to establish a

communication channel to an InterBase server. The connection can be local if the

application executes on the same node as the InterBase server, or remote if the

application must use a network to connect to the InterBase server.

Chapter 2 Client/Server Concepts 2-2

The InterBase Client Library

Figure 2.1 Basic client/server relationship

InterBase is designed to allow clients to access an InterBase server on a platform

and operating system different from the client’s platform and operating system.

The InterBase Client Library

The InterBase client library provides functions that developers of client

applications use to initiate connections to a server and to programmatically

perform database operations. The library uses the operating system’s client

network interface to communicate with one or more InterBase servers, and

implements a special InterBase client/server application protocol on top of a

network protocol (see “Network protocols” in the Operations Guide).

The client library provides a set of high-level functions in the form of an Application

Programmer’s Interface (API) for communication with an InterBase server. All

client applications or middleware must use this API to access InterBase

databases. The API Guide provides reference documentation and guidelines for

using the API to develop high-performance applications.

ibserverApplication

Client node Server node

.ib

Network

Chapter 2 Client/Server Concepts 2-3

Definition of a Server

Figure 2.2 Role of the InterBase client library

Definition of a Server

The InterBase server is a software process that executes on the node that hosts

the storage space for databases. The server process is the only process on any

node that can perform direct I/O to the database files.

Clients send to the server process requests to perform actions on the database,

including:

• Search the database based on criteria

• Collate, sort and tabulate data

• Return sets of data

• Modify data values

• Insert new data into the database

• Remove data from the database

• Create new databases or data structures

• Execute procedural code on the server

• Send messages to other clients currently connected

The server process is fully network-enabled; it services connection requests that

originate on another node. The server process implements the same InterBase

application protocol that the client uses.

Many clients can remain connected to the multi-threaded server process

simultaneously. The server regulates access to individual data records within the

database, and enforces exclusive access to records when clients request to

modify the data in the records.

ibserver

Application

Client node Server nodeNetwork

DBX

GDS32.DLL

.gdb

.ib

Chapter 2 Client/Server Concepts 2-4

Application Development

Once you create and populate a database, you can access the information through

an application. If you use one of Embarcadero’s client tools, you can access

information through your existing application. You can also design and implement

a new application by embedding SQL statements or API calls in an application

written in a programming language such as C or C++.

Client Tools Applications

Client tools such as Delphi and C++ Builder can access InterBase databases

using Database Express (DBX). Server query reporting is built into the client tool

providing Windows application support. This enables you to build sophisticated,

user-friendly database applications with minimal programming effort.

InterBase Express (IBX) for Delphi and C++ Builder

IBX is a library of components that allows Delphi and C++ Builder programmers to

access InterBase features.The version of IBX that ships with Delphi and C++

Builder does not access the latest InterBase features. An enhanced version of IBX

ships with InterBase.

dbExpress (DBX)

dbExpress is a set of database drivers that provide fast access to a variety of SQL

database servers. For each supported type of database, dbExpress provides a

driver that adapts the server-specific software to a set of uniform dbExpress

interfaces. When you deploy a database application that uses dbExpress, you

need only include a dll (the server-specific driver) with the application files you

build.

dbExpress lets you access databases using unidirectional datasets, which are

designed for quick lightweight access to database information, with minimal

overhead. Unidirectional datasets can only retrieve a unidirectional cursor. They

do not buffer data in memory, which makes them faster and less resource-

intensive than other types of dataset. However, because there are no buffered

records, unidirectional datasets are also less flexible than other datasets. For

example, the only supported navigation methods are the First and Next methods.

Most others raise exceptions. Some, such as the methods involved in bookmark

support, simply do nothing.

There is no built-in support for editing because editing requires a buffer to hold the

edits. The CanModify property is always False, so attempts to put the dataset into

edit mode always fail. You can, however, use unidirectional datasets to update

data using an SQL UPDATE command or provide conventional editing support by

using a dbExpress-enabled client dataset or connecting the dataset to a client

dataset.

Page is loading ...

Embarcadero INTERBASE XE7 Update 1 Developer's Guide

Embarcadero INTERBASE XE7 Update 1 Developer's Guide

Related papers

Other documents