Professional Web Applications Themes

MySQL Data Truncation error??? - MySQL

Hello, Hoping to get some help on something that just makes no sense to me... Here's hoping someone can help! I have table setup with a data type as such: max_dollars double(4,2) I have a piece of Java code that takes a form entry and submits it to the database. The insert command looks like this: db_insert = "insert into BIG_MONEY (max_dollars) values ("+request.getParameter("maxdollars"))"; stmt.executeUpdate(db_insert); The problem is that when I type in: 140 in the textbox, MySQL throws an error that looks like this: SQLException Data truncation: Out of range value adjusted for column 'max_dollars' at row 1 What ...

  1. #1

    Default MySQL Data Truncation error???

    Hello,

    Hoping to get some help on something that just makes no sense to me...
    Here's hoping someone can help!

    I have table setup with a data type as such:
    max_dollars double(4,2)

    I have a piece of Java code that takes a form entry and submits it to
    the database. The insert command looks like this:
    db_insert = "insert into BIG_MONEY (max_dollars) values
    ("+request.getParameter("maxdollars"))";
    stmt.executeUpdate(db_insert);

    The problem is that when I type in: 140 in the textbox, MySQL throws an
    error that looks like this:
    SQLException Data truncation: Out of range value adjusted for column
    'max_dollars' at row 1

    What gives??? I did some looking on the groups and found something that
    said to try adding the following to the URL:
    ?jdbcCompliantTruncation=false

    That eliminated the error, but now it just rounds off any number
    greater than 100 to 99.99... Any ideas??? This is driving me mad...

    Let me know if I left anything else out that might help... Oh, I'm
    using MySQL version 5.0.27 and MySQL Java connector 5.04

    Thank in advance...
    Nino Skilj

    nino9stars@yahoo.com Guest

  2. #2

    Default Re: MySQL Data Truncation error???

    com wrote: 

    Well, you defined the field to take a maximum of 99.99. What do you
    expect MySQL to do?

    If you want to put 140 in there, you need to leave room for three digits
    to the left of the decimal point.

    --
    ==================
    Remove the "x" from my email address
    Jerry Stuckle
    JDS Computer Training Corp.
    net
    ==================
    Jerry Guest

  3. #3

    Default MySQL Data Truncation error???

    > Well, you defined the field to take a maximum of 99.99. 
     

    Maybe I am misunderstanding, but isn't that what I did by defining my
    data type as: double(4,2)??? Doesn't that mean 4 numbers to the left of
    the decimal place, and 2 numbers to the right???

    Don't tell me it means 4 total digits with 2 decimal spots...

    Nino

    nino9stars@yahoo.com Guest

  4. #4

    Default Re: MySQL Data Truncation error???

    com wrote: 
    >

    >
    >
    > Maybe I am misunderstanding, but isn't that what I did by defining my
    > data type as: double(4,2)??? Doesn't that mean 4 numbers to the left of
    > the decimal place, and 2 numbers to the right???
    >
    > Don't tell me it means 4 total digits with 2 decimal spots...
    >
    > Nino
    >[/ref]

    Yes, when specifying the size, the first number is the total size
    (digits) and the second is the precision (number of digits to the right
    of the decimal point).

    See http://dev.mysql.com/doc/refman/5.1/en/numeric-types.html


    --
    ==================
    Remove the "x" from my email address
    Jerry Stuckle
    JDS Computer Training Corp.
    net
    ==================
    Jerry Guest

  5. #5

    Default Re: MySQL Data Truncation error???

    On 9 Jan 2007 10:37:07 -0800, com wrote: 

    >
    > Maybe I am misunderstanding, but isn't that what I did by defining my
    > data type as: double(4,2)??? Doesn't that mean 4 numbers to the left of
    > the decimal place, and 2 numbers to the right???
    >
    > Don't tell me it means 4 total digits with 2 decimal spots...[/ref]

    That's exactly what it means, though.

    --
    "Doesn't everybody?" is a question that never expects an answer of "No."
    Peter Guest

  6. #6

    Default Re: MySQL Data Truncation error???

    Peter H. Coffin wrote: 
    >> 
    >>
    >> Maybe I am misunderstanding, but isn't that what I did by defining my
    >> data type as: double(4,2)??? Doesn't that mean 4 numbers to the left
    >> of the decimal place, and 2 numbers to the right???
    >>
    >> Don't tell me it means 4 total digits with 2 decimal spots...[/ref]
    >
    > That's exactly what it means, though.[/ref]

    You went and told him and he specifically asked you not to! I mean he had
    gone to all the trouble of not reading it in the manual and then asked not
    to be told and you still went and did it. Some friend you are ;-)


    Paul Guest

  7. #7

    Default Re: MySQL Data Truncation error???

    On Tue, 9 Jan 2007 20:05:15 -0000, Paul Lautman wrote: 
    >>
    >> That's exactly what it means, though.[/ref]
    >
    > You went and told him and he specifically asked you not to! I mean he had
    > gone to all the trouble of not reading it in the manual and then asked not
    > to be told and you still went and did it. Some friend you are ;-)[/ref]

    If I can shatter some illusions *and* ruin someone's day with only one
    sentence, I consider that a job well done.

    --
    When all you have is a hammer, every problem looks like a messiah.
    Peter Guest

  8. #8

    Default Re: MySQL Data Truncation error???

    > Yes, when specifying the size, the first number is the total size 

    Thanks Jerry! I've read over the manual before, and have been using
    doubles in tables with no problems for years now. I never had problems
    before, so it didn't dawn on me that I had a false notion of how a
    double is defined. I really appreciate you pointing that out to me...
    thanks.

    Nino

    nino9stars@yahoo.com Guest

Similar Threads

  1. Replies: 10
    Last Post: April 27th, 05:04 PM
  2. Error in insert Data into MySQL 5.0.18
    By ky_loh in forum Coldfusion Database Access
    Replies: 1
    Last Post: March 10th, 08:04 PM
  3. Data Truncation error
    By d.acha in forum Coldfusion Database Access
    Replies: 12
    Last Post: October 5th, 01:57 AM
  4. CF 7 data source error with MySQL 4.1
    By Shane930 in forum Coldfusion Database Access
    Replies: 3
    Last Post: May 17th, 01:56 PM
  5. Data truncation in SELECT
    By myersonconsulting in forum Coldfusion Database Access
    Replies: 2
    Last Post: March 7th, 04:57 PM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139