Difference between revisions of "Using Recital SQL"

From Recital Documentation Wiki
Jump to: navigation, search
(Executing Procedures and Prepared Statements using Recital SQL)
(Constraints)
 
(78 intermediate revisions by one user not shown)
Line 27: Line 27:
 
</pre>
 
</pre>
  
The ''open database'' command triggers the DBC_OPENDATA database event. If a dbc_opendata.prg program file exists in the database's directory, this will be run.  If the dbc_opendata.prg program returns .F. (False), the ''open database'' operation will be abandoned.
+
The ''open database'' statement triggers the DBC_OPENDATA database event. If a dbc_opendata.prg program file exists in the database's directory, this will be run.  If the dbc_opendata.prg program returns .F. (False), the ''open database'' operation will be abandoned.
  
 
Databases can also have an associated procedure library that is activated automatically when the database is opened.  The procedure library must follow the naming convention dbc_<database>_library.prg and must exist in the database's directory.  When the database is closed, the procedure library is also closed.
 
Databases can also have an associated procedure library that is activated automatically when the database is opened.  The procedure library must follow the naming convention dbc_<database>_library.prg and must exist in the database's directory.  When the database is closed, the procedure library is also closed.
 +
 +
<code lang="recital">
 +
open database southwind
 +
</code>
  
 
Tables from a database that is not currently open can be accessed by preceding the table name by the database name and a ''!''.
 
Tables from a database that is not currently open can be accessed by preceding the table name by the database name and a ''!''.
Line 83: Line 87:
 
* VARCHAR
 
* VARCHAR
  
====Table Constraints====
+
====Constraints====
  
 
{| class="wikitable"
 
{| class="wikitable"
|[[CHECK Table Constraint|CHECK]]||[[ERROR Table Constraint|ERROR]]||[[FOREIGN KEY Table Constraint|FOREIGN KEY]]
+
|Table Constraints|| ||
 
|-
 
|-
|[[INDEX Table Constraint|INDEX]]||[[KEY Table Constraint|KEY]]||[[ONCLOSE Table Constraint|ONCLOSE]]
+
|[[CHECK Table Constraint|check]]||[[ERROR Table Constraint|error]]||[[FOREIGN KEY Table Constraint|foreign key]]
 
|-
 
|-
|[[ONDELETE Table Constraint|ONDELETE]]||[[ONINSERT Table Constraint|ONINSERT]]||[[ONOPEN Table Constraint|ONOPEN]]
+
|[[INDEX Table Constraint|index]]||[[KEY Table Constraint|key]]||[[ONCLOSE Table Constraint|onclose]]
 
|-
 
|-
|[[ONROLLBACK Table Constraint|ONROLLBACK]]||[[ONUPDATE Table Constraint|ONUPDATE]]||[[PRIMARY KEY Table Constraint|PRIMARY KEY]]
+
|[[ONDELETE Table Constraint|ondelete]]||[[ONINSERT Table Constraint|oninsert]]||[[ONOPEN Table Constraint|onopen]]
 
|-
 
|-
|[[UNIQUE Table Constraint|UNIQUE]]||||
+
|[[ONROLLBACK Table Constraint|onrollback]]||[[ONUPDATE Table Constraint|onupdate]]||[[PRIMARY KEY Table Constraint|primary key]]
 
|-
 
|-
|}
+
|[[UNIQUE Table Constraint|unique]]|| ||
 
+
|-
====Column Constraints====
+
|Column Constraints|| ||
 
+
|-
{| class="wikitable"
+
|[[AUTO_INCREMENT Column Constraint|auto_increment]]||[[AUTOINC Column Constraint|autoinc]]||[[CALCULATED Column Constraint|calculated]]
|[[AUTO_INCREMENT Column Constraint|AUTO_INCREMENT]]||[[AUTOINC Column Constraint|AUTOINC]]||[[CALCULATED Column Constraint|CALCULATED]]
+
 
|-
 
|-
|[[CHECK Column Constraint|CHECK]]||[[DEFAULT Column Constraint|DEFAULT]]||[[DESCRIPTION Column Constraint|DESCRIPTION]]
+
|[[CHECK Column Constraint|check]]||[[DEFAULT Column Constraint|default]]||[[DESCRIPTION Column Constraint|description]]
 
|-
 
|-
|[[ERROR Column Constraint|ERROR]]||[[FOREIGN KEY Column Constraint|FOREIGN KEY]]||[[NOCPTRANS Column Constraint|NOCPTRANS]]
+
|[[ERROR Column Constraint|error]]||[[FOREIGN KEY Column Constraint|foreign key]]||[[NOCPTRANS Column Constraint|nocptrans]]
 
|-
 
|-
|[[NOT NULL Column Constraint|NOT NULL]]||[[NULL Column Constraint|NULL]]||[[PRIMARY KEY Column Constraint|PRIMARY KEY]]
+
|[[NOT NULL Column Constraint|not null]]||[[NULL Column Constraint|null]]||[[PRIMARY KEY Column Constraint|primary key]]
 
|-
 
|-
|[[RANGE Column Constraint|RANGE]]||[[RECALCULATE Column Constraint|RECALCULATE]]||[[REFERENCES Column Constraint|REFERENCES]]
+
|[[RANGE Column Constraint|range]]||[[RECALCULATE Column Constraint|recalculate]]||[[REFERENCES Column Constraint|references]]
 
|-
 
|-
|[[SET CHECK Column Constraint|SET CHECK]]||[[UNIQUE Column Constraint|UNIQUE]]||
+
|[[SET CHECK Column Constraint|set check]]||[[UNIQUE Column Constraint|unique]]||
 
|-
 
|-
 
|}
 
|}
Line 118: Line 121:
 
