Forum Discussion

rohini11's avatar
rohini11
New Contributor
4 years ago

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

Sort By
    • Mr_Bro's avatar
      Mr_Bro
      Champion Level 0
      4 years ago

      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))

       

      • rohini11's avatar
        rohini11
        New Contributor
        4 years ago

        Hi Satish,

         

        Thanks for the reply.

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

  • AlexKaras's avatar
    AlexKaras
    Champion Level 3
    4 years ago

    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's avatar
      rohini11
      New Contributor
      4 years ago

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

       

       

       



      • AlexKaras's avatar
        AlexKaras
        Champion Level 3
        4 years ago

        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.