aspose file tools*
The moose likes JDBC and the fly likes prepareStatement returns 0? Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "prepareStatement returns 0?" Watch "prepareStatement returns 0?" New topic
Author

prepareStatement returns 0?

Jeppe Sommer
Ranch Hand

Joined: Jan 07, 2004
Posts: 270
I do use "select MAX(id)..." in some synchronized methods... and I use prepareStatement...

The first prepareStatement return a correct ID. After calling the second synchronized method the "select MAX(id)..." returns 0. I have tried to execute the sql string in my SQL editor and it works fine and returns the right ID...

I think the problem is something about that prepareStatement do use bind variables.. How can I solve the problem?

Thanks
Bear Bibeault
Author and ninkuma
Marshal

Joined: Jan 10, 2002
Posts: 61206
    
  66

Jeppe, you have been asked three times to change your display name to comply with JavaRanch standards: here, here, and here.

Accounts with invalid display names are subject to closure. Please read the JavaRanch Naming Policy and change your display name to match it before your next post.

bear
Forum bartender
[ January 25, 2005: Message edited by: Bear Bibeault ]

[Asking smart questions] [Bear's FrontMan] [About Bear] [Books by Bear]
David Harkness
Ranch Hand

Joined: Aug 07, 2003
Posts: 1646
Originally posted by Jeppe:
I think the problem is something about that prepareStatement do use bind variables..
Me too, but without even a hint there's no way we can help you find the problem. Can you provide the code that prepares the statement, binds the parameters, and performs the query? Are you sharing the same PreparedStatement between threads or doing any other non-straight-forward tricks? Without code, I can only guess.
Jeppe Sommer
Ranch Hand

Joined: Jan 07, 2004
Posts: 270
I�m working within a servlet. First I do make a database connection and share that connection through the servlet lifetime... Inside the doPost method I first call a synchronized method which do the following task:

synchronized method_1 ()
1) SELECT MAX(id)
2) UPDATE date for the max(id) (I use type 2 historic)
3) INSERT request values
4) SELECT MAX(id) (from the insert)
5) return new MAX(id)

- That works fine. After that I call yet another synchronized method where I do the same task, just in another table:

synchronized method_2 ( int invoiceID )
1) SELECT MAX(id) returns 0 //FAILS!
2) ...

I close my resultSet after every Query. I have tried to close my preparedStatement too (stat.close()). I have also tried to close my connection after doing the job in each synchronized method, and open a new connection. That doesn�t work either...
The code looks like the following:



[ January 26, 2005: Message edited by: Jeppe Fjord ]
[ January 28, 2005: Message edited by: Jeppe Fjord ]
Jeppe Sommer
Ranch Hand

Joined: Jan 07, 2004
Posts: 270
****
Are you sure the inserts are being committed to the database? Also, out of curiousity, why are you using a synchronized method rather than a transaction?
****

Yes I�m sure that the inserts are being committed to the database. I have tried to execute the same query in my database editor and it returns the correct ID!

Why I�m using a synchronized method is that I want to make sure that a new thread isn�t making an insert before the first thread make the SELECT MAX(ID). I have tried to remove the synchronized statement for the methods but that doesn�t help to solve the problem!
David Harkness
Ranch Hand

Joined: Aug 07, 2003
Posts: 1646
I don't see anything wrong there. Can you post more information?
  • Database type
  • JDBC driver type -- you say "I use type 2 historic"; does that refer to the JDBC driver type?
  • Table definition
  • The first step of the working method that selects max(id) from the other table.

  • I'm just hoping that something in one of those will shed some light on this. You're using ints so it can't be a null value going into the where clause which could make it fail.

    Juicy bugs are fun. Though I guess not when you're on a deadline.
    Jeppe Sommer
    Ranch Hand

    Joined: Jan 07, 2004
    Posts: 270
    ***********************
    Database type?

    - I use a mySQL, myISAM type tables
    ***********************

    ***********************
    JDBC driver type?

    Class.forName("com.mysql.jdbc.Driver").newInstance();
    (mysql-connector-java-3.0.8-stable-bin)
    ***********************

    ***********************
    Table definition?

    # Host: localhost
    # Database: canteensite2
    # Table: 'oio_buyerparty'
    #
    CREATE TABLE `oio_buyerparty` (
    `oio_BuyerPartyID` int(11) NOT NULL auto_increment,
    `oio_BuyerPartySchemeID` varchar(50) NOT NULL default '',
    `oio_BuyerPartyIdentification` varchar(50) NOT NULL default '',
    `oio_BuyerPartyName` varchar(100) NOT NULL default '',
    `oio_BuyerPartyAddressTypeID` varchar(50) NOT NULL default '',
    `oio_BuyerPartyStreet` varchar(100) NOT NULL default '',
    `oio_BuyerPartyAdditionalStreet` varchar(100) NOT NULL default '',
    `oio_BuyerPartyHouseNo` varchar(20) NOT NULL default '',
    `oio_BuyerPartyInHouseMail` varchar(20) NOT NULL default '',
    `oio_BuyerPartyCityName` varchar(100) NOT NULL default '',
    `oio_BuyerPartyPostalZone` varchar(20) NOT NULL default '',
    `oio_BuyerPartyCountryCode` varchar(20) NOT NULL default '',
    `oio_EditableStart` datetime NOT NULL default '0000-00-00 00:00:00',
    `oio_EditableEnd` datetime NOT NULL default '0000-00-00 00:00:00',
    `oio_EditableIsNew` tinyint(1) NOT NULL default '0',
    `oio_invoiceID` int(11) NOT NULL default '0',
    PRIMARY KEY (`oio_BuyerPartyID`)
    ) TYPE=MyISAM;

    ***********************

    ***********************
    The first step of the working method that selects max(id) from the other table?

    ***********************

    ***********************
    SQL query that doesn�t work?

    SELECT MAX(oio_buyerparty.oio_BuyerPartyID) FROM oio_buyerparty WHERE oio_buyerparty.oio_EditableIsNew=1
    AND oio_buyerparty.oio_invoiceID=79


    returns: 69 in sql editor
    ***********************
    [ January 31, 2005: Message edited by: Jeppe Fjord ]
    Jeppe Sommer
    Ranch Hand

    Joined: Jan 07, 2004
    Posts: 270
    If I remove the first synchronized method the Select MAX(id) query in the second synchronized method is working fine... How can that be? Does anyone knows what the problem could be?
     
    I agree. Here's the link: http://aspose.com/file-tools
     
    subject: prepareStatement returns 0?