Trafodion 存储过程SPJ

本文通过一个例子简单介绍如何创建并使用Trafodion SPJ。主要步骤如下，

1 编写Java文件

import java.sql.*;
import java.math.*;

public class test
{
  public static void adjCredit(String name, String[] newCredit) throws SQLException {

     Connection conn = DriverManager.getConnection("jdbc:default:connection");

     PreparedStatement updateCredit = conn.prepareStatement("update trafodion.sales.customer " +
                                    "set credit = 'A2' " +
                                 "where custname = ?");

     PreparedStatement getCredit = conn.prepareStatement("select credit " +
                                "from trafodion.sales.customer " +
                                "where custname = ?");

     updateCredit.setString(1, name);

     updateCredit.executeUpdate();

     getCredit.setString(1, name);

     ResultSet rs = getCredit.executeQuery();
     rs.next();

     newCredit[0] = rs.getString(1);

     rs.close();
     conn.close();
  }
}

2 编译1中的java文件生成class文件

[trafodion@cent-1 ~]$ javac test.java
[trafodion@cent-1 ~]$ ls -la test.*
-rw-rw-r--. 1 trafodion trafodion 1113 Oct  9 09:21 test.class
-rw-rw-r--. 1 trafodion trafodion  823 Oct  9 09:21 test.java

3 打包2中的class文件

[trafodion@cent-1 ~]$ jar cvf test.jar test.class
added manifest
adding: test.class(in = 1113) (out= 642)(deflated 42%)
[trafodion@cent-1 ~]$ ls -la test.*
-rw-rw-r--. 1 trafodion trafodion 1113 Oct  9 09:21 test.class
-rw-rw-r--. 1 trafodion trafodion 1095 Oct  9 09:21 test.jar
-rw-rw-r--. 1 trafodion trafodion  823 Oct  9 09:21 test.java

4 发布3中的jar文件

• 使用pdsh命令在每个节点上创建相同的目录

pdsh $MY_NODES -x $HOSTNAME mkdir $HOME/trafodion-spjs

• 使用pdcp把前面生成的jar复制到每个节点的相同目录下

pdcp $MY_NODES test.jar  trafodion-spjs/

• 创建library指向上述jar路径

SQL>create library trafodion.sales.sales file '/home/trafodion/trafodion-spjs/test.jar';

--- SQL operation complete.

SQL>get libraries;

--- SQL operation complete.

SQL>get libraries in schema trafodion.sales;

SALES

--- SQL operation complete.

5 创建SPJ

SQL>create procedure trafodion.sales.adjCredit(in name varchar(10),out credit varchar(10))
+>external name 'test.adjCredit'
+>library trafodion.sales.sales
+>language java
+>parameter style java;

--- SQL operation complete.

SQL>get procedures;

Procedures in Schema TRAFODION.SALES
====================================

ADJCREDIT

6 执行SPJ

SQL>call adjcredit('A',?);
....

CREDIT
----------
A2

--- SQL operation complete.