Difference between revisions of "SQL Overview"

Revision as of 13:03, 24 March 2009

Recital SQL is SQL ANSI 92 compliant and compatible with both Microsoft® Visual FoxPro® and MySQL™ SQL. Where Recital, MySQL and Visual FoxPro differ in their SQL syntax, the SET SQL TO <dialect> command can be used to select the syntax to be used.

Recital SQL commands may be used interactively at the command prompt in Recital Terminal Developer products, embedded within Recital/4GL programs, issued from '.sql' programs or sent from SQL clients for direct execution by the Recital Database Server.

Recital Terminal Developer Interactive Mode

Recital SQL statements may be typed interactively at the command prompt. The command SET SQL ON must first be issued. This changes the command prompt to the following: 'Recital/SQL>'.

When in interactive SQL mode, Recital SQL and Recital/4GL commands can be intermixed. The following Recital SQL commands take precedence over their Recital/4GL equivalents:

• CLOSE
• DELETE
• INSERT
• SELECT
• UPDATE

In interactive SQL mode, Recital SQL statements must be terminated with a semi-colon (;). Pressing [RETURN] without terminating the statement with a semi-colon continues the statement onto the next line. NOTE: If SQL is set to VFP, the semi-colon is not required.

The default Recital/SQL> prompt may be changed with the SET SQLPROMPT TO <expC> command.

Embedded Recital SQL

SQL statements may be embedded in Recital/4GL programs. If SQL is set to the default RECITAL, SQL statements must be preceded by the EXEC SQL command and terminated with a semi-colon (;).

// Embedded Recital SQL statement in program
exec sql
 select * from accounts
 where account_no = "00010";
dialog box [end of select]

All lines that follow the EXEC SQL line up to the terminating semi-colon are treated as a single SQL statement. SET SQL ON may be used as an alternative to EXEC SQL.

.sql Programs

Programs consisting entirely of SQL statements may be given a '.sql' file extension to differentiate them from standard Recital/4GL programs (.prg files). When a '.sql' file is run, SQL is automatically set ON and SQL is set to MYSQL. Compiled '.sql' files are given a '.sqo' file extension.

Database Server execution

Database Server SQL clients such as the Recital Universal JDBC Driver and the Recital Universal ODBC Driver, send individual SQL commands for execution and process the results.

rs = stmt.executeQuery("SELECT * from jdbctest");