<code lang="recital">
 
<code lang="recital">
 
create table customer;
 
create table customer;
  (account_no char(5) description "Account Code";
+
    (account_no char(5) description "Account Code";
    default strzero(seqno(),5),;
+
        default strzero(seqno(),5),;
  title char(3) description "Personal Title",
+
    title char(3) description "Personal Title",
  last_name char(16) description "Customer's Last Name",;
+
    last_name char(16) description "Customer's Last Name",;
  first_name char(10) description "Customer's Given Name",;
+
    first_name char(10) description "Customer's Given Name",;
  initial char(2) description "Customer's Middle Initial",;
+
    initial char(2) description "Customer's Middle Initial",;
  street char(25) description "Street Number and Name",;
+
    street char(25) description "Street Number and Name",;
  city char(12) description "City",;
+
    city char(12) description "City",;
  state char(2) description "State Abbreviation";
+
    state char(2) description "State Abbreviation";
    check rlookup(customer.state,state);
+
        check rlookup(customer.state,state);
    error "Invalid State",;
+
        error "Invalid State",;
  zip char(10) description "Zip Code",;
+
    zip char(10) description "Zip Code",;
  limit decimal(11,2) description "Credit Limit";
+
    limit decimal(11,2) description "Credit Limit";
    recalculate,;
+
        recalculate,;
  balance decimal(11,2) description "Credit Balance";
+
    balance decimal(11,2) description "Credit Balance";
    recalculate,;
+
        recalculate,;
  available decimal(11,2) description "Credit Available";
+
    available decimal(11,2) description "Credit Available";
    calculated limit-balance,;
+
        calculated limit-balance,;
  notes long varchar description "Customer Notes",;
+
    notes long varchar description "Customer Notes",;
  start_date date description "Customer Start Date";
+
        start_date date description "Customer Start Date";
    default date(),;
+
        default date(),;
  onopen "customer")
+
        onopen "customer")
 
</code>
 
</code>
  
Line 252: Line 255:
 
drop table <table>
 
drop table <table>
 
</pre>
 
</pre>
 +
 +
'''Example'''
 +
 +
<code lang="recital">
 +
drop table example
 +
</code>
  
 
====Creating an Index====
 
====Creating an Index====
Line 261: Line 270:
 
</pre>
 
</pre>
  
====Altering or Removing an Index====
+
'''Example'''
Existing indexes may be altered using the [[ALTER INDEX|alter index]] statement.
+
 
 +
<code lang="recital">
 +
create index lname;
 +
  on example (lower(last_name))
 +
</code>
 +
 
 +
====Rebuilding or Removing an Index====
 +
Existing indexes may be rebuilt using the [[ALTER INDEX|alter index]] statement.
  
 
<pre>
 
<pre>
alter index <index> on <table>  
+
alter index <index> on <table> rebuild
 
</pre>
 
</pre>
 +
 +
'''Example'''
 +
 +
<code lang="recital">
 +
alter index lname;
 +
  on example;
 +
  rebuild
 +
</code>
  
 
The [[DROP INDEX|drop index]] statement is used to remove an index.
 
The [[DROP INDEX|drop index]] statement is used to remove an index.
Line 273: Line 297:
 
drop index <index> on <table>
 
drop index <index> on <table>
 
</pre>
 
</pre>
 +
 +
'''Example'''
 +
 +
<code lang="recital">
 +
drop index lname;
 +
  on example
 +
</code>
  
 
===Inserting Records into a Recital Table using Recital SQL===
 
===Inserting Records into a Recital Table using Recital SQL===
* INSERT
+
Records are added into a Recital table using the [[INSERT|insert]] statement.  A single row can be inserted by specifying the row contents in the ''values'' clause.  Specifying a list of column names determines the columns whose values will be set.
  
 
<pre>
 
<pre>
insert into <table> [(<column> [,...])]
+
insert into <table> [(<column> [,...])] values (<expr> [,...])
 +
</pre>
  
values (<expr> [, ] | NULL [, ] | <empty> [, ] [,...]) | <sub-query> | [FROM] XML <.xml file>
+
'''Example'''
  
insert into <table>
+
<code lang="recital">
 +
insert into example;
 +
  (account_no, last_name);
 +
  values("01001","Brown")
 +
</code>
  
from array <array> | from memvar | from name <object>  
+
One or more rows can be inserted by specifying a sub-query as the data source.  Again the columns to be set can be restricted.
 +
 
 +
<pre>
 +
insert into <table> [(<column> [,...])] <sub-query>
 
</pre>
 
</pre>
 +
 +
'''Example'''
 +
 +
<code lang="recital">
 +
insert into example;
 +
  (account_no, last_name);
 +
  select * from temp
 +
</code>
 +
 +
Data can also be loaded from an XML file, from an array, from a collection of memory variables with the same names as the table columns or from an object with property names that match the table columns.
 +
 +
<pre>
 +
insert into <table> from array <array> | from memvar | from name <object> | from [xml] <xml filename>
 +
</pre>
 +
 +
'''Examples'''
 +
 +
<code lang="recital">
 +
account_no = "01002"
 +
title = "Ms"
 +
last_name = "Smith"
 +
 +
insert into example;
 +
  from memvar
 +
 +
arr1 = {"01003","Mr","Jones"}
 +
 +
insert into example;
 +
  from array arr1
 +
 +
obj1 = object("account_no" => "01004","title" => "Mrs","last_name" => "Green")
 +
 +
insert into example;
 +
  from name obj1
 +
 +
insert into example;
 +
  from xml newcust.xml
 +
