Results 1 to 9 of 9
  1. #1
    jasonwucinski is offline Member
    Join Date
    Feb 2011
    Posts
    64
    Rep Power
    0

    Default can java handle nested sub queries?

    Hello everyone,
    Im trying to run an sql Select query that has several sub queries. when i run it, though, i get an error (see below). I'm not sure why this error is occurring. If i print out the SQL query and run it against the db, it's fine. If i remove the sub queries, it's fine. anyone have any ideas why this is happening. I'm using Access as my db. bellow is the sql statement and the associated error:

    Java Code:
    SELECT 
    tblPatientMeds.PatientMedID,
    (Select  PossibleMedName from tblPossibleMeds where PossibleMedID =  tblPatientMeds.PossibleMedID),
    tblPatientMeds.DateStarted,
    tblPatientMeds.DateStopped,
    tblPatientMeds.IsSuspectedMed, 
     tblPatientMeds.PatientID,
     tblPatientMeds.PossibleMedID, 
    tblPatientMeds.MedDosageID,
     tblPatientMeds.MedRouteID, 
     tblPatientMeds.MedIntervalID, 
    tblPatientMeds.MedPerscriberID ,
    (Select  DosageSize from tblPossibleMedDosage where MedDosageID=  tblPatientMeds.MedDosageID),
    (Select  MedRoute from tblPossibleMedRoute where MedRouteID=  tblPatientMeds.MedRouteID) , 
    (Select  MedInterval from tblPossibleMedInterval where MedIntervalID=  tblPatientMeds.MedIntervalID) ,
    (Select  Perscriber from tblPossibleMedPerscriber where MedPerscriberID=  tblPatientMeds.MedPerscriberID)  
    FROM  
    tblPatientMeds  WHERE (((tblPatientMeds.[PatientID])='7c7e811b-6e91-4f65-9735-1a40c36fd0e4'));
    printStackTrace:
    Java Code:
    java.sql.SQLException: General error
    	at sun.jdbc.odbc.JdbcOdbc.createSQLException(JdbcOdbc.java:6986)
    	at sun.jdbc.odbc.JdbcOdbc.standardError(JdbcOdbc.java:7114)
    	at sun.jdbc.odbc.JdbcOdbc.SQLExecDirect(JdbcOdbc.java:3110)
    	at sun.jdbc.odbc.JdbcOdbcStatement.execute(JdbcOdbcStatement.java:338)
    	at sun.jdbc.odbc.JdbcOdbcStatement.executeQuery(JdbcOdbcStatement.java:253)
    	at sun.jdbc.odbc.JdbcOdbcResultSet.calculateRowCount(JdbcOdbcResultSet.java:6353)
    	at sun.jdbc.odbc.JdbcOdbcResultSet.initialize(JdbcOdbcResultSet.java:155)
    	at sun.jdbc.odbc.JdbcOdbcStatement.getResultSet(JdbcOdbcStatement.java:424)
    	at sun.jdbc.odbc.JdbcOdbcStatement.executeQuery(JdbcOdbcStatement.java:254)
    	at eadr.dbClass.LoadMeds(dbClass.java:94)
    	at eadr.EADRView.tabStateChange(EADRView.java:2243)
    	at eadr.EADRView.access$800(EADRView.java:33)
    	at eadr.EADRView$4.stateChanged(EADRView.java:361)
    	at javax.swing.JTabbedPane.fireStateChanged(JTabbedPane.java:400)
    	at javax.swing.JTabbedPane$ModelListener.stateChanged(JTabbedPane.java:253)
    	at javax.swing.DefaultSingleSelectionModel.fireStateChanged(DefaultSingleSelectionModel.java:116)
    	at javax.swing.DefaultSingleSelectionModel.setSelectedIndex(DefaultSingleSelectionModel.java:50)
    	at javax.swing.JTabbedPane.setSelectedIndexImpl(JTabbedPane.java:599)
    	at javax.swing.JTabbedPane.setSelectedIndex(JTabbedPane.java:574)
    	at javax.swing.plaf.basic.BasicTabbedPaneUI$Handler.mousePressed(BasicTabbedPaneUI.java:3628)
    	at java.awt.Component.processMouseEvent(Component.java:6285)
    	at javax.swing.JComponent.processMouseEvent(JComponent.java:3267)
    	at java.awt.Component.processEvent(Component.java:6053)
    	at java.awt.Container.processEvent(Container.java:2041)
    	at java.awt.Component.dispatchEventImpl(Component.java:4651)
    	at java.awt.Container.dispatchEventImpl(Container.java:2099)
    	at java.awt.Component.dispatchEvent(Component.java:4481)
    	at java.awt.LightweightDispatcher.retargetMouseEvent(Container.java:4577)
    	at java.awt.LightweightDispatcher.processMouseEvent(Container.java:4235)
    	at java.awt.LightweightDispatcher.dispatchEvent(Container.java:4168)
    	at java.awt.Container.dispatchEventImpl(Container.java:2085)
    	at java.awt.Window.dispatchEventImpl(Window.java:2478)
    	at java.awt.Component.dispatchEvent(Component.java:4481)
    	at java.awt.EventQueue.dispatchEventImpl(EventQueue.java:643)
    	at java.awt.EventQueue.access$000(EventQueue.java:84)
    	at java.awt.EventQueue$1.run(EventQueue.java:602)
    	at java.awt.EventQueue$1.run(EventQueue.java:600)
    	at java.security.AccessController.doPrivileged(Native Method)
    	at java.security.AccessControlContext$1.doIntersectionPrivilege(AccessControlContext.java:87)
    	at java.security.AccessControlContext$1.doIntersectionPrivilege(AccessControlContext.java:98)
    	at java.awt.EventQueue$2.run(EventQueue.java:616)
    	at java.awt.EventQueue$2.run(EventQueue.java:614)
    	at java.security.AccessController.doPrivileged(Native Method)
    	at java.security.AccessControlContext$1.doIntersectionPrivilege(AccessControlContext.java:87)
    	at java.awt.EventQueue.dispatchEvent(EventQueue.java:613)
    	at java.awt.EventDispatchThread.pumpOneEventForFilters(EventDispatchThread.java:269)
    	at java.awt.EventDispatchThread.pumpEventsForFilter(EventDispatchThread.java:184)
    	at java.awt.EventDispatchThread.pumpEventsForHierarchy(EventDispatchThread.java:174)
    	at java.awt.EventDispatchThread.pumpEvents(EventDispatchThread.java:169)
    	at java.awt.EventDispatchThread.pumpEvents(EventDispatchThread.java:161)
    	at java.awt.EventDispatchThread.run(EventDispatchThread.java:122)

  2. #2
    masijade is offline Senior Member
    Join Date
    Jun 2008
    Posts
    2,571
    Rep Power
    9

    Default

    The standard JDBC Drivers have no problem with it. You, unfortunately for you, are using the Bridge, which then uses an ODBC Driver to connect to the DB (which I assume is probably Access) and your problem lies there. Use a real DB and a real JDBC driver rather than a bridge and relying on an ODBC Driver.

  3. #3
    jasonwucinski is offline Member
    Join Date
    Feb 2011
    Posts
    64
    Rep Power
    0

    Default

    thanks but response like that aren't very helpful. I realize that Microsoft access isn't a full DBMS and has limitation but, as an employee tasked with job, I don't always get to choose what DBMS to use. You are right, MSSQL or SQL lite db would be better but that is not an option for me.

    As for the bridge issue, unfortunately i ran into a problem awhile ago about using the bridge on a windows 7 64bit OS. I posted a question about this awhile back but no one responded so I had to chose to use the method below. Recap for that issue is that it seems that I cannot setup an ODBC datasource for access in the 64 bit OS. if you can help with that problem, I would appreciate it as well. (you can see the full post here: how to run java app on 32bit OS



    So, my original question still stands, if anyone can help with that.

    thanks
    jason

  4. #4
    masijade is offline Senior Member
    Join Date
    Jun 2008
    Posts
    2,571
    Rep Power
    9

    Default

    Whether you consider it helpful or not is irrelevant. It's the truth. P.S. the DSN drivers not being available on a 64 bit system is not Java's fault, of course. One other thing, you do know that when there is a space in the path to the DB file that it will need to be surrounded by quotes, right? Your "printed" version of the URL did not contain any.

  5. #5
    jasonwucinski is offline Member
    Join Date
    Feb 2011
    Posts
    64
    Rep Power
    0

    Default

    thanks for the reply. as I said in the above post, I realize that Access is not a full DBMS but I am forced to work with it. So, I guess that would be what is relevant :). As for not being Java's fault, I understand that. That post was looking for any sort of help or insight.
    What are you referring to when you said my printed URL had no space? do you mean in my other DSN post? if so, where and why would there be any spaces? as i mentioned in that post, I am confident that the URL to the db is correct, as i am still using that URL and it works fine. is there something else i'm missing?

    thanks
    jason

  6. #6
    masijade is offline Senior Member
    Join Date
    Jun 2008
    Posts
    2,571
    Rep Power
    9

    Default

    Quote Originally Posted by jasonwucinski View Post
    thanks for the reply. as I said in the above post, I realize that Access is not a full DBMS but I am forced to work with it. So, I guess that would be what is relevant :). As for not being Java's fault, I understand that. That post was looking for any sort of help or insight.
    What are you referring to when you said my printed URL had no space? do you mean in my other DSN post? if so, where and why would there be any spaces? as i mentioned in that post, I am confident that the URL to the db is correct, as i am still using that URL and it works fine. is there something else i'm missing?

    thanks
    jason
    I am saying, that in your other post the path to the file in the url contained a space and the path was not quoted, and it should have been quoted.

  7. #7
    jasonwucinski is offline Member
    Join Date
    Feb 2011
    Posts
    64
    Rep Power
    0

    Default

    well, in the event that someone else comes across this issue, It seems as though using nested sub queries causes an issue with Java/Access but using joins works. both accomplish the same thing just differently. In my case i used a Left Join

  8. #8
    masijade is offline Senior Member
    Join Date
    Jun 2008
    Posts
    2,571
    Rep Power
    9

    Default

    I just really looked at your query, and all I can say is that using a select query as a select field is not valid SQL. Access may allow it, but that makes it an Access feature, but either the Bridge or the ODBC Driver rejected it as it is not valid SQL.

    Edit: I had assumed that by "nested" queries you meant "sub" queries, i.e. a query used as a "from" element, which is valid SQL.

  9. #9
    jasonwucinski is offline Member
    Join Date
    Feb 2011
    Posts
    64
    Rep Power
    0

    Default

    hey thanks! I guess you were just biding your time to "really" look at the original question and drop some pearls of wisdom. just kidding, thanks for the help.

Similar Threads

  1. How to handle Transaction IN and OUT in Inventory using java
    By techsing14 in forum JavaServer Pages (JSP) and JSTL
    Replies: 0
    Last Post: 04-13-2011, 03:27 AM
  2. Replies: 20
    Last Post: 03-22-2011, 04:02 AM
  3. need help with java nested exception.
    By trusiekpt in forum Advanced Java
    Replies: 4
    Last Post: 03-01-2011, 09:13 AM
  4. java.lang.NoClassDefFound --Any way to handle this
    By sunny_sykes in forum Advanced Java
    Replies: 1
    Last Post: 03-30-2009, 08:29 AM
  5. Porblem Executing Queries in Java
    By ajit.mishra@gs.com in forum New To Java
    Replies: 1
    Last Post: 09-05-2008, 07:31 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •