Execute SQL Query on a JDBC-Compatible Database

Description

Executes a SQL command on a JDBC-Compatible database.

Usage

executeJDBC(sqlQuery, driverClass, con, user, password, keepAlive)

Arguments

Details

Executes the command on the database using the JDBC driver specified in driverClass. The required JDBC driver must be loaded in sjdbc before it is used. See loadJDBCDriver for details.

Database connections are closed by default after executing the query, unless keepAlive is set to TRUE. If keepAlive = TRUE, the connection remains open, and successive database commands can reuse the open connection if and only if the same values for driverClass, con, user, and password are supplied.

Value

returns the number of rows affected, if applicable.

Note

Some arguments can also be set using sjdbcOptions.

See Also

loadJDBCDriver, sjdbcOptions

Examples

## Not run: 
executeJDBC(driverClass="com.microsoft.sqlserver.jdbc.SQLServerDriver", 
            con="jdbc:sqlserver://qadb-s2k:1433;databaseName=testdb;user=testqa;password=testqa;", 
            user="testqa", password="testqa",
            sqlQuery="UPDATE TEST1 SET Weight = NULL WHERE Weight < 2500")

executeJDBC(driverClass="com.microsoft.sqlserver.jdbc.SQLServerDriver", 
            con="jdbc:sqlserver://qadb-s2k:1433;databaseName=testdb;user=testqa;password=testqa;", 
            user="testqa", password="testqa",
            sqlQuery="DROP TABLE TEST1")

## End(Not run)

Export To a JDBC-Compatible Database

Description

Exports data to a database using JDBC drivers.

Usage

exportJDBC(data, table, appendToTable = TRUE,
    driverClass = sjdbcOptions()$driverClass, con = sjdbcOptions()$con,
    user = sjdbcOptions()$user, password = sjdbcOptions()$password,
    keepAlive = sjdbcOptions()$keepAlive, preserveColumnCase = FALSE,
    batchSize = sjdbcOptions()$batchSize, 
    useTransaction = sjdbcOptions()$useTransaction)

Arguments

Details

Exports data to the database using the JDBC driver specified in driverClass. The required JDBC driver must be loaded in sjdbc before use. See loadJDBCDriver for details.

Database connections are closed by default after the query executes, unless keepAlive is set to TRUE. If keepAlive = TRUE, the connection remains open, and successive database commands can reuse the open connection if and only if the same values for driverClass, con, user, and password are supplied.

Setting a larger value for the batchSize argument can improve efficiency when you need to export large data tables, if batch updates are supported by the JDBC driver.

Value

returns the number of rows exported.

Note

Some arguments can also be set using sjdbcOptions.

When you export to a new table (appendToTable=FALSE), you might find that the column types of the resulting table are not as desired. Columns containing text data are of type VARCHAR(255) (or database equivalent), and numeric and timeDate columns attempt to use appropriate database-specific column types. If you want a specific column type or precision in your tables, you should create the table manually using executeJDBC, and then append your data to the existing table.

See Also

loadJDBCDriver, sjdbcOptions, executeJDBC

Examples

## Not run: 
exportJDBC(data=fuel.frame, driverClass="com.microsoft.sqlserver.jdbc.SQLServerDriver", 
            con="jdbc:sqlserver://qadb-s2k:1433;databaseName=testdb;user=testqa;password=testqa;", 
            user="testqa", password="testqa",
            table="TEST1", append=F)

## End(Not run)

Import From a JDBC-Compatible Database

Description

Imports data from a database using JDBC drivers.

Usage

importJDBC(sqlQuery, table, driverClass = sjdbcOptions()$driverClass,
    con = sjdbcOptions()$con, user = sjdbcOptions()$user,
    password = sjdbcOptions()$password,
    keepAlive = sjdbcOptions()$keepAlive, bigdata = FALSE)

Arguments

Details

Imports data from the database using the JDBC driver specified in driverClass. The required JDBC driver must be loaded in sjdbc before use. See loadJDBCDriver for details.

