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 downloadedJSQLExec.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(); }
Add to test script 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 statementsupdate
- 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(); }
test.js
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()
Add to test script 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 statementsupdate
- 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()
test.py
Accessing databases via external tool and Python sqlite3 module¶
Python example¶
Step 1 - Download
execute_query.py
andsquishpyutil.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()
test.py 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()
test.py