Android Question Connecting to a 'live' Oracle 11G database with JRDC2

B

beelze69

Active Member
Licensed User
Longtime User
Hi !

I want to connect to a live Oracle 11G database using JRDC2.... and fire queries from a b4A Application... .. I went through the various available content for accomplishing the same through MYSQL..


Here, I have the following doubts:

1) In B4J JRDC2 program , for connecting to MYSQL , the driver to be taken is mysql-connector-java-5.1.27.jar file; relevant entry in the code is #AdditionalJar: mysql-connector-java-5.1.27

(which is to be kept in C:\Program Files (x86)\Anywhere Software\B4J\Libraries)

My doubt: 1) For Oracle, what is the relevant .jar file ? 2) What is the #AdditionalJar: entry to be made ?

2) In the Config file in the JRDC2 folder .. for MYSQL Database, a typical entry would be


JdbcUrl=jdbc:mysql://localhost/mydb_store?characterEncoding=utf8

User=root
Password=
#Java server port
ServerPort=17178

where : mydb_store is the name of the database
root is the user with NULL as password.

My doubt: Now, for an Oracle database with:

TNSNAMES="MYTNS"
localhost=127.0.0.1
port: 1521 (listener runs on default port 1521)
SCHEMA NAME="MYSCHEMA"
PASSWORD="mypassword"

How will the entries appear in the 'config' file under the JRDC2 folder ?

(NOTE: The ServerPort 17178 will not change since it is where the JRDC2 server will run on ) ...


Please help.

Thanks in advance.
 
Sort by date Sort by votes
B

beelze69

Active Member
Licensed User
Longtime User
Erel said:
Please don't make duplicate posts.
Click to expand...
Hi Erel

I am sorry.. I did not understand and I do not mean to create any duplicate posts... Kindly correct me if I there is any mistake from my side.

I tried to post this query in one forum but I got a message that the 'thread is old and I was asked to post a new thread' which I did .

Kindly guide me.

Thanks.
 
Upvote 0

OliverA

Expert
Licensed User
Longtime User
beelze69 said:
(which is to be kept in C:\Program Files (x86)\Anywhere Software\B4J\Libraries)
Click to expand...
No. Any non-core additional libraries, either from this forum or from outside sources should be placed in the "Additional Libraries" folder that is set up in the Paths Configuration dialog box of the B4J IDE (Tools-Configure Paths)
beelze69 said:
My doubt: 1) For Oracle, what is the relevant .jar file ? 2) What is the #AdditionalJar: entry to be made ?
Click to expand...
That depends on the version of Oracle that you are using. You can start looking here for the appropriate driver: https://www.oracle.com/database/technologies/appdev/jdbc-downloads.html
beelze69 said:
TNSNAMES="MYTNS"
Click to expand...
To use a TNS file, you'll have to tell the jRDC2 server where to find it. The easiest way would be to just pass a command line argument when starting the jRDC2 server (see https://stackoverflow.com/a/14195154)
beelze69 said:
SCHEMA NAME="MYSCHEMA"
Click to expand...
Using a schema adds more complexity. Out of the box, B4J's jServer (used by jRDC2) uses C3P0 for pooling. Looks like you'll have to use some JavaObject to set the preferred test query for C3P0 and have it execute it on each pool connection check-out. See http://dan.bodar.com/2013/01/24/setting-the-default-schema-with-oracle-jdbc-driver/

As to the JDBC URL configuration, look under the Connections header here https://www.oracle.com/database/technologies/faq-jdbc.html. Also, the link given above for the usage of schemas with JDBC has a good example of a JDBC URL.
 
Upvote 0

OliverA

Expert
Licensed User
Longtime User
OliverA said:
Looks like you'll have to use some JavaObject to set the preferred test query for C3P0 and have it execute it on each pool connection check-out.
Click to expand...
In the RDCConnector class module, add
B4X: 
Dim jo As JavaObject = pool
jo.RunMethod("setPreferredTestQuery", Array("alter session set current_schema=MYSCHEMA"))
jo.RunMethod("setTestConnectionOnCheckout", Array(True))
after the pool.Initialize line.
Notes:
1) Adding this to jRDC2 may cause other non-Oracle DB's to fail. A non-existent schema may also cause failures (this will impact all SQL requests).
2) This code is adding a fixed schema. If you want to have a configurable schema via the config.properties file, you'll have to make some modifications (just look at how DriverClass, JdbcUrl, User and Password are handled)
 
Upvote 0
B

beelze69

Active Member
Licensed User
Longtime User
Hi Oliver,

Thanks for the response.. Will try this out and revert back if there are any issues.

One doubt:

In the above command, I understand that the 'alter session set current_schema=MYSCHEMA' is a 'Oracle-specific'.

But what is setTestConnectionOnCheckout ? I mean is it some java function ? or some B4A ? ..in which library is this function existing ?

Thank you very much.
 
Upvote 0

OliverA

Expert
Licensed User
Longtime User
beelze69 said:
But what is setTestConnectionOnCheckout ? I mean is it some java function
Click to expand...
It’s an api method provided by the c3po connection pool. C3po is written in Java and we call the method directly via use of JavaObject. It’s a third party library that is included in the base jServer library.
 
Upvote 0
B

beelze69

Active Member
Licensed User
Longtime User
Hi Oliver,

Thanks.. I was able to connect to my Oracle database via JRDC2 directly today.. I used jdbc8.jar driver. Followed EREL's postings on JRDC2 and was able to successfully invoke the query output in my b4A Application..

Thanks for all the support...
 
Upvote 0
B

beelze69

Active Member
Licensed User
Longtime User
Erel said:
Please don't make duplicate posts.
Click to expand...
Hi Erel !

Thanks ! I was able to connect to my Oracle database via JRDC2 finally today.

Thanks for all the help and support.. Followed your Tutorial on JRDC2 till the end.

I understood later what you meant when you said not to put duplicate posts... I had earlier posted regarding my JRDC2 doubts a couple of months back. But since I could not find time to follow up on it, I again posted it this time.

What I understand is that , henceforth if I have to follow up on my doubts then I have to 'PICK THE SAME THREAD' and continue (and only if that thread is old), should I post a new thread.

Please correct me if I am mistaken.

Thank you once again.
 
Last edited:
Upvote 0
You must log in or register to reply here.
Menu
Log in
Register
Cookies are required to use this site. You must accept them to continue using the site. Learn more…