Forum Discussion

nassim's avatar
nassim
Occasional Contributor
13 years ago

How to read the result of a query in a database opened in HeidiSQL6?

Hi.I have let Test complete run a query in HeidiSQL6. It is a select count(*) query and has just one row and column as result. Now I want to read this result and save it to a variable in Test complete. It seems that test complete can not distinguish this result table as object. I was not able to read the result. Does anyone have an idea how to read it?

Thanks in advance

5 Replies

  • tristaanogre's avatar
    tristaanogre
    Esteemed Contributor
    What is the automation code that you are using for this?  It depends upon what you're using to execute the query as to what it returns.
  • nassim's avatar
    nassim
    Occasional Contributor
    Hi.I need to substract the results of two select count(*) queries from each other and check if the substraction result is 1. I tried to do a part with database table checkpoint without success. The test runs on a VM. To connect to the database, a TCP/IP connection to a server should be done. After I could not do it via database table checkpoint, I tried to record opening HeidiSQL and typing the query and make it run. However, I have now problems while the query is not typed correctly during playback. Have you an idea how to do the job in a better way so that I can have the query result in a variable?

    Thank you very much
  • AlexeyK's avatar
    AlexeyK
    SmartBear Alumni (Retired)

    Nassim,


    As far as I understand, you need to perform two "count" queries (these queries return numbers) and then check if the difference between these numbers is 1.

    You can do this by using the following code snippet. I don't know which language you use in your project, so I wrote it in VBScript. You will need to change the connection string. Please read about this below.

    Here is the code --




    Sub DBTest

      ' Define connection. You need to change connection string in your tests

      Set connection = ADO.CreateConnection

      connection.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" + _

     
              "Data Source=C:\Program Files\Microsoft Office\OFFICE11\SAMPLES\Northwind.mdb"

     

      ' Open connection

      connection.Open

     

      ' Execute the first query

      Set query1 = ADO.CreateRecordset

      query1.Open "select count(*) from YOUR_TABLE_NAME_1", connection, -1, -1, adCmdText

     

      ' Execute another query

     
    Set query2 = ADO.CreateRecordset

      query2.Open "select count(*) from YOUR_TABLE_NAME_2", connection, -1, -1, adCmdText

     

      ' Get results  

      ' The count query returns a recordset with one row and one column,

      ' so you can address it by index

      Number1 = query1.Fields.Item(0).Value

      Number2 = query2.Fields.Item(0).Value

     

      ' Get the difference

     
    Diff = Number1 - Number2

     

      ' Process the result

      If Diff <> 1 Then

        ' If the difference is not 1, output error info to the test log

        Log.Error "Error! Diff <> 1", _

                  "Result1 = " + CStr(Number1) + Chr(13) + Chr(10) + _

                  "Result2 = " + CStr(Number2) + Chr(13) + Chr(10) + _

                  "Diff = " + CStr(Diff) 

      Else

        ' Else, post a success message

        Log.Message "Success. Diff = 1"

      End If 



      ' Close the recordsets and the connection

      query1.Close

      query2.Close

      connection.Close

    End Sub


    NOTE: This code works for a database that resides on local hard drive. To work with your SQL database, you need to specify another connection string. I'm not a database programming guru, and I don't know which connection strings are used for HeidiSQL. Most likely, it will look like this --


    Driver ={MySQL ODBC 3.51 Driver}; Server =myServerAddress; Database =myDataBase; User =myUsername; Password =myPassword; Option =3;


    If possible, ask your developers to assist you with the connection string.

  • swapnaliuk's avatar
    swapnaliuk
    Occasional Contributor

    Hi,

     

    Have you managed to get the answer of this query, I am also looking for the solution of same problem. Could you please forward the solution if you have found one?

    • Colin_McCrae's avatar
      Colin_McCrae
      Community Hero

      Can you be more specific to your problem rather than dragging up a 5 year old thread? (So the author is ulikely to see it and reply to you)

       

      Are you in EXACTLY the same position?

       

      Is the DB remote or local?

      How are you connecting?

      What is the query you are trying to execute and what are you trying to do with the result?

       

      Probably better if you start a new, and more relevant, thread with up to date information.

       

      I run counts, assertions and row/field validations against SQL DB's using ADO connections no problem.