ODBC - SQL Syntax

It is possible to embed SQL statements in a Gsharp script language program. This allows easy access to data stored in an external relational database such as Oracle. The syntax of the SQL statements conforms to the ANSI X3.168н1989 with a number of exceptions.

The embedded SQL statements is only available when the Gsharp Database Option has been licensed. If the option is not licensed, then execution of an SQL statement will result in an error message and Gsharp will abort the script language program.
There is no support for cursors, dynamic SQL or the Data Definition Language part of SQL. Instead of cursors, Gsharp provides the ability to use host arrays. Gsharp also allows you to embed the SELECT, INSERT, UPDATE and DELETE SQL statements.
Set operations are now supported in select statements and subqueries. The Oracle operations UNION [ALL], INTERSECT and MINUS are all supported by the GSL interpreter, but not all relational databases will support them.

Using Embedded SQL

An SQL statement is introduced by the keywords EXEC SQL and is terminated by the semicolon.

if a < 10 then 
  EXEC SQL SELECT y_data INTO :y FROM experiment_data 
  WHERE threshold > :a; 
  echo("Data collected"); 
endif

Whenever the script language parser sees the EXEC SQL keywords, it switches into SQL mode and understands SQL statements. Gsharp's script language keywords are no longer reserved unless they are reserved in SQL as well.

Variables and Keywords

The following guidelines apply to using variables and keywords in SQL
statements.

Unlike Gsharp keywords, SQL keywords can be entered both in upper and lower case. For example, the following two statements are both acceptable.

    EXEC SQL SELECT yData INTO :y FROM myData WHERE threshold > :a; 

    exec sql select yData into :y from myData where threshold > :a;

