Hi,
I am trying to gather employee information from a SQL system, including a photo which is held in an embedded field on the SQL form onto a notes document.
I have managed to gather the standard field information (text fields etc) using the code below but I am unsure how to extract the embedded image and add it to my notes document. The image is held in the field called LNL_BLOB.
I have read Andre’s sandbox entry on dealing with File Attachments but I have don’t have a very strong understanding of ODBC connectors as this is my first project using them.
Here is a version of my code :
Sub Initialize
Dim sqlConnection As New LCConnection("odbc2")
Dim notesConnection As New LCConnection("notes")
Dim sqlFieldList As LCFieldList
Dim notesFieldList As LCFieldList
Dim blobField As LCField
Dim count As Double
Dim SQL_STATEMENT As String
Set lcSession = New LCSession
SQL_STATEMENT = |
SELECT EMP.SSNO, EMP.ID, EMP.LASTNAME, EMP.FIRSTNAME
FROM AccessControl.dbo.EMP EMP
WHERE (EMP.SSNO>‘0’)
ORDER BY EMP.SSNO
|
SQL_STATEMENT = |
SELECT EMP.SSNO, EMP.ID, EMP.LASTNAME, EMP.FIRSTNAME, MMOBJS.LNL_BLOB, MMOBJS.LASTCHANGED
FROM AccessControl.dbo.EMP EMP, AccessControl.dbo.MMOBJS MMOBJS
WHERE EMP.ID = MMOBJS.EMPID AND ((EMP.SSNO>‘0’))
ORDER BY EMP.SSNO
|
' -- Connect to SQL Database
sqlConnection.Userid = userid
sqlConnection.Password = password
sqlConnection.server = connection
sqlConnection.metadata = table
sqlConnection.Connect
' -- Connect to Notes Database
notesConnection.server = servername
notesConnection.database = dbname
notesConnection.metadata = formname
notesConnection.writeback = True
Call notesConnection.Connect
'initalise the fieldlist
Set sqlFieldList = New LCFieldList(1, XREF_TRUNCATION_FLAGS)
'execute sql
Call sqlConnection.Execute(SQL_STATEMENT, sqlFieldList)
Set notesFieldList = New LCFieldList
Call notesFieldList.MapName (sqlFieldList, _
"SSNO, ID, LASTNAME, FIRSTNAME, LNL_BLOB, LASTCHANGED", _
"SSNO, ID, LASTNAME, FIRSTNAME, LNL_BLOB, LASTCHANGED" )
Do Until sqlConnection.fetch (sqlFieldList) = 0
Set blobField = sqlFieldList.Lookup("LNL_BLOB")
Call notesConnection.insert(notesFieldList)
Loop
sqlConnection.Disconnect
notesConnection.Disconnect
lcSession.ClearStatus
End Sub
Many thanks in advance
Fee