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:[email protected]] Sent: Thursday, March 03, 2011 9:42 AM To: [email protected] 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.
