Re: usage of unqualified table name within Java stored procedure

From the InfoCenter:

A Java stored procedure cannot set the properties (for example, system
naming) of the JDBC connection that is used to connect to the database. The
default JDBC connection properties are always used, except when prefetching
is disabled.

HTH,
Gary

-----Original Message-----
From: java400-l-bounces@xxxxxxxxxxxx [mailto:java400-l-
bounces@xxxxxxxxxxxx] On Behalf Of Yan Zhang
Sent: Wednesday, April 29, 2009 5:42 AM
To: java400-l@xxxxxxxxxxxx
Subject: usage of unqualified table name within Java stored procedure

Hi all,

This problem is about the usage of unqualified table names within Java
stored procedures. Before, I always use code "select * from

aSchema.aTable"

in my stored procedure. As different user sign-on may use different

schema,

I want to use system naming, and add a suitable schema name into the
library
list. So that, according to different user sign-on, the program can find
corresponding schema to use.

When I use SQL stored procedure, it works fine. For example, if I run

"call

idigproc/naming()" with a specific user name and password, it can go to

the

library list to find out which schema to use. Here is the code:

--------------------------------------------------------------------------

----------------

CREATE PROCEDURE IDIGPROC/NAMING( )
DYNAMIC RESULT SETS 1
LANGUAGE SQL
SPECIFIC IDIGPROC/NAMING
NOT DETERMINISTIC
MODIFIES SQL DATA
CALLED ON NULL INPUT
SET OPTION ALWBLK = *ALLREAD ,
ALWCPYDTA = *OPTIMIZE ,
COMMIT = *NONE ,
DECRESULT = (31, 31, 00) ,
DFTRDBCOL = *NONE ,
DYNDFTCOL = *NO ,
DYNUSRPRF = *USER ,
SRTSEQ = *HEX
BEGIN

DECLARE SQLSTMT VARCHAR ( 200 ) ;
DECLARE C2 CURSOR WITH RETURN FOR S1 ;
SET SQLSTMT =
'select DIST,DDBA from DIMDIST' ;
PREPARE S1 FROM SQLSTMT ;
OPEN C2 ;

END ;

---------------------------------------------------------------------------

-------

However, it does not work when I use a JAVA stored procedure. The error
message is "DIMDIST in A*** type * FILE not found", where A*** is the

user

name. Apparently it is using the SQL naming convention. Otherwise, the
error
message would be "DIMDIST in *LIBL type * FILE not found". My question

is,

how do I force the it to use system naming? I have attached my code

below.

I
appreciate any comments!

---------------------------------------------------------------------------

--------
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;

public class JavaSP {

public static void testStoredProc(ResultSet[] rs) throws

SQLException,

Exception{
Properties p = new Properties();
p.setProperty("naming", "system");
p.setProperty("libraries", "SRSGUSDIG");
Connection con =
DriverManager.getConnection("jdbc:default:connection",p);
Statement stmt = null;
String sql = "select DIST,DDBA from DIMDIST";
stmt = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
ResultSet.CONCUR_READ_ONLY);
rs[0] = stmt.executeQuery(sql);
}
}

---------------------------------------------------------------------------

------
CREATE PROCEDURE IDIGPROC/JAVANAMING ( )
DYNAMIC RESULT SETS 1
LANGUAGE JAVA
SPECIFIC IDIGPROC/JAVANAMING
NOT DETERMINISTIC
MODIFIES SQL DATA
CALLED ON NULL INPUT
EXTERNAL NAME 'JavaSP.testStoredProc'
PARAMETER STYLE JAVA ;

---------------------------------------------------------------------------

-------




thanks,
yan
--

--
This is the Java Programming on and around the iSeries / AS400 (JAVA400-L)
mailing list
To post a message email: JAVA400-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/java400-l
or email: JAVA400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/java400-l.