Comment créer une procédure (ou fonction) stockée) sous Pointbase?

Version imprimable

01/03/2010, 16h09
mumu27

Comment créer une procédure (ou fonction) stockée) sous Pointbase?
Bonjour,

j'essaye de créer une procédure ou plutôt une fonction stockée sous PointBase. Voici les commandes que j'utilise dans la console PointBase:
Code:

1 2 3 4 5 6 CREATE FUNCTION carre(IN valeur INT) RETURNS BIGINT LANGUAGE Java NO SQL EXTERNAL NAME \"SampleExternalMethods::carre\" PARAMETER STYLE SQL;
Voici la classe SampleExternalMethods.java que j'utilise:
Code:

1 2 3 4 5 6 7 8 9 class SampleExternalMethods { public long carre(int valeur) { long toto = valeur * valeur; return(toto); } }
J'ai placé le .class dans le répertoire C:\Sun\AppServer\pointbase\lib, car j'ai vu que dans certains exemples, on y plaçait des .jar contenant des classes java.

Cependant, il me met une erreur:

Citation:

SQL> Error Message: Expected to find "an identifier" instead found "\" at position 87.
SQL> Error SQLState: ZB005
SQL> Error Code: 10006
SQL>

Si j'enlève les "\", il me met cette erreur:

Citation:

SQL>
SQL> Error Message: Cannot create Store Procedure CARRE. java.security.AccessControlException: access denied (com.pointbase.sp.spPermission SampleExternalMethods).
SQL> Error SQLState: ZG037
SQL> Error Code: 25037
SQL>

J'avoue ne pas avoir forcément bien saisi, comment faire le lien entre ma classe java et l'EXTERNAL NAME, en effet d'après ce que j'ai lu il faudrait un constructeur à cette classe qui établisse une java.sql.Connection, mais bon comme il n'y a pas d'exemples, difficile de se faire une idée.
Merci d'avance pour vos réponses.

Mumu27!

Bonjour,

après avoir quelque peu cherché dans la doc Pointbase, j'ai réussi à écrire un code plus proche de la réalité et plus parlant je pense, mais il doit manquer apparemment quelques petites choses.

Voici le fichier SampleExternalMethods.java:
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 import java.sql.*; //import com.pointbase.jdbc.jdbcInOutDoubleWrapper; public class SampleExternalMethods { // A connection object to allow database callback static Connection conn = null; static Statement l_stmt; static Statement m_stmt; static CallableStatement m_callStmt = null; static ResultSet l_rs = null; public static void main(String[] args) { try { String url = "jdbc:pointbase:server://localhost/pointbaseDB"; String username = "PBPUBLIC"; String password = "PBPUBLIC"; conn = DriverManager.getConnection(url, username, password); doCreateProcedure(); doInvokeProcedure(); } catch (SQLException e) { e.printStackTrace(); } finally { if (m_stmt != null) { try { m_stmt.close(); } catch (Exception e) { e.printStackTrace(); } } if (m_callStmt != null) { try { m_callStmt.close(); } catch (Exception e) { e.printStackTrace(); } } if (conn != null) { try { conn.close(); } catch (Exception e) { e.printStackTrace(); } } } } public static void getCountry(String Iso_Code) { try { // Query the database for the country iso code l_stmt = conn.createStatement(); l_rs = l_stmt.executeQuery( "SELECT * FROM countries" + " WHERE country_iso_code ='" + Iso_Code + "'"); //Affichage du résultat de la requête l_rs.next(); System.out.print(l_rs.getString(1) + " - "); System.out.print(l_rs.getString(2) + " - "); System.out.println(l_rs.getString(3)); // Close the result set l_rs.close(); } catch (SQLException e) { e.printStackTrace(); } finally { if (l_rs != null) { try { l_rs.close(); } catch (Exception e) { e.printStackTrace(); } } if (l_stmt != null) { try { l_stmt.close(); } catch (Exception e) { e.printStackTrace(); } } } } public static void doCreateProcedure() throws SQLException { // SQL statement to create a stored procedure String SQL_CREATE_PROC = "CREATE PROCEDURE getCountry(IN P1 VARCHAR(30))" + " LANGUAGE JAVA" + " SPECIFIC getCountry" + " NO SQL" + " EXTERNAL NAME \"SampleExternalMethods::getCountry\"" + " PARAMETER STYLE SQL"; // Create a SQL statement m_stmt = conn.createStatement(); // Execute the SQL m_stmt.executeUpdate(SQL_CREATE_PROC); // Close the statement //m_stmt.close(); } public static void doInvokeProcedure() throws SQLException { // Create SQL to invoke stored procedures String SQL_USE_PROC = "{ call getCountry(?) }"; // Create a callable statement with three binding parameters m_callStmt = conn.prepareCall(SQL_USE_PROC); m_callStmt.setString(1, "CA"); m_callStmt.executeQuery(); // Close the callable statement //m_callStmt.close(); } }
Voici comment j'ai procédé dans un premier temps:
(Mon programme SampleExternalMethods.class est sous D:\J2EE\Ch07Code\Ch07_06\Ch07)
1) Démarrage de Pointbase
2) Configuration du classpath
set classpath=C:\Sun\AppServer\pointbase\lib\pbclient.jar
set classpath=%classpath%;D:\J2EE\Ch07Code\Ch07_06

