Build Stored Procedure Step By Step

[Marc Melancon]

Is there a specific document on how to create Stored Procedure in C++ and
Java. That would show what to do step by step.

I.E for Java :create class, then collect classes in jar file, ...

Thanks

[Juan Lanus]

See
http://www7.software.ibm.com/vad.nsf/data/document1588?OpenDocument&p=1
for an article
--
Juan Lanus
TECNOSOL

[David Marvin]

DB2 UDB V6.1 ships a Stored Procedure Builder. See the Stored Procedure
Builder documentation provided in the Information menu item of Db2 UDB (on Nt
at least).

Using this tool stored procedures can be written and deployed in Java and the
SQL Procedure language. This tools is also used for building and deploying
stored procedures on DB2 OS/390 from your workstation.

[Serge Rielau]

Hi Marc,

Small clarification: The SQL Procedure language is not yet available on NT.

Cheers
Serge

[alok]

Hi Serge,

Have you tried the DB2 version 7.1 that IBM has released recently. It claims to
have the PSM support there but when I tried to create SQL stored procedures using
the tool Stored Procedure Builder, It returned the Build failed error with SQLSTATE
42904 for a very valid SQL Procedure. I also tried to generate the procedure code
by its own wizard but still it was unable to compile its own code, looks some bug
with it.
Any Idea?

Thanks
Alok

[alok]

[richard...@my-deja.com]

Quite simply, do the following.
Either create the stored procedure using the SPB, or directly in native
programming language (C++, Java):

The following is a sample stored procedure that I have built, along
with the calling code. The calling code "shouldn't" have the
registration of the stored procedure in it, its' used as an example of
how you register it...... this example basically calls a procedure to
insert a row into a table (using a trigger to generate an incremented
count), returning this counter value to the calling procedure.

/**
* SQLJ Stored Procedure DEVT01.GetMaxID
**/

import java.sql.*; // JDBC classes
import COM.ibm.db2.jdbc.app.*; // DB2 UDB classes
import COM.ibm.db2.app.*; // StoredProc and associated classes
import sqlj.runtime.*;
import sqlj.runtime.ref.*;

class GetMaxID extends StoredProc
{
public void insPublisher ( String name,
String gif_code,
String description,
String url,
String public_yn,
int publisher_id ) throws Exception
{

publisher_id = -1;

// get caller's connection to the database; inherit from
StoredProc
DefaultContext ctx = DefaultContext.getDefaultContext();
if (ctx == null)
{
Connection con = getConnection();
ctx = new DefaultContext(con);
DefaultContext.setDefaultContext(ctx);
con.setAutoCommit(false);
}
try
{
#sql
{
INSERT INTO DEVT01.PUBLISHER ( NAME, GIF_CODE,
DESCRIPTION, URL, PUBLIC )
VALUES
( :name, :gif_code, :description, :url, :public_yn )
};
}
catch (SQLException e)
{
return;
}
try
{
#sql
{
SELECT MAX(PUBLISHER_ID) INTO :publisher_id
FROM DEVT01.PUBLISHER
};

}
catch (SQLException e)
{
return;
}
// all good, assign value of newly inserted publisher_id
set (6, publisher_id);
}
}

The calling procedure....
import java.sql.*; // JDBC classes
import COM.ibm.db2.jdbc.app.*; // DB2 UDB JDBC classes
import COM.ibm.db2.app.*; // StoredProc and associated classes
import sqlj.runtime.*;
import sqlj.runtime.ref.*;

