0

I'm trying to run HSQL with following two scripts and then test it, but each time I get this error which I cannot handle.

Failed to execute database script; nested exception is org.springframework.jdbc.datasource.init.ScriptStatementFailedException: Failed to execute SQL script statement at line 4 of resource class path resource [data.sql]: insert into spittle (spitter_id, spittleText, postedTime) values (2, 'Trying out Spring''s new expression language.', '2010-06-11')

Update - additional exception that has been thrown:

integrity constraint violation: foreign key no parent; SYS_FK_10108 table: SPITTLE

These are my scripts:

schema.sql

drop table if exists spittle;
drop table if exists spitter;

create table spitter (
  id identity,
  username varchar(25) not null,
  password varchar(25) not null,
  fullname varchar(100) not null,
  email varchar(50) not null,
  update_by_email boolean not null
);

create table spittle (
  id integer identity primary key,
  spitter_id integer not null,
  spittleText varchar(2000) not null,
  postedTime date not null,
  foreign key (spitter_id) references spitter(id)
);

data.sql

insert into spitter (username, password, fullname, email, update_by_email) values ('habuma', 'password', 'Craig Walls', '[email protected]', false);
insert into spitter (username, password, fullname, email, update_by_email) values ('artnames', 'password', 'Art Names', '[email protected]', false);

insert into spittle (spitter_id, spittleText, postedTime) values (1, 'Have you read Spring in Action 3? I hear it is awesome!', '2010-06-09');
insert into spittle (spitter_id, spittleText, postedTime) values (2, 'Trying out Spring''s new expression language.', '2010-06-11');
insert into spittle (spitter_id, spittleText, postedTime) values (1, 'Who''s going to SpringOne/2GX this year?', '2010-06-19');

appContext.xml

<?xml version="1.0" encoding="UTF-8"?>

<beans xmlns="http://www.springframework.org/schema/beans"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:jdbc="http://www.springframework.org/schema/jdbc"
    xsi:schemaLocation="http://www.springframework.org/schema/jdbc http://www.springframework.org/schema/jdbc/spring-jdbc-3.0.xsd
        http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-3.0.xsd">

    <jdbc:embedded-database id="dataSource" type="H2">
        <jdbc:script location="classpath:schema.sql" />
        <jdbc:script location="classpath:data.sql" />
    </jdbc:embedded-database>

</beans>

UnitTest

package com.habuma.spitter.persistence;

import org.springframework.jdbc.datasource.embedded.EmbeddedDatabase;
import org.springframework.jdbc.datasource.embedded.EmbeddedDatabaseBuilder;

public class DataAccessUnitTestTemplate {
    private static EmbeddedDatabase db;

    @Before
    public void setUp() {
        // creates a HSQL in-memory db populated from default scripts classpath:schema.sql and classpath:test-data.sql
        // obviously, this is the 'in-code' method, but the xml should work for Spring managed tests.
        db = new EmbeddedDatabaseBuilder().addDefaultScripts().build();     
    }

    @Test
    public void testDataAccess() {
        JdbcSpitterDao jdbc = new JdbcSpitterDao(db);

        System.out.println(jdbc.getSpitterById(1L));

    }

    @After
    public void tearDown() {
        db.shutdown();
    }


}
5
  • @SotiriosDelimanolis Prepared statement looks like this SQL_SELECT_SPITTER = "select id, username, fullname from spitter where id = ?". But I tested app and it fails on setUp() method(commented other too). Commented Aug 30, 2013 at 22:00
  • @SotiriosDelimanolis Can you be more specific ? I'm not sure what I should remove. I updated questions with second exception thas was threw. Commented Aug 30, 2013 at 22:05
  • @SotiriosDelimanolis It didn't help. But I've just noticed that at the beggining of output there is log4j:WARN No appenders could be found for logger (org.springframework.jdbc.datasource.embedded.EmbeddedDatabaseFactory). log4j:WARN Please initialize the log4j system properly. Can it be source of my problem ? Commented Aug 30, 2013 at 22:12
  • No that is irrelevant. Commented Aug 30, 2013 at 22:13
  • @SotiriosDelimanolis It seems that the problem lies in data.sql in the line that inserts to spittle table. I tested same script without inserting to spittle and it works. The error is java.sql.SQLIntegrityConstraintViolationException: integrity constraint violation: foreign key no parent; SYS_FK_10108 table: SPITTLE. I'm not sure but is there really something wrong with the script ? Commented Aug 30, 2013 at 23:22

1 Answer 1

2

Your script starts the ID column value with 0. You can specify the start value.

create table spitter (
  id int generated by default as identity (start with 1) primary key,
  username varchar(25) not null,
  password varchar(25) not null,
  fullname varchar(100) not null,
  email varchar(50) not null,
  update_by_email boolean not null
);

Alternatively you can insert the ids explicitly:

insert into spitter (id, username, password, fullname, email, update_by_email) values (1, 'habuma', 'password', 'Craig Walls', '[email protected]', false);
Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.