Cacher is the code snippet organizer for pro developers

-- =================================================================  
-- 将指定字符串转换为有效的文件名,不能包含字符(\/:*?"<>|Tab、换行、回车)之一.  
-- 如果遇到不能用作文件名的特殊字符,用'#'代替,并用后缀'_?'标识,  
-- 如:'电脑部<开发组>#"网络组"',变更为'电脑部#开发组###网络组#_78066'  
-- 返回值: 返回可用作文件名的字符串.  
  
-- Create by lenya on 2010/10/10  
-- =================================================================  
  
CREATE function [dbo].[fn_StrToFileName](@str varchar(100))  
returns varchar(150)  
as  
begin  
    declare @strFileName varchar(150),  
            @suffix varchar(30),  
            @start int,  
            @len int,  
            @letter varchar(2)  
  
    -- 如果不包含特殊字符(\/:*?"<>|Tab、换行、回车),直接返回  
    if not exists(select 1 where @str like '%[\/:*?"<>|'+char(9)+char(10)+char(13)+']%')  
        return @str  
      
    set @suffix = '_'  
    set @len = len(@str)  
    set @start = 1  
  
    -- 用数字作后缀,标识'特殊'字符,如'电脑部#开发组###网络组#_78066',则可反译出'电脑部<开发组>#"网络组'  
    while (@start <= @len)  
    begin  
        set @letter = substring(@str, @start, 1)   
        set @suffix = @suffix +    
        case @letter  
            when '#' then '0'  
            when '\' then '1'  
            when '/' then '2'  
            when ':' then '3'  
            when '*' then '4'  
            when '?' then '5'  
            when '"' then '6'  
            when '<' then '7'  
            when '>' then '8'  
            when '|' then '9'  
            when char(9) then 'a'  
            when char(10) then 'b'  
            when char(13) then 'c'  
            else ''  
        end          
        set @start = @start + 1  
    end  
  
    -- 文件名不能包含下列任何字符之一:\/:*?"<>|Tab、换行、回车  
    -- 将这些不允许作为文件名的字符替换为#  
    set @str = replace(@str,'\','#')  
    set @str = replace(@str,'/','#')  
    set @str = replace(@str,':','#')  
    set @str = replace(@str,'*','#')  
    set @str = replace(@str,'?','#')  
    set @str = replace(@str,'"','#')  
    set @str = replace(@str,'<','#')  
    set @str = replace(@str,'>','#')  
    set @str = replace(@str,'|','#')  
    set @str = replace(@str,char(9),'#')  
    set @str = replace(@str,char(10),'#')  
    set @str = replace(@str,char(13),'#')  
      
    return @str + @suffix  
  
end  

-- =================================================================  
-- 根据字符串产生QR图片,保存到指定的路径中.  
-- 返回值: 成功返回图片保存所有的完整路径,否则返回''  
-- Create by lenya on 2010/10/10  
  
-- =================================================================  
  
CREATE function [dbo].[fn_CreateQRImage](@uc_Data varchar(100))  
returns varchar(255)  
as  
begin  
    declare @hr int, @object int, @result int  
    declare @strFilePath varchar(50), @strFileName varchar(100), @strFullFileName varchar(256)  
   
    if rtrim(ltrim(@uc_Data)) = '' or @uc_Data is null   
        return ''  
  
    set @strFileName = [dbo].[fn_StrToFileName](@uc_Data)-- 将@uc_Data转换为有效的文件名  
  
    set @strFileName = @strFileName + '.bmp'  
    -- set @strFilePath = 'ftp://192.168.1.222/' --不可以写入ftp中  
    set @strFilePath = '\\192.168.1.1\qrPic\' --若要修改产生的图片存放的文件夹,修改该字符串.  
    set @strFullFileName = @strFilePath + @strFileName  
  
    EXEC @hr = sp_OACreate '{F5E7BFDD-DE8E-47FB-B009-F9230FFF154A}', @object OUTPUT --创建QRBarCode OLE 对象实例  
    if @hr <> 0   
    begin  
       EXEC sp_OAGetErrorInfo @object  
       return ''  
    end  
  
    --初始化属性      
    EXEC @hr = sp_OASetProperty @object, 'nLevel', 2            -- 纠错等级  
    IF @hr <> 0 RETURN ''  
    EXEC @hr = sp_OASetProperty @object, 'nVersion', 1          -- 版本号    
    IF @hr <> 0 RETURN ''  
    EXEC @hr = sp_OASetProperty @object, 'nMaskingNo', 7        -- 掩码  
    IF @hr <> 0 RETURN ''  
      
    EXEC @hr = sp_OAMethod @object, 'CreateQRImage', @result OUTPUT, @uc_Data, @strFullFileName  
--     IF @hr <> 0 or @result <> 0 RETURN ''  
    IF @hr <> 0   
        RETURN convert(varchar,@hr)  
    IF @result <> 0   
        RETURN convert(varchar,@result)  
  
    EXEC @hr = sp_OADestroy @object  
      
    RETURN @strFullFileName  -- 成功后返回外部可访问路径(完整路径).  
  
end