jQuery in Action, 2nd edition*
The moose likes JDBC and the fly likes JDBC Connection Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


JavaRanch » Java Forums » Databases » JDBC
Bookmark "JDBC Connection" Watch "JDBC Connection" New topic
Author

JDBC Connection

Raihan Jamal
Ranch Hand

Joined: Mar 23, 2010
Posts: 86
When i am trying to run my program TrimLob.java it is giving me these errors.... Any body knows why is it happening so?



C:\Program Files\Java\jdk1.6.0\bin>javac TrimLob.java
TrimLob.java:13: package oracle.sql does not exist
import oracle.sql.*;
^
TrimLob.java:37: package oracle.jdbc does not exist
DriverManager.registerDriver(new oracle.jdbc.OracleDriver());
^
TrimLob.java:71: cannot find symbol
symbol : class BLOB
location: class TrimLob
BLOB blob = (BLOB) rset.getObject (2);
^
TrimLob.java:71: cannot find symbol
symbol : class BLOB
location: class TrimLob
BLOB blob = (BLOB) rset.getObject (2);
^
TrimLob.java:72: cannot find symbol
symbol : class CLOB
location: class TrimLob
CLOB clob = (CLOB) rset.getObject (3);
^
TrimLob.java:72: cannot find symbol
symbol : class CLOB
location: class TrimLob
CLOB clob = (CLOB) rset.getObject (3);
^
6 errors
Jan Cumps
Bartender

Joined: Dec 20, 2006
Posts: 2501
    
    8

Welcome to JavaRanch, Raihan.

To get rid of the compilation errors, you have to add the Oracle jdbc libraries to your classpath.

