April 14, 2009 at 9:11 am
I have images stored locally. I have a database that contains employee data. I need to insert the images into the table with the employee data. I began writing a cursor that programmatically determines the path/filename, but once I have the file path... how do I pull the image into SQL? I am very confused. I did a lot of searching but all I could come up with is how to upload images using ASP/.net and things like that. I just wanna have a dynamic sql statement to import the existing stuff, then I can write it into my web application for future employees. I just need to pull the current stuff in. The naming convention of the files are firstname_lastname.bmp, so the select statement from my cursor is as follows:
select 'P:\Inetpub\wwwroot\BFM_Photos' +
case when job_function = 'Administration' then '\admin\' else '' end +
case when job_function = 'Parks and Plazas' then '\Park_Stewards\' else '' end +
case when job_function = 'Temp' then '\Temp\' else '' end +
case when job_function = 'Public Space Maintenance' then '\CSA\' else '' end +
case when job_function = 'Public Safety' then '\PSG\' else '' end + last_name + '_' +
first_name + '.bmp' as imagepath from employees
This gets me the path just fine for each employee. Once I have the path, what in the heck do I do with it? I hope this doesnt sound too stupid....
April 14, 2009 at 10:07 am
Check out the OPENROWSET function. I did something similair to this a long time ago. :w00t:
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 14, 2009 at 10:10 am
I will check that out now. Additionally, I tried using the sp_textcopy procedure as follows:
select 'P:\Inetpub\wwwroot\BFM_Photos' +
case when job_function = 'Administration' then '\admin\' else '' end +
case when job_function = 'Parks and Plazas' then '\Park_Stewards\' else '' end +
case when job_function = 'Temp' then '\Temp\' else '' end +
case when job_function = 'Public Space Maintenance' then '\CSA\' else '' end +
case when job_function = 'Public Safety' then '\PSG\' else '' end + last_name + '_' +
first_name + '.bmp' as filepath from employees
EXEC sp_textcopy @srvname = 'DPOBDBS',
@login = 'username',
@password = 'pass',
@dbname = 'Database',
@tbname = 'Employees',
@colname = 'picture',
@filename = "filepath",
@whereclause = " WHERE @filename LIKE '%' + last_name + '_' + first_name + '%' ",
@direction = 'I'
I get this error:
(1186 row(s) affected)
Msg 2812, Level 16, State 62, Line 9
Could not find stored procedure 'sp_textcopy'.
and no data is inserted.
April 14, 2009 at 10:20 am
isn't sp_textcopy an old sproc used to access textxopy? I believe that was removed from sql server after version 7.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 14, 2009 at 10:25 am
Yea... I know it existed in SQL 2000, but I guess not anymore. Is there an SP in SQL 2005 that does something similar?
April 14, 2009 at 2:31 pm
Say I go the easy route. I have the file path specified in a column within my table. How do I programmatically call that picture for reporting purposes? That is the only reason I wanted to put the picture in the database... I need it for reports. In this case, if I could programmatically call the image based on the path specified in another column, that would work. Any ideas??
April 14, 2009 at 2:43 pm
That would depend on what reporting system you are using and the rules about generating an image in their reports.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 14, 2009 at 2:45 pm
reporting services 2005, and I am using VS2003 or VS2005 to design the reports.
April 14, 2009 at 2:50 pm
something similair to this should let you insert them without much issue:
INSERT INTO myTable(Image)
SELECT * FROM
OPENROWSET(BULK N'FullPathHere', SINGLE_BLOB)
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 14, 2009 at 2:52 pm
If you want to put the images in the database, a CLR proc should be able to do that pretty easily. It would connect to the file (filesystem object in VB, not sure in C#), and then load it into the database.
Is that an option for you?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 14, 2009 at 2:56 pm
To Slange: I dont want to do one at a time. Moving forward that will work when I add employees, but I need a bulk update statement to bring in existing images into the database.
April 14, 2009 at 2:58 pm
To G:
I will do some research. I am familiar with VB to the extent of .net..... I will find out more info about VB and creating a CLR.
April 14, 2009 at 3:08 pm
I figured you could just the dreaded cursor for the one time import since you already have figured out the path to the current file.
If you want to do this in .net you can read the image file into a byte[] with a stream reader and then add the byte array as a parameter to an update or insert statement. If that is the path you are taking and run into problems let me know. I did something just like a few weeks ago in c#.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 14, 2009 at 3:10 pm
ok ok ok slange... you could be onto something here. Ok.... so! Say I use the code you supplied and just do a cursor with that code. I have only written 2 or 3 cursors.. I am unsure as to how I would incorporate that into a cursor... I will play with it. Any suggestions, let me know!
April 14, 2009 at 3:14 pm
If you can post at least the create scripts for your tables i will play with it and see what I can come up with. 😛
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 15 posts - 1 through 15 (of 25 total)
You must be logged in to reply to this topic. Login to reply