Overview¶

Squish supports accessing databases indirectly through respective modules for the scripting languages (Python, Perl, Tcl, Ruby, except for JavaScript).

Another solution for accessing databases is to use external programs which perform the desired database accesses.

This article demonstrates using two such an external (example) programs.

Accessing databases via external tool and JDBC¶

JavaScript example¶

• Step #1 - Download JSQLExec.class and put it into the test suite folder. (The source code for JSQLExec.class can be downloaded JSQLExec.java .)

• Step #2 - Add these two functions to your JavaScript test script:

  function exec_sql(jsqlexec_cfg, jdbc_driver_path, jsqlexec_path, sql_statement)
  {
      var cwd = squishinfo.testCase;
      var input_file_name = "input.txt";
      write_file_utf8(input_file_name, sql_statement);
  
      var pathsep = ":";
      if (OS.name == "Windows") {
          pathsep = ";";
      }
  
      var cmd = 'cd "%s" & java -cp "%s' + pathsep + '%s" JSQLExec "%s"';
      cmd = cmd.replace("%s", cwd);
      cmd = cmd.replace("%s", jdbc_driver_path);
      cmd = cmd.replace("%s", jsqlexec_path);
      cmd = cmd.replace("%s", jsqlexec_cfg);
  
      test.log("exec_sql(): Executing: " + cmd);
      OS.system(cmd);
  }
  
  function write_file_utf8(file_name, content)
  {
      var f = File.open(file_name, "w");
      f.write(content);
      f.close();
  }
  

• Step #3 - Create file UTF-8 encoded file jsqlexec_select_cfg.txt in test suite folder and make it contain the following information on the exactly line numbers as specified:

  • Line #1:

    • select - For executing SELECT statements

    • update - For executing statements that modify the database (UPDATE, DELETE, etc.)

  • Line #2: JDBC driver class name

  • Line #3: JDBC URL

  • Line #4: Database user name

  • Line #5: Database password

  • Line #6: Path of UTF-8 encoded file with the database statements to be executed

  • Line #7: Path of UTF-8 encoded file where the results of the database statements will be written to in CSV (Comma Separated Values) format.

For example:

select
org.hsqldb.jdbc.JDBCDriver
jdbc:hsqldb:file:databases/testdb
administrator
password
input.txt
output.csv

• Step #4 - Execute SQL statement in your test script:

  function main()
  {
      // Execute SQL statement:
      var jsqlexec_cfg = squishinfo.testCase + "/jsqlexec_select_cfg.txt";
      var jdbc_driver_path = "hsqldb.jar";
      var jsqlexec_path = squishinfo.testCase + "/..";
      var sql_statement = "SELECT COLA, COLB FROM TABLE1";
      exec_sql(jsqlexec_cfg, jdbc_driver_path, jsqlexec_path, sql_statement);
  
      // Read generated output:
      var dataset = testData.dataset(squishinfo.testCase + "/output.csv");
      for (var i = 0; i < dataset.length; i++){
          var record = dataset[i];
          var value_cola = testData.field(record, "COLA");
          var value_colb = testData.field(record, "COLB");
          test.log("Next row:");
          test.log("  " + value_cola);
          test.log("  " + value_colb);
      }
  }
  
  function exec_sql(jsqlexec_cfg, jdbc_driver_path, jsqlexec_path, sql_statement)
  {
      var cwd = squishinfo.testCase;
      var input_file_name = "input.txt";
      write_file_utf8(input_file_name, sql_statement);
  
      var pathsep = ":";
      if (OS.name == "Windows") {
          pathsep = ";";
      }
  
      var cmd = 'cd "%s" & java -cp "%s' + pathsep + '%s" JSQLExec "%s"';
      cmd = cmd.replace("%s", cwd);
      cmd = cmd.replace("%s", jdbc_driver_path);
      cmd = cmd.replace("%s", jsqlexec_path);
      cmd = cmd.replace("%s", jsqlexec_cfg);
  
      test.log("exec_sql(): Executing: " + cmd);
      OS.system(cmd);
  }
  
  function write_file_utf8(file_name, content)
  {
      var f = File.open(file_name, "w");
      f.write(content);
      f.close();
  }
  

Python example¶

• Step #1 - Download JSQLExec.class and put it into the test suite folder. (The source code for JSQLExec.class can be downloaded JSQLExec.java .)

• Step #2 - Add these two imports and these two functions to your Python test script:

  import os
  import codecs
  
  def exec_sql(jsqlexec_cfg, jdbc_driver_path, jsqlexec_path, sql_statement):
      cwd = squishinfo.testCase
      input_file_name = "input.txt"
      write_file_utf8(input_file_name, sql_statement)
  
      cmd = 'cd "%s" & java -cp "%s' + os.pathsep + '%s" JSQLExec "%s"'
      cmd = cmd % (cwd, jdbc_driver_path, jsqlexec_path, jsqlexec_cfg)
  
      test.log("exec_sql(): Executing: " + cmd)
      os.system(cmd)
  
  def write_file_utf8(file_name, content):
      f = codecs.open(file_name, "w", "utf8")
      f.write(content)
      f.close()

