I believe it is because of your no sql in the CREATE FUNCTION call. This
should probably be reads sql data but check the reference manual section on
the 'create' statement.
Brett
-Original Message-
From: trolll [mailto:m.roki...@ibe.edu.pl]
Sent: Thursday, March 03, 2011 9:42 AM
To: derby-user@db.apache.org
Subject: Executing sql from function
Hi everyone
I have a problem with executing sql statements from inside of a sql function
written in java. This is what I did
1. This is how I execute the statemnt in java
...
con=DriverManager.getConnection(jdbc:default:connection);
q=con.prepareStatement(SELECT * FROM users);
q.executeQuery();
...
2. I built a Jar that contains this class
3. I install the jar in a derby database
CALL SQLJ.install_jar ('file:///home/michal/foo.jar', 'foo', 1);
CALL SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY ('derby.database.classpath',
'App.foo');
4. I create a function that uses my java method from the jar file
CRATE FUNCTION bar(varchar(50)) RETURNS varchar(50) LANGUAGE java EXTERNAL
NAME 'foo.Tools.bar' PARAMETER STYLE java no sql;
5. I use the method in a sql query
SELECT bar(user_id) FROM users
While query execution I get the following exception:
java.sql.SQLException: The external routine is not allowed to execute SQL
statements.
I use Java 6 and derby 10.7.1.1
Does anybody know what do I do wrong
--
View this message in context:
http://old.nabble.com/Executing-sql-from-function-tp31059263p31059263.html
Sent from the Apache Derby Users mailing list archive at Nabble.com.
