how to update binary field types in ADO

Hi,

Having some difficulties updating binary fields. This bit of code
seems to work fine in VB6, but when I make the minor changes necessary
for it to run in VBscript the Update never happens.

In fact the assigment to fld.Value doesn't appear to happen since
msgbox fld.Value(0) after the assignment does nothing. Is this
something that is beyong the capabilities of VBscript?

Thank You!


Dim arrBin(7) As Byte
Dim b As Integer
Dim strBin As String
strBin = "bintest"

For b = 1 To 7
arrBin(b - 1) = Asc(Mid(strBin, b, 1))
Next

While Not oRs.EOF
For Each fld In oRs.Fields
lngtype = fld.Type
Select Case lngtype
Case adBinary, adVarBinary, adLongVarBinary
fld.Value = arrBin
oRs.Update
End Select
Next
oRs.MoveNext
Wend

Richard
Tue Feb 27 22:50:34 CST 2007


<everymn@yahoo.com> wrote in message
news:qsv9u2ptdf6hcfpjqhiftd2lhq1n1ukv1i@4ax.com...
> Hi,
>
> Having some difficulties updating binary fields. This bit of code
> seems to work fine in VB6, but when I make the minor changes necessary
> for it to run in VBscript the Update never happens.
>
> In fact the assigment to fld.Value doesn't appear to happen since
> msgbox fld.Value(0) after the assignment does nothing. Is this
> something that is beyong the capabilities of VBscript?
>
> Thank You!
>
>
> Dim arrBin(7) As Byte
> Dim b As Integer
> Dim strBin As String
> strBin = "bintest"
>
> For b = 1 To 7
> arrBin(b - 1) = Asc(Mid(strBin, b, 1))
> Next
>
> While Not oRs.EOF
> For Each fld In oRs.Fields
> lngtype = fld.Type
> Select Case lngtype
> Case adBinary, adVarBinary, adLongVarBinary
> fld.Value = arrBin
> oRs.Update
> End Select
> Next
> oRs.MoveNext
> Wend

You cannot type variables in VBScript, so the As keyword is invalid. The
problem is that VBScript cannot declare byte arrays. VBScript can read them,
but not create or modify them.

--
Richard Mueller
Microsoft MVP Scripting and ADSI
Hilltop Lab - http://www.rlmueller.net
--

Richard
Tue Feb 27 23:03:43 CST 2007

One technique suggested by Paul Randall works in some cases. He writes a hex
value to an ADO stream, then reads it back as a byte array. This may help
you:
===============
Function HexStrToOctet(strInput)
' Function to convert hex string to an OctetString (byte array).
' Based on a program by Paul Randall, October, 2002.

Dim orginalLocale, adoStream

' Retain original locale setting.
originalLocale = SetLocale(1033)

Set adoStream = CreateObject("ADODB.Stream")

' Write hex string to stream.
adoStream.Type = 2
adoStream.Charset = "x-ansi"
adoStream.Open
adoStream.WriteText strInput, 0

' Read stream as byte array.
adoStream.Position = 0
adoStream.Type = 1
HexStrToOctet = adoStream.Read

' Restore original locale setting.
SetLocale(originalLocale)
End Function

--
Richard Mueller
Microsoft MVP Scripting and ADSI
Hilltop Lab - http://www.rlmueller.net
--

everymn
Wed Feb 28 01:15:50 CST 2007

Richard,
Thanks yes of this I was aware. In the script versions I was omitting
the typing. I wasn't aware that you couldn't create byte arrays
though. I thought that the variant arrays would take the form of
whatever was inserted into them.
Thanks

>
>You cannot type variables in VBScript, so the As keyword is invalid. The
>problem is that VBScript cannot declare byte arrays. VBScript can read them,
>but not create or modify them.
>
>--
>Richard Mueller
>Microsoft MVP Scripting and ADSI
>Hilltop Lab - http://www.rlmueller.net

everymn
Wed Feb 28 02:03:03 CST 2007

Richard,
Interesting idea. I'm a bit confused by this though. Why is it
called hex conversion? It's taking plain text as input right?
adoStream.Type = 2 is adTypeText. Is text stored as a hex array?

Although the stream converson is a handy idea, I'm not sure how I'd go
about getting it into fld.Value? I took a leap and tried a direct
assignment which went nowhere.

Also out of curiousity what am I ending up with when I do this in
VBscript?

Dim strBin, b, arrBin(7)
strBin = "bintest"
For b = 0 To 6
arrBin(b) = Asc(Mid(strBin, b+1, 1))
Next

Have I not created an array of bytes? I can read them in, I can read
them out. I just can't seem to assign it to fld.Value, when that
property happens to be a byte array. I gather an array of bytes is
fundamentally different from a byte array.

Just thinking out loud but is there a way I can compile something in
VB6 that I could call from VBscript to help me with this bit? These
binary fields are part of a much larger record so I'd need some way to
pass the record into VB and then get it back into script.

