|
Home > Archive > MS SQL Server ODBC > August 2005 > Troubles pulling binary data into VBScript
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
Troubles pulling binary data into VBScript
|
|
| maximillianx 2005-08-05, 8:23 pm |
| I'm a normal resident of the vbscript/wsh/server scripting forums, as I'm
more of a scripter, and don't know word one about SQL...so here I am - :)
I'm attempting to query a MS WSUS database stored on our SQL server (SQL
2000) - one of the two fields I am querying is reporting back fine (the
filename field). However, when I query the filedigest field (I believe it's
a binary field), it comes back as garbage. How could I modify this code so
I can convert this garbage into a legible string?
Does this require converting the value via the SQL query itself?
When I run the same query through MSQuery, it comes back with a string of
numbers...which is correct. I'm not sure how MSQuery is different in what
it is doing to pull the values as compared to my script...I'm at a total
loss.
Here's what I have so far (SQL query inside of a VBScript)
------------
On Error GoTo 0
set myconn = CreateObject("adodb.connection")
connection = "Provider=SQLOLEDB;" & _
"Data Source=MYSQLSERVER;" & _
"Initial Catalog=SUSDB;" & _
"Integrated Security=SSPI"
myconn.open (connection)
set result = CreateObject("adodb.recordset")
SQL = "SELECT FileDigest,FileName from tbFile WHERE (tbFile.FileName Like
'%889101%' And tbFile.FileName Like '%enu%')"
set result = myconn.execute(SQL)
if not result.EOF then
while not result.EOF
MsgBox result("filename")
MsgBox result("filedigest")
result.movenext()
wend
Else
End if
---------------------
Thanks for any help...
Rob
| |
|
| Garbage, you say? Can you post an example? Seeing some DDL and sampe data
would e nice.
ML
| |
| maximillianx 2005-08-08, 11:23 am |
| I'll apologize for my ignorance right away - I'm not sure what DDL is:
However, upon first run, the data I receive looks like this:
results("filedigest") = ?????????? - - and depending on what character set
you have installed, this might look different than what I'm actually seeing.
Upon first glance, the data returned via VBScript appears to be a series of
boxes.
My original SQL statement was this (this started the whole problem):
"SELECT * from tbFile WHERE (tbFile.FileName Like '%889101%' And
tbFile.FileName Like '%enu%')"
When I run the following SQL query string (given to me by our SQL guy):
SQL = "SELECT CAST(FileDigest AS VARCHAR(30)) as
testrob,FileName,CON
VERT(varchar(30), filedigest) as testrob2,filedigest
from tbFile WHERE (tbFile.FileName Like '%889101%' And tbFile.FileName Like
'%enu%')"
I get this:
testrob = Fµeí»~·$Vl.ËYê!?GÙ
The actual string should be:
46B565EDBB9811B72456
6C95CB9FEA213F47D909
When I run the following query in MSQuery, it returns the proper results:
SELECT tbFile.FileDigest, tbFile.FileName
FROM SUSDB.dbo.tbFile tbFile
WHERE (tbFile.FileName Like '%889101%' And tbFile.FileName Like '%enu%')
I realize the SQL query is slightly different, but my thinking was because
of the way MSQuery pulls the data in comparison to how a VBScript (or
really, ASP) would pull it...
I hope this helps explain my problem... again, many apoligies - like I said,
I'm more of a scripting guy than a SQL guy...
Thanks for your attention -
Rob
"ML" <ML@discussions.microsoft.com> wrote in message
news:66895AB6-BD0F-48E9-A3FD- 8EBE0FF0B0B7@microso
ft.com...
> Garbage, you say? Can you post an example? Seeing some DDL and sampe data
> would e nice.
>
>
> ML
| |
| maximillianx 2005-08-08, 8:23 pm |
| I figured this would happen - where it says ????????? below actually should
have shown outlined boxes (similar to when you have a character typed, but
there is no font value for that character)...
"maximillianx" < u1p2h3o4l5d2001@hotm
ail.com> wrote in message
news:um5e%23FDnFHA.2484@TK2MSFTNGP15.phx.gbl...
> I'll apologize for my ignorance right away - I'm not sure what DDL is:
>
> However, upon first run, the data I receive looks like this:
>
> results("filedigest") = ?????????? - - and depending on what character set
> you have installed, this might look different than what I'm actually
> seeing. Upon first glance, the data returned via VBScript appears to be a
> series of boxes.
>
> My original SQL statement was this (this started the whole problem):
> "SELECT * from tbFile WHERE (tbFile.FileName Like '%889101%' And
> tbFile.FileName Like '%enu%')"
>
> When I run the following SQL query string (given to me by our SQL guy):
> SQL = "SELECT CAST(FileDigest AS VARCHAR(30)) as
> testrob,FileName,CON
VERT(varchar(30), filedigest) as testrob2,filedigest
> from tbFile WHERE (tbFile.FileName Like '%889101%' And tbFile.FileName
> Like '%enu%')"
>
> I get this:
>
> testrob = Fµeí»~·$Vl.ËYê!?GÙ
>
> The actual string should be:
> 46B565EDBB9811B72456
6C95CB9FEA213F47D909
>
> When I run the following query in MSQuery, it returns the proper results:
> SELECT tbFile.FileDigest, tbFile.FileName
> FROM SUSDB.dbo.tbFile tbFile
> WHERE (tbFile.FileName Like '%889101%' And tbFile.FileName Like '%enu%')
>
> I realize the SQL query is slightly different, but my thinking was because
> of the way MSQuery pulls the data in comparison to how a VBScript (or
> really, ASP) would pull it...
>
> I hope this helps explain my problem... again, many apoligies - like I
> said, I'm more of a scripting guy than a SQL guy...
>
> Thanks for your attention -
>
> Rob
>
>
> "ML" <ML@discussions.microsoft.com> wrote in message
> news:66895AB6-BD0F-48E9-A3FD- 8EBE0FF0B0B7@microso
ft.com...
>
>
| |
|
| If you cast binary data as a character data type, then this is expected
behaviour. Don't cast.
What exactly are you trying to achieve?
ML
| |
| maximillianx 2005-08-08, 8:23 pm |
| Inside of the WSUS database, the binary data contains a string where
& #91;filename]Windows
Server2003-KB889101-SP1-Express-x86-ENU.exe =
& #91;filedigest]46B56
5EDBB9811B724566C95C
B9FEA213F47D909
The .exe name is converted into a numerical value
(46B565EDBB9811B7245
66C95CB9FEA213F47D9)
and the last two digits denotes the
directory location where the file resides - i.e. in the WSUS\Content folder
(09) - for example, this update would be stored as
D:\WSUS\Content\09\4
6B565EDBB9811B724566
C95CB9FEA213F47D9.exe.
My hope was to generate a legible listing of updates and their source path
on my WSUS server. By using this list, I was going to generate a table with
a select box so a person could select a grouping of updates, then copy them
to another location (but my script would copy the files to the destination
share/folder using the actual filename, not the numerical one)...
Unfortunately, I don't know anything about the CAST or CONVERT command,
etc. - would you recommend a different command syntax to convert the data
type into, say, string? If so, I may need a little pointer on doing this
properly.
My first inclination was to simply find the code to convert to binary, but
this has led me to similar results. When my SQL admin helped me out, I
didn't even think that I'd have to convert the data in the SQL string rather
than convert it when it was returned to the script...or does this matter as
much as I think it does?
Thanks ML -
Rob
"ML" <ML@discussions.microsoft.com> wrote in message
news:877DF5EF-90DE-48D3-8A8B- EB07C6841564@microso
ft.com...
> If you cast binary data as a character data type, then this is expected
> behaviour. Don't cast.
>
> What exactly are you trying to achieve?
>
>
> ML
| |
| maximillianx 2005-08-08, 8:23 pm |
| "maximillianx" < u1p2h3o4l5d2001@hotm
ail.com> wrote in message
news:OniNDiFnFHA.1372@TK2MSFTNGP10.phx.gbl...
> Inside of the WSUS database, the binary data contains a string where
> & #91;filename]Windows
Server2003-KB889101-SP1-Express-x86-ENU.exe =
> & #91;filedigest]46B56
5EDBB9811B724566C95C
B9FEA213F47D909...
> My first inclination was to simply find the code to convert to binary, but
> this has led me to similar results.
I'm sorry, I meant convert -from- binary.
Rob
| |
|
| Is this value stored in a column of either binary, varbinary or image data
type? If not, then there's no need to cast/convert it to character type.
ML
| |
| maximillianx 2005-08-09, 11:23 am |
| The values are stored in a column of binary type, and I couldn't quite
figure out why VBScript wouldn't produce the results in a readable
formate...
Very strange - am I crazy, or should this be a relatively simple thing to
perform?
"ML" <ML@discussions.microsoft.com> wrote in message
news:E9D94E19-E6BA-414A-BC30- 9333BF1EA830@microso
ft.com...
> Is this value stored in a column of either binary, varbinary or image data
> type? If not, then there's no need to cast/convert it to character type.
>
>
> ML
| |
|
| Do you need the entire value in VBScript - you've mentioned that you need to
parse it in order to isolate parts with different meaning. Maybe you could do
all that on the server before you return the data to the client.
ML
| |
| Craig Kelly 2005-08-10, 3:23 am |
| "maximillianx" wrote:
> The values are stored in a column of binary type, and I couldn't quite
> figure out why VBScript wouldn't produce the results in a readable
> formate...
>
> Very strange - am I crazy, or should this be a relatively simple thing to
> perform?
Well, I'm no psychologist, but no you're not crazy and yes it should be
easy. However, VBScript doesn't handle byte arrays (which is what the
recordset is returning for the filedigest field). Here's something I hacked
together...
Function ConvertByteArray(arr
)
Dim str
Dim ln
Dim i
Dim ret
str = CStr(arr)
ln = LenB(str)
ret = ""
For i = 1 To ln
ret = ret & Right("00" & Hex(AscB(MidB(str, i, 1))), 2)
Next
ConvertByteArray = ret
End Function
So that you could change your line
MsgBox result("filedigest")
to
MsgBox ConvertByteArray(res
ult("filedigest"))
However, when testing vs our WSUS DB (which I've never even messed with, so
who knows :), it appears that a leading zero is showing up, so your
46B565EDBB9811B72456
6C95CB9FEA213F47D909
would come out
0046B565EDBB9811B724
566C95CB9FEA213F47D9
09
Obviously, caveat coder and YMMV... (If it were me and I had to use VBScript
for this, I'd write a COM object for processing or a command line program to
output a file I could read in VBScript, but to each his own.)
Craig
| |
| maximillianx 2005-08-10, 11:24 am |
| Craig - Whoa - a HUGE thank you is in order!
This is exactly what I needed...and I didn't have to go the way of stored
procedures (or any of that other stuff I don't know how to work with) - - -
This problem stumped our SQL guys as well, so I'll be sure to forward this
to them for the next time they/I run into this problem.
I may post this code on my website, but I'll be sure to credit the source...
Thanks again Craig!
Rob
"Craig Kelly" <cnkelly@spamnomore.worldnet.att.net> wrote in message
news:8VdKe.83039$5N3.60611@bgtnsc05-news.ops.worldnet.att.net...
> "maximillianx" wrote:
>
>
> Well, I'm no psychologist, but no you're not crazy and yes it should be
> easy. However, VBScript doesn't handle byte arrays (which is what the
> recordset is returning for the filedigest field). Here's something I
> hacked together...
>
> Function ConvertByteArray(arr
)
> Dim str
> Dim ln
> Dim i
> Dim ret
>
> str = CStr(arr)
> ln = LenB(str)
> ret = ""
>
> For i = 1 To ln
> ret = ret & Right("00" & Hex(AscB(MidB(str, i, 1))), 2)
> Next
>
> ConvertByteArray = ret
> End Function
>
> So that you could change your line
>
> MsgBox result("filedigest")
>
> to
>
> MsgBox ConvertByteArray(res
ult("filedigest"))
>
> However, when testing vs our WSUS DB (which I've never even messed with,
> so who knows :), it appears that a leading zero is showing up, so your
>
> 46B565EDBB9811B72456
6C95CB9FEA213F47D909
>
> would come out
>
> 0046B565EDBB9811B724
566C95CB9FEA213F47D9
09
>
> Obviously, caveat coder and YMMV... (If it were me and I had to use
> VBScript for this, I'd write a COM object for processing or a command line
> program to output a file I could read in VBScript, but to each his own.)
>
> Craig
>
>
| |
|
| I have found a web page solution for this issue; you can find this on
the following link.
http://wsus.collewijn.info/main.php...fixinfo_enu.php
Unfortunately is this the only page that's in English (the rest of
the web site is still under construction and in Dutch).
Robert
|
|
|
|
|