How does SQL insert raw / Binary field value from bytes / integer array using VBScript (or VB6)?

Question

Does anyone know how to pass multiple bytes to a binary (or varbinary) field using SQL and ideally in ADO (classic) and VBScript (or Visual Basic 6)?

I want to encode 10-20 (maybe more) bytes of data and store that data in an SQL db field. (This is not MS-SQLSVR, but I hope the standard SQL support format will work!)

Bytes are available as a string of bytes received through AscB / ChrB, OR, an array of bytes (in fact, variants of a byte of the type received through "cbyte") and are stored in the array.

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

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

But I am worried that null lines will truncate the data in the field, and other non-printable control characters will interfere with data processing. Is there any way to avoid this?

Second option: byte array I can easily place data in an array, but I can’t figure out how to get to the SQL Insert statement. If I try to pass in 12 bytes, the insert will fail due to the fact that CAST is trying to save the data in Integer (4 bytes). If I transfer one byte, it works, for example:

x = a(0)

And it continues to work for 4 bytes, but does not work when Integer overflows. In addition, it reorders the data.

I tried using 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 also tried similar combinations with:

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

But all this does not work!

, , , 20 ( 0-255, ) , , SQL .

VBScript/ADO, VB6. , "" , ascii-, Base64.

googled , SQL.

? . .

Answer 1

ADO ( > 8000) varbinary image (blob) chunking. MS SQL Server, INT ID varbinary (100). SQL- 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

Answer 2

VB6 GetChunk AppendChunk ADO. KB.

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 3

Matt gave me a guide to the solution: although the target is not a blob block, and the main part of the code indicates how to pass a string to db via ADO, the bit I need is how to create bytes to feed into the variable 'vntBlobData', by of creating VBS 'byte string from source bytes using charb / ascb, I got my solution. Now I have a VBS solution (and using the correct byte array, VB6 solution too!) Thanks a lot 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