Storing Images in MySQL

MRMadhav · 02-28-2006, 10:24 AM

Hi Guys!

I wanted to Know any MySQL command that I can use to insert an image file as it is in a certain field in a database. I've heard that the blob field can store images but i don't know how to add the images and to retrieve them! All the codes I've seen up to now are in PHP which I don't understand!! I am using Java to serve as platform to view and save the images!. Any help would be greatly appreciated

!

airswit · 02-28-2006, 02:05 PM

the best way is just to store the location of the file in a varchar or text or something like that, and then just retrieve it with whatever language you are using

MRMadhav · 02-28-2006, 02:14 PM

Yeah I know about that but thats not possible with the app am just doing. In this app, there are multiple computers each having a database for them. The data for each computer are then centralised to a server in another place and then redistributed. Its a sort of Distributed System. This the method of keeping path that process becomes very lenthly and I think that saving the images in the database itself is the best way! So if anyone could tell me how that can be done it would be just great! I know it can be done as I've seen it being done and I've also seen it in the MySQL Documentation... Its just that I Don't know how to do that!! Help Please!!!!

MRMadhav · 02-28-2006, 11:42 PM

Haven't anyone tried to store an image in the MySQL??? A MySQL Command would do!

paulsm4 · 03-01-2006, 12:46 AM

For whatever it's worth, we store images - and we do the same thing airswit recommended. Here's an example:

Code:

mysql> describe images;
+----------------+------------------+------+-----+---------+----------------+
| Field          | Type             | Null | Key | Default | Extra          |
+----------------+------------------+------+-----+---------+----------------+
| image_id       | int(10) unsigned |      | PRI | NULL    | auto_increment |
| image_location | varchar(50)      | YES  |     | NULL    |                |
| image_alt      | varchar(255)     | YES  |     | NULL    |                |
+----------------+------------------+------+-----+---------+----------------+

MRMadhav · 03-01-2006, 01:10 AM

I know about the saving of location! but my problem is that i want the image in the database itself!

k^7 · 03-01-2006, 01:54 AM

I hope the links will help you.

http://forum.java.sun.com/thread.jsp...7166&tstart=30
http://forum.java.sun.com/thread.jspa?messageID=4069690
http://www.experts-exchange.com/Data..._21743389.html

PS: for PHP here is a great tutorial: http://www.php-mysql-tutorial.com/php-mysql-upload.php

MRMadhav · 03-01-2006, 04:11 AM

Thanks! I found out how to save the images! But now i am totally lost at displaying thos saved images in the JFrame. How can i do that?

k^7 · 03-01-2006, 04:29 AM

After searching a bit with google, here is what i found:

Link 1
Link 2

Link 2 is connected to google cache so you can see the article without registering on that site.

MRMadhav · 03-01-2006, 05:19 AM

Thanks! But these are a bit complicated. I've finally done it! Its a Simple Code:

Code:

import java.awt.*;
import javax.swing.*;
import java.sql.*;
import java.io.*;
import java.awt.image.*;
import javax.imageio.*;

public class IMAGETEST extends JFrame
{
	public IMAGETEST()
	{
		try
		{
                  //Put your connection details here
                  //Displaying the Picture
			String query = "select Image from testdb.images where ID ='10'";
			ResultSet rs = Connection.createStatement.executeQuery(query);
			rs.next();
			byte[] bytearray = new byte[4096];
			int size=0;
			InputStream sImage;
			sImage = rs.getBinaryStream(1);
			Image image = null;
			try {
				image = ImageIO.read(sImage);
			} catch (IOException e) {System.out.println(e);}
			JLabel label = new JLabel(new ImageIcon(image));
			getContentPane().add(label, BorderLayout.CENTER);
			pack();
			setVisible(true);
			sImage.close();
                  //Updating the picture with a new one
			PreparedStatement stmt = Connection.prepareStatement("update testdb.images set image = ? where id = 10");
			File f = new File("08.jpg");
			FileInputStream fis = new FileInputStream(f);
			stmt.setBinaryStream(1,fis,(int)f.length());
			System.out.println("photo length = " + f.length());
			System.out.println("no of rows effected "+stmt.executeUpdate());

		}catch(Exception ex){System.out.println(ex);}
	}
	public static void main(String args[])
	{
		IMAGETEST im = new IMAGETEST();
	}
}

k^7 · 03-01-2006, 05:23 AM

You're welcome!

PS: You should put the code for inserting a picture in mysql, just as a future reference.

MRMadhav · 03-01-2006, 05:49 AM

for inserting you just have to replace the paragraph for updating th picture with this:

PreparedStatement stmt = Connection.prepareStatement("insert into testdb.images values(2,?)");
File f = new File("pic.jpg");
FileInputStream fis = new FileInputStream(f);
stmt.setBinaryStream(2,fis,(int)f.length());
System.out.println("photo length = " + f.length());
System.out.println("no of rows effected "+stmt.executeUpdate());

//the 2 in values is the key field value and the ? represent the image

johnMG · 03-01-2006, 10:41 AM

paulsm4, is that image_location value a URL or a fully-qualified pathname like image_user@image_server:/data/images ?

paulsm4 · 03-01-2006, 11:06 AM

johnMG -

It's whatever you want it to be. In our case, we're:
a) The script is in PHP
b) It generates a random number between 1 and [max image id]
c) It does a PHP/mySql "select image_location,alt_image where image_id=id;"
d) It writes out "<img src=\"$image_location\" alt=\"$alt_image />

So in our case, it's a path relative to the web root.

johnMG · 03-01-2006, 11:13 AM

Thanks Paul.