SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 Old Forums
 CLOSED - General SQL Server
 Inserting Images as BLOB in SQL server table
 Forum Locked
 Printer Friendly
Author Previous Topic Topic Next Topic  

rpichur
Starting Member

8 Posts

Posted - 02/04/2005 :  20:33:06  Show Profile
I have an urgent requirement for inserting images(tif) of size between 8 to 12 KB.

I am looking for your help in providing required coding that I could use to accomplish this.

I went through a number of SQL Server books, I couldn't find anywhere.

Thanks in advance.

arpp
Yak Posting Veteran

61 Posts

Posted - 02/04/2005 :  20:58:26  Show Profile
If you have an option of using ADO, there is a Stream Reader object you can explore.But general practice is that images are stored on a file server and a pointer is provided in the sql server table.
Go to Top of Page

rpichur
Starting Member

8 Posts

Posted - 02/05/2005 :  09:45:41  Show Profile
Thanks for your idea. I don't have the ADO option.

The images are necessarily to be loaded into the tables directly as BLOB instead of storing themin separate folders and storing the path in the tables.

Any suggestions are most welcome.
Go to Top of Page

robvolk
Most Valuable Yak

USA
15678 Posts

Posted - 02/05/2005 :  10:10:50  Show Profile  Visit robvolk's Homepage
Just out of curiosity, if you can't use ADO to insert them, how exactly are you going to extract them?
Go to Top of Page

rpichur
Starting Member

8 Posts

Posted - 02/05/2005 :  10:49:16  Show Profile
Thanks for your attention.

I started learning to work with SQL server few months back.

Though I have done programming in main frame 10 years back, now my role is managing the data conversion.

With little exposure to MS technology, I assume ADO is not an option, thinking you are meaning ActiveX Data Objects.

Appreciate your help in any way.
Go to Top of Page

robvolk
Most Valuable Yak

USA
15678 Posts

Posted - 02/05/2005 :  10:59:55  Show Profile  Visit robvolk's Homepage
ADO is a standard install on any Windows machine since Windows 2000, and you can always download and install it from here:

http://msdn.microsoft.com/data/Default.aspx

My point is twofold:

1. ADO is really the only easy way to input BLOB data into SQL Server
2. Why would you NOT be able to use ADO for this job?

It would help if you could describe the environment you're working in, and what feature you are trying to support that requires image storage. Storing images in SQL Server is NOT recommended, it is much easier and more flexible to store the images as files, and instead store a link to those files in the database.
Go to Top of Page

rpichur
Starting Member

8 Posts

Posted - 02/05/2005 :  11:35:43  Show Profile
Again thanks for your interest.

My requirement is as follows:

1. I work with Windows XP and I have installed SQL (2000) Enterprise Manager.

2. I have an input data file that has multiple images for a persons. They are in tif format.

3. I have a CD that has these images(sample) with the file names as follows:
30_001.tif
30_2_001.tiff
30_3_001.tif
40_001.tiff
41_001.tiff

Here 30, 40, 41 represents the persons's ID.
You will notice that person with ID=30, has multiple images. While we have to load all images into the table, there is a column in the table as current_flag, that needs to be set to "Y" if there is only one image, and if there are multiple images, the latest image has to be chosen and the current_flag is set to "Y".

We can select the latest either by the image file date, or the highest in the file name. Example 30-3_001.tiff will be the latest here, as per user.

The table has the following columns,minimum:
ID_Image (Int) (PK)
Person_ID (char)
Picture (Image)
dt_created(datetime)
current_flag
***********************
With this info,probably you can get a picture of my working environment and the requirement.

Can you help me with writing the procedure for this, please?
Go to Top of Page

arpp
Yak Posting Veteran

61 Posts

Posted - 02/05/2005 :  23:43:45  Show Profile
Iam not aware of any other way of doing this (I would be interested to know though).All you wanted to do can be done with Stream object.I assume the application that displays the images would be using something like this.You could still use a simple DTS package and create an Active Script step with in that package to do your job.It's only about 6 or 7 statements.
--Read
Set oStream = New ADODB.Stream
oStream.Type = adTypeBinary
oStream.Open
oStream.Write YourRecordset("FieldName").Value
oStream.SaveToFile YourFilePath, adSaveCreateOverWrite

