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):
EmbeddedDatabaseBuilder builder = new EmbeddedDatabaseBuilder();
EmbeddedDatabase db = builder.setType(EmbeddedDatabaseType.H2).
} // 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);
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
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.
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
Joined: Mar 31, 2012
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.