using jdbc for excel and db connection

muryan

Hi everyone,

I need to use _getDB function to access data in excel files or mySql databases. As I read in the "working with excel sheet" section of documentation, I am using:

1var db = _getDB("sun.jdbc.odbc.jdbcOdbcDriver","jdbc:odbc:Driver={Microsoft Excel Driver(*.xls,*.xlsx,*.xlsm,*xlsb)};DBQ=C://Users/ospite/Downloads/Sahi/xlsInputs/example.xls;readOnly=true","","");

$rs = db.select("select * from [Sheet1$]");

but this select operation launches a "ClassNotFoundException: sun.jdbc.odbc.jdbcOdbcDriver" error, so I guess I missed something about download and/or configuring jdbc... I also read _getDB documentation but failed to understand clearly.
I am running the latest JRE released on windows 7 pro.
What do I need to download (and how do I configure it) to have my scripts working with excel sheet and mySql?

Thanks a lot to anyone,

Muryan

muryan

Update:

I re-read "working with excel sheets" documentation and now I have:

var db = _getDB("sun.jdbc.odbc.JdbcOdbcDriver","jdbc:odbc:Driver={Microsoft Excel Driver(*.xls,*.xlsx,*.xlsm,*xlsb)};DBQ=C://Users/ospite/Downloads/Sahi/xlsInputs/example.xls;readOnly=true","","");

but I'm now getting another error:

[Microsoft][Driver Manager ODBC] Nome origine dati non trovato e driver predefinito non specificato

which I would translate from italian this way: "data source name not found, driver undefined"

Thanks again,

Muryan

narayan

C://Users/ospite/Downloads/Sahi/xlsInputs/example.xls

should be

C:\\Users\\ospite\\Downloads\\Sahi\\xlsInputs\\example.xls

That was a little unclear in the documentation. Will fix it.

Thanks,
Narayan

pradeek

if you want to connect to mysql you need mysql-connector.jar configure with sahi by giving correct path if you are confusing with the path just put connector.jar to your /jre/lib/ext folder
if path is not configured correctly you will get a error message driver class not found.

code to connect mysql with sahi:
var db = _getDB("com.mysql.jdbc.Driver", "jdbc:mysql://localhost:3306/databasename", "root", "password");

if you want to write data of your database to excel sheet as csv format:

_writeCSVFile($rs,"C:\\SAHI\\userdata\\scripts\\file.csv");


if you want to take data from excel file here is the code:

var db = _getDB("sun.jdbc.odbc.JdbcOdbcDriver", "jdbc:odbc:Driver={Microsoft Excel Driver (*.xls)};DBQ=C:\\sahi\\userdata\\scripts\\file.xls;readOnly=false", "", "");

saniya

Hi,
I need to use MS access database.I have been through your forums. And I think that following should work

var $db = _getDB("sun.jdbc.odbc.JdbcOdbcDriver", "jdbc:odbc:Driver={Microsoft Access Driver (*.mdb,)};DBQ=C:\\sahi\\userdata\\db\\test.mdb ;readOnly=false", "", "");
var $rs= $db.select("select * from 'test1'");

But this is not working. Gives me following error:
exception: java.sql.SQLException: [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified

I have managed to work with excel files with following code
var $db = _getDB("sun.jdbc.odbc.JdbcOdbcDriver", "jdbc:odbc:Driver={Microsoft Excel Driver (*.xls)}; DBQ=C:\\sahi\\userdata\\db\\test.xls ;readOnly=false", "", "");
var $rs = $db.select("select * from [Sheet1$]");

Can you please help me with this problem??
Thanks,
Saniya

muryan

Thank you very much, Narayan and Pradeek

sivarajE!23

Hi to all,
This is sivaraj.E, please let me clarify this error message, I'm very new to Sahi


Starting script
ERROR
exception: java.sql.SQLException: [Microsoft][ODBC Excel Driver] The Microsoft Access database engine could not find the object 'Sheet1$'. Make sure the object exists and that you spell its name and the path name correctly. If 'Sheet1$' is not a local object, check your network connection or contact the server administrator.
at sun.jdbc.odbc.JdbcOdbc.createSQLException(Unknown Source)
at sun.jdbc.odbc.JdbcOdbc.standardError(Unknown Source)
at sun.jdbc.odbc.JdbcOdbc.SQLExecDirect(Unknown Source)
at sun.jdbc.odbc.JdbcOdbcStatement.execute(Unknown Source)
at sun.jdbc.odbc.JdbcOdbcStatement.executeQuery(Unknown Source)
at net.sf.sahi.plugin.DBClient.getResult(DBClient.java:101)
at net.sf.sahi.plugin.DBClient.select(DBClient.java:79)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
at java.lang.reflect.Method.invoke(Unknown Source)
at org.mozilla.javascript.MemberBox.invoke(MemberBox.java:161)
at org.mozilla.javascript.NativeJavaMethod.call(NativeJavaMethod.java:247)
at org.mozilla.javascript.Interpreter.interpretLoop(Interpreter.java:3330)
at org.mozilla.javascript.Interpreter.interpret(Interpreter.java:2487)
at org.mozilla.javascript.InterpretedFunction.call(InterpretedFunction.java:164)
at org.mozilla.javascript.ContextFactory.doTopCall(ContextFactory.java:398)
at org.mozilla.javascript.ScriptRuntime.doTopCall(ScriptRuntime.java:3065)
at org.mozilla.javascript.InterpretedFunction.exec(InterpretedFunction.java:175)
at org.mozilla.javascript.Context.evaluateString(Context.java:1104)
at net.sf.sahi.rhino.RhinoScriptRunner.run(RhinoScriptRunner.java:114)
at java.lang.Thread.run(Unknown Source) at Aug 21, 2012 4:10:42 PM
Stopping script




This is code for this error.

function login($uname, $pwd)
{
_setValue(_textbox("user"), $uname);
_setValue(_password("password"), $pwd);
_click(_submit("Login"));
}
var db = _getDB("sun.jdbc.odbc.JdbcOdbcDriver", "jdbc:odbc:Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};DBQ=C:\Documents and Settings\sivaraje\Desktop\sheet1.xls;readOnly=false", "", "");
$rs = db.select("select * from [Sheet1$]");
login($rs[0]["uname"], $rs[0]["password"]);