how to connect to postgreSQL DB

[Jurij]

Hello,

We want to enter something on the web page and then check it if it was stored in the database. We use postgreSQL DB. I have found this example below which we would use, but the problem is that we don't know how to connect to the DB:

'Define a new SQL connection with a connection string.
'The connection string will be different depending on your environment and the name of the database, table, etc.
'See http://www.connectionstrings.com for connection string examples.
Dim thisConnection As New SqlConnection("Data Source=localhost,5556;Network Library=DBMSSOCN;Initial Catalog=myTable;User ID=username;Password=password;")
thisConnection.Open()

'Write the name of the database to the log
Log.WriteLine(thisConnection.Database)

'Create an SQL command
Dim thisCommand As SqlCommand = thisConnection.CreateCommand()

'This is a simple SQL command that will go through all the values from the table "myTable"
thisCommand.CommandText = "SELECT * FROM myTable"

If I try the test option when connecting to the DB I get errors (see attached picture). Note that we have to connect via SSH tunnel to the DB, which is working fine.

We have also installed npgsql from http://npgsql.projects.pgfoundry.org/, but this doesn't help. Can you please help me set up the connection?

BR, Jurij

[Ivaylo]

Hello Jurij,

Here is how you access an SQL database.

The error message you are getting is that the server is not accessible, which is not Test Studio related problem, you either use the wrong connection string or your SQL server does not allow remote connection.

Regards,
Ivaylo
Telerik

 

[Jurij]

HI Ivaylo,

Link you provided is not related to database connection set up, but it shows how to add coded step. I'm not asking you to give me connection, what I'm asking is HOW I can connect to POSTGRESQL database in coded step from Test Studio. I've searched through this forum and found only how to connect to MS SQL, MySQL and SQL Express databases.

So can you point me to an example of connecting to POSTRESQL database?

BR, Jurij

[Cody]

Hi Jurij,

Connecting to a POSTGRESQL database in coded step is a general programming issue which falls outside of Test Studio support. We cannot help you with this, mostly because we have never done it and don't have a POSTGRESQL database setup to even try.

Your best option is to do an Internet search on how to connect to a POSTGRESQL database from code. What code you find can be put into a Test Studio coded step.


Regards,
Cody
Telerik

 

[Jurij]

Hi Cody,

Understood and successfully connected to PostgreSQL database.

Thanks for the hint.

BR, Jurij

[Cody]

Hello Jurij,

Excellent and thank you for the update. Would you mind sharing the code you put together that is working for you? May we share it in a KB article for our other customers looking for similar functionality?


Regards,
Cody
Telerik

 

[Jurij]

Hi Cody,

It's nothing special and already seen, here is the excerpt from the code:

-------------------------------------------------------------------------------------------------------------------------------------​

Imports Npgsql
Imports NpgsqlTypes
Imports System.Data.Common

Dim userEmail As Object = GetExtractedValue("extractedEmail").ToString
Dim myConnection As NpgsqlConnection = New NpgsqlConnection()
Dim myCommand As NpgsqlCommand = New NpgsqlCommand
Dim myQueue As String​

myConnection.ConnectionString = "Server=localhost;Port=​1234;Database=web_stage;User Id=​username;Password=password;"

myConnection.Open()

Log.WriteLine("Successfully connected to the web_stage database!")

myQueue = "SELECT email FROM web.email_queue"           
myCommand = New NpgsqlCommand(myQueue, myConnection)           
Dim myResult As NpgsqlDataReader = myCommand.ExecuteReader()​

Do While myResult.Read()
            Log.WriteLine("Getting results: " + myResult.GetValue(0))
            If userEmail = myResult.GetValue(0) Then
                    Log.WriteLine("User matches! " + userEmail + " = " + myResult.GetValue(0) + " in table web.email_queue")
                    Assert.AreNotEqual(userEmail, myResult.GetValue(0))
             Else
                    Log.WriteLine("User doesn't match! " + userEmail + " != " + myResult.GetValue(0))
                    Assert.AreEqual(userEmail, myResult.GetValue(0))
                End If
            Loop
            
            myResult.Close()           
            Log.WriteLine("Reader closed!")
            
            myConnection.Close()
            Log.WriteLine("Successfully disconnected from the web_stage database!")

-------------------------------------------------------------------------------------------------------------------------------------

I had to install Npgsql (https://github.com/npgsql/npgsql) and refer to it in Scripting options in the project settings.

BR, Jurij

 ​

[Cody]

Hi Jurij,

Thanks so much! We added it to our documentation queue. I've also given you some Telerik Points as a token of gratitude.


Regards,
Cody
Telerik