這幾天被一個悲劇的存儲過程搞得很煩。昨天找了一天的資料都沒辦法搞好。由於項目開始是在DB2的開發中心,用Java寫的存儲過程。由於客戶升級的需要,數據庫遷移到更高版本的數據庫中去了,遷移後,這個存儲過程就用不了了。於是我打開DB2的開發中心,打開該存儲過程的項目,打算部署到新的DB2上,無論怎麼部署,始終都提示下面的錯誤:
- Call SQLJ.DB2_INSTALL_JAR (<<C:\Documents and Settings\jinghua\Application Data\IBM\DB2\DC\項目\bld1309245830936\SQL110528115458880.jar>>, 'GDZJ.SQL110528115458880', 0)
- [IBM][CLI Driver][DB2/AIX64] SQL4304N 具有特定名稱 "SQL110628152507130" 的 Java 存儲過程或用戶定義的函數 "sqlejReadJar"不能裝入 Java 類 "COM/ibm/db2/app/sqlejProcs",原因碼為 "5"。 SQLSTATE=42724
在google中無論搜索中文還是英文的資料,都找不到解決的方案,於是,把這腦殘的問題,從Java中轉換到db2的sql中去。下面先看看該存儲過程的代碼:
- /**
- * SQLJ 存儲過程 GDZJ.BaseCodeGen
- * @param enprName 企業名稱
- * @param enprAddr 企業地址
- * @param enprLeader 法人
- * @param enprPhone 聯系電話
- * @param baseCode 返回的基准碼
- */
- package com.Excellence.basecodesrv;
- import Java.io.FileWriter;
- import Java.io.PrintWriter;
- import Java.sql.*; // JDBC 類
- import Java.util.Map;
- import Java.util.HashMap;
- import Java.util.Date;
- import sqlj.runtime.*;
- import sqlj.runtime.ref.*;
- #sql context SPContext;
- #sql iterator BaseCodeGen_Cursor1 ( String curBaseCode );
- #sql iterator BaseCodeGen_Cursor2 ( String curBaseCode );
- public class BaseCodeGen
- {
- /**
- * @param enprName
- * @param enprAddr
- * @param enprLeader
- * @param enprPhone
- * @param baseCode
- * @exception SQLException
- * @exception Exception
- */
- public static void baseCodeGen ( String enprName,
- String enprAddr,
- String enprLeader,
- String enprPhone,
- String[] baseCode ) throws SQLException, Exception
- {
- BaseCodeGen_Cursor1 cursor1 = null;
- BaseCodeGen_Cursor2 cursor2 = null;
- SPContext ctx = null;
- try
- {
- if(enprName==null || enprName.trim().equals("")){
- // 設置返回參數
- baseCode[0] = null;
- return;
- }
- ctx = new SPContext( "jdbc:default:connection", false );
- #sql [ctx] cursor1 =
- {
- select max(enpr_basecode) as curBaseCode from T_PUB_BASECODE where enpr_name like concat(concat('%',:enprName),'%')
- };
- // 用來存取結果
- //ResultSet rs1 = cursor1.getResultSet();
- String curBaseCode = "";
- while (cursor1.next())
- {
- curBaseCode = cursor1.curBaseCode();
- }
- // 關閉打開資源
- if (cursor1 != null) cursor1.close();
- if(curBaseCode!=null && !curBaseCode.trim().equals("")){
- // 設置返回參數
- baseCode[0] = curBaseCode;
- return;
- }else{
- #sql [ctx] cursor2 =
- {
- select max(enpr_basecode) as curBaseCode from T_PUB_BASECODE
- };
- while (cursor2.next())
- {
- curBaseCode = cursor2.curBaseCode();
- }
- // 關閉打開資源
- if (cursor2 != null) cursor2.close();
- }
- //權重
- Map weight = new HashMap();
- weight.put(new Integer("3"), new Integer("9"));
- weight.put(new Integer("4"), new Integer("10"));
- weight.put(new Integer("5"), new Integer("5"));
- weight.put(new Integer("6"), new Integer("8"));
- weight.put(new Integer("7"), new Integer("4"));
- weight.put(new Integer("8"), new Integer("2"));
- String domain = "GZ"; // 主體碼
- String entityCode = ""; // 本體碼
- String checkCode = ""; // 校驗碼
- String rtnBaseCode = ""; // 返回的基准碼
- entityCode = curBaseCode==null?"333333":curBaseCode.substring(2,8);
- if (entityCode.equals("ZZZZZZ")) {
- throw new Exception("本體碼分配已經用完,無法再生成新的企業基准碼!");
- }
- // 本體碼 begin
- String newEntityCode = "";
- char c = '0';
- for(int i=5; i>=0; --i){
- c = entityCode.charAt(i);
- if(c == '9'){
- c = 'A';
- }else if(c == 'Z'){
- c = '0';
- }else{
- ++c;
- }
- newEntityCode = c+newEntityCode;
- if(c<'Z' && c!='0'){
- newEntityCode = entityCode.substring(0,i)+newEntityCode;
- break;
- }
- }
- entityCode = newEntityCode;
- // 本體碼 end
- // 校驗碼 begin
- int charWeight = 0; // 本體碼‘位’的權重
- int entityCharNum = 0; // 本體碼‘位’的數值
- int sum = 0; // 本體碼‘位’的權重 與 本體碼‘位’的數值 的乘積
- char sglChar = '0';
- for(int i=0; i<6; i++){
- sglChar = entityCode.charAt(i);
- if(sglChar > '9'){
- entityCharNum = sglChar - 55;
- }else{
- entityCharNum = sglChar - 48;
- }
- charWeight = ((Integer)weight.get(new Integer(i+3))).intValue();
- sum += entityCharNum*charWeight;
- }
- int cc = 11 - sum%11;
- switch(cc){
- case 10:
- checkCode = "X";
- break;
- case 11:
- checkCode = "0";
- break;
- default :
- checkCode = String.valueOf(cc);
- break;
- }
- // 校驗碼 end
- rtnBaseCode = domain+entityCode+checkCode;
- Date genTime = new Java.sql.Date(new Date().getTime());
- Date giveTime = new Java.sql.Date(new Date().getTime());
- #sql
- {
- insert into T_PUB_BASECODE(enpr_name,charger,address,phone,enpr_basecode,gen_bc_time,give_bc_time) values(:enprName,:enprLeader,:enprAddr,:enprPhone,:rtnBaseCode,:genTime,:giveTime)
- };
- // 設置返回參數
- baseCode[0] = rtnBaseCode;
- }
- catch (Exception e)
- {
- // 關閉打開資源
- try
- {
- if (cursor1 != null) cursor1.close();
- if (cursor2 != null) cursor2.close();
- if (ctx != null) ctx.close();
- } catch (SQLException e2) { /* 忽略 */ };
- /*
- try{
- PrintWriter pwx = new PrintWriter(new FileWriter("c:/db2ps.txt"),true);
- e.printStackTrace(pwx);
- }catch(Exception e3){ }
- */
- throw e;
- }
- }
- }
轉換後的DB2存儲過程為:
- -- Start of generated script for server226-DB2-GDZJ (gdzj)
- -- Jun-29-2011 at 10:51:42
- SET SCHEMA GDZJ ;
- SET CURRENT PATH = "SYSIBM","SYSFUN","SYSPROC","GDZJ";
- drop PROCEDURE GDZJ.BASECODEGEN_ZGW;
- CREATE PROCEDURE GDZJ.BASECODEGEN_ZGW
- (IN ENPRNAME VARCHAR(256),
- IN ENPRADDR VARCHAR(256),
- IN ENPRLEADER VARCHAR(64),
- IN ENPRPHONE VARCHAR(64),
- OUT BASECODE VARCHAR(32)
- )
- LANGUAGE SQL
- NOT DETERMINISTIC
- CALLED ON NULL INPUT
- MODIFIES SQL DATA
- INHERIT SPECIAL REGISTERS
- l1:begin
- declare curBaseCode varchar(16) default '';
- declare domain varchar(16) default 'GZ';--主體碼
- declare entityCode varchar(16) default '';-- 本體碼
- declare checkCode varchar(16) default '';-- 校驗碼
- declare rtnBaseCode varchar(16) default '';-- 返回的基准碼
- declare newEntityCode varchar(16) default '';
- declare V_COUNT int default 0;
- declare V_INDEX int default 0;
- declare cc int default 0;
- declare c char(1) default '0';
- declare charWeight int default 0; -- 本體碼‘位’的權重
- declare entityCharNum int default 0; -- 本體碼‘位’的數值
- declare sum int default 0; -- 本體碼‘位’的權重 與 本體碼‘位’的數值 的乘積
- declare sglChar char(1) default '0';
- if ENPRNAME is null or rtrim(ENPRNAME)='' then
- set BASECODE = null;
- return;
- end if;
- set curBaseCode=(select max(enpr_basecode) from T_PUB_BASECODE where enpr_name like '%'||ENPRNAME||'%' );
- if curBaseCode is not null and rtrim(curBaseCode)!='' then
- set BASECODE =curBaseCode;
- return;
- else
- set curBaseCode=(select max(enpr_basecode) from T_PUB_BASECODE);
- end if;
- if curBaseCode is null then
- set entityCode='333333';
- else
- set entityCode = SUBSTR(curBaseCode,3,6);
- end if;
- if entityCode='ZZZZZZ' then
- set BASECODE =null;
- return;
- end if;
- --本體碼 begin
- SET V_COUNT = LENGTH(entityCode);
- set V_INDEX=V_COUNT;
- WHILE V_INDEX >0 DO
- set c = SUBSTR(entityCode,V_INDEX,1);
- if c = '9' then
- set c = 'A';
- elseif c='Z' then
- set c='0';
- else
- set c=chr(DEC_TO_TEN(HEX(c),16)+1);
- end if;
- set newEntityCode=c||newEntityCode;
- if c<'Z' and c!='0' then
- set newEntityCode = SUBSTR(entityCode,1,V_INDEX-1)||newEntityCode;
- set V_INDEX=0;
- end if;
- set V_INDEX=V_INDEX-1;
- END WHILE;
- set entityCode = newEntityCode;
- --本體碼 end
- --校驗碼 begin
- set V_INDEX=0;
- WHILE V_INDEX<6 DO
- set sglChar = SUBSTR(entityCode,V_INDEX+1,1);
- if sglChar>'9' then
- set entityCharNum=DEC_TO_TEN(HEX(sglChar),16)-55;
- else
- set entityCharNum=DEC_TO_TEN(HEX(sglChar),16)-48;
- end if;
- --設置權重值
- case V_INDEX
- when 0 then set charWeight=9;
- when 1 then set charWeight=10;
- when 2 then set charWeight=5;
- when 3 then set charWeight=8;
- when 4 then set charWeight=4;
- when 5 then set charWeight=2;
- else
- set charWeight=0;
- end case;
- set sum=sum+entityCharNum*charWeight;
- set V_INDEX=V_INDEX+1;
- END WHILE;
- set cc=11 - mod(sum,11);
- case cc
- when 10 then set checkCode = 'X';
- when 11 then set checkCode = '0';
- else
- set checkCode = char(cc);
- end case;
- --校驗碼 end
- set rtnBaseCode = domain||entityCode||checkCode;
- insert into T_PUB_BASECODE(enpr_name,charger,address,phone,enpr_basecode,gen_bc_time,give_bc_time)
- values(enprName,enprLeader,enprAddr,enprPhone,rtnBaseCode,current date,current date);
- set BASECODE =rtnBaseCode;
- return;
- end l1;
- #SYNC 10;
- -- End of generated script for server226-DB2-GDZJ (gdzj)
其中,該存儲過程中使用的一個自定義函數:DEC_TO_TEN(16進制轉換成10進制),其代碼如下(該代碼非原創,在網絡上搜索到的):
- -- Start of generated script for server226-DB2-GDZJ (gdzj)
- -- Jun-29-2011 at 13:01:54
- SET SCHEMA GDZJ ;
- SET CURRENT PATH = "SYSIBM","SYSFUN","SYSPROC","GDZJ";
- CREATE FUNCTION GDZJ.DEC_TO_TEN
- (pStr VARCHAR(25),
- p_from_base INTEGER
- )
- RETURNS INTEGER
- LANGUAGE SQL
- NOT DETERMINISTIC
- READS SQL DATA
- STATIC DISPATCH
- CALLED ON NULL INPUT
- EXTERNAL ACTION
- INHERIT SPECIAL REGISTERS
- BEGIN ATOMIC
- DECLARE l_num int default 0;
- DECLARE l_hex varchar(16) default '0123456789ABCDEF';
- DECLARE k int default 1;
- DECLARE kcount int;
- DECLARE posNum int default 0;
- set kcount = length(pStr);
- WHILE k <=kcount DO
- set posNum = locate(UPPER(SUBSTR(pStr,k,1)),l_hex)-1;
- set l_num = l_num * p_from_base + posNum;
- set k=k+1;
- END WHILE;
- RETURN l_num;
- END;
- #SYNC 10;
- -- End of generated script for server226-DB2-GDZJ (gdzj)
其中,來總結一下經驗:
1:DB2本身沒有16進制轉換成10進制的函數,得自己搞一個。
2:DB2的函數用法,如SUBSTR,HEX,char,以及||等的用法,其實,很多Java中有的基本方法,DB2中也有的。
3:Java中的循環,DB2其實也有的。
4:能把該java的代碼轉換到DB2中的sql來,主要是由於該存儲過程只是一個字符串的處理,沒涉及到Java中復雜的東西,這才是成功的關鍵。遇到不會的,多上google搜索下,總有解決的方法的。但我部署這個存儲過程的錯誤,真的實在沒找到解決的方法.......比較郁悶。