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:
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…
1 2 3 4 5 6 7 8 9 10 11 12 13 |
// 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!
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.
Thanks Tom. Yeah, looks like they’ve updated the userdata directory var name. Nice addition and bug catch!
Thanks, How to use integrated authentication?
Java constructor for “net.sf.sahi.plugin.DBClient” with arguments “string,string,string,string” not found on OpenSource version