DBI (version 0.1-10)

make.db.names-methods: Make R/S-Plus identifiers into legal SQL identifiers

Description

Produce legal SQL identifiers from a character vector.

Usage

make.db.names(dbObj, snames, unique=T, allow.keywords=T)
  SQLKeywords(dbObj, ...)
  isSQLKeyword(dbObj, name, keywords=.SQL92Keywords, 
                  case=c("lower", "upper", "any")[3])

Arguments

dbObj

any DBI object (e.g., DBIDriver).

snames

a character vector of R/S-Plus identifiers (symbols) from which we need to make SQL identifiers.

name

a character vector of SQL identifiers we want to check against keywords from the DBMS.

unique

logical describing whether the resulting set of SQL names should be unique. Its default is TRUE. Following the SQL 92 standard, uniqueness of SQL identifiers is determined regardless of whether letters are upper or lower case.

allow.keywords

logical describing whether SQL keywords should be allowed in the resulting set of SQL names. Its default is TRUE

keywords

a character vector with SQL keywords, by default it's .SQL92Keywords defined in the DBI package.

case

a character string specifying whether to make the comparison as lower case, upper case, or any of the two. it defaults to any.

...

any other argument are passed to the driver implementation.

Value

• make.db.names returns a character vector of legal SQL identifiers corresponding to its snames argument. SQLKeywords returns a character vector of all known keywords for the database-engine associated with dbObj.

  isSQLKeyword returns a logical vector parallel to name.

synopsis

make.db.names(dbObj, snames, ...) SQLKeywords(dbObj, ...) isSQLKeyword(dbObj, name, ...)

Bugs

The current mapping is not guaranteed to be fully reversible: some SQL identifiers that get mapped into S identifiers with make.names and then back to SQL with make.db.names will not be equal to the original SQL identifiers (e.g., compound SQL identifiers of the form username.tablename will loose the dot ``.'').

Details

The algorithm in make.db.names first invokes make.names and then replaces each occurrence of a dot ``.'' by an underscore ``_''. If allow.keywords is FALSE and identifiers collide with SQL keywords, a small integer is appended to the identifier in the form of "_n".

The set of SQL keywords is stored in the character vector .SQL92Keywords and reflects the SQL ANSI/ISO standard as documented in "X/Open SQL and RDA", 1994, ISBN 1-872630-68-8. Users can easily override or update this vector.

References

The set of SQL keywords is stored in the character vector .SQL92Keywords and reflects the SQL ANSI/ISO standard as documented in "X/Open SQL and RDA", 1994, ISBN 1-872630-68-8. Users can easily override or update this vector.

See the Database Interface definition document DBI.pdf in the base directory of this package or http://developer.r-project.org/db.

See Also

dbReadTable, dbWriteTable, dbExistsTable, dbRemoveTable, dbListTables.

Examples

# This example shows how we could export a bunch of data.frames
# into tables on a remote database.

con <- dbConnect("Oracle", user="iptraffic", pass = pwd)

export <- c("trantime.email", "trantime.print", "round.trip.time.email")
tabs <- make.db.names(export, unique = T, allow.keywords = T)

for(i in seq(along = export) )
   dbWriteTable(con, name = tabs[i],  get(export[i]))

# Oracle's extensions to SQL keywords
oracle.keywords <- c("CLUSTER", "COLUMN", "MINUS", "DBNAME")
isSQLKeyword(nam, c(.SQL92Keywords, oracle.keywords))
[1]  T  T  T  F