Insert photos into SQL database using java

[dragon_baby]

Hello!

I am struggling to insert pictures into "Books" table in column "photo" from a SQL server database, using the following code:

package insertPhotos;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;

import persistency.Database;

public class Pictures {
	static String location="xxxxxxxxx";
	static String password="xxx";
	static String username="xxx";
	
	public static void main(String[] args) throws Exception, IOException, SQLException {
		
		
		Connection con = DriverManager.getConnection(location,username,password);
		con.setAutoCommit(false);
		String INSERT_PICTURE="INSERT INTO Books(ISBN, photo) values(?,?)";
		
		FileInputStream fis = null;
		PreparedStatement ps = null;
		try
		{
			con.setAutoCommit(false);
			File file = new File("cien_anos_de_soledad.jpg");
			fis = new FileInputStream(file);
			ps = con.prepareStatement(INSERT_PICTURE);
			ps.setString(1, "002356");
			ps.setBinaryStream(2, fis, (int) file.length());
			ps.executeUpdate();
			con.commit();
		}
		finally
		{
			ps.close();
			fis.close();
		}
	}

}

The following error pops out: "Exception in thread "main" java.lang.NullPointerException
at insertPhotos.Pictures.main(Pictures.java:40)"

I really don't understand what is wrong, as I am new to java programming. Any suggestions?

Thank you :)

[Fubarable]

The code line number in your posted code may not correspond to the line number in your actual code. Please clarify for us which line is causing your exception.

[dragon_baby]

It is the same line - ps.close(); as I copy pasted all.

[Fubarable]

It is the same line - ps.close(); as I copy pasted all.

OK, cool. I think that an easy fix is to check that it's not null before closing. So consider changing it to:

finally
{
  if (ps != null) {
    ps.close();
  }
  if (fis != null) {
    fis.close();
  }
}

[dragon_baby]

Well...again I am getting a more huge error:

Exception in thread "main" com.microsoft.sqlserver.jdbc.SQLServerException: Violation of PRIMARY KEY constraint 'PK_Books'. Cannot insert duplicate key in object 'dbo.Books'.
at com.microsoft.sqlserver.jdbc.SQLServerException.ma keFromDatabaseError(SQLServerException.java:216)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.ge tNextResult(SQLServerStatement.java:1515)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStat ement.doExecutePreparedStatement(SQLServerPrepared Statement.java:404)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStat ement$PrepStmtExecCmd.doExecute(SQLServerPreparedS tatement.java:350)
at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IO Buffer.java:5696)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.e xecuteCommand(SQLServerConnection.java:1715)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.ex ecuteCommand(SQLServerStatement.java:180)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.ex ecuteStatement(SQLServerStatement.java:155)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStat ement.executeUpdate(SQLServerPreparedStatement.jav a:314)
at insertPhotos.Pictures.main(Pictures.java:35)

Line 35 is ps.executeUpdate();

I also changed this line File file = new File("D:\\Java work\\Ultimate_project_library\\src\\cien_anos_de_ soledad.jpg");

:( :( :(

[dragon_baby]

I managed to solve it, the problem was an incorrect SQL query. This line:

String INSERT_PICTURE="UPDATE Books SET photo=? WHERE ISBN=?";