Connect to Mysql DB and write query to verify data

[rohini11]

Hi, Im looking for a function to connect to MYsql Db 

and write query to verify data

function connDB() {
// Create and open a connection to the OrdersDB.mdb database

var connection = getActiveXObject("ADODB.Connection");
Connection.connectionString = "Provider=MSDASQL;Driver={MySQL ODBC 8.0 ANSI Driver};[Server="");Database="";User="";password=""Option=3;"; // use above format with your server/db details and assign the string value to this variable
connection.Open();

var rs = getActiveXObject("ADODB.Recordset");
rs.Open('select count(distinct STORE_CODE),CLAIM_PAY_DATE from eyos_dwh_uat.aud_campaign_claims where CLAIM_PAY_DATE ="2021-01-16"; ', connection);

connection.close;

}

ReferenceError: Connection is not defined

Please let me know if i can do database connection by project variables 

thanks 

[Marsha_R]

Here's some information about working with databases:

https://support.smartbear.com/testcomplete/docs/testing-with/working-with-external-data-sources/data...

Yes, you can use variables if you use the correct format.

What have you already tried and what happened when you did?

[Mr_Bro]

Please find the below python script to retrieve the data from database and log the count the record set.

import sys

sys.path.insert(0,r'c:\users\skuma37\appdata\roaming\python\python38\site-packages')

import mysql.connector

def SampleDBCheck():
  connection = mysql.connector.connect(
    host="HOSTNAME",
    database='DATABASENAME',
    user="USERNAME",
    password="PASSWORD"
  )
  sql_select_Query = "select * from SampleTable where id < 10"
  cursor = connection.cursor()
  cursor.execute(sql_select_Query)
  records = cursor.fetchall()
  Log.Message("Total number of rows in table: "+ str(cursor.rowcount))

[AlexKaras]

Hi,

> var connection = getActiveXObject("ADODB.Connection");
> Connection.connectionString = "Provider=MSDASQL;Driver={MySQL ODBC 8.0 ANSI Driver};

JScript/JavaScript is case-sensitive language, so use either 'connection' or 'Connection'.

[rohini11]

hi Alex, Thanks for the reply.

Currently I connected db using the below command but i need to get the query result. 

Please let me know how to overcome this issue. Thanks 

Error:

JavaScript runtime error.
Field 'date' not found

Error location:
Line: 64 Column: 18.

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

function Main()
{
var Qry;
var fieldName = "date";
// Create a query
Qry = ADO.CreateADOQuery();
// Specify the connection string
Qry.ConnectionString = 'db connection;
// Specify the SQL expression

Qry.SQL =' select count(distinct store_id) from tb_name where date = "2021-02-16"';

// Execute the query

Qry.Open();
// Process results and insert data into the test log
Log.AppendFolder("Disbursement");

Qry.First();
//EOF _Returns true if the current record position is after the last record, otherwise

while (! Qry.EOF)
{
// result = (Qry.FieldByName(fieldName).Value);
Log.Message(Qry.FieldByName(fieldName).Value);
Qry.Next();
};
// Close the query
Qry.Close();
};

[rohini11]

Hi Satish,

Thanks for the reply.

I'm beginner in coding and trying to work with Java script. 

[Mr_Bro]

@rohini11 ,

can you please try the below snippet by changing the necessary connecting string and query and tell us what is the error:

function TestProc()
 {
   var AConnection, RecSet;
   AConnection = ADO.CreateADOConnection();
   
   AConnection.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;"+
   "Data Source=C:\\Users\\Public\\Documents\\TestComplete 14 Samples\\Desktop\\Checkpoints\\XML\\DataGridViewSample\\OrdersDB.mdb";
   
   AConnection.LoginPrompt = false;
   AConnection.Open();

   // Execute a simple query
   RecSet = AConnection.Execute_("SELECT * FROM orders WHERE [quant] > 1");
   RecSet.MoveFirst();
   while(! RecSet.EOF)
   {
     //Print the recordset value into the Log 
     Log.Message(RecSet.Fields.Item("name").Value);
     RecSet.MoveNext();
   }
   AConnection.Close();
 }

[AlexKaras]

Hi,

> Field 'CLAIM_PAY_DATE' not found

I assume that:

-- This is the SQL been executed:

Qry.SQL =' select count(distinct STORE_CODE) from aud_campaign_claims where CLAIM_PAY_DATE = "2021-02-16"';

-- And this line of code throws the error:

Log.Message(Qry.FieldByName(fieldName).Value);

If the above is correct then this is correct behavior because the result of your query does not contain CLAIM_PAY_DATE field but a count of records with distinct value for the STORE_CODE field.

You need to check (using some MySQL or another tool or using the code) how this field is named in the returned recordset and use this name to get the value.

[sonya_m]

Thanks for participating in this thread, Community!

@rohini11 did the suggestions help? Please mark the best one as a Solution. This will help a lot of people going forward.