REBOL Database Access
REBOL/Command/SDK Developer Reference
Contents:
1. Overview
1.1 What is ODBC?
1.2 What is MySQL?
1.3 ODBC and REBOL
1.4 Using the REBOL/Command Database Interfaces
2. Opening a Database Connection Port
2.1 Providing a User Name and Password
2.2 Connecting without a DSN (ODBC only)
2.3 Database Connection Port Options
2.4 Changing Database Connection Port Options
2.5 SQL Transaction Commands
2.6 Closing Database Connection Ports
3. Sending SQL Commands
3.1 Opening Command Ports
3.1.1 Opening Multiple Command Ports
3.1.2 Command-Port Options
3.2 Inserting SQL Statements
3.3 Using REBOL Values in SQL Statements
3.4 Retrieving Command Results
3.5 Performance Considerations
3.6 Handling Errors
3.7 Closing a Command Port
4. Retrieving Lists of Database Tables and Columns
4.1 Retrieving a List of Tables
4.1.1 Examples
4.1.2 Information in a Table List
4.2 Listing Table Columns
4.2.1 Examples
4.2.2 Information in a Column List
5. More Examples
5.1 Opening the Database
5.2 Reading Rows From the Database
5.3 Adding Rows to the Database
5.4 Modifying Rows in the Database
5.5 Removing Rows From the Database
5.6 Copying Database Data to and from a REBOL File
1. Overview
The REBOL/Command Database Interfaces enable REBOL programs to use SQL
commands to interact with external databases. This chapter describes
how to use the REBOL/Command Database Interfaces to ODBC (Windows),
MySQL, and Oracle.
Where the examples in this document refer to a port scheme as scheme://,
the word scheme should be changed to odbc, oracle, or mysql as
appropriate for the interface method being used.
1.1 What is ODBC?
Microsoft's ODBC (Open Database Connectivity) is a standard application
program interface that enables programs to access data from any
ODBC-compatible application, regardless of the database management
system.
ODBC inserts a middle layer, called the ODBC driver, between the
application and the database management system. The ODBC driver, which
is a database driver, translates the application's data queries into
commands the database management system understands. Both the
application and the database management system must be ODBC-compliant.
To be ODBC compliant, an application must be capable of issuing ODBC
commands and a database management system must be capable of responding
to ODBC commands. Current versions of ODBC support the use of structured
query language (SQL).
1.2 What is MySQL?
MySQL is an open source database management system. Complete information
about the MySQL database is available at
www.mysql.com.
1.3 ODBC and REBOL
The REBOL/Command Database Interfaces interacts with ODBC drivers using
ports. A port is a type of REBOL object that enables programs to access
and manipulate external data. Ports can be operated on in the same
manner as other REBOL objects. The Database Interfaces uses two types of
database ports: connection ports and command ports. The Database
Interfaces ports are discussed in more detail later in this chapter.
(See Opening a Database Connection Port and Opening Command Ports.)
1.4 Using the REBOL/Command Database Interfaces
The following steps provide an overview of using the Database Interfaces
to access databases through an ODBC driver. These steps are described in
more detail in the remaining sections of this chapter.
- Open a database connection port. Use OPEN to establish a
connection to an external database. See Opening a
Database Connection Port for more details.
- Open a command port. Use FIRST to open a database command port,
which is used for sending SQL commands and receiving results. See Opening Command Ports for more details.
- Send SQL statements to the database. Use INSERT on the command
port to send SQL statements to the database. See Inserting SQL Statements for more details.
- Retrieve the data returned from the database. Use FIRST, PICK,
or COPY on the command port to retrieve the data. See Retrieving Command Results for more details.
- Close the command port. Use CLOSE to close the open command
port. See Closing a Command Port for more
details.
- Close the database connection port. Use CLOSE to shut the
port.
| Special Connections | |
ODBC: The database interfaces require the Windows ODBC Driver
Manager and a correctly configured database driver be installed in order
to function. Please refer to your database documentation for more
information.
Oracle: The Oracle Interface requires that all Oracle Client
libraries and configuration files be installed correctly in order to
function. Please refer to your Oracle documentation for more information
|
2. Opening a Database Connection Port
Before accessing a database, you must open a database connection port
between a REBOL program and the database. To open the connection
port, use the following syntax:
ODBC and Oracle:
connect-name: open odbc://database-name
connect-name: open oracle://database-name
|
where CONNECT-NAME is a REBOL variable that holds the port and
DATABASE-NAME is the name of the database as configured in
the ODBC driver manager.
If there is a problem connecting to the named database REBOL returns an
error from ODBC driver manager. Refer to the ODBC driver documentation
for descriptions of possible errors.
MySQL:
connect-name: open mysql://host-name/database-name
|
where
where CONNECT-NAME is a REBOL variable that holds the port, HOST-NAME is
the name or IP address of the machine that is running the MySQL
database, and DATABASE-NAME is the name of the database in the MySQL
database system that will be used.
If there is a problem connecting to the named database REBOL returns an
error from MySQL. Refer to the MySQL documentation for descriptions of
possible errors.
| Not for Commands | |
Connection ports cannot be used for sending SQL commands to the
database. All SQL commands must be sent through command ports
that are opened in the database connection port. See Sending SQL Commands for details.
|
2.1 Providing a User Name and Password
Some databases require a user name and password authentication
to open a connection port. To open a database that requires
authentication, use the following syntax:
connect-name: open scheme://user:password@database-name
|
where USER represents a valid user name, and PASSWORD
represents a valid password for the specified user name.
2.2 Connecting without a DSN (ODBC only)
In cases where you do not have control over the ODBC control panel to
set up a DSN for your database or where you may want the higher
efficiency, you can connect to a datasource wihtout using a DSN. Instead
of using the URL syntax, a block is pased to open. The block has
the following syntax:
connect-name: open [
scheme: `odbc
target: join "{DRIVER=Microsoft Access Driver (*.mdb)}; "
"DBQ=c:\Databses\mydb.mdb"
]
|
where SCHEME is always set to `odbc, and TARGET is a complete connection
string defining the datasource, driver, and any other needed parameters.
The example above uses a Micrsoft Access database and driver. Any number
of standard and driver-specific parameters may be passed in the target
string.
2.3 Database Connection Port Options
ODBC and Oracle
Database connection port options control how SQL commands sent by
command ports are handled. These options can be set in the locals
object in the connection port. To view the current settings of
the locals object of an open connection port, use PROBE. In the
following example, PROBE is used to display the locals object of a
connection port defined as dbase:
probe dbase/locals
make object! [
auto-commit: true
access: `write
rows: 10
]
|
Each field in the locals object contains a connection port
option.
|
Option
|
Type
|
Settings
|
Description
| |
auto-commit
|
logic!
|
true (default) false
|
Controls how SQL transactions that modify the database are
handled. When set to true, it is assumed that each SQL command
is a complete unit, and therefore, is committed to the database
immediately upon execution. When set to false, SQL commands that
modify the database are queued as a transaction until a COMMIT
or ROLLBACK command is manually sent through the connection port
to the database (see SQL Transaction
Commands).
Changing the setting from false to true causes any queued
transactions to be committed to the database immediately.
| |
access
|
word!
|
'write (default) 'read
|
Determines whether the database can be updated. When set to 'write, SQL
commands can add, change, and delete data in the database. When set to
'read, SQL commands are prevented from adding, changing, or deleting
data in the database, and an access error is issued if a command
attempts to do so.
| |
rows
|
integer!
|
10 (default)
|
Specifies how rows of data (records) are retrieved when COPY is
used to read rows from a command port (see Retrieving Command Results).
REBOL allocates a temporary block for the specified number of
rows. This block is used as an intermediary between the database
and REBOL/Command. If the result set of an operation contains
fewer rows than the rows setting, REBOL retrieves all the rows
at once . If the result set contains more rows than the rows
setting, REBOL reads the command port multiple times, each time
retrieving the number of rows specified in the rows setting,
until it reaches the end of the result set.
When accessing large database tables, setting rows to a greater
number may improve performance, by reducing the number of
connections needed to retrieve the result set. However, this
will increase the amount of memory necessary for the temporary
block.
The rows setting must be a positive integer greater than 0.
|
MySQL:
MySQL databse connection ports do not have any user options. The user
options such as auto-commit, read/write access, and a block setting for
the number of rows read are not valid for MySQL ports.
2.4 Changing Database Connection Port Options
ODBC and Oracle:
To change a database connection port option, set the path of the option
to the new value, as shown in the following examples:
close command-port
dbase/locals/auto-commit: false
dbase/locals/access: 'read
update dbase
open command-port
|
Before changing the access or auto-commit options, first close
any open command ports (see Closing a Command
Port). After changing the access or auto-commit option, use
UPDATE to update the connection port options. Once the port is
updated, reopen the command ports (see Opening Command Ports). For example:
It is not necessary to use UPDATE after changing the rows option.
| Note | |
The rows option can also be set at the command port level. See Command-Port Options for details. Also, some databases
do not support all of the transaction and connection options. Please
refer to the documentation for your database for complete information.
|
MySQL:
MySQL ports do not have any user optoins.
2.5 SQL Transaction Commands
ODBC and Oracle:
An SQL transaction is a set of SQL commands that update the database and
need to be handled as a single unit, instead of individually. When the
auto-commit connection port option is set to false, SQL commands
that update the database are queued as a transaction. Transactions must
be handled manually by inserting one of the following SQL commands into
the connection port:
- COMMIT: Commits current transactions in a command port to the database.
- ROLLBACK Cancels current transactions for a command port.
To insert either of these commands into a connection port, use
INSERT, as shown in the following examples:
insert db-port [COMMIT]
insert db-port [ROLLBACK]
|
| Note | |
If the auto-commit connection port option is set to true,
inserting COMMIT or ROLLBACK causes an error.
|
MySQL:
MySQL databases require special tables in order to perform transactions.
REBOL does not support performing transactions at this time though it
may be possible to manually perform transactions entirely through SQL
commands. Check the MySQL documentation for more details.
2.6 Closing Database Connection Ports
Closing a database connection port also closes all open command ports
for that connection. The following example shows the syntax for closing
a connection port:
where connect-name is the REBOL word defined as the open
connection port.
3. Sending SQL Commands
Once a connection port is opened, command ports can be opened
for that connection. REBOL interfaces with an open database through
command ports. Command ports are a mechanism for sending SQL
statements to the database and receiving results.
3.1 Opening Command Ports
To open a command port from a connection port, use FIRST. The
following example shows the syntax for opening a command port:
cport-name: first connect-name
|
where cport-name is the variable that holds the command port, and
connect-name represents the REBOL word defined as the open
connection port.
In the following example, a connection port is opened and
defined as the word dbase. Then a command port, db-port, is
opened for the dbase connection port.
dbase: open scheme://my-database
db-port: first dbase
|
3.1.1 Opening Multiple Command Ports
The number of commands ports that can be open simultaneously for the
same connection port is limited only by available memory and
the ODBC driver being used.
Iterative functions can be used to open multiple command ports, as shown
in the following example:
portlist: [cmd-port1 cmd-port2 cmd-port3 cmd-port4]
foreach cmd-port portlist [set cmd-port first dbase]
|
3.1.2 Command-Port Options
ODBC and Oracle:
Command-port options inherit their settings from the
connection port options (see Connecting
without a DSN (ODBC only)). These settings are stored in the
command-port locals object. As with connection port options,
you can use PROBE to display the current settings of the command-port
locals object.
In the command-port locals object, you can change only the rows setting.
The rows setting specifies how many rows of data (records) are retrieved
when an SQL command reads the database. Each open command port can have
a different rows setting. In the following example, the rows setting of
the command port called db-port is changed to 500. The new settings of
the command-port locals object are displayed using PROBE.
db-port: first dbase
db-port/locals/rows: 500
probe db-port/locals
make object! [
auto-commit: on
access: write
rows: 500
]
|
MySQL:
REBOL does not support options for MySQL command ports. These values are
inherited from the parent connection port but have no effect on the
functioanlity of the command port.
3.2 Inserting SQL Statements
SQL statements are inserted as a string or block into a command port.
The command port sends the SQL statements to the driver manager, which
sends the statement to the ODBC driver. The database driver then
executes the statement. Results from the database can be read from the
command port.
The following example shows the syntax for inserting an SQL statement
into a command port:
insert cmd-port "SQL-statement"
|
where cmd-port represents the REBOL word assigned to the open command
port, and SQL-statement represents any valid SQL statement.
The following examples show SQL statements being inserted into the
command port, db-port.
insert db-port "SELECT * FROM table1"
insert db-port "SELECT * FROM table2 where (name='john')"
|
3.3 Using REBOL Values in SQL Statements
To include a REBOL value in an SQL statement, enclose the statement
string in a block and use question marks (?) as parameter markers to
indicate where REBOL values are to be inserted. Also inside the block,
but after the SQL statement string, list the REBOL values to be inserted
in place of the parameter markers. The REBOL values must appear in the
same order as the parameter markers they are to replace.
In the following example, the value of name is inserted into the SQL
statement in place of the question mark:
name: "John"
insert db-port ["SELECT * FROM table1 WHERE (name=?)" name]
|
The next example inserts multiple REBOL values into an SQL statement:
fname: "Johnny"
lname: "Johnson"
title: "President"
phone: "(707) 555-1212"
insert db-port [{
INSERT INTO table1 (First, LastName, Title, Phone)
values (?, ?, ?, ?)
} fname lname title phone
]
|
Because of ODBC restrictions, REBOL values cannot be used in SQL
statements in the following ways:
- As WHERE values in SELECT statements
- As both elements in a comparison operation
- As both the first and second operands or the last operand of a BETWEEN operation
- As both expressions and the first value of an IN operation
- As the argument to the SET or AGGREGATE functions, which are SQL
functions that return a single value from sets of values like SUM and
COUNT
3.4 Retrieving Command Results
Many SQL statements sent by a command port return results from the
database. Results, which are usually database rows, are queued in the
command port. As you read database rows from the command port, they are
removed from the queue.
To retrieve the first or next row queued in a command port, use FIRST.
In the following example, the first row in the command port, db-port, is
retrieved and assigned to the REBOL word one-rec:
When FIRST is used to access a command port that has an empty queue
(meaning no data was returned or all rows have been retrieved), an error
will be returned, as shown in the following example:
first db-port
** Script Error: Out of range or past end.
** Where: first db-port
|
To avoid receiving an error when the command port queue is empty, use
PICK to retrieve the rows, as shown in the following example:
When the command port queue is empty, PICK returns none. When retrieving
data from a command port, PICK must use the index argument 1. Using any
other index argument besides 1, causes PICK to always return none.
Because PICK returns none when the command port queue is empty, pick can
be used to retrieve rows using a loop, as shown in the following
example:
while [record: pick db-port 1][print record]
|
In this example, PICK retrieves rows from the command port queue and
returns none when the queue is empty. The return value none causes the
WHILE loop to exit.
To retrieve a specific number of rows queued in a command port, use COPY
with the /part refinement. In the following example, 10 records are
retrieved from the command port, db-port, and defined as the REBOL word
ten-recs:
ten-recs: copy/part db-port 10
|
To retrieve all the rows queued in a command port, use COPY without any
refinement. In the following example, all the rows queued in the db-port
command port are retrieved and assigned to the REBOL word all-recs:
If COPY is performed on an empty command port queue, the none value is
returned.
3.5 Performance Considerations
ODBC and Oracle:
Some database tables have columns defined as large, unbounded objects,
such as long text or binary data columns. Reading rows that contain
large columns from the database negatively affects performance because
the rows are read individually, instead of in blocks. The columns named
Pic1 and Pic2 contain binary image information that is stored in the
database as large object fields.
insert db-port "SELECT Name,Dte1,Pic1,Dte2,Pic2 FROM Info"
data: copy db-port
|
To maximize performance when reading these types of columns, send two
SELECT statements to the database, instead of one. The first SELECT
statement should return all columns that do not contain large object
data. The second SELECT statement should return the remaining large
object columns. For example, the following SQL SELECT statement selects
five columns from a table called Info.
insert db-port "SELECT Name,Dte1,Dte2 FROM Info"
data1: copy db-port
insert db-port "SELECT Pic1,Pic2 FROM Info"
pictures: copy db-port
|
MySQL:
MySQL ports communicate with the MySQL database through TCP ports. When
a request for data is sent, all of the results must be read before
another request can be sent. The effect is that if a very large table of
data is requested and only a few rows are picked from the port, when the
next request is sent
3.6 Handling Errors
When the ODBC driver returns an error, REBOL reports it like any other
error. Errors can be caught using TRY, as shown in the following
example:
if error? try [insert db-port "SELECT * FROM bogus-table"][
print "error happened"
]
|
3.7 Closing a Command Port
Open command ports hold resources both within REBOL and in the database.
Therefore, command ports should always be closed when they are no longer
needed. To close a command port, use CLOSE, as shown in the following
example:
where command-port is the REBOL word assigned to the command port.
4. Retrieving Lists of Database Tables and Columns
REBOL/Command provides dialects that you can insert into command ports
for retrieving a list of database tables or columns. You can retrieve
lists of all the tables or columns, or use pattern matching with SQL
wildcard characters to list specific tables or columns.
| Note | |
SQL wildcard characters are different from those used by REBOL. SQL uses
% to represent one or more characters (equivalent to * in REBOL) and _
to represent one character (equivalent to ? in REBOL).
|
Requesting a list of tables or columns, returns the results to a command
port just like any other SQL request. Use FIRST, PICK, or COPY to
retrieve the results from the command port. Each row in a list provides
specific information about each table or column. For details about what
information is returned in a table or column list, see Information in a Table List and Information in a Column List
4.1 Retrieving a List of Tables
To get a list of all the tables in a database, insert the 'tables
dialect word into the command port, as shown in the following example:
The above example returns a list of all the tables in the
database, including system tables, user tables, system views.
| Note | |
Results of this operation are driver dependent, as some drivers do not
support the `tables command. Information in an ODBC
Table List below portrays typical results.
|
If you do not want a list of all the tables, you can use pattern
matching and SQL wildcard characters to list only specific tables. You
can use pattern matching on table, schema names, and table types. The
syntax for using pattern matching is as follows:
insert db-port ['tables "table" "schema" "TABLE-TYPE,..."]
|
where table represents the table name pattern you want to match and
schema represents the schema name pattern you want to match. TABLE-TYPE
represents the type of table you want to match. You can list more than
one table type to match. Valid types are TABLE, VIEW, SYSTEM TABLE,
GLOBAL TEMPORARY, LOCAL TEMPORARY, ALIAS, SYNONYM, and any
data-source-specific types. Note that the table type must typed in all
uppercase.
Use NONE if you do not want to use pattern matching on either the table
name or schema name, as shown in the following example:
insert db-port ['tables none "schema-name"]
|
4.1.1 Examples
The following example returns a list of all the tables with names that
start with foo:
insert db-port ['tables "foo%"]
|
The next example lists all the tables that end with X and reside in the
schema named Exp.
insert db-port ['tables "%X" "Exp"]
|
The following example lists all the tables that are of the type TABLE or
SYSTEM TABLE:
insert db-port ['tables none none "TABLE, SYSTEM TABLE"]
|
Note how the previous example uses NONE in place of the table and schema
to prevent pattern matching on those names.
The next example uses REBOL words defined as pattern strings to take the
place of the quoted strings in the block:
table-pattern: "Finance%"
schema: "Exp"
insert db-port ['tables table-pattern schema]
|
This example lists all tables with names that start with Finance and
reside in the Exp schema.
4.1.2 Information in a Table List
ODBC:
The table below describes the information in each row returned
to the command port when a table list is requested through ODBC.
|
Field
|
Description
| |
Table Catalog
|
Contains the name of the table catalog. If the table does not have a
catalog or the database does not support catalogs, the value of this
field is an empty string.
| |
Table Scheme
|
Contains the schema the table resides in. If the table does not have a
schema or the database does not support schemas, the value of this field
is an empty string.
| |
Table Name
|
Contains the name of the table.
| |
Table Type
|
Contains the type of table.
| |
Remarks
|
Contains the table remarks.
|
Oracle:
The table below describes the information in each row returned
to the command port when a table list is requested through
Oracle.
|
Field
|
Description
| |
Table Scheme
|
Contains the schema the table resides in.
| |
Table Name
|
Contains the name of the table.
| |
Table Type
|
Contains the type of table.
|
MySQL:
The table below describes the information in each row returned
to the command port when a table list is requested through
MySQL.
|
Field
|
Description
| |
Table Name
|
Contains the name of the table.
|
4.2 Listing Table Columns
To get a list of all the columns in a database table, insert the
'columns dialect word into the command port, as shown in the following
example:
insert db-port ['columns "table-name"]
|
The above example returns data about all the columns in the
table.
| Note | |
Results of this operation are driver dependent, as some drivers
do not support the `columns command. Information in an ODBC
Column List below shows typical results.
|
Just as with table lists, with column lists you can use pattern matching
and SQL wildcard characters to list only specific columns from a table.
You can use pattern matching on table name, column name, and schema. The
syntax for using pattern matching for columns lists is as follows:
insert db-port ['columns "table" "column" "schema"]
|
where table represents the table name pattern you want to match, column
represents the column name pattern you want to match, and schema
represents the schema name you want to match.
Use NONE if you do not want to use pattern matching on either the table
name or column name, as shown in the following example:
insert db-port ['columns none none]
|
4.2.1 Examples
The following example returns a list of all the columns in the table
named Credits:
insert db-port ['columns "Credits"]
|
The next example returns a list of all the columns with a name that
starts with debit from the table named All_Debits:
insert db-port ['columns "All_Debits" "debit%"]
|
The following example returns a list of all columns from all tables in
the Exp schema:
insert db-port ['columns none none "Exp"]
|
4.2.2 Information in a Column List
ODBC:
The table below describes the information in each row returned
to the command port when a column list is requested through
ODBC.
|
Field
|
Description
| |
Catalog Name
|
Contains the name of the catalog in which the column resides. If the
column does not reside in a catalog, this field is empty.
| |
Schema Name
|
Contains the name of the schema in which the column resides. If the
column does not reside in a schema, this field is empty.
| |
Table Name
|
Contains the name of the table in which the column resides.
| |
Column Name
|
Contains the name of the column.
| |
Data Type
|
Contains the data type of the column. This can be an ODBC SQL data type
or a driver-specific SQL data type.
| |
Data Type Name
|
Contains the driver-specific data type name. This value can differ from
the SQL Data Type.
| |
Column Size
|
Contains the ODBC-defined data size of the column.
| |
Buffer Length
|
Contains the length, in bytes, of data transferred from the driver. This
value may differ from the Column Size for certain data types.
| |
Decimal Digits
|
Contains the total number of significant digits to the right of the
decimal point. This value has no meaning for non-decimal data types.
| |
Precision
|
Contains the precision of numeric data types. This value has no meaning
for non-numeric data types.
| |
Nullable
|
Indicates whether the column is nullable or not.
| |
Remarks
|
Contains the textual remarks for the column.
| |
Default Value
|
Contains the default value of the column.
| |
SQL Data Type
|
Contains the SQL data type of the column.
| |
SQL Datetime Subtype
|
Contains the subtype for a datetime data type. This value has no meaning
for non datetime data types.
| |
Char Octet Length
|
Contains the maximum length of a string or binary data column. This
value has no meaning for non character data types.
| |
Ordinal Position
|
Indicates the position of the column in the table definition.
| |
Is Nullable
|
Indicates whether the column contains NULL values.
|
Oracle:
The table below describes the information in each row returned
to the command port when a column list is requested through
Oracle.
|
Field
|
Description
| |
Owner
|
Contains the name of the schema in which the column resides.
| |
Table Name
|
Contains the name of the table in which the column resides.
| |
Column Name
|
Contains the name of the column.
| |
Data Type
|
Contains the data type of the column.
| |
Data Length
|
Maximum length of the column in bytes.
| |
Data Precision
|
The decimal precision for NUMBER columns, binary precision for FLOAT
columns, and empty otherwise.
| |
Data Scale
|
Scale for NUMBER columns.
| |
Nullable
|
Indicates whether the column is nullable or not.
| |
Column ID
|
Contains the textual remarks for the column.
| |
Default Length
|
Contains the length of the default value of the column, if specified.
| |
Data Default
|
Contains the default value for the column, if specified.
| |
Num Distinct
|
Number of distinct values in the column.
| |
Low Value
|
Next to lowest value in the column. See your Oracle Documentation for
further information.
| |
High Value
|
Next to highest value in the column. See your Oracle Documentation for
further information.
| |
Density
|
Density of the column.
| |
Num Nulls
|
Number of rows which contain null values.
| |
Num Buckets
|
Number of buckets used when analyzing the table.
| |
Last Analyzed
|
Timestamp when the table was last analyzed.
| |
Sample Size
|
Sample size used during the last analyze.
|
MySQL:
The table below describes the information in each row returned
to the command port when a column list is requested through
MySQL.
|
Field
|
Description
| |
Table Name
|
Contains the name of the table in which the column resides.
| |
Column Name
|
Contains the name of the column.
| |
Data Type
|
Contains the data type of the column. This can be an MySQL SQL data type
or a driver-specific SQL data type.
| |
Nullable
|
Indicates whether the column contains NULL values.
| |
Key
|
Contains a value if the column is indexed.
| |
Default Value
|
Contains the default value of the column.
| |
Extra
|
Contains extra information about the column.
|
5. More Examples
Using a simple database, the following examples build on each other to
show how to use the Database Interfaces to perform a variety of database
tasks, including:
- Opening a database connection port and command port
- Reading rows
- Adding rows
- Modifying rows
- Removing rows
- Copying database data to and from a REBOL file
5.1 Opening the Database
The following example opens a database connection port to a simple book
collection database, books, which has individual tables for book,
author, and topic.
books: open scheme://books
db-port: first books
|
5.2 Reading Rows From the Database
The following examples show three ways to use the SQL SELECT statement
to read the book title and publisher from the Books table.
One row at a time:
insert db-port "SELECT Title,PublisherName FROM Books"
probe pick db-port 1
["Dirk Luchte" "GGG&G Publishing"]
|
Three rows at a time using a FOREACH loop:
insert db-port "SELECT Title,PublisherName FROM Books"
foreach title (copy/part db-port 3) [probe title]
["Dirk Luchte" "GGG&G Publishing"]
["Planning Your Career" "Jean-Paul Deloria"]
["Diamonds" "Ramona Publishing"]
|
All the records at once using a FOREACH loop:
insert db-port "SELECT Title,PublisherName FROM Books"
foreach topic copy db-port [probe topic]
["Dirk Luchte" "GGG&G Publishing"]
["Planning Your Career" "Jean-Paul Deloria"]
["Diamonds" "Ramona Publishing"]
["Techniques of Tai Chi" "Ramona Publishing"]
["My Family" "GGG&G Publishing"]
["Collected Prose by R. Service" "Dodd, Mead & Company"]
["Collected Poems of R. Service" "G. P. Putnam's Sons"]
|
5.3 Adding Rows to the Database
The next two examples show how to add a new book to the books table. The
row consists of a book title and its publisher's name. After the row is
added, it is read from the database.
insert db-port {
INSERT INTO books (Title,PublisherName)
VALUES ('Ancient Greece','Acropolis Books, Inc')
}
insert db-port "SELECT Title,PublisherName FROM Books"
foreach title copy db-port [
if find first title "Greece" [probe title]
]
["Ancient Greece" "Acropolis Books, Inc"]
|
5.4 Modifying Rows in the Database
This example shows how to modify then retrieve an existing row
in the books table.
insert db-port {
UPDATE books SET
Title='Ancient Greeks',
PublisherName='Acropolis Books, Inc'
WHERE (Title='Ancient Greece')
}
foreach title copy db-port [
if find first title "Ancient" [probe title]
]
["Ancient Greeks" "Acropolis Books, Inc"]
|
5.5 Removing Rows From the Database
The next example shows how to remove all books from the books table that
were published by GGG&G Publishing. All the rows in the table are
then retrieved and displayed with PROBE to show that the removal was
successful.
insert db-port {
DELETE FROM books
WHERE (PublisherName='GGG&G Publishing')
}
insert db-port "SELECT Title,PublisherName FROM Books"
foreach topic copy db-port [probe topic]
["Planning Your Career" "Jean-Paul Deloria"]
["Diamonds" "Ramona Publishing"]
["Techniques of Tai Chi" "Ramona Publishing"]
["Collected Prose by R. Service" "Dodd, Mead & Company"]
["Collected Poems of R. Service" "G. P. Putnam's Sons"]
["Ancient Greece" "Acropolis Books, Inc"]
|
5.6 Copying Database Data to and from a REBOL File
Data retrieved from a database can be copied into a REBOL file, and data
in a REBOL file can be copied into a database table.
The following example retrieves all the data in the authors table from
the book database and copies it to the REBOL file authors.r.
insert db-port "SELECT * FROM authors"
save %authors.r copy db-port
|
The following example removes all the data from the Authors table, then
copies the data from the REBOL authors.r file back into the database
table.
insert db-port "DELETE FROM authors"
insert db-port {
SELECT FName,LName,Nation FROM Authors
}
foreach author copy db-port [probe author]
foreach record load %authors.r [
insert db-port join[{
INSERT INTO Authors (FName,LName,Nation,Notes)
VALUES (?, ?, ?, ?)
}] next record
]
insert db-port {
select FName,LName,Nation from Authors
}
foreach author copy db-port [probe author]
["Andrew" "Fuller" "American"]
["Janet" "Leverling" "American"]
["Margaret" "Peacock" "French"]
["Laura" "Callahan" "American"]
["R." "Service" "Scottish"]
|
| | REBOL/MakeDoc 2.0 | REBOL is a registered trademark of REBOL Technologies Copyright 2003 REBOL Technologies | 5-Aug-2003 |
|