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

Question

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

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.

Did you try to get the text from the file instead of the raw bytes instead? — juergen d, Jan 13 '19 at 07:52
That is a simple google call: https://stackoverflow.com/questions/7729302/how-to-read-a-file-in-groovy-into-a-string+ — juergen d, Jan 13 '19 at 07:53
sorry that didnt work. No signature of method: org.springframework.web.multipart.commons.CommonsMultipartFile.getText() — kofhearts, Jan 13 '19 at 08:05
please note request.getFile('file') gives CommonsMultipartFile — kofhearts, Jan 13 '19 at 08:06
i have added the actual code with some modifications. Please look above. — kofhearts, Jan 13 '19 at 08:30

daggett · Accepted Answer · 2019-01-13T18:14:36.037

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)

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

1 Answers1