--Write
Set oStream = New ADODB.Stream
oStream.Type = adTypeBinary
oStream.Open
oStream.LoadFromFile YourFilePath
YourRecordset("FieldName").value= oStream.Read
YourRecordset.Update

It's that simple.You can plug this in a DTS.Hope this helps.GL

Edited by - arpp on 02/05/2005 23:52:16
Go to Top of Page

ianyates
Starting Member

Australia
6 Posts

Posted - 02/06/2005 :  07:58:21  Show Profile  Send ianyates an ICQ Message
Look on the web for information regarding the textcopy.exe tool that ships with SQL Server - by default it is at
C:\Program Files\Microsoft SQL Server\MSSQL\Binn\textcopy.exe

An example

textcopy /S localhost /U user /P pass /D dox /T tableWithBlob /C Definition /W "where 1=1" /F RecallQueryDefinition.txt /I

Will use the server localhost, with the username "user", password "pass", database "dox", table "tableWithBlob", column "Definition" locating the row to update using the clause "where 1=1". The file on disk that holds the data is RecallQueryDefinition.text.

The /I says to import from the file to SQL.
If /I is replaced with /O then the reverse occurs.

You could create a long batch file (use some SQL selects to create it for you) to create a file containing a single execution of TextCopy for each file that you need to import.

Very useful utility :)
Go to Top of Page

rpichur
Starting Member

8 Posts

Posted - 02/06/2005 :  09:56:47  Show Profile
Thanks ianyates for your suggestion.

I tried by creating the Stored Procedure,using the textcopy.exe, but got into some problems as explained here:
$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$
First created a SP:

CREATE PROCEDURE sp_imp_exp_images
(@runpath varchar(100),
@srvr varchar(50),
@db varchar(50),
@usr varchar(50),
@pwd varchar(50),
@tbl varchar(50),
@col varchar(50),
@whr varchar(200),
@fil varchar(100),
@mod char(1))
AS
DECLARE @cmd varchar(1000)
SET @cmd = @runpath + ' /S ' + @srvr + ' /D ' + @db + ' /U ' + @usr +
' /P ' + @pwd+ ' /T ' + @tbl + ' /C ' + @col + ' /W ' + @whr +
' /F ' + @fil+ ' /' + @mod
EXEC Master..xp_cmdShell @cmd
GO
CREATE TABLE pic (pic_id int,picture image)
INSERT INTO pic VALUES (1,null)
UPDATE pic SET picture = 'xx'
%%%%%%%%%%%%%%%%%

EXEC sp_imp_exp_images
'C:\textCopy.exe',
'CIN-LT1',
'ENET',
'administrator',
'PassWord',
'pic',
'picture',
'"where pic_id = 1"',
'C:\00000009_001.tif',
'I'
$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$
When I ran the SP,I got these in the result panes:

1. TEXTCOPY Version 1.0
2. DB-Library version 8.00.194
3. DB-Library Error 10004: Unable to connect: SQL Server is unavailable or does not exist. Unable to connect: SQL Server does not exist or network access denied.
4. Operating System Error 51: ConnectionOpen (Connect()).
5. ERROR: Could not connect to SQL Server 'CIN-LT1'
6. NULL

6 rows affected.
%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
I am using my Windows userid & pass word that is the same for my SQL Server running in my computer
************************************************
I got this suggestion from this link:
http://www.planet-source-code.com/vb/scripts/ShowCode.asp?txtCodeId=498&lngWId=5
$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$
I couldn't figure out the problem.

Appreciate your help, "ianyates"

Thanks in advance.
Go to Top of Page

rpichur
Starting Member

8 Posts

Posted - 02/06/2005 :  10:01:06  Show Profile
Hello arpp:

Sorry for the delay in responding to your idea:

