GeeCON Prague 2014*
The moose likes JDBC and the fly likes Failed to Fetch the hierarchial data from excel sheet using JDBC Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


JavaRanch » Java Forums » Databases » JDBC
Bookmark "Failed to Fetch the hierarchial data from excel sheet using JDBC" Watch "Failed to Fetch the hierarchial data from excel sheet using JDBC" New topic
Author

Failed to Fetch the hierarchial data from excel sheet using JDBC

Naveen Kumar Gundu
Greenhorn

Joined: Sep 09, 2010
Posts: 1
Hi all,
I was trying to fetch the data from an excel sheet using the below query. The connection details are as follows.

public void ConnectToSource()
{
try
{
currPath = currPath.replace("\\", "\\\\");
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
String myDB = "jdbcdbcriver={Microsoft Excel Driver (*.xls)};DBQ="+currPath+"\\export_1.xls;"
+ "DriverID=22;READONLY=false";
con = DriverManager.getConnection(myDB,"","");
st = con.createStatement();
}
catch (Exception e) {System.out.println(e);}
}


rs = st.executeQuery("SELECT BookID, ParentID, Name FROM [export$] " +
" START WITH ParentID=" + parentID +
" CONNECT BY NOCYCLE PRIOR BookID=ParentID" +
" WITH OWNERACCESS OPTION;");

I wrote in different styles like moving the WITH OWNERACCESS OPTION before the FROM and after the FROM keyword. But nothing had worked out and keeps giving me the following error.

java.sql.SQLException: [Microsoft][ODBC Excel Driver] Syntax error in WITH OWNERACCESS OPTION declaration.
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 Convert.actionPerformed(Convert.java:184)
at javax.swing.JComboBox.fireActionEvent(Unknown Source)
at javax.swing.JComboBox.setSelectedItem(Unknown Source)
at javax.swing.JComboBox.setSelectedIndex(Unknown Source)
at javax.swing.plaf.basic.BasicComboPopup$Handler.mouseReleased(Unknown Source)
at java.awt.AWTEventMulticaster.mouseReleased(Unknown Source)
at java.awt.Component.processMouseEvent(Unknown Source)
at javax.swing.JComponent.processMouseEvent(Unknown Source)
at javax.swing.plaf.basic.BasicComboPopup$1.processMouseEvent(Unknown Source)
at java.awt.Component.processEvent(Unknown Source)
at java.awt.Container.processEvent(Unknown Source)
at java.awt.Component.dispatchEventImpl(Unknown Source)
at java.awt.Container.dispatchEventImpl(Unknown Source)
at java.awt.Component.dispatchEvent(Unknown Source)
at java.awt.LightweightDispatcher.retargetMouseEvent(Unknown Source)
at java.awt.LightweightDispatcher.processMouseEvent(Unknown Source)
at java.awt.LightweightDispatcher.dispatchEvent(Unknown Source)
at java.awt.Container.dispatchEventImpl(Unknown Source)
at java.awt.Window.dispatchEventImpl(Unknown Source)
at java.awt.Component.dispatchEvent(Unknown Source)
at java.awt.EventQueue.dispatchEvent(Unknown Source)
at java.awt.EventDispatchThread.pumpOneEventForFilters(Unknown Source)
at java.awt.EventDispatchThread.pumpEventsForFilter(Unknown Source)
at java.awt.EventDispatchThread.pumpEventsForHierarchy(Unknown Source)
at java.awt.EventDispatchThread.pumpEvents(Unknown Source)
at java.awt.EventDispatchThread.pumpEvents(Unknown Source)
at java.awt.EventDispatchThread.run(Unknown Source)



My purpose of this query to fetch the data which are interlinked to each other with BookID and ParentID. So, I want to fetch all the multiple level records by passing the first ParentID.
I also tried replacing this query with writing multilevel InnerJoin and left Join statement but that is giving me the SQL General Error. Here is the other query.

rs = st.executeQuery("select BOOK.BookID as a, BOOK.ParentID as b, BOOK.Name as c, " +
" LINES.name as LINENAME, LINES.BOOKTYPEID as LINEBKTYPE, LINES.BOOKID as LNBOOKID, " +
" LINES1.name as LINE1NAME, LINES1.BOOKTYPEID as LN1BOOKTYPE, LINES1.BOOKID as LN1BOKID, " +
" LINES2.name as LN2NAME, LINES2.BOOKTYPEID as LN2BOKTYPE, LINES2.BOOKID as LN2BOOKID, " +
" LINES3.name as LN3NAME, LINES3.BOOKTYPEID as LN3BOKTYPE, LINES3.BOOKID as LN3BOOKID, " +
" LINES4.name as LN4NAME, LINES4.BOOKTYPEID as LN4BOKTYPE, LINES4.BOOKID as LN4BOOKID " +
" from [export$] BOOK " +
" inner join [export$] LINES on LINES.PARENTID=BOOK.BOOKID " +
" left join [export$] LINES1 on LINES1.PARENTID=LINES.BOOKID " +
" LEFT JOIN [export$] LINES2 ON LINES2.PARENTID=LINES1.BOOKID " +
" left join [export$] LINES3 on LINES3.PARENTID=LINES2.BOOKID " +
" left join [export$] LINES4 on LINES4.PARENTID=LINES3.BOOKID " +
" where BOOK.ParentID = "+ parentID +
" ORDER BY BOOK.NAME, LINES.NAME, LINES1.NAME, LINES2.NAME, LINES3.NAME, LINES4.NAME;");

Could some one help me in this?

Thanks in Advance.
Naveen Kumar G.
Jan Cumps
Bartender

Joined: Dec 20, 2006
Posts: 2501
    
    8

Welcome to JavaRanch, Naveen Kumar Gundu .


OCUP UML fundamental and ITIL foundation
youtube channel
 
GeeCON Prague 2014
 
subject: Failed to Fetch the hierarchial data from excel sheet using JDBC