Troubleshooting Common Connection Problems

If you have problems establishing an InterBase connection with SQL Link, try to isolate the problem the following way 1. Run the Connection Utility (CONNECT.EXE) to determine if you can connect to the InterBase server from your client workstation. If CONNECT does not work Consult your database administrator. If CONNECT works Continue with step 2. 2. Verify that your InterBase SQL Link driver is correctly installed. Reinstall SQL Link by following the procedures in Getting Started. Also, check...

Connecting to a Database

Choose File I Connect to Database to connect to an existing database. If Windows ISQL is currently connected to a database, the connection will be closed a dialog box will prompt you to commit changes to it (if there are any). If you choose No, then all database changes since the last commit will be rolled back and the connection will be closed. If you choose Yes, then database changes will be committed. Then the Database Connect dialog box will open The Server text field contains 'local' and...

Working With Transactions

All SQL data definition and data manipulation statements take place within the context of a transaction, a set of SQL statements that works to carry out a single task. This chapter explains how to open, control, and close transactions using the following SQL transaction management statements Table 3-1 SQL Transaction Management Statements SET TRANSACTION Starts a transaction, assigns it a name, and specifies its behavior. The following behaviors can be specified Access mode describes the...

Data Type Default Value and Check Constraints

CUST_NO (CUSTNO) FOREIGN KEY - references CUSTOMER (CUST_NO) SALES_REP (EMPNO) FOREIGN KEY - references EMPLOYEE (EMP_NO) ORDER_DATE SHIP_DATE DATE_NEEDED PAID INTEGER NOT NULL CHECK (VALUE > 1000) (PRODTYPE) VARCHAR(12) NOT NULL DEFAULT 'software' CHECK (VALUE IN ('software', 'hardware', 'other', 'N A')) Computed by (ship_date - order_date) Several checks are performed on the SALES table, among them A sale order must have a status open, shipped, waiting. The ship date must be entered, if...

Creating and Executing SQL Files

The basic steps for using script files with Windows ISQL are Create the file using a text editor. Execute the file with Windows ISQL. View output and confirm database changes with Windows ISQL. You can use any text editor to create an ISQL script file, as long as the final file format is plain text (ASCII). Every ISQL script file must begin with either a CREATE DATABASE statement or a CONNECT statement (including user name and password) to specify the database on which the script file operates....

Generating Unique Column Values With Triggers

Recall the EMPLOYEE table in the example database. This table has a primary key column named EMP_NO for each employee's employee number. Because it is a primary key, each employee number must be unique. And, generally, employee numbers are sequential. So, each time you insert a new employee record in this table, you would have to remember what the last employee number issued was, and then give the new employee the next number. This would be cumbersome and error-prone. Triggers provide a simple...

Starting and Exiting Windows ISQL

To start Windows ISQL, double-click on the Windows ISQL icon in the Delphi program group. The ISQL window will open The ISQL window can also be opened from the Server Manager by choosing Tasks I Interactive SQL or clicking on the corresponding Speedbar button. Windows ISQL will then be connected to Server Manager's current database (if any). The Interactive SQL window consists of a menu bar with pull-down menus, the SQL Statement area, the ISQL Output area, control buttons, and a status bar at...

What is the Borland Local Inter Base Server

The Borland Local InterBase Server is a single-user Windows-based version of Borland's InterBase Workgroup Server, an SQL-compliant relational database management system RDBMS . The Local InterBase Server includes Windows ISQL and the Server Manager, a Windows tool that can be used with Local InterBase Server or a remote InterBase server. Using the Local Interbase Server, you can access local databases through Windows ISQL or through a SQL application program. Figure 1-1 shows the relationships...

Inserting Data From an External File

Note This section covers an optional topic and may be skipped without losing any continuity. However, it is an important topic not covered in detail elsewhere in the documentation. An external table is a special kind of table that stores its data in an ASCII file separate from the database. It may occasionally want to import data from an ASCII file into a database for example, if the data was originally entered in another application or at a remote location . You can populate a table with data...

Column Name Data Type Default Value and Check Constraints

MNGR_NO EMPNO SMALLINT Nullable FOREIGN KEY - references EMPLOYEE EMP_NO BUDGET BUDGET DOUBLE PRECISION Nullable CHECK VALUE gt 10000 AND VALUE lt 2000000 PHONE_NO PHONENUMBER VARCHAR 20 Nullable The JOB table contains a record for each job in the company. The three columns JOB_CODE, JOB_GRADE, and JOB_COUNTRY are the primary key that uniquely identifies a job. JOB_COUNTRY references the COUNTRY table, which identifies the currency of each country. Table C-6, JOB Table shows the contents of the...

