代码之家 › 专栏 › 技术社区 › Ashish Chaurasia

如何使用VBScript(或VB6)从字节/整数数组中SQL插入原始/二进制字段值?

ado binary vbscript vb6 sql

Ashish Chaurasia · 技术社区 · 17 年前

有人知道如何使用SQL和ADO(经典)将几个字节传递到Binary(或varbinary)字段中吗;VBScript(还是Visual Basic 6)?

字节可以是通过AscB/ChrB获得的字节字符串,也可以是“字节”数组(实际上是通过“cbyte”获得的字节类型的变体)并存储在数组中。

x = ".>?hD-&91k[="    '<psuedo string of chars, some unprintable
Insert Into rawtest (byt) Values (CAST('" & x & "' as SQL_BINARY(12)))

第二种选择:字节数组 我可以很容易地将数据放入数组中,但看不到如何传递给SQLInsert语句。如果我尝试传入12个字节,由于CAST试图将数据存储为Integer(4字节),插入失败。如果我传入一个字节,它就会工作,例如:

x = a(0)

并继续工作4个字节,但当Integer溢出时失败。此外,它还会对数据进行重新排序

Insert Into rawtest (byt) Values (CAST('12,34,45' as SQL_BINARY(12)))
Insert Into rawtest (byt) Values (CAST(&h12&h34 as SQL_BINARY(12)))
Insert Into rawtest (byt) Values (CAST(0x123456789012 as SQL_BINARY(12)))

我也尝试过类似的组合:

Insert Into rawtest (byt) Values (CONVERT('" & x & "', SQL_BINARY)

但这些都失败了!

理想情况下,我需要在VBScript/ADO中完成此操作,但如果可用,可以采用基于VB6的解决方案。我希望它是“原始”二进制文件,我不想使用ascii编码,比如Base64。

我一直在谷歌上搜索,直到我麻木了,还没有找到太多与SQL中的二进制字段相关的东西。

你能帮忙吗?任何答案都很感激。非常感谢

3 回复 | 直到 15 年前

Matt Spradley 17 年前

Dim vntBlobData As Variant
vntBlobData = "ReplaceThisWithBinaryData - A byte array will work"

Dim cn As ADODB.Connection
Set cn = New ADODB.Connection
cn.ConnectionString = "Provider = sqloledb; Data Source = DBServerName; Initial Catalog = DBName; User ID = UserID; Password = Password; Persist Security Info = False"
cn.Open

Dim strQry As String
strQry = "INSERT INTO TestBinaryTable (ID, BlobData) VALUES (?, ?)"

Dim cm As ADODB.Command
Set cm = New ADODB.Command
cm.ActiveConnection = cn
cm.CommandText = strQry
cm.Parameters.Append cm.CreateParameter("@ID", adInteger, adParamInput, , 1)
cm.Parameters.Append cm.CreateParameter("@BlobData", adVarBinary, adParamInput, 100, vntBlobData)
cm.CommandType = adCmdText
cm.Execute

Andomar 17 年前

VB6可以使用ADO字段类的GetChunk和AppendChunk方法访问二进制列。看这个 KB article .

blog post

CREATE FUNCTION dbo.HexStrToVarBinary(@hexstr varchar(8000))
RETURNS varbinary(8000)
AS
BEGIN 
    DECLARE @hex char(1), @i int, @place bigint, @a bigint
    SET @i = LEN(@hexstr) 

    set @place = convert(bigint,1)
    SET @a = convert(bigint, 0)

    WHILE (@i > 0 AND (substring(@hexstr, @i, 1) like '[0-9A-Fa-f]')) 
     BEGIN 
        SET @hex = SUBSTRING(@hexstr, @i, 1) 
        SET @a = @a + 
    convert(bigint, CASE WHEN @hex LIKE '[0-9]' 
         THEN CAST(@hex as int) 
         ELSE CAST(ASCII(UPPER(@hex))-55 as int) end * @place)
    set @place = @place * convert(bigint,16)
        SET @i = @i - 1

     END 

    RETURN convert(varbinary(8000),@a)
END

Ashish Chaurasia 17 年前

20亿越南比索:

Function a2b(x)
    For i = 1 To Len(x)+1 Step 2
        d = Mid(x, i, 2)
        a2b = a2b & chrb(CByte(d))
    Next
End Function

VBS Bin2$

Function eb2s(c)
    If IsNull(c) Then
        eb2s = ""
    else
        For i = 1 To lenb(c)
        eb2s = eb2s & ascb(Midb(c, i, 1))
        Next
    End if
End Function