ODBC Driver for QuickBooks Online Connecting to QuickBooks Online Using Oracle Database Link and ODBC Driver |
This article explains how to configure Oracle Database Gateway for ODBC. If your data is stored in a non-Oracle database system or cloud application, and you need to access it from an Oracle Database server, you can create a database link to an Oracle Database Gateway for ODBC. The gateway works with an ODBC driver to access non-Oracle systems or other, remote Oracle servers. Any ODBC-compatible data source can be accessed using the gateway and the appropriate ODBC driver. The driver must be installed on the same machine as the gateway. The non-Oracle system can run on the same machine as the Oracle server or on a different machine. The gateway can be installed on the machine running the non-Oracle system, the machine running the Oracle database or on a third machine as a standalone.
After installing the gateway and the ODBC driver for QuickBooks Online, create an initialization file for your Oracle Database Gateway for ODBC. The sample file initdg4odbc.ora
is stored in the ORACLE_HOME\hs\admin
directory. To create an initialization file for the gateway, copy the sample initialization file and rename it. The name must be prefixed with init — for example, initQuickBooks Online.ora
. You need a separate initialization file for each ODBC data source. After creating the file, set the HS_FDS_CONNECT_INFO
parameter to the system DSN that you created earlier, for example:
HS_FDS_CONNECT_INFO=QuickBooks Online
After configuring the gateway, you need to configure Oracle Net Listener to communicate with the Oracle database. Information about the gateway must be added to the listener.ora
configuration file which is located in the ORACLE_HOME\NETWORK\ADMIN\
directory. The following example is the address on which the Oracle Net Listener listens (HOST
is the address of the machine on which the gateway is installed):
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
)
)
Add an entry to the listener.ora
file to start the gateway in response to connection requests. The SID of the gateway (SID_NAME
) must be the same in listener.ora
and tnsnames.ora
. ORACLE_HOME
is the Oracle home directory where the gateway resides. To apply the new settings, stop and restart the Oracle Net Listener service.
SID_LIST_LISTENER=
(SID_LIST=
(SID_DESC=
(SID_NAME=QuickBooks Online)
(ORACLE_HOME=D:\ORACLE_HOME)
(PROGRAM=dg4odbc)
)
)
Add a connect descriptor for the gateway to the tnsnames.ora file
, which is located in ORACLE_HOME\NETWORK\ADMIN
directory. The SID
must match the value specified in the listener.ora
file.
QuickBooks Online =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = tcp)(HOST = localhost)(PORT = 1521))
(CONNECT_DATA =
(SID = QuickBooks Online)
)
(HS = OK)
)
To access an ODBC data source, you must create a database link using a database tool like SQL Plus or dbForge Studio for Oracle: connect to your database server and execute the CREATE DATABASE LINK
statement, as follows:
CREATE DATABASE LINK dblink CONNECT TO "username" IDENTIFIED BY "password" USING 'tns_name_entry';
dblink
is the complete database link name. tns_name_entry
is the Oracle Net connect descriptor specified in the tnsnames.ora
file.
When you create the database link in dbForge Studio for Oracle, you can see your newly created link in Database Links on the left panel. After creating the database link, you can run a query against the ODBC data source using the following syntax:
SELECT * FROM table_name@"dblink_name"