Domino Code Fragment

Code Name*
Update Oracle records via LotusScript agent		Date*
04/29/2024		Source (or email address if you prefer)*
Rlatulippe@romac.com
IP address:.3.128.199.210
Description*
This database keys on a record in Oracle and then updates the record from the notes Document.		Type*
LotusScript		Categories*
(Misc)
Implementation:
Required Client:
Server:
Limitations:
Comments:
Files/Graphics attachments (if applicable): Code:

(Options)
Option Public
Uselsx "*LSXODBC"


Sub Initialize
'Agent to push changes made in job profiles back to Oracle
'jlathren@romac.com 01/27/00

' 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("Oracle Update")
Call agentLog.OpenAgentLog
Call agentLog.LogAction("Start")
Dim session As New NotesSession
Dim db As NotesDatabase
Dim doc As NotesDocument
Set db = session.CurrentDatabase
Set doc = session.DocumentContext
Dim con As New ODBCConnection
Dim qry As New ODBCQuery
Dim result As New ODBCResultSet
Dim Oracle_JOBID As String
Dim Oracle_ID As String
Dim Notes_JOBID As String
Dim Notes_ID As String
Dim Notes_City As String

'get all the new Notes values
Notes_JOBID = doc.Job_ID(0)
Notes_ID = doc.ID(0)
Notes_DisplayOrg = doc.Display_Org_Name(0)
Notes_City = doc.Position_Loc_City(0)
Notes_State = doc.Position_Loc_State(0)
Notes_Country = doc.Position_Loc_Country(0)
Notes_Title= doc.Position_Loc_Title(0)
Notes_Responsibilities = doc.Position_Responsibilities(0)
Notes_ReportsTo= doc.Title_job_reports_to(0)
Notes_NumDirect= doc.Num_Direct_Reports(0)
Notes_YearsNeeded= doc.Years_Experience_Needed(0)
Notes_ReqExp= doc.Required_Industry_Exp(0)
Notes_ReqCert= doc.Required_Certifications(0)
Notes_ReqEd= doc.Required_Ed_Level(0)
Notes_ReqStudy= doc.Required_Area_of_Study(0)
Notes_Langs= doc.Fluent_Languages(0)
Notes_OtherReqs= doc.Other_Requirements(0)
Notes_DesExp= doc.Desired_Industry_Exp(0)
Notes_DesCert= doc.Desired_Certifications(0)
Notes_DesEd= doc.Desired_Ed_Level(0)
Notes_DesStudy= doc.Desired_Area_of_Study(0)
Notes_AnnSalDes= doc.Annual_Salary_Desired(0)
Notes_AnnSalMax= doc.Annual_Salary_Max(0)
Notes_AnnBonPotDollars= doc.Annual_Bonus_Potential_Dollars(0)
Notes_AnnBonPotPercent= doc.Annual_Bonus_Potential_Percent(0)
Notes_HourRateDes= doc.Hourly_Rate_Desired(0)
Notes_HourRateMax= doc.Hourly_Rate_Max(0)
Notes_BenOffered= doc.Benefits_Offered(0)
Notes_BenCoverages= doc.Benefit_Coverages(0)
Notes_FreeParking= doc.Free_Parking(0)
Notes_DesStartDate= doc.Desired_Start_Date(0)
Notes_Job_Status= doc.Job_Status(0)
Notes_LengthAssgn= doc.Length_of_Assignment(0)
Notes_EstAvgWeek= doc.Est_Avg_Work_Week(0)
Notes_Worksite= doc.Worksite_Options(0)
Notes_NonlocCan= doc.Nonlocal_Candidates(0)
Notes_DressCode= doc.Dress_Code(0)
Notes_HowLongOpen= doc.How_Long_Position_Been_Open(0)
Notes_ReasPosOpen= doc.Reason_for_Pos_Opening(0)
Notes_RelocAssist= doc.Relocation_Assistance(0)
Notes_RelocAssistDet= doc.Relocation_Assistance_Details(0)
Notes_TravArea= doc.Travel_Area(0)
Notes_AmtTravReq= doc.Amt_Travl_Required(0)
Notes_ExDetPerm= doc.Expenses_Details_Perm(0)
Notes_PayIntPerm= doc.Pay_For_Interview_Expenses_Perm(0)
Notes_ExDetCon= doc.Expenses_Details_Contract(0)
Notes_PayIntCon= doc.Pay_For_Interview_Exp_Contract(0)
Notes_ContactFirst= doc.Contact_First_Name(0)
Notes_ContactLast= doc.Contact_Last_Name(0)
Notes_ContactEmail= doc.Contact_Email(0)