(additional: your code should not code for a particular database. It is better to use general jdbc classes in stead of Oracle's classes)

Regards, Jan


OCUP UML fundamental and ITIL foundation
youtube channel
Raihan Jamal
Ranch Hand

Joined: Mar 23, 2010
Posts: 86
I have figured it out the above problem...
but now i am having one more problem of :---

java.sql.SQLException: ORA-01722: invalid number at oracle.jdbc.driver.SQLStateMapping.newSQLException(SQLStateMapping.

I am trying to insert record into oracle database by reading it from notepad(textfile).. so i am having two table.... Photo and Restaurant....
For photo it gets inserted easily with no problem.... but for restaurant it is giving this error as soon as it start reading the first value of first row in my notepad..


import java.sql.DriverManager;
import java.sql.Connection;
import java.sql.Statement;
import java.sql.ResultSet;
import java.sql.SQLException;

import java.util.StringTokenizer;

import java.io.FileReader;
import java.io.BufferedReader;
import java.io.IOException;

import java.util.Date;
import java.text.SimpleDateFormat;
import java.text.NumberFormat;
import java.text.ParseException;



public class populate {


public static void main(String[] args)
throws java.lang.InterruptedException {

String driverClass = "oracle.jdbc.driver.OracleDriver";
String connectionURL = "jdbcracle:thin:@RJ:1521:ORCL";
String userID = "scott";
String userPassword = "tiger";
//String inputFileName = "photos.txt";
// String TABLE_TYPE = "PHOTO_TYPE";
String TABLE_NAME1 = "PHOTO";
String TABLE_NAME2 = "RESTAURANT";
String DELIM = ",";
Connection con = null;
String inputFileName1 = args[0];
String inputFileName2 = args[1];

try {

System.out.print(" Loading JDBC Driver -> " + driverClass + "\n");
Class.forName(driverClass).newInstance();

System.out.print(" Connecting to -> " + connectionURL + "\n");
con = DriverManager.getConnection(connectionURL, userID, userPassword);
System.out.print(" Connected as -> " + userID + "\n");

} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (InstantiationException e) {
e.printStackTrace();
} catch (IllegalAccessException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
}

Statement stmt1 = null;
Statement stmt2 = null;

int insertResults = 0;

StringTokenizer st1= null;
StringTokenizer st2= null;

String ID;
String NAME;
String X;
String Y;
String TAGS;

String ID_RESTAURANT;
String Name_RESTAURANT;
String X_RESTAURANT;
String Y_RESTAURANT;
String FOODTYPE;
String PHONENO;




try {

System.out.print(" Creating Statement...\n");
stmt1 = con.createStatement ();
stmt1.executeUpdate("delete from PHOTO");
stmt1.executeUpdate("commit");
System.out.print(" Create FileReader Object for file: " + inputFileName1 + "...\n");
FileReader inputFileReader1 = new FileReader(inputFileName1);

System.out.print(" Create BufferedReader Object for FileReader Object...\n");
BufferedReader inputStream1 = new BufferedReader(inputFileReader1);

String inLine1 = null;
while ((inLine1 = inputStream1.readLine()) != null) {

st1 = new StringTokenizer(inLine1, DELIM);

ID = st1.nextToken();
NAME = st1.nextToken();
X = st1.nextToken();
Y = st1.nextToken();
TAGS = st1.nextToken();

System.out.print(" Inserting value for [" + NAME + "]\n");

insertResults = stmt1.executeUpdate(
"INSERT INTO " + TABLE_NAME1 + " VALUES ("
+ ID +
" , '" + NAME + "'" +
" , '" + X + "'" +
" , '" + Y + "'" +
" , '" + TAGS + "')");

System.out.print(" " + insertResults + " row created.\n");
System.out.print(" " + ID + " FOR PARTICLUAR ROW.\n");
System.out.print(" " + NAME + " row created.\n");
System.out.print(" " + X + " row created.\n");
System.out.print(" " + Y + " row created.\n");



}



System.out.print(" Commiting Transaction...\n");
con.commit();

System.out.print(" Closing inputString...\n");
inputStream1.close();

System.out.print(" Closing Statement...\n");
stmt1.close();




------- till this data is getting inserted in to my PHOTO table.... but after this for RESTAURANT table it displayS the error of invalid number..... as i have posted above





// for restaurant
System.out.println();System.out.println();System.out.println();
System.out.print(" Creating Statement for restaurants...\n");
stmt2 = con.createStatement ();
stmt2.executeUpdate("delete from RESTAURANT");
stmt2.executeUpdate("commit");
System.out.print(" Create FileReader Object for file: " + inputFileName2 + "...\n");
FileReader inputFileReader2 = new FileReader(inputFileName2);
System.out.print(" Create BufferedReader Object for FileReader Object...\n");
BufferedReader inputStream2 = new BufferedReader(inputFileReader2);
String inLine2 = null;
while ((inLine2 = inputStream2.readLine()) != null) {

st2 = new StringTokenizer(inLine2, DELIM);



ID_RESTAURANT = st2.nextToken();
Name_RESTAURANT = st2.nextToken();
X_RESTAURANT = st2.nextToken();
Y_RESTAURANT = st2.nextToken();
FOODTYPE = st2.nextToken();
PHONENO = st2.nextToken();






System.out.print(" Inserting value for [" + Name_RESTAURANT + "]\n");
insertResults = stmt2.executeUpdate(
"INSERT INTO " + TABLE_NAME2 + " VALUES ("
+ ID_RESTAURANT +
" , '" + Name_RESTAURANT + "'" +
" , '" + X_RESTAURANT + "'" +
" , '" + Y_RESTAURANT + "'" +
" , '" + FOODTYPE + "'" +
" , '" + PHONENO+ "')");

System.out.print(" " + insertResults + " row created.\n");


}


System.out.print(" Commiting Transaction...\n");
con.commit();

System.out.print(" Closing inputString...\n");
inputStream2.close();

System.out.print(" Closing Statement...\n");
stmt2.close();

} catch (SQLException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}


try {
System.out.print(" Closing Connection...\n");
con.close();

} catch (SQLException e) {

e.printStackTrace();

}


}

}

----------------------------------------------------------------------

do reply what is the problem.... as in my database these are the tabe that i am using....

CREATE TABLE PHOTO(
ID NUMBER NOT NULL PRIMARY KEY ,
Name VARCHAR2(10),
X DOUBLE PRECISION,
Y DOUBLE PRECISION,
Tags VARCHAR2(40)
);

CREATE TABLE RESTAURANT(
ID_RESTAURANT NUMBER NOT NULL PRIMARY KEY ,
Name_RESTAURANT VARCHAR2(10),
X_RESTAURANT DOUBLE PRECISION,
Y_RESTAURANT DOUBLE PRECISION,
FOODTYPE VARCHAR2(20),
PHONENO NUMBER
);
Raihan Jamal
Ranch Hand

Joined: Mar 23, 2010
Posts: 86
These are the error i am having to the above question put up by me..... Do reply as soon as possible.......


java.sql.SQLException: ORA-01722: invalid number

at oracle.jdbc.driver.SQLStateMapping.newSQLException(SQLStateMapping.java:74)
at oracle.jdbc.driver.DatabaseError.newSQLException(DatabaseError.java:110)
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:171)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:455)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:413)
at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:1030)
Closing Connection...
at oracle.jdbc.driver.T4CStatement.doOall8(T4CStatement.java:183)
at oracle.jdbc.driver.T4CStatement.executeForRows(T4CStatement.java:936)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1222)
at oracle.jdbc.driver.OracleStatement.executeUpdateInternal(OracleStatement.java:1706)
at oracle.jdbc.driver.OracleStatement.executeUpdate(OracleStatement.java:1674)
at oracle.jdbc.driver.OracleStatementWrapper.executeUpdate(OracleStatementWrapper.java:274)
at populate.main(populate.java:170)
Jan Cumps
Bartender

