Hi guys.

I wasn't 100% sure whether this was java or Mysql, but I have a feeling it's mysql, so I posted here.

In the code below, there is an UPDATE and a SELECT.
Following an SQLException on running the SELECT, I found I had to quote the ? in the PreparedStatement.
That worked fine.

For consistency, It seemed logical to quote the string literal in the UPDATE line.  This fails to work, but quietly without exception.

Now I'm not sure what the 'Right Thing' to do is.
And, should the INSERT statement have quoted values?

Examples and documentation I have seen so far don't put quotes in like I did.

To Quote or Not To Quote.

Any ideas?


Code:

/** Get the next free id gfor this table, and increment it and store back in IDGenerator table.        */        public int getNext( String table ) throws SQLException {                LOG.debug( "getNext:");                int currentId=-1;                try {                        // Get the current ID                        currentId = getCurrent( table );                        // Increment the stored value of currentId                        PreparedStatement ps = connection.prepareStatement( "UPDATE "+tableName+" SET currentId=currentId+1 WHERE tableName=?");                        ps.setString( 1, table );                        ps.executeUpdate();                        // Check rc??? !!!!!                } catch( SQLException e ) {                        LOG.error( "getNext: failed."+e );                        throw( new SQLException( "IDGenerator.getNext( "+table+" ) failed."+e ));                }                LOG.debug( "getNext: Done. "+currentId);                return currentId;        }        /** Get the current id for this table without incrementing it.        */        public int getCurrent( String table ) throws SQLException {                LOG.debug( "getCurrent:");                int currentId=-1;                try {                        // Get the current ID                        PreparedStatement ps = connection.prepareStatement( "SELECT * from "+tableName+" WHERE tableName=\"?\"" );                        ps.setString( 1, table );                        ResultSet rs = ps.executeQuery();                        currentId = rs.getInt( "currentId" );                } catch( SQLException e ) {                        LOG.error( "getCurrent: Select failed."+e );                        throw( new SQLException( "IDGenerator.getCurrent( "+table+" ) failed."+e ));                }                LOG.debug( "getCurrent: Done. "+currentId);                return currentId;        }        /** store        */        public void store( String table, int currentId ) throws SQLException {                LOG.debug( "store:");                try {                        // Get the current ID                        PreparedStatement ps = connection.prepareStatement( "INSERT into "+tableName+" values (?,?)" );                        ps.setString(   1,table);                        ps.setInt(      2,currentId);                        ps.executeUpdate();                } catch( SQLException e ) {                        LOG.error( "store: insert failed."+e );                        throw( new SQLException( "store: "+table+" id "+currentId+" failed."+e ));                }                LOG.debug( "store: Done");        }

I would expect to have to put quotes around any literal text value or anything that's resolved to a literal text value - so I would expect you to need them as you found on the SELECT.  UPDATE has always struck me as a bit of an oddity where the more normal syntax rules don't apply so I'm not 100% suprised - only 80%  

By the way - am I reading your code right? Do you have a tablename column within your table, and you're dealing only with references to a table within itself?  Wow ... reminds me of a Donald Rumsfeld: "We know what we know and we don't know what we don't know. We do know there are some things we don't know ....."

He he. I tricked you by not giving the context for tableName.

I have a bunch of classes, one for each table, and to assist in trying to keep some of the names consistent, I have a private final tableName="idGenerator" at the top.

They are sort of DAO classes, but home-grown. This one is an ID generator class, so it breaks some of my normal conventions by a) not having 'findBy' methods, and b) by updating its table, but It doesn't make sense for this class, and think I can get away with it the once

Anyway, regarding the quotes. I have left the code as it is, 'because it works' at least for now with mysql. If it fails in the future when we change db, then I will cross that bridge at that time.

This is one of those projects that you just want to get over and done with asap any way that you can.

Thanks for your help.

B

Ok. Here's the _answer_.

It's a bit OT, as it turned our to be a Java issue, but here goes.

The PreparedStatement does quote correctly, and you don't need to put quotes around the ? like I did.

The error I got was a red-herring, and my mistake was not calling rs.next() before reading the contents of the record.
The code should read...

Code:

if (rs.next()) {      read the data from rs here  }

The ResultSet is returned with the cursor positioned before the first record, not at it as I thought.
So, slap on the wrist to me for not RTFM!

B



Thanks for the update and completing the loop for us!

---