April 19, 2011 at 10:40 am
The result is quite simple: every image that exists is added to the database for all the other ones we get the message:
(1 row(s) affected)
Msg 4860, Level 16, State 1, Line 19
Cannot bulk load. The file "D:\Mijn Documenten\Documents\DWCoureurLocaleData\Irfanview_26012011\33.jpg" does not exist.
So I don't quite understand what you are getting at: typical newbie of course.
April 19, 2011 at 10:44 am
Can you zip me up a couple dozens of the pictures? I'll finish the code for you.
Th eonly obvious part is that either you have a begin tran / rollback which cancels everything or the code stops after the first error.
April 19, 2011 at 11:01 am
The wonder has happened: I have got some records in the database!! The problem is as follows:
In the first 100 counters there are 7 images. Sometimes images occur 3 times and sometimes 2 times: total 17 images where it should be 7.
To be certain the code:
USE WielerDatabaseSQL
GO
DECLARE @counter AS int
DECLARE @folderpath AS varchar(255)
DECLARE @fullpath AS varchar(4000)
DECLARE @shellpath AS varchar(4000)
DECLARE @shellfullpath AS varchar(4000)
DECLARE @sqlstring AS nvarchar(4000)
DECLARE @single_quote varchar(1)
DECLARE @imagename varchar(255)
DECLARE @imagenamequoted varchar(255)
DECLARE @fullpathquoted varchar(255)
DECLARE @cmd varchar(255)
SET @single_quote = ''''
SET @folderpath = 'D:\Mijn Documenten\Documents\DWCoureurLocaleData\Irfanview_26012011'
SET @shellpath = 'D:\"Mijn Documenten"\Documents\DWCoureurLocaleData\Irfanview_26012011'
SET @counter = 1
WHILE @counter <100
BEGIN
SET @imagename = convert(varchar, @counter) + '.jpg'
SET @imagenamequoted = @single_quote + @imagename + @single_quote
SET @fullpath = @folderpath + '\' + @imagename
SET @shellfullpath = @shellpath + '\' + @imagename
SET @fullpathquoted = @single_quote + @fullpath + @single_quote
SET @sqlstring = ''
SET @sqlstring = @sqlstring + 'INSERT INTO Renner_Foto (ImageID, ImageName, ImageData ) '
SET @sqlstring = @sqlstring + 'SELECT ' + convert(varchar, @counter) + ' AS ImageID, '
SET @sqlstring = @sqlstring + @imagenamequoted + ' As ImageName, '
SET @sqlstring = @sqlstring + 'BulkColumn FROM OPENROWSET( Bulk ' + @fullpathquoted + ','
SET @sqlstring = @sqlstring + ' SINGLE_BLOB) As BLOB '
-- See if the file exists first then only insert it
CREATE TABLE #tmp(s varchar(1000))
SET @cmd = 'dir /B ' + @shellfullpath
INSERT #tmp EXEC master.dbo.xp_cmdshell @cmd
IF EXISTS(SELECT 1 FROM #tmp WHERE s=@imagename)
BEGIN
exec sp_executesql @sqlstring
END
SELECT 'go to results in textmode' as Outputmode
SELECT @sqlstring
DROP TABLE #tmp
SET @counter = @counter + 1
END
Is the .zip still necessary?
Grz,
Robert
April 19, 2011 at 11:12 am
So you want me to do the work for you and you won't even want to supply to tools I need to test the code? I need a zip file with a couple sample files so I can make your code work for you?
I'm doing this for free and I'm frankly tired of teaching a complete beginner that should be taking a class rather than having me do the work for him.
I don't mind helping but this is getting ridiculous.
April 19, 2011 at 11:27 am
Let's be clear: I appreciate what your doing. But when acting on the newbees site you can expect something like me (perhaps I am the worst ever, but still). The old saying is: stupid questions don't exist, only stupid answers.
Anyway, I tried to save you some work with sending you some (partly) working code. Not appreciated, I guess.
The zip file enclosed gives an answer to your request.
Thanks,
Robert
April 19, 2011 at 12:03 pm
Thanks for helping me help YOU.
USE [tempdb]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE dbo.Renner_Foto (
[ImageID] [int] NULL,
[ImageName] [nvarchar](50) NULL,
[ImageData] [varbinary](max) NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
CREATE TABLE #out (files VARCHAR(500) NULL, InsertCMD
AS 'INSERT INTO Renner_Foto (ImageID, ImageName, ImageData ) '
+ ' SELECT ' + LEFT(files, PATINDEX('%[^0-9]%', files) - 1) + ' AS ImageID, '
+ '''' + files + ''' As ImageName, '
+ 'BulkColumn FROM OPENROWSET( Bulk ' + '''D:\Test img\' + files + ''','
+ ' SINGLE_BLOB) AS BLOB '
)
INSERT INTO #out (files)
EXEC xp_cmdshell 'dir / B /O "D:\Test img\"'
DELETE FROM #out WHERE files IS NULL OR PATINDEX('%[^0-9]%', files) = 0
--SELECT files, PATINDEX('%[^0-9]%', files) AS Period, LEFT(files, PATINDEX('%[^0-9]%', files) - 1) As Id, InsertCMD
--FROM #out
--WHERE PATINDEX('%[^0-9]%', files) <> 0
DECLARE @cmd VARCHAR(MAX)
DECLARE file_cursor CURSOR
FOR SELECT
InsertCMD
FROM
#out
ORDER BY
files
OPEN file_cursor
FETCH NEXT FROM file_cursor INTO @cmd
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT @cmd
EXEC (@cmd)
FETCH NEXT FROM file_cursor INTO @cmd
END
CLOSE file_cursor
DEALLOCATE file_cursor
SELECT * FROM dbo.Renner_Foto
April 19, 2011 at 12:23 pm
Thanks for your code (very, very fast production - that must be a pro): for simplicity I made the same directory as you did (D:\test img\) and its running like the fire brigade :-):-).
I must say for a newbee is the code a bit complicated, but w'll overcome that of course.
Thanks again. Having patience with newbees is a necessary virtue as you now by now.
Grz,
Robert
April 19, 2011 at 1:26 pm
hi Robert,
we all started as novices..now to business ...do one thing first... the script i sent works out of the tin...can you create a c:\development_support\images directory and add 10 files in there from 1.jpg to 10.jpg and see what happens...let us isolate first if its a directory naming problem...
April 19, 2011 at 2:05 pm
forget the above post...i caught the post trail on page 5 thinkg it was the last post ...look as if its been fixed...chow for now....
April 19, 2011 at 2:09 pm
r_slot (4/19/2011)
Thanks for your code (very, very fast production - that must be a pro): for simplicity I made the same directory as you did (D:\test img\) and its running like the fire brigade :-):-).I must say for a newbee is the code a bit complicated, but w'll overcome that of course.
Thanks again. Having patience with newbees is a necessary virtue as you now by now.
Grz,
Robert
I deliberately put a space in the folder so that you could just copy / paste your own folder info in there. Now that this is working, the only thing that could screw up is permissions to access the files.
Assuming this is only a one off insert, just move the file to the d: folder and be done with it! :w00t:
That code is still simple. You just don't understand my thought / debug process. The idea is to get 1 import working, from there on out it's just a loop.
April 20, 2011 at 1:09 am
Hello Robin,
To be complete I want to inform you about your code: that code is running too, but I had to make two changes:
1). Because xp_cmdshell accepts no spaces in a filepath I made two more scalars @shellpath and @shellfullpath with the space-part between "".
2). When the string is not emptied some images are copied two or three times into the database. So in the bottom I put some code like SET @fullpath=' ', etc.
After these changes everything works fine, but not as fast as the Ninja solution. To give you an idea: Robin solution 8 seconds, Ninja less then a second with 29 (the same) images.
Your changed code looks like this:
USE WielerDatabaseSQL
GO
IF EXISTS (SELECT ImageID FROM Renner_Foto WHERE ImageID=1)
BEGIN
DELETE Renner_Foto
END
GO
DECLARE @counter AS int
DECLARE @folderpath AS varchar(255)
DECLARE @fullpath AS varchar(4000)
DECLARE @shellpath AS varchar(4000)
DECLARE @shellfullpath AS varchar(4000)
DECLARE @sqlstring AS nvarchar(4000)
DECLARE @single_quote varchar(1)
DECLARE @imagename varchar(255)
DECLARE @imagenamequoted varchar(255)
DECLARE @fullpathquoted varchar(255)
DECLARE @cmd varchar(255)
SET @single_quote = ''''
SET @folderpath = 'D:\Mijn Documenten\Documents\DWCoureurLocaleData\Irfanview_26012011'
SET @shellpath = 'D:\"Mijn Documenten"\Documents\DWCoureurLocaleData\Irfanview_26012011'
SET @counter = 1
WHILE @counter <387
BEGIN
SET @imagename = convert(varchar, @counter) + '.jpg'
SET @imagenamequoted = @single_quote + @imagename + @single_quote
SET @fullpath = @folderpath + '\' + @imagename
SET @shellfullpath = @shellpath + '\' + @imagename
SET @fullpathquoted = @single_quote + @fullpath + @single_quote
SET @sqlstring = ''
SET @sqlstring = @sqlstring + 'INSERT INTO Renner_Foto (ImageID, ImageName, ImageData ) '
SET @sqlstring = @sqlstring + 'SELECT ' + convert(varchar, @counter) + ' AS ImageID, '
SET @sqlstring = @sqlstring + @imagenamequoted + ' As ImageName, '
SET @sqlstring = @sqlstring + 'BulkColumn FROM OPENROWSET( Bulk ' + @fullpathquoted + ','
SET @sqlstring = @sqlstring + ' SINGLE_BLOB) As BLOB '
-- See if the file exists first then only insert it
CREATE TABLE #tmp(s varchar(1000))
SET @cmd = 'dir /B ' + @shellfullpath
INSERT #tmp EXEC master.dbo.xp_cmdshell @cmd
IF EXISTS(SELECT 1 FROM #tmp WHERE s=@imagename)
BEGIN
exec sp_executesql @sqlstring
END
--SELECT 'go to results in textmode' as Outputmode
--SELECT @sqlstring
SET @fullpath =' '
SET @fullpathquoted =' '
SET @shellfullpath =' '
DROP TABLE #tmp
SET @counter = @counter + 1
END
In either case I want to thank you for your contribution to get this newbee to an acceptable level of operation.
Grz,
Robert
April 20, 2011 at 5:26 am
Do you have to run that code more than once for the same folder?
Is there any reason why there's no promary key on the image id column?
April 20, 2011 at 11:39 am
There should be a primary key, yet to be installed. I am going to produce some improvements, but that has to be decided. Had no time today, tomorrow neither.
To be continued, but we have several working solutions!
Grz,
Robert
April 20, 2011 at 11:42 am
Sorry, forgot something. When there are additions/changes, (fi other images for the same ryder), we loop through the same folder. This only happens twice a year, maximum.
Grz,
Robert
April 20, 2011 at 11:43 am
Ninja's_RGR'us (4/20/2011)
Do you have to run that code more than once for the same folder?Is there any reason why there's no promary key on the image id column?
That's what I thought, then you need to add if not exists either in the loop. Let me know if you need help with that one.
Viewing 15 posts - 46 through 60 (of 61 total)
You must be logged in to reply to this topic. Login to reply