× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.



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
--


As an Amazon Associate we earn from qualifying purchases.

This thread ...

Follow-Ups:
Replies:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.