Ask a Question

Connect to Mysql DB and write query to verify data

rohini11
New Contributor

Connect to Mysql DB and write query to verify data

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 
8 REPLIES 8
Marsha_R
Community Hero

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?


Marsha_R
[Community Hero]
____
[Community Heroes] are not employed by SmartBear Software but
are just volunteers who have some experience with the tools by SmartBear Software
and a desire to help others. Posts made by [Community Heroes]
may differ from the official policies of SmartBear Software and should be treated
as the own private opinion of their authors and under no circumstances as an
official answer from SmartBear Software.
The [Community Hero] signature is used with permission by SmartBear Software.
https://community.smartbear.com/t5/custom/page/page-id/hall-of-fame
Mr_Bro
Frequent Contributor

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
Community Hero

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'.

 

Regards,
  /Alex [Community Hero]
____
[Community Heroes] are not employed by SmartBear Software but
are just volunteers who have some experience with the tools by SmartBear Software
and a desire to help others. Posts made by [Community Heroes]
may differ from the official policies of SmartBear Software and should be treated
as the own private opinion of their authors and under no circumstances as an
official answer from SmartBear Software.
The [Community Hero] signature is used with permission by SmartBear Software.
https://community.smartbear.com/t5/custom/page/page-id/hall-of-fame
================================

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();
};

 

 

 



Hi Satish,

 

Thanks for the reply.

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

Mr_Bro
Frequent Contributor

@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();
 }

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.

 

Regards,
  /Alex [Community Hero]
____
[Community Heroes] are not employed by SmartBear Software but
are just volunteers who have some experience with the tools by SmartBear Software
and a desire to help others. Posts made by [Community Heroes]
may differ from the official policies of SmartBear Software and should be treated
as the own private opinion of their authors and under no circumstances as an
official answer from SmartBear Software.
The [Community Hero] signature is used with permission by SmartBear Software.
https://community.smartbear.com/t5/custom/page/page-id/hall-of-fame
================================
sonya_m
SmartBear Alumni (Retired)

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.


Sonya Mihaljova
Community and Education Specialist

cancel
Showing results for 
Search instead for 
Did you mean: