Domino Code Fragment

Code Name*
ODBC using LS:DO to push to an Oracle database.		Date*
06/28/2025		Source (or email address if you prefer)*
[email protected]
IP address:.172.69.6.66
Description*
Pushes LotusNotes data to an Oracle database!		Type*
LotusScript		Categories*
(Misc)
Implementation:
Required Client:
Server:
Limitations:
Comments:
The machine this runs on must have ODBC installed and a connection setup to the Oracle database that will be pushed to.
Files/Graphics attachments (if applicable): Code:
(Options)
Option Public
Uselsx "*LSXODBC"

Sub Initialize
' Agentlog is used as debugger to write to log to detemine how far we have proceeded when agent is executed
Dim agentLog As New NotesLog("Put Jobs into INTRA__JOB table Version 2")
Call agentLog.OpenAgentLog
Call agentLog.LogAction("Declaring objects/variables...")
Dim session As New NotesSession
Dim db As NotesDatabase
Dim doc As NotesDocument
Dim JobByIDView As NotesView
Dim itemType As Variant
Dim con As New ODBCConnection
Dim qry As New ODBCQuery
Dim res As New ODBCResultSet
Dim dateTime As New NotesDateTime( "" )
dateTime.LSLocalTime = Now
Set db = session.CurrentDatabase

'On Error Goto ProcessError

' Production db
' If con.ConnectTo("kprod", "kfrc", "kfrc") Then
' Set qry.Connection = con
' Print "Connected to kfrc"
' qry.SQL = "SELECT * FROM dbadmin.intra_job"
' Set res.query = qry
' res.CacheLimit = DB_NONE
' Call res.Execute()
' Print "Connected to the datasource"
' Development db
If con.ConnectTo("kfrc2", "kfrc", "kfrc") Then
Set qry.Connection = con
Print "Connected to kfrc"
qry.SQL = "SELECT * FROM intra_job_scrub"
Set res.query = qry
res.CacheLimit = DB_NONE
Call res.Execute()
Print "Connected to the datasource"

Set JobByIDView = db.GetView( "KForceJobs" )
Set doc = JobByIDView.GetFirstDocument

' Loop through documents in the view
While Not (doc Is Nothing)
res.AddRow

' PAGE 1
Set itemType = doc.getfirstitem("CompanyName")
sDataString = Implode(itemType)
Call res.SetValue("CONAME", Left$(Implode(itemType), 100))

Set itemType = doc.getfirstitem("HomeCity")
Call res.SetValue("CITY", Left$(Implode(itemType), 50))

' Set itemType = doc.getfirstitem("City")
' Call res.SetValue("CITY", Left$(Implode(itemType), 50))

Set itemType= doc.getfirstitem("HomeState")
Call res.SetValue("STATE", Left$(Implode(itemType), 50))

' Set itemType= doc.getfirstitem("State")
' Call res.SetValue("STATE", Left$(Implode(itemType), 50))

Set itemType = doc.getfirstitem("SpecialtyArea")
sSpecialtyArea = Left$(Implode(itemType), 100)

Select Case sSpecialtyArea
Case "Manufacturing and Engineering"
Call res.SetValue("SPECIALTY", "Engineering")

Case "Executive Support"
Call res.SetValue("SPECIALTY", "Executive Support (Administrative)")

Case "Finance and Accounting"
Call res.SetValue("SPECIALTY", "Finance and Accounting - General")

Case "Health Care"
Call res.SetValue("SPECIALTY", "Health Care - Clinical")

Case "Information Technology"
Call res.SetValue("SPECIALTY", "Information Technology - General")

Case "Life Insurance and Investments"
Call res.SetValue("SPECIALTY", "Insurance and Investments")

Case Else
Call res.SetValue("SPECIALTY", sSpecialtyArea)
End Select

Set itemType = doc.getfirstitem("JobTitle")
Call res.SetValue("TITLE", Left$(Implode(itemType), 50))

Set itemType = doc.getfirstitem("JobResp")
Call res.SetValue("POSITION_RESPONSIBILITIES", Left$(Implode(itemType), 255))

' PAGE 2
Set itemType = doc.getfirstitem("PosType")
sPositionType = Implode(itemType)
Select Case sPositionType
Case "Permanent" : Call res.SetValue("POSITION", "P")
Case "Contract W-2" : Call res.SetValue("POSITION", "C")
Case "Contract to Perm" : Call res.SetValue("POSITION", "R")
Case "Contract-to-Permanent" : Call res.SetValue("POSITION", "R")
Case "Contract or Permanent" : Call res.SetValue("POSITION", "B")
Case "Independent Consultant" : Call res.SetValue("POSITION", "I")
Case Else
Call res.SetValue("POSITION", "P")
End Select

Set itemType = doc.getfirstitem("PrimarySkill")
Call res.SetValue("SKILLS", Left$(Implode(itemType), 500))

Set itemType = doc.getfirstitem("Experience")
Call res.SetValue("RESPON", Left$(Implode(itemType), 500))

Set itemType = doc.getfirstitem("Academic")
Call res.SetValue("EDUCATION", Left$(Implode(itemType), 255))

Set itemType = doc.getfirstitem("Certification")
Call res.SetValue("REQUIRED_CERTIFICATIONS", Left$(Implode(itemType), 1000))

