Basic ORACLE question

[jmich]

Hi !
I've been programming java for a while and am now working on an applet wich should access an ORACLE Database.

Until now I fit every DB-related parts of the applet to work with an MSAccess-DB using the ODBC-bridge.

I've downloaded the ORACLE thin-driver, but have no idea how to use it.

Could anyone give me some advice ??

[Leon]

Well, I think you have some examples that comes together eith the driver!
Have a look at them

orathin\samples\thin-1.0.2\JdbcApplet.java

[jmich]

Hi again !!!!

Well, I found the examples on the oracle homepage.

But in the example they import jdbc.sql.*
The compiler can't find that package - I browsed through the thindriver and it's not there.
Where is that package located ??

Jesper

[Leon]

There is no need to import Oracle driver package.
It should be loaded dynamicly.
In the applet tag, plase the parameter archive="classes111.jar".

In the source:
import java.sql.*;
........
Class.forName("oracle.jdbc.driver.OracleDriver");
....
Connection conn = DriverManager.getConnection ("blabla");
....

That's all!

Leon
http://people.bulgaria.com/vleonkev

[jmich]

Hi Leon - you're really beeing helpfull here, thanks !!!

I don't have classes111.jar, but I have classes111.zip - I guess that's all the same.
Ok, so far so good.

Now the hard part :
In the samples it says
Connection conn = DriverManager.getConnection ("jdbcracle:thin:scott/tiger@dlsun511:1721bms733");

that scott/tiger@blablabla- thing is totally chinese for me.
I've read a lot of docs so far, but still don't see how it all works.

In the sample .java-file it's kind of explained by :
"jdbcracle:thin:scott/tiger@(description=(address_list=(address=(protocol=tcp)(host=dlsun511)(port=1610))(address=(protocol=tcp)(host=pkrishna-pc2)(port=1521)))(source_route=yes)(connect_data=(sid=orcl)))"
I don't know if that's supposed to help me, but it doesn't !

I made a Oracle DB named MusikerDB and want to connect to it.
Could you give me a hint ?


Jesper

[Leon]

You have the 'hard part'

take a look at this method, it is quite useful:

/*=======================================================================
returns the Oracle connect string
sample: "jdbcracle:thin:system/[email protected]:1521:ORCL";
=======================================================================*/
public String getConnectString(){
return "jdbcracle:thin" + ":" +
getORAUserName() + "/" +
getORAPassword() + "@" +
getORAIPAddress() + ":" +
getORAPort() + ":" +
getORADSN();
}

You will have to write your own methods getORAxxxxxx

Leon
http://people.bulgaria.com/vleonkev

[jmich]

Hi Leon !
I've just visited your homepage and seen your CV - no wonder you know so much about this stuff !!!

Ok, I just want to make sure I got everything right now :

1) I must figure out the IP-adress and port of my homepage.
2) I have to make my own DSN (how do I do that ?)
3) The DB, DSN, the class111.zip-file and the applet should all be on my homepage.

Of course I should setup my database with the right username and passwords.

Did I miss anything ?


Jesper

[Leon]

All this getORAxxx methods return information about your ORACLE server.
I should be located on teh web server, because of the java security restrictions.
The DSN is usually "ORCL", and the port "1521". You don't need to setup the DSN.
THe IP address it the same as the web server's IP.
You also need vallid user name/password to connect to the ORA server.

Here is the "getORAIP" method, really a good one

/*=======================================================================
returns the IP address of the Oracle server
=======================================================================*/
public String getORAIPAddress(){
String r = "";
String defIP = "200.1.1.1"; // default ORA server IP while running in appletviewer
try{
if(this.getCodeBase().getHost().length()>0)
r = InetAddress.getByName(this.getCodeBase().getHost()).getHostAddress();
}catch(Exception e){
System.out.pritnln("getORAIP() error!"):
}
if(r.length()==0) // started within appletviewer
return defIP;
return r;
}

Leon
http://people.bulgaria.com/vleonkev

[jmich]

Ok, Leon - thanks.
You've been a great help on getting me started on this !!!

All the best !

Jesper

[jmich]

I've tried my best, but I can't make it work !
I made the code below to test the connection, but it returns "Driver not found" !

Also I wondered how the applet knows wich DB to access, since it doesn't say anything about that in the code.

============================ index.html ==================
<APPLET CODEBASE="." CODE="test.class" archive="classes111.zip" WIDTH=300 HEIGHT=300></APPLET>


