Talking testing, automation... and anything else.


How To Connect To SQL Server In Sahi

1373908671_databaseSahi includes JDBC support that allows you to read and write to popular databases. In this example we’ll be connecting to a SQL Server database.

The Gist…

First off, you need to download the Microsoft Java Database Connectivity (JDBC) driver for SQL Server. This is just a jar that Sahi uses to connect to SQL Server. I’m using version 4 in this example but other versions should work. Next, tell Sahi where to find the JDBC jar (putting it in Sahi’s extlib folder is a good idea). And finally, use the dang thing.

Download and install the JDBC

  1. Download the Microsoft JDBC
  2. Check the sqljdbc_4.0.2206.100_enu.tar.gz
  3. Unzip the file and navigate to Microsoft JDBC Driver 4.0 for SQL Server\sqljdbc_4.0\enu\
  4. Copy the file sqljdbc4.jar to Sahi’s exlib folder: eg. C:\sahi_pro\extlib\db\ (your Sahi path might be different)

Add the JDBC jar to Sahi’s dashboard.bat

  1. Open sahi_pro\userdata\bin\start_dashboard.bat and add the following line:
  2. SET SAHI_EXT_CLASS_PATH=%SAHI_USERDATA_DIR%\extlib\db\sqljdbc4.jar;

Use it!

Now that you have the JDBC driver installed and configured in Sahi, it’s time to use it. Sahi includes a method _getDB to handle calls to a database. It uses two other methods, select and update to do the heavy lifting. The gist here is we create a variable to handle the db connection (which returns an object) and then use that object to select or update data in the database.

Here’s an example…

// config... 
var $dbServer = "localhost";
var $dbUser = "sa";
var $dbPass = "password";
var $dbName = "myDB"; 

// create a handle object...
var $db = _getDB("com.microsoft.sqlserver.jdbc.SQLServerDriver","jdbc:sqlserver://"+$dbServer+";databaseName="+$dbName+"", $dbUser, $dbPass);

// get all rows from a table...
var $rows = $db.select("Select * from MyTable");
_alert($rows.length); // output number of rows returned...
_alert($rows[0].toString()); // output the first row returned...

That’s it!

4 comments

  1. Perhaps my install is different from yours but I needed to have:

    SET SAHI_EXT_CLASS_PATH=%SAHI_USERDATA_DIR%\extlib\db\sqljdbc4.jar;

    set to this instead:

    SET SAHI_EXT_CLASS_PATH=%SAHI_HOME%\extlib\db\sqljdbc4.jar;

    As my directory is in: C:\SahiPro\extlib\db

    Also, in line 8 of your example you have two var = $db commands back to back.

     

  2. Thanks, How to use integrated authentication?

  3. Java constructor for “net.sf.sahi.plugin.DBClient” with arguments “string,string,string,string” not found on OpenSource version

Leave a Reply