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 ("jdbc:oracle:thin:scott/tiger@dlsun511:1721:dbms733");

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 :
"jdbc:oracle: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: "jdbc:oracle:thin:system/manager@200.1.1.100:1521:ORCL";
=======================================================================*/
public String getConnectString(){
return "jdbc:oracle: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 = "jdbc:oracle:thin:system/manager@198.182.148.157: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 = "jdbc:oracle:thin:system/manager@198.182.148.157: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 = "jdbc:oracle:thin:system/manager@198.182.148.157: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

[Leon]

Man, what is that kind of database with *.odb files?
It is not oracle! :) You don't need to specify file names, when connecting to Oracle DB!!!
In the common case you need ORA client (with is the driver inside the classes111.zip archive).
The Oracle server is listening on a port (usually 1521) and that's all!
Mind that the Ora server should be on the same mashine, where the web server is!!!!

Leon

[jmich]

Well, I've never worked with any kind of Oracle-products before and I don't know anything about Oracle whatsoever.
I just chose Oracle because it seemed to have good drivers for the internet !

So, what I did was to visit the Oracle-homepage and download Oracle8i Lite 4.0 to try to create a DB.
And when I create a DB in Oracle Lite, it's named *.odb !!

I've tried to find other programs to create Oracle DB's, but I can't find any.

1. What program should I use to create an Orace DB ?
2. What are Oracle DB's supposed to be named as ?

Jesper

[Leon]

Actually you don't need files, you need properrly configured Oracle :)

To do this use the oracle tools. Here are some of them (from my Programs startup menu):
Enterprise Manager.lnk
Daemon Manager.lnk
Network Topology Generator.lnk
Security Manager.lnk
Storage Manager.lnk
Instance Manager.lnk
Schema Manager.lnk
Backup Manager.lnk
SQL Worksheet.lnk
Data Manager.lnk
Enterprise Manager Help.lnk
Enterprise Manager Readme.lnk
Oracle Administrator Toolbar.lnk

[jmich]

I guess I'm just a total novice in Oracle matters.
I don't understand that you can put a DB on a homepage without creating a DB with tables and all that.

You wrote that I need no files, but properly configured Oracle.
Does 'Oracle' come in the form of a file or do I have to consult my ISP to make them install something on the server or what ?

I realize that this will properly take me some time to learn and I guess that you don't really have time to give me a 'step to step'-instruction on this matter (you have to work on your diploma-work) !

I don't want to take all your time, so unless it's really no problem for you, you don't have to respond to this.

All the best

Jesper

[Leon]

think it's better to read this:
http://www.itlibrary.com/reference/library/078970935x/index.htm
http://www.itlibrary.com/reference/library/1575211025/index.htm
http://www.itlibrary.com/reference/library/1575211661/index.htm

...and forget the files ;)