File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
The moose likes JDBC and the fly likes Looks right to me Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Looks right to me" Watch "Looks right to me" New topic
Author

Looks right to me

Al Wells
Ranch Hand

Joined: Apr 18, 2005
Posts: 87
I continue to get a syntax error but can't figure out why.
I have tried a number of things to get this to work.
It looks like an extra single quote is being inserted (based on
what I see in the printStackTrace). I have tried removing the
quotes to no avail as well.

The value in the first query (facility) comes from a combo box
which passes the value to this class. Just the code that I am
having problems with follows. I have verified that the values
I am trying to query on match the values in the DB. I am sure
that something simple is going wrong but can't see what it is...



Here is the print stack trace:

____________
java.sql.SQLException: [Microsoft][ODBC Microsoft Access Driver] Syntax error. in query expression 'Select FacilityID FROM MD_Facility
WHERE FacilityName = 'LifeCare of Greely''.
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 BackUpAdd.<init>(BackUpAdd.java:46)
at PatientAdd$AddBackRoomListener.actionPerformed(PatientAdd.java:167)
at javax.swing.AbstractButton.fireActionPerformed(Unknown Source)
at javax.swing.AbstractButton$Handler.actionPerformed(Unknown Source)
at javax.swing.DefaultButtonModel.fireActionPerformed(Unknown Source)
at javax.swing.DefaultButtonModel.setPressed(Unknown Source)
at javax.swing.plaf.basic.BasicButtonListener.mouseReleased(Unknown Sour
ce)
at java.awt.Component.processMouseEvent(Unknown Source)
at javax.swing.JComponent.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.pumpOneEventForHierarchy(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)
______________________

I know that there should be nothing hard about this. Is there something
being added to the value passed from the combo box that I need
to account for? Other than that, I can't figure out what is causing
this to not work.

Thanks for your help.
Gregg Bolinger
GenRocket Founder
Ranch Hand

Joined: Jul 11, 2001
Posts: 15286
    
    6

I can't see anything wrong in the code you posted. Kind of a mystery. Out of curiosity, if you print out the value of facility before the first try/catch block that you posted, what is the value? Do you see any extra characters with the value by itself?


GenRocket - A Test Data Generation Platform
Al Wells
Ranch Hand

Joined: Apr 18, 2005
Posts: 87
I did not print it out. However, I knew that the value was correct
because the print stack trace reports it and it is the value
that I selected in the combo box.

I am scratching my head.
Al Wells
Ranch Hand

Joined: Apr 18, 2005
Posts: 87
Just did a test print and the consol shows no other characters.
Al Wells
Ranch Hand

Joined: Apr 18, 2005
Posts: 87
I added some things to this class and now I am not getting the earlier
error. But, I am getting a new one. First I got a missing operator
error. So, I concanterated my string further (I don't know why...
just trying anything now). That fixed that error. Now I get an
"invalid cursor state" error but it does not tell me in which
block of code I am getting this. That could be the insert statement.
I have not yet done any inserting in Java so I could be doing that
wrong. Here is the updated code.



This creates a printStackTrace that tells me that I have an
"Invalid Cursor State". If I had any clue what that was,
I would try and fix it and see if this works.
Avi Abrami
Ranch Hand

Joined: Oct 11, 2000
Posts: 1121

Al,
There are many "wrong" things in the code you posted in your last reply. What you are trying to do is fairly simple and standard for JDBC. So I get the impression you have no idea what you are doing or how to accomplish your current task.

I'm assuming you would like to learn how to do this properly -- rather than have someone write the code for you. If my assumption is correct, then I suggest (if you haven't already done so) that you peruse the JDBC Trail in the Java Tutorial.

Good Luck,
Avi.
Gregg Bolinger
GenRocket Founder
Ranch Hand

Joined: Jul 11, 2001
Posts: 15286
    
    6

My guess is that the problem lies in your Database class where you actually perform all the JDBC operations. AS far as your program structure, it is a little sloppy joe, but I know how you code so I am not worried that will all be cleaned up.

Can you show us your Database class?
Al Wells
Ranch Hand

Joined: Apr 18, 2005
Posts: 87
I do agree that I am still learning. But, know that I first worry
about getting the thing to compile and run and then I modify,
fix and clean up the code for what I am trying to do. I will
read the turtorial though. Maybe that will at least help with
the invalid cursor state problem. Still, you are probably right
when you indicate that I am a little lost. That is why I posted!

