12 Replies Latest reply: Feb 3, 2009 10:47 AM by 796440 RSS

    Java String to Currency

    807588
      I'm trying to enter a string value '5.0000' into a DB value as a 'currency' value. I'm inputting the data into an access DB. But I don't think my DB work is the problem. What must I do to input my 'theBook.price' into the DB?

      These are the values of theBook:
      String Code = 'SDFS';
      String Title = "a Title";
      String Price = "5.00000"; //note its a string but I guess 'Microsoft Access' is expecting a currency value??
      void addRecord(Book theBook) throws SQLException
           {
                try{
                     String addQuery =
                             "INSERT INTO Books (BookCode, BookTitle, BookPrice) " +
                             "VALUES ('" + theBook.getCode() + "', " +
                                     "'" + theBook.getTitle() + "', " +
                                     "'" + theBook.getPrice() + "')";
                          Statement statement = con.createStatement();
                          statement.executeUpdate(addQuery);
                }
                catch(SQLException sqle)
               {
                         System.out.println("Could not add the Record: " + sqle);
               }
           }
      Error:
      Could not add the Record: java.sql.SQLException: [Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression ''The CICS Programmer's Desk Reference (2nd Ed.)', '5.0000')'.
        • 1. Re: Java String to Currency
          807588
          1. Find out your database column types, cubby. Don't guess.
          2. Use PreparedStatement: [http://java.sun.com/docs/books/tutorial/jdbc/basics/prepared.html]
          • 2. Re: Java String to Currency
            796440
            I'd guess that the price column is numeric and you're trying to stuff a string in.

            Irrespective of the cause of that particular error, you should be using PrepaedStatements. Something like this:
            String addQuery =
                                   "INSERT INTO Books (BookCode, BookTitle, BookPrice) " +
                                   "VALUES (?, ?, ?)";
            PreparedStatement ps = con.prepareStatement(addQuery);
            ps.setSetString(1, book.getCode());
            ps.setString(2, book.getTitle());
            ps.setDouble(3, book.getPrice()); // make the price a double, not a string, or change the column type to vachar
            ps.executeUpdate();
            • 3. Re: Java String to Currency
              807588
              I actually did go into the access database and its value type is "currency".
              • 4. Re: Java String to Currency
                807588
                ManRed wrote:
                I actually did go into the access database and its value type is "currency".
                Try using PreparedStatement's setDouble as suggested. I don't think Currency is a standard (SQL 92) data type.

                Be ready for people to tell you Access is a shite database, too ;-)
                • 5. Re: Java String to Currency
                  796447
                  That has nothing to do with your current error.
                  Could not add the Record: java.sql.SQLException: [Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression ''The CICS Programmer's Desk Reference (2nd Ed.)', '5.0000')'.
                  That error is simply due to the fact that your values have embedded quote marks in them. To get around them, you either have to:
                  a) Escape the possible embedded quotes in your statements, or
                  b) Use PreparedStatements
                  Choose option b).
                  • 6. Re: Java String to Currency
                    807588
                    Your right about that... wow. Access, makes up its own Database I guess. Its really just a double.
                    • 7. Re: Java String to Currency
                      807588
                      I think I'd try BigDecimal for currency. A currency field isn't really a double.

                      C# used decimal for SQL Server money type. So I think a BigDecimal would be the Java equivalent of that. What the ODBC bridge will do I have no idea.
                      • 8. Re: Java String to Currency
                        796440
                        cotton.m wrote:
                        I think I'd try BigDecimal for currency. A currency field isn't really a double.
                        True, but if Access' Currency type is just a double, then BigD won't buy you anything when storing it. Still, I suppose it makes sense to write the Java side of things correctly.
                        • 9. Re: Java String to Currency
                          807588
                          jverd wrote:
                          cotton.m wrote:
                          I think I'd try BigDecimal for currency. A currency field isn't really a double.
                          True, but if Access' Currency type is just a double,
                          It isn't. Even MS knows that you can't have a floating point number for money.
                          • 10. Re: Java String to Currency
                            796440
                            cotton.m wrote:
                            jverd wrote:
                            cotton.m wrote:
                            I think I'd try BigDecimal for currency. A currency field isn't really a double.
                            True, but if Access' Currency type is just a double,
                            It isn't. Even MS knows that you can't have a floating point number for money.
                            I took the OP at his word when he said it was. Shoulda known better.
                            • 11. Re: Java String to Currency
                              796440
                              So there are 3 key points to take away from this thread:

                              1. Use a data type that's compatible with the DB column.

                              2. Use PreparedStatement.

                              3. Use BigD for currency, not double.
                              • 12. Re: Java String to Currency
                                807588
                                [http://msdn.microsoft.com/en-us/library/bb177899.aspx]

                                See a FLOAT is a DOUBLE... and a DECIMAL is something else and a CURRENCY/MONEY field works like a DECIMAL.

                                Simple. :)