Following code can be used to connect to database to perform select/update queries.
Prerequisites:
- Install DB client in the machine from where we are executing this piece of code
- Establish DSN(Data Source Name)
Logic how VBScript code connects to DB:
DB client and WSH engine creates the interfaces/platforms separately but to make the bridge between both, we should have DSN which works as communication channel to perform the transactions.
strServerName= “abcd.com”
strDBName= “database”
strDBUserName="DBUserName"
strDBPWD= “DBPWD"
strSQL= "Select * from employee "
If rc = micPass Then rc = DB_Execute_Query(strSQL,strServerName,strDBName,strDBUserName,strDBPWD)
'*******************************************************************************
'Function Name : DB_Connection
'Description : Function to establish the connection to database
'Input Parameters : strDataBaseUser,strDBPassword,strDataBaseName
'Output Parameters: None
'Createb By: Govardhan
'********************************************************************************
Public Function DB_Connection(strServerName,strDBName,strDBUserName,strDBPWD)
On Error Resume Next
Set Conn = CreateObject ("ADODB.Connection")
Set rs = CreateObject("ADODB.Recordset")
''' 'Create the string which is the command for connection
'''' strCon="Driver={PostgreSQL ANSI};Server=abc.com;Port=5432;Database=database;UID=server;PWD=server;"
strCon="Driver={PostgreSQL ANSI};Server="&strServerName&";Port=5432;Database="&strDBName&";UID="&strDBUserName&";PWD="&strDBPWD
Conn.ConnectionString = strCon
Conn.Open()
If Err.Number <> 0 then
rc = micFail
Reporter.ReportEvent rc,"Connect to database","Unable to connect to database due to following error," &_
vbcrlf & "Error Code: " & Err.Number &_
vbcrlf & "Error Description: " & Err.Description
Err.Clear
Else
rc = micPass
Reporter.ReportEvent rc,"Connect to database - "&strDBName &" - was successful","Connection successful"
Environment("DB_Connection_"&strDBName)= Conn
Environment("DB_Recordset_"&strDBName)= rs
End If
On Error GoTo 0
DB_Connection = rc
End Function
'*******************************************************************************
'Function Name : DB_Execute_Query
'Description : Function to execute a query and check if successfully executed or not
'Input Parameters : strSql,strDataBaseUser,strDBPassword,strDataBaseName
'Output Parameters: 0- Error,1 - Successful
'********************************************************************************
Public Function DB_Execute_Query(strSql, strServerName,strDBName,strDBUserName,strDBPWD)
On Error Resume Next
DB_Connection strServerName,strDBName,strDBUserName,strDBPWD
Set Conn = Environment("DB_Connection_"&strDBName)
Set rs = Environment("DB_Recordset_"&strDBName)
Set rs = Conn.Execute (strSql)
If Err.Number <> 0 then
Reporter.ReportEvent micFail,"Query the database","Following error while querying the database," &_
vbcrlf & "Sql Statement: " & strSql &_
vbcrlf & "Error Code: " & Err.Number &_
vbcrlf & "Error Description: " & Err.Description
Err.Clear
rc = micFail
DB_Execute_Query = rc
Else
rc = micPass
Reporter.ReportEvent micPass, "Execute Sql Query","User should able to execute the sql query and the query is" & strSql ," Query Executed succefully"
DB_Execute_Query = rc
End If
'Close Connection and Recordset objects
DB_Close Conn,rs
On Error GoTo 0
DB_Execute_Query = rc
End Function
'*******************************************************************************
'Function Name : DB_Close
'Description : Function to disconnect the DB connection
'Input Parameters : strDataBaseName,RecordSet
'Output Parameters: None
'********************************************************************************
Public Function DB_Close(strConnection,StrRecordSet)
On Error Resume Next
Set StrRecordSet= nothing
strConnection.close
Set strConnection = nothing
On Error GoTo 0
End Function
