Difference between Normal Insert and BLOB insert

I have sql database with table Images, in that table there are 2 Columns ID (identity), DisplayImage (image). Table has 2 rows for first row i have written

insert into Image values(1,'Libraries\Pictures\Lotus.jpg') and 2nd row as

INSERT INTO [dbo].[Image] ([Id],Images) SELECT 2,
(select * FROM OPENROWSET(BULK 'C:\Users\Public\Pictures\Sample Pictures\Tulips.jpg',
SINGLE_BLOB) AS BLOB) Here is the code for reading the image
WebClient instanceHTTP = new WebClient();
Uri MyUri = new Uri("http://localhost:52293/WebSite/ImageHandler.ashx?ImageId=" + TextBox5.Text);
//TextBox5.Text for id i.e 1 or 2 
Stream returnValue;
returnValue = instanceHTTP.OpenRead(MyUri);
**System.Drawing.Image MyImage = System.Drawing.Image.FromStream(returnValue);**
// Error if id=1 used i.e normal insert bytearray = imageToByteArray(MyImage); 

MyImage.Dispose();

if i use id 1 that is normal insert than it gives error that invalid Parmeter but if i use id 2 it runs properly. So what is the difference?? and what changes i have to make in order to runn normal insert image.. I dont want to use AS BLOB in insert statement

View Answers

September 7, 2012 at 5:41 PM

If you want to store the image into database, you have to use blob type. The other datatype will not store the image into database. There are different data types for different fields, varchar for string, bigint for integer, blob for image etc.If you will access the image with other datatypes, it will give nvalid parameter error.

Here is a code that insert image into database:

 import java.sql.*;
 import java.io.*;
class  InsertDataAndImage
{
    public static void main(String[] args) 
    {

    FileInputStream fis;
    String url="jdbc:mysql://localhost:3306/test";
   try{
     Class.forName("com.mysql.jdbc.Driver").newInstance();
     Connection con=DriverManager.getConnection(url,"root","root");
     File image=new File("c:/image1.jpg");
     PreparedStatement psmt=con.prepareStatement("insert into data(name,city,image)"+"values(?,?,?)");
     psmt.setString(1,"John");
     psmt.setString(2,"Austin");
     fis=new FileInputStream(image);
     psmt.setBinaryStream(3, (InputStream)fis, (int)(image.length()));
     int s = psmt.executeUpdate();
     if(s>0) {
     System.out.println("Inserted Successfully!");
     }
     con.close();
     psmt.close();
     }
     catch(Exception ex){
     System.out.println(ex);
     }
  }
}

Here is a code that retrieves image from database.

import java.sql.*;
import java.awt.*;
import javax.swing.*;

public class RetrieveImage {
public static void main(String argv[]){
try {
Class.forName("com.mysql.jdbc.Driver").newInstance();
Connection con = DriverManager.getConnection(
"jdbc:mysql://localhost:3306/test", "root", "root");
Statement stmt = con.createStatement();
ResultSet rs = stmt
.executeQuery("select image from data where id='1'");
byte[] bytes = null;
if (rs.next()) {
bytes = rs.getBytes(1);
}
rs.close();
stmt.close();
con.close();
if (bytes != null) {
JFrame f = new JFrame();
f.setTitle("Display Image From database");
Image image = f.getToolkit().createImage(bytes);
ImageIcon icon=new ImageIcon(image);
JLabel lab=new JLabel();
lab.setIcon(icon);
f.getContentPane().add(lab);
f.setSize(400, 300);
f.setVisible(true);
}
}
catch (Exception e){
}
}
}

September 10, 2012 at 4:04 PM

I am working with C#