aspose file tools*
The moose likes JDBC and the fly likes errors uploading an image to the database Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Java 8 in Action this week in the Java 8 forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "errors uploading an image to the database" Watch "errors uploading an image to the database" New topic
Author

errors uploading an image to the database

Heshan Perera
Greenhorn

Joined: Sep 06, 2011
Posts: 7
I already did the image browsing and this is my code for the upload button,

// im taking the path of the image from where i retrieved from browsing to a variable called 'pic'...and also im getting the Id of a student to enter in with the pic into the database
String pic = path.getText();
int sid= Integer.parseInt(SID.getText());

// created a data access object called upload in the DAO in another frame to send the image to the database through this form where i have written the method for it to convert the image into a bit stream

StudentDAO upld = new StudentDAO();
try {
upld.SaveImageToDatabase(sid, pic);
} catch (SQLException ex) {
Logger.getLogger(student.class.getName()… null, ex);
}

//and this is the method i wrote for that



public boolean SaveImageToDatabase(int sid,String fin) throws SQLException {

boolean result=false;
Connection dbConn = null;

FileInputStream fis = null;

try {
dbConn = dbConnManager.connect();

ResultSet rs = null;

PreparedStatement psmnt = null;

String query = "insert into image(person_id,image) "+ "values(sid,fin)";

PreparedStatement stmt = dbConn.prepareStatement(query);



File file= new File(fin);
fis = new FileInputStream(fin);
stmt.setInt(1, sid);
stmt.setString(1,fin);

stmt.setBinaryStream(2, fis, (int) fin.length());

System.out.println("Image SIZE = " +fin.length());
System.out.println("Successfully inserted ");
stmt.execute();
dbConn.commit();

} catch (SQLException e) {
System.out.println("Could not insert into DB");
e.printStackTrace();
} catch (FileNotFoundException e) {
System.out.println("Could not insert into DB");
e.printStackTrace();
}
finally {

try {
fis.close();
} catch (IOException e) {
e.printStackTrace();
}
dbConn.close();

return result;
}
}

and im getting errors in the runtime saying "java.sql.SQLException: Parameter index out of range (1 > number of parameters, which is 0)." So is my method incorrect and is there more errors apart from this and what can i do ???
Aruna Jayabalu
Greenhorn

Joined: Aug 09, 2011
Posts: 13
What is the datatype of the image column in your database?
Check for the column size also..

Thanks,
Aruna.J
Heshan Perera
Greenhorn

Joined: Sep 06, 2011
Posts: 7
Aruna Jayabalu wrote:What is the datatype of the image column in your database?
Check for the column size also..

Thanks,
Aruna.J


it's image LONGBLOB ....and did not define a size...do we have to define a size for longblob?
Aruna Jayabalu
Greenhorn

Joined: Aug 09, 2011
Posts: 13
stmt.setInt(1, sid);
stmt.setString(1,fin);

You are trying to set both sid and fin to the 1st parameter person_id.
This is the reason for the error. Change it to 2 and try once.


Thanks,
Aruna.J
Heshan Perera
Greenhorn

Joined: Sep 06, 2011
Posts: 7
Aruna Jayabalu wrote:stmt.setInt(1, sid);
stmt.setString(1,fin);

You are trying to set both sid and fin to the 1st parameter person_id.
This is the reason for the error. Change it to 2 and try once.


Thanks,
Aruna.J


same error i get again..."java.sql.SQLException: Parameter index out of range (1 > number of parameters, which is 0)."
Aruna Jayabalu
Greenhorn

Joined: Aug 09, 2011
Posts: 13
Your prepared statement usage is wrong..
You are trying to set 3 values for 2 parameters which is again wrong.

Just try like this

File file = new File("myimage.gif");
FileInputStream fis = new FileInputStream(file);
PreparedStatement ps =
conn.prepareStatement("insert into images values (?,?)");
ps.setString(1,file.getName());
ps.setBinaryStream(2,fis,(int)file.length());
ps.executeUpdate();
ps.close();
fis.close();

Change accordingly.

Thanks,
Aruna.J
Heshan Perera
Greenhorn

Joined: Sep 06, 2011
Posts: 7
Aruna Jayabalu wrote:Your prepared statement usage is wrong..
You are trying to set 3 values for 2 parameters which is again wrong.

Just try like this

File file = new File("myimage.gif");
FileInputStream fis = new FileInputStream(file);
PreparedStatement ps =
conn.prepareStatement("insert into images values (?,?)");
ps.setString(1,file.getName());
ps.setBinaryStream(2,fis,(int)file.length());
ps.executeUpdate();
ps.close();
fis.close();

Change accordingly.

Thanks,
Aruna.J


i did the changes according to you and here it is..

