RmSQL {RmSQL}R Documentation

Accessing mSQL2.0 databases from R

Description

RmSQL implements a low-level interface between R and mSQL 2.0 by Hughes Technologies. mSQL is a relational database managment system. With RmSQL, the power of SQL is available within R. The mSQL homepage is available under http://www.Hughes.com.au.

Usage

msqlConnect(host)
msqlSelectDB(sock, dbName)
msqlQuery(sock, query)
msqlStoreResult()
msqlFetchRow()
msqlFreeResult()
msqlDataSeek(pos)
msqlNumRows()
msqlFetchField()
msqlFieldSeek(pos)
msqlNumFields()
msqlClose(sock)
msqlGetErrMsg()
msqlListDBs(sock)
msqlListTables(sock)
msqlListFields(sock, tableName)
msqlListIndex(sock, tableName, index)

Arguments

host Hostname of the database server
sock connection handle returned by msqlConnect(host)
query a SQL statement
dbName Name of the database to access
pos a position number when fetching a row or field

Details

msqlConnect(host) establishs a connection to the msql2d at host. It returns a integer, which is used as handle if no error occured, -1 otherwise.

msqlSelectDB(sock, dbName) selects the database dbName and requires the handle sock, returned by msqlConnect(host).

msqlQuery(sock, query) sends the SQL statement query to the msql2d, using connection sock, returned by msqlConnect(host). A integer containing the number of available rows is returned, -1 is returned if an error occured. NOTE: query not only means SELECT statements but also UPDATE, CREATE TABLE, DELETE etc.

msqlStoreResult() stores data returned by a SELECT query. This must be done before accessing returned data (e.g. by msqlFetchRow(). Nothing is returned.

msqlFetchRow() returns the next available row (next row after current cursor position) of data as character vector and an integer indicating an error by -1. Remember to call msqlFreeResult() when the data is no longer needed.

msqlFreeResult(): When data is no longer needed, free the memory.

msqlDataSeek(pos) set the cursor to row number pos. Returns an integer, which is -1 if an error occured.

msqlNumRows() returns the number of rows available and an integer, which is -1 if an error occured (e.g. no data available).

msqlFetchField() returns the field name, table name, type of field as integer and an integer indicating an error by the value -1.

name: attribute name
table: name of table
type:
1 int
2 char
3 double
6 text
7 date

msqlFieldSeek(pos) same as msqlDataSeek(pos) with fields.

msqlNumFields() same as msqlNumRows() with fields.

msqlClose(sock) closes the connection opened by msqlConnect(host). Nothing is returned.

msqlGetErrMsg() returns a character string with the error message, if an error occured.

The following functions are related to database and relation schemes.

msqlListDB(sock) lists all currently known databases at host. Results can be read by the msqlFetchRow() mechanism.

msqlListTables(sock) lists all tables (relations) known by the database selected by msqlSelectDB(). Results can be read by the msqlFetchRow() mechanism.

msqlListFields(sock, tableName) lists all fields (attribute) known by the database selected by msqlSelectDB() and table tableName . Results can be read by the msqlFetchField() mechanism.

msqlListIndex(sock, tableName, index) returns information about the used index in table tableName. Results can be read by the msqlFetchField() mechanism, the first row contains a symbolic name of the index mechanism, the following rows contain the names of the fields that comprise the index (well, indeed index means key attributes).

For excellent information about mSQL and how to use it from other languages, see the mSQL manual. NOTE that mSQL is commercial and NOT GPLed but open source and free of charge for universities and noncommercial organisations.

Author(s)

Torsten Hothorn, <hothorn@amadeus.statistik.uni-dortmund.de>

Examples

see ./RmSQL/Example/crash.R


[Package Contents]