Pervasive SQL

If you are interested in learning about Pervasive SQL, this is the site. Our online tutorial provides a basic introduction to Pervasive SQL. It covers the create, import, retrieve, order, update, delete, insert, and join commands.

Table of Contents

Introduction

This tutorial is on Pervasive SQL, also known as Scalable SQL, version 3.01 by Btrieve Technologies, Inc. It was developed for users of Solomon IV version 2.0x; however, anyone using Scalable SQL will find it useful. You will need a copy of Scalable SQL to complete the exercises. 

SQL

SQL -- pronounced sequel -- stands for Structured Query Language. It is the language used to access Scalable SQL for Windows data. With SQL, you can query your database in a variety of ways, using English-like statements. 

Tables

A table is a set of columns and rows.  Each column is referred to as a field. Each value in a field represents a single type of data. For example, a table might have three fields: name, city, and state. The table will consist of three columns: one for name, one for city, and one for state.  For every row in the table, the name field contains the name, the city field contains the city, and the state field contains the state. 

Scalable SQL for Windows

Scalable SQL for Windows contains tables used to store, retrieve, and modify data. As you enter data into Solomon IV, it is stored in Scalable SQL tables. You can retrieve, add to, and modify that data by using Scalable SQL for Windows. 

Definition of Database

A database is a collection of related data. It can also be viewed as a collection of related tables. 

Tables Used by Solomon IV

To find a list of the tables used by Solomon IV, follow the steps outlined below: 

  1. Log in to Solomon IV.
  2. Click on Help on the Menu bar.
  3. Click on Solomon Data Fields on the drop-down menu.
  4. Click on Solomon IV Application Database Schema. You will be presented with a screen that lists each module.
  5. To see a list of the tables used by a particular module, click on the module name.
  6. Then to see a list of the fields in a specific table, click on the table name. You will be presented with a report.
  7. To print the report, click on Print.

Reading the Reports

SQL reports look similar to the ones shown here. 

Vendor Training TrnVendor
 

Add1 String 30 Vendor street address
City String 30 Vendor city
CurrBal Float 8 Current outstanding balance
ExpAcc String 10 Expense account
Name String 30 Vendor name
State String 3 Vendor city
Vendid String 10 Vendor ID

AP Training Documents TrnAPDoc

CuryDocBal Float 8 Current document balance
DocDate Date 4 Date the document was created
OpenDoc Logical 2 Is the document unpaid?
OrigDocAmt Float 8 Amount of the original document
RefNbr String 10 Reference number
Vendid String 10 Vendor ID

The first column of the report lists field names. The second column indicates field types. The third column indicates field lengths. The fourth and final column provides a description of the field contents. 

The tables just described are similar to the ones used by Solomon IV. We will create these tables later and use them in our exercises. 

Field Type

Field types define the kind of data the field can contain. Scalable SQL supports four major field types: 

String 
Numeric 
Date and time 
Boolean 

String -- A string field stores characters. It can also store numbers; however, a number cannot be used in mathematical calculations unless it is first converted to a numeric. Scalable SQL supports the following types of strings: 

character 
lstring1
zstring1
note1
lvar 1

Numeric -- A numeric field stores numbers. You can use the field to perform mathematical calculations. Scalable SQL supports the following types of numeric fields: 

Decimal1
Float1
Integer1
Numeric1

Logical -- A logical field will accept one of only two specified values. Examples are true or false and yes or no. 

Date and time -- Date and time fields accept the date and time. 

Length

The length column specifies the maximum number of characters the field will accept. 

SQLScope

SQLScope is the interface used to access and manipulate data stored in a Scalable SQL for Windows database. The following sections describe the interface. 

  1. To log in to SQLScope: 
  2. Open SQLScope.
  3. In the Database Name field, enter the name of the database you wish to open.

    Note: For these exercises, use a test database.

  4. Type Master in the User Name field.
  5. If a password has been assigned, enter the password in the Password field.
  6. Click on Login.

The Menu Bar

The Menu bar is located at the top of the SQLScope screen. It begins with the word File and continues with Edit, View, Settings, Database, Run, Window, and Help. You use the menus to give instructions to the software. To use a menu, you point with your mouse at the menu option and click the left mouse button. A drop-down menu will appear. To select an option, highlight the item on the drop-down menu and press Enter. An ellipse after a menu item signifies additional options; if you select that menu item, a dialog box will appear. 

The View Options

The View options on the Menu bar control how the SQL Scope interface displays to the user. The Lists option displays table names, field names, and templates. The Status Bar displays messages and is located at the very bottom of the screen. Move To buttons display to the left of the SQL text box; use Move To buttons to move around your SQL text screen quickly. Run buttons display to the right of the SQL text box; use Run buttons to run your SQL statement. For these lessons, you should turn on all of these options by following these instructions: 

  1. Click on View.
  2. Click on any item that does not have a checkmark next to it.

    Note: You might have to perform this process several times, because only one option can be selected at a time. So click on View and then click on any item that does not have a checkmark next to it. Then click on View again and click on any additional item that does not have a checkmark next to it. Continue until all items are checked.

Database in Use

Directly below the Menu bar is the Database field. The name of the current database is displayed in this field. 

Tables

The Tables box lists all of the tables in the database. Clicking on a table name will place the table name in the SQL text box. 

Fields

The Fields box lists all of the fields in a particular table. Clicking on a field name will place the field name in the SQL text box.

To see a list of the fields in a table: 

  1. Go to the Tables box.
  2. Highlight the table of interest.
  3. Click on the >> icon. 
  4. The field names for the table you highlighted will appear in the Field box.

Templates

The Templates box provides the syntax for commands. If you forget the syntax for a particular command, click on the command in the template field for a reminder. Refer to your Scalable SQL for Windows manual for further information. The conventions used in this guide are not the same as those used by Scalable SQL for Windows. 

Conventions Used in this Guide

This guide uses the following conventions to illustrate SQL syntax. 
 

Regular type Standard SQL clause.
Bold User-specified information.
Variable repeated three times Use as many variables as needed.
... Use as many variables as needed.
/ Use any one of the items separated by the slash.
<> Item between brackets is optional.

Errors

SQL consists of commands you type into the SQLScope text box. If you receive an error message after entering a SQL command, check your spelling and syntax. 

1 Explanation of these terms is beyond the scope of this course.

Table of Contents

Legal Dot Privacy