VBScript or UFT code to connect database and extract/update data

Following code can be used to connect to database to perform select/update queries.

Prerequisites:

1. Install DB client in the machine from where we are executing this piece of code
2. 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