How does "51cto/bbs" use SQL statements to save (fetch) files to the database?

Original address: http://bbs.51cto.com/thread-1130840-1.html

Problem Description:

I'm going to set up a stored procedure on the Ms-sql server that requires a pure SQL statement to access the file.
Ask the teacher to guide you!!

Solution:

Some methods are used to import and export blob type data such as pictures, photos, videos, documents, and so on.

SQL Server:

The open tool in the Binn directory is available, prompting you to provide the appropriate parameters:

Textcopy

Read and write the image field of SQL Server using Textcopy

Using Stored procedure methods:

1, the establishment process

create procedure sp_textcopy  (        @srvname      varchar  (,       ) @login        varchar  (,        @password     varchar)   (,       ) @dbname       varchar  ( ,        @tbname       varchar  (30),         @colname     varchar  (,   )      @filename     varchar  (,     )    @whereclause  varchar  (,       ) @direction    char (1))      AS     DECLARE  @exec_str   varchar  (255)      select  @exec_str  =               ' textcopy /s  '  +  @srvname  +               '  /U  '  +  @login  +               '  /P  '  +  @password  +               '  /D  '  +  @dbname  +               '  /T  '  + @ tbname +              '  /C  '  +  @colname  +              '  /W  "'  +  @whereclause  +               '  /F  ' &Nbsp;+  @filename  +              '  /'  +  @direction      exec master. xp_cmdshell  @exec_str  go

2. Build tables and initialize data

CREATE table table name (number int,image column name image) go Insert table name VALUES (1,0x)--required, and NOT null Insert Table name VALUES (2,0x)--Must Required, and NOT NULL go

3. Read in

Sp_textcopy ' Your server name ', ' sa ', ' Your password ', ' Library name ', ' table name ', ' image column name ', ' C: \ ' picture. bmp ', ' where number =1 ', ' I '--note the condition is the number =1 sp_textcopy ' Your server name ', ' sa ', ' Your password ', ' Library name ', ' table name ', ' image column name ', ' C:\bb.doc ', ' where number =2 ', ' I '--note condition is numbered =2 go

4, read out into a file

Sp_textcopy ' Your server name ', ' sa ', ' Your password ', ' Library name ', ' table name ', ' image column name ', ' C: \ ' picture. bmp ', ' where number =1 ', ' O '--note the condition is the number =1 sp_textcopy ' Your server name ', ' sa ', ' Your password ', ' Library name ', ' table name ', ' image column name ', ' C:\bb.doc ', ' where number =2 ', ' O '--note condition is numbered =2 go

Attention:

If the textcopy is not an executable file, you will be there.
C:\Program Files\Microsoft SQL Server\mssql\binn
Directory copy Textcopy.exe to:
C:\Program Files\Microsoft SQL Server\80\Tools\Binn

To open the Xp_cmdshell method:

--Enable xp_cmdshell EXEC sp_configure ' show advanced options ', 1; RECONFIGURE; EXEC sp_configure ' xp_cmdshell ', 1;    RECONFIGURE; --Close xp_cmdshell EXEC sp_configure ' show advanced options ', 1; RECONFIGURE; EXEC sp_configure ' xp_cmdshell ', 0;    RECONFIGURE; --If you are prompted to deny access, it is generally a matter of directory permissions, you can create a new directory, add everyone Full Control permissions.

SQL Server 2005:

Through OPENROWSET and BCP:

Example 1:

-- sql server import image - sql server export image--  extract image sql server - sql server export binary datause  AdventureWorks; go -- create image warehouse for importing image into sql  Databasecreate table dbo. photolibrary  (  PhotoLibraryID INT IDENTITY  ( 1 , 1 )   Primary key,  imagename        varchar (, )  photo            varbinary (MAX)) GO --  sql server import image - sql storing images database sql  serverinsert into dbo. Photolibrary ([ImageName]) values      (' madisonave.jpg ') update dbo. photolibraryset    photo =  (SELECT *from openrowset (bulk  ' E:\image\photo\MadisonAVE.JPG ', Single_blob)  as x) where   [ImageName] =  ' madisonave.jpg ' go -- check table populationselect * From   dbo. photolibrarygo -- sql server export imagedeclare   @Command  nvarchar ( 4000)  -- keep the command on one line - single line!!!  - broken here for presentationSET  @Command  =  ' bcp  ' select  Photo from adventureworks.dbo.photolibrary "                    queryout  "E:\image\photo\expMadisonAVE.jpg"  -t -n -sprod\sql2005 ' print  @Command  -- debuggingexec xp_cmdshell @ Commandgo

Example 2:

-------------- t-sql export all images in table to file system  folder-- source table: production.productphoto  - destination: k:\ Data\images\productphoto------------ USE AdventureWorks2008; go declare   @Command        varchar (4000),           @PhotoID        INT,           @ImageFileName  varchar (declare curphotoimage cursor)  for -- cursor for each image in tableselect productphotoid,        largephotofilenamefrom   production.productphotowhere   LargePhotoFileName !=  ' No_image_available_large.gif ' open curphotoimagefetch  next from curphotoimageinto  @PhotoID,      @ImageFileNameWHILE   (@ @FETCH_STATUS  = 0)  -- cursor loop begin  -- keep the bcp command on one line - single line!!!  - broken up for presentationSET  @Command  =  ' bcp  ' select  largephoto from    adventureworks2008.production.productphoto where  productphotoid =  '  +convert (VARCHAR, @PhotoID)  +  '  queryout  ' K:\data\images \productphoto\ '  +     @ImageFileName  +  ' " -t -n -shpestar ' print  @Command  – debugging  /* bcp  "select largephoto from  adventureworks2008.production.productphotowhere productphotoid = 69 " queryout" K:\data\ Images\productphoto\racer02_black_f_large.gif " -t -n -shpestar*/ exec xp_cmdshell   @Command    &nbsP; -- carry out image export to file from db tablefetch  NEXT FROM curPhotoImageINTO  @PhotoID,           @ImageFileNameEND  -- cursor loopclose curphotoimagedeallocate curphotoimage  /*outputnullstarting copy ... Null1 rows copied. network packet size  (bytes): 4096clock time  (Ms.)  Total      : 16     Average :  (62.50 rows per sec.) null.....*/ ------------

This article is from the SQL Server deep dives blog, so be sure to keep this source http://ultrasql.blog.51cto.com/9591438/1585003

How does "51cto/bbs" use SQL statements to save (fetch) files to the database?