如何 SQL 使用 VBScript（或 VB6）从字节/整数数组插入原始/二进制字段值？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/922366/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to SQL insert a raw/Binary field value from bytes/integer array using VBScript (or VB6)?
提问by andora
Does anyone know how to pass a several bytes into a Binary (or varbinary) field using SQL and ideally in ADO (classic) & VBScript (or Visual Basic 6)?
有谁知道如何使用 SQL 并理想地在 ADO（经典）和 VBScript（或 Visual Basic 6）中将几个字节传递到 Binary（或 varbinary）字段中？
I wish to encode 10-20 (maybe more) bytes of data and have this data stored in a SQL db field. (Its not MS-SQLSVR, but I'm hoping that a standard SQL supported format will work!)
我希望编码 10-20（可能更多）字节的数据并将这些数据存储在 SQL db 字段中。（它不是 MS-SQLSVR，但我希望支持标准 SQL 的格式可以工作！）
The bytes are available as either a string of bytes obtained via AscB/ChrB, OR, an array of 'bytes' (actually variants of type byte obtained via 'cbyte') and stored in the array.
字节可以作为通过 AscB/ChrB 获得的字节串或“字节”数组（实际上是通过“cbyte”获得的字节类型的变体）并存储在数组中。
First option: using the string formatI have had some (limited) success creating the SQL insert as:
第一个选项：使用字符串格式我在创建 SQL 插入方面取得了一些（有限的）成功：
x = ".>?hD-&91k[=" '<psuedo string of chars, some unprintable
Insert Into rawtest (byt) Values (CAST('" & x & "' as SQL_BINARY(12)))
But I am concerned that string nulls will truncate the data in the field and other non-printing control characters will get in the way of handling the data. Is there a way to avoid this?
但我担心字符串空值会截断字段中的数据，其他非打印控制字符会妨碍处理数据。有没有办法避免这种情况？
Second Option: Byte ArrayI can put the data in a array easily enough, but cannot see how to pass to the SQL Insert statement. If I attempt to pass in 12 bytes, the insert fails due to the CAST attempting to store the data into a Integer (4bytes). If I pass in a single byte, it works, eg:
第二个选项：字节数组我可以很容易地将数据放入数组中，但看不到如何传递给 SQL Insert 语句。如果我尝试传入 12 个字节，由于 CAST 尝试将数据存储到整数（4 个字节）中，因此插入失败。如果我传入一个字节，它就可以工作，例如：
x = a(0)
And continues to work for 4 bytes, but fails when the Integer overflows. Also, it reorders the data
并继续工作 4 个字节，但在 Integer 溢出时失败。此外，它重新排序数据
I've attempted to use various workarounds:
我尝试使用各种解决方法：
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)))
I've also tried similar combinations with:
我也尝试过类似的组合：
Insert Into rawtest (byt) Values (CONVERT('" & x & "', SQL_BINARY)
But these all fail!
但这些都失败了！
Ideally, I want a method, any method, that takes a small binary-array of upto 20 bytes(ideally full byte range 0-255, but could take less) and passes them thru to a plain, raw, binary SQL field.
理想情况下，我想要一个方法，任何方法，它需要一个最多 20 个字节的小二进制数组（理想情况下是完整的字节范围 0-255，但可能需要更少）并将它们传递到一个普通的、原始的、二进制 SQL 字段。
Ideally I need to do this in VBScript/ADO, but can take a VB6 based solution if available. I want this as 'raw' binary, I don't want to use an ascii-encoding, like Base64.
理想情况下，我需要在 VBScript/ADO 中执行此操作，但可以采用基于 VB6 的解决方案（如果可用）。我希望这是“原始”二进制文件，我不想使用 ascii 编码，例如 Base64。
I've googled till I'm numb and havn't not found much atall relevant to binary fields in SQL.
我一直在谷歌搜索，直到我麻木了，并且没有发现与 SQL 中的二进制字段相关的太多内容。
Can you help? Any answers appreciated. Many thx.
你能帮我吗？任何答案表示赞赏。许多谢谢。
回答by Matt Spradley
Classic ADO can manipulate very large (>8000) varbinary and image (blob) fields directly without chunking. Below is a sample against MS SQL Server that inserts binary data into a table with an INT ID field and a varbinary(100) field. In this example a parameterized SQL query is inserting the binary data from a Variant. The Variant just needs to be populated with the binary data.
经典 ADO 可以直接操作非常大 (>8000) 的 varbinary 和图像 (blob) 字段而无需分块。下面是一个针对 MS SQL Server 的示例，该示例将二进制数据插入到具有 INT ID 字段和 varbinary(100) 字段的表中。在这个例子中，一个参数化的 SQL 查询从一个 Variant 插入二进制数据。Variant 只需要填充二进制数据。
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
回答by Andomar
VB6 can access binary columns with the GetChunk and AppendChunk methods of the ADO field class. See this KB article.
VB6 可以使用 ADO 字段类的 GetChunk 和 AppendChunk 方法访问二进制列。请参阅此知识库文章。
This blog posthas a function to convert a hex string to a varbinary:
这篇博文有一个将十六进制字符串转换为 varbinary 的函数：
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
回答by andora
Matt gave me a lead-in to the solution: Although the target is not a blob field and the bulk of the code indictaes how to pass a string into the db via ADO, the bit I needed was how to create the bytes to feed into the variable 'vntBlobData', by creating a VBS 'byte string' from the source bytes using charb/ascb I got my solution. I now have a VBS solution (and using a proper byte-array, a VB6 solution too!) Many thanks Matt.
Matt 给了我一个解决方案的引导：虽然目标不是 blob 字段并且大部分代码指示如何通过 ADO 将字符串传递到数据库中，但我需要的是如何创建要输入的字节变量“vntBlobData”，通过使用 charb/ascb 从源字节创建 VBS“字节字符串”，我得到了我的解决方案。我现在有一个 VBS 解决方案（并使用适当的字节数组，也是 VB6 解决方案！）非常感谢 Matt。
'VBS $2Bin:
'VBS $2Bin：
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$
'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