Database connections are closed by default after the query executes, unless keepAlive is set to TRUE. If keepAlive = TRUE, the connection remains open, and successive database commands can reuse the open connection if and only if the same values for driverClass, con, user, and password are supplied.

Value

returns a data.frame containing the requested data.

Time Zone Handling

Times, Dates, and Timestamps that the datebase returns are assumed to be GMT. The resulting timeDate objects are created in GMT, without conversion. If you know the time zone of the incoming data, you can specify an alternative time zone for the timeDate objects by setting options("time.zone") prior to import. For further details, see the class.timeDate help file.

Note

Character data can be imported either as character or as factor. importJDBC uses the value of options(stringsAsFactors) to determine how to import the data.

Some arguments can also be set using sjdbcOptions.

See Also

loadJDBCDriver, sjdbcOptions

Examples

## Not run: 
importJDBC(driverClass="com.microsoft.sqlserver.jdbc.SQLServerDriver", 
	    con="jdbc:sqlserver://qadb-s2k:1433;databaseName=testdb;user=testqa;password=testqa;", 
	    sqlQuery="SELECT * FROM FUEL_FRAME")
	    
importJDBC(driverClass="COM.ibm.db2.jdbc.net.DB2Driver", 
	    con="jdbc:db2://qadb1:6789/QATESTDB", 
	    user="testqa", 
	    password="testqa", 
	    sqlQuery="SELECT * FROM FUEL_FRAME")

## End(Not run)

Convert an splusTimeDate::timeDate object to standard JDBC Timestamp string

Description

Converts a timeDate vector to a character vector in the standard format expected by java.sql.Timestamp: yyyy-mm-dd hh:mm:ss.fffffffff (in GMT)

Usage

jdbcTimeDate(data)

Arguments

Value

returns a character vector in the specified format.

See Also

exportJDBC

Examples

my.td <- as.POSIXct("2011/1/1")
jdbcTimeDate(my.td)

Retrieve Supported Column Type Info from a Database

Description

Retrieves a table containing the data types supported by the connected database.

Usage

jdbcTypeInfo(driverClass, con, user, password, keepAlive)

Arguments

Details

A direct interface to the java.sql.DatabaseMetaData.getTypeInfo() method. See the Java documentation for description of the fields in the table. Useful for debugging.

Value

returns a data.frame containing the entire table.

References

2004. https://docs.oracle.com/javase/1.5.0/docs/api/java/sql/DatabaseMetaData.html#getTypeInfo(). Java SE Developer Documentation. Redwood Shores, CA: Oracle Corporation.

Examples

## Not run: 
jdbcTypeInfo(driverClass="com.microsoft.sqlserver.jdbc.SQLServerDriver", 
            con="jdbc:sqlserver://qadb-s2k:1433;databaseName=testdb;user=testqa;password=testqa;", 
            user="testqa", password="testqa")

## End(Not run)

Load a JDBC Driver

Description

Makes a JDBC Driver available to the sjdbc package.

Usage

loadJDBCDriver(driverJar)

Arguments

Details

Makes the specified driver jars available to the sjdbc package. The driver must be loaded prior to its first use in the TIBCO Enterprise Runtime for R session.

Note

The JDBC drivers need to be loaded each time you use the sjdbc package. To load a driver automatically when loading the sjdbc package, place it in the in the drivers folder where the sjdbc package is installed.

Examples

## Not run: 
loadJDBCDriver(file.path("C:", "sqljdbc.jar"))

## End(Not run)

SJDBC Package Documentation

Description

The SJDBC Package provides an interface to databases using Java's JDBC connectivity.

Details

Provides an interface to a databases using JDBC drivers. You can get JDBC drivers from the software providers. Place the JAR or ZIP file containing the JDBC drivers in the drivers folder under the package installation directory. All files placed in this directory are added automatically to the Java CLASSPATH when the package is loaded. Alternatively, drivers can be loaded explicitly at runtime using loadJDBCDriver.

