设为首页 收藏本站
查看: 833|回复: 0

[经验分享] Using FTP in Transact-SQL

[复制链接]

尚未签到

发表于 2016-6-8 10:39:58 | 显示全部楼层 |阅读模式
Using FTP in Transact-SQL
GuestAuthor on 10/1/2001 in Stored Procs

mfemenel sent us a great article on how you can FTP a file using T-SQL. He writes "The following article is based on a resource I found at 15Seconds.com but will be helpful here for all you SQL Developers. The article assumes a bit of existing VB knowledge, I’ll attempt to make this one useful for “everyman(woman)”. I’ve included the compiled DLL file, so if you don’t want to mess around with VB, there’s no need to, you can skip straight to registering the DLL on your server.


Introduction
  I’ve seen a few posts asking “How do I ftp a file into SQL” Well, if you have 6.5 or 7.0 this article should be helpful. Unfortunately it’s not an easy answer, but it’ll work great once you’ve set it up. The answer is that there isn’t a function in SQL to let you do this, you need to create a DLL to help you. For those of you who just hit the floor, get up, dust yourself off, take a deep breath and keep going. It’s not that bad, I promise.


Download the suppport files
  There are a few files for you to download which go along with this article. (Note: All the files are in this ZIP file.) The first one, FTP_SQLDLL.cls is the VB file we’ll use to create our FTP_SQLTEAM.dll. Our second one is SQLTeam_FTP.sql which is what we’ll use in Query Analyzer to execute our compiled DLL. Also included in the zip file is the DLL itself (FTP_SQLTEAM.dll), all compiled and ready to go, should you not feel like building it yourself. I’ve also added a Word document called “API Notes” which contains a little more detail about the VB side of creating the DLL that didn’t fit into a SQL Server article.


The DLL
  The fine folks at Microsoft have a DLL called “WinInet”. It provides you with internet functionality. We’re going to make some API calls to that DLL and take advantage of their code. Let’s think about this for a second. Using this method, you will be able to make API calls from TSQL!

  To register the DLL, follow these steps. Copy the FTP_SQLTEAM.dll to your server. I prefer to keep my DLL files in Winnt/System folder. Now, from a command line, type in Regsvr32.exe <dll path>/DLLNAME.dll</dll> to register the dll.


The Transact-SQL Script
  From Query Analyzer or a text editor go ahead and open up the SQLTeam_FTP.sql file so we can go through the details:

  In the first section, we’re not doing anything new, just setting up some variables to catch the various things that we need in our script:

--Create an instance of FTP Object
Set NoCount on
DECLARE @hr int--Holds error value for each sp_OA function
DECLARE @oPKG int--Holds the handle of the object
Declare @source varchar(255)--Hold error info
Declare @description varchar(255)--Hold error description
Declare @connected  int--Hold the handle of the Internet session
declare @opened int--Hold the handle of the connection to the FTP Server
declare @getfile bit--Result of the success(1)/Failure(2) of the getfile operation
  Our next block of code is creating an instance of the object. We’re going to store the object token, which is just an integer identifying the created object in the variable @oPKG. We’ll use it everytime we run a method or property setting, so the functions know which object (were we to have more than one) we’re talking about. Let’s go through this line by line.

  Here, we’re going to tell SQL to create an instance of the FTP object we created (using sp_OACreate) and store it’s “object id” in a variable called @oPkg.

--First, we want to create the object and store it's handle in @oPKG
EXEC @hr = sp_OACreate 'FTP_SQLTeam.FTP_SQLDll', @oPKG OUT
  Our value @hr will catch the return code of the sp_OACreate method. If the return code is 0 then we were successful in creating an instance of our object. Any other return value is a failure. I find it extremely useful, at least on our initial script set up, to put in a print line so we know which block is giving us our errors.

--Check for errors
IF @hr <> 0
BEGIN
PRINT '***  Create Package object failed'
EXEC  sp_OAGetErrorInfo @oPKG, @source OUT, @description OUT
select @description ,@source
RETURN
END
  Now that we have an instance of our object created, we can start putting it to work by invoking those Public Methods we created for it in our DLL. If you’re used to coding in VB I’ve put in a reference to what the VB syntax would be. Let’s look at that first line as we’ll use it a few more times for each of our subsequent functions.

  We’re going to execute a method of our object (Open_Internet). First, we pass it the object id from when we created the object(@oPkg), then pass the method name “Open_Internet”, then a variable to catch any return parameter from the method call. If there is no return value, you can just use “null”, then we specify the parameters the function expects. Here is a big caveat. The parameters are not like parameters you use in TSQL. Query Analyzer does not see @alias as a sql variable. You must use the same name as your original function declaration expects.