</code>
  
 
===Selecting Records from a Recital Table Using Recital SQL===
 
===Selecting Records from a Recital Table Using Recital SQL===
* SELECT
+
The SQL [[SQL SELECT|select]] statement is used to query the data in one or more tables.
  
===Updating Records in a Recital Table using Recital SQL===
+
The columns to be returned can be defined individually, or the ''*'' can be used to return all columns.  Constants, functions and aggregate functions ([[AVG()|avg()]], [[COUNT()|count()]], [[SQL MAX()|max()]], [[SQL MIN()|min()]], [[SUM()|sum()]]) can also be included and each element to be returned may be given an alias name.
* UPDATE
+
  
 
<pre>
 
<pre>
update <table>
+
select * | <column>|<"column"> | <constant> | <function> | <aggregate> [[as] <alias>] [,...] [, *] from <table-definition>
set <column> = <expr> [,...]
+
[where <condition> | current of <cursor>]
+
 
</pre>
 
</pre>
  
* UPDATE FROM XML
+
'''Examples'''
 +
 
 +
<code lang="recital">
 +
// Return all columns
 +
select * from products
 +
// Return specified columns
 +
select 'Product Details: ',  productid, upper(productname) as Name,;
 +
  unitprice*unitsinstock as stockholding;
 +
  from products
 +
// Return aggregate function results for the whole table
 +
select avg(unitprice) Average, min(unitprice) Minimum,;
 +
  max(unitprice) Maximum, count(unitprice) Count,;
 +
  sum(unitprice) Sum;
 +
  from products
 +
</code>
 +
 
 +
Rows can be restricted in number and duplicates excluded:
 +
 
 
<pre>
 
<pre>
update <table> FROM XML <xml filename>
+
select [all | distinct | distinctrow | top <expN> [percent]] <column-definition> from <table-definition>
 +
 
 +
select <column-definition> from <table-definition> limit [<offset>,] <row_count> | <row_count> offset <offset>]
 
</pre>
 
</pre>
  
* BEGIN...END TRANSACTION
+
'''Examples'''
 +
 
 +
<code lang="recital">
 +
// Return one row for each group of rows with an identical contactname column
 +
select distinct contactname from customers
 +
// Return one row for each group of identical rows
 +
select distinctrow contactname from customers
 +
// Return the first 10 rows
 +
select top 10 * from customers
 +
// Return the first 50% of rows
 +
select top 50 percent * from customers
 +
// Select a maximum of 10 rows, starting from row 6
 +
select * from customers limit 5,10
 +
// or
 +
select * from customers limit 10 offset 5
 +
</code>
 +
 
 +
Rows can be queried from tables and views. These data sources can be related using ''inner joins'', ''cross joins'' or ''outer joins'' (''left'', ''right'' and ''full''). Each data source may be given an alias name.
 +
 
 +
<pre>
 +
select <column-definition> from <table> | <view> [[as] <t_alias>] [, ...]
 +
</pre>
 +
 
 +
<pre>
 +
select <column-definition> from <table|view>
 +
inner join | outer join | left [outer] join | right [outer] join <table2|view2>
 +
on <table|view>.<column> = <table2|view2>.<column> [, ...]
 +
</pre>
 +
 
 +
<pre>
 +
select <column-definition> from <table> | <view>
 +
cross join | full [outer] join <table2|view2>
 +
</pre>
 +
 
 +
'''Examples'''
 +
 
 +
<code lang="recital">
 +
// Select all rows from shippers and employees without relating the tables
 +
select * from shippers ship, employees as emp
 +
// Relate orders, order_details and shippers tables with multiple joins
 +
select orders.orderid, orders.shipvia,;
 +
  shippers.companyname, order_details.productid;
 +
  from orders left outer join order_details;
 +
  on orders.orderid = order_details.orderid,;
 +
    orders inner join shippers;
 +
    on orders.shipvia = shippers.shipperid
 +
// Equivalent to 'select * from shippers, employees'
 +
select * from shippers full join employees
 +
</code>
 +
 
 +
Rows can be sorted by expression or column in ascending or descending order using the ''order by'' clause.
 +
 
 +
<pre>
 +
select <column-definition> from <table-definition> order by <expr> | <column-name> | <column-number> [asc | desc] [,...]
 +
</pre>
 +
 
 +
'''Example'''
 +
 
 +
<code lang="recital">
 +
// Return rows sorted in descending order by orderid then productid
 +
select orderid, productid, quantity;
 +
  from order_details;
 +
  order by orderid, 2 desc
 +
</code>
 +
 
 +
The ''group by'' clause allows results for a group of rows to be summarized as a single row.  This is particularly used with the aggregate functions ([[AVG()|avg()]], [[COUNT()|count()]], [[SQL MAX()|max()]], [[SQL MIN()|min()]], [[SUM()|sum()]]).  Groups can be based on expressions, column names and column numbers.
 +
 
 +
<pre>
 +
select <column-definition> from <table-definition> group by <expr> | <column-name> | <column-number> [,...]
 +
</pre>
 +
 
 +
'''Example'''
 +
 
 +
<code lang="recital">
 +
// Return the orderid and total for each order
 +
select orderid as Order, sum(unitprice * quantity * (1-discount)) as Total;
 +
  from order_details;
 +
  group by orderid
 +
</code>
 +
 
 +
The ''having'' and ''where'' clauses restrict the selection based on specified conditions. Only those rows for which the conditions are true are returned.
 +
 
 +
<pre>
 +
select <column-definition> from <table-definition> having <condition>
 +
</pre>
 +
 
 +
<pre>
 +
select <column-definition> from <table-definition> where <condition>
 +
</pre>
 +
 
 +
