File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
The moose likes JDBC and Relational Databases and the fly likes Accessing a function stored in the Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC and Relational Databases
Bookmark "Accessing a function stored in the "H2" database" Watch "Accessing a function stored in the "H2" database" New topic

Accessing a function stored in the "H2" database

Mike Tabak
Ranch Hand

Joined: Mar 31, 2012
Posts: 68
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 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 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.

Jan Cumps

Joined: Dec 20, 2006
Posts: 2565

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?

OCUP UML fundamental and ITIL foundation
Mike Tabak
Ranch Hand

Joined: Mar 31, 2012
Posts: 68
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

Joined: Mar 31, 2012
Posts: 68
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.
I agree. Here's the link:
subject: Accessing a function stored in the "H2" database
It's not a secret anymore!