'Error Checking
On Error Goto ProcessError

'Connect to Oracle Database through SQL statements
If con.ConnectTo("kfrc2", "kfrc", "kfrc") Then
Set qry.Connection = con

'Every field to update - done this way because some Oracle fields are VarChr(4000) and bringing them all in will cause agent error
' qry.SQL = "SELECT DISPLAY_ORG_NAME, CITY, STATE, POSITION_LOC_COUNTRY, TITLE, POSITION_RESPONSIBILITIES, TITLE_JOB_REPORTS_TO," & _
' "NUM_DIRECT_REPORTS, YEARS_EXPERIENCE_NEEDED, RESPON, REQUIRED_CERTIFICATIONS, EDUCATION, REQUIRED_AREA_OF_STUDY, " &_
' "FLUENT_LANGUAGES, OTHER_REQUIREMENTS, DESIRED_INDUSTRY_EXP, DESIRED_CERTIFICATIONS, DESIRED_ED_LEVEL, " &_
' "DESIRED_AREA_OF_STUDY, SALMIN, SALMAX, ANNUAL_BONUS_POTENTIAL_DOLLARS, ANNUAL_BONUS_POTENTIAL_PERCENT, BILLMIN," &_
' "BILLMAX, OTHERCOMP, HEALTHINS, FREE_PARKING, DESIRED_START_DATE, APPROVED, LENGTH, EST_AVG_WORK_WEEK," &_
' "WORKSITE_OPTIONS, NONLOCAL_CANDIDATES, HOW_LONG_POSITION_BEEN_OPEN, REASON_FOR_POS_OPENING, TRAVEL_AREA, DRESSCODE," &_
' "RELOCATION, RELOALLOW, TRAVELPCT, PAY_FOR_INTERVIEW_EXP_CONTRACT, EXPENSES_DETAILS_PERM, EXPENSES_DETAILS_CONTRACT," &_
' "INTERVIEW, ID, JOID, CONTACT_FIRST_NAME, CONTACT_LAST_NAME, CONTACT_EMAIL FROM intra_job_scrub2 WHERE JOID='343'"
qry.SQL = "SELECT CONAME, JOID from INTRA_JOB_SCRUB2 where JOid = '343'"
Set result.Query = qry
result.CacheLimit = DB_NONE
result.Execute

' res.LastRow
' i = result.NumRows
' Call agentLog.LogAction("The number of rows in the result set is: " & Cstr(i))

'get all records that match the current Notes document (with ID and Job_ID)
status1=result.LocateRow("JOID",Notes_JobID)
status2=result.LocateRow("ID",Notes_ID)
If status1 And status2 Then
Oracle_JOBID = Cstr(result.GetValue("JOID"))
Oracle_ID = Cstr(result.GetValue("ID"))