The ''where'' clause condition can be a nested select.
 +
 
 +
'''Examples'''
 +
 
 +
<code lang="recital">
 +
// Having condition
 +
select sum(unitprice * quantity) as subtotal,;
 +
  avg(unitprice * quantity) as averagetotal;
 +
  from order_details;
 +
  where discount = 0;
 +
  group BY orderid;
 +
  having quantity > 10
 +
 
 +
// Where condition with nested select
 +
select shipname from orders, customers;
 +
  where orders.customerid = customers.customerid;
 +
  and employeeid = (select max(employeeid);
 +
  from orders where orderdate = {07/19/1996});
 +
  order by shipname
 +
</code>
 +
 
 +
The ''into'', ''save as'' and ''to'' clauses offer a wide range of output destinations and formats for the selected rows.
 +
 
 +
<pre>
 +
select <column-definition> from <table-definition> into <variable> [,...] |
 +
array <array-name> |
 +
cursor <cursor-name> |
 +
name | object <object-ref> |
 +
json <filename> |
 +
html <html filename> |
 +
xml <xml filename> |
 +
dbf | table <table-name> [database <database>]
 +
</pre>
 +
 
 +
<pre>
 +
select <column-definition> from <table-definition> save as <table name> | xml <xml filename>
 +
</pre>
 +
 
 +
<pre>
 +
select <column-definition> from <table-definition> to file <filename> | printer | screen
 +
</pre>
 +
 
 +
'''Examples'''
 +
 
 +
<code lang="recital">
 +
select productid from products into array array1
 +
select * from shippers into html shippers
 +
select employeeid, lastname, firstname from employees into table emp database newsouth
 +
 
 +
select employeeid, lastname, firstname from employees save as xml emp
 +
 
 +
select * from shippers to file shiptxt
 +
</code>
 +
 
 +
===Updating Records in a Recital Table using Recital SQL===
 +
The [[SQL UPDATE|update]] statement is used to update records.  The individual columns and their new values are listed and an optional condition can be specified to restrict the records affected.
 +
 
 +
<pre>
 +
update <table> set <column> = <expr> [,...] [where <condition>]
 +
</pre>
 +
 
 +
'''Example'''
 +
 
 +
<code lang="recital">
 +
update products;
 +
  set unitsinstock = unitsinstock + unitsonorder, unitsonorder = 0;
 +
  where supplierid = 1
 +
</code>
 +
 
 +
Updates, along with insertions and deletions, can be processed as a transaction within a [[SQL BEGIN TRANSACTION|begin...end transaction]] block.  Nested transactions are supported.
  
 
<pre>
 
<pre>
Line 312: Line 564:
 
</pre>
 
</pre>
  
* COMMIT
+
As are save points:
 +
 
 +
<pre>
 +
save transaction <savepoint>
 +
</pre>
 +
 
 +
Transactions can be manually [[COMMIT|committed]]:
  
 
<pre>
 
<pre>
Line 318: Line 576:
 
</pre>
 
</pre>
  
* ROLLBACK
+
or [[SQL ROLLBACK|rolled back]]:
  
 
<pre>
 
<pre>
 
rollback [transaction <transaction> | <savepoint>] [to savepoint <savepoint>]
 
rollback [transaction <transaction> | <savepoint>] [to savepoint <savepoint>]
 
</pre>
 
</pre>
 +
 +
'''Examples'''
 +
<code lang="recital">
 +
begin transaction trans1
 +
    update orders;
 +
      set requireddate = date()+30;
 +
      where orderid = 10248
 +
    update order_details set discount = 0.05;
 +
      where orderid = 10248
 +
    begin transaction trans2
 +
        update order_details set discount = 0.10;
 +
          where orderid = 10248
 +
    rollback transaction trans1
 +
end transaction
 +
 +
begin transaction trans3
 +
    update orders;
 +
      set requireddate = date()+30;
 +
      where orderid = 10248
 +
    update order_details set discount = 0.05;
 +
      where orderid = 10248
 +
    commit transaction trans3
 +
end transaction
 +
 +
begin transaction trans4
 +
    update orders;
 +
      set requireddate = date()+30;
 +
      where orderid = 10248
 +
    save transaction parent_updated
 +
    update order_details set discount = 0.10;
 +
      where orderid = 10248
 +
    rollback transaction parent_updated
 +
end transaction
 +
 +
begin transaction trans5
 +
    update orders;
 +
      set requireddate = date()+30;
 +
      where orderid = 10248
 +
    savepoint parent_updated
 +
    update order_details set discount = 0.15;
 +
      where orderid = 10248
 +
    rollback transaction parent_updated
 +
end transaction
 +
</code>
  
 
===Deleting Records in a Recital Table using Recital SQL===
 
===Deleting Records in a Recital Table using Recital SQL===
Line 380: Line 682:
  
 
<code lang="recital">
 
<code lang="recital">
open database southwind
 
 
 
// Declare the cursor to select records from the orders table
 
// Declare the cursor to select records from the orders table
 
declare cursor1;
 
declare cursor1;
Line 408: Line 708:
 
close cursor1
 
close cursor1
 
drop cursor cursor1
 
drop cursor cursor1
 
close databases
 
 
</code>
 
</code>
  
Line 432: Line 730:
 
* [[SYSVERSIONCOLUMNS|sysversioncolumns]] - description of the columns in a table that are automatically updated when any row is updated
 
* [[SYSVERSIONCOLUMNS|sysversioncolumns]] - description of the columns in a table that are automatically updated when any row is updated
  
'''Example'''
+
'''Examples'''
  
 
<code lang="recital">
 
