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

[beelze69]

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.

 

[Erel]

Please don't make duplicate posts.

 

[beelze69]

Please don't make duplicate posts.

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.

 

[OliverA]

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

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)

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

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

TNSNAMES="MYTNS"

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)

SCHEMA NAME="MYSCHEMA"

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.

 

[OliverA]

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.

In the RDCConnector class module, add

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)

 

[beelze69]

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.

 

[OliverA]

But what is setTestConnectionOnCheckout ? I mean is it some java function

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.

 

[beelze69]

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...

 

[beelze69]

Please don't make duplicate posts.

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.