'Set all Oracle values to their Notes equivalent
If Oracle_JOBID = Notes_JobID And Oracle_ID = Notes_ID Then
' 2/3/00 - just update joid
status = result.SetValue("JOID", "1000")
' citystatus = result.SetValue("CITY", Notes_City)
' displayorgstatus = result.SetValue("DISPLAY_ORG_NAME", Notes_DisplayOrg)
' citystatus = result.SetValue("CITY", Notes_City)
' statestatus = result.SetValue("STATE", Notes_State)
' countrystatus = result.SetValue("POSITION_LOC_COUNTRY", Notes_Country)
' titlestatus = result.SetValue("TITLE", Notes_Title)
' responsstatus = result.SetValue("POSITION_RESPONSIBILITIES", Notes_Responsibilities)
' reportstostatus = result.SetValue("TITLE_JOB_REPORTS_TO", Notes_ReportsTo)
' numdirectstatus = result.SetValue("NUM_DIRECT_REPORTS", Notes_NumDirect)
' yearsneededstatus = result.SetValue("YEARS_EXPERIENCE_NEEDE", Notes_YearsNeeded)
' reqexpstatus = result.SetValue("RESPON", Notes_ReqExp)
' reqcertstatus = result.SetValue("REQUIRED_CERTIFICATIONS", Notes_ReqCert)
' reqedstatus = result.SetValue("EDUCATION", Notes_ReqEd)
' reqstudystatus = result.SetValue("REQUIRED_AREA_OF_STUDY", Notes_ReqStudy)
' langsstatus = result.SetValue("FLUENT_LANGUAGES", Notes_Langs)
' otherreqsstatus = result.SetValue("OTHER_REQUIREMENTS", Notes_OtherReqs)
' desexpstatus = result.SetValue("DESIRED_INDUSTRY_EXP", Notes_DesExp)
' descertstatus = result.SetValue("DESIRED_CERTIFICATIONS", Notes_DesCert)
' desedstatus = result.SetValue("DESIRED_ED_LEVEL", Notes_DesEd)
' destudystatus = result.SetValue("DESIRED_AREA_OF_STUDY", Notes_DesStudy)
' annsaldesstatus = result.SetValue("SALMIN", Cint(Notes_AnnSalDes))
' annsalmaxstatus = result.SetValue("SALMAX", Cint(Notes_AnnSalMax))
' annbonpotdolstatus = result.SetValue("ANNUAL_BONUS_POTENTIAL_DOLLARS", Cint(Notes_AnnBonPotDollars))
' annbonpotpertstatus = result.SetValue("ANNUAL_BONUS_POTENTIAL_PERCENT", Cint(Notes_AnnBonPotPercent))
' hourdesstatus = result.SetValue("BILLMIN", Cint(Notes_HourRateDes))
' hourmaxstatus = result.SetValue("BILLMAX", Cint(Notes_HourRateMax))
' benoffstatus = result.SetValue("OTHERCOMP", Notes_BenOffered)
' bencovstatus = result.SetValue("HEALTHINS", Notes_BenCoverages)
' freeparkstatus = result.SetValue("FREE_PARKING", Notes_FreeParking)
' desstartstatus = result.SetValue("DESIRED_START_DATE", Notes_DesStartDate)
' jobstatstatus = result.SetValue("APPROVED", Notes_Job_Status)
' lengthassgnstatus = result.SetValue("LENGTH", Notes_LengthAssgn)
' estavgweekstatus = result.SetValue("EST_AVG_WORK_WEEK", Notes_EstAvgWeek)
' worksitestatus = result.SetValue("WORKSITE_OPTIONS", Notes_Worksite)
' nonlocanstatus = result.SetValue("NONLOCAL_CANDIDATES", Notes_NonlocCan)
' dresscodestatus = result.SetValue("DRESSCODE", Notes_DressCode)
' showlongopenstatus = result.SetValue("HOW_LONG_POSITION_BEEN_OPEN", Notes_HowLongOpen)
' reasposopenstatus = result.SetValue("REASON_FOR_POS_OPENING", Notes_ReasPosOpen)
' relocasststatus = result.SetValue("RELOCATION", Notes_RelocAssist)
' relocasstdetstatus = result.SetValue("RELOALLOW", Notes_RelocAssistDet)
' travareastatus = result.SetValue("TRAVEL_AREA", Notes_TravArea)
' amttravreqstatus = result.SetValue("TRAVELPCT", Notes_AmtTravReq)
' exdetpermstatus = result.SetValue("EXPENSES_DETAILS_PERM", Notes_ExDetPerm)
' payintpermstatus = result.SetValue("NEXPENSES_DETAILS_PERM", Notes_PayIntPerm)
' exdetconstatus = result.SetValue("EXPENSES_DETAILS_CONTRACT", Notes_ExDetCon)
' payintconstatus = result.SetValue("PAY_FOR_INTERVIEW_EXP_CONTRACT", Notes_PayIntCon)
' confirststatus = result.SetValue("CONTACT_FIRST_NAME", Notes_ContactFirst)
' conlaststatus = result.SetValue("CONTACT_LAST_NAME", Notes_ContactLast)
' conemailstatus = result.SetValue("CONTACT_EMAIL", Notes_ContactEmail)
' approvedstatus = result.SetValue("APPROVED", "U")
status=result.UpdateRow
End If
End If
'close ODBC connection
result.Close(DB_CLOSE)
con.Disconnect
End If