<code lang="recital">
open database southwind
 
 
select * from syscolumns;
 
select * from syscolumns;
 
   where table_cat = "southwind" and table_name = "orders"
 
   where table_cat = "southwind" and table_name = "orders"
Line 459: Line 756:
  
 
<code lang="recital">
 
<code lang="recital">
open database southwind
 
 
create procedure creaxml as
 
create procedure creaxml as
 
select orders.orderid, orders.customerid, employees.employeeid,;
 
select orders.orderid, orders.customerid, employees.employeeid,;
Line 478: Line 774:
 
</code>
 
</code>
  
* EXECUTE
+
The [[PREPARE|prepare]] statement is used to set up an SQL statement for subsequent execution. 
  
 
<pre>
 
<pre>
execute <statement> using :<variable>[,<variable2>[,...]]
+
prepare <statement> from :<variable>
 
</pre>
 
</pre>
  
* EXECUTE IMMEDIATE
+
Variables used as parameters in the SQL statement can then be set just before executing it using the [[EXECUTE|execute]] statement.
  
 
<pre>
 
<pre>
execute immediate <statement>  
+
execute <statement> using :<variable>[,<variable2>[,...]]
 
</pre>
 
</pre>
  
* PREPARE
+
'''Examples'''
 
+
<pre>
+
prepare <statement> from :<variable>
+
</pre>
+
  
 
<code lang="recital">
 
<code lang="recital">
open database southwind
+
stmtbuf = "select * from example where account_no = ?"
stmtbuf = "SELECT * FROM example WHERE account_no = ?"
+
prepare mystmt from :stmtbuf
PREPARE mystmt FROM :stmtbuf
+
 
m_acc = "00002"
 
m_acc = "00002"
EXECUTE mystmt USING :m_acc
+
execute mystmt using :m_acc
  
 
m_acc = "00003"
 
m_acc = "00003"
EXECUTE mystmt using :m_acc
+
execute mystmt using :m_acc
  
stmtbuf = "INSERT INTO example (account_no, last_name, forename) VALUES (?,?,?)"
+
stmtbuf = "insert into example (account_no, last_name, forename) values (?,?,?)"
PREPARE mystmt FROM :stmtbuf
+
prepare mystmt from :stmtbuf
 
buf1 = "99999"
 
buf1 = "99999"
 
buf2 = "Smith"
 
buf2 = "Smith"
 
buf3 = "John"
 
buf3 = "John"
EXECUTE mystmt USING :buf1, :buf2, :buf3
+
execute mystmt using :buf1, :buf2, :buf3
  
stmtbuf = "DELETE FROM example WHERE account_no = ?"
+
stmtbuf = "delete from example where account_no = ?"
PREPARE mystmt FROM :stmtbuf
+
prepare mystmt from :stmtbuf
 
buf1 = "99999"
 
buf1 = "99999"
EXECUTE mystmt USING :buf1
+
execute mystmt using :buf1
 +
</code>
  
EXECUTE IMMEDIATE SELECT * FROM example WHERE account_no = m_acc
+
The [[EXECUTE IMMEDIATE|execute immediate]] statement is used to prepare and execute immediately a ''delete'', ''insert'', or ''update'' SQL statement.
 +
 
 +
<pre>
 +
m_acc = "00003"
 +
execute immediate <statement>
 +
</pre>
 +
 
 +
'''Example'''
 +
 
 +
<code lang="recital">
 +
execute immediate select * from example where account_no = m_acc
 
</code>
 
</code>
  
* SYSRESULTSET Used to return the singleton result from any Recital expression
+
The [[SYSRESULTSET|sysresultset]] system table returns the singleton result from any Recital expression.
 +
 
 
<code lang="recital">
 
<code lang="recital">
select set("exclusive") as Excl, time() as Time from sysresultset
+
select set("exclusive") as excl, time() as time from sysresultset
 
</code>
 
</code>

Latest revision as of 02:22, 1 October 2010

Using Recital SQL

Creating a Recital Database

The create database statement is used to create a new database. This will create a sub-directory in the DB_DATADIR directory and a database catalog with the same name as the database specified.

create database [if not exists] <database>

A database, its tables and other associated files can be physically deleted using the drop database statement.

drop database [if exists] <database>

Example

create database test
drop database test

Opening a Recital Database

To open a database and set it as the default database for any subsequent SQL statements, use the open database statement.

open database <database> [EXCLUSIVE | SHARED] [NOUPDATE] [VALIDATE]

The open database statement triggers the DBC_OPENDATA database event. If a dbc_opendata.prg program file exists in the database's directory, this will be run. If the dbc_opendata.prg program returns .F. (False), the open database operation will be abandoned.

Databases can also have an associated procedure library that is activated automatically when the database is opened. The procedure library must follow the naming convention dbc_<database>_library.prg and must exist in the database's directory. When the database is closed, the procedure library is also closed.

open database southwind

Tables from a database that is not currently open can be accessed by preceding the table name by the database name and a !.

select * from southwind!shippers

Recital also supports the MySQL compatible use statement to open a database.

use <database>

Creating a Recital Table using Recital SQL

The create table statement creates a new table in the current database, or a new free table if no database is open.

Table column names, data types and constraints can be defined. Table constraints can also be specified.

create table [if not exists] <table>
  (<column> <datatype> [<column constraint> [...]][,...] [<table constraint> [...]])

Data Types

Recital supports the following data types. For full details, please see Appendix A.

  • BIGINT
  • BIT
  • CHAR
  • CURRENCY
  • DATE
  • DATETIME
  • DECIMAL
  • DOUBLE
  • FLOAT
  • GENERAL
  • INTEGER
  • LOGICAL
  • LONG VARCHAR
  • LONG VARBINARY
  • MEDIUMINT
  • MEMO
  • NUMERIC
  • OBJECT
  • REAL
  • SHORT
  • SMALLINT
  • TEXT
  • TIME
  • TIMESTAMP
  • TINYINT
  • VARCHAR

