Share on Google+Share on Google+

Jignesh
Difference between Normal Insert and BLOB insert
2 Answer(s)      4 years and 7 months ago
Posted in : SQL

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

Ads

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#










Related Tutorials/Questions & Answers:
Difference between Normal Insert and BLOB insert
Difference between Normal Insert and BLOB insert  I have sql database... normal insert image.. I dont want to use AS BLOB in insert statement   ... = System.Drawing.Image.FromStream(returnValue);** // Error if id=1 used i.e normal insert bytearray
Insert Mysql Blob data
Insert Mysql Blob data   In this section, we will discuss about how to insert a blob data into a database table. A Blob stores a binary large object in the database table's row. Blob object contains a logical pointer
Advertisements
insert
insert  insert data in database from servlet through JDBC   Ho Friend, Please visit the following: Insert data into database Thanks
Insert Blob(Image) in Mysql table using JSP
Insert Blob(Image) in Mysql table using JSP In this Section, we will insert blob data(image) in Mysql database table using JSP code. A Blob stores a binary large object in the database table's row. Blob object contains a logical
Insert and Delete an element in between an array
Insert and Delete an element in between an array In this section, you will learn how to insert and delete an element in between an array. For this purpose, we... a new array. Following code insert an element in an array: public int
difference between prepared statement and statement
difference between prepared statement and statement  i mean...(?,?) but in normal statement we write insert into tablename(jtextfiled1.gettext(),jtextfield2.getText()); whats the difference between these two and which is more
DB Insert
DB Insert  How to insert XML data into a database column? Column data type is CLOB
jdbc insert
jdbc insert  Hi , i want to insert a declared integer variable into a mysql table through jdbc. how to insert that. help me with query... thanks... a table there. After creating a table in database, it insert a rows in the database
Mysql Insert
Mysql Insert       Mysql Insert is used to insert the records or rows to the table. Understand with Example The Tutorial illustrate an example from 'Mysql Insert'.To grasp
insert data
insert data  i've got a problem to insert my data to database.i can upload my multipart file but not text data.Please help me to solve this .Attached...("insert into images values(?,?,?,?,?)"); st.setString(2
Insert image in database
Insert image in database  Want a jsp-struts program in which the imageFile is in the form of getter and setter and inserted in the database as a Blob using . and then download the image and show on next jsp pg. No use
difference between == and === operators?
difference between == and === operators?  Is (====) operator available in java or not? difference between
difference between ForwardAction and IncludeAction
difference between ForwardAction and IncludeAction  What is the difference between ForwardAction and IncludeAction
Difference between DispatchAction and LookupDispatchAction
Difference between DispatchAction and LookupDispatchAction  What is the Difference between DispatchAction and LookupDispatchAction
Difference between struts and JSF
Difference between struts and JSF  What is the difference between struts and JSF
difference between SessionState and ViewState
difference between SessionState and ViewState  What is the difference between SessionState and ViewState
The INSERT INTO Statement, SQL Tutorial
The INSERT INTO Statement       The INSERT INTO statement is used to insert or add a record of data into the table. To insert records into a table, just write the key word
Difference between http and https
Difference between http and https  Difference b/w HTTP and HTTPS... in order to prevent unauthorized access. 2) HTTP transmits normal data where as HTTPS transmits closed or encrypted data. 3) HTTP is for normal applications
What is the difference between the >> and >>> operators?
What is the difference between the >> and >>> operators?   hi, What is the difference between the >> and >>> operators? Thanks
Difference between 3d and 4d
Difference between 3d and 4d  what is the difference between 3d and 4d
difference between varchar & varchar2?
difference between varchar & varchar2?  What is the difference between varchar & varchar2
Deleting Blob Data from Database table using servlet
Deleting Blob Data from Database table using servlet In this section, we will discuss about how to insert a blob data into a database table. A Blob stores a binary large object in the database table's row. Blob object
insert code jsp to access
insert code jsp to access   insert code jsp to access
Spring 3.2 MVC insert and retrieve blob from the database
In this section, you will learn about inserting and retrieving blob from the database
Difference between Timer and Thread?
Difference between Timer and Thread?  Can anyone tell me about the difference between Timer and Thread, Why we need to have Timer in case we have Thread implimentation startegy in Java
Difference between Mysql and SQL
Difference between Mysql and SQL  hello, What is the difference between Mysql and SQL??   hii, SQL is structural quary language but mysql is database package
The INSERT INTO Statement
The INSERT INTO Statement       The INSERT INTO statement is used to insert or add a record of data into the table. To insert records into a table, just write the key word
The INSERT INTO Statements in SQL?
The INSERT INTO Statements in SQL?  The INSERT INTO Statements in SQL?   Hi, The INSERT INTO statement is used to insert a new row or multiple rows into a table. SQL INSERT INTO Syntax INSERT INTO table_name VALUES
insert multiple selection - Java
insert multiple selection - Java  how to insert multiple selection values from html into database using servlets
insert query in jsp
insert query in jsp  give me insert code in jsp at run time
single query to insert the data
single query to insert the data  How to insert data for all HTML fields in single MYSQL query
Tutorials   
Java Spring Hibernate Struts Training How to Fetch Data using dropdown option How to get day from date in SQL? Example of sorting results in MySQL Distinct and Limit example in MySQL How to download install and use MySQL workbench? Example of Average, Sum, Min and Max functions in MySQL How to delete data from MySQL? Update SQL Query Example Order by example in MySQL AND and OR Operators in MySQL Query Where clause with query in MySQL How to use select Statement in MySQL? How to insert data into MySQL Table? Create Database and tables in MySQL example of before insert trigger in mysql Failed to Call Procedure MY Sql Query Error - Not able to Understand the issue which query will be run for previous(button) question How to access data yearly from DB in C# ? Fastest way to upload huge raw data in database - c#, Java or stored procedure DB Insert Nb hours between 2 time stamps meets specific criteria mysql select into table from another table example MySQLselect into new table Example code How to create a .mdf file from script (.sql) Please help me out fixing broken relational links in MySQL database after an upgrade How do I upgrade mysql? mysql 4.x to 5.x upgrade problem Upgrading a PHP/MYSQL application? upgrading sql query join phpmyadmin in whm whm php version update php cpanel cpanel mysql downgrade cpanel mysql version upgrade mysql whm upgrade mysql 4 5 cpanel upgrade mysql cpanel server Did my upgrade from mySQL 5.1 to 5.5 work Upgrade to MySQL 5.5 ? - cPanel Mem use upgrade from MySQL 5.1 to 5.5 - cPanel Upgrading MySQL from 5.0 to 5.1 or 5.5 - cPanel Mysql Upgrade on WHM (Mysql 5.1 to 5.5)cPanel Mysql Upgrade on WHM (Mysql 5.1 to 5.5) - cPanel Updating from MySQL 5.0 to 5.5 cPanel MySQL 5.1 upgrade to 5.5 error Charset problem upon publishing PHP + MySQL website MySQL issue: Table 'data_dictionary.CHARACTER_SETS' doesn't exist SQL Error Version 5.5.15

Ads

 
Advertisement null

Ads