Ensuite, je lance le client:
D:\J2EE\Ch07Code\Ch07_06>java -Djdbc.drivers=com.pointbase.jdbc.jdbcUniversalDriver Ch07.SampleExternalMethods

Voici le message d'erreur:

Citation:

Exception in thread "main" java.lang.NoClassDefFoundError: Ch07.SampleExternalMethods (wrong name: SampleExternalMethods)
at java.lang.ClassLoader.defineClass0(Native Method)
at java.lang.ClassLoader.DefineClass(ClassLoader.java:539)
...

J'ai vu ceci dans une Doc de Pointbase:

Citation:

To invoke the dateConvert external Java method from a stored function, you must use the
CREATE FUNCTION statement. The dateConvert external Java method is called from the
class, SampleExternalMethods.
In order for the database to access this external Java method, the class SampleExternalMethods
must be included in the database CLASSPATH. For PointBase Embedded - Server Option, it
must be in the Server CLASSPATH, but not in the Client CLASSPATH.
If PointBase Server is run with the Java Security Manager, in the java policy file grant
’com.pointbase.sp.spPermission’ to the class that implements the external Java method.
An "spPermission" consists of a class name with no action. The class name is a name of a class
that could be used in creating a Stored Procedure in PointBase. The naming convention follows
the hierarchical property naming convention and that is supported by
"java.security.BasicPermission". An asterisk may appear by itself, or if immediately preceded
by ".", may appear at the end of the name, to signify a wildcard match. The name cannot
contain any white spaces.

J'ai donc ajouté ceci à la configuration du classpath:

set classpath =%classpath%;D:\J2EE\Ch07Code\Ch07_06;D:\J2EE\Ch07Code\Ch07_06\Ch07\SampleExternalMethods.class

Mais même problème que précedemment.

Aussi dans la doc, il y'a ceci:

Citation:

External Java Methods and Functions
In PointBase, functions may be implemented using external Java methods. These user-defined
methods manipulate SQL data when the function is called by the database. This java method can
be static or non-static. If it is non-static, a connection object will be established during function
invocation, so a non-static member variable of type java.sql.connection and a constructor having
a parameter of type java.sql.connection needs to be implemented. If it is static, the method is
called directly and no connection object will be established during function invocation.

Et au lieu de déclarer la variable conn de type Connection statique, comme moi dans l'exemple de la Doc, il procède ainsi:
Code:

