Connect to AWS RDS from TestComplete

[acondinho]

Hi,

We are currently running an SQL DB on AWS RDS, and I have a very simple use case that is frustratingly difficult to achieve. I want to select a single value from the DB based on a known parameter. This part is easy, it's actually establishing a connection to the DB that I cannot get working. With ReadyAPI, it's a simple connection and I am able to query like I expect.

Has anyone got a connection to an AWS RDS DB established that could shed some light on this?

[Kitt]

You can use ADO to create the connection, provided you have a valid connection string. Yours would look something like:

Server=tcp:example-endpoint-db.rds.amazonaws.com,1234; Initial Catalog=MyApp.DatabaseName; User=myappusername; Password=myapppassword; MultipleActiveResultSets=True; Application Name=MyApp.Api

You should be able to find your connection string using this [Amazon reference] then use it in the code below, which you can add to a TestComplete script

// executes sql statement capturing single result
function sqlQueryOne(query) {
  // set log and variables
  Log.Message("SQL Query: " + query);
  var AConnection, recSet, result;
  
  // create a connection object
  AConnection = ADO.CreateConnection();
  
  // specify the connection string
  AConnection.ConnectionString = "Provider=SQLNCLI11;"
    + "Server=tcp:" + ProjectSuite.Variables.dboDatabaseServer + ".database.windows.net;"
    + "Database=" + ProjectSuite.Variables.dboDatabase + ";"
    + "Uid=" + ProjectSuite.Variables.dboDatabaseUser + ";" 
    + "Pwd=" + ProjectSuite.Variables.dboDatabasePass.DecryptedValue + ";";
  
  // open the connection
  AConnection.Open();
  
  // execute a simple query
  recSet = AConnection.Execute(query);
  
  // validate results  
  if (!equal(recSet.Fields.Item(0).Value, "0")) {
    result = recSet.Fields.Item(0).Value;
    Log.Message("SQL Query Result: " + result);
  }
  else {
    Log.Warning("WARNING: no records found");
  }
  return result;
  
  // close the connection
  AConnection.Close();
}

And your main function to get the result within TestComplete would be something like:

function getSQLResult() {
   var result = sqlQueryOne("SELECT [column] FROM [table] WHERE [your condition]");
   Log.Message("DB RESULT: " + result);
}