public boolean SaveImageToDatabase(int sid,String fin) throws SQLException {

boolean result=false;
Connection dbConn = null;


try {
dbConn = dbConnManager.connect();

ResultSet rs = null;

PreparedStatement psmnt = null;

File file= new File(fin);
FileInputStream fis = new FileInputStream(file);
PreparedStatement ps =
dbConn.prepareStatement("insert into image(person_id,image) values("+sid+","+fis+")");

ps.setString(1,file.getName());
ps.setBinaryStream(2,fis,(int)file.length());
ps.executeUpdate();
ps.close();
fis.close();

dbConn.commit();

} catch (SQLException e) {
System.out.println("Could not insert into DB");
e.printStackTrace();
} catch (FileNotFoundException e) {
System.out.println("Could not insert into DB");
e.printStackTrace();
}
finally {

dbConn.close();

return result;
}
}

and yet im getting the error "java.sql.SQLException: Parameter index out of range (1 > number of parameters, which is 0)."
Jesper de Jong
Java Cowboy
Saloon Keeper

Joined: Aug 16, 2005
Posts: 13884
    
  10

When you use PreparedStatement, there must be question marks "?" in the SQL string in the place where you want to have the parameter values inserted.

You don't have question marks there, you are just concatenating values into the string:


Java Beginners FAQ - JavaRanch SCJP FAQ - The Java Tutorial - Java SE 7 API documentation
Scala Notes - My blog about Scala
Heshan Perera
Greenhorn

Joined: Sep 06, 2011
Posts: 7
Jesper de Jong wrote:When you use PreparedStatement, there must be question marks "?" in the SQL string in the place where you want to have the parameter values inserted.

You don't have question marks there, you are just concatenating values into the string:



Thankz alot it works
Heshan Perera
Greenhorn

Joined: Sep 06, 2011
Posts: 7
Heshan Perera wrote:
Jesper de Jong wrote:When you use PreparedStatement, there must be question marks "?" in the SQL string in the place where you want to have the parameter values inserted.

You don't have question marks there, you are just concatenating values into the string:



Thankz alot it works


i got that right...but now there's another prob...i wrote a method to retrinve values from the DB and display it in the search interface.....

//Studetails is the class where i initialized objects and get/set methods

public StuDetails getStupic(int StuID) throws IOException {

Connection dbConn = null;

String pic = null;
StuDetails stuDetails = null;

try {

dbConn = dbConnManager.connect();
Statement stmt = dbConn.createStatement();

String query = "SELECT image" +
" FROM person " + " WHERE person_id = '"+StuID+"' ";

System.out.println(query);

ResultSet rs = stmt.executeQuery(query);
stuDetails = new StuDetails();

if (rs.next()) {

Blob test=(Blob) rs.getBlob("binarydata");
InputStream x=test.getBinaryStream();
int size=x.available();
OutputStream out=new FileOutputStream(pic);
byte b[]= new byte[size];
x.read(b);


stuDetails.setpic(out);

}

} catch (SQLException sQLException) {
System.out.println(sQLException + "-----------Select query failed for JobID");
} finally {
//Close the db connection
dbConnManager.con_close(dbConn);
}
return stuDetails;

}

but nothing is being dsplayed
Heshan Perera
Greenhorn

Joined: Sep 06, 2011
Posts: 7
Heshan Perera wrote:
Heshan Perera wrote:
Jesper de Jong wrote:When you use PreparedStatement, there must be question marks "?" in the SQL string in the place where you want to have the parameter values inserted.

You don't have question marks there, you are just concatenating values into the string:



Thankz alot it works


Ohhh and btw i, also adding more details of the person to the database..thereofore when i add the details and photos seperately the primary key gets duplicated....therefore in the above query statement i want to have only 1 parameter(the image) to be selected by the WHERE clause...as in like this..,

PreparedStatement ps = dbConn.prepareStatement("insert into image(image) values(?) where person_id="+sid+");

now is this correct then?? or is this line should be modified ???
Paul Clapham
Bartender

Joined: Oct 14, 2005
Posts: 18152
    
    8

No. If you want to update an existing record, you don't use an INSERT command. You use an UPDATE command.
Ganesan Ramakrishnan
Ranch Hand

Joined: Mar 18, 2008
Posts: 87
Hi Heshan,
You cannot insert the image directly in table.

try the following steps,
1.insert the person_id first, PreparedStatement ps = dbConn.prepareStatement("insert into image(person_id) values(?)");
2.Then update the person_id with image using update query update image set image = ? where person_id = ?

hope you understand.

do remeber you cannot insert the image using insert comment anyhow it does not show any error but you cannot retrive it again.

regards,
Ganesh

 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: errors uploading an image to the database
 
Similar Threads
How can I put the image into the DataBase?
get files from database
ORA-01461: can bind a LONG value only for insert into a LONG column
Data type mapping issue
ORA-01460: unimplemented or unreasonable conversion requested