Joined: Dec 20, 2006
Posts: 2501
    
    8

Hi Raihan,

Please use code tags in your post.
You can edit your post by clicking on the Edit icon at its right top.


I would have a look at the error message, and at the line shown in the errormessage: at populate.main(populate.java:170)

Regards, Jan
Raihan Jamal
Ranch Hand

Joined: Mar 23, 2010
Posts: 86
Thanks Jan.... I have solved the above problem... but now i am having one more problem...
I wanted to insert multiple images into database using JDBC by reading it from the file... and i am passing photos.txt(my text file) as an input parameter... I have inserted all the values into the database except for the image part... this is my content of photos.txt file and i have copied all the images into the folder
" C:\\photos "

1,in1.jpg,108,19,in-n-out
2,in2.jpg,187,21,in-n-out
3,in3.jpg,308,41,in-n-out
4,in4.jpg,477,52,in-n-out
5,in5.jpg,530,50,in-n-out


and i want to store in1.jpg,in2.jpg,in3.jpg,in4.jpg,in5.jpg into the oracle databse using JDBC.... i have tried a lot using BLOB column.... and i have created my table as

CREATE TABLE PHOTO(
ID NUMBER NOT NULL PRIMARY KEY ,
Name BLOB,
X DOUBLE PRECISION,
Y DOUBLE PRECISION,
Tags VARCHAR2(40)
);


-----------------------------------------------------------------------------------------------------------

------------------------------------------------------------------------------------------------------------



As i am able to read one image file by above code in1.jpg in to the oracle databse.... but i am not able to insert all the image file in to the database...do tell me what should i do.... and can you give me the example on the basis of the above code of mine...
do reply as soon as possible..
Jan Cumps
Bartender

Joined: Dec 20, 2006
Posts: 2501
    
    8

Hi Raihan,

Glad your original issue is resolved.

For the new issue with uploading pictures, I think it is better that you create a brand new thread.
(and that you also tell in that new thread in what way it is not working. "...but i am not able to insert all the image file in to the database..." does not tell what is not working)



do reply as soon as possible..
You should not ask that on the JavaRanch

Regards, Jan

Jan Cumps
Bartender

Joined: Dec 20, 2006
Posts: 2501
    
    8

New question thread:
http://www.coderanch.com/t/489044/JDBC/java/Inserting-Multiple-Images-into-oracle
 
GeeCON Prague 2014
 
subject: JDBC Connection