Advertisement

09.03.2008 at 08:11AM PDT, ID: 23699528 | Points: 125
[x]
Attachment Details

how to start writing a binary variable to a file from a certain offset?

Asked by jv01ls1 in Visual Basic Programming

Tags: , ,

I am trying to extract a BLOB from an "image" data type in SQL Server by using VBA in Access 2003, or calling the code with cscript.  

The reason I am doing this in VBA is that the data in the image data type was originally inserted using an OLE object in Access 2003.

I now want to extract that data to files and replace the OLE object with a button that opens the folder with the files in it (this I know how to do).  

My problem is that Access inserted data before the actual binary data that I need, and I want to strip this data off.  

I was thinking of replacing line 115 below:

fs.write rs(1)

with

fs.write mid(rs(1),100)

to start writing the binary data into the buffer at position 100, but the mid function is only for text strings.  

Is there a similar function for binary data?

Thanks!Start Free Trial 
1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30:
31:
32:
33:
34:
35:
36:
37:
38:
39:
40:
41:
42:
43:
44:
45:
46:
47:
48:
49:
50:
51:
52:
53:
54:
55:
56:
57:
58:
59:
60:
61:
62:
63:
64:
65:
66:
67:
68:
69:
70:
71:
72:
73:
74:
75:
76:
77:
78:
79:
80:
81:
82:
83:
84:
85:
86:
87:
88:
89:
90:
91:
92:
93:
94:
95:
96:
97:
98:
99:
100:
101:
102:
103:
104:
105:
106:
107:
108:
109:
110:
111:
112:
113:
114:
115:
116:
117:
118:
119:
120:
121:
122:
123:
124:
125:
126:
127:
128:
129:

Option explicit
'On Error Resume Next
 
'---- DataTypeEnum Values ----
Const adEmpty = 0
Const adTinyInt = 16
Const adSmallInt = 2
Const adInteger = 3
Const adBigInt = 20
Const adUnsignedTinyInt = 17
Const adUnsignedSmallInt = 18
Const adUnsignedInt = 19
Const adUnsignedBigInt = 21
Const adSingle = 4
Const adDouble = 5
Const adCurrency = 6
Const adDecimal = 14
Const adNumeric = 131
Const adBoolean = 11
Const adError = 10
Const adUserDefined = 132
Const adVariant = 12
Const adIDispatch = 9
Const adIUnknown = 13
Const adGUID = 72
Const adDate = 7
Const adDBDate = 133
Const adDBTime = 134
Const adDBTimeStamp = 135
Const adBSTR = 8
Const adChar = 129
Const adVarChar = 200
Const adLongVarChar = 201
Const adWChar = 130
Const adVarWChar = 202
Const adLongVarWChar = 203
Const adBinary = 128
Const adVarBinary = 204
Const adLongVarBinary = 205
Const adChapter = 136
Const adFileTime = 64
Const adDBFileTime = 137
Const adPropVariant = 138
Const adVarNumeric = 139
Const adTypeText = 2
Const adTypeBinary = 1
 
'---- CommandTypeEnum Values ----
Const adCmdText = &H0001
Const adCmdStoredProc = &H0004
 
'---- ParameterDirectionEnum Values ----
Const adParamInput = &H0001
Const adParamOutput = &H0002
 
'---- SaveOptionsEnum Values ----
Const adSaveCreateNotExist = 1
Const adSaveCreateOverWrite = 2
 
 
Dim fso, outFile, fs
Dim conn
Dim rs
Dim objArgs
Dim queryStr
Dim oerr
 
WScript.Echo "BLOB extractor using SQL Server Provider for OLE-DB"
Set objArgs = WScript.Arguments
If (objArgs.Count <> 4) Then
  WScript.Echo "Syntax:"
  WScript.Echo "  CScript.exe SampleBlobExtractor.vbs <database> <server> <userid> <password>"
  WScript.Echo
  WScript.Echo "Note:"
  WScript.Echo "  Windows Integrated Security (Trusted Connection) will be used"
  WScript.Echo "  if the <userid> and <password> params are empty strings."
  WScript.Quit
