SQL Commands

Jive uses ODBC for access to SQL databases. Basic SQL commands are supported - select, update, delete, insert, execute plus a connect command. execute is used for stored procedures and catalog access. connect can be in embedded commands. The others are external commands - enclosed in braces. select and execute are control structure commands and require a terminating {end}, like if and while.

connect Command

The connect command is a basic command that connects to the database through ODBC. It is followed by an expression for the ODBC driver connection string. The connect command establishes the current database connection, used by subsequent SQL commands. Another connect is issued to change the database connection.

select Command

The select command retrieves a result set from the database. select is an external command and must be enclosed in braces. It processes the HTML text following, up to a terminating {end}, for each row of the result set. It defines a set of readonly variables to contain the columns of the result set. Unnamed columns are given the name - 'column_nn', where nn is the column number in the result set. These database variables are local to the select block.

select commands can be labelled by preceding the select with a symbol and a colon. The label can be used for qualification. For example,

<h3>Customers</h3>
<ul>
{cust:select * from customer}
<li>{cust.name}
{end}
</ul>

The label (or the default - query) is set as the cursor name for the query. It can be referenced in UPDATE and DELETE WHERE CURRENT OF. For example,

{cust:select * from customer}
{if cust.custno = 1555}{delete customer where current of cust}{end}
{end}

Jive variables can be referenced in the select, using syntax like embedded SQL. A Jive variable in a select command is prefixed with a colon (:). Qualification and subscripting may be used. For example,

{var state = 'CA'}
Customers in {state}:
<ul>
{cust:select * from customer where state = :state}
<li>{cust.name}
{end}
</ul>

update, delete, insert Commands

These command modify the database. They are external commands and must be enclosed in braces. Unlike select, they don't return a result set, so the {end} is not used and labelling is not allowed.

The update, delete and insert command can use Jive variables in the same manner as select. Jive variables in SQL commands are preceded with a colon. For example,

{var salary = 25000.00, id = 1475}
{update employee set salary = :salary where employee_id = :id}

execute Command

The execute command is used to call database stored procedures through ODBC. A set of special stored procedures provide access to the database catalog. For details, see execute Command