' PAGE 4
Set itemType = doc.getfirstitem("TotalCompAnnual1")
If Not (itemType Is Nothing) Then
Call res.SetValue("SALMIN", Implode(itemType))
End If

Set itemType = doc.getfirstitem("TotalCompAnnual")
If Not (itemType Is Nothing) Then
If doc.TotalCompAnnual(0) <> "" Then Call res.SetValue("SALMAX", Implode(itemType))
End If

Set itemType = doc.getfirstitem("TotalCompHour1")
If Not (itemType Is Nothing) Then
Call res.SetValue("BILLMIN", Implode(itemType))
End If

Set itemType = doc.getfirstitem("TotalCompHour")
If Not (itemType Is Nothing) Then
If doc.TotalCompHour(0) <> "" Then Call res.SetValue("BILLMAX", Implode(itemType))
End If

' PAGE 5
Set itemType = doc.getfirstitem("DesiredStartDate")
If Not (itemType Is Nothing) Then
If doc.DesiredStartDate(0) <> "" Then Call res.SetValue("DESIRED_START_DATE", Implode(itemType))
End If

Set itemType = doc.getfirstitem("CompanyID")
If Not (itemType Is Nothing) Then
If doc.CompanyID(0) <> "" Then Call res.SetValue("CO_NUMBER", Implode(itemType))
End If

Set itemType = doc.getfirstitem("AverageHours")
If Not (itemType Is Nothing) Then
If doc.AverageHours(0) <> "" Then Call res.SetValue("EST_AVG_WORK_WEEK", Cint(Left$(Implode(itemType), 3)))
End If

Set itemType = doc.getfirstitem("WorkEnv")
Call res.SetValue("DRESSCODE", Implode(itemType))
Set itemType = doc.getfirstitem("HowLongOpen")
Call res.SetValue("HOW_LONG_POSITION_BEEN_OPEN", Implode(itemType))

Set itemType = doc.getfirstitem("Relocation")
Call res.SetValue("RELOCATION", Left$(Implode(itemType), 3))

Set itemType = doc.getfirstitem("RelocationOther")
Call res.SetValue("RELOALLOW", Implode(itemType))

' LAST SECTION
Set itemType = doc.getfirstitem("JobID")
Call res.SetValue("JOID", Implode(itemType))

Set itemType = doc.getfirstitem("DatePosted")
If Not (itemType Is Nothing) Then
Call res.SetValue("DATEIN", doc.DatePosted(0))
End If

Set itemType = doc.getfirstitem("NumPositions")
If Not (itemType Is Nothing) Then
If doc.NumPositions(0) <> "" Then Call res.SetValue("NUMPOS", doc.NumPositions(0))
End If

Call res.SetValue("ID", 3)
Call res.SetValue("APPROVED", "U")
Call res.SetValue("INSERT_DATE", dateTime.DateOnly)


res.UpdateRow
' Grab next document in the view
Set doc = JobByIDView.GetNextDocument(doc)
Wend

res.Close(DB_CLOSE)
con.Disconnect

Else
Call agentLog.LogAction("Connection to kfrc failed")
End If

Exit Sub

ProcessError:
If Not con.IsConnected Then
emsg = "Connection not made - " & con.GetErrorMessage
Elseif con.GetError <> DBstsSuccess Then
emsg = "ODBC Error: " & con.GetErrorMessage
Elseif qry.GetError <> DBstsSuccess Then
emsg = "ODBC Error: " & qry.GetErrorMessage
Elseif res.GetError <> DBstsSuccess Then
emsg = "ODBC Error: " & res.GetErrorMessage
Else
emsg= "Runtime error: Line #" & Str(Erl) & Str(Err) & " : " & Error$
End If

Call agentLog.LogAction(emsg)
Call agentLog.LogAction(itemType.name)
Call agentLog.LogAction(status)
res.Close(DB_CLOSE)
con.Disconnect
Exit Sub
End Sub

Function Implode(Item As Variant) As String
Dim sReturnString As String
If Not (item Is Nothing) Then
Select Case item.Type

Case ERRORITEM ' Field might contain error so we ensure error trapping here
sReturnString = ""

Case UNAVAILABLE ' Field might not exist so we ensure error trapping here
sReturnString = ""

Case RICHTEXT ' Field might be richtext so we get the formatted text
sReturnString = item.GetFormattedText( False, 0 )

Case DATETIMES ' Field might be DATETIME so we get the datetime value as text string
sReturnString = item.DateTimeValue.LSLocalTime

Case NUMBERS ' Field might be numbers so we get the converted text of the values
Forall v In item.values
'-- Concatenate each element in item to sReturnString
If sReturnString = "" Then
sReturnString = Cstr(v)
Else
sReturnString = sReturnString & "," & Cstr(v)
End If
End Forall

Case TEXT ' Field might be numbers so we get the text of the values
Forall v In item.values
'-- Concatenate each element in item to sReturnString
If sReturnString = "" Then
sReturnString = v
Else
sReturnString = sReturnString & "," & v
End If
End Forall

Case Else
sReturnString = ""

End Select

Else
sReturnString = ""
End If

Implode = sReturnString

End Function