End If
 
Set conn = CreateObject("ADODB.Connection")
conn.Provider = "sqloledb"
conn.Properties("Data Source").Value = objArgs(1)
If objArgs(2) = "" Then
  conn.Properties("Integrated Security").Value = "SSPI"
Else
  conn.Properties("User ID").Value = objArgs(2)
  conn.Properties("Password").Value = objArgs(3)
End If
conn.Properties("Initial Catalog").Value = objArgs(0)
 
conn.Open
 
Set rs = CreateObject("ADODB.Recordset")
queryStr = "SELECT CAST(SERVERPROPERTY('MachineName') AS VARCHAR(30)) + '\' + CAST(ISNULL(SERVERPROPERTY('InstanceName'), '') AS VARCHAR(30)), CURRENT_USER"
rs.Open queryStr, conn
WScript.Echo "Successful connection to SQL Server instance [" & rs(0) & "] as user [" & rs(1) & "]"
 
rs.Close
Set rs = Nothing
 
' Output file
Set fso = CreateObject("Scripting.FileSystemObject")
 
Set fs = CreateObject("ADODB.Stream")
fs.Type = adTypeBinary
 
Set rs = CreateObject("ADODB.Recordset")
queryStr = "SELECT QUOTENUMBER, EMBEDDEDOBJECT from quotes where quotenumber='070464'"
rs.Open queryStr, conn
  Do Until rs.EOF
    outFile = rs(0) & "." & "PDF"
    WScript.Echo "Extracting " & outFile
 
    fs.Open
    fs.Write rs(1)
    fs.SaveToFile outFile, adSaveCreateOverWrite
    fs.Close
 
    rs.MoveNext
  Loop
rs.Close
Set rs = Nothing
 
conn.Close
Set conn = Nothing
 
Set fs = Nothing
 
Set fso = Nothing
 
Keywords: how to start writing a binary variable t…
Title
1 Export Word Documents from BLOB or …
2 Assingin an OLE object to a varient
3 ASP BLOB help
4 CLOB to BLOB
5 Loading BMP's to an OLE field
6 Insert Into Blob field
 
Loading Advertisement...
 
[+][-]09.03.2008 at 10:55AM PDT, ID: 22379834

At Experts Exchange, members can ask their questions to thousands of technology professionals, also known as Experts. Experts compete and collaborate to answer those questions by leaving comments like this one.

Start your 14-day free trial to view this Expert Comment or ask the Experts your question.

 
[+][-]09.03.2008 at 11:01AM PDT, ID: 22379921

Often, when Experts are collaborating with members who have asked questions, they will request additional information about the problem. Askers respond with an author comment like this one.

Start your 14-day free trial to view this Author Comment or ask the Experts your question.

 
[+][-]09.03.2008 at 11:11AM PDT, ID: 22380081

At Experts Exchange, members can ask their questions to thousands of technology professionals, also known as Experts. Experts compete and collaborate to answer those questions by leaving comments like this one.

Start your 14-day free trial to view this Expert Comment or ask the Experts your question.

 
[+][-]10.02.2008 at 07:13AM PDT, ID: 22624410

Often, when Experts are collaborating with members who have asked questions, they will request additional information about the problem. Askers respond with an author comment like this one.

Start your 14-day free trial to view this Author Comment or ask the Experts your question.

 
[+][-]10.07.2008 at 10:56PM PDT, ID: 22666443

At Experts Exchange, members can ask their questions to thousands of technology professionals, also known as Experts. Experts compete and collaborate to answer those questions by leaving comments like this one.

Start your 14-day free trial to view this Expert Comment or ask the Experts your question.

 
 
Loading Advertisement...
20081112-EE-VQP-43 / EE_QW_2_20070628