Difference between revisions of "SQL Overview"

Revision as of 16:05, 5 July 2011

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 is no longer required.

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

No statement terminator is required. The semi-colon (;) is used to continue the statement on the next line.

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");