使用存储过程从SQL Server导出图像 [英] Export images from a SQL Server using Stored procedures

问题描述

我使用Microsoft Sql server 2012作为DBMS，我有我的数据库，其中包含有关足球运动员的信息。每个播放器都有一个摄影，我需要导出播放器照片到我的电脑，我想它是可以通过TSQL来做，所以我可以避免编程或编辑我的应用程序只导出照片。

这是我到目前为止做的：

MY SQL CODE：

EXEC sp_configure'show advanced options'，1 GO RECONFIGURE GO EXEC
sp_configure 'xp_cmdshell'，1 GO RECONFIGURE GO

EXEC master..xp_cmdshell'mkdir C：\ImagesFromSql'

EXEC master..xp_cmdshell'BCPSELECT Photo FROM
[FootballTeam]。[dbo]。[Players]queryout
C：\ImagesFromSql\TestImage.jpg-T -N'

如您所见，我的文件夹ImagesFromSql是在C：\上创建的，还有一张照片存储在那里，但intersting那张照片是361MB大，所以可能是数据库中包含的所有照片的大小？
我想知道如何在数据库中导出所有图像，而不是一个在这种情况下，可以设置他们的名字，例如让我们设置图像名称为PlayerID，例如1.jpg，2.jpg， 3.jpg ... 650.jpg ...

after @ H.Fadlallah建议一些答案这是我得到的：

无法预览图片..：）

@ H.Fadlallah后再次我执行此查询：

DECLARE @ID as int DECLARE @SQL as varchar（4000）

DECLARE csr CURSOR FOR SELECT PlayerID FROM
[FootballTeam]。[dbo]。[Players]

OPEN csr

FETCH NEXT FROM csr INTO @ID

WHILE @@ FETCH_STATUS = 0 BEGIN

SET @SQL ='BCPSELECT Photo FROM [FootballTeam]。[dbo]。[Players] WHERE
PlayerID ='+ CAST（@ID as varchar（10））+'queryout
C：\ImagesFromSql\'+ CAST（@ID as varchar（10））+'.jpg-T -f
C：\ImagesFromSql\formatfile.fmt'

EXEC master..xp_cmdshell @SQL

FETCH NEXT FROM csr INTO @ID

END

CLOSE csr DEALLOCATE csr

我得到空图像，可能是我错过了东西..（btw文件formatfile.fmt是在与图像相同的文件夹中创建，我将我的Photo属性的二进制值从8改为0，因为H.Fadallah在另一篇文章中建议我） / p>

解决方案

请尝试以下查询：

  EXEC sp_configure'show advanced options'，1 
 
 GO 
 RECONFIGURE 
 GO 
 EXEC sp_configure'xp_cmdshell'，1 
 GO 
 RECONFIGURE 
 GO 
 
 EXEC master..xp_cmdshell'mkdir C：\ImagesFromSql'
 
 
 DECLARE @ID as int 
 DECLARE @SQL as varchar（4000）
 
 DECLARE csr CURSOR FOR SELECT ID from [FootballTeam]。[dbo]。[玩家] 
 
 OPEN csr 
 $ b b FETCH NEXT FROM csr INTO @ID 
 
 WHILE @@ FETCH_STATUS = 0 
 BEGIN 
 
 
 SET @SQL ='BCPSELECT Photo FROM [ （10））+'queryout'C：\ImagesFromSql\'+ CAST（@ID as varchar（10））+ CAST（@ID as varchar（10）） '.jpg-T -N'
 
 EXEC master..xp_cmdshell @SQL 
 
 FETCH NEXT FROM csr INTO @ID 
 
 END 
 
 
 CLOSE csr 
 DEALLOCATE csr 
  

I'm using Microsoft Sql server 2012 as DBMS, there I have my database which is containing informations about football players. Each player has a photography, and I need to export players photos to my computer, I guess it is possible to do it over TSQL, so I could avoid programming or editing my application to export photos only.

Here is what I did so far:

MY SQL CODE:

EXEC sp_configure 'show advanced options', 1 GO RECONFIGURE GO EXEC sp_configure 'xp_cmdshell', 1 GO RECONFIGURE GO

EXEC master..xp_cmdshell 'mkdir C:\ImagesFromSql'

EXEC master..xp_cmdshell 'BCP "SELECT Photo FROM [FootballTeam].[dbo].[Players]" queryout "C:\ImagesFromSql\TestImage.jpg" -T -N'

As you can see, my folder ImagesFromSql is created on "C:\", also one photo is stored there but intersting is that photo is 361MB big so probably that is size of all photos that are contained in database? And I am wondering how could I export all images from database instead of one in this case, and is it possible to set their names for example lets set Image name as PlayerID, for example 1.jpg, 2.jpg, 3.jpg....650.jpg...

after @H.Fadlallah suggested some answers this is what I get:

It is impossible to preview image.. :)

Again after @H.Fadlallah help I executed this query:

DECLARE @ID as int DECLARE @SQL as varchar(4000)

DECLARE csr CURSOR FOR SELECT PlayerID FROM [FootballTeam].[dbo].[Players]

OPEN csr

FETCH NEXT FROM csr INTO @ID

WHILE @@FETCH_STATUS = 0 BEGIN

SET @SQL = 'BCP "SELECT Photo FROM [FootballTeam].[dbo].[Players] WHERE PlayerID = "' + CAST(@ID as varchar(10)) + ' queryout "C:\ImagesFromSql\' + CAST(@ID as varchar(10)) + '.jpg" -T -f C:\ImagesFromSql\formatfile.fmt'

EXEC master..xp_cmdshell @SQL

FETCH NEXT FROM csr INTO @ID

END

CLOSE csr DEALLOCATE csr

But Now I'm getting empty images, probably I missed something.. (btw file formatfile.fmt is created in same folders as images and I changed binary value for my Photo attribute from 8 to 0 as H.Fadallah suggested me in another post).

解决方案

try the following query:

EXEC sp_configure 'show advanced options', 1 

GO 
RECONFIGURE 
GO 
EXEC sp_configure 'xp_cmdshell', 1 
GO 
RECONFIGURE 
GO

EXEC master..xp_cmdshell 'mkdir C:\ImagesFromSql'


DECLARE @ID as int
DECLARE @SQL as varchar(4000)

DECLARE  csr  CURSOR FOR SELECT ID FROM [FootballTeam].[dbo].[Players]

OPEN csr

FETCH NEXT FROM csr INTO @ID

WHILE @@FETCH_STATUS = 0 
BEGIN


SET @SQL = 'BCP "SELECT Photo FROM [FootballTeam].[dbo].[Players] WHERE ID = "' + CAST(@ID as varchar(10)) + ' queryout "C:\ImagesFromSql\' + CAST(@ID as varchar(10)) + '.jpg" -T -N'

EXEC master..xp_cmdshell  @SQL

FETCH NEXT FROM csr INTO @ID

END


CLOSE csr
DEALLOCATE csr

这篇关于使用存储过程从SQL Server导出图像的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！