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 -- 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.
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 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.
A database is a collection of related data. It can also be viewed as a collection of related tables.
To find a list of the tables used by Solomon IV, follow the steps outlined below:
- Log in to Solomon IV.
- Click on Help on the Menu bar.
- Click on Solomon Data Fields on the drop-down menu.
- Click on Solomon IV Application Database Schema. You will be presented with a screen that lists each module.
- To see a list of the tables used by a particular module, click on the module name.
- Then to see a list of the fields in a specific table, click on the table name. You will be presented with a report.
- To print the report, click on Print.
SQL reports look similar to the ones shown here.
Vendor Training TrnVendor
|Add1||String||30||Vendor street address|
|CurrBal||Float||8||Current outstanding balance|
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|
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 types define the kind of data the field can contain. Scalable SQL supports four major field types:
Date and time
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:
Numeric -- A numeric field stores numbers. You can use the field to perform mathematical calculations. Scalable SQL supports the following types of numeric fields:
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.
The length column specifies the maximum number of characters the field will accept.
SQLScope is the interface used to access and manipulate data stored in a Scalable SQL for Windows database. The following sections describe the interface.
- To log in to SQLScope:
- Open SQLScope.
- In the Database Name field, enter the name of the database you
wish to open.
Note: For these exercises, use a test database.
- Type Master in the User Name field.
- If a password has been assigned, enter the password in the Password field.
- Click on Login.
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 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:
- Click on View.
- 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.
Directly below the Menu bar is the Database field. The name of the current database is displayed in this field.
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.
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:
- Go to the Tables box.
- Highlight the table of interest.
- Click on the >> icon.
- The field names for the table you highlighted will appear in the Field box.
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.
This guide uses the following conventions to illustrate SQL syntax.
|Regular type||Standard SQL clause.|
|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.|
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|