As your posting said assuming that the application uses Stream object to retrieve the images, I am sending your suggestion to my technical architect to confirm.

As I don't have any exposure to "Stream object" I will wait for my tech, architect's response.

Thanks again for your reply.

Go to Top of Page

Andraax
Aged Yak Warrior

Sweden
790 Posts

Posted - 02/06/2005 :  17:08:00  Show Profile
If you (or someone you know) have more experience with java, you can use JDBC instead of ADO. It's not much more complicated. A very nice JDBC driver for SQL Server can be found at: http://jtds.sourceforge.net/
Go to Top of Page

ianyates
Starting Member

Australia
6 Posts

Posted - 02/06/2005 :  18:01:41  Show Profile  Send ianyates an ICQ Message
I was wondering why it wouldn't work for you, until you said
quote:
I am using my Windows userid & pass word that is the same for my SQL Server running in my computer



You cannot pass your windows user/pass to SQL server - if it is to authenticate you with windows (ie Trusted) authentication then it does so because it trusts your windows account - you cannot tell it a user/pass.
The user/pass that the textcopy utility requires is a SQL server login.

OR, you can leave out the /U and /P parameters and a trusted connection will be used - ie, the windows account that you are using to run the utility.

For more info, run textcopy /?

Cheers

quote:
Originally posted by rpichur

Thanks ianyates for your suggestion.

I tried by creating the Stored Procedure,using the textcopy.exe, but got into some problems as explained here:
$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$
First created a SP:

CREATE PROCEDURE sp_imp_exp_images
(@runpath varchar(100),
@srvr varchar(50),
@db varchar(50),
@usr varchar(50),
@pwd varchar(50),
@tbl varchar(50),
@col varchar(50),
@whr varchar(200),
@fil varchar(100),
@mod char(1))
AS
DECLARE @cmd varchar(1000)
SET @cmd = @runpath + ' /S ' + @srvr + ' /D ' + @db + ' /U ' + @usr +
' /P ' + @pwd+ ' /T ' + @tbl + ' /C ' + @col + ' /W ' + @whr +
' /F ' + @fil+ ' /' + @mod
EXEC Master..xp_cmdShell @cmd
GO
CREATE TABLE pic (pic_id int,picture image)
INSERT INTO pic VALUES (1,null)
UPDATE pic SET picture = 'xx'
%%%%%%%%%%%%%%%%%

EXEC sp_imp_exp_images
'C:\textCopy.exe',
'CIN-LT1',
'ENET',
'administrator',
'PassWord',
'pic',
'picture',
'"where pic_id = 1"',
'C:\00000009_001.tif',
'I'
$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$
When I ran the SP,I got these in the result panes:

1. TEXTCOPY Version 1.0
2. DB-Library version 8.00.194
3. DB-Library Error 10004: Unable to connect: SQL Server is unavailable or does not exist. Unable to connect: SQL Server does not exist or network access denied.
4. Operating System Error 51: ConnectionOpen (Connect()).
5. ERROR: Could not connect to SQL Server 'CIN-LT1'
6. NULL

6 rows affected.
%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
I am using my Windows userid & pass word that is the same for my SQL Server running in my computer
************************************************
I got this suggestion from this link:
http://www.planet-source-code.com/vb/scripts/ShowCode.asp?txtCodeId=498&lngWId=5
$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$
I couldn't figure out the problem.

Appreciate your help, "ianyates"

Thanks in advance.

Go to Top of Page

rpichur
Starting Member

8 Posts

Posted - 02/07/2005 :  19:10:35  Show Profile


Brief description of the job need to be done in SQL Sever:

1)We have a table named, “Images” that has the following columns.

id_image (PK) (Forced Identity Insert)(int)
image_data (image)
image_file_name (00000049_001.tif)

Except for image_data column all others are populated.

This will be exported to SQL Sever from the image folder in Windows.

2)We have CD (Windows Folder) that has the images, with the following
images:

I have attached the file listing of the CD here.

Directory of C:\d1_sample

