exportJDBC {sjdbc}

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

data

the data.frame object to export.

table

a string containing the name of the database table.

appendToTable

a logical. If TRUE (the default), rows are appended to the existing table; if FALSE, any existing table is dropped and an empty table is created prior to exporting the data.

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 TRUE, keeps the database connection alive after executing the query. The default is FALSE.

preserveColumnCase

a logical. If TRUE, preserves case-sensitive column names, if supported by database. If FALSE (the default), column name case is converted to the database-specific default.

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 1000. A value of 0 disables batch exporting.

useTransaction

If TRUE, exports the data as a single transaction, otherwise commits throughout export.

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)