How to SQL insert a raw/Binary field value from bytes/integer array using VBScript (orVB6)?

Question

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)?

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!)

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.

First option: using the string format I have had some (limited) success creating the SQL insert as:

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 Array I 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:

x = a(0)

And continues to work for 4 bytes, but fails when the Integer overflows. Also, it reorders the data

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.

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.

I've googled till I'm numb and havn't not found much atall relevant to binary fields in SQL.

Can you help? Any answers appreciated. Many thx.

Answer 1

VB6 can access binary columns with the GetChunk and AppendChunk methods of the ADO field class. See this KB article.

This blog post has a function to convert a hex string to a 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

Answer 2

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.

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

Answer 3

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.

'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$

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