08/24/1999 09:10 AM 33,100 00000009_001.tif
08/24/1999 09:10 AM 45,376 00000011_001.tif
08/27/1999 09:40 AM 21,004 00000041_001.tif
08/27/1999 09:40 AM 21,310 00000047_001.tif
04/29/2002 02:43 PM 30,416 00000049-2_001.tif
04/02/2004 11:00 AM 37,882 00000049-3_001.tif
08/27/1999 09:40 AM 21,612 00000049_001.tif
08/27/1999 09:40 AM 20,816 00000115_001.tif
8 File(s) 231,516 bytes

Total Files Listed:
8 File(s) 231,516 bytes
##############################################


Sample Code that I used to load one image that worked successfully:


Create a Store procedure:


CREATE PROCEDURE sp_imp_exp_images
(@runpath varchar(100),
@srvr varchar(50),
@db varchar(50),
@usr varchar(50),
@pwd varchar(50),
@tbl varchar(50),
@col varchar(50),
@whr varchar(200),
@fil varchar(100),
@mod char(1))
AS
DECLARE @cmd varchar(1000)
SET @cmd = @runpath + ' /S ' + @srvr + ' /D ' + @db + ' /U ' + @usr +
' /P ' + @pwd+ ' /T ' + @tbl + ' /C ' + @col + ' /W ' + @whr +
' /F ' + @fil+ ' /' + @mod
EXEC Master..xp_cmdShell @cmd
GO


$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$

I have to execute the above SP by supplying the following parameters:

EXEC sp_imp_exp_images
'C:\textcopy.exe',
'CIN- LT1',
'IENET',
'userid',
'password',
'images',
' image_data',
'"where image_file_name = "00000049_001.tif""',
'C:\d1_sample\00000049_001.tif',
'I'

*******************************************************************

@@ Here I tested this with these parameters and it worked;

The above parameters except in ‘where clause’, I used:
'"where id_image =10"' that corresponded to the above image.

But when I changed the where clause as: '"where image_file_name = "00000049_001.tif""', it gives me error as below:
SQL Server 'CIN-LT1' Message 170: Line 1: Incorrect syntax near '_001'. (Concerning line 1)
%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%


REQUIREMENT:

(1) Compare the image filename “image_file_name” from this table with the corresponding file in the folder and load that image into the table: “Images” in SQL Sever into the column: “image_data”

This inert will happen until all the images in the folder are completed.

PROBLEMS FACED:

1. Do you see an error in the where clause: '"where image_file_name = "00000049_001.tif""', that generates the error as shown above?

2. Assuming this code works with the above where clause, how do I make it work for loading all the images in the folder into the tables. I have populated corresponding rows that match every image file name in the folder.

I greatly appreciate your help.







Go to Top of Page

ianyates
Starting Member

Australia
6 Posts

Posted - 02/08/2005 :  01:47:25  Show Profile  Send ianyates an ICQ Message
I am not sure why the _001 is causing you trouble, but I would suggest that you add a /Z parameter to the textcopy command which will give you debug info.

One of the lines of debug info will look like
debug: Query: select column from table where x=y

I suggest that you copy/paste that query and ensure that it runs in query analyzer.

Actually, I now see why. Your bad where clause looks like
"where image_file_name = "00000049_001.tif""

You will want to make it
"where image_file_name = '00000049_001.tif'"
(Copy and paste into notepad for a nice fixed-width font so you can see the difference in the quotes easily)

SQL was trying to interpret the 00000049_001 as a number because of the bad quoting (the " characters are interpreted by the command shell). Using single quotes (') to delimit your strings will work better and avoids problems with quoted identifiers being set on or off.

Finally, as for your requirement to have it load in all images from a folder, a clever use of xp_cmdshell and the "dir /b" command dumping into a table should do the trick.

For example
quote:

create table #files(filename varchar(256))
insert into #files
exec master..xp_cmdshell 'dir c:\ /b /a-d'

delete from #files where filename is null


select * from #files


drop table #files



Will list just the files (not the folders) in the root of your C: drive with one filename per record in the #files table. Unfortunately you cannot use a @files table variable instead of a #files temporary table, but such is life

You could do some joining on the #files table and your table storing the files and import those filenames in #files that are not in your files table.

Hope this solves your problems!

Ian


quote:
Originally posted by rpichur



Brief description of the job need to be done in SQL Sever:

1)We have a table named, “Images” that has the following columns.

