3.2 JDBC Adapter.
JDBC Adapter
JDBC Adapter is used as a connector to transfer data from Database to Salesforce and from Salesforce to Database. The agent supports five database types include Oracle, SQLServer, MySQL, MsAcess, and PostgreSQL. The following sample is about the SQLServer configuration. Run Agent UI → Integration Wizard → Choose Integration → Choose existing interface.
- Adapter Section: Choose Adapter Type (JDBC) → Name Adapter
- Adapter Properties:
- Username → sa
- Password → * (The default password to use when making the connection to the database).
- Hostname → 192.168.1.141 (Ip address)
- Port → 1433 (default)
- Database Name → Account (User choice)
- Server Instance → (Local)
- Click “Test Source Connection” to test the connection to the specified folder and file.
- Click Save
Since the user now completely understand; How to use JDBC Adapter (SQLServer) with Agent Control Board, by hitting the upper link, now we understand the Architect of the Agent Control Board. SF – Agent: JDBC Oracle Integration Users want to synchronize Account data from the Salesforce system to their DBMS system and vice versa. In this use case, we use Oracle as DBMS to/from which Account data will be synchronized from/to Salesforce respectively. Requisites
- Oracle DBMS has been installed on User-machine
- Integration Agent installed on User-machine
- Salesforce Organization set up for the User
Mappings defined for Account Synchronization (SF-Agent and Agent-SF)
- It is required that the client tool for those DBMS exists on the client machine where it will be used for doing batch integration (SF – Agent).
For example, in this case of Oracle, the SQLPLUS.exe tool is required. In the case of the SQL server, either osql.exe or sqlcmd.exe is required depending on the version of the SQL Server.
- For this version of the Integration Agent, only Oracle and SQL servers are supported.
Implementation: Step 1: DBMS configuration
- Log in to Oracle and create a table named “ACCOUNTIN” on Oracle system with the following structures:
Salesforce – Agent Integration:
- Create a New Integration (for SF-Agent integration) as follows
- Either click Integration Wizard link on the above figure
Supply your Salesforce organization info and environment and click “Test Salesforce Connection” for Testing. Remarks: There are two Integrations fields here:
- Integration (Agent – SF): this one is used to do integration from Agent to Salesforce.
- Integration (SF – Agent): this one is used to do integration from Salesforce to Agent.
We need to create a new integration for SF – Agent
- Choose Integration Direction “SF – Agent” among (SF-Agent; Agent-SF)
- Name your integration
- Choose the appropriate adapter type. Here we use JDBC adapter and name the adapter
- On Adapter Property information section
- Choose Database Type Oracle among (“Oracle” or “SQLServer”)
- Fill in all necessary information needed to make connections and data manipulation to the Oracle system.
Please “FAQs” for more details
- Click “Test Source Connection” to test the connection to the specified Oracle instance
- Click “Save & Next” to save the Integration and its Adapter and Properties and to create a New Interface and fill in all necessary info as shown in the below figure:
- Name the interface
- Status: Deployed
- Choose type “Outbound” among the list (“Inbound”; “Outbound”).
Inbound: for Agent – SF Outbound: for SF – Agent
- Target: the table name in the Oracle system where we created on Step 1
- On the Query section: input the Salesforce SOQL statement. Here select statement from Account for those created on today.
Click Next to do Mappings as shown in the below figure:
- Click button “Save” on Data Mapping grid to save the defined Mappings
- Then click “Process” to test integrate
- Click “Test Integrate” to test this interface setup and data integration from Salesforce Account data as shown in the table into the Oracle system on table ACCOUNT.
- Finish completing the wizard. You will be asked as in the figure:
- Click “Yes” to save the integration you have just created on the Integration Properties Setup page.
- As a result, you will see the figure as shown below:
[su_box title="Note" box_color="#2a8af0" title_color="#000000"] If you want to change the integration setup (eg, change the SF instance info like password or token; or change integration either SF2Agent or Agent2SF), always change thru the Agent UI. Change it directly in the file might cause unexpected errors. For example, if you change the password in the file, it will cause an encryption/ decryption problem since the password shown in the file has been encrypted.[/su_box]
MySQL Integration
Pre-Integration: Check whether MySQL service is running and the required tool for integration has been installed along with the MySQL installation. The tool required for the batch process is “mysql.exe” in the directory “C:\Program Files\MySQL\MySQL Server 5.5\bin Integration Adapter properties:
- Data Source Name: (ODBC type) data source specified in the ODBC configuration of the computer
- Username: user name to log in to MySQL
- Password: password to login to MySQL
- Hostname: host address of MySQL server.
Ex. “localhost” for the server installed in the current agent computer
- Port: port number of the MySQL server, 3306 by default.
- Database Name: Name of the database you wish to integrate.
Oracle Integration
1.Pre-Integration: Check whether Oracle service is running and the required tool for integration has been installed along with the Oracle server installation. The tool required for the batch process is “sqlplus.exe” in directory “C:\oraclexe\app\oracle\product\10.2.0\server\BIN” 2. Integration Adapter properties:
- Data Source Name: (ODBC adapter type) data source specified in the ODBC configuration on the computer
- Username: user name to log in to Oracle
- Password: password to login to Oracle
- Hostname: host address of Oracle server.
Ex. “localhost” for the server installed in the current agent computer
- Port: port number of the Oracle server, 1521 by default.
- SID/Service Name: Name of Oracle Service or SID (Service ID).
SQLserver Integration
1.Pre-Integration: Check whether Oracle service is running and the required tool for integration has been installed along with the Oracle server installation. a. For SQL 2000, the tool required for the batch process is “osql.exe” b.For SQL 2005 or higher, the tool required for the batch process is “sqlcmd.exe” 2.Integration Adapter properties:
- Data Source Name: (ODBC adapter type) data source specified in the ODBC configuration on the computer
- Username: user name to log in to SQL Server
- Password: password to login to SQL Server
- Hostname: host address of SQL Server.
Ex. “localhost” for the server installed in the current agent computer Port: port number of the SQL Server. By default, the value is 1433 for SQL Server 2000, and 2934 for SQL Server 2005 or higher
- Database Name: Name of the database you wish to integrate.
- Server Instance: Instance name of the SQL Server.
Ms Access Integration
1.Pre-Integration: Make sure Ms Access has been installed on the computer, and the database file format supported for the integration is 2003 file format. 2.Integration Adapter properties:
- Data Source Name: (ODBC adapter type) data source specified in the ODBC configuration on the computer
- Username: user name to log in to Ms Access
- Password: password to login to Ms Access
- URL: URL for access to Ms Access.
Usually, it is something like “jdbc:odbc:DRIVER={Microsoft Access Driver (*.mdb)};DBQ=path\test.mdb”