| Title: | JDBC Driver Interface |
|---|---|
| Description: | Provides a database-independent JDBC interface. |
| Authors: | TIBCO Software Inc. |
| Maintainer: | Joe Roberts <[email protected]> |
| License: | BSD_3_clause + file LICENSE |
| Version: | 1.6.1 |
| Built: | 2026-06-06 07:41:44 UTC |
| Source: | https://github.com/cran/sjdbc |
Executes a SQL command on a JDBC-Compatible database.
executeJDBC(sqlQuery, driverClass, con, user, password, keepAlive)executeJDBC(sqlQuery, driverClass, con, user, password, keepAlive)
sqlQuery |
a string containing the SQL query to execute. |
driverClass |
a string containing the name of the Java class for required JDBC driver. |
con |
a string containing the JDBC connection string. |
user |
a string containing the user name with access to database. |
password |
a string containing the password for the given user name on the database. |
keepAlive |
a logical. If |
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.
returns the number of rows affected, if applicable.
Some arguments can also be set using sjdbcOptions.
## 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)## 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)
Exports data to a database using JDBC drivers.
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)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)
data |
the |
table |
a string containing the name of the database table. |
appendToTable |
a logical. If |
driverClass |
a string containing the name of the Java class for the required JDBC driver. |
con |
a string specifying the JDBC connection string. |
user |
a string containing the user name with access to database. |
password |
a string containing the password for the given user name on the database. |
keepAlive |
a logical. If |
preserveColumnCase |
a logical. If |
batchSize |
an integer specifying the number of rows sent to the database in each batch, if batch updates are supported by the JDBC driver.
Default value is |
useTransaction |
If |
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.
returns the number of rows exported.
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.
loadJDBCDriver, sjdbcOptions, executeJDBC
## 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)## 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)
Imports data from a database using JDBC drivers.
importJDBC(sqlQuery, table, driverClass = sjdbcOptions()$driverClass, con = sjdbcOptions()$con, user = sjdbcOptions()$user, password = sjdbcOptions()$password, keepAlive = sjdbcOptions()$keepAlive, bigdata = FALSE)importJDBC(sqlQuery, table, driverClass = sjdbcOptions()$driverClass, con = sjdbcOptions()$con, user = sjdbcOptions()$user, password = sjdbcOptions()$password, keepAlive = sjdbcOptions()$keepAlive, bigdata = FALSE)
sqlQuery |
the SQL query string describing the data to be retreived from the database. Required if |
table |
a string specifying the name of the table to import. Required if |
driverClass |
a string containing the name of the Java class for the required JDBC driver. |
con |
the JDBC connection string. |
user |
a string specifying the user name with access to the database. |
password |
a string containing the password for the given user name on the database. |
keepAlive |
a logical. If |
bigdata |
unsupported in this version. Exists for compatibility with Spotfire S+. |
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.
returns a data.frame containing the requested data.
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.
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.
## 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)## 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)
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)
jdbcTimeDate(data)jdbcTimeDate(data)
data |
a |
returns a character vector in the specified format.
my.td <- as.POSIXct("2011/1/1") jdbcTimeDate(my.td)my.td <- as.POSIXct("2011/1/1") jdbcTimeDate(my.td)
Retrieves a table containing the data types supported by the connected database.
jdbcTypeInfo(driverClass, con, user, password, keepAlive)jdbcTypeInfo(driverClass, con, user, password, keepAlive)
driverClass |
a string specifying the name of the Java class for the required JDBC driver. |
con |
the JDBC connection string. |
user |
a string specifying the user name with access to the database. |
password |
a string containing the password for given the user name on the database. |
keepAlive |
a logical. If |
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.
returns a data.frame containing the entire table.
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.
## 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)## 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)
Makes a JDBC Driver available to the sjdbc package.
loadJDBCDriver(driverJar)loadJDBCDriver(driverJar)
driverJar |
a vector of one or more strings containing the full paths to JDBC driver jars. |
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.
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.
## Not run: loadJDBCDriver(file.path("C:", "sqljdbc.jar")) ## End(Not run)## Not run: loadJDBCDriver(file.path("C:", "sqljdbc.jar")) ## End(Not run)
The SJDBC Package provides an interface to databases using Java's JDBC connectivity.
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
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.
Closes any open persistent database connection.
sjdbcCloseConnection()sjdbcCloseConnection()
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.
returns no value.
importJDBC, exportJDBC, executeJDBC
## Not run: # close an open connection sjdbcCloseConnection() ## End(Not run)## Not run: # close an open connection sjdbcCloseConnection() ## End(Not run)
Retreives a ResultSet previously stored in a static instance of SJDBCResultSetUtilities class as a data.frame.
sjdbcGetResultSet(key, unregister = TRUE, default.num.rows = NULL, start.at.first=TRUE, rows.to.read=-1)sjdbcGetResultSet(key, unregister = TRUE, default.num.rows = NULL, start.at.first=TRUE, rows.to.read=-1)
key |
a string containing the key into the hash table in |
unregister |
a logical value. If |
default.num.rows |
an integer containing the number of rows. When the ResultSet is of type |
start.at.first |
a logical. If |
rows.to.read |
an integer specifying the maximum number of rows to read. If less than zero, read all rows in the result set. |
This function is called by importJDBC and usually is not called directly.
returns a data.frame containing the ResultSet.
## Not run: sjdbcGetResultSet("resultid") ## End(Not run)## Not run: sjdbcGetResultSet("resultid") ## End(Not run)
Stores presistent options and defaults for sjdbc package functions.
sjdbcOptions(...)sjdbcOptions(...)
... |
you can provide no arguments. You can provide a list or vector of character strings
as the only argument, or you can provide arguments in |
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.
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.
driverClass |
a string containing the name of the Java class for the required JDBC driver. | |
con |
the JDBC connection string. | |
user |
a string specifying the user name with access to database. Note: Some drivers do not require this option. | |
password |
a string containing the password for the given user name on the database. Note: Some drivers do not require this option. | |
keepAlive |
a logical. if TRUE, keeps the database connection alive after executing the query. Defaults to FALSE. |
|
batchSize |
an integer containing the number of rows exported per batch in exportJDBC. Defaults to 1000. |
|
useTransaction |
export data as a single transaction. Defaults to TRUE.
|
This function closely mimics the behavior of the options function in base TIBCO Enterprise Runtime for R.
# 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")# 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")