id_image (PK) (Forced Identity Insert)(int)
image_data (image)
image_file_name (00000049_001.tif)

Except for image_data column all others are populated.

This will be exported to SQL Sever from the image folder in Windows.

2)We have CD (Windows Folder) that has the images, with the following
images:

I have attached the file listing of the CD here.

Directory of C:\d1_sample

08/24/1999 09:10 AM 33,100 00000009_001.tif
08/24/1999 09:10 AM 45,376 00000011_001.tif
08/27/1999 09:40 AM 21,004 00000041_001.tif
08/27/1999 09:40 AM 21,310 00000047_001.tif
04/29/2002 02:43 PM 30,416 00000049-2_001.tif
04/02/2004 11:00 AM 37,882 00000049-3_001.tif
08/27/1999 09:40 AM 21,612 00000049_001.tif
08/27/1999 09:40 AM 20,816 00000115_001.tif
8 File(s) 231,516 bytes

Total Files Listed:
8 File(s) 231,516 bytes
##############################################


Sample Code that I used to load one image that worked successfully:


Create a Store procedure:


CREATE PROCEDURE sp_imp_exp_images
(@runpath varchar(100),
@srvr varchar(50),
@db varchar(50),
@usr varchar(50),
@pwd varchar(50),
@tbl varchar(50),
@col varchar(50),
@whr varchar(200),
@fil varchar(100),
@mod char(1))
AS
DECLARE @cmd varchar(1000)
SET @cmd = @runpath + ' /S ' + @srvr + ' /D ' + @db + ' /U ' + @usr +
' /P ' + @pwd+ ' /T ' + @tbl + ' /C ' + @col + ' /W ' + @whr +
' /F ' + @fil+ ' /' + @mod
EXEC Master..xp_cmdShell @cmd
GO


$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$

I have to execute the above SP by supplying the following parameters:

EXEC sp_imp_exp_images
'C:\textcopy.exe',
'CIN- LT1',
'IENET',
'userid',
'password',
'images',
' image_data',
'"where image_file_name = "00000049_001.tif""',
'C:\d1_sample\00000049_001.tif',
'I'

*******************************************************************

@@ Here I tested this with these parameters and it worked;

The above parameters except in ‘where clause’, I used:
'"where id_image =10"' that corresponded to the above image.

But when I changed the where clause as: '"where image_file_name = "00000049_001.tif""', it gives me error as below:
SQL Server 'CIN-LT1' Message 170: Line 1: Incorrect syntax near '_001'. (Concerning line 1)
%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%


REQUIREMENT:

(1) Compare the image filename “image_file_name” from this table with the corresponding file in the folder and load that image into the table: “Images” in SQL Sever into the column: “image_data”

This inert will happen until all the images in the folder are completed.

PROBLEMS FACED:

1. Do you see an error in the where clause: '"where image_file_name = "00000049_001.tif""', that generates the error as shown above?

2. Assuming this code works with the above where clause, how do I make it work for loading all the images in the folder into the tables. I have populated corresponding rows that match every image file name in the folder.

I greatly appreciate your help.









Go to Top of Page

rpichur
Starting Member

8 Posts

Posted - 02/08/2005 :  10:38:33  Show Profile
Thanks for the excellent clue.

When you say insert into #files ,you are assigning a datatype as varchar.

When you dump these images into the table #files, are we actually dumping the images?

use of xp_cmdshell.Is this dumping all the images into the table?

Incidentally all these images will not exceed a size of 12 KB.

Iam not sure about this?

Appreciate your response.
Go to Top of Page
  Previous Topic Topic Next Topic  
 Forum Locked
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.34 seconds. Powered By: Snitz Forums 2000