Thanks
Eric


>One technique suggested by Paul Randall works in some cases. He writes a hex
>value to an ADO stream, then reads it back as a byte array. This may help
>you:
>===============
>Function HexStrToOctet(strInput)
> ' Function to convert hex string to an OctetString (byte array).
> ' Based on a program by Paul Randall, October, 2002.
>
> Dim orginalLocale, adoStream
>
> ' Retain original locale setting.
> originalLocale = SetLocale(1033)
>
> Set adoStream = CreateObject("ADODB.Stream")
>
> ' Write hex string to stream.
> adoStream.Type = 2
> adoStream.Charset = "x-ansi"
> adoStream.Open
> adoStream.WriteText strInput, 0
>
> ' Read stream as byte array.
> adoStream.Position = 0
> adoStream.Type = 1
> HexStrToOctet = adoStream.Read
>
> ' Restore original locale setting.
> SetLocale(originalLocale)
>End Function
>
>--
>Richard Mueller
>Microsoft MVP Scripting and ADSI
>Hilltop Lab - http://www.rlmueller.net

Paul
Wed Feb 28 18:18:42 CST 2007


<everymn@yahoo.com> wrote in message
news:e7cau21at1b57l9fv5hvdedqq8j5h2jmkj@4ax.com...
> Richard,
> Interesting idea. I'm a bit confused by this though. Why is it
> called hex conversion? It's taking plain text as input right?
> adoStream.Type = 2 is adTypeText. Is text stored as a hex array?
>
> Although the stream converson is a handy idea, I'm not sure how I'd go
> about getting it into fld.Value? I took a leap and tried a direct
> assignment which went nowhere.
>
> Also out of curiousity what am I ending up with when I do this in
> VBscript?
>
> Dim strBin, b, arrBin(7)
> strBin = "bintest"
> For b = 0 To 6
> arrBin(b) = Asc(Mid(strBin, b+1, 1))
> Next
>
> Have I not created an array of bytes? I can read them in, I can read
> them out. I just can't seem to assign it to fld.Value, when that
> property happens to be a byte array. I gather an array of bytes is
> fundamentally different from a byte array.
>
> Just thinking out loud but is there a way I can compile something in
> VB6 that I could call from VBscript to help me with this bit? These
> binary fields are part of a much larger record so I'd need some way to
> pass the record into VB and then get it back into script.
>
> Thanks
> Eric

Hi, Eric
I've been trying to understand the term 'byte array' for years. Perhaps
in some languages, it is an array of bytes whose elements can be
accessed as barray(n), but VBScript's variant data type doesn't
allow that kind of access. Look at the the 'B' versions of VBScript's
string functions in the scripting help file. For example, in the info for
the Mid function, you will find:

The MidB function is used with byte data contained in a string. Instead
of specifying the number of characters, the arguments specify numbers
of bytes.

Googleing the scripting groups for MidB may turn up some scripts that use
the 'B' versions of the string functions, but I find them confusing, which
is why I came up with the ADODB.Stream method of converting
between 'byte arrays' and strings. For all but very short strings, I think
the
stream method is also much faster.

-Paul Randall

everymn
Wed Feb 28 18:58:31 CST 2007

Paul,

Yes I've used the b functions previously for reading byte arrays from
ADO. It's no problem to assign a field.value byte array to a variable
in VBscript, however I haven't figure out a way to do the reverse.
Have you been able to use your stream method to accomplish this?
Thanks
Eric


>
>Hi, Eric
>I've been trying to understand the term 'byte array' for years. Perhaps
>in some languages, it is an array of bytes whose elements can be
>accessed as barray(n), but VBScript's variant data type doesn't
>allow that kind of access. Look at the the 'B' versions of VBScript's
>string functions in the scripting help file. For example, in the info for
>the Mid function, you will find:
>
>The MidB function is used with byte data contained in a string. Instead
>of specifying the number of characters, the arguments specify numbers
>of bytes.
>
>Googleing the scripting groups for MidB may turn up some scripts that use
>the 'B' versions of the string functions, but I find them confusing, which
>is why I came up with the ADODB.Stream method of converting
>between 'byte arrays' and strings. For all but very short strings, I think
>the
>stream method is also much faster.
>
>-Paul Randall
>

Paul
Wed Feb 28 20:40:20 CST 2007


<everymn@yahoo.com> wrote in message
news:a39cu2dicrebkenipgugvei0r0oqvmalbe@4ax.com...
> Paul,
>
> Yes I've used the b functions previously for reading byte arrays from
> ADO. It's no problem to assign a field.value byte array to a variable
> in VBscript, however I haven't figure out a way to do the reverse.
> Have you been able to use your stream method to accomplish this?
> Thanks
> Eric

I don't use ADO or byte arrays much, and haven't tried it.

Have you tried something like:
objADOField.Value = adoStream.Read

Hopefully this will not need a variant to be the middleman between the
stream and your ADO object.

-Paul Randall