The dollar sign ( $ ) and the # are valid characters in SQL data names. (This is not the case with Gsharp variable names.)
You must use host variables to communicate with SQL.
A host variable is formed by adding a colon in front of a Gsharp variable name. For example, if myData and yData are Gsharp variables, you would refer to them as :myData and :yData when using them in SQL statements.
SQL strings are always enclosed in single quotes.
You cannot use strings starting and ending with double quotes as SQL literals. However, strings delimited by double quotes are used in SQL to explicitly name table and columns which contains spaces, reserved keyword, or is in mixed case. Thus, you can write SQL statements like:
```
EXEC SQL SELECT "MyVar" FROM "SELECT" 
WHERE "Strange NAME" IS LIKE '_YO_'; 
```
Note that `_YO_' is a literal string and "MyVar'', "SELECT'' and "Strange NAME'' are actual objects in the database. This use is greatly discouraged by database vendors. Beware that if you use host variables containing strings, it doesn't matter if the string was initialized using single or double quoted strings.

Commenting Your SQL Statements

You can use either ANSI style or C style comments in SQL statements.

The following example demonstrates the ANSI style comment. Note that the ANSI comment extends to the end of the line.

EXEC SQL UPDATE tab1 SET num = :x --нн Set num to a calculated value 
WHERE c = 123;

The following example demonstrates the C style comment.

EXEC SQL UPDATE tab1 SET num = :x /* Set num to a value */ 
WHERE c = 123;

IMPORTANT: You cannot use the # character to initiate a comment.

Data Definition Language

GSL now supports part of the data definition language of SQL. It is now possible to create, alter, and drop tables and views. The syntax for performing these tasks is shown below.

EXEC SQL CREATE TABLE table ( 
column datatype opt_constraints [, ...] ); 
EXEC SQL ALTER TABLE table 
[ ADD ( 
column datatype opt_constraints [, ...] 
) ] 
[ MODIFY ( 
columns opt_datatype opt_constraints [, ...] 
) ]; 
EXEC SQL DROP TABLE table;

Where table is the table name optionally including a schema, column is a column name, datatype is a valid data type, opt_constraints is optional column constraints, and opt_datatype is an optional data type.

Valid data types include:

CHAR, CHARACTER, CHAR(n), CHARACTER(n) 
CHAR VARYING(n), CHARACTER VARYING(n) 
NUMBER, NUMBER(p), NUMBER(p,s) 
NUMERIC, NUMERIC(p), NUMERIC(p,s) 
DECIMAL, DECIMAL(p), DECIMAL(p,s) 
DEC, DEC(p), DEC(p,s) 
INT, INTEGER, SMALLINT 
FLOAT, FLOAT(b), DOUBLE PRECISSION, REAL

Valid constraints include:

EXEC SQL CREATE VIEW view AS subquery; 
EXEC SQL DROP VIEW view;

Where a subquery is a select statement with no ORDER BY clause.

Note: You must have the appropriate permissions in the database system in order to create, alter, or drop tables and views.

Schema Definition Statement

GSL supports the schema definition statement.

EXEC SQL CREATE SCHEMA AUTHORIZATION user 
[CREATE TABLE command] 
[CREATE VIEW command] 
[GRANT command];

The CREATE TABLE, CREATE VIEW, and GRANT command are valid
statements providing they are issued as part of the CREATE SCHEMA command.

Connecting to the Database

Oracle and most other relational databases require that you identify yourself to access the data in the database. Gsharp uses the same method and syntax as does Oracle. To connect to the database use the CONNECT statement, which is defined as:

EXEC SQL CONNECT literal|:hostvar [IDENTIFIED BY literal|:hostvar];

The following code demonstrates how you can automate the process of connecting to a database. The code prompts for a user name and password and then uses that information to connect to the database:

/* Log on to database */ 
user = input_text("Enter username"); 
pwd = input_text("Enter password"); 
exec sql connect :user identified by :pwd;

If the database refuses the connection, Gsharp aborts the current program.

Retrieving Data

The SELECT statement is used to retrieve data from the database. A query of the database may result in one or more rows each containing one or more columns. To use the data in Gsharp, each column must refer to a Gsharp host variable. This reference is established using the INTO clause. Here is an example of a query, retrieving four columns:

EXEC SQL SELECT x, y, z, v INTO :x, :y, :z, :v FROM coords;

If the coords table contains the following values:

x y z v
10.4 11.5 н5.3 20.45
11.4 10.6 н3.4 15.67
9.5 6.3 0 0

The SELECT will result in four real datasets each containing three values. This is an exception to normal embedded SQL, where you usually retrieve each row separately using a cursor.

For further explanation of the SQL SELECT statement refer to the ANSI standard document. Note that you cannot use Oracle extensions to the SQL language.

If you enter the SQL SELECT statement without the INTO clause, Gsharp will display the retrieved rows in the message area.

IMPORTANT: If you include a WHERE clause, all host variables in the WHERE clause must be scalars.

Entering Data into the Database

You store data in a database using the INSERT statement. You can use Gsharp datasets as well as literals in the VALUES clause of the INSERT statement:

EXEC SQL INSERT INTO coords VALUES( :x, :y, :z, :v ); 

EXEC SQL INSERT INTO coords VALUES( :x, :y, 1.0, 0.0 );

If one or more Gsharp dataset is an arrays, they must be of equal length. If arrays are given, an INSERT statement is executed for each data value in the array. If scalars and arrays are specified together, the scalar is duplicated for each inserted row.

The example below demonstrates using arrays and scalars to populate an empty table, TDATA:

TDATA: 
NUMBER NUM 
CHAR(10) TXT 
NUMBER C 

a = 1//2//3//4//5; 
b = "This"//"is"//"a"//"database"//"program"; 

EXEC SQL INSERT INTO TDATA VALUES (:a, :b, 123); 

EXEC SQL SELECT * FROM TDATA ORDER BY NUM;

The result of running the code above is shown below. Notice how the scalar, 123 is duplicated for each row in the table.

NUM TXT C
1 This 123
2 is 123
3 a 123
4 database123
5 program123

Updating Data in the Database

You can update rows using the UPDATE statement. You can use host variables in the UPDATE statement in both the SET clause and the WHERE clause. An example of an UPDATE statement is shown below.

EXEC SQL UPDATE tab1 SET num = :x WHERE c = 123;

If one or more Gsharp datasets is an array, they must be of equal length. If arrays are given, an UPDATE statement is executed for each data value in the array. If scalars and arrays are specified together, the scalar is duplicated for each updated row.

Deleting Data from the Database

You delete rows from a database using the DELETE statement. You can use host variables in WHERE clause.

If one or more Gsharp datasets is an array, they must be of equal length. If arrays are given, a DELETE statement is executed for each data value in the array. If scalars and arrays are specified together, the scalar is duplicated for each deleted row.

The following statement deletes all rows from the TDATA table where C is equal to 123.

EXEC SQL DELETE FROM TDATA WHERE C = 123;

The statement below deletes all data from the TDATA table.

EXEC SQL DELETE FROM TDATA;

Handling SQL Errors

The GSL can now receive error information from the SQL. To work with this error information, you must create a variable, SQLCODE, in your current folder. This variable will receive the status of the SQL statement processed at the RDBMS.

If SQLCODE is not defined, Gsharp will abort the runtime with an error message.
If SQLCODE is defined, processing continues even if an error occurs (except for internal errors such as no memory available).

Committing or cancelling changes

Changes to the database are not committed automatically by Gsharp - you are responsible for managing the contents of SQLCODE and for either committing or cancelling the changes to the database with the COMMIT and ROLLBACK
statements.

EXEC SQL COMMIT [WORK]; 
EXEC SQL ROLLBACK [WORK];

Note: The WORK keyword is optional in GSL to be compatible with Oracle practice.

The following code demonstrates how to use the SQLCODE variable to manage the changes to your database.

SQLCODE=0; 
user = `Paul';
 
EXEC SQL DELETE FROM user_work WHERE user_name = :user; 
if not SQLCODE < 0 then 
  EXEC SQL DELETE FROM user_master WHERE user_name = :user; 
  If SQLCODE < 0 then 
    EXEC SQL ROLLBACK WORK; 
  else 
    EXEC SQL COMMIT WORK; 
  endif 
endif