--Establish our internet connection
-- VB Equivalent=Open_Internet("myftp", 1, vbNullString, vbNullString, 0)
Exec @hr=sp_OAMethod @oPkg,Open_Internet,@opened Out,
@alias='myftp',
@accesstype=1,
@proxy="",
@bypass="",
@flags=0
  The next step is to connect to an FTP site. In this case we'll connect to ftp.microsoft.com.

--Connect to the FTP Server(microsoft.com)
--vb Equivalent Connect_Internet(connected, "ftp.microsoft.com", , __
--"anonymous", "graz@sqlteam.com", 1, 0, 0)
Exec @hr=sp_OAMethod @oPkg,Connect_Internet,@Connected Out,
@handleid=@opened,
@server='ftp.microsoft.com',
@port=0,
@username='anonymous',
@pwd='graz@sqlteam.com',
@service=1,
@flags=0,
@context=0
  And finally we can copy a file from their ftp server to the hard drive of the local machine.

--Get the file and direct it to our local drive
--vb Equivalent Get_File(sessionid, remotefile, newfile, _
--failifexists, flagsandattr, flags, context) As Boolean
Exec @hr=sp_OAMethod @oPkg,Get_File,@getfile OUT,
@sessionid=@Connected,
@remotefile='/bussys/readme.txt',
@newfile='c:/msreadme.txt',
@failifexists=0,
@flagsandattr=0,
@flags=1,
@context=0
Conclusion
  That should do it! Remember, I’ve kept this pretty simple so it was easy to follow. You can make it a much more robust script by adding parameters for the file name, the server, userid, pwd, etc. The article on 15seconds.com points out several other function calls you can make that I didn’t go into. Here’s another link that details what functions are available in WinInet.dll. Get your feet wet with this version first. Then you can add in the fancier stuff. Walk before you run!




运维网声明 1、欢迎大家加入本站运维交流群:群②:261659950 群⑤:202807635 群⑦870801961 群⑧679858003
2、本站所有主题由该帖子作者发表,该帖子作者与运维网享有帖子相关版权
3、所有作品的著作权均归原作者享有,请您和我们一样尊重他人的著作权等合法权益。如果您对作品感到满意,请购买正版
4、禁止制作、复制、发布和传播具有反动、淫秽、色情、暴力、凶杀等内容的信息,一经发现立即删除。若您因此触犯法律,一切后果自负,我们对此不承担任何责任
5、所有资源均系网友上传或者通过网络收集,我们仅提供一个展示、介绍、观摩学习的平台,我们不对其内容的准确性、可靠性、正当性、安全性、合法性等负责,亦不承担任何法律责任
6、所有作品仅供您个人学习、研究或欣赏,不得用于商业或者其他用途,否则,一切后果均由您自己承担,我们对此不承担任何法律责任
7、如涉及侵犯版权等问题,请您及时通知我们,我们将立即采取措施予以解决
8、联系人Email:admin@iyunv.com 网址:www.yunweiku.com

所有资源均系网友上传或者通过网络收集,我们仅提供一个展示、介绍、观摩学习的平台,我们不对其承担任何法律责任,如涉及侵犯版权等问题,请您及时通知我们,我们将立即处理,联系人Email:kefu@iyunv.com,QQ:1061981298 本贴地址:https://www.yunweiku.com/thread-227761-1-1.html 上篇帖子: FTP设置SSL安全加密 下篇帖子: 一些ftp命令(转)
您需要登录后才可以回帖 登录 | 立即注册

本版积分规则

扫码加入运维网微信交流群X

扫码加入运维网微信交流群

扫描二维码加入运维网微信交流群,最新一手资源尽在官方微信交流群!快快加入我们吧...

扫描微信二维码查看详情

客服E-mail:kefu@iyunv.com 客服QQ:1061981298


QQ群⑦:运维网交流群⑦ QQ群⑧:运维网交流群⑧ k8s群:运维网kubernetes交流群


提醒:禁止发布任何违反国家法律、法规的言论与图片等内容;本站内容均来自个人观点与网络等信息,非本站认同之观点.


本站大部分资源是网友从网上搜集分享而来,其版权均归原作者及其网站所有,我们尊重他人的合法权益,如有内容侵犯您的合法权益,请及时与我们联系进行核实删除!



合作伙伴: 青云cloud

快速回复 返回顶部 返回列表