Connect an Oracle database via ODBC to Squish using JavaScript

Squish is able to connect to different databases and excecute all known SQL statements. Most of the databases need to be accessed by the ODBC driver because the database vendor’s license does not permit redistributing their client libraries without owning a license for their product.

The creation of a database was the next step after a successfull installation. A so called “database configurationassistent” was used for this step. Just follow the shown steps to create a new Database.

Now the login onto the administration website should work. Same for the SQL Developer. Please make sure that your login ID has enough privileges otherwise issues realted to “not enough rights” could pop up. The SQL Developer installtion seems broken on my machine so I had to install a new version. I just picked the latest one from the Oracle Website. A JDK installation is required to run the SQL Developer.
After loggin to the database, one create statment and several insert statements later the table in SQL Developer looks like this:

Note: The shown database entries are just a small example. Squish does support Python, JavaScript, Ruby, Perl and Tcl in all Editions.

As next step the ODBC Driver need to be prepared. ODBC is installed on all Windows machines by default. The only additionals are the drivers for other Software that want to communicate via ODBC. During the Oracle installation the Oracle ODBC Driver got installed as well. Please make sure that you are using the right architecture as squish does. A 32 Bit Squish package has problems with a 64 bit ODBC driver.

The Data Source Name could be anything, it’s just a symcolic name. The TSN Service Name should be created while the Database creation process. Otherwise there is a “net-configurationassistent” to get additional TSN names. The user ID should be the same as the login name for the SQL Developer. You can try the connection via the “Test Connection” button. “Connection successful” should be returned.

Now it’s time to install a Squish package. It doesn’t matter which package is used because no application is needed. Every Squish binary package is sufficient.
By using the following test Script …

function main() {
    var conn = SQL.connect( { 
        Driver: "ODBC",
        Port: 1521, // standard Port from Oracle side
        DataSource: "froglogic", // can be anything, need to be specified in ODBC
        Database: "FROGLOGIC", 
        UserName: "SYSTEM",
        Password: "sdxqwsd45zJs23fG" } );

    var result = conn.query("SELECT * FROM SYSTEM.SQUISH;");
    test.pass("OS" + "   -   " + "EDITION" + "   -   " + "SCRIPT");
    while (result.isValid) {
        test.log(result.value("OS") + "   -   "  + result.value("EDITION") + "   -   " + result.value("SCRIPT"));
        result.toNext();
    }
    conn.close();
}

… Squish will show all information stored in the databse.

A Windows 7 VM was used as operating system to install “Oracle Database 11g Release 2”. Higher versions of Oracle aren’t supported on a Windows 7 machine. The steps should be transferable to newer Orcale versions and operating systems.

Please take a look at the following page for additional information about the Sql connection https://doc.froglogic.com/squish/latest/index.html?q=result

0 Comments

Leave a reply

Your email address will not be published. Required fields are marked *

*