View topic - Oracle 10g client from QNX

Oracle 10g client from QNX

Discussion about the general applications that run on QNX6

Oracle 10g client from QNX

Postby juanplacco » Sat Jan 17, 2009 7:30 am

Hi!

Somebody knows how can I connect to a 10g Oracle Server (running on windows) from QNX 6.3?

Thank you!

Regards,
Juan Manuel
juanplacco
Senior Member
 
Posts: 318
Joined: Wed May 17, 2006 2:30 pm

RE: Oracle 10g client from QNX

Postby maschoen » Sun Jan 18, 2009 6:48 pm

Ethernet? TCP/IP? ODBC? SQL? I don't think I've answered your question. Is there more to the question?
maschoen
QNX Master
 
Posts: 2720
Joined: Wed Jun 25, 2003 5:18 pm

RE: Oracle 10g client from QNX

Postby juanplacco » Sun Jan 18, 2009 8:00 pm

Thank you maschoen!!

Yes.. sure... Everything is possible... I know... I just wondering which would be the most direct method, or better, "which is already done..."

I thinked ODBC as first choice, but is there an ODBC library already done or compiled for QNX to access Oracle? I don't think so...

Is there someone who already did it in some way?

Thank you!!!
Juan Manuel
juanplacco
Senior Member
 
Posts: 318
Joined: Wed May 17, 2006 2:30 pm

RE: Oracle 10g client from QNX

Postby juanplacco » Tue Feb 17, 2009 1:05 am

Hi again... Sorry, but this Oracle DB is driving me mad! The thing is:

I need to query an Oracle DB and I think the best way is doing so in XML format, because I don't find some ODBC driver for QNX.

I've made some progress but they're still not enough. :x

I wrote a FIXED query (just a SELECT) from JDeveloper (here is my big lack of knowledge) and then I run it. Doing so, a tcp service listening to specific port was created. (I also have a program called lsnrctl running at server side)

After this I wrote a tcp client wich opens a socket to that IP/PORT, and then write something like: "Application1-test1-context-root/query.xsql" wich is a virtual directory where query.xsql was created by me (well, that's my guess).

Ok, I happily have the result of my FIXED query in XML format:

<?xml version="1.0" encoding="windows-1252"?>
<ROWSET>
<ROW NUM = '1'>
<field_1>value_1</field_1>
<field_2>value_2</field_2>
<field_3>value_3</field_3>
<field_4>value_4</field_4>
</ROW>
<ROW NUM = '2'>
<field_1>value_A</field_1>
<field_2>value_B</field_2>
<field_3>value_C</field_3>
<field_4>value_D</field_4>
</ROW>
:
:
</ROWSET>

Even I can access this data from Voyager, or any browser just pointing to:

http://IP:PORT/Application1-test1-conte ... query.xsql and everything works just fine.

I bet you already know my question. I need to pass the query as a dynamic parameter. Something like:

http:IP:PORT/...?SQL="SELECT * FROM MY_TABLE WHERE my_condition ORDER BY my_column" from the browser or my client, it doesn't really matter.

Well, have you ever have met with an Oracle DB? Or similar?

I will appreciate any suggestion !!

Thank you in advance !
JM
juanplacco
Senior Member
 
Posts: 318
Joined: Wed May 17, 2006 2:30 pm

RE: Oracle 10g client from QNX

Postby juanplacco » Wed Feb 25, 2009 3:25 pm

This post is just for historical purposes. Now it's working, I want to post this simple guide if someone ever needs to communicate to Oracle from QNX node. It's simple (once you know the way :)).

Obviously is not the only way to do it

Server side:
---------------

Prerequisites:

a) Oracle 10g (I think it works with 9i and 8i, but 8i needs to install the xsql functionality)
b) J2SE 1.3.1 or later

Now,

1) Install OC4J Servlet Container in stand alone mode. It's a ZIP file. oc4j_extended.zip

2) Unzip it (maybe ORACLE_HOME is the best place)

3) Now OC4J HOME is: ORACLE_HOME\oc4j_extended\j2ee\home

4) You must verify that the following files are in HOME\lib (OC4J HOME not ORACLE_HOME)
a. xmlparserv2.jar
b. classes12.jar

5) You must add these ones to the same directory (this is very important):
a. xsu12.jar
b. oraclexsql.jar
c. xsqlserializers.jar

6) Copy XSQLConfig.xml to HOME\ default-web-app\WEB-INF\classes

7) Edit this file to configure the connection. Like this:

<connection name="my_connection">
<username>your_usr</username>
<password>your_pwd</password>
<dburl>jdbc:oracle:thin:@localhost:1521:SID</dburl>
<driver>oracle.jdbc.driver.OracleDriver</driver>
</connection>

Note the SID: Put yours!

8 ) now is time to configure OC4J to make it able to execute .XSQL Servlets Template Files. Edit HOME\config\global-web-application.xml and add:

<servlet>
<servlet-name>xsql</servlet-name>
<servlet-class>oracle.xml.xsql.XSQLServlet</servlet-class>
</servlet>

<servlet-mapping>
<servlet-name>xsql</servlet-name>
<url-pattern>/*.xsql</url-pattern>
</servlet-mapping>

9) Add a virtual directory entry to HOME\application-deployments\default\defaultWebApp\orion-web.xml

<virtual-directory virtual-path="/xsql"
real-path="C:\JDeveloper\bin\oc4j_extended\j2ee\home\default-web-app\examples\servlets\xsql" />
This was just my example.

10) Define JAVA_HOME env variable (Eg: C:\Programs Files\Java\jdk1.5.0_06)

11) Create .xsql file (maybe this is the most important step). For example query.xsql

<?xml version = '1.0'?>
<xsql:query connection="your_connection" xmlns:xsql="urn:oracle-xsql" date-format="yyyy-MM-dd HH:mm:ss">
{@sql}
</xsql:query>

And save it in the real-path associated to the virtual-path (step 9)
{@sql} will be replace by your own dynamic query.

12) Start OC4J server (there is a .cmd file somewhere)

13) Try from your browser: http://localhost:8888 (8888 is just the default port, you can change if you want)

14) It must show you OC4J home page

15) Final test: Try: http://localhost:8888/xsql/query.xsql?sql=<sql_query>

16) If you see the XML response, all it's done.

CLIENT SIDE
---------------

Well... it's up to you to: (this could be little hard for newbies)

1) Open the Socket
2) Send the request (GET /virtual_path/query.xsql?sql=<you_query> HTTP/1.1... and so on)
3) Read the answer,
3.1) Get and verify the header
3.2) Get the XML response data
3) Parse the XML data

Error checking/format checking/time outs... well, this needs some work.

Last notes:

1) If XML if big enough, the page will be naturally chunked (check this when parsing)

2) The default heap-size of JVM (wich is used for data exchange) is 2MB, so if is not enough for you, you must change this default in order to avoid heap-exhausted error.

I hope this helps... I struggled enough to make it work...

Regards,
JM
juanplacco
Senior Member
 
Posts: 318
Joined: Wed May 17, 2006 2:30 pm

RE: Oracle 10g client from QNX

Postby mario » Wed Feb 25, 2009 10:40 pm

Great info!
mario
QNX Master
 
Posts: 4132
Joined: Sun Sep 01, 2002 1:04 am


Return to QNX6 - Applications

Who is online

Users browsing this forum: No registered users and 1 guest

cron