Long Driver Name Short Driver Name InterBase Subtype

Paradox ascii ascii 0 default , 1, 100, 101 Borland DAN Latin-1 BLLT1DA0 139 Long Driver Name Short Driver Name InterBase Subtype Long Driver Name Short Driver Name InterBase Subtype Note For information on InterBase subtypes that correspond to dBASE language drivers, contact Borland Technical Support. Note For information on InterBase subtypes that correspond to dBASE language drivers, contact Borland Technical Support.

Performing an Immediate Database Sweep

To perform a database sweep, choose Maintenance I Database Sweep from the menu bar in the Database Maintenance window. This operation runs an immediate sweep of the database, releasing space held by records which were rolled back and by out-of-date record versions. Sweeps are also done automatically at a specified interval see Adjusting Database Sweeping, in this chapter. Important Sweeping a database does not require it to be shut down. You can perform sweeping at any time, but it can impact...

Testing the Inter Base Connection

To test whether you can connect to InterBase successfully, use the InterBase Connection Utility CONNECT.EXE . This utility is stored in the same directory as the BDE files. 1. Choose File I Run from the Program Manager menu bar. The Run dialog box appears. 2. In the Command Line text box, enter the command to run CONNECT. If you installed BDE files in C BDE, the command is C BDE CONNECT.EXE. 3. Choose OK. The InterBase Connect Utility dialog box appears. 4. Enter information in each text box 4....

Connecting to a Data Source Using a Logon Dialog

Some ODBC applications display a Logon dialog box when you are connecting to a data source. For InterBase, the dialog box is as follows In this dialog box, do the following 1. Enter the name of the server and database you want to access case-sensitive or click the arrow to the right of the box to select a server name you specified in the Setup dialog box. This must be a full connection string including the server name, the network protocol separator, directory path and database file name, as...

Configuring Data Sources

If you have an ODBC administrator installed on your system, you can configure an InterBase data source as follows 1. Start the ODBC Administrator by double-clicking on the ODBC icon in the Control Panel application in the Main program group. A dialog box with a list of data sources appears. 2. If you are configuring a new data source, click Add. A list of installed drivers appears. Select Borland InterBase, and click OK. If you are configuring an existing data source, select the data source...

Restore Options

The restore options are shown in check boxes on the right side of the Database Restore dialog box. If a check box has an X inside, then the option is selected. If the box is empty, the option is not selected. Start Page o Page Size 11024 i Replace Existing Database l i Commit Alter Each Table j Restore Without Shadow Deactivate indexes l i Do Not Restore Validity Conditions r Verbose Output The Start Page is the page on which to start the restore. In most cases, this should be left as the...

Inter Base Data Type Translations

Certain database operations cause SQL Link to convert data from Paradox or dBASE format to InterBase format. For example, a BDE application that copies or appends data from a local table to an InterBase table causes SQL Link to convert the local data to InterBase format before performing the copy or append operation. Other database operations cause a conversion in the opposite direction, from InterBase format to Paradox or dBASE format. For example, suppose you run a local query against one or...

Starting a Transaction With Set Transaction

SET TRANSACTION issued without parameters starts a transaction with the following default behavior READ WRITE WAIT ISOLATION LEVEL SNAPSHOT The following table summarizes these settings Table 3-2 Transaction Default Behavior Parameter Setting Purpose Access Mode READ WRITE Access mode. This transaction can select, insert, update, Lock Resolution WAIT Lock resolution. This transaction waits for locked tables and rows to be released to see if it can then update them before reporting a lock...

Configuring Database Properties

To view and configure database properties, choose Database Properties from the menu bar in the Database Maintenance window or click on the Database Properties SpeedBar button. The Database Properties dialog box will then appear This dialog box contains a Summary Information area that displays properties but does not allow modification of them and a Configuration area that does allow modification of the parameters. User name of the database owner. Secondary file names and sizes. The...

Extracting Metadata

Windows ISQL enables you to extract metadata for the entire database and for a specific table or view. To extract data definition statements metadata from a database to a file, choose Extract SQL Metadata for Database The following dialog box will open If you choose Yes, then another dialog box will open, enabling you to enter the name of the file to which to extract the metadata. If you choose No, then the metadata will be displayed to the ISQL Output area only. If you choose Cancel, then the...

Restoring to Multiple Files

