RmSQL {RmSQL} | R Documentation |
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.
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)
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 |
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.
Torsten Hothorn, <hothorn@amadeus.statistik.uni-dortmund.de>
see ./RmSQL/Example/crash.R