OpenBase SQL

This guide covers the Standard Query Language (SQL), the means by which client applications communicate with database server programs. This guide is only meant to give you a basic understanding of how SQL can be used to create, read and manage database information. For more in depth information we recommend that you refer to a book on ANSI SQL.

SQL can be entered and executed using the GUI ISQL program or openisql tools. Both of these are described in the OpenBase Manager documentation.

Please note that while OpenBase supports standard SQL, we have taken the liberty to deviate from the standards in order to more closely match the needs of customers.

videoicon3.png Video: Introduction to SQL

Server-side Variables

OpenBase provides server-side global variables to make it easy to coordinate between stored procedures and database clients. The variables are thread-safe and can be safely incremented and decremented to provide specialized key generation or state management.

Creating and Using Indexes

OpenBase offers 5 different types of indexes using SQL syntax and one index type using stored procedures (described in the stored procedure section). Indexes are used to speed up searching and are particularly necessary when joining tables. We recommend that columns used in searching or as foreign keys and primary keys get indexed. This section describes how to create indexes using SQL.

SQL Cursors

Server-side Cursors in OpenBase SQL enable clients to perform a select or stored procedure call and cache a snapshot of the results on the server. This allows clients to incrementally request portions of the result set or start fetching again from the beginning.

SQL Triggers

Triggers are a mechanism that allows you to perform an action when someone modifies a table. The trigger mechanism in OpenBase allows you to perform different actions depending on whether the table is modified with an insert, update or delete. Triggers allow both modified and original values to be passed in.

SQL Transactions & Locking Techniques

In this section we will discuss the OpenBase SQL syntax for transactions, and the concepts and principles for them.

Connection Status and Aborting Connections

Show stats returns a result set showing all of the sessions that are connected to a database. The first line contains statistic totals and the first column provides the thread . Session numbers are assigned based on seniority. As sessions quit, the remaining sessions are renumbered. Note that you should NOT try and identify a session by it’s session number because they can change. The process id is a better identifier for this.

openbase 1> show stats
openbase 2> go

Alert Message System

OpenBase has a built in notification system that allows applications and stored procedures to send messages to system administrators via a central notification system. Alerts can set the database error light on the OpenBase Manager so an administrator will know to look at the log.

These examples use openisql to send the SQL to the database. However, you can use any programatic means to send the commands to the server.

Managing Users

The OpenBase Manager includes graphical tools that make it easy to manage database users. But for those who need to manage users programmatically, this section offers some instruction.

SQL Functions

Functions can be embedded SQL, used in formulas or called separately. This section covers the variety of functions that are supported by OpenBase.

Create View

CREATE VIEW statements provide a way to specify database views. A database view is a phantom table which includes data from one or more tables. Views are normally used to provide a way of flattening out complex relationships and queries so that the data appears like it is coming from a single table.

Unique Key Generation

To insert a new unique key into your record, you first need to generate a unique key value using the NEWID command.Or you can use one of the API calls and then insert it into your record using an insert statement.

Schema Management

The CREATE TABLE keyword at the beginning of an SQL statement tells OpenBase that you want to create a new table. Creating new tables can also be accomplished by using the graphical tools provided with the OpenBase Manager. However, this section will give you an idea of what happens on the SQL level.

Delete Statements

DELETE FROM provides a way to remove records from a table. Deleting records from a table works similarly to updating in that the WHERE clause specifies which records will be affected.

Update Statements

The UPDATE statement is used to update values in the database. UPDATE statements have the ability to update multiple records at a time depending on the search constraints given.
Here is an example of an UPDATE statement:

UPDATE <Table Name> SET <field1> = <value1> [,<field2>=<value2>...] where <search conditions>

Insert & Replace Statements

OpenBase offers two variations of the insert SQL syntax: INSERT INTO and REPLACE INTO. The SQL standard specifies that INSERT INTO will insert values into a table as a new record. If it is unable to insert the values as a new record an error is returned. REPLACE INTO does the same thing except it will update the values of an existing record if it finds one. REPLACE INTO is an extension to SQL. Both are covered in this section.

Select Statements

Select statements can be the most complicated of all SQL statements. It is important to note that select statements are the only statements that will operate on multiple tables at the same time. For this reason, select statements support a slightly different, although standard, SQL syntax. This will be explained further when we talk about joins between tables, but let's first examine a simple SELECT statement.

SQL Standards

These days a lot of emphasis is placed on SQL syntax standards. The problem is that the standardization stops with the easiest part — the SQL syntax.

Unless otherwise stated, the content of this page is licensed under Creative Commons Attribution-ShareAlike 3.0 License