errors uploading an image to the database

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 ???

What is the datatype of the image column in your database?
Check for the column size also..

Thanks,
Aruna.J

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?

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

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)."

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

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)."

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:

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:
// This is wrong! PreparedStatement ps = dbConn.prepareStatement("insert into image(person_id,image) values("+sid+","+fis+")"); // It should be like this! PreparedStatement ps = dbConn.prepareStatement("insert into image(person_id,image) values(?,?)");

Thankz alot it works

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:
// This is wrong! PreparedStatement ps = dbConn.prepareStatement("insert into image(person_id,image) values("+sid+","+fis+")"); // It should be like this! PreparedStatement ps = dbConn.prepareStatement("insert into image(person_id,image) values(?,?)");

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 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:
// This is wrong! PreparedStatement ps = dbConn.prepareStatement("insert into image(person_id,image) values("+sid+","+fis+")"); // It should be like this! PreparedStatement ps = dbConn.prepareStatement("insert into image(person_id,image) values(?,?)");

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 ???

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

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