2

I have the following piece of code to read an Excel file and convert it into a Excel File:

var sql = "Select * from [#sheetName#$]";

CreateObject("java", "java.lang.Class").forName("sun.jdbc.odbc.JdbcOdbcDriver");

objInst = CreateObject("java","java.sql.DriverManager").getConnection(
    "jdbc:odbc:Driver={Microsoft Excel Driver (*.xls)};DBQ=" & arguments.fileName);

stmnt = objInst.createStatement();
sheetData = stmnt.executeQuery(sql);
qryData = CreateObject('java', 'coldfusion.sql.QueryTable').init(sheetData);
objInst.Close();

The code works fine in ColdFusion 8, 9, 10 and 11.

But when tried in Railo, I get the following error:

Railo 4.2.1.008 Error (java.sql.SQLException) Message [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified Cause java.sql.SQLException Stacktrace The Error Occurred in D:\Engineering\eclipse_workspace\simplymail\filename.cfm: line 22 20: objInst = CreateObject("java", 21: "java.sql.DriverManager").getConnection( 22: "jdbc:odbc:Driver={Microsoft Excel Driver (*.xls)};DBQ=" & arguments.fileName); 23: stmnt = objInst.createStatement(); 24: sheetData = stmnt.executeQuery(sql);

Unable to find the solution and posting here for a pointer.

Fish Below the Ice
  • 1,273
  • 13
  • 23
Naveen Krishnamurthy
  • 21
  • 1
  • 3
  • Did you look at your datasources to see if it's there? – Dan Bracuk Oct 24 '14 at 11:52
  • Why would you want to read an Excel file as if it was a database? This just seems like a horrible idea. – Scott Stroz Oct 24 '14 at 13:24
  • It can be useful scott - say for importing data FROM a spreadsheet that you need to filter. not ideal - but convenient for the end user (for whom excel is much less daunting than say Access) – Mark A Kruger Oct 24 '14 at 13:55
  • 2
    I would take it at face value. One thought - is case sensitivity an issue in Ralio? make sure you match case on the filename string. – Mark A Kruger Oct 24 '14 at 13:56
  • @MarkAKruger Yup, I've checked for the casing and that is not the issue.. – Naveen Krishnamurthy Oct 24 '14 at 15:37
  • @DanBracuk Nope, this is noway related to the databses.. It is just reading a excel file directly... Trying to go with this approach... Plan B would be to use the POI Utility... – Naveen Krishnamurthy Oct 24 '14 at 15:38
  • I tend to agree with Mark. It sounds like the issue is as described: problem finding a driver. When you say it "works fine" are you testing in the same environment or on different machines? Also, what is your o/s and which versions of CF/Railo are you using: 32bit or 64bit? – Leigh Oct 24 '14 at 22:10
  • @Leigh as mentioned, it is not the same environment. I have tested it with Adobe ColdFusion (different versions) and Railo. It works fine in Adobe CF but the error mentioned above pops up in Railo. – Naveen Krishnamurthy Oct 25 '14 at 00:46
  • @Naveen - By environment, I meant are you testing it on the same o/s and machine? If yes, then we know the drivers *are* installed because it works with ACF. The reason for asking is I get that same error testing the above code w/ACF9 64-bit on a machine that does not have the right Excel drivers installed. (Plus there can also be issues w/32 vs 64 bit drivers). So I suspect the error means exactly what it says: there is a problem finding/loading the driver. – Leigh Oct 25 '14 at 01:13
  • Nothing to do with the driver issue, but `coldfusion.sql.QueryTable` is an internal ACF class, so obviously [you need different code in Railo](http://stackoverflow.com/questions/2227134/how-does-one-convert-from-a-java-resultset-to-coldfusion-query-in-railo) – Leigh Oct 25 '14 at 01:28
  • @Leigh, Thank you for taking time to comment, I ran a test script and found that "coldfusion.sql.QueryTable" works for me in Railo The error persists in the following statement; objInst = CreateObject("java", "java.sql.DriverManager").getConnection( "jdbc:odbc:Driver={Microsoft Excel Driver (*.xls)};DBQ=" & arguments.fileName) – Naveen Krishnamurthy Oct 27 '14 at 06:49
  • Hm.. are you certain about that? I have not tried it recently, but I skimmed the source and do not see a 'coldfusion.sql.QueryTable`. As far as the error, did you see [my question about your environment](http://stackoverflow.com/questions/26547063/railo-microsoftodbc-driver-manager-data-source-name-not-found-and-no-defaul?noredirect=1#comment41737763_26547063)? – Leigh Oct 27 '14 at 10:57
  • @Leigh, Yes, the OS is the same Win 7 Ultimate 64 Bit. Yes, I did run a test file to create an object for the `coldfusion.sql.QueryTable` and was able to do so without a hassle. – Naveen Krishnamurthy Oct 27 '14 at 12:28
  • Ah, you are right about the class. But I just realized I remembered the issue incorrectly. You can create the object, but you will get an error when you try and call `init()`. Okay, and your ACF and Railo versions are both 64 bit? Are you able to create *any* type of ODBC datasource in Railo? – Leigh Oct 27 '14 at 13:26
  • Likely problem is bitness, if ColdFusion runs as 32-bit (or 64-bit) and matches the bitness of the DSN but Railo is a different bitness then this would explain everything. – KylePorter Oct 27 '14 at 16:48

0 Answers0