LinuxQuestions.org
Visit the LQ Articles and Editorials section
Go Back   LinuxQuestions.org > Forums > Non-*NIX Forums > Programming
User Name
Password
Programming This forum is for all programming questions.
The question does not have to be directly related to Linux and any language is fair game.

Notices

Reply
 
Search this Thread
Old 02-28-2006, 11:24 AM   #1
MRMadhav
Member
 
Registered: Nov 2005
Location: Mauritius
Distribution: PCQLinux, SUSE Linux,Fedora Core 5, Fedora Core 6, Knoppix Live, Kubuntu Edgy, PCLinuxOS
Posts: 167

Rep: Reputation: 30
Question Storing Images in MySQL


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 !
 
Old 02-28-2006, 03:05 PM   #2
airswit
Member
 
Registered: Dec 2005
Distribution: Fedora 4
Posts: 89

Rep: Reputation: 15
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
 
Old 02-28-2006, 03:14 PM   #3
MRMadhav
Member
 
Registered: Nov 2005
Location: Mauritius
Distribution: PCQLinux, SUSE Linux,Fedora Core 5, Fedora Core 6, Knoppix Live, Kubuntu Edgy, PCLinuxOS
Posts: 167

Original Poster
Rep: Reputation: 30
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!!!!
 
Old 03-01-2006, 12:42 AM   #4
MRMadhav
Member
 
Registered: Nov 2005
Location: Mauritius
Distribution: PCQLinux, SUSE Linux,Fedora Core 5, Fedora Core 6, Knoppix Live, Kubuntu Edgy, PCLinuxOS
Posts: 167

Original Poster
Rep: Reputation: 30
Haven't anyone tried to store an image in the MySQL??? A MySQL Command would do!
 
Old 03-01-2006, 01:46 AM   #5
paulsm4
Guru
 
Registered: Mar 2004
Distribution: SusE 8.2
Posts: 5,863
Blog Entries: 1

Rep: Reputation: Disabled
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    |                |
+----------------+------------------+------+-----+---------+----------------+
 
Old 03-01-2006, 02:10 AM   #6
MRMadhav
Member
 
Registered: Nov 2005
Location: Mauritius
Distribution: PCQLinux, SUSE Linux,Fedora Core 5, Fedora Core 6, Knoppix Live, Kubuntu Edgy, PCLinuxOS
Posts: 167

Original Poster
Rep: Reputation: 30
I know about the saving of location! but my problem is that i want the image in the database itself!
 
Old 03-01-2006, 02:54 AM   #7
k^7
LQ Newbie
 
Registered: Feb 2006
Location: Bucharest, Romania
Distribution: OpenSUSE 10
Posts: 10

Rep: Reputation: 0
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

Last edited by k^7; 03-01-2006 at 03:01 AM.
 
Old 03-01-2006, 05:11 AM   #8
MRMadhav
Member
 
Registered: Nov 2005
Location: Mauritius
Distribution: PCQLinux, SUSE Linux,Fedora Core 5, Fedora Core 6, Knoppix Live, Kubuntu Edgy, PCLinuxOS
Posts: 167

Original Poster
Rep: Reputation: 30
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?
 
Old 03-01-2006, 05:29 AM   #9
k^7
LQ Newbie
 
Registered: Feb 2006
Location: Bucharest, Romania
Distribution: OpenSUSE 10
Posts: 10

Rep: Reputation: 0
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.
 
Old 03-01-2006, 06:19 AM   #10
MRMadhav
Member
 
Registered: Nov 2005
Location: Mauritius
Distribution: PCQLinux, SUSE Linux,Fedora Core 5, Fedora Core 6, Knoppix Live, Kubuntu Edgy, PCLinuxOS
Posts: 167

Original Poster
Rep: Reputation: 30
Lightbulb

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();
	}
}
 
Old 03-01-2006, 06:23 AM   #11
k^7
LQ Newbie
 
Registered: Feb 2006
Location: Bucharest, Romania
Distribution: OpenSUSE 10
Posts: 10

Rep: Reputation: 0
You're welcome!

PS: You should put the code for inserting a picture in mysql, just as a future reference.
 
Old 03-01-2006, 06:49 AM   #12
MRMadhav
Member
 
Registered: Nov 2005
Location: Mauritius
Distribution: PCQLinux, SUSE Linux,Fedora Core 5, Fedora Core 6, Knoppix Live, Kubuntu Edgy, PCLinuxOS
Posts: 167

Original Poster
Rep: Reputation: 30
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
 
Old 03-01-2006, 11:41 AM   #13
johnMG
Member
 
Registered: Jul 2003
Location: CT, USA
Distribution: Debian Sarge (server), Etch (work/home)
Posts: 601

Rep: Reputation: 31
paulsm4, is that image_location value a URL or a fully-qualified pathname like image_user@image_server:/data/images ?
 
Old 03-01-2006, 12:06 PM   #14
paulsm4
Guru
 
Registered: Mar 2004
Distribution: SusE 8.2
Posts: 5,863
Blog Entries: 1

Rep: Reputation: Disabled
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.
 
Old 03-01-2006, 12:13 PM   #15
johnMG
Member
 
Registered: Jul 2003
Location: CT, USA
Distribution: Debian Sarge (server), Etch (work/home)
Posts: 601

Rep: Reputation: 31
Thanks Paul.
 
  


Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
mysql error Can't connect to local MySQL server through socket '/var/lib/mysql/mysql. Dannux Linux - Software 3 03-24-2006 09:44 AM
how to import images into mysql x2000koh Programming 6 02-03-2005 01:16 PM
Storing Strings ToothlessRebel Programming 4 09-05-2004 04:16 AM
PHP and MySQL (Images) Jayantha Linux - Newbie 1 05-20-2004 04:45 AM
storing multiple values within one field in mysql antken Programming 8 12-15-2002 11:08 PM


All times are GMT -5. The time now is 01:28 AM.

Main Menu
Advertisement
My LQ
Write for LQ
LinuxQuestions.org is looking for people interested in writing Editorials, Articles, Reviews, and more. If you'd like to contribute content, let us know.
Main Menu
Syndicate
RSS1  Latest Threads
RSS1  LQ News
Twitter: @linuxquestions
identi.ca: @linuxquestions
Facebook: linuxquestions Google+: linuxquestions
Open Source Consulting | Domain Registration