How to connect to SQL from Notes
Subject: SQL Connection
============call this function on button click=======function changeVal()
{
var dbPath = getDBPath()
var frm=document.forms[0]
var office= frm.officeTX.options[frm.officeTX.selectedIndex].text
var cName = frm.ClientNameTX.value
var url=dbPath+“/(agtGFIS_Lookup_Client)?Openagent&Office=”+office+“&ClientNme=”+cName+“&flag=True”;
xmlHttp.open(“GET”,url,true);
xmlHttp.onreadystatechange=changeval;
xmlHttp.send(null)
}
function changeval()
{
if (xmlHttp.readystate==4)
{
var rn = xmlHttp.responseXML.getElementsByTagName(“Xvalues”)
var cn=rn.item(0).getElementsByTagName(“unique”)
for (i=0;i<cn.length;i++)
{
var j=i+1
if (cn.item(i).firstChild.nodeValue == “0”) {
classNme = "txtfld1"
}
else if (cn.item(i).firstChild.nodeValue == “1”) {
classNme = "txtfld2"
}
else if (cn.item(i).firstChild.nodeValue > “1”) {
classNme = "txtfld3"
}
document.getElementById(“c_Lookup”+j).className = classNme
}
===============Agent====================
Option Public
Option Declare
Uselsx “*LSXODBC”
Sub Initialize
On Error Resume Next
Dim session As NotesSession
Dim agent As NotesAgent
Dim db As NotesDatabase
Dim entryCollection As NotesViewEntryCollection
Dim entry As NotesViewEntry
Dim view As NotesView
Dim SearchResultentry As notesviewentrycollection
Dim doc As NotesDocument
Dim docEntry As NotesDocument
Dim docClientToSync As NotesDocument
Dim viewToSync As NotesView
Dim vc As NotesViewEntryCollection
Dim entryOne As NotesViewEntry
Dim conn As ODBCConnection
Dim con As ODBCConnection
Dim qry As ODBCQuery
Dim result As ODBCResultSet
Dim strDataSourceName As String
Dim strDBUserName As String
Dim strPassword As String
Dim strUserName As Variant
Set session = New NotesSession
Set db=session.CurrentDatabase
Set agent=session.CurrentAgent
Set conn = New ODBCConnection
Set doc = session.DocumentContext
Set qry = New ODBCQuery
Set result= New ODBCResultSet
Set qry.Connection = conn
Set result.Query = qry
Set view = db.GetView("vwGFIS_DisplayClients")
Set entryCollection = view.AllEntries
'get DSN ,Username, Password fromDSN script libtrary
strDataSourceName=getDSN(db,"DSN for GFIS")
strDBUserName=getUserName(db,"Username for GFIS")
strPassword=getPassword(db,"Password for GFIS")
If(strDataSourceName<>"" And strDBUserName<>"" And strPassword<>"") Then
Call conn.ConnectTo(strDataSourceName, strDBUserName,strPassword)
Else
Exit Sub
End If
'''''''''''''computing the query
office = Strleft(Strright(doc.Query_String(0),"="),"&ClientNme")
client = Strleft(Strright(doc.Query_String(0),"&ClientNme="),"&flag")
doc.cNme = client
doc.off = office
client1=Evaluate({@URLDecode("Domino";cNme )},doc)
office1=Evaluate({@URLDecode("Domino";off )},doc)
If office1(0)<>"" Then
QryOff = |FIELD eyofficeTX CONTAINS |+office1(0)+||
End If
If office="All" Then
QryOff = ""
End If
If Trim$(client1(0)) <>"" Then
QryClient = " AND "+ |FIELD nameTX CONTAINS "|+client1(0)+|"|
Else
QryClient = ""
End If
If Trim$(QryOff) ="" And Trim$(QryClient)<>"" Then
QryClient = |FIELD nameTX CONTAINS "|+client1(0)+|"|
End If
Msgbox "Query = "&QryOff+QryClient
Call entryCollection.FTSearch(QryOff+QryClient,0)
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''Calling sorting function
Set SearchResultentry= view.GetAllEntriesByKey("zzxxzz")
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''end of calling sorting function
i=1
If SearchResultentry.Count >0 Then
Set entry =SearchResultentry.GetFirstEntry
While Not entry Is Nothing
Set docEntry = entry.Document
qry.SQL = "select count(s.T_C_NM) as Cnt from VW_GMEDW_Client_Details as s where s.T_C_NM like '%" +docEntry.nameTX(0) + "%' and s.T_CITY_NM='"+docEntry.eyofficeTX(0) +"'"
result.Execute
If result.IsResultSetAvailable Then
Count =result.GetValue("Cnt")
Else
Count = "0"
End If
GFISCount = GFISCount &Count &","
Set entry =SearchResultentry.GetNextEntry(entry)
Wend
End If
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''end of calling sorting function
res = Split(Strleftback(GFISCount,","), ",")
Print "Content-Type: text/xml"
Print |<?xml version="1.0" encoding="UTF-8" ?> |
Print |<Xvalues>|
For x=0 To Ubound(res)
Print |<unique>|+res(x)+|</unique>|
Next
Print |</Xvalues>|
End Sub
=============Function getDSN============
Function getDSN(dbSurDSN As NotesDatabase,strDSNKeyword As String) As String
Dim strDSN As String
strDSN=""
Set viewKeywordsDSN =dbSurDSN.GetView("vwKeywords")
If Not viewKeywordsDSN Is Nothing Then
Set docKeywordDSN=viewKeywordsDSN.GetDocumentByKey(strDSNKeyword,True)
If Not docKeywordDSN Is Nothing Then
strDSN= docKeywordDSN.subItemsTX(0)
End If
End If
getDSN= strDSN
End Function
======Function getUsername===============
Function getUserName(dbSurDSN As NotesDatabase,strDSNUserName As String) As String
strUserName=""
Set viewKeywordsDSN =dbSurDSN.GetView("vwKeywords")
If Not viewKeywordsDSN Is Nothing Then
Set docKeywordDSN=viewKeywordsDSN.GetDocumentByKey(strDSNUserName,True)
If Not docKeywordDSN Is Nothing Then
strUserName= docKeywordDSN.subItemsTX(0)
End If
End If
getUserName= strUserName
End Function
=========Function getPassword============
Function getPassword(dbSurDSN As NotesDatabase,strDSNpassword As String) As String
Dim strPassword As String
strPassword=""
Set viewKeywordsDSN =dbSurDSN.GetView("vwkeywords")
If Not viewKeywordsDSN Is Nothing Then
Set docKeywordDSN=viewKeywordsDSN.GetDocumentByKey(strDSNpassword,True)
If Not docKeywordDSN Is Nothing Then
strPassword= docKeywordDSN.subItemsTX(0)
End If
End If
getPassword= strPassword
End Function