Technical Articles
Configuration of JDBC adapter to poll data from Database in Cloud Integration(CPI)
Dear Reader,
In this blog I will explain each step required to poll data from any database system using JDBC adapter in Cloud Integration and post that data in xml file format to FTP server location.
I will only focus on configurations required for JDBC adapter in CPI, I already covered FTP adapter configuration in my previous blog:
JDBC adapter enable us to connect cloud integration to cloud or on-premise databases.
Prerequisites to connect to database using JDBC adapter to cloud integration:
- Driver deployment: Tenant admin has to upload JDBC driver (SQL/Oracle) to the tenant under Manage JDBC Material -> JDBC Driver
- TCP connection: If we are connecting to on-premise database then we need to create a TCP connection in cloud connector. Login to cloud connector and go to Cloud to On-Premise -> add mapping virtual to internal systems.
- Data Source creation: To connect to database we need to create a data source under Manage JDBC material->JDBC Data Source
We need to provide following information for Data Source:Name: Name of Data SourceDescription: provide relevant description for data source
Database type: select your database type
User: user id for database
Password: Password for database
JDBC URL: this we need to form
SQL: jdbc:sqlserver://virtualHost:virtualPort;DatabaseName=databaseName;
Oracle: jdbc:Oracle:thin:@virtualHost:virtualPort:databaseNameLocation ID: CPI tenant location ID
Cloud Connector: check this check box
Click on deploy after providing all above information.
- JDBC adapter configuration: Configure JDBC adapter in integration flow to communicate with database.Once these prerequisites are done then we can create an iflow which will pull data from SQL database and generate a file at target FTP Server.
Integration Flow:
In content modifier we will write query to call stored procedure.
In table we need to provide stored procedure name which we want to call in database.
Then we will use Receiver JDBC adapter to call database and use request reply to get response from database.
In JDBC Data Store Alias provide the datastore name which we have created in previous section.
Once we receive data from database, we are sending it to the FTP server location in xml file format.
Now we will deploy the Integration flow.
XML files have been created at FTP server location.
So, we learnt how to poll data from database stored procedure using JDBC adapter in Cloud Integration aka Cloud Platform Integration.
Reference:
Happy reading 😊
Thanks & Regards,
Nisha Shrivansh
Thanks a lot for sharing.
Thanks for sharing such great information, the post you published have some great information which is quite beneficial for me.
mymilestonecard
Gbwhatsapp is one of the best whatsapp mod ever. You can Download Apk from GBWhatsapp official website
Very helpful. I was wondering how to connect to database without sender JDBC adaptor.
Thanks a lot!!
Hi,
We have problems to connect to a SQL server named instance : we tried to reach another server without name and it works. Can you confirm the url to use in jdbc ?
internally to connect with SQL Studio manager, me must use "myserver/INSTANCE1".
We tried the following url (through Cloud Connector were virtual-server is map to myserver)
jdbc:sqlserver://virtual-server:1433;DatabaseName=VtNextDW;instanceName=INSTANCE1
but still having authentification error.
Connectivity works for other sql servers without named instance.
Any hint ?
Pierre