================================ test.class ==========================

import java.applet.*;
import java.awt.*;

public class test extends Applet {

public void init() {
paint(getGraphics());
}

public void paint(Graphics g) {
g.drawString(Queries.get("Jesper"),10,100);
}
}

==================================================================================



================================= Queries.class ==========================

import java.util.*;
import java.sql.*;
import oracle.sql.*;


public class Queries {

public static String get(String keyWord) {
String result=""; //(I found the IP below using getORAIP on the server)
String Connect = "jdbcracle:thin:system/[email protected]:1521:ORCL");
String q = "Select * from bands WHERE Fornavn = '"+keyWord+"';";

try {
Class.forName("oracle.jdbc.driver.OracleDriver");
result = "Driver found";
}
catch(ClassNotFoundException cnfe) {
result="Driver not found";
}

try {
Connection c = DriverManager.getConnection(Connect);
}
catch(SQLException se) {
}

return result;

}
==================================================================================

On the server I placed the folowing files in the folder ../test :

classes111.zip
getIP.class
index.html
musikere.odb
Queries.class
test.class

[jmich]

Here's the code in the right format - sorry :


<APPLET CODEBASE="." CODE="test.class" archive="classes111.zip" WIDTH=300 HEIGHT=300></APPLET>


================================ test.class ==========================

import java.applet.*;
import java.awt.*;

public class test extends Applet {

public void init() {
paint(getGraphics());
}

public void paint(Graphics g) {
g.drawString(Queries.get("Jesper"),10,100);
}
}
==================================================================================



================================= Queries.class ==========================

import java.util.*;
import java.sql.*;
import oracle.sql.*;


public class Queries {

public static String get(String keyWord) {
String result=""; //(I found the IP below using getORAIP on the server)
String Connect = "jdbcracle:thin:system/[email protected]:1521:ORCL");
String q = "Select * from bands WHERE Fornavn = '"+keyWord+"';";

try {
Class.forName("oracle.jdbc.driver.OracleDriver");
result = "Driver found";
}
catch(ClassNotFoundException cnfe) {
result="Driver not found";
}

try {
Connection c = DriverManager.getConnection(Connect);
}
catch(SQLException se) {
}

return result;

}
==================================================================================




On the server I placed the folowing files in the folder /test :

classes111.zip
getIP.class
index.html
musikere.odb
Queries.class
test.class

[Leon]

1. This sould be working! I can't find any errors!

2. The DB is specified in the connect string:

String Connect = "jdbcracle:thin:system/[email protected]:1521:ORCL");
^^^^^^

Leon

[jmich]

I corrected a few things - I exchanged the classes111.zip(wich I think was corrupted) with a newer version called 816classes12.zip.

You wrote about the DSN earlier that it is usually called ORCL and that I don't need to set it up. But isn't the DSN a file and shouldn't it be placed on the server manually or is it a part of the classes-file ?

This is really starting to get on my nerves !!

You can take a look at the part of my site where I put these files :
http://home3.inet.tele.dk/jmich/test
I changed the index-file to _index.html so that you'll get a listing of the files in the directory.

I hope you're not tired of answering all my questions, but if you are - please let me know and I'll try to find someone else to help me !!

Jesper

[Leon]

You have problems with the archive file, as you know

have a look at your applet on my server with my driver archive
http://testpc.mlsystems.bg/vlady/_index.html

it says "driver found!

So take this driver archive:
http://testpc.mlsystems.bg/vlady/classes111.jar

I hope everything will be fine now
Besides I am really sorry to be late with the answers,
just because I am currently writing on my dyploma work.

Leon
http://people.bulgaria.com/vleonkev

[jmich]

Hi Leon !

First of all I don't think you are slow to answer at all - even in the microsoft newsgroup, wich is constantly monitored by several staff-members you can wait days to get an answer (if you get one !).

I made an incredible stupid mistake : in the Applet-tag I misspelled the 816classes12.zip. Now I corrected it and it also said 'Driver Found'.

I tried with the archive file I downloaded from you and with the same good result !!!

Still I can't get the applet to find posts in the database, but I'm not sure if I've set up the database correctly - I'll have to work on that.

But you are sure that I don't have to specify the name of the database (musikere.odb) in my code somewhere ?

I'm sorry to disturb you in your work - thanks again !!!

Jesper