Blobs

Question

Post reply

Blobs

randal.schmidt

SSChasing Mays

Points: 611
More actions
October 18, 2010 at 1:42 pm

#97509

I have a need to update a document in an existing row of a table which is in blob format (it happens to be a pdf file). My process is 1). Check file system for a changed pdf; 2). Get the doc in stream (blob) format; 3). find the corresponding row for that pdf is the detail table. I have looked at all the examples on the net and seem to have the correct syntax. But all columns EXCEPT the blob get updated (the ostream.read line does not generate any error). The blob update is ignored but there are no errors. I am using ADO 2.8 and VBScript 5.6.
Here is the sample code (any help is appreciated):
' This requires a System DSNs named LDRPS
'========================================
filename = "dynamics1.pdf"
Set wshShell = CreateObject("WScript.Shell")
Set conn = CreateObject("ADODB.Connection")
Set wshShell = CreateObject("WScript.Shell")
Set conn2 = CreateObject("ADODB.Connection")
Const adTypeBinary = 1
Const adSaveCreateOverWrite = 2
' Make sure the registry entry exists
currentDirectory = Left(WScript.ScriptFullName,(Len(WScript.ScriptFullName)) - (Len(WScript.ScriptName)))
' add the entry or overwrite it
wshShell.Run "regedit /s """ & "\\tqclfs2\shared\ITOps\NOTES\Software\ODBC\LDRPS.reg" & """", 0, True
' open the db connection
conn.open "LDRPS","sa","password"
conn2.open "LDRPS","sa","password"
ssql = "SELECT fkDocObject " _
& "FROM dbo.[Document] " _
& "WHERE (fileName = N'" & filename & "') "
Set rs = CreateObject("ADODB.Recordset")
Set ors = CreateObject("ADODB.Recordset")
rs.Open sSQL, conn, 3, 3
' read the record set
On Error goto 0
Do until rs.eof
For Each tmp In rs.Fields
' this field is our key to the docobjects table
If tmp.name = "fkDocObject" Then
doc_object = tmp.value
conn2.execute("USE ldrps10")
ssql2 = "select * from dbo.DocObject WHERE (pkDocObject =" & doc_object & ")"
oRS.Open sSQL2, Conn2, 1, 3
Set oStream = CreateObject("ADODB.Stream")
oStream.Type = adTypeBinary
oStream.Open
' get the pdf file to update
oStream.LoadFromFile "\\tqclfs2\shared\itops\BCP_Input\" & filename
' put new values in the columns
oRS.Fields("value").value = oStream.Read
ors.Fields("dateModified") = CDate(Now)
'
oStream.Close
Set oStream = Nothing
' update the table row
oRS.Update
oRS.Close
End If
Next
Set oRS = Nothing
rs.movenext
Loop
rs.Close
conn.Close
Set conn = Nothing

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

shouldn't that be oRS.Fields("value").value = oStream.ReadAll, not just oRS.Fields("value").value = oStream.Read?

the rest looks ok to me, i think....been a little while since i did vb6.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

randal.schmidt SSChasing Mays Points: 611 More actions · Answer 2

Nope. Making it is ReadAll results in a Object doesn't support this property or method: 'oStream.ReadAll'.

This is VBScript (interpreted) and not VB6 because that might make a difference. I know that blobs may contain a length value or data pointer inside the blob itself. I am not sure if my retrieval method will mess that up or prevent the column from being written. I put a debug line in the code to write the ostream.read to a file. I am able to open the file (a pdf) just fine (like cloning a file!). So I know the ostream.read has correct data in it. It just won't update the SQL column in the table like other field types.

randal.schmidt SSChasing Mays Points: 611 More actions · Answer 3

Is there a place I can find any error that may be occurring? I see nothing in the SQL logs. and VBScript does not generate any error, either.