• Step #3 - Create file UTF-8 encoded file jsqlexec_select_cfg.txt in test suite folder and make it contain the following information on the exactly line numbers as specified:

  • Line #1:

    • select - For executing SELECT statements

    • update - For executing statements that modify the database (UPDATE, DELETE, etc.)

  • Line #2: JDBC driver class name

  • Line #3: JDBC URL

  • Line #4: Database user name

  • Line #5: Database password

  • Line #6: Path of UTF-8 encoded file with the database statements to be executed

  • Line #7: Path of UTF-8 encoded file where the results of the database statements will be written to in CSV (Comma Separated Values) format.

For example:

select
org.hsqldb.jdbc.JDBCDriver
jdbc:hsqldb:file:databases/testdb
administrator
password
input.txt
output.csv

• Step #4 - Execute SQL statement in your test script:

  import os
  import codecs
  
  def main():
      # Execute SQL statement:
      jsqlexec_cfg = squishinfo.testCase + "/jsqlexec_select_cfg.txt"
      jdbc_driver_path = "hsqldb.jar"
      jsqlexec_path = squishinfo.testCase + "/.."
      sql_statement = "SELECT COLA, COLB FROM TABLE1"
      exec_sql(jsqlexec_cfg, jdbc_driver_path, jsqlexec_path, sql_statement)
  
      # Read generated output:
      dataset = testData.dataset(squishinfo.testCase + "/output.csv")
      for row in dataset:
          value_cola = testData.field(row, "COLA")
          value_colb = testData.field(row, "COLB")
          test.log("Next row:")
          test.log("  " + value_cola)
          test.log("  " + value_colb)
  
  def exec_sql(jsqlexec_cfg, jdbc_driver_path, jsqlexec_path, sql_statement):
      cwd = squishinfo.testCase
      input_file_name = "input.txt"
      write_file_utf8(input_file_name, sql_statement)
  
      cmd = 'cd "%s" & java -cp "%s' + os.pathsep + '%s" JSQLExec "%s"'
      cmd = cmd % (cwd, jdbc_driver_path, jsqlexec_path, jsqlexec_cfg)
  
      test.log("exec_sql(): Executing: " + cmd)
      os.system(cmd)
  
  def write_file_utf8(file_name, content):
      f = codecs.open(file_name, "w", "utf8")
      f.write(content)
      f.close()

Accessing databases via external tool and Python sqlite3 module¶

Python example¶

• Step 1 - Download execute_query.py and squishpyutil.py and put them into the test suite folder.

• Step 2 - Add these two imports and these two functions to your Python test script:

  import os
  import codecs
  
  def exec_sql_select(db_name, sql_statement, csv_output_file_name):
      query_file_name = squishinfo.testCase + "/query.txt"
      tool = squishinfo.testCase + "/../execute_query.py"
      write_file_utf8(query_file_name, sql_statement)
      cmd = ('python "%s" %s "%s" "%s" --testmode') % (tool, db_name, query_file_name, csv_output_file_name)
      test.log("exec_sql_select(): Executing: " + cmd)
      os.system(cmd)
  
  def write_file_utf8(file_name, content):
      f = codecs.open(file_name, "w", "utf8")
      f.write(content)
      f.close()

• Step #3 - Execute SQL statement in your test script:

  import os
  import codecs
  
  def main():
      # Execute SQL statement:
      sql_statement = "select idx, ch from characters"
      csv_output_file_name = squishinfo.testCase + "/output.csv"
      exec_sql_select("dummy_db_name", sql_statement, csv_output_file_name)
  
      # Read generated output file:
      dataset = testData.dataset(csv_output_file_name)
      for row in dataset:
          value_cola = testData.field(row, "Column1")
          value_colb = testData.field(row, "Column2")
          test.log("Next row:")
          test.log("  " + value_cola)
          test.log("  " + value_colb)
  
  def exec_sql_select(db_name, sql_statement, csv_output_file_name):
      query_file_name = squishinfo.testCase + "/query.txt"
      tool = squishinfo.testCase + "/../execute_query.py"
      write_file_utf8(query_file_name, sql_statement)
      cmd = ('python "%s" %s "%s" "%s" --testmode') % (tool, db_name, query_file_name, csv_output_file_name)
      test.log("exec_sql_select(): Executing: " + cmd)
      os.system(cmd)
  
  def write_file_utf8(file_name, content):
      f = codecs.open(file_name, "w", "utf8")
      f.write(content)
      f.close()

Related Information¶

• suite_qt_jsqlexec_js.zip

• suite_qt_jsqlexec_py.zip

• suite_qt_execute_query_py.zip

• Microsoft Access Database Engine 2010 Redistributable (32 & 64 bit)

• Service Pack 1 for Microsoft Access Database Engine 2010 (KB2460011) (32 bit and 64 bit)

• Executing external applications

• Accessing databases via external Python scripts

• Test Data Functions (Manual)

• Reading test data, data-driven testing

• scripting
• python
• javascript