Introduction | Obtaining drivers | The JAR folder | Obtaining drivers for SQL Server, Oracle, PostgreSQL, PDW, Spark, RedShift, Snowflake, BigQuery, and Synapse | Obtaining drivers for Netezza, Impala and InterSystems IRIS | Obtaining drivers for SQLite or DuckDb | Creating a connection | Using Windows Authentication for SQL Server | Connecting to a SQLite database | Connecting with Windows authentication from a non-windows machine
Package: DatabaseConnector 7.2.0
Martijn Schuemie
DatabaseConnector: Connecting to Various Database Platforms
An R 'DataBase Interface' ('DBI') compatible interface to various database platforms ('PostgreSQL', 'Oracle', 'Microsoft SQL Server', 'Amazon Redshift', 'Microsoft Parallel Database Warehouse', 'IBM Netezza', 'Apache Impala', 'Google BigQuery', 'Snowflake', 'Spark', 'SQLite', and 'InterSystems IRIS'). Also includes support for fetching data as 'Andromeda' objects. Uses either 'Java Database Connectivity' ('JDBC') or other 'DBI' drivers to connect to databases.
Authors:
DatabaseConnector_7.2.0.tar.gz
DatabaseConnector_7.2.0.zip(r-4.7)DatabaseConnector_7.2.0.zip(r-4.6)DatabaseConnector_7.2.0.zip(r-4.5)
DatabaseConnector_7.2.0.tgz(r-4.6-any)DatabaseConnector_7.2.0.tgz(r-4.5-any)
DatabaseConnector_7.2.0.tar.gz(r-4.7-any)DatabaseConnector_7.2.0.tar.gz(r-4.6-any)
DatabaseConnector_7.2.0.tgz(r-4.6-emscripten)
manual.pdf |manual.html✨
DESCRIPTION |NEWS
card.svg |card.png
DatabaseConnector/json (API)
| # Install 'DatabaseConnector' in R: |
| install.packages('DatabaseConnector', repos = c('https://ohdsi.r-universe.dev', 'https://cloud.r-project.org')) |
Bug tracker:https://github.com/ohdsi/databaseconnector/issues
Pkgdown/docs site:https://ohdsi.github.io
Last updated from:128c84fa4f. Checks:7 WARNING, 2 OK. Indexed: yes.
| Target | Result | Time | Files | Syslog |
|---|---|---|---|---|
| linux-devel-x86_64 | WARNING | 186 | ||
| source / vignettes | OK | 242 | ||
| linux-release-x86_64 | WARNING | 180 | ||
| macos-release-arm64 | WARNING | 123 | ||
| macos-oldrel-arm64 | WARNING | 102 | ||
| windows-devel | WARNING | 108 | ||
| windows-release | WARNING | 2717 | ||
| windows-oldrel | WARNING | 125 | ||
| wasm-release | OK | 172 |
Exports:assertTempEmulationSchemaSetcomputeDataHashconnectcreateConnectionDetailscreateDbiConnectionDetailscreateZipFileDatabaseConnectorDriverdbAppendTabledbCanConnectdbClearResultdbColumnInfodbConnectdbCreateTabledbDisconnectdbExecutedbExistsTabledbFetchdbGetInfodbGetQuerydbGetRowCountdbGetRowsAffecteddbGetStatementdbHasCompleteddbIsReadOnlydbIsValiddbListFieldsdbListTablesdbmsdbReadTabledbRemoveTabledbSendQuerydbSendStatementdbUnloadDriverdbWriteTabledisconnectdownloadJdbcDriversdropEmulatedTempTablesexecuteSqlexistsTableextractQueryTimesgetAvailableJavaHeapSpacegetTableNamesinDatabaseSchemainsertTableisSqlReservedWordquerySqlquerySqlToAndromedarenderTranslateExecuteSqlrenderTranslateQueryApplyBatchedrenderTranslateQuerySqlrenderTranslateQuerySqlToAndromedarequiresTempEmulationsql_escape_logical
Dependencies:backportsbitbit64blobcheckmateclicliprcpp11crayonDBIdbplyrdigestdplyrgenericsgluehmslifecyclemagrittrpillarpkgconfigprettyunitsprogresspurrrR6RcppreadrrJavarlangSqlRenderstringistringrtibbletidyrtidyselecttriebeardtzdburltoolsutf8vctrsvroomwithr
Connecting to a database
Last update: 2026-01-08
Started: 2022-02-09
Querying a database
Introduction | Querying | Querying using Andromeda objects | Querying different platforms using the same SQL | Inserting tables | Logging all queries
Last update: 2023-01-30
Started: 2022-02-09
Using DatabaseConnector through DBI and dbplyr
Introduction | Connecting | Querying | Using dbplyr | Date functions | Allowed table and field names in dbplyr | Temp tables | Temp tables in dbplyr | Cleaning up emulated temp tables | Closing the connection
Last update: 2023-01-30
Started: 2023-01-30
Readme and manuals
Help Manual
| Help page | Topics |
|---|---|
| Assert the temp emulation schema is set | assertTempEmulationSchemaSet |
| Compute hash of data | computeDataHash |
| connect | connect |
| createConnectionDetails | createConnectionDetails |
| Create DBI connection details | createDbiConnectionDetails |
| Compress files and/or folders into a single zip file | createZipFile |
| Create a DatabaseConnectorDriver object | DatabaseConnectorDriver |
| Insert rows into a table | dbAppendTable,DatabaseConnectorConnection,character-method |
| Clear a result set | dbClearResult,DatabaseConnectorDbiResult-method |
| Clear a result set | dbClearResult,DatabaseConnectorJdbcResult-method |
| Information about result types | dbColumnInfo,DatabaseConnectorDbiResult-method |
| Information about result types | dbColumnInfo,DatabaseConnectorJdbcResult-method |
| Create a connection to a DBMS | dbConnect,DatabaseConnectorDriver-method |
| Create a table in the database | dbCreateTable,DatabaseConnectorConnection-method |
| Disconnect (close) a connection | dbDisconnect,DatabaseConnectorConnection-method |
| Change database state | dbExecute,DatabaseConnectorDbiConnection,character-method |
| Change database state | dbExecute,DatabaseConnectorJdbcConnection,character-method |
| Does a table exist? | dbExistsTable,DatabaseConnectorConnection,character-method |
| Fetch records from a previously executed query | dbFetch,DatabaseConnectorDbiResult-method |
| Fetch records from a previously executed query | dbFetch,DatabaseConnectorJdbcResult-method |
| Get DBMS metadata | dbGetInfo,DatabaseConnectorConnection-method |
| Get DBMS metadata | dbGetInfo,DatabaseConnectorDriver-method |
| Retrieve results from a query | dbGetQuery,DatabaseConnectorDbiConnection,character-method |
| Retrieve results from a query | dbGetQuery,DatabaseConnectorJdbcConnection,character-method |
| The number of rows fetched so far | dbGetRowCount,DatabaseConnectorDbiResult-method |
| The number of rows fetched so far | dbGetRowCount,DatabaseConnectorJdbcResult-method |
| The number of rows affected | dbGetRowsAffected,DatabaseConnectorDbiResult-method |
| The number of rows affected | dbGetRowsAffected,DatabaseConnectorJdbcResult-method |
| Get the statement associated with a result set | dbGetStatement,DatabaseConnectorDbiResult-method |
| Get the statement associated with a result set | dbGetStatement,DatabaseConnectorJdbcResult-method |
| Completion status | dbHasCompleted,DatabaseConnectorDbiResult-method |
| Completion status | dbHasCompleted,DatabaseConnectorJdbcResult-method |
| Is this DBMS object still valid? | dbIsValid,DatabaseConnectorDbiConnection-method |
| Is this DBMS object still valid? | dbIsValid,DatabaseConnectorJdbcConnection-method |
| List field names of a remote table | dbListFields,DatabaseConnectorConnection,character-method |
| List remote tables | dbListTables,DatabaseConnectorConnection-method |
| Get the database platform from a connection | dbms |
| Declare dbplyr backend edition | dbplyr_edition.DatabaseConnectorConnection dbplyr_edition.DatabaseConnectorDbiConnection dbplyr_edition.DatabaseConnectorJdbcConnection |
| Read database tables as data frames | dbReadTable,DatabaseConnectorConnection,character-method |
| Remove a table from the database | dbRemoveTable,DatabaseConnectorConnection,ANY-method |
| Execute a query on a given database connection | dbSendQuery,DatabaseConnectorDbiConnection,character-method |
| Execute a query on a given database connection | dbSendQuery,DatabaseConnectorJdbcConnection,character-method |
| Execute a data manipulation statement on a given database connection | dbSendStatement,DatabaseConnectorConnection,character-method |
| Load and unload database drivers | dbUnloadDriver,DatabaseConnectorDriver-method |
| Copy data frames to database tables | dbWriteTable,DatabaseConnectorConnection,ANY-method |
| Disconnect from the server | disconnect |
| Download DatabaseConnector JDBC Jar files | downloadJdbcDrivers |
| Drop all emulated temp tables. | dropEmulatedTempTables |
| Execute SQL code | executeSql |
| Does the table exist? | existsTable |
| Extract query times from a 'ParallelLogger' log file | extractQueryTimes |
| Get available Java heap space | getAvailableJavaHeapSpace |
| List all tables in a database schema. | getTableNames |
| Refer to a table in a database schema | inDatabaseSchema |
| Insert a table on the server | insertTable |
| Test a character vector of SQL names for SQL reserved words | isSqlReservedWord |
| How to download and use JDBC drivers for the various data platforms. | jdbcDrivers |
| Retrieve data to a data.frame | querySql |
| Retrieves data to a local Andromeda object | querySqlToAndromeda |
| Render, translate, execute SQL code | renderTranslateExecuteSql |
| Render, translate, and perform process to batches of data. | renderTranslateQueryApplyBatched |
| Render, translate, and query to data.frame | renderTranslateQuerySql |
| Render, translate, and query to local Andromeda | renderTranslateQuerySqlToAndromeda |
| Does the DBMS require temp table emulation? | requiresTempEmulation |