You might want to restore a database to multiple files to distribute it among different disks, which provides more flexibility in allocating system resources. To restore a database to multiple database files, click on the Multi-file button in the Database Restore dialog box. The following dialog box opens To specify the database files to restore to, type the file name in the File Path text field and then type the number of pages for that file in the text field below it. The minimum number of...

Borland Language Drivers for Inter Base

The following table lists language drivers available for use with InterBase and their corresponding InterBase subtypes. The language driver you choose must use the same collation sequence as your server, and the same character set as the one your server uses to pass data to your BDE application. The default can be set at either a database or a table level. InterBase supports subtypes for different fields in the same relation. However, rules of a language driver you specify will apply to a...

Database

Relational databases store all their data in tables. A table is a data structure consisting of an unordered set of horizontal rows, each containing the same number of vertical columns. The intersection of an individual row and column is a field that contains a specific piece of information. Much of the power of relational databases comes from defining the relations among the tables. InterBase stores information about metadata in special tables, called system tables. System tables have...

Changing Windows ISQL Settings

The Session menu enables you to change ISQL settings for the current session and display information about the database and its metadata. Choose Session I Basic ISQL Settings to open a dialog box displaying all the basic settings that can be toggled on or off Each setting has a corresponding check box. If there is an X in the box, then the setting is on. Otherwise, it is off. Click on the check box or the setting name to toggle the setting. The following table summarizes basic ISQL settings...

Isql Set Statements

SET Statements are used to configure the ISQL environment from a script file. Changes to the session setting from SET statements in a script affect the session only while the script is running. After a script completes, the session settings prior to running the script will be restored. You cannot enter ISQL SET statements interactively in the SQL Statement area. When using ISQL interactively, perform these same functions with the Session menu items. SET GENERATOR and SET TRANSACTION without a...

Validation Options

You can select three options with Database Validation By default, database validation reports and releases only page structures. When you select the Validate record fragments option, validation reports and releases record structures as well as page structures. By default, validating a database updates it, if necessary. To prevent updating, select the Read-only validation option. A checksum is a page-by-page analysis of data to verify its integrity. A bad checksum means that a database page has...

System Requirements

The Local InterBase Server installation program asks if you want to install the InterBase ODBC driver. If you choose to install the InterBase ODBC driver, the installation program copies all necessary DLLs to the WINDOWS SYSTEM directory, and set up Borland InterBase as the driver name and InterBase as the database source. If you attempt to configure a data source and you do not have the INTERBASE directory on your path or the driver DLLs in your WINDOWS SYSTEM directory, the following message...

Set Blobdisplay

Specifies subtype of BLOB data to display. Syntax set blobdisplay n all off Integer specifying the BLOB subtype to display. Use 0 for BLOB data of an unknown subtype use 1 default for BLOB data of a text sub type, and other integer values for other subtypes. Turn off display of BLOB data of all subtypes. Description SET BLOBDISPLAY has the following uses To display BLOB data of a particular subtype, use SET BLOBDISPLAY n. By default, ISQL displays BLOB data of text subtype n 1 . To display BLOB...

Ending a Transaction

When a transaction's tasks are complete, or an error prevents a transaction from completing, the transaction must be ended to set the database to a consistent state. There are two statements that end transactions COMMIT makes a transaction's changes permanent in the database. It signals that a transaction completed all its actions successfully. ROLLBACK undoes a transaction's changes, returning the database to its previous state, before the transaction started. ROLLBACK is typically used when...

Using Data Definition Files

To define the rest of the domains in the database, you can use a data definition file. A data definition file also referred to as an ISQL script file contains ISQL statements, and is created with an editor such as Windows Notepad and run by Windows ISQL. Data definition files can be very useful, because you can enter multiple SQL statements with all the tools that a text editor provides, including cut, copy, and paste. This makes repetitive tasks much easier. In practice, most data definition...

Simple Select Procedure

The first procedure defined in PROCS.SQL is named GET_EMP_PROJ CREATE PROCEDURE GET_EMP_PROJ EMP_NO SMALLINT RETURNS PROJ_ID CHAR 5 AS FOR SELECT PROJ_ID FROM EMPLOYEE_PROJECT WHERE EMP_NO EMP_NO INTO PROJ_ID DO This is a select procedure that takes an employee number as its input parameter EMP_NO, specified in parentheses after the procedure name and returns all the projects to which the employee is assigned PROJ_ID, specified after RETURNS . It uses a FOR SELECT . . . DO statement to retrieve...

Pattern Matching

Besides comparing values, search conditions can also test character strings for a particular pattern. If data is found that matches a given pattern, the row is retrieved. There are a great many pattern matching operators. This section will only discuss some of the most commonly used ones LIKE, STARTING WITH, IS NULL, and BETWEEN. The LIKE operator lets you use wildcard characters in matching text. Wildcard characters are characters that have special meanings when used in a search condition. A...