Constraints

Table Constraints
check error foreign key
index key onclose
ondelete oninsert onopen
onrollback onupdate primary key
unique
Column Constraints
auto_increment autoinc calculated
check default description
error foreign key nocptrans
not null null primary key
range recalculate references
set check unique

Example

create table customer;
    (account_no char(5) description "Account Code";
        default strzero(seqno(),5),;
    title char(3) description "Personal Title",
    last_name char(16) description "Customer's Last Name",;
    first_name char(10) description "Customer's Given Name",;
    initial char(2) description "Customer's Middle Initial",;
    street char(25) description "Street Number and Name",;
    city char(12) description "City",;
    state char(2) description "State Abbreviation";
        check rlookup(customer.state,state);
        error "Invalid State",;
    zip char(10) description "Zip Code",;
    limit decimal(11,2) description "Credit Limit";
        recalculate,;
    balance decimal(11,2) description "Credit Balance";
        recalculate,;
    available decimal(11,2) description "Credit Available";
        calculated limit-balance,;
    notes long varchar description "Customer Notes",;
        start_date date description "Customer Start Date";
        default date(),;
        onopen "customer")

Loading Metadata from an Array or XML File

The create table statement also allows a new table to be created from metadata stored in an array or an XML file. In addition, data can optionally be loaded from the specified XML file.

create table [if not exists] <table>
  [from] xml <.xml file> [load] | from array <array> 

Examples

// Create table from XML file
select * from customer;
  save as xml cust.xml
 
create table customer2;
  from xml cust.xml
 
// Create table from array
create table newtable from array meta1

Modifying a Table

The alter table statement allows columns in an existing table to be added, modified, renamed or dropped. Both table and column constraints may be added or dropped.

Add column(s), column constraint(s) or table constraint

alter table <table> add [column] (<column> <datatype> [<column constraints>] [,...])
  | <table constraint>

Alter column(s), column constraint(s) or table constraint

alter table <table> alter [column] <column> [set default <value> | drop default]
  | (<column> <datatype> [<column constraint>] [,...])
  | constraint (<column> set <column constraint> <value> [,...])
  | <table constraint>

Drop column(s), column constraint(s) or table constraint

alter table <table> drop [column] <column>
  | (<column> [,...])
  | constraint (<column> <column constraint> [,...]) | <table constraint>

Add check table constraint

alter table <table> set check <condition> [error <message>]

Rename column

alter table <table> rename (<column>,<new column>)

Examples

// Add new column with column constraints
alter table customer add column timeref char(8);
  check validtime(timeref);
  error "Not a valid time string"
 
// Alter existing columns to add column constraints
alter table customer;
  alter column available calculated limit-balance;
  alter column limit recalculate;
  alter column balance recalculate
 
//or
alter table customer;
  alter (available calculated limit-balance,;
  limit recalculate,;
  balance recalculate)
 
// Add new column, add column constraint,
// modify column datatype and drop constraints then drop column
alter table customer add (timeref char(8))
 
alter table customer;
  alter constraint ;
  (timeref set check validime(timeref);
  error "Not a valid time string")
 
alter table customer;
  alter (timeref datetime);
  drop constraint (timeref check, timeref error)
 
alter table customer drop (timeref)
 
// Add an onupdate table constraint 
alter table customer;
  add onupdate "do check_update"
 
// Add and then remove check table constraint
alter table customer set check checkit() error "Invalid operation"
 
alter table customer drop check
 
// Rename column
alter table customer rename(first_name,forename)

Deleting a Table

The drop table statement is used to delete a table.

drop table <table>

Example

drop table example

Creating an Index

Indexes may be created using the column and table constraints (foreign key, primary key, unique) and the create table or alter table statements or with the create index statement.

create [unique] index <index> on <table> (<column> [asc | desc] [,...])
create [unique] index <index> on <table> (<expression> [asc | desc]) 

Example

create index lname;
  on example (lower(last_name))

Rebuilding or Removing an Index

Existing indexes may be rebuilt using the alter index statement.

alter index <index> on <table> rebuild

Example

alter index lname;
  on example;
  rebuild

The drop index statement is used to remove an index.

drop index <index> on <table>

Example

drop index lname;
  on example

Inserting Records into a Recital Table using Recital SQL

Records are added into a Recital table using the insert statement. A single row can be inserted by specifying the row contents in the values clause. Specifying a list of column names determines the columns whose values will be set.

insert into <table> [(<column> [,...])] values (<expr> [,...])

Example

insert into example;
  (account_no, last_name);
  values("01001","Brown")

One or more rows can be inserted by specifying a sub-query as the data source. Again the columns to be set can be restricted.

insert into <table> [(<column> [,...])] <sub-query>

Example

insert into example;
  (account_no, last_name);
  select * from temp

Data can also be loaded from an XML file, from an array, from a collection of memory variables with the same names as the table columns or from an object with property names that match the table columns.

insert into <table> from array <array> | from memvar | from name <object> | from [xml] <xml filename>

Examples

account_no = "01002"
title = "Ms"
last_name = "Smith"
 
insert into example;
  from memvar
 
arr1 = {"01003","Mr","Jones"}
 
insert into example;
  from array arr1
 
obj1 = object("account_no" => "01004","title" => "Mrs","last_name" => "Green")
 
insert into example;
  from name obj1
 
insert into example;
  from xml newcust.xml

