Comments

• Cubicle.Jockey Members

  August 2008

  I am not sure what your problem is but I am in agreement that there needs to be a major update to the help files. If I understood the objects I could use them properly! I think my issue is more related to the repeated socket generation when it only needs to get one socket.
• StringyLow Members

  August 2008

  Hey Keats...
  
  This is a group effort here.
  
  If you think the docs need to be updated, please, by all means, do so and post them to the forum.
  
  As for the decimal issue, load the data into a variable and then concatenate an empty string to the variable to cast the data type to string.
  
  Example:
  
  $data = Excel[data];
  $data = $data + "";
• sumeet Members

  September 2008

  Instead doing thise you can simply use - parseInt($data) you will get rid of that extra decimal
• narayan Administrators

  September 2008

  OK. db connection and excel docs will be updated as per numerous requests If you have any working code samples (which you sure have) please post it here and I will include them in the docs.
  
  Thanks
  Narayan
• sumeet Members

  September 2008

  Here is code sample for accessing data from Excel sheet -
  
  =====================================================================
  
  var $maindrvr = "sun.jdbc.odbc.JdbcOdbcDriver";
  var $maindt = "jdbc:odbc:Driver={Microsoft Excel Driver (*.xls)};DBQ=C:/Sahi/TestData.xls";
  
  var $maindb = _getDB($maindrvr,$maindt, "", "");
  
  var $mainrs = [];
  
  $mainrs = $maindb.select("select * from [Sheet1$]");
  
  for (var $row=0; $row< $mainrs.length; $row++) // If you want to run iterations
  {
  
  _setValue(_textbox("userId", $mainrs [$row]["UserName"]);
  _setValue(_password("password", $mainrs [$row]["Password"]);
  _click(_byId("login"));
  
  }
  
  =====================================================================
• narayan Administrators

  September 2008

  Thanks Sumeet.
• saini.007 Members

  October 2008

  Hi Sumeet,
  
  1.) I as is copy pasted your code above code that is reading the Excel file for "Username" & "Password".
  
  2.) I have added the "rt.jar" to access the JDBC driver from JDK in the "extlib" folder of Sahi and referencing them in the sahi.bat and sahi.sh files.
  
  2.) Recreated the Data file by first making the cells as "TEXT".
  
  3.) My Code now looks as below which contains almost all the changes that you and Narayan have already suggested.
  ===========================================================================
  var $maindrvr = "sun.jdbc.odbc.JdbcOdbcDriver";
  var $maindt = "jdbc:odbc:Driver={Microsoft Excel Driver (*.xls)};DBQ=D:/sahi/Data/Users.xls";
  var $maindb = _getDB($maindrvr,$maindt, "", "");
  var $rs = [];
  $rs = $maindb.select("select * from [Sheet1$]");
  _setValue(_textbox("txtUserName"),$rs[0]["Username"]);
  _setValue(_password("txtPassword"),$rs[0]["Password"]);
  _click(_imageSubmitButton("Login"));
  _assertExists(_byId("WebPartManager1_gwpTimeSheet1_TimeSheet1_lblShift"));
  _assertContainsText("Current Shift : MORNING",_byId("WebPartManager1_gwpTimeSheet1_TimeSheet1_lblShift"));
  _click(_link("Logout"));
  ==================================================================================
  
  But it gives me same error (which i have posted in my another post), as mentioned in my post which comes in the command prompt of the Sahi Server: java.sql.SQLException: [Microsoft][ODBC Excel Driver] External table is not in the expected format.
  
  It seems that i should give up using "sun.jdbc.odbc.JdbcOdbcDriver".
  
  So i thought of using XLSQL instead and took following steps to try it:
  
  1.) Added xlSQL_Y7.jar in the extlib folder and made corresponding changes in the Sahi.bat & Sahi.sh
  2.) Used Driver name: com.nilostep.xlsql.jdbc.xlDriver
  3.) JDBC URL: jdbc:nilostep:excel:[D:/sahi/Data/] As per below explanation.
  JDBC URL: jdbc:nilostep:excel:[FullPathToTheDirectoryContainingTheExcelFiles]
  
  But Things did not worked, i am still at the same stage where i was..........If you feel i am asking genuine help, please help me.
• saini.007 Members

  October 2008

  Hey Guys,
  
  I am very happy to share with you that i was able to sucessfully pick values from ACCESS DB instead of EXCEL.
  
  Here goes my code:
  ========================================================
  var db = _getDB("sun.jdbc.odbc.JdbcOdbcDriver","jdbc:odbc:Driver={Microsoft Access Driver (*.mdb)};DBQ=D:/sahi/Data/db1.mdb","","");
  var $rs = [];
  _set($rs, db.select("select * from Logininfo"));
  _setValue(_textbox("txtUserName"), $rs[0]["Username"]);
  _setValue(_password("txtPassword"), $rs[0]["Password"]);
  _click(_imageSubmitButton("Login"));
  _assertExists(_byId("WebPartManager1_gwpTimeSheet1_TimeSheet1_lblShift"));
  _assertContainsText("Current Shift : MORNING",_byId("WebPartManager1_gwpTimeSheet1_TimeSheet1_lblShift"));
  _click(_link("Logout"));
  ===========================================================================
  
  Complete script execution went fine and did all steps as expected, but still gave me the below error in the results pane:
  
  Test Total Steps Failures Errors Success Rate
  DEMOscript01.sah 8 0 1 87%
  
  Starting script
  Error loading script. Firefox may point to the exact line. TypeError: '0.Username' is null or not an object
  No trace available
  _sahi.handleSet('\$rs' + 1, db.select("select * from Logininfo"));
  _setValue(_textbox("txtUserName"), "rajesh.saini");
  _setValue(_password("txtPassword"), "fiservpwd");
  _click(_imageSubmitButton("Login"));
  _assertExists(_byId("WebPartManager1_gwpTimeSheet1_TimeSheet1_lblShift"));
  _assertContainsText("Current Shift : MORNING",_byId("WebPartManager1_gwpTimeSheet1_TimeSheet1_lblShift"));
  _click(_link("Logout"));
  Stopping script
  
  Is there a way to get Clean and correct results when the script ran successfully in this case????????
• pankaj.nith Members

  October 2008

  hi,
  
  Add line
  $rs[0] = [];
  after
  var $rs = [];
  I mean make $rs[0] as a two dimensional array and see if it solves your problem.
  
  Regards,
  Pankaj.
• sumeet Members

  October 2008

  What MS-Excel version you are using?
• saini.007 Members

  October 2008

  Summet,
  On my machine i have both Office 2003 and 2007, but i have saved the file as .xls instead of .xlsm
  
  Pankaj,
  Thanks!!! your solution solved my problem.
• pankaj.nith Members

  October 2008

  Welcome
• sumeet Members

  October 2008

  I don't see problem using the Sun JDBC ODBC and there is no need to give reference of rt.jar in any of the file. I use excel to run my scirpts and I never had problem except when there is numeric data in excel then Sun JDBC ODBC returns null.