Important alert: (current site time 4/19/2014 5:48:19 PM EDT)
 

VB icon

Procedure to Export/Import Images In/From SQL SERVER

Email
Submitted on: 6/27/2002 7:16:46 AM
By: Eli Leiba  
Level: Advanced
User Rating: By 9 Users
Compatibility: SQL Server 7.0
Views: 53411
author picture
(About the author)
 
     the procedure is used as an Interface to the TextCopy Utility supplied with SQL SERVER in order to simplify handling of BLOBS (images/docs)
 
code:
Can't Copy and Paste this?
Click here for a copy-and-paste friendly version of this code!
 
Terms of Agreement:   
By using this code, you agree to the following terms...   
  1. You may use this code in your own programs (and may compile it into a program and distribute it in compiled format for languages that allow it) freely and with no charge.
  2. You MAY NOT redistribute this code (for example to a web site) without written permission from the original author. Failure to do so is a violation of copyright laws.   
  3. You may link to this code from another website, but ONLY if it is not wrapped in a frame. 
  4. You will abide by any additional copyright restrictions which the author may have placed in the code or code's description.
				
--**************************************
-- Name: Procedure to Export/Import Images In/From SQL SERVER
-- Description:the procedure is used as an Interface to the TextCopy Utility supplied with SQL SERVER in order to simplify handling of BLOBS (images/docs)
-- By: Eli Leiba
--
-- Inputs:@runpath varchar(100), -- textCopy Location
@srvrvarchar(50), -- server to load
@db varchar(50), -- database to load
@usrvarchar(50), -- login user
@pwdvarchar(50), -- login password
@tblvarchar(50), -- table to load/unload
@colvarchar(50), -- column to load/unload
@whrvarchar(200), -- where clause
@filvarchar(100), -- filename including path
@modchar(1) -- I for Load into Sql
--
-- Assumes:-- usage (assuming TextCopy.exe is in c:\)
create table pic (pic_id int,picture image)
insert into pic values (1,null)
update pic set picture = 'xx'
-- Inserting image
EXEC sp_imp_exp_images 'c:\textCopy.exe', 
 'PCN1943',
'PUBS',
'sa',
'sa',
'pic',
'picture',
'"where pic_id = 1"',
'c:\pic.jpg',
'I'
-- Extracting Image 
EXEC sp_imp_exp_images 'c:\textCopy.exe', 
 'PCN1943',
'PUBS',
'sa',
'sa',
'pic',
'picture',
'"where pic_id = 1"',
'D:\pic.jpg',
'O'
--
-- Side Effects:Import/Export of BLOBS
--
--This code is copyrighted and has-- limited warranties.Please see http://www.Planet-Source-Code.com/vb/scripts/ShowCode.asp?txtCodeId=498&lngWId=5--for details.--**************************************

Create Procedure sp_imp_exp_images
(@runpath varchar(100), -- textCopy Location
 @srvrvarchar(50), -- server to load
 @db varchar(50), -- database to load
 @usrvarchar(50), -- login user
 @pwdvarchar(50), -- login password
 @tblvarchar(50), -- table to load/unload
 @colvarchar(50), -- column to load/unload
 @whrvarchar(200), -- where clause
 @filvarchar(100), -- filename including path
 @modchar(1)) -- I for Load into Sql , O FOR output from SQL
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


Other 20 submission(s) by this author

 


Report Bad Submission
Use this form to tell us if this entry should be deleted (i.e contains no code, is a virus, etc.).
This submission should be removed because:

Your Vote

What do you think of this code (in the Advanced category)?
(The code with your highest vote will win this month's coding contest!)
Excellent  Good  Average  Below Average  Poor (See voting log ...)
 

Other User Comments

2/6/2003 2:48:16 PM

How to insert images into SQL 7.0 using VB?
mailto:joluzaga@hotmail.com

(If this comment was disrespectful, please report it.)

 
5/27/2003 5:46:36 PM

What about Windows Authentication mode. I suppose the parms should default to NULL. But, has anyone had any luck using TextCopy in a Windows Auth mode server?
(If this comment was disrespectful, please report it.)

 
9/23/2003 3:41:29 PM

This is realy Good!
How to insert images into SQL 7.0 using
VB?
mailto:megasoftcare@yahoo.com

(If this comment was disrespectful, please report it.)

 
12/5/2004 10:28:24 PM

Realy Good codes !
But, How to insert
images into SQL 7.0 or SQL 2000 using
VB?
mailto:dicky_mkb@yahoo.com


(If this comment was disrespectful, please report it.)

 
2/8/2005 4:36:08 PM

This is really good. And the images display well in reporting services. When some apps save images in SQL they will not display in Reporting services.
(If this comment was disrespectful, please report it.)

 
3/7/2005 12:18:46 PM

Hi
I am trying to excute this stored procedure from VB6 but I not getting any out put.

pls check below code. Pls note stored procedure is wroking fine when I am running through SQL Query Ananlyzer.
----
Private Sub Command1_Click()
Set rs = ExecuteSP(
(If this comment was disrespectful, please report it.)

 
3/7/2005 12:20:11 PM

hi,

how to use above stored procedure with Visual Basic6

please send me vb6 code
thanks
kailas
(If this comment was disrespectful, please report it.)

 
3/7/2005 12:21:35 PM

please send me code to excute above stored procedure from VB6
on hismartkk@yahoo.com
thanks
(If this comment was disrespectful, please report it.)

 
6/26/2005 5:50:24 AMSiraj Jameel

I'm receiving a message
(If this comment was disrespectful, please report it.)

 
6/26/2005 5:54:31 AMSiraj Jameel

why the follwing message appears when executing from query analyser Msg 50001, Level 1, State 50001
xpsql.cpp: Error 87 from GetProxyAccount on line 604

(If this comment was disrespectful, please report it.)

 
6/26/2005 5:56:22 AMSiraj Jameel

send me your reply for the above comment to siraj.jameel@saudi-ericsson.com
Thanks & Regards
(If this comment was disrespectful, please report it.)

 
1/12/2006 10:47:18 AMSahib

Excellent from me.Also tell me if u know any code to store images in Oracle 8.0 from Visual Basic 6.0
(If this comment was disrespectful, please report it.)

 
1/12/2006 10:48:16 AMSahib

my email address is pio_pos@yahoo.com
(If this comment was disrespectful, please report it.)

 
1/6/2007 12:47:05 AMIan Cuello

No Offense: All the step by step details can view here:) http://www.databasejournal.com/features/mssql/article.php/3444771
(If this comment was disrespectful, please report it.)

 
7/9/2013 11:50:57 PMkeifee

how to insert an image to a microsoft visual basic 6.0 that has also a link from it thank you pls response in my email thank you.
(If this comment was disrespectful, please report it.)

 

Add Your Feedback
Your feedback will be posted below and an email sent to the author. Please remember that the author was kind enough to share this with you, so any criticisms must be stated politely, or they will be deleted. (For feedback not related to this particular code, please click here instead.)
 

To post feedback, first please login.