Database error "Out of range value" (numerics) JDBC Driver

mugicagonzalez_
mugicagonzalez_ New Altair Community Member
edited November 5 in Community Q&A
Hello all,

I've been struggling with this for a while. When I try to update a MySQL database with DOUBLE field types from my dataset in Rapidminer (in attachment) with REAL or Numeric fields (and missings), I get the error 

Database error occured: Out of range value for column 'productie_e' at row 1
 I know something is wrong with the range of the numeric values, but the error doesn't report enough to deeply understand the issue for a data warehouse beginner. 

Any idea what's wrong and how to solve it?

Thanks
Pello




Answers

  • sgenzer
    sgenzer
    Altair Employee
    hi @mugicagonzalez_ - sorry no one has chimed in here. My go-to database expert is @BalazsBarany. Maybe he has time for a quick look? :wink:

    Scott
  • rfuentealba
    rfuentealba New Altair Community Member
    Hi @mugicagonzalez_

    I apologize for being a tad late in replying. Your issue is quite simple, actually.

    Your file isn't a typical comma-separated value in a sense that it uses the ; symbol to separate. If you don't set it properly, your column productie_e gets the value:

    ;;;;;0.5;33.0;1497.0;0.0;0.0;0.0;0.0;0.6;5.0;5.0;0.0;"[CX-1FE7DE][poll][2018-12-02__08-15-00.5990].csv_";"FTP";"CX-1FE7DE";"Antwerpen Upkot";12/2/18 7:15 AM

    That's obviously not a number, what you want is null, as there are no values in the first columns.

    Solutions:
    • Use the Import Configuration Wizard from your database.
    • Set the column separators setting from the Read CSV operator to ;
    Also, make sure that your column is declared as DECIMAL DEFAULT NULL as it will return another error if you get it declared as NOT NULL.

    All the best,

    Rodrigo.