Set Count

Specifies whether to display number of rows retrieved by queries. Turns on display of the rows returned Turns off display of the rows returned Description By default, when a SELECT statement retrieves rows from a query, no message appears to say how many rows were retrieved. Use SET COUNT ON to change the default behavior and display the message. To restore the default behavior, use SET COUNT OFF. Tip The ON and OFF keywords are optional. If they are omitted, SET COUNT switches from one mode to...

Using CAST to Convert Data Types

Normally, only similar data types can be compared in search conditions, but you can work around this by using CAST . Use the CAST function in search conditions to translate one data type into another. The syntax for CAST is CAST lt value gt NULL AS datatype For example, the following WHERE clause uses CAST to translate a CHAR data type, INTERVIEW_DATE, to a DATE data type. This conversion lets you compare INTERVIEW_DATE to another DATE column, HIRE_DATE . . . WHERE HIRE_DATE CAST INTERVIEW_DATE...

Ending the ISQL Session

Whenever you finish your work with ISQL, you should commit it to make it per- pg manent. Choose File I Commit Work. If you want to continue the tutorial, do not exit Windows ISQL continue to the next chapter. If you've had enough for now, you can end your ISQL session by choosing File I Exit to disconnect from the database and exit ISQL. If you want to keep Windows ISQL running, you can choose File I Disconnect from Database to disconnect from the database only. Now that you have gained some...

Twophase Commit and Transaction Recovery

When committing a transaction that spans multiple databases, InterBase automatically performs a two-phase commit. A two-phase commit guarantees that the transaction updates either all of the databases involved or none of them data is never partially updated. In the first phase of a two-phase commit, InterBase prepares each database for the commit by writing the changes from each subtransaction to the database. A subtransaction is the part of a multi-database transaction that involves only one...

Removing Duplicate Rows With Distinct

Suppose you want to retrieve a list of all the valid job codes in the EMPLOYEE database. Enter this query As you can see, the results of this query are rather long, and some job codes are repeated a number of times. What you really want is a list of job codes where each value returned is distinct from the others. To eliminate duplicate values, use the DISTINCT keyword. Revise the previous query by clicking on the Previous button and editing the command as follows As you can see, each job code...

Deleting Data Using a Subquery

The previous section used a subquery to update data. DELETE statements can also use subqueries. To remove all employees who are in the same department as Katherine Young, including Katherine Young herself, you could first determine Katherine Young's department number WHERE FULL_NAME Young, Katherine This query returns 623 as the department number. Then, using 623 as the search condition in a DELETE, you would enter The other way to remove the desired rows is to combine the two previous...

Backup Options

The backup options are indicated by check boxes on the right side of the Database Backup dialog box. If a check box has an X inside, then the option is selected. If the box is empty, the option is not selected. r Transportable Format r Backup M_etadata Only r Disable Sarbage Collection Ignore Transactions In Limbo I- ignore Checksums r Verbose Output To move a database to a machine with a different operating system from the machine on which the backup was performed, check the Transportable...

Isql -i Interbase

This chapter introduces some fundamental database concepts, and illustrates them with simple examples in Windows ISQL. Some of the basic database tasks include Adding data to tables and modifying the data. Retrieving data from tables. If you are new to SQL, read this chapter for an introduction to SQL database concepts, and the Windows ISQL tool. Then move on to the following chapters that provide detailed SQL tutorials. If you are an experienced SQL programmer, skim this chapter for an...

ODBC Conformance Level

The InterBase driver supports the Core, Level 1, and Level 2 API functions listed below. The following table lists the connection options supported by the ODBC driver. Table D-5 ODBC Connection Options Supported Not Supported SQL_CURRENT_QUALIFIER SQL_ACCESS_MODE SQL_OPT_TRACE SQL_OPT_TRACEFILE SQL_QUIET_MODE SQL_TRANSLATE_OPTION SQL_TXN_ISOLATION The following table lists statement options supported by the ODBC driver.

Testing Referential Integrity

InterBase databases include a feature called referential integrity. Referential integrity in its simplest form are constraints placed upon data by primary and foreign key definitions. When you defined the EMPLOYEE table, you made EMP_NO its primary key and DEPT_NO its foreign key, referencing the DEPARTMENT table. What this means is that each row in the EMPLOYEE table must have a unique value for the EMP_NO column and the value of the DEPT_NO column must match a value in the DEPARTMENT table....