Selecting Records from a Recital Table Using Recital SQL

The SQL select statement is used to query the data in one or more tables.

The columns to be returned can be defined individually, or the * can be used to return all columns. Constants, functions and aggregate functions (avg(), count(), max(), min(), sum()) can also be included and each element to be returned may be given an alias name.

select * | <column>|<"column"> | <constant> | <function> | <aggregate> [[as] <alias>] [,...] [, *] from <table-definition>

Examples

// Return all columns
select * from products
// Return specified columns
select 'Product Details: ',  productid, upper(productname) as Name,;
  unitprice*unitsinstock as stockholding;
  from products
// Return aggregate function results for the whole table
select avg(unitprice) Average, min(unitprice) Minimum,;
  max(unitprice) Maximum, count(unitprice) Count,;
  sum(unitprice) Sum;
  from products

Rows can be restricted in number and duplicates excluded:

select [all | distinct | distinctrow | top <expN> [percent]] <column-definition> from <table-definition>

select <column-definition> from <table-definition> limit [<offset>,] <row_count> | <row_count> offset <offset>] 

Examples

// Return one row for each group of rows with an identical contactname column
select distinct contactname from customers
// Return one row for each group of identical rows
select distinctrow contactname from customers
// Return the first 10 rows
select top 10 * from customers
// Return the first 50% of rows
select top 50 percent * from customers
// Select a maximum of 10 rows, starting from row 6
select * from customers limit 5,10
// or
select * from customers limit 10 offset 5

Rows can be queried from tables and views. These data sources can be related using inner joins, cross joins or outer joins (left, right and full). Each data source may be given an alias name.

select <column-definition> from <table> | <view> [[as] <t_alias>] [, ...]
select <column-definition> from <table|view> 
inner join | outer join | left [outer] join | right [outer] join <table2|view2> 
on <table|view>.<column> = <table2|view2>.<column> [, ...] 
select <column-definition> from <table> | <view> 
cross join | full [outer] join <table2|view2> 

Examples

// Select all rows from shippers and employees without relating the tables
select * from shippers ship, employees as emp
// Relate orders, order_details and shippers tables with multiple joins
select orders.orderid, orders.shipvia,;
  shippers.companyname, order_details.productid;
  from orders left outer join order_details;
  on orders.orderid = order_details.orderid,;
     orders inner join shippers;
     on orders.shipvia = shippers.shipperid
// Equivalent to 'select * from shippers, employees'
select * from shippers full join employees

Rows can be sorted by expression or column in ascending or descending order using the order by clause.

select <column-definition> from <table-definition> order by <expr> | <column-name> | <column-number> [asc | desc] [,...]

Example

// Return rows sorted in descending order by orderid then productid
select orderid, productid, quantity;
  from order_details;
  order by orderid, 2 desc

The group by clause allows results for a group of rows to be summarized as a single row. This is particularly used with the aggregate functions (avg(), count(), max(), min(), sum()). Groups can be based on expressions, column names and column numbers.

select <column-definition> from <table-definition> group by <expr> | <column-name> | <column-number> [,...]

Example

// Return the orderid and total for each order
select orderid as Order, sum(unitprice * quantity * (1-discount)) as Total;
  from order_details;
  group by orderid

The having and where clauses restrict the selection based on specified conditions. Only those rows for which the conditions are true are returned.

select <column-definition> from <table-definition> having <condition>
select <column-definition> from <table-definition> where <condition>

The where clause condition can be a nested select.

Examples

// Having condition
select sum(unitprice * quantity) as subtotal,;
  avg(unitprice * quantity) as averagetotal;
  from order_details;
  where discount = 0;
  group BY orderid;
  having quantity > 10
 
// Where condition with nested select
select shipname from orders, customers;
  where orders.customerid = customers.customerid;
  and employeeid = (select max(employeeid);
  from orders where orderdate = {07/19/1996});
  order by shipname

The into, save as and to clauses offer a wide range of output destinations and formats for the selected rows.

select <column-definition> from <table-definition> into <variable> [,...] |
 array <array-name> |
 cursor <cursor-name> |
 name | object <object-ref> |
 json <filename> |
 html <html filename> |
 xml <xml filename> |
 dbf | table <table-name> [database <database>]
select <column-definition> from <table-definition> save as <table name> | xml <xml filename>
select <column-definition> from <table-definition> to file <filename> | printer | screen

Examples

select productid from products into array array1
select * from shippers into html shippers
select employeeid, lastname, firstname from employees into table emp database newsouth
 
select employeeid, lastname, firstname from employees save as xml emp
 
select * from shippers to file shiptxt

Updating Records in a Recital Table using Recital SQL

The update statement is used to update records. The individual columns and their new values are listed and an optional condition can be specified to restrict the records affected.

update <table> set <column> = <expr> [,...] [where <condition>]

Example

update products;
  set unitsinstock = unitsinstock + unitsonorder, unitsonorder = 0;
  where supplierid = 1

Updates, along with insertions and deletions, can be processed as a transaction within a begin...end transaction block. Nested transactions are supported.

begin transaction [<transaction>]
<statements>
end transaction [<transaction>]

As are save points:

save transaction <savepoint> 

Transactions can be manually committed:

commit [transaction <transaction>]

or rolled back:

rollback [transaction <transaction> | <savepoint>] [to savepoint <savepoint>]

Examples

begin transaction trans1
    update orders;
      set requireddate = date()+30;
      where orderid = 10248
    update order_details set discount = 0.05;
      where orderid = 10248
    begin transaction trans2
        update order_details set discount = 0.10;
          where orderid = 10248
    rollback transaction trans1
end transaction
 