The interface has been tested with the following drivers:

• Microsoft SQL Server 2005

  • Connection String: jdbc:sqlserver://<host>:1433;databaseName=<database>;user=<username>;password=<password>;

  • Driver Class: com.microsoft.sqlserver.jdbc.SQLServerDriver

• IBM DB2 Universal Database 7.2

  • Connection String: jdbc:db2://<host>:6789/<database>

  • Driver Class: COM.ibm.db2.jdbc.net.DB2Driver

• MySQL Connector/J 3.1.14

  • Connection String: jdbc:mysql://<host>:3306/<database>

  • Driver Class: com.mysql.jdbc.Driver

• Oracle 10g Release 2 10.2.0.4 (ojdbc14.jar)

  • Connection String: jdbc:oracle:thin:@<host>:1521:<databaseSID>

  • Driver Class: oracle.jdbc.OracleDriver

• PostgreSQL 8.3 (JDBC3 driver 8.3-603)

  • Connection String: jdbc:postgresql://<host>:5432/<database>

  • Driver Class: org.postgresql.Driver

Known Issues

• Missing values might not be handled correctly in all cases. exportJDBC handles missing (NA) values for integer and numeric class columns by creating NULL values in the database table. Currently, this does not work for character or factor columns. NA values are stored as “NA” in the table, but empty strings (“”) are stored as empty strings.

Close a Persistent Database Connection

Description

Closes any open persistent database connection.

Usage

  sjdbcCloseConnection()

Details

Closes a connection that was made persistent previously using the keepAlive argument to one of the database functions.

This function is used primarily by importJDBC, exportJDBC, and executeJDBC to close connections after execution. It rarely needs to be called directly.

Value

returns no value.

See Also

importJDBC, exportJDBC, executeJDBC

Examples

## Not run: 
  # close an open connection
  sjdbcCloseConnection()

## End(Not run)

Get a ResultSet From Static Java Class

Description

Retreives a ResultSet previously stored in a static instance of SJDBCResultSetUtilities class as a data.frame.

Usage

sjdbcGetResultSet(key, unregister = TRUE, default.num.rows = NULL,
                  start.at.first=TRUE, rows.to.read=-1)

Arguments

Details

This function is called by importJDBC and usually is not called directly.

Value

returns a data.frame containing the ResultSet.

See Also

importJDBC

Examples

## Not run: 
sjdbcGetResultSet("resultid")

## End(Not run)

Package Options and Defaults

Description

Stores presistent options and defaults for sjdbc package functions.

Usage

sjdbcOptions(...)

Arguments

Value

The sjdbcOptions function always returns a list, even if the list is of length 1.

• if no arguments are given, returns a list of current values for all options.

• if a character vector is given as the only argument, returns a list of current values for the options named in the character vector.

• if an object of mode "list" is given as the only argument, its components become the values for options with the corresponding names. The function returns a list of the option values before they were modified. Usually, the list given as an argument is the return value of a previous call to sjdbcOptions.

• if arguments are given in name=value form, sjdbcOptions changes the values of the specified options and returns a list of the option values before they were modified.

Side Effects

When options are set, the sjdbcOptions function changes a list named .sjdbcOptions in the session frame (frame 0). The components of .sjdbcOptions are all of the currently defined options. If sjdbcOptions is called with either a list as the single argument or with one or more arguments in name=value form, the options specified are changed or created.

Supported Options

See Also

This function closely mimics the behavior of the options function in base TIBCO Enterprise Runtime for R.

Examples

# set a single option
sjdbcOptions(driverClass="COM.ibm.db2.jdbc.net.DB2Driver")

# set multiple options
sjdbcOptions(driverClass="COM.ibm.db2.jdbc.net.DB2Driver", 
	    con="jdbc:db2://qadb1:6789/QATESTDB", 
	    user="testqa", 
	    password="testqa")