how to connect to postgreSQL DB

8 posts, 1 answers
  1. Jurij
    Jurij avatar
    54 posts
    Member since:
    Sep 2013

    Posted 02 Jul 2015 Link to this post

    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

  2. Ivaylo
    Admin
    Ivaylo avatar
    750 posts

    Posted 07 Jul 2015 Link to this post

    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
     
    The New Release of Telerik Test Studio Is Here! Download, install,
    and send us your feedback!
  3. Jurij
    Jurij avatar
    54 posts
    Member since:
    Sep 2013

    Posted 13 Jul 2015 in reply to Ivaylo Link to this post

    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

  4. Answer
    Cody
    Admin
    Cody avatar
    3360 posts

    Posted 13 Jul 2015 Link to this post

    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
     
    The New Release of Telerik Test Studio Is Here! Download, install,
    and send us your feedback!
  5. Jurij
    Jurij avatar
    54 posts
    Member since:
    Sep 2013

    Posted 29 Jul 2015 in reply to Cody Link to this post

    Hi Cody,

    Understood and successfully connected to PostgreSQL database.

    Thanks for the hint.

    BR, Jurij

  6. Cody
    Admin
    Cody avatar
    3360 posts

    Posted 29 Jul 2015 Link to this post

    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
     
    The New Release of Telerik Test Studio Is Here! Download, install,
    and send us your feedback!
  7. Jurij
    Jurij avatar
    54 posts
    Member since:
    Sep 2013

    Posted 30 Jul 2015 in reply to Cody Link to this post

    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

     ​

  8. Cody
    Admin
    Cody avatar
    3360 posts

    Posted 30 Jul 2015 Link to this post

    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
     
    The New Release of Telerik Test Studio Is Here! Download, install,
    and send us your feedback!
Back to Top