1 2 3 4 5 6 7 8 9 10 11 12 */ import java.sql.*; import com.pointbase.jdbc.jdbcInOutDoubleWrapper; public class SampleExternalMethods { // A connection object to allow database callback private Connection m_conn; // Constructor accepts a java.sql.Connection object to allow database callback public SampleExternalMethods(Connection p_conn) { m_conn = p_conn; }
Je pense pas que cela change quelque chose, mais bon je le signale à tout hasard. Voilà où j'en suis, si quelqu'un a une idée du pourquoi de l'erreur je suis preneur étant un peu perdu dans la doc.

Merci d'avance.

Mumu27!

09/03/2010, 13h53
mumu27

Hello,

Cette fois-ci j'ai démarré du database CLASSPATH, c'est à dire de:
C:\Sun\AppServer\pointbase\lib

Ensuite j'exécute ces commandes

set classpath=C:\Sun\AppServer\pointbase\lib\pbclient.jar
set classpath=%classpath%;C:\Sun\AppServer\pointbase\lib

C:\Sun\AppServer\pointbase\lib>java -Djdbc.drivers=com.pointbase.jdbc.jdbcUniver
salDriver SampleExternalMethods

J'ai ce message d'erreur:

C:\Sun\AppServer\pointbase\lib>java -Djdbc.drivers=com.pointbase.jdbc.jdbcUniver
salDriver SampleExternalMethods
java.sql.SQLException: Cannot create Store Procedure GETCOUNTRY. java.security.A
ccessControlException: access denied (com.pointbase.sp.spPermission SampleExtern
alMethods).
at com.pointbase.net.netJDBCPrimitives.handleResponse(DashoA13*..:370)
at com.pointbase.net.netJDBCPrimitives.handlePrimitiveResponse(DashoA13*
..:473)
at com.pointbase.net.netJDBCStatement.executeUpdate(DashoA13*..:196)
at SampleExternalMethods.doCreateProcedure(SampleExternalMethods.java:97
)
at SampleExternalMethods.main(SampleExternalMethods.java:21)

Peut-être avez-vous une idée?

Merci par avance.

mumu27!
11/03/2010, 11h49
mumu27

Bonjour,

apparemment mon problème n'est pas un problème JDBC, voici ce que j'ai dans une des notes que j'ai lu:

Citation:

If PointBase Server is run with the Java Security Manager, in the java policy file grant
’com.pointbase.sp.spPermission’ to the class that implements the external Java method.
An "spPermission" consists of a class name with no action. The class name is a name of a class
that could be used in creating a Stored Procedure in PointBase. The naming convention follows
the hierarchical property naming convention and that is supported by
"java.security.BasicPermission". An asterisk may appear by itself, or if immediately preceded
by ".", may appear at the end of the name, to signify a wildcard match. The name cannot
contain any white spaces.

J'ai trouvé le fichier de police Java, voici ce que j'ai dedans:

Citation:

// Standard extensions get all permissions by default

grant codeBase "file:${java.home}/lib/ext/*" {
permission java.security.AllPermission;
};

// default permissions granted to all domains

grant {
// Allows any thread to stop itself using the java.lang.Thread.stop()
// method that takes no argument.
// Note that this permission is granted by default only to remain
// backwards compatible.
// It is strongly recommended that you either remove this permission
// from this policy file or further restrict it to code sources
// that you specify, because Thread.stop() is potentially unsafe.
// See "http://java.sun.com/notes" for more information.
permission java.lang.RuntimePermission "stopThread";

// allows anyone to listen on un-privileged ports
permission java.net.SocketPermission "localhost:1024-", "listen";

// "standard" properies that can be read by anyone

permission java.util.PropertyPermission "java.version", "read";
permission java.util.PropertyPermission "java.vendor", "read";
permission java.util.PropertyPermission "java.vendor.url", "read";
permission java.util.PropertyPermission "java.class.version", "read";
permission java.util.PropertyPermission "os.name", "read";
permission java.util.PropertyPermission "os.version", "read";
permission java.util.PropertyPermission "os.arch", "read";
permission java.util.PropertyPermission "file.separator", "read";
permission java.util.PropertyPermission "path.separator", "read";
permission java.util.PropertyPermission "line.separator", "read";

permission java.util.PropertyPermission "java.specification.version", "read";
permission java.util.PropertyPermission "java.specification.vendor", "read";
permission java.util.PropertyPermission "java.specification.name", "read";

permission java.util.PropertyPermission "java.vm.specification.version", "read";
permission java.util.PropertyPermission "java.vm.specification.vendor", "read";
permission java.util.PropertyPermission "java.vm.specification.name", "read";
permission java.util.PropertyPermission "java.vm.version", "read";
permission java.util.PropertyPermission "java.vm.vendor", "read";
permission java.util.PropertyPermission "java.vm.name", "read";
};

Cependant je ne vois pas bien comment je vais pouvoir accorder une ’com.pointbase.sp.spPermission’ à ma classe SampleExternalMethods qui implémente "l'external Java method", ici "getCountry". En fait je vois bien qu'il faut utiliser la commande grant dans le "java policy file", par contre je ne vois pas trop la syntaxe. Si quelqu'un peut m'aider, je suis preneur.

Merci d'avance!

mumu27!

J'ai essayé ceci, mais cela ne marche toujours pas:

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
// Standard extensions get all permissions by default

grant codeBase "file:${java.home}/lib/ext/*" {
	permission java.security.AllPermission;
};

// default permissions granted to all domains

grant { 
	// Allows any thread to stop itself using the java.lang.Thread.stop()
	// method that takes no argument.
	// Note that this permission is granted by default only to remain
	// backwards compatible.
	// It is strongly recommended that you either remove this permission
	// from this policy file or further restrict it to code sources
	// that you specify, because Thread.stop() is potentially unsafe.
	// See "http://java.sun.com/notes" for more information.
	permission java.lang.RuntimePermission "stopThread";

	// allows anyone to listen on un-privileged ports
	permission java.net.SocketPermission "localhost:1024-", "listen";

	// "standard" properies that can be read by anyone

	permission java.util.PropertyPermission "java.version", "read";
	permission java.util.PropertyPermission "java.vendor", "read";
	permission java.util.PropertyPermission "java.vendor.url", "read";
	permission java.util.PropertyPermission "java.class.version", "read";
	permission java.util.PropertyPermission "os.name", "read";
	permission java.util.PropertyPermission "os.version", "read";
	permission java.util.PropertyPermission "os.arch", "read";
	permission java.util.PropertyPermission "file.separator", "read";
	permission java.util.PropertyPermission "path.separator", "read";
	permission java.util.PropertyPermission "line.separator", "read";

	permission java.util.PropertyPermission "java.specification.version", "read";
	permission java.util.PropertyPermission "java.specification.vendor", "read";
	permission java.util.PropertyPermission "java.specification.name", "read";

	permission java.util.PropertyPermission "java.vm.specification.version", "read";
	permission java.util.PropertyPermission "java.vm.specification.vendor", "read";
	permission java.util.PropertyPermission "java.vm.specification.name", "read";
	permission java.util.PropertyPermission "java.vm.version", "read";
	permission java.util.PropertyPermission "java.vm.vendor", "read";
	permission java.util.PropertyPermission "java.vm.name", "read";
	
	//permission com.pointbase.sp.spPermission "<<ALL FILES>>", "read,write,delete,execute";
	permission com.pointbase.sp.spPermission "getCountry";

};

Si quelqu'un a une idée, je suis preneur, car je ne connais pas vraiment la syntaxe.

mumu27!

Bonjour,

j'ai essayé de modifier le fichier java.policy du jdk. Voici les deux programmes que j'utilise pour ma procédure stockée.

SampleExternalMethods.java

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
package Ch07;
 
import java.sql.*;
 
public class SampleExternalMethods
{
  public static void getCountry(String Iso_Code)
  {
	// A connection object to allow database callback
	Connection l_conn = null;
	Statement l_stmt = null;
	ResultSet l_rs = null;
 
    try
    {
	  String url = "jdbc:pointbase:server://localhost/pointbaseDB";
	  String username = "PBPUBLIC";
	  String password = "PBPUBLIC";
	  l_conn = DriverManager.getConnection(url, username, password);
      // Query the database for the country iso code
      l_stmt = l_conn.createStatement();
      l_rs = l_stmt.executeQuery( "SELECT * FROM PBPUBLIC.COUNTRIES"
      + " WHERE country_iso_code ='" + Iso_Code + "'");
      //Affichage du résultat de la requête
      l_rs.next();
      System.out.print(l_rs.getString(1) + " - ");
      System.out.print(l_rs.getString(2) + " - ");
      System.out.println(l_rs.getString(3));
      // Close the result set
	  l_rs.close();
    } catch (SQLException e) {
	  e.printStackTrace();
    } finally {
	  if (l_rs != null) {
	  	try {
	  	  l_rs.close();
	  	} catch (Exception e) {
	  	  e.printStackTrace();
	  	}
  	  }
	  if (l_stmt != null) {
		try {
		  l_stmt.close();
		} catch (Exception e) {
		  e.printStackTrace();
		}
  	  }
  	  if (l_conn != null) {
	    try {
	      l_conn.close();
	    } catch (Exception e) {
	      e.printStackTrace();
	    }
	  }
  	}
  }
}

CreateMethods.java

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
package Ch07;
 
import java.sql.*;
 
public class CreateMethods
{
  // A connection object to allow database callback
  static Connection m_conn = null;
  static Statement m_stmt;
  static CallableStatement m_callStmt = null;
 
  public static void doCreateProcedure() throws SQLException {
	// SQL statement to create a stored procedure
	String SQL_CREATE_PROC = "CREATE PROCEDURE PBPUBLIC.getCountry(IN P1 VARCHAR(30))"
	+ " LANGUAGE JAVA"
	+ " SPECIFIC getCountry"
	+ " NO SQL"
	+ " EXTERNAL NAME \"Ch07.SampleExternalMethods::getCountry\""
	+ " PARAMETER STYLE SQL";
	// Create a SQL statement
	m_stmt = m_conn.createStatement();
    // Execute the SQL
    m_stmt.executeUpdate(SQL_CREATE_PROC);
	// Close the statement
    //m_stmt.close();
  }
 
  public static void doInvokeProcedure() throws SQLException {
      // Create SQL to invoke stored procedures
      String SQL_USE_PROC = "{ call getCountry(?) }";
      // Create a callable statement with three binding parameters
      m_callStmt = m_conn.prepareCall(SQL_USE_PROC);
      m_callStmt.setString(2, "CA");
      m_callStmt.executeQuery();
      // Close the callable statement
      //m_callStmt.close();
  }
 
  public static void main(String[] args)
  {
	try
	{
	  String url = "jdbc:pointbase:server://localhost/pointbaseDB";
	  String username = "PBPUBLIC";
	  String password = "PBPUBLIC";
	  m_conn = DriverManager.getConnection(url, username, password);
	  doCreateProcedure();
	  doInvokeProcedure();
	} catch (SQLException e) {
	  e.printStackTrace();
	} finally {
	  if (m_stmt != null) {
	  	try {
	  	  m_stmt.close();
	  	} catch (Exception e) {
	  	  e.printStackTrace();
	  	}
  	  }
  	  if (m_callStmt != null) {
	    try {
	      m_callStmt.close();
	    } catch (Exception e) {
	   	  e.printStackTrace();
	  	}
  	  }
	  if (m_conn != null) {
	    try {
	      m_conn.close();
	    } catch (Exception e) {
	      e.printStackTrace();
	    }
	  }
	}
  }
 
}

Voici le fichier java.policy

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
 
// Standard extensions get all permissions by default
 
grant codeBase "file:${{java.ext.dirs}}/*" {
	permission java.security.AllPermission;
};
 
// default permissions granted to all domains
 
grant { 
	// Allows any thread to stop itself using the java.lang.Thread.stop()
	// method that takes no argument.
	// Note that this permission is granted by default only to remain
	// backwards compatible.
	// It is strongly recommended that you either remove this permission
	// from this policy file or further restrict it to code sources
	// that you specify, because Thread.stop() is potentially unsafe.
	// See "http://java.sun.com/notes" for more information.
	permission java.lang.RuntimePermission "stopThread";
 
	// allows anyone to listen on un-privileged ports
	permission java.net.SocketPermission "localhost:1024-", "listen";
 
	// "standard" properies that can be read by anyone
 
	permission java.util.PropertyPermission "java.version", "read";
	permission java.util.PropertyPermission "java.vendor", "read";
	permission java.util.PropertyPermission "java.vendor.url", "read";
	permission java.util.PropertyPermission "java.class.version", "read";
	permission java.util.PropertyPermission "os.name", "read";
	permission java.util.PropertyPermission "os.version", "read";
	permission java.util.PropertyPermission "os.arch", "read";
	permission java.util.PropertyPermission "file.separator", "read";
	permission java.util.PropertyPermission "path.separator", "read";
	permission java.util.PropertyPermission "line.separator", "read";
 
	permission java.util.PropertyPermission "java.specification.version", "read";
	permission java.util.PropertyPermission "java.specification.vendor", "read";
	permission java.util.PropertyPermission "java.specification.name", "read";
 
	permission java.util.PropertyPermission "java.vm.specification.version", "read";
	permission java.util.PropertyPermission "java.vm.specification.vendor", "read";
	permission java.util.PropertyPermission "java.vm.specification.name", "read";
	permission java.util.PropertyPermission "java.vm.version", "read";
	permission java.util.PropertyPermission "java.vm.vendor", "read";
	permission java.util.PropertyPermission "java.vm.name", "read";
 
	permission com.pointbase.sp.spPermission "Ch07.SampleExternalMethods", "read, write, execute, delete";
};

Cependant j'ai toujours ce message d'erreur:

Citation:

D:\J2EE\Ch07Code\Ch07_06>java -Djdbc.drivers=com.pointbase.jdbc.jdbcUniversalDriver Ch07.CreateMethods
java.sql.SQLException: Cannot create Store Procedure GETCOUNTRY. java.security.AccessControlException: access denied (com.pointbase.sp.spPermission Ch07.SampleExternalMethods).
at com.pointbase.net.netJDBCPrimitives.handleResponse(DashoA13*..:370)
at com.pointbase.net.netJDBCPrimitives.handlePrimitiveResponse(DashoA13*..:473)
at com.pointbase.net.netJDBCStatement.executeUpdate(DashoA13*..:196)
at Ch07.CreateMethods.doCreateProcedure(CreateMethods.java:23)
at Ch07.CreateMethods.main(CreateMethods.java:47)

Voici le chemin d'accès du fichier java.policy:

Citation:

C:\Program Files (x86)\Java\jdk1.5.0_22\jre\lib\security

Si vous avez une idée, je suis preneur.

Merci d'avance!

Mumu27