VBScript and SQL databases

[jfish]

Hi

I need some assistance, I have this table in SQL that has the following columns

filename varchar(50)
doc varbinar(max)

whats best approach the save the contents of the doc field locally to a file (all documents will either be PDF or Word documents)

I have managed to write a vbscript to display the contents of the records, obviously the doc filed is garabage as its a varbin - just need to know how to convert this and then save the file.

 

[Phonesis]

What IDE are you using for your VBScript? I saw something in relation to this a few weeks ago for something I was doing and found this solution for VB 6:


----------

Private Sub ByteArrayToPDFConverter_Click()

Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim mstream As ADODB.Stream

Set cn = New ADODB.Connection
cn.Open "Provider=SQLOLEDB;data Source=test;Initial Catalog=test;User Id=_test;Password=test"

Set rs = New ADODB.Recordset
rs.Open "Select document from tbltest where id = 13", cn, adOpenKeyset, adLockOptimistic
Set mstream = New ADODB.Stream
mstream.Type = adTypeBinary
mstream.Open
mstream.Write rs.Fields("document").Value
mstream.SaveToFile "c:\ByteTopdf.pdf", adSaveCreateOverWrite


rs.Close
cn.Close

You would need to modify it quite a bit to work with VBScript

 

[jfish]

What IDE are you using for your VBScript? I saw something in relation to this a few weeks ago for something I was doing and found this solution for VB 6:


----------

Private Sub ByteArrayToPDFConverter_Click()

Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim mstream As ADODB.Stream

Set cn = New ADODB.Connection
cn.Open "Provider=SQLOLEDB;data Source=test;Initial Catalog=test;User Id=_test;Password=test"

Set rs = New ADODB.Recordset
rs.Open "Select document from tbltest where id = 13", cn, adOpenKeyset, adLockOptimistic
Set mstream = New ADODB.Stream
mstream.Type = adTypeBinary
mstream.Open
mstream.Write rs.Fields("document").Value
mstream.SaveToFile "c:\ByteTopdf.pdf", adSaveCreateOverWrite


rs.Close
cn.Close

You would need to modify it quite a bit to work with VBScript

thanks for this.... done this .. saves the file but cant open pdf .. can only assume the the varbin column has some additional data which is also being written hence the file wont open in Acrobat Reader.

 

[jfish]

right think I know what problem is.. opened up the pdf file in notepad++ to see the contents and compared it to another PDF file I have also opened up in Notepad++

The header section look correct, but looks like the vbscript is only saving 8K of data from the varbinary column to the file, hence reason why PDF is corrupt.