• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 235
  • Last Modified:

How to change Oracle SP to UDB SP in Visual Age for JAVA?

my original source is used for ORACLE :

    sql =  " DECLARE";
    sql += "     CURSOR line is";
    sql += "         " + SelectStatment + ";";
    sql += "     lrec line%ROWTYPE;";
    sql += " CNT     NUMBER(7):=1;";
    sql += " BEGIN";
    sql += "     OPEN line;";
    sql += "     WHILE CNT <= " + PersonCount + " LOOP";
    sql += "         FETCH line INTO lrec;";
    sql += "         EXIT WHEN line%NOTFOUND;";
    sql += "       " + InsertStatment + ;";
    sql += "         CNT := CNT + 1;";
    sql += "     END LOOP;";
    sql += "     CLOSE line;";
    sql += " END; ";
   
    db.executeUpdate(sql);

How can I change this SP to avaliable for UDB?

0
ChristBoy
Asked:
ChristBoy
  • 2
1 Solution
 
Jim CakalicSenior Developer/ArchitectCommented:
Wish I had time and expertise to write the full answer for you. Right now the best I can do is point you at this IBM Redbook:
    http://www.redbooks.ibm.com/pubs/pdfs/redbooks/sg245485.pdf

DB2 has historically supported stored procedures as native language methods that are executed by the database engine. The SQL Procedures language described by the Redbook is more traditionally like PL/SQL. As such, it may not be that difficult to syntactically convert the PL/SQL.

One hurdle you may have to overcome is that it appears you are using PL/SQL directly from your Java program. I don't know that DB2 would really support this in any form. I think you would have to write a stored procedure and install it on the database -- not assemble it dynamically in your application as you appear to be doing.

Barring that, it seems that it would be necessary to convert this to Java code which uses an outer SELECT statement and an inner INSERT statement to do the same thing.

Jim
0
 
girionisCommented:
No comment has been added lately, so it's time to clean up this TA.

I will leave a recommendation in the Cleanup topic area that this question is:

- points to jim_cakalic@idg

Please leave any comments here within the
next seven days.

PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER !

girionis
Cleanup Volunteer
0
 
Jim CakalicSenior Developer/ArchitectCommented:
OK. Thanks. :-)
Jim
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now