The DB class is posted below. However, it works for all other instances
where I am doing SELECT queries. Where I think it is going wrong is
in the INSERT. The book that I am learning from does not give me
enough to work with. Nonetheless, I'll figure this out one way or
another today.

Thanks for the help so far!


[ May 31, 2005: Message edited by: Al Wells ]
Pavan Kumar
Ranch Hand

Joined: Jan 23, 2004
Posts: 78
I think, you should be using executeUpdate(sqlstring) method rather than executeQuery(sqlstring).

Hope I'm not spoiling your fun,

Cnu


formerly known as Cnu
Al Wells
Ranch Hand

Joined: Apr 18, 2005
Posts: 87
It is past the point of fun! Spoil away.

I tried executeUpdate(query) as well. I also seperated my trys
into two blocks. That helped me see the error better. What I am getting is
an invalid cursor error in the first result set. Since that is the case
no facilityID is being returned and I believe that the error in the
next block is due to that. The INSERT error is "Error in row".

The data table that I am accessing in the select query is MD_Facility.
The column that I am selecting is "FacilityID". The filter is
"FacilityName". FacilityName comes from a combo box object which
returns all of the facility names to that box and the user then
selects the faciity which the patient is added to (in this case a
patient is a 'back up room'). That passes to this object as String
facility. I am printing out that string in my console and it is
right. Now I am trying to figure out how to move the cursor through the
result set. I changed the way that the insert code works to be
commisserate with 2.0. Now I am going to try the same with the select
query and see what happens.
Al Wells
Ranch Hand

Joined: Apr 18, 2005
Posts: 87
Incidentally, the combo box referenced above gets its values from a
vector which adds all of the facilities in the DB to itself before
being added to the combo box. So, I know that the facility names are
correct because it is only facility names that exist in the table that
I am trying to get an ID on that can be returned.
Pavan Kumar
Ranch Hand

Joined: Jan 23, 2004
Posts: 78
"WHERE FacilityName =" + " '" + facility+"'";
dataRequest = Database.createStatement();facIDResults = dataRequest.executeQuery(facID);String finalFacID = new String();finalFacID = facIDResults.getString(1);

I may be totally wrong, but I think if you want to iterate through the Resultset you have to say facIDResults.next() in order to move the cursor to the next row in the result set.

from javadoc of Resultset
Moves the cursor down one row from its current position. A ResultSet cursor is initially positioned before the first row; the first call to the method next makes the first row the current row; the second call makes the second row the current row, and so on.

[Edit] removed code tags, because it's all appearing in one long line.
Best Regards,
Cnu
[ May 31, 2005: Message edited by: cnu sri ]
Gregg Bolinger
GenRocket Founder
Ranch Hand

Joined: Jul 11, 2001
Posts: 15286
    
    6

Yes, cnu is correct. The following should resolve the cursor issue:

String finalFacID = new String();
facIDResults.next(); //moves the cursor to the first record
finalFacID = facIDResults.getString(1);

If you don't call next() then the cursor actually is sitting before the first record. There are method calls to get back to that point as well as after the last record.

And just some advice, and again realizing you will clean up your code later, there is no need to nest try/catch blocks in the code like you are. One try/catch should be sufficient.
Gregg Bolinger
GenRocket Founder
Ranch Hand

Joined: Jul 11, 2001
Posts: 15286
    
    6

Oh, and one more tid bit, the execute(String query) method should be enough as well for inserts.
  • executeQuery() - When you need a ResultSet. Usually used for SELECT statements
  • executeUpdate() - When you issue an UPDATE statement
  • execute() - When you issue an INSERT statement.

  • Al Wells
    Ranch Hand

    Joined: Apr 18, 2005
    Posts: 87
    Wish I would have refreshed the page...

    I finally figured that out and just got it all working.

    The great thing about learning the hard way is that once it is learned,
    it is learned. I finally got the row to insert into the DB!

    I can not figure out why you have a virtual cursor in Java. It would make
    sense to me that a Select with a WHERE which returns only one record
    would mean that there is only one record and therefore that is the
    record that is desired. But, now at least I know.

    Thanks for your help.
     
    I agree. Here's the link: http://aspose.com/file-tools
     
    subject: Looks right to me
     
    Similar Threads
    JButton Problem
    NullPointerException
    Am I doing this correctly?
    Passing values to constructor
    SQLException on connect