Skip to Content
Author's profile photo Former Member

Configuring JDBC Connector Service To Perform Database Lookups

This Web log describes how to Activate JDBC Connector service to perform database lookups (without JDBC adapter) in XI.

The standard How To guide covers almost all steps but falls short in describing some configuration steps.

This web log outlines the steps that are required to configure the JDBC Connector service for Oracle database

The following reference documents were used, this web log explains the steps that were missed in those documents

http://www.sdn.sap.com/irj/sdn/go/portal/prtroot/docs/library/uuid/9aedca11-0d01-0010-3b90-9ea04182875f

http://help.sap.com/saphelp_nw2004s/helpdata/en/b0/6e62f30cbe9e44977c78dbdc7a6b27/frameset.htm

Steps to configure JDBC connector service

1.Download the oracle JDBC driver ojdbc14.jar from the website

http://www.oracle.com/technology/software/tech/java/sqlj_jdbc/index.html

2.Use Visual administrator to upload the ojdbc14.jar
against JDBC connector service.

3.Select the Service node from the list of nodes shown below

image

4.Select the JDBC Connector from the list of services

image

5.Place the cursor in the Drivers node in the Runtime tab, Click the create button to add the drivers.

image

6.Place the cursor in the DataSources to create a new data source.

Enter the following parameters

Application Name

Data source Name

Add alias name ( Do not add jdbc/Name)

since what ever the name you add will be registered in the JNDI Registry under jdbc context, while writing the java code access the alias by (jdbc/aliasname)

Subsequently, to check if the alias is registered with JNDI, you can browse to the JNDI Registry node in Visual administrator and locate the alias

image

Select the appropriate driver from the list of drivers (from step 5)

Select JDBC Version

If the version is JDBC 2.0 compliant (Oracle 10.x drivers are compliant) then enter

Object factory – oracle.jdbc.pool.OracleDataSourceFactory

DataSource type – XADataSource

XADS Class name – oracle.jdbc.xa.client.OracleXADataSource

image

image

8.How to test the data source to working fine ?

In the DB initialization tab, enter a SQL statement to query the table, & press execute button, it should return the results.

Do not forget to remove the statement after the test.

image

9.Develop java code that performs DBLookup, the sample code for the performing the DBLook up is given in the How to Guide Appendix.

10.Follow the procedure as given in the how to guide to Create UDF’s

Assigned Tags

      9 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Daniel Graversen
      Daniel Graversen
      Hi,

      What is the point in using this direct approach. Is it because language used in the adapter is limited to select.

      /daniel 

      Author's profile photo Bhavesh Kantilal
      Bhavesh Kantilal
      Adding to Daniel, what if I need to make changes in the user id and password?I go to the Visual Admin and make the changes?

      I would not like doing this. I would rather have the control in my Receiver JDBC adapter and make the changes there.

      Nice to know that there are multiple options available though.

      Regards,
      Bhavesh

      Author's profile photo Former Member
      Former Member
      Blog Post Author
      Hi Daniel,
                I didn't understand your question. Can u elobrate.

      With regards
      K.vardharajan

      Author's profile photo Former Member
      Former Member
      I get a NullPointerException after a long idle time (typically overnight). After a few hits to the database the connection is established and stays good for the day.

      I made the following changes to the configuration yesterday and hoping that it will work out:

      Expiration - checked.
      Connection Lifetime = 600
      Cleanup Thread = 300

      Please advice.

      Author's profile photo Former Member
      Former Member
      Hi,
      I am getting the following error message:
      "java.rmi.RemoteException: com.sap.engine.services.dbpool.exceptions.BaseRemoteException:
      QL statement(s) cannot be executed over DataSource "OracleJDBC". If you are using an Open SQL DataSource, you must register your tables using JDDI before you request them. Reason: com.sap.engine.services.jndi.persistent.exceptions.NameNotFoundException: Object not found in lookup of OracleJDBC. [id = Unknown]"

      Any idea what could be wrong on my side?
      Kevin

      Author's profile photo Former Member
      Former Member
      Kevin,

      How did you resolve this issue? I would appreciate your response.

      Thanks
      Satheesh

      Author's profile photo senthilprakash selvaraj
      senthilprakash selvaraj
      Dear Sir,

      I am also getting the same error which you have mentioned above!

      Could you please tell me the solution if you have found out? i am not able to rectify it ! for past two days i am struggling.

      Please Advice,
      prakash

      Author's profile photo Former Member
      Former Member
      Hi,

      I am getting this same error which you were facing, did find a solution for this one, if yes
      please guide me what did you do.

      Thanks.

      Author's profile photo Arpil Gupta
      Arpil Gupta
      For lookup we use DataBaseAccessor class which can only perform select query. However if i use this weblog can i perform Insert/Update/Stored Proc using udf in Mapping.