Connect to PostgreSQL Database Using VBScript in DIAdem

This article demonstrates how to connect to and query a PostgreSQL database using Visual Basic Script (VBScript) in DIAdem.

A connection can be made using ODBC and a ODBC driver for Postgres.

Prerequisites

• A PostgreSQL Server.
• A PostgreSQL database that you wish to access.

Follow the steps below to write a VBScript in DIAdem that connects to and queries an external PostgreSQL database:

1. First, download and install the ODBC driver for Postgres [External] here.
   1. This driver allows you to connect to local or remote Postgres databases.
2. Once installed, launch the ODBC Data Sources application from the Windows Start Menu.
3. Select either the User DSN or System DSN tab depending on your needs:
   1. User DSN makes the PostgreSQL database available for the current user.
   2. System DSN makes the PostgreSQL database available for all users and services on the machine.
4. Click Add... from the right-hand side to select a driver.

5. In the pop-up window, scroll and select one of the following drivers:
   1. PostgreSQL Unicode: if your database was set up with the UTF-8 character set.
   2. PostgreSQL ANSI: if your database was set up with a LATIN character set.
6. Click Finish.

7. Configure the following settings:
   1. Data Source should be a descriptive name.
   2. Database should be the name of the database you wish to connect to. Set the value as postgres to connect to all databases.
   3. Server should be the IP address of the server.
   4. User Name should be the Postgres database username.
   5. Port should be the port used for the connection.
   6. Password should be the Postgres database password.
8. Click Save to save the configuration settings.

9. Click OK to close the ODBC Data Sources app.
10. Launch DIAdem.
11. From the SCRIPT Panel, select File >> New VBS.
12. Save the script.
13. Enter the following line to declare variables that will be used in the script. The variables are as follows:
    1. Conn: used to create an ADODB connection object.
    2. ConnectionString: used to define the connection parameters to the Postgres database.
    3. rs: used to hold a set of records from a database table.
    4. Record: used to copy records from rs into a 2D array.
    5. objFSO: used to create a File System Object.
    6. objFile: used to read from and write to a file.
    7. TableName: used to define the name of the Postgres database table.
    8. Item: used to iterate through records in the Record array.

Dim Conn, ConnectionString, rs, Record, objFSO, objFile, TableName, Item

14. Next, enter the below lines to create a connection to the Postgres database.
    1. Modify the Server, Port, Database, UID and PWD in ConnectionString for your specific database.

' Create connection
Set Conn = CreateObject("ADODB.Connection")
ConnectionString = "Driver={PostgreSQL UNICODE};Server=localhost;Port=5433;Database=databasename;UID=myusername;PWD=mypassword;"
Conn.Open ConnectionString

15. Define the name of the table to query and retrieve the record.
    1. The below code queries a table called "tablename".result and limits the record set to 10 rows.

'Query the Database table
Set rs = CreateObject("ADODB.recordset")
TableName = Chr(34) & "tablename" & Chr(34) 'Chr(34) ensures that double quotations surround the table name
rs.Open "select * from " & TableName & ".results limit 10", Conn

16. Store the records in an array and close the connection using the below lines.

Record = rs.GetRows()
rs.Close
Conn.Close

17. [OPTIONAL] Write the results to a file.
    1. Change the path defined in objFile to suit your requirements.

'Write results to file
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFile = objFSO.OpenTextFile("C:\dbData.txt", 8, True)

For Each Item In Record
  objFile.WriteLine (Str(Item))
Next

Once executed, the script will connect to the specified database and store the results in a file.

The attached VBS file can be downloaded to work with the example code shown throughout this article.