Not able to insert image more than 4K in Database

[sk mehrotra]

Dear All,
I am using a Servlet to insert an image into oracle database, its working if the image is less then 4KB.

File file =new File(file1);
InputStream is = new FileInputStream(file);
ps =con.prepareStatement("insert into emp_photo VALUES(?,?,?)");
ps.setString(1,name);
ps.setString(2,type);
ps.setBinaryStream(3,is,Integer.MAX_VALUE);

Please Suggest.
Thanks

[Bear Bibeault]

Please take the time to choose the correct forum for your posts. This forum is for questions on Servlets.

For more information, please read this.

This post has been moved to a more appropriate forum.

[Paul Clapham]

Check the setup of the database table. See what the maximum number of bytes allowed in that column is.

[sk mehrotra]

Thanks.

I am using Blob as DataType. Able to insert the large images through PL/SQL, but when trying through servlet, its not. Can you suggest me where i am going wrong.

[Balu Sadhasivam]

Which Oracle version is it ? if PL/SQL works , you can use CallableStatement to call stored procedure and see if that works.

[sk mehrotra]

I am using Oracle 9i., DataType is Blob, Problem is that, not able to insert the images having size more then 4KB.

[Paul Clapham]

Okay. And does Oracle allow you to specify the maximum number of bytes for a Blob column? If so, what is the default value for that maximum number?

[sk mehrotra]

No, it dont allow us to set the size of the blob.
I tried using this but the result is same :

File f = new File(pathoffile);
byte[] bytes = new byte[(int)f.length()];
FileInputStream fs = new FileInputStream(f);
BufferedInputStream bis = new BufferedInputStream(fs);
bis.read();

[sk mehrotra]

Can you give me any idea how to use
BLOB.createTemporary(con, true, BLOB.DURATION_SESSION) to insert an image into database using Java/Servlet.
Thanks.

[Balu Sadhasivam]

 ps =con.prepareStatement("insert into emp_photo VALUES(?,?,?)");
ps.setString(1,name);
ps.setString(2,type);
ps.setBinaryStream(3,is,Integer.MAX_VALUE); 

You are inserting data without specifying column name. I guess the data is inserted to a wrong column (which has 4K limit). add column names to your insert statement to avoid confusion. this is just a thought..

[sk mehrotra]

Thanks, but when I am inserting a file less then 4k, then its inserting into the blob column and when the file is more then 4k the blob column is empty.

[Balu Sadhasivam]

how do you check if its empty ? Use SQL Developer to view the BLOB.

[sk mehrotra]

If the bolb is empty u'll find the (OraBlob) else it will be in upper. i am using Toad to view the rows.

[Balu Sadhasivam]

I m not sure TOAD is right tool to view BLOB type. I couldnt view using TOAD , but could view BLOB data in SQLDeveloper.

if there are no errors during JDBC opertation , may be your data is stored , but you are not able to view it ?

[sk mehrotra]

Yeah Toad is not an appropriate tool to view Blob, but not able to view the Blob throgh SqlDeveloper too. Its just an empty column. Please provide the code you are using to insert the image to database.

[Balu Sadhasivam]

Your code seems fine to insert BLOB type.

[sk mehrotra]

Please Suggest me, what should i do?

[Balu Sadhasivam]

Cant guess why is it not working for data size above 4 KB. are you able to view if image is less than 4KB data alteast ?

[sk mehrotra]

Yes, it can be viewed.

[sk mehrotra]

Is there any alternative to do this?
Please suggest.
Thanks

[Balu Sadhasivam]

Like said earlier , if PL/SQL works, call the stored procedure using CallableStatement and store the BLOB data.

[Paul Sturrock]

Which driver are you using? The drivers that came with Oracle 9i didn't do so well with LOBs.

Also, are you aware that a LOB will only be stored inline up to 4k? After that it will be stored outside the row. Have a read of the Oracle docs on LOB data types for both issues.

[Jan Cumps]

Paul Sturrock wrote:...Also, are you aware that a LOB will only be stored inline up to 4k? After that it will be stored outside the row. Have a read of the Oracle docs on LOB data types for both issues.

