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...

Using the String Operator in Search Conditions

The string operator, also referred to as a concatenation operator, II, joins two or more character strings into a single string. Character strings can be constants or HP values retrieved from a column. For example, enter the following SELECT DEPARTMENT, LAST_NAME is the manager FROM DEPARTMENT, EMPLOYEE WHERE MNGR_NO EMP_NO Corporate Headquarters Bender is the manager Sales and Marketing MacDonald is the manager Engineering Nelson is the manager Finance Steadman is the manager Quality Assurance...

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...

Using the Where Clause

The WHERE clause of the SELECT statement follows the SELECT and FROM clauses. If an ORDER BY clause is used, the WHERE clause must precede it. The WHERE clause tests data to see whether it meets certain conditions, and the SELECT statement only returns the rows that meet the condition. For example, the statement SELECT LAST_NAME, FIRST_NAME, PHONE_EXT FROM EMPLOYEE WHERE LAST_NAME Green returns only rows for which LAST_NAME is Green. The text following the WHERE keyword, in this case is called...

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...

Deleting Data

To remove one or more rows of data from a table, use the DELETE statement. A simple DELETE has the following syntax As with UPDATE, the WHERE clause specifies a search condition that determines the rows to delete. Search conditions can be combined or can be formed using a subquery. Caution A WHERE clause is not required in a DELETE statement. If you fail to include a WHERE clause, you will delete all rows in the table. Enter the following statement to delete rows from the EMPLOYEE table for...

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...

Logical Operators

Up until now, the examples presented have included only one search condition. However, you can include any number of search conditions in a WHERE clause by combining them with the logical operators AND or OR. When AND appears between search conditions, both conditions must be true for a row to be retrieved. For example, enter this query SELECT DEPT_NO, LAST_NAME, FIRST_NAME, HIRE_DATE FROM EMPLOYEE WHERE DEPT_NO 623 AND HIRE_DATE gt 01-Jan-1992 The query returns information on employees in...

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...

Controlling Performance of Forced Writes

When InterBase performs forced writes also referred to as synchronous writes , it physically writes data to disk whenever the database performs an internal write operation. If forced writes are not enabled, then even though InterBase performs a write, the data may not be physically written to disk, because operating systems buffer disk writes. If there is a system failure before the data is written to disk, then information can be lost. Performing forced writes ensures data integrity and...

Set Stats

Specifies whether to display performance statistics after the results of a query. Turns on display of performance statistics. Turns off display of performance statistics default . Description By default, when a SELECT statement retrieves rows from a query, ISQL does not display performance statistics after the results. Use SET STATS ON to change the default behavior and display performance statistics. To restore the default behavior, use SET STATS OFF. Performance statistics include Current...

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....