Grab a SQL Image data type with the Connector, ODBC or Microsofts ADO

Sub Initialize

'How do I read an image from SQL Server and put it in a document?
'How do i get a picture form SQL Server and write it to disk?
' WITHOUT 3rd party software? Yes, free.
'In this example, I read a blob (image data, stored in JPG format) from
'SQL Server and write it to disk,
'as well as embedding it in a notesDocument


'MAKE SURE YOU PUT Uselsx "*lsxlc" in the option section,
'and always use option explicit for your own safety, mkaaay?
' you need mdac_typ.exe (MS data access components) installed where the
'script executes ( to set up odbc DSN)

' I know some folks want to do the reverse of this, and I think it's
'doable. you might need to use
' the SetFormatStream method for the LCField object. If you figure it out,
'could you email me?
' thanks,
'- lucas

' Stuff you almost always need
Dim session As New NotesSession
Dim db As NotesDatabase
Set db = session.CurrentDatabase
'Stuff for the lsxlc
Dim LCsession As New LCSession
Dim fld As LCField
Dim fldLst As New LCFieldList
Dim fld2 As New lcField(LCTYPE_BINARY)
' not sure if i have to specify binary here
Dim keyFldLst As New LCFieldList
Dim src As New LCConnection ("odbc2")
' why odbc2 ? i dunno , but it works

' Stuff for the new notes doc & attachment
Dim strPic As String
Dim doc As notesDocument
Dim rt As notesRichTextItem
Dim object As NotesEmbeddedObject

Dim x

'Set the connection properties
src.server="epi5" ' Then name of my odbc DSN (using odbcad32.exe)
src.Userid = "yourmom" ' this may already be in the DSN, but what the heck
src.Password = "isso" ' this may already be in the DSN, but what the heck
src.Metadata = "GA_IMAGE" ' my table name
src.Connect ' this, uh... connects i think

' Now specify the keys.
Call keyFldLst.append("*",LCTYPE_TEXT) ' This (*) enables me to get all
'the fields. you could do some key searching and stuff if you really wanted to
' Now issue the select statement
x = src.Select (keyFldLst, 1, fldLst) ' fldLst is populated with fieldNames

Set fld = fldLst.getField(1) ' the employee id field in my case. You
'HAVE to use the ordinal... yuck. I just looked in
'Debug to find the right field. Whatever.
Set fld2 = fldLst.getField(7) ' my BLOB, or image field (stored in jpg format)


' Now (finally) we can get some data
x= src.fetch(fldLst) ' fldlst is populated with values
Do While x <> 0
'Create a file for each employee
strPic = "C:/pictureDirectory/" + fld.text(0) + ".jpg"
Open strPic For Binary As #1
' Write the picture data to the file (look mom ... no GETCHUNK !)
Put #1 ,, fld2.value(0)
Close #1
'Now, create a notesDoc for each picture
Set doc = New notesDocument(db)
doc.myDocType="image"
doc.employeeID=fld.text(0)
doc.form="employee_image"
'CreateThe body
Set rt = New notesRichTextItem(doc,"body")
'Embed the pic (you can create as an object if you REALLY want to,
' but I needed the file on disk anyway)
Set object = rt.EmbedObject( _
EMBED_OBJECT, "", strPic, "person_image" )
' I could MAIL it,
' or embed a hyperlink to the file i created...
' the possibilities are endless
' today,I'll just save it.
Call doc.save(True,False)
' get the next rec
x=src.fetch(fldLst)
Loop


'Refer to lsxlc.nsf for lsx documentation


End Sub