REBOL Database Access

SDK Documentation

Contents

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.

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).

What is MySQL?

MySQL is an open source database management system. Complete information about the MySQL database is available at www.mysql.com.

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.)

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.

1. Open a database connection port.
   Use OPEN to establish a connection to an external database. See Opening a Database Connection Port for more details.
2. 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.
3. 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.
4. 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.
5. Close the command port.
   Use CLOSE to close the open command port. See Closing a Command Port for more details.
6. Close the database connection port.
   Use CLOSE to shut the port.

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

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 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.

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.

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.

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 without 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.

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.

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.

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.

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]

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.

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:

close connect-name

where connect-name is the REBOL word defined as the open connection port.

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.

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

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]

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.

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

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

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:

one-rec: first db-port

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:

pick db-port 1

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:

all-recs: copy db-port

If COPY is performed on an empty command port queue, the none value is returned.

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

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"
]

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:

close command-port

where command-port is the REBOL word assigned to the command port.

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.

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

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:

insert db-port ['tables]

The above example returns a list of all the tables in the database, including system tables, user tables, system views.

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"]

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.

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.

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.

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]

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"]

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.

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

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

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"]

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"]

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"]

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"]

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"]