Search code examples
mysqlhibernategrailsgroovy

UncategorizedSQLException Incorrect String value error when saving a text directly from text file to database?

I import a file and read its content. I then save the content directly to database. The code sample is as follows.

def file = request.getFile('file')
if (file.empty) {
    flash.message = "File cannot be empty"
    return
}
String content = new String(file.getBytes())
Product product = new Product()
product.description = content
product.save(flush:true, failOnError:true)

The save fails throwing the following error. 

org.springframework.jdbc.UncategorizedSQLException: Hibernate operation: could not insert: [Product]; uncategorized SQLException for SQL [...]; SQL state [HY000]; error code [1366]; Incorrect string value: '\xEF\xBB\xBFNan' for column 'product_description' at row 1; nested exception is java.sql.SQLException: Incorrect string value: '\xEF\xBB\xBFNan' for column 'product_description' at row 1

I am guessing the problem is related to encoding. I wonder if there is something that needs to be done to content imported from file before saving the content to database.

I appreciate any help. Thanks!

Please look below the error screen

enter image description here

UPDATE:

Here is the actual code

def uploadRegistrations() {


    def file = request.getFile('file')

    if (file.empty) {
        flash.message = "File cannot be empty"
        return
    }

    String content = new String(file.getInputStream().getText('UTF-8'))

    def id = params['id']    

    def event = CompositeEvent.get(id.toLong())



    def reg = new RaceRegistration(race: event.races[0], compositeEvent: event, raceParticipant: new EmbeddedRaceParticipant(
            firstName: content.split(',')[0],
            lastName: "none",
            gender: Gender.MALE

    ),
            waiver: Waiver.getInstance(event),
            status: EntityStatus.ACTIVE

    )

    reg.save(flush: true, failOnError: true)

The important part is the content is used in firstname of RaceRegistration domain.

Solution

  • the key in

    java.sql.SQLException: Incorrect string value: '\xEF\xBB\xBFNan'

    the \xEF\xBB\xBF or EFBBBF is a Byte order mark (BOM) for UTF-8 encoding

    and seems your database prevent you to do wrong encoding conversion from stream to string

    actually first 2-5 bytes in text file could show the unicode encoding that has been used to save the file (UTF-8, UTF-16, UTF-32, ...).

    if you need to read text file with different encodings i suggest you to use BOMInputStream from apache commons io

    like this: 

    import org.apache.commons.io.input.BOMInputStream
...

BOMInputStream bis = new BOMInputStream(file.getInputStream())
//get charset from stream or default if not defined
String charset =  bis.getBOM()?.getCharsetName() ?: "UTF-8" 
String content = bis.getText(charset)