Exit Sub
'error processing
ProcessError:
If con.GetError <> DBstsSuccess Then
Call agentLog.LogAction(con.GetErrorMessage)
Call agentLog.LogAction(con.GetExtendedErrorMessage)
Call agentLog.LogAction(WhatIsError(con.Error))
Elseif qry.GetError <> DBstsSuccess Then
Call agentLog.LogAction(qry.GetErrorMessage)
Call agentLog.LogAction(qry.GetExtendedErrorMessage)
Call agentLog.LogAction(WhatIsError(qry.Error))
Elseif result.GetError <> DBstsSuccess Then
Call agentLog.LogAction(result.GetErrorMessage)
Call agentLog.LogAction(result.GetExtendedErrorMessage)
Call agentLog.LogAction(WhatIsError(result.Error))
End If
result.Close(DB_CLOSE)
con.Disconnect
Exit Sub
End Sub

Function WhatIsError(number As Integer) As String
Select Case number
Case DBSTSACCS : WhatIsError = "DBSTSACCS"
Case DBSTSAHVR : WhatIsError = "DBSTSAHVR"
Case DBSTSBADP : WhatIsError = "DBSTSBADP"
Case DBSTSCANF : WhatIsError = "DBSTSCANF"
Case DBSTSCARR : WhatIsError = "DBSTSCARR"
Case DBSTSCCON : WhatIsError = "DBSTSCCON"
Case DBSTSCNVD : WhatIsError = "DBSTSCNVD"
Case DBSTSCNVR : WhatIsError = "DBSTSCNVR"
Case DBSTSCOAR : WhatIsError = "DBSTSCOAR"
Case DBSTSCPAR : WhatIsError = "DBSTSCPAR"
Case DBSTSCXIN : WhatIsError = "DBSTSCXIN"
Case DBSTSENTR : WhatIsError = "DBSTSENTR"
Case DBSTSEOFD : WhatIsError = "DBSTSEOFD"
Case DBSTSFAIL : WhatIsError = "DBSTSFAIL"
Case DBSTSHSTMT : WhatIsError = "DBSTSHSTMT"
Case DBSTSILLG : WhatIsError = "DBSTSILLG"
Case DBSTSINTR : WhatIsError = "DBSTSINTR"
Case DBSTSINVC : WhatIsError = "DBSTSINVC"
Case DBSTSINVR : WhatIsError = "DBSTSINVR"
Case DBSTSMEMF : WhatIsError = "DBSTSMEMF"
Case DBSTSNAFI : WhatIsError = "DBSTSNAFI"
Case DBSTSNCOJ : WhatIsError = "DBSTSNCOJ"
Case DBSTSNCOL : WhatIsError = "DBSTSNCOL"
Case DBSTSNCON : WhatIsError = "DBSTSNCON"
Case DBSTSNODA : WhatIsError = "DBSTSNODA"
Case DBSTSNOEX : WhatIsError = "DBSTSNOEX"
Case DBSTSNQOJ : WhatIsError = "DBSTSNQOJ"
Case DBSTSNUNQ : WhatIsError = "DBSTSNUNQ"
Case DBSTSODBC : WhatIsError = "DBSTSODBC"
Case DBSTSPMIS : WhatIsError = "DBSTSPMIS"
Case DBSTSRCHG : WhatIsError = "DBSTSRCHG"
Case DBSTSRDON : WhatIsError = "DBSTSRDON"
Case DBSTSROWD : WhatIsError = "DBSTSROWD"
Case DBSTSRUNC : WhatIsError = "DBSTSRUNC"
Case DBSTSSNFD : WhatIsError = "DBSTSSNFD"
Case DBSTSUBLE : WhatIsError = "DBSTSUBLE"
Case DBSTSUNIM : WhatIsError = "DBSTSUNIM"
End Select
Mid(WhatIsError, 3, 5) = "sts"
End Function