SQL Commands

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

connect Command

The connect command is 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 commands 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 sumbol and a colon. The label can be used for qualification. For example,


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

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 like 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}