4

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.

Improve this question
1
  • Just to be clear, I'm looking to store raw binary in the table, not as an 'attached' memo binary/blob/image type field. Commented May 28, 2009 at 20:24

3 Answers 3

Reset to default
5

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
Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

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
Improve this answer

1 Comment

Thanks for the suggestion, but not a BLOB field, but plain/raw binary (or could be varbinary) field stored in the table. You've got me thinking thou, perhaps some other ado stream type function might help...
1

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
Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.