Why h2 database have data conversion error converting

Question

I have problem with h2 database.

I try to into values to the database like this

INSERT INTO INGREDIENT(id,name,type) values ('FLTO','pszenna','WRAP');

But then i got this error

Data conversion error converting "'WRAP' (INGREDIENT: ""TYPE"" INTEGER)"; SQL statement:

also when i do smth like down my query is working

INSERT INTO INGREDIENT(id,name,type) values ('FLTO','pszenna','3');

but in schema.sql file the type field is varchar so why this doesnt working

create table if not exists Ingredient (
  id varchar(4) not null,
  name varchar(40) not null,
  type varchar(10) not null
);

H2 believes your type column is an integer, and it complains that it can't convert 'WRAP' to integer. The second example works beacause '3' can be converted to 3. — Erik
– Erik, Commented Mar 12, 2021 at 23:54
Okey i know this but i create a field type like varchar not int so this should working — Spectra
– Spectra, Commented Mar 12, 2021 at 23:56
Verify that create and insert are applied to the same database; append ;IFEXISTS=TRUE, as shown here to avoid creating spurious database files. — trashgod
– trashgod, Commented Mar 13, 2021 at 0:00
Try to drop and re-create the table. You may have an older schema still, and "create table if exists" ends up doing nothing. — Erik
– Erik, Commented Mar 13, 2021 at 0:17

Ljy · Accepted Answer · 2022-09-02 03:02:58Z

2

The Type type field of Ingredient entity class should be annotated with @Enumerated(EnumType.STRING)

    @Enumerated(EnumType.STRING)
    private final Type type;

answered Sep 2, 2022 at 3:02

Ljy

212 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Erik · Accepted Answer · 2021-03-13 00:20:26Z

I tried to reproduce your error with code (using Spring's JdbcTemplate) but it runs succesfully:

@Test
public void testH2() {
    JdbcDataSource ds = new JdbcDataSource();
    ds.setUrl("jdbc:h2:file:/tmp/test123");
    JdbcTemplate jdbc = new JdbcTemplate(ds);
    jdbc.update("drop table if exists ingredient");
    jdbc.update(
            "create table if not exists ingredient(" +
            " id varchar(4) not null," +
            " name varchar(40) not null," +
            " type varchar(10) not null)"); // if this is " type int)" I get the same error message
    jdbc.update("delete from ingredient");
    showTable(jdbc, "empty");
    jdbc.update("insert into ingredient(id,name,type) values ('FLTO','pszenna','3')");
    showTable(jdbc, "added FLTO");
    jdbc.update("insert into ingredient(id,name,type) values ('FLTW','pszennb','WRAP')");
    showTable(jdbc, "added FLTW");
}

void showTable(JdbcTemplate jdbc, String message) {
    System.out.println("=== " + message + " ===");
    jdbc.query("select id, name, type from ingredient order by id", rs -> {
        int n = 0;
        while (rs.next()) {
            System.out.println("id=" + rs.getString(1) + " name=" + rs.getString(2) + " type=" + rs.getString(3));
            n++;
        }
        System.out.println(n + " rows");
        return n;
    });
}

If I change the type of type to int I get the exact same error message as you.

Spectra · Accepted Answer · 2021-03-13 02:06:11Z

0

Okey, so i think the problem is smth with the database because when i want

DROP TABLE Ingredient;

I got error table "Ingrdient" not found.

This is my application.properties file

# Enabling H2 Console
spring.h2.console.enabled=true

# Custom H2 Console URL
spring.h2.console.path=/h2
spring.datasource.url=jdbc:h2:mem:testdb

Idk what i should to do

answered Mar 13, 2021 at 2:06

Spectra

713 silver badges9 bronze badges

2 Comments

Erik Over a year ago

H2 in-memory databases are a little tricky because they disappear when you close the last connection. If you change your url to something like spring.datasource.url=jdbc:h2:file:mydb you might get better results.

Erik Over a year ago

Another way to get around it is to add ;DB_CLOSE_DELAY=-1 to the jdbc url like this: spring.datasource.url=jdbc:h2:mem:testdb;DB_CLOSE_DELAY=-1

Collectives™ on Stack Overflow

Why h2 database have data conversion error converting

3 Answers 3

Comments

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related