来源:[url]www.databasejournal.com[/url] 作者:MAK
翻译:xiaolu 来自:
[url]http://666w.com/[/url]
December 14, 2004
Upload multiple files to SQL Server Image column
By Muthusamy Anantha Kumar aka The MAK
翻译:xiaolu
这篇文章是个上传多个文件到Ms sqlserver的p_w_picpath类型字段的例子,很多办法可以做到,我想介绍的这种方法利用了OSQL.exe和TEXTCOPY.exe 工具。
先决条件:
a.必须安装了ms sqlsever 2000的客户端 工具
b. sql的用户必须至少是dbowner权限
步骤:
1.建立upload.bat,内容如下:
Upload multiple files to SQL Server Image column
By Muthusamy Anantha Kumar aka The MAK
翻译:xiaolu
这篇文章是个上传多个文件到Ms sqlserver的p_w_picpath类型字段的例子,很多办法可以做到,我想介绍的这种方法利用了OSQL.exe和TEXTCOPY.exe 工具。
先决条件:
a.必须安装了ms sqlsever 2000的客户端 工具
b. sql的用户必须至少是dbowner权限
步骤:
1.建立upload.bat,内容如下:
@ECHO off cls REM ECHO on REM Objective: To upload all the files in the folder to SQL server as a table REM Created by: MAK REM Date: Nov 3, 2004 REM Contact: mak_[email]999@yahoo.com[/email] REM Check parameters if "%1"=="" Goto noparmErr if "%1"=="/?" Goto Help
REM assign variables
set Sourcefolder=%1 set servername=%2 set Databasename=%3 set loginname=%4 set password=%5 set LogFile=%6
Date/t > %6
time /t >> %6
REM Export DIR listing to Dirlist.txt
dir %1 /b > Dirlist.txt
OSQL /S%2 /U%4 /P%5 /d%3 /Q "if exists (select * from %3.dbo.sysobjects where id = object
_id(N'[Myuploadtable]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table %3.dbo.[Myuploadtable] " >> %6
OSQL /S%2 /U%4 /P%5 /d%3 /Q "create table %3.dbo.Myuploadtable (id varchar(500),ImageFiles Image)" >> %6
FOR /F "usebackq delims==" %%i in (Dirlist.txt) do OSQL /S%2 /U%4 /P%5 /Q "insert into %3.dbo.Myuploadtable (id,ImageFiles) values('%%i',' ') " >> %6
FOR /F "usebackq delims==" %%i in (Dirlist.txt) do textcopy.exe /S%2 /U%4 /P%5 /D%3 /Tmyuploadtable /CImagefiles /F"%1%%i" /I /W"where [id]='%%i'" >> %6
GOTo END2
:noparmErr
Echo Usage help: example as follows echo ... Echo Objective: To upload all the files in the folder to SQL server Table Echo Created by: MAK Echo USAGE: ECHO .... ECHO .... Echo %0 SourceFolder Servername Databasename Login Password LogFile Goto END2
:Help
Echo Usage help: example as follows echo ... Echo Objective: To upload all the files in the folder to SQL server Table Echo Created by: MAK Echo USAGE: ECHO .... ECHO .... Echo %0 SourceFolder Servername Databasename Login Password Logfile Goto END2
:END
Echo "Compare Query results Completed" >>%LogFile% date/t >> %LogFile% time/t >> %LogFile% goto END2
:END2
|
_blank>
upload.bat下载
2.把textcopy.Exe从Microsoft SQL Server\MSSQL\Binn目录拷贝到upload.bat同一目录。
3.运行upload.bat,参数如下:
Upload - bat文件名
C:\myfiles\ - 所有要传的文件目录(放在一个目录下)
SQL - sqlserver的网络名称或者ip地址
MyDB - 数据库名(必须存在)
sa - 数据库用户名
yeahright - 数据库 密码
x.log - 本地log文件
3.运行upload.bat,参数如下:
_blank> |
Upload - bat文件名
C:\myfiles\ - 所有要传的文件目录(放在一个目录下)
SQL - sqlserver的网络名称或者ip地址
MyDB - 数据库名(必须存在)
sa - 数据库用户名
yeahright - 数据库 密码
x.log - 本地log文件
_blank> |
Select ID from Myuploadtable 01-2004-08-20-.wav Pfizer 12625.doc STARTSQL.bat status.bat STOPSQL.bat sysperfinfo.zip Tech Interview.xls Unix.bat Taiwan.zip
select * from Myuploadtable
01-2004-08-20-.wav 0x5249464618990A00574 Pfizer 12625.doc 0xD0CF11E0A1B11AE STARTSQL.bat 0x6E65742073746F7D status.bat 0x6E6574207374617SDF STOPSQL.bat 0x6E65742073746FDF sysperfinfo.zip 0x5249464618990A00574 Tech Interview.xls 0xD0CF11E0A1B11AEDF Unix.bat 0x6E65742073746F7DER Taiwan.zip 0x5249464618990A00DE |