Subject: Need to extract data from SQL database
Ok, I finally got it to work a few days ago, thank you all for your help!!! Here is the complete code (including parsing) if anyone needs it for the future…—AGENT CODE—
Option Public
Uselsx “*LSXODBC”
Dim db As NotesDatabase
Dim doc As NotesDocument
Sub Initialize
Dim s As New NotesSession
Set db = s.CurrentDatabase
Set doc = s.DocumentContext
Dim AgentArgs List As String
Dim torig As String
Dim tdest As String
Dim tldest As String
Dim tlorig As String
Dim torig2 As String
Dim tdest2 As String
Dim transoption As String
Dim tlduration As String
Call ExplodeQueryString (doc.Query_String_Decoded(0), AgentArgs)
'Check to see if values exist
If Iselement( AgentArgs ("t_orig") ) Then
Print "<br>"
Print " <H3>Directions from " + AgentArgs ("t_orig")
Else
Print "There is an error in the URL that was used."
Print "<br>"
End If
If Iselement( AgentArgs ("t_dest") ) Then
Print "<H3> To " + AgentArgs ("t_dest")
Print "<br>"
Else
Print "There is an error in the URL that was used."
Print "<br>"
End If
'---Code for sql query after parsing values---
Dim con As ODBCConnection
Dim qry As ODBCQuery
Dim result As ODBCResultSet
Set con = New ODBCConnection
Set qry = New ODBCQuery
Set result = New ODBCResultSet
Set qry.Connection = con
Set result.Query = qry
con.ConnectTo("myodbc3-trans")
'retrieve parsed variables from user
torig2 = AgentArgs ("t_orig")
tdest2 = AgentArgs ("t_dest")
'Send query to SQL database
qry.SQL = "SELECT DISTINCT torig, tdest, tldest, tlorig, transoption, tlduration FROM Trans WHERE opt_num ='1' AND torig='" + torig2 + "' AND tdest='" + tdest2 + "'"
result.Execute
If Not result.Execute Then
result.GetErrorMessage
Print "No chance"
End If
'Retrieve variables from SQL Query
Do
result.NextRow
'torig = result.GetValue("TORIG", torig)
'tdest = result.GetValue("TDEST", tdest)
tldest = result.GetValue("TLDEST", tldest)
tlorig = result.GetValue("TLORIG", tlorig)
transoption = result.GetValue("TRANSOPTION",transoption)
tlduration = result.GetValue("TLDURATION", tlduration)
Print"<table border = 1cellpadding = 10 cellspacing = 3>"
'Print"<tr>"
'Print"<td col span = 2>"
'Print torig " to "
'Print tdest
Print"<tr>"
Print"<td>"
Print tlorig " to "
Print"<td>"
Print tldest
Print"<td>"
Print "Using " + transoption
Print"<td>"
Print tlduration + " Mins"
Print "<tr>"
Print"</table>"
Loop Until result.IsEndOfData
result.Close(DB_CLOSE)
con.Disconnect
'-close connection
End Sub
—Called Functions—
Private Sub ExplodeQueryString (QueryString As String, AgentArgs List As String)
Dim Args As String
Args = RightStr(QueryString, "OpenAgent&")
Dim ArgsList As Variant
ArgsList = Evaluate ({@Explode("} & Args & {"; "&")})
Dim ArgKey As String
Dim ArgValue As String
Forall Arg In ArgsList
ArgKey = LeftStr(Arg, "=")
ArgValue = RightStr(Arg, "=")
AgentArgs(ArgKey) = ArgValue
End Forall
End Sub
Function LeftStr(OrigStr, LeftOf ) As String
Dim Pos As Integer
Dim OrigStrLen As Integer
Pos = Instr( Lcase(OrigStr), Lcase(LeftOf) )
OrigStrLen = Len(OrigStr)
If pos>0 Then
LeftStr = Left( OrigStr, (Pos-1))
Else
LeftStr = OrigStr
End If
End Function
Function RightStr(OrigStr, RightOf ) As String
Dim Pos As Integer
Dim OrigStrLen As Integer
Dim RightOfLen As Integer
Pos = Instr( Lcase(OrigStr), Lcase(RightOf) )
OrigStrLen = Len(OrigStr)
RightOfLen = Len(RightOf)
If Pos>0 Then
RightStr = Right( OrigStr, OrigStrLen - (RightOfLen+Pos-1))
Else
RightStr = OrigStr
End If
End Function
