April 16, 2011 at 12:09 pm
Hello folks,
A typical newbie question, I suppose.
Imagine at one side we have a directory with images of employees. The images have a name that correponds with the id of the employee. For instance employee with id 1 has a image/picture/photo called 1.jpg. On the other side we have a SQL Server 2005 database with (ao) a table employees which have an id, lastname, surname, address, etc. and a blank field called employee_photo.
The question is: how can I fill the field employee_photo with the pictures of the employees from the directory batchwise given the above described situation? Important: not every employee has a picture!
Anyone any ideas?
I hope to hear from you soon.
Thanks for any help,
Robert
April 17, 2011 at 3:28 am
i dont know if there's a solution for this within SQL (pretty new at the sql thingy myself). but what i would do in a situation like this is connect to the sql table with odbc or some-such and use ADODB and VB or VBA (in MSACCESS for example) to run through the directory and populate the table.
code in ACCESS can look something like this:
public function GetPictureFileLocations
dim strMyLocation as string
dim strRunningLocation as string
strMyLocation = "C:\MyPath\*.jpg"
strRunningLocation = dir(strMyLocation,vbNormal)
do until strRunningLocation = ""
currentdb.execute "UPDATE MyTable SET MyField = '" & left(strMyLocation,len(strMyLocation)-5) & strRunningLocation & "' WHERE ID = " & Left(strRunningLocation,len(strRunningLocation)-4)
strRunningLocation = dir
loop
end function
you can do the same with a recordset update rather than a query update for each record but this is the simplest way to illustrate what i'm trying to explain. btw, the query will not update anything (will run on empty) if the photo number does not correspond to any ID in the table
hope this helps.
April 17, 2011 at 8:04 am
Adirudik thank you for your answer. I don't know if this is the right solution because inserting images through MS Access can give all sorts of problems (an OLE-object in Access doesnot function in SQL Server. In SQL Server you need a varbinary(max) datatype for images).
When I want to insert an image in the SQL Server database then I use the following for one image (in this case we use a separate table called Renner_Foto):
USE WielerDatabaseSQL
GO
INSERT INTO Renner_Foto
(ImageID, ImageName, ImageData)
SELECT '1' As ImageID,
'Lemond' As ImageName,
BulkColumn FROM OPENROWSET( Bulk 'D:\Mijn Documenten\Documents\DWCoureurLocaleData\Irfanview_26012011\1.jpg',
SINGLE_BLOB) As BLOB
GO
The question is: how to automate this process? And a second question is: how to replace the imageid with the fileid? The imagename can stay empty.
Perhaps this generates some new ideas.
Grz,
Robert
April 18, 2011 at 2:46 am
Hi folks,
Because of a lack of reactions on my image-post I try it a second time.
1. My problem was: how to automate a process of inserting images into a table of a database. The images are .jpg files and the name of the file corresponds with the name of the person (in this case a 'renner'). The image 100.jpg is the image of renner with ID 100.
2. When I insert one image then I use the following code:
USE WielerDatabaseSQL
GO
INSERT INTO Renner_Foto
(ImageID, ImageName, ImageData)
SELECT '1' As ImageID,
'Lemond' As ImageName,
BulkColumn FROM OPENROWSET( Bulk 'D:\Mijn Documenten\Documents\DWCoureurLocaleData\Irfanview_26012011\1.jpg',
SINGLE_BLOB) As BLOB
GO
3. The problem is: there are 1000 jpg.files. Is it possible to set ImageID to the name of the jpg.file without .jpg and to set ImageData to the jpg file in one workflow? The ImageName can stay empty, that is ' '.
We need some kind of batch operation that I cannot figure out. Is there anyone outthere who can help me out?
Thanks in advance,
Robert
April 18, 2011 at 4:27 am
hi robert,
Try something like this...quick and dirty (thats me...) ..use sp_executesql and a while loop
make up yr sql as a string and then execute it
something like this....back of a fag packet
DECLARE @counter int. @folderpath varchar(255), @fullpath varchar(4000), @ImageName varchar(255)
DECLARE @sqlstring varchar(4000)
SET folderpath = 'D:\Mijn Documenten\Documents\DWCoureurLocaleData\Irfanview_26012011'
SET @counter = 1
WHILE @counter < 1000
BEGIN
SET fullpath= @folderpath + '\' + convert(varchar, @counter) + '.jpg'
SET @ImageName = 'imagename' + convert(varchar, @counter)
SET @sqlstring = ''
SET @sqlstring = @sqlstring + 'INSERT INTO Renner_Foto, ImageName, ImageData ) '
SET @sqlstring = @sqlstring + 'SELECT '' + convert(varchar, @counter) + '' AS ImageId'
SET @sqlstring = @sqlstring + @imagename + ' As ImageName,'
SET @sqlstring = @sqlstring + ' BulkColumn FROM OPENROWSET( Bulk '' + @fullpath + ','
SET @sqlstring = @sqlstring + ' SINGLE_BLOB) As BLOB '
exec sp_executesql @string
SET @counter = @counter + 1
END
problem is doubling up the single quotes as always using a string to hold sql ...just have a play...get my drift...its not pretty but should do the job
April 18, 2011 at 8:07 am
Hello Robin,
Thanks for your great advise - we are getting in the right direction.
I changed your code somewhat (ImageName = ' ', we fill that in later). This gives the following result:
USE WielerDatabaseSQL
GO
DECLARE @counter AS int
DECLARE @folderpath AS varchar(255)
DECLARE @fullpath AS varchar(4000)
DECLARE @sqlstring AS varchar(4000)
SET @folderpath = 'D:\Mijn Documenten\Documents\DWCoureurLocaleData\Irfanview_26012011'
SET @counter = 670
WHILE @counter < 12000
BEGIN
SET @fullpath = @folderpath + '\' + convert(varchar, @counter) + '.jpg'
SET @sqlstring = ''
SET @sqlstring = @sqlstring + 'INSERT INTO Renner_Foto (ImageID, ImageName, ImageData) '
SET @sqlstring = @sqlstring + 'SELECT '' + convert(varchar, @counter) + '' AS ImageId, '
SET @sqlstring = @sqlstring + ' ' + ' As ImageName,'
SET @sqlstring = @sqlstring + 'BulkColumn FROM OPENROWSET( Bulk '' + @fullpath + ','
SET @sqlstring = @sqlstring + ' SINGLE_BLOB) As BLOB '
IF EXISTS BulkColumn
exec sp_executesql @string
SET @counter = @counter + 1
END
The other important change is, that not every 'renner' has an image. So when using a counter, there are counters without an image file. I tried to solve that one with IF EXISTS BulkColumn. Because the highest number with a .jpg file is near 12000 we loop through 670-12000 (670 because I already did the first 670 manually - a terrible job).
When I use the above codeblock I'm getting the following errors:
Msg 102, Level 15, State 1, Line 20
Incorrect syntax near ','.
Msg 105, Level 15, State 1, Line 21
Unclosed quotation mark after the character string '
IF EXISTS BulkColumn
exec sp_executesql @string
SET @counter = @counter + 1
END
'.
This must be quotation problems, one of my weakest virtues. Would you be so kind to look to the code once more?
Thanks a lot.
Grz,
Robert
April 18, 2011 at 8:11 am
OPENROWSET( Bulk ''
That needs a 3rd quotation.
April 18, 2011 at 8:12 am
BTW, in SSMS, you can double-click the error in the output window and it'll bring you in the code in the region of the error. Sometimes right on it.
In this case you can look for red text where there should not be red text and backtrack to find the beginning of the problem.
April 18, 2011 at 9:25 am
Thanks for the additions (and the handy double click tip) - I think the quotation is OK now, but we get another problem:
Msg 214, Level 16, State 2, Procedure sp_executesql, Line 1
Procedure expects parameter '@statement' of type 'ntext/nchar/nvarchar'.
When I change @sqlstring in (nvarchar) we get an endless list of errors, so something must be wrong.
Another problem is the if-test for existence of the jpg file - if exists begin ... end doesnot give the right solution because we get an error of column unknown (bulkcolumn, imagedata, whatever).
We can forget the existence of the .jpg file for most of the counters. Another posibility is that we try out all counters (some have a jpg, some not). After doing so I can delete all the rows without an jpg-file.
Before doing is the code below should be up and running without errors:
USE WielerDatabaseSQL
GO
DECLARE @counter AS int
DECLARE @folderpath AS varchar(255)
DECLARE @fullpath AS varchar(4000)
DECLARE @sqlstring AS varchar(4000)
SET @folderpath = 'D:\Mijn Documenten\Documents\DWCoureurLocaleData\Irfanview_26012011'
SET @counter = 670
WHILE @counter < 12000
BEGIN
SET @fullpath = @folderpath + '\' + convert(varchar, @counter) + '.jpg'
SET @sqlstring = ''
SET @sqlstring = @sqlstring + 'INSERT INTO Renner_Foto (ImageID, ImageName, ImageData) '
SET @sqlstring = @sqlstring + 'SELECT '' + convert(varchar, @counter) + '' AS ImageID, '
SET @sqlstring = @sqlstring + ''' As ImageName,'
SET @sqlstring = @sqlstring + 'BulkColumn FROM OPENROWSET( Bulk '' + @fullpath + '','
SET @sqlstring = @sqlstring + ' SINGLE_BLOB) As BLOB '
exec sp_executesql @sqlstring
SET @counter = @counter + 1
END
I hope that someone has an idea to get things going the right way.
Grz,
Robert
April 18, 2011 at 9:33 am
Another golden nugget.
Instead of running the code print it (select with output to text mode rather than grid), then copy paste back into ssms and check syntaxe there and then run. That's give you all the info you need to debug.
1 more tip, the default text column with is 256 characters, you'll need to put it back to 8000. You can do that in the query option with affects only that query window or edit the setting in ssms options which is permanant and for all new query windows you open.
April 18, 2011 at 9:50 am
hi Robert
a little trick to double up single quotes is to create a simple varchar @single_quotes then use this a wrapper around any string you want to double up...stops you going bonkers...been down there many times mate
i.e
DECLARE @single_quote varchar(1) , @fullname varchar(255)
SET @single_quote = ''''
SET @fullname = 'robin rai'
print 'name with quotes = ' + @single_quote + @fullname + @single_quote
have fun...
April 18, 2011 at 9:54 am
hi Robert
a tip to add single quotes..create a varchar and use it as wrapper...before and after what you want to double up
declare @single_quote varchar(1) , @fullname varchar(255)
SET @single_quote = ''''
SET @fullname = 'robin rai'
print 'name with quotes = ' + @single_quote + @fullname + @single_quote
April 18, 2011 at 10:29 am
Guys, thanks for all the tips and suggestions. The quote-tip I understand, but the copy/paste tip in textmode I cannot follow (grasshopper level remember). Furthermore I cannot solve that item, because the errormessage is quite clear: turn varchar in nvarchar (@sqlstring). The following list of error-messages appears:
Msg 137, Level 15, State 2, Line 1
Must declare the scalar variable "@fullpath".
Msg 105, Level 15, State 1, Line 1
Unclosed quotation mark after the character string ', SINGLE_BLOB) As BLOB '.
Msg 137, Level 15, State 2, Line 1
etc, etc.
So something is terribly wrong and I have no idea what. When you try it on your PC Robin, you get the same error-list? The only thing your missing is the table defnition:
USE [WielerDatabaseSQL]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE Renner_Foto (
[ImageID] [int] NULL,
[ImageName] [nvarchar](50) NULL,
[ImageData] [varbinary](max) NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
Next round?
Grz,
Robert
April 18, 2011 at 10:37 am
You're not listening.
Kill the loop for now (or only 1 iteration).
PRINT the code and see what's wrong within 1 iteration.
You're getting 1000s of errors because you are looping 11000+ times.
April 18, 2011 at 10:41 am
DECLARE @counter AS int
DECLARE @folderpath AS varchar(255)
DECLARE @fullpath AS varchar(4000)
DECLARE @sqlstring AS varchar(4000)
SET @folderpath = 'D:\Mijn Documenten\Documents\DWCoureurLocaleData\Irfanview_26012011'
SET @counter = 670
WHILE @counter < 671
BEGIN
SET @fullpath = @folderpath + '\' + convert(varchar, @counter) + '.jpg'
SET @sqlstring = ''
SET @sqlstring = @sqlstring + 'INSERT INTO Renner_Foto (ImageID, ImageName, ImageData) '
SET @sqlstring = @sqlstring + 'SELECT '' + convert(varchar, @counter) + '' AS ImageID, '
SET @sqlstring = @sqlstring + ''' As ImageName,'
SET @sqlstring = @sqlstring + 'BulkColumn FROM OPENROWSET( Bulk '' + @fullpath + '','
SET @sqlstring = @sqlstring + ' SINGLE_BLOB) As BLOB '
select 'go into results to text mode' as OutputMode
SELECT @sqlstring
SET @counter = @counter + 1
END
current output :
--That's what your outputing, see any problems with it??
INSERT INTO
Renner_Foto
(
ImageID
, ImageName
, ImageData
)
SELECT
' + convert(varchar, @counter) + ' AS ImageID
, ' As ImageName,BulkColumn FROM OPENROWSET( Bulk ' + @fullpath
+ ', SINGLE_BLOB) As BLOB
Viewing 15 posts - 1 through 15 (of 61 total)
You must be logged in to reply to this topic. Login to reply