How To Connect To SQL Server In Sahi

Sahi 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

Download the Microsoft JDBC
Check the sqljdbc_4.0.2206.100_enu.tar.gz
Unzip the file and navigate to Microsoft JDBC Driver 4.0 for SQL Server\sqljdbc_4.0\enu\
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

Open sahi_pro\userdata\bin\start_dashboard.bat and add the following line:

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

1	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...

// 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!

This entry was posted on Thursday, July 11th, 2013 at 10:11 pm and is filed under Code, How-Tos, Sahi. You can follow any comments to this entry through the RSS 2.0 feed. You can skip to the end and leave a comment. Pinging is currently not allowed.

4 comments

Tom (NH)

Sep 18, 2013 at 2:32 pm

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.

Reply
- Brian Ray
  
  Sep 18, 2013 at 3:46 pm
  
  Thanks Tom. Yeah, looks like they’ve updated the userdata directory var name. Nice addition and bug catch!
  
  Reply
Nitin G

Mar 23, 2017 at 10:50 am

Thanks, How to use integrated authentication?

Reply
Nitin G

Mar 23, 2017 at 11:37 am

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

Reply

Quality Shepherd towards better software…

Talking testing, automation... and anything else.