• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other all forums
this forum made possible by our volunteer staff, including ...
  • Campbell Ritchie
  • Liutauras Vilda
  • Jeanne Boyarsky
  • Devaka Cooray
  • Paul Clapham
  • Tim Cooke
  • Knute Snortum
  • Bear Bibeault
Saloon Keepers:
  • Ron McLeod
  • Tim Moores
  • Stephan van Hulst
  • Piet Souris
  • Ganesh Patekar
  • Frits Walraven
  • Carey Brown
  • Tim Holloway

Accessing a function stored in the "H2" database

Ranch Hand
Posts: 71
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
O.K., I've looked at this for the last 2 days and can't get anywhere. This may be more a SQL question, but here goes. I'm accessing a stored function in the H2 Database. I'm using Spring Java Configuration to set up the H2 database without an XML file. Here is the file I'm using to create the DataSource (database):

package com.apress.prospring3.ch8.javaconfig;

//Demonstrate database access using java configuration in an H2 database

import javax.sql.DataSource;

import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.ComponentScan;
import org.springframework.context.annotation.Configuration;
import org.springframework.jdbc.datasource.embedded.EmbeddedDatabase;
import org.springframework.jdbc.datasource.embedded.EmbeddedDatabaseBuilder;
import org.springframework.jdbc.datasource.embedded.EmbeddedDatabaseType;

public class AppConfig {

public DataSource dataSource() {

EmbeddedDatabaseBuilder builder = new EmbeddedDatabaseBuilder();
EmbeddedDatabase db = builder.setType(EmbeddedDatabaseType.H2).

return db;

} // dataSource()

} // class

The stored_fcn.sql file contains the function I'm trying to store and access, it contains the following:
CREATE FUNCTION getFirstNameById(in_id INT)
RETURN (SELECT first_name FROM contact WHERE id = in_id);
END //

The main program I use to test is :
// This class is similar to the JdbcContactSfDaoSample.java class
// but it uses com.apress.prospring3.ch8.javacofig.AppConfig class to
// obtain the dataSource (db) object and thus does away with the need for
// and XML file. It still tests the ContactSfDao xface which only has one
// method, getFirstNameById(). For this class we had to add a stored_fcn.sql
// file to store the function getFirstNameById() in the H2 database, along
// with adding the script to the AppConfig.java file above

import org.springframework.context.ApplicationContext;
import org.springframework.context.annotation.AnnotationConfigApplicationContext;

import com.apress.prospring3.ch8.dao.ContactSfDao;
// To config DataSource and get rid of need for xml file
import com.apress.prospring3.ch8.javaconfig.AppConfig;

public class JdbcContactSfJavaConfigDaoSample {

public static void main(String[] args) {

ApplicationContext ctx = new AnnotationConfigApplicationContext(AppConfig.class);

// Get the ContactSfDao object and test the getFirstNameById() method
ContactSfDao contactSfDao = ctx.getBean("contactSfDao", ContactSfDao.class);



When I run the test program I get the following error:

Error creating bean with name 'dataSource' defined in class com.apress.prospring3.ch8.javaconfig.AppConfig: Instantiation of bean failed; nested exception is org.springframework.beans.factory.BeanDefinitionStoreException: Factory method [public javax.sql.DataSource com.apress.prospring3.ch8.javaconfig.AppConfig.dataSource()] threw exception; nested exception is org.springframework.dao.DataAccessResourceFailureException: Failed to execute database script; nested exception is org.springframework.jdbc.datasource.init.ScriptStatementFailedException: Failed to execute SQL script statement at line 1 of resource class path resource [stored_fcn.sql]: DELIMITER // CREATE FUNCTION getFirstNameById(in_id INT) RETURNS VARCHAR(60) BEGIN RETURN (SELECT first_name FROM contact WHERE id = in_id)

This is telling me there is an error on the first line of the stored_fcn.sql file above, but I can't figure out what it is. Strange thing is, this is the same file I used to store this function in my MySQL database, with a use prospring_ch8; statement included to select the name of the MySQL database (prospring_ch8), but I don't think that statement is required here as none of the other .sql files assign a name in the schema created in the H2 database type.

Can anyone help?
Thank you very much.

Posts: 2653
Netbeans IDE C++ Linux
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
At first glance, I think you are right that you have to look for the error in the sql script stored_fcn.sql
Where did you get it from? Do you have an example of a working script?

Is there anything a bit further down the error log that gives the H2 specific error message?
Mike Tabak
Ranch Hand
Posts: 71
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I got the stored_fcn.sql file from the Pro Spring3 book, chpt 8. It is the same script I used to add the function to MySQL, of course with a "use prospring_ch; in the beginning to select the database in MySQL. It worked fine there.

There is an error - Caused by: org.h2.jdbc.JdbcSQLException: Syntax error in SQL statement "DELIMITER[*], that seems to indicate it is coming from the H2 database

Mike Tabak
Ranch Hand
Posts: 71
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I'm wondering if I really had this running. I may have run the wrong test driver, but I'm pretty sure I didn't. At this point I can't find an error with the add function sql code, so I guess I'll move on.
It is sorta covered in the JavaRanch Style Guide.
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
Boost this thread!