begin transaction trans3
    update orders;
      set requireddate = date()+30;
      where orderid = 10248
    update order_details set discount = 0.05;
      where orderid = 10248
    commit transaction trans3
end transaction
 
begin transaction trans4
    update orders;
      set requireddate = date()+30;
      where orderid = 10248
    save transaction parent_updated
    update order_details set discount = 0.10;
      where orderid = 10248
    rollback transaction parent_updated
end transaction
 
begin transaction trans5
    update orders;
      set requireddate = date()+30;
      where orderid = 10248
    savepoint parent_updated
    update order_details set discount = 0.15;
      where orderid = 10248
    rollback transaction parent_updated
end transaction

Deleting Records in a Recital Table using Recital SQL

The delete statement is used to delete records. It executes an immediate physical deletion of the specified records: records are permanently removed from the table and cannot be recalled.

delete from <table> [where <condition>]

Example

delete from currorders where shippeddate < date()

Using a Cursor to Select, Update and Delete Records

A cursor is a temporary selection of rows that can be fetched one at a time and processed further. The update and delete statements can both operate on the most recently fetched row from a cursor.

update <table> set <column> = <expr> [,...] where current of <cursor>

delete from <table> where current of <cursor>

A cursor must be declared:

declare <cursor> cursor for select <statement>

then opened:

open <cursor>

then fetched into memory variables or an XML file:

fetch [next | previous | first | last | absolute <row> | current | relative <row>] <cursor>
  into [<variable> [, ...] | xml <xml filename>]

Once processing is complete, the cursor can be closed:

close <cursor>

then released:

drop cursor <cursor> 

Example

// Declare the cursor to select records from the orders table
declare cursor1;
  cursor for;
  select orderid, customerid, orderdate;
  from orders;
  where requireddate < date()
 
// Open the cursor
open cursor1
 
// Fetch records one at a time from the cursor and update them
fetch cursor1;
  into m_order, m_customer, m_orderdate
  do while sqlcode = 0
    if not empty(m_order) and empty(shippeddate)
        update orders;
          set shippeddate = date();
          where current of cursor1
    endif
    fetch cursor1;
      into m_order, m_customer, m_orderdate
enddo
 
// Close the cursor and free up any resources used for the cursor
close cursor1
drop cursor cursor1

Obtaining Information about a Recital Table using SQL

System defined read-only system tables can be queried using the select statment to provide a range of information about Recital tables.

System tables include the following:

  • sysbestrowidentifier - description of a table’s optimal set of columns that uniquely identifies a row
  • syscolumnconstraints - description of the constraints for a table’s columns
  • syscolumnprivileges - description of the access rights for a table’s columns
  • syscolumns - description of the table columns available in the catalog
  • syscrossreference - description of how one table imports the keys of another table
  • sysexportedkeys - description of the foreign key columns that reference the primary key columns
  • sysimportedkeys - description of the primary key columns that are referenced by the foreign key
  • sysindexinfo - description of a table’s indices and statistics
  • sysprimarykeys - description of the primary key columns in the table
  • systableconstraints - description of the constraints for each table available in the catalog
  • systableprivileges - description of the access rights for each table available in the catalog
  • systables - description of the tables available in the catalog
  • systabletypes - table types available in the database system
  • sysversioncolumns - description of the columns in a table that are automatically updated when any row is updated

Examples

select * from syscolumns;
  where table_cat = "southwind" and table_name = "orders"
select * from sysindexinfo;
  where table_cat = "southwind" and table_name = "orders"

Executing Procedures and Prepared Statements using Recital SQL

The create procedure statement allows for the creation of a stored procedure in a Recital database.

create procedure <procedure> as <procedure source code>
endcreate

To remove a stored procedure from a database, use the drop procedure statement.

drop procedure <procedure> 

Example

create procedure creaxml as
select orders.orderid, orders.customerid, employees.employeeid,;
  employees.lastname, employees.firstname, orders.orderdate,;
  orders.freight, orders.requireddate, orders.shippeddate,;
  orders.shipvia, orders.shipname, orders.shipaddress, orders.shipcity,;
  orders.shipregion, orders.shippostalcode, orders.shipcountry,;
  customers.companyname, customers.address, customers.city,;
  customers.region, customers.postalcode, customers.country;
  from orders inner join customers;
  on customers.customerid = orders.customerid,;
  orders inner join employees;
  on orders.employeeid = employees.employeeid;
  save as xml orderinfo
endcreate
 
drop procedure creaxml

The prepare statement is used to set up an SQL statement for subsequent execution.

prepare <statement> from :<variable>

Variables used as parameters in the SQL statement can then be set just before executing it using the execute statement.

execute <statement> using :<variable>[,<variable2>[,...]]

Examples

stmtbuf = "select * from example where account_no = ?"
prepare mystmt from :stmtbuf
m_acc = "00002"
execute mystmt using :m_acc
 
m_acc = "00003"
execute mystmt using :m_acc
 
stmtbuf = "insert into example (account_no, last_name, forename) values (?,?,?)"
prepare mystmt from :stmtbuf
buf1 = "99999"
buf2 = "Smith"
buf3 = "John"
execute mystmt using :buf1, :buf2, :buf3
 
stmtbuf = "delete from example where account_no = ?"
prepare mystmt from :stmtbuf
buf1 = "99999"
execute mystmt using :buf1

The execute immediate statement is used to prepare and execute immediately a delete, insert, or update SQL statement.

m_acc = "00003"
execute immediate <statement> 

Example

execute immediate select * from example where account_no = m_acc

The sysresultset system table returns the singleton result from any Recital expression.

select set("exclusive") as excl, time() as time from sysresultset