The SQL functionality for the time being is limited only to MySQL connectivity. See http://www.mysql.com for more information about this server product. Full documentation of MySQL can be found at this site as well as links to downloadable binaries (if your distro does not come with it preinstalled). Discussion of SQL is also not presented here (as well as any MySQL specific extensions).

Summary

SQL Functions	The SQL functionality for the time being is limited only to MySQL connectivity.
rsql_connect	Establish a connection to a database.
rsql_connection_info$	Get information about an existing connection.
rsql_ping	Verify a database connection is active and working.
rsql_last_insert_id	Return the numeric id of the last record inserted if that id was auto-generated by the system.
rsql_error$	Return the description of the last error.
rsql_prepare	Prepare a new statement for execution.
rsql_query_param	Set the parameter value for a prepared query.
rsql_execute	Execute an SQL command.
rsql_release	Release all resources associated with a query.
rsql_fetch_row	Fetch the data for a single row of the result set.
rsql_fetch_row_v	Fetch the data for a single row of the result set and copy the values into a vector.
rsql_num_rows	Return the number of rows in a result set.
rsql_row_data$	Get the data from the currently fetched row.
rsql_seek_row	Advance the result set cursor to an exact row.
rsql_cleanup	Release all statement handles and any extra internal data.

rsql_connect

Establish a connection to a database. This will attempt to establish (or re-establish) a connection using the given host, username, and password. The returned value is a handle to be used in any further SQL operations for that connection.

Params

db$	name of the database to connect to
host$	host the database resides on
user$	username to use in connection
pwd$	plaintext password to use in connection

Returns

A non-zero handle that can be used for this connection, or 0 if a connection cannot be established.

rsql_connection_info$

Get information about an existing connection. This will return an informative string containing info about an established connection.

Params

con	connection handle

Returns

An informative string about the connection.

rsql_ping

Verify a database connection is active and working. If left open for long periods of time, a database connection can be closed down (particularly on remote database accesses). This will reopen the connection if necessary using the cached connection info.

Params

con	a connection handle

Returns

True if the connection is valid, false if not.

rsql_last_insert_id

Return the numeric id of the last record inserted if that id was auto-generated by the system. Some SQL column types allow an id to be generated automatically, when inserting new records, it is often desireable to know what id was used. This will return that value and can only be used immediately after an insert operation which caused an id to be generated, use elsewhere is undefined.

Params

con	a connection handle

Returns

The auto-generated numeric id from the last insert operation.

rsql_error$

Return the description of the last error. This is used to determine if an SQL error has occurred. Sometimes it is impossible to tell whether a query has no result set or if an error occurred. This will return “” if no error occurred.

Params

none

Returns

A descriptive error message of any error to occur on the last SQL operation, or “” if no error has occurred.

rsql_prepare

Prepare a new statement for execution. All queries must first be prepared. This will set up the query for any parameters you may use for it, as well as allocate resources for a new statement handle. The statement handle is used to iterate through the result set (if any). The SQL query provided here should be valid (parseable) or an error will be generated when attempting to execute it. It may optionally provide ‘?’ characters for any place a parameter is needed (<rsql_query_param>() is then used to set the parameters). Using parameters is highly recommended for various reasons, highest among them are safety and speed. Parameters are automatically escaped so you can safely embed strings in a query without worrying about their content.

Params

con	db connection
query$	an SQL query string

Returns

A statement handle or 0 if it cannot be prepared.

rsql_query_param

Set the parameter value for a prepared query. This will give a value to a parameter in a query. It will automatically escape any strings so you can safely use any you wish as a parameter value. This method is HIGHLY recommended, and not using it can be the source of very hard to track and highly dangerous bugs. This is a danger for any SQL execution system, and allowing arbitrary SQL queries (by simply concatenating parameter values beforehand) should be avoided. An example of query parameterization:

sth = rsql_prepare(con, "SELECT id,price FROM t WHERE id=? AND name='?'");
rsql_query_param(sth, 1, "1234");
rsql_query_param(sth, 2, "A big widget");
rsql_execute(sth);

Using query parameters can increase query performance as well. It will remember any query parameters after execution and allow you to modify only a single parameter and re-execute the query. For example, we can modify the above snippet to look for a widget with a different id.

rsql_query_param(sth, 1, "5678");
rsql_execute(sth);

Each parameter is numbered as it is encountered, starting with 1. So the first ? encountered is parameter 1, the second is 2, etc.

Params

sth	a statement handle
n	modify the value of the Nth parameter
value$	the value to set the parameter to

Returns

nothing

rsql_execute

Execute an SQL command. Make sure that any parameters in the query have been given values. This will return true if the given query has results available. To determine whether the query had an error, check the value of <rsql_error$>().

Params

sth	a statement handle

Returns

Returns true if the query has a result set, false otherwise.

rsql_release

Release all resources associated with a query. Queries, when executed will cache the results obtained, as well as any connection information they need. This can often be very large amounts of data. It is recommended that when finished with a statement, you release it. This is akin to closing a file. The routine <rsql_cleanup>() will force any open queries to be released as well as perform internal cleanups, but relying on it to free up query resources is not recommended. Once a statement handle has been released, it can no longer be executed or used in any other SQL operation.

Params

sth	a statement handle

Returns

nothing

rsql_fetch_row

Fetch the data for a single row of the result set. This will obtain the data from the server and cache it for speedy access. Use <rsql_row_data$>() to access the data for the current row. If no more rows are available, this will return false. A query must first be executed before results may be fetched.

Params

sth	a statement handle

Returns

True if the fetch succeeded (there were rows left to fetch), or false if there is no more data.

rsql_fetch_row_v

Fetch the data for a single row of the result set and copy the values into a vector. This works exactly like <rsql_fetch_row>() however it will also copy the values into a vector. The vector will be cleared prior to the copy process.

Params

sth	a statement handle
vec@	a vector to receive column values

Returns

True if the fetch succeeded (there were rows left to fetch), or false if there is no more data.

rsql_num_rows

Return the number of rows in a result set. After executing a query, this may be used to find out how many rows were in the result set.

Params

sth	a statement handle

Returns

The number of rows in the result set.

rsql_row_data$

Get the data from the currently fetched row. This can be used to obtain the column data in the current result row. The value is returned as a string even though the underlying value may represent a numeric (or other type of data). The columns are numbered starting with 1. Note that this routine implies a fetch operation has already been performed.

Currently, it is impossible to retrieve NULL values from the database. Any NULL value will be fetched as an empty string. (This should be addressed in a future version of Rapture). As a workaround, you can select the NULL status of a column into another column using the syntax “IS NULL(col), col” in your select statement. The following is an example of this.

SELECT IS NULL(col), col FROM table

For the above, the first column value will always be 1 or 0 (true or false) depending on the NULL status of the column.

Params

sth	a statement handle
col	the column number to get

Returns

The value in the row for the specified column.

rsql_seek_row

Advance the result set cursor to an exact row. The row is a numeric index into the entire result set and must be in the range of 0 to rsql_num_rows()-1.

Params

sth	a statement handle
row	row to seek to (must between 0 and num_rows-1)

Returns

True if the cursor was moved successfully.

rsql_cleanup

Release all statement handles and any extra internal data. This can be used to force a recollection of any stray statement handles that may not have been released, and should probably be called periodically when it it’s safe to do so. If SQL routines are being used properly, this should be unnecessary, it’s provided for the lazy coder or for emergencies.

Params

none

Returns

nothing