class Temp
{
static
{
try
{ Class.forName ("COM.ibm.db2.jdbc.app.DB2Driver");
}
catch (Exception e)
{
System.out.println ("\n Error loading DB2 Driver...");
e.printStackTrace ();
}
}

// (1) register the stored procedure
public static void registerStoredProc (Connection con,
String procname,
String classMethod,
String mode) throws Exception
{
try
{
// drop the stored procedure if it exists
Statement dropStmt = con.createStatement ();
dropStmt.executeUpdate ("DROP PROCEDURE " + procname);
dropStmt.close ();
}
catch (SQLException e)
{
// ignore this error
}

try
{
Statement stmt = con.createStatement ();

// construct a parameter list for the stored procedure and
// register it in the system catalogs
String parameterList =
"(in name char(64)," +
" in gif_code char(16)," +
" in description char(128)," +
" in url char(128)," +
" in public char(1)," +
" out publisher_id Integer)";

System.out.println ("\n Registering Java stored procedure " +
procname +
"\n as " + classMethod +
"\n in " + mode + " mode");

stmt.executeUpdate ("CREATE PROCEDURE " + procname +
parameterList +
" LANGUAGE JAVA " +
" PARAMETER STYLE DB2GENERAL " + mode +
" EXTERNAL NAME '" + classMethod + "'");
stmt.close ();
}
catch (SQLException e)
{
System.out.println ("\n Error received registering stored
procedure");
throw e;
}
}

// (2) call the requested stored procedure and display results
public static void callStoredProc (Connection con,
String procname,
String name,
String gif_code,
String description,
String url,
String public_yn) throws Exception
{
// prepare the CALL statement
CallableStatement stmt;
String sql = "Call " + procname + "(?,?,?,?,?,?) ";
stmt = con.prepareCall (sql);

// register the output parameters
stmt.registerOutParameter (6, Types.INTEGER);

// set all parameters (input and output)
int publisher_id = 0;
stmt.setString (1, name);
stmt.setString (2, gif_code);
stmt.setString (3, description);
stmt.setString (4, url);
stmt.setString (5, public_yn);
stmt.setInt (6, publisher_id);

// call the stored procedure
System.out.println ("\n Calling stored procedure: " + procname);
stmt.execute ();
System.out.println ("\n Returned from stored procedure: " +
procname);

// retrieve output parameters
publisher_id = stmt.getInt (6);

// display the information returned from the stored procedure
System.out.println ();
System.out.println (" Puhlisher_id : " + publisher_id);
stmt.close ();
}

// (3) main application: .connect to the database
// .register the stored procedure
// .call the stored procedure
public static void main (String argv[])
{
Connection con = null;
// URL is jdbc:db2:dbname
String driver_url = "jdbc:db2:DEVT01";

try
{
String callName = "GETMAXID";
String storedProcName = "GetMaxID!insPublisher";
String mode = "fenced";
String name = "Mitch Mitchell";
String gif_code = "Mitch GIF";
String description = "Mitchs' #n test";
String url = "http://www.dummy.url";
String public_yn = "Y";
if (argv.length == 0)
{
// connect with default id/password
con = DriverManager.getConnection(driver_url);
}
else if (argv.length == 2)
{
String userid = argv[0];
String passwd = argv[1];
// connect with user-provided username and password
con = DriverManager.getConnection(driver_url, userid,
passwd);
}
else
{
System.out.println("\nUsage: java temp [username password]
\n");
System.exit(0);
}

// register the stored procedure
registerStoredProc (con, callName, storedProcName, mode);

// call the stored procedure
callStoredProc (con, callName, name, gif_code, description,
url, public_yn);

con.close ();
}
catch (Exception e)
{
try { con.close(); } catch (Exception x) { }
e.printStackTrace ();
}
}
}

The steps to compile are as follows:
SERVER-SIDE (on pc)
1. Build Stored Procedure (.sqlj file)
2. Compile .sqlj file (sqlj procedurename.sqlj)
3. Compile .java file (javac procedurename.java)
4. Create DB2 entry (db2profc -
url=jdbc:db2://dev2.ibooks.com/DEVT01 -prepoptions="package using
procedurename" procedurename_SJProfile0)
5. FTP:
All resulting .class files
procedurename_SJProfile0.ser
to servers' /SQLLIB/FUNCTION subdirectory

CLIENT SIDE (on pc)
6. Compile .java file (javac callingjavafile.java)
7. READY TO EXECUTE (java callingjavafile)

Sent via Deja.com http://www.deja.com/
Before you buy.