Maybe the data is stored (Balu Sadhasivam suggested that too), but your viewers can't show it, because, as Paul says, it is stored differently for +4K data.
Write a java application to fetch the data from the database, and validate if what you get is what you uploaded.

[sk mehrotra]

Using classes12.jar and ojdbc14.jar.
Using thin to connect through the database. Is there any possibility that using thin can create this problem. But when i tried to use Oci it gave the following Error : java.sql.SQLException: Invalid Oracle URL specified.

Secondly I have created a JSp page to read the Blob, but its displaying the images (all images less then 4K).

[Paul Sturrock]

Using classes12.jar and ojdbc14.jar.

No, you are using one or the other not both. I'd recommend using the latest driver you can get rather than relying on the de-supported Oracle 9i drivers.

Secondly I have created a JSp page to read the Blob, but its displaying the images (all images less then 4K).

Not sure what you mean by this, are you saying you can successfully view images over 4K or that you tested it with images under 4K and they are OK?

[sk mehrotra]

I am using one at a time.
while Using OCI driver i am getting the error : java.sql.SQLException: Invalid Oracle URL specified.

Secondly The JSp page is able to show all images that are in the Database and all are less then 4K. There is no Images being saved i.e. greater then 4K.

[sk mehrotra]

One more thing , when i am using the same code to insert the image through my Local system (Java Class), I am able to insert images more then 4K, but when i am using the same through a JSP page and calling a servlet, its not inserting the images more then 4K.

[Balu Sadhasivam]

Is there any error thrown? Are you catching up the errors ? What is the difference in logs (SOP , logs) when using 4k and >4K

[Jan Cumps]

sk mehrotra wrote:One more thing , when i am using the same code to insert the image through my Local system (Java Class), I am able to insert images more then 4K, but when i am using the same through a JSP page and calling a servlet, its not inserting the images more then 4K.

Can you post the relevant code part of both your Java ans JSP/servlet attempts?

[sk mehrotra]

Java and servlet hav the same code that was posted earlier. Just a JSP page that allows to browse a file.
Java Code :

import java.sql.*; 
import java.math.*;
import java.io.*;
import java.awt.*;
import oracle.jdbc.driver.*;

class JdbcTest { 
     
public static void main (String args []) throws SQLException { 
  
    DriverManager.registerDriver (new oracle.jdbc.driver.OracleDriver());
     
   Connection conn = DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:orcl", "test","test");

    PreparedStatement pre=null;  
    
	try{

	File fileimg = new File("C:\\error_screen_test.bmp");
					FileInputStream fis = new FileInputStream(fileimg);
					
					pre = conn.prepareStatement("insert into UPLOAD_test (id, image) values(?,?)");
					
					pre.setString(1, "image");
					pre.setBinaryStream(2,fis,Integer.MAX_VALUE); 
					int i=pre.executeUpdate();
					fis.close();
       
	}
	catch(Exception e)
	{
		System.out.println(e);
	}
         
    pre.close();
    conn.close();
} 
      }

=============================================================================
Servlet:

import java.io.*;
import java.util.*;
import java.net.*;
import java.sql.*;
import javax.servlet.*;
import javax.servlet.http.*;
import com.atishae.fileupload.*;
import oracle.jdbc.driver.*;

public class DisFileUploadSac extends HttpServlet implements SingleThreadModel
{
	PrintWriter out;
 
	public void init(ServletConfig config) throws ServletException
	{
		
		super.init(config);
	}

	public void doPost(HttpServletRequest req, HttpServletResponse res) throws ServletException, IOException
	{
		HttpSession session = req.getSession(true);
		out = res.getWriter();
		
		Properties prop = new Properties();
		File file = new File("./path.properties");
		prop.load(new FileInputStream(file));
		String uploadDirectory = prop.getProperty("DisFileUpload");
		
		String nullDirectory = prop.getProperty("nullDir");
		String connString = prop.getProperty("dbConnectString");
		Calendar cal = new GregorianCalendar();
		int year = cal.get(Calendar.YEAR);
		int month = cal.get(Calendar.MONTH);      
		int day = cal.get(Calendar.DAY_OF_MONTH);
		month++;
		String folderName = day+"-"+month+"-"+year+"/";
		uploadDirectory = uploadDirectory+folderName;
		File folder=new File(uploadDirectory);
		if (!(folder.exists())){
			folder.mkdir();
			File nullDir=new File(nullDirectory);
			String[] nullList=nullDir.list();
			for (int i=0;i<nullList.length;i++){
				FileInputStream inFile= new FileInputStream(nullDirectory+"/"+nullList[i]); 
				FileOutputStream outFile= new FileOutputStream(uploadDirectory+"/"+nullList[i]); 
				int fileByte=0;
				while (fileByte!= -1){
					fileByte=inFile.read();
					outFile.write(fileByte);
				}
				inFile.close();
				outFile.close();
			}
		}

		MultipartRequest multi = null;
		Enumeration files = null;
		Enumeration params = null;
		String orgFileName = null;
		String orgFileExt = null;
		String newFileName = null;
		String moduleName = null;			
		String branchName = null;			
		String paramName = null;
		String paramVal = null;
		File uploadedFile = null;
		File renamedFile = null;

		try
		{
			PreparedStatement pre=null;  
				
			Class.forName("oracle.jdbc.driver.OracleDriver");
			Connection con = DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:ORCL", "test","test");
			
			out.println("><html><title>File Upload Status</title>");
			out.println("<script language=javascript><!-- function closewindow(){self.close();} --></script>");
			try{	  
			multi = new MultipartRequest(req, uploadDirectory, 2048 * 1024);
			
			}catch(Exception e)
			{
				out.println("in error");	
			   out.println(e);
			}	  				
			files = multi.getFileNames();
			params = multi.getParameterNames();
			orgFileName = multi.getFilesystemName((String)files.nextElement());
			orgFileExt = orgFileName.substring(orgFileName.indexOf(".") + 1);
			
			while (params.hasMoreElements())
			{
				paramName = (String)params.nextElement(); 
				paramVal = multi.getParameter(paramName);

				if (paramName.equals("modulename"))
				{
					moduleName = paramVal;
				}
				else if (paramName.equals("filename"))
				{
					newFileName = paramVal;
				}
				else if (paramName.equals("brch"))
				{
					branchName = paramVal;
				}
				
			}
			
			moduleName = moduleName.substring(0,12); 
			uploadedFile = new File(uploadDirectory + orgFileName);
			renamedFile = new File(uploadDirectory + newFileName + "." + orgFileExt);
						
			if (renamedFile.exists())
			{
				out.println("<table border=0 >");
				out.println("File for this Cheque already uploaded!! Current file upload was unsuccessful" + renamedFile);
				out.println("</table>");
				uploadedFile.delete();
				
			}
			else
			{
				uploadedFile.renameTo(renamedFile);	
							
				String FilePathUpld=uploadDirectory + newFileName + "." + orgFileExt;

				String responseStr = IStockUploadDis.insertStockInstructionDis(branchName, moduleName, newFileName+ "." + orgFileExt,orgFileName);
								
				if (!(responseStr.equalsIgnoreCase("Record Insert"))) {
					 renamedFile.delete();
					 out.println("<table border=0 >");
					 out.println("<tr><font ><b>Error Occured in Uploaded File: " + responseStr + "</b></font></tr>");
					 out.println("</table>");
					 responseStr = null;
				}else{
					
					try{		
										  
					File fileimg = new File(FilePathUpld);
					FileInputStream fis = new FileInputStream(fileimg);
					pre = con.prepareStatement("insert into UPLOAD_test (id, image) values(?,?)");
					pre.setString(1,newFileName+ "." + orgFileExt);
					pre.setBinaryStream(2,fis,Integer.MAX_VALUE); 
					int i=pre.executeUpdate();
					
					fis.close();
					}
					catch (Exception a)
					{
					
					out.println(a);
					}
					out.println("<table border=0 >");
					out.println("<tr>Successfully Uploaded File for this DIS Slip. " + responseStr+ "</tr>");	
					out.println("</table>"); 

					responseStr = null;
					
				}
			}
			out.println("<table border=0 >");
			out.println("<p><a >Close window</a></p></table></body></html>");	
						
	   }
	   catch(Exception e)
	   {
		   out.println(e);
	   }	  
	   finally
	   {
			multi = null;
			orgFileName = null;
			orgFileExt = null;
			newFileName = null;
			moduleName = null;			
			branchName = null;
			paramName = null;
			paramVal = null;
			files = null;
			params = null;
			uploadedFile = null;
			renamedFile = null;
						
	   }
	}

	public void doGet(HttpServletRequest req, HttpServletResponse res) throws ServletException, IOException
	{
		doPost(req,res);  
	}

	public void destroy()
	{
		super.destroy();
	}
 }

===============================================================================

JSP:

<form method=POST action="/servlet/DisFileUploadSac" ENCTYPE="multipart/form-data" 
id=form1 name=form1> 
<input type=hidden id=brch name=brch value=<%=request.getParameter("brch")%>>
<input type=hidden id=modulename name=modulename value=<%=request.getParameter("modulename")%>>
<table border="0"    >
 	<tr  >
 	<td  ><Font color = white>Uploaded  File  Path :   </font></td>
 	<td><Input type=file name=path style="width: 210px"></font></td></tr>
 </table>
<br><br>
<center>
<input type=hidden id=filename name=filename value=<%=request.getParameter("filename")%>>
<input type="submit" value="Upload File" name="insert"	id="insert" onclick="return showaler()">
</center>
<p><font color="red" ><b>
</form>

[Jhakda Velu]

Hi
It is surprising that the standalone code is able to insert but the servlet code isn't. I suggest you first insert the mandatory fields(non blob), then get the blob by querying the record you entered and update it. It *may* work. I did that for a clob.

 
 pStmt1.setInt(1, sequence);
 pStmt1.execute();
// Now get the same record
 pStmt1 = conn.prepareStatement("Query to get the clob/blob");
 pStmt1.setInt(1, sequence);
 rs= pStmt1.executeQuery();
 while (rs.next()){
	Clob tempClob = rs.getClob(7);//index of the clob column was 7 for me
	((oracle.sql.CLOB)tempClob).putString(1, "Data"); 
   }			        
   

Jhakda

[Geneson Subramaniam]

Hi All,

I'm trying to find a GUI tool that connects to Derby in embedded mode. Can Eclipse is capable of this? If so,
where can I start??

TQ

[Paul Sturrock]

Jhakda Velu wrote:Hi
It is surprising that the standalone code is able to insert but the servlet code isn't. I suggest you first insert the mandatory fields(non blob), then get the blob by querying the record you entered and update it. It *may* work. I did that for a clob.

 
 pStmt1.setInt(1, sequence);
 pStmt1.execute();
// Now get the same record
 pStmt1 = conn.prepareStatement("Query to get the clob/blob");
 pStmt1.setInt(1, sequence);
 rs= pStmt1.executeQuery();
 while (rs.next()){
	Clob tempClob = rs.getClob(7);//index of the clob column was 7 for me
	((oracle.sql.CLOB)tempClob).putString(1, "Data"); 
   }			        
   

Jhakda

This shouldn't be needed. My guess as to why the behaviour is different would be that the JSP/Servelt application is using a differernt version of the JDBC driver. There is no difference between using JDBC code from a stand alone applciation and from a web application other than the configuation of the JDBC driver and data source.

[Paul Sturrock]

Geneson Subramaniam wrote:Hi All,

I'm trying to find a GUI tool that connects to Derby in embedded mode. Can Eclipse is capable of this? If so,
where can I start??

TQ

Please don't use someone elses topic to ask you question. Please start your own thread for this discussion.

[sk mehrotra]

Have compiled the java code in the same environment used for Servlets.

[Paul Sturrock]

sk mehrotra wrote:Have compiled the java code in the same environment used for Servlets.

OK. The driver jar files are not important at compile time, only runtime. What driver files are on your classpaths?

[Jan Cumps]

sk mehrotra wrote:Have compiled the java code in the same environment used for Servlets.

But have you used the same jdbc jar at runtime?

[Jan Cumps]

You type 10 seconds faster.

[sk mehrotra]

Using the same JDBC jar at the runtime.

[Paul Sturrock]

sk mehrotra wrote:Using the same JDBC jar at the runtime.

This seems unlikely. The same code running in a web application or as a stand alone application with the same JDBC driver will behave the same. If it isn't then its not the JDBC code that is the problem. What size are the files you upload?