By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,972 Members | 1,115 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,972 IT Pros & Developers. It's quick & easy.

Java procedure and the "alter sequence" statement

P: n/a
Hello,

I have a problem with a Java user-defined stored procedure
and the "alter sequence" statement. I use DB2 8.1.2 and had the
same problem with 8.1.0, but not with 7.*.
I get the following message:

SQL0206N "dipf.AENDERUNGSLISTE_SEQ" ist in dem verwendeten Kontext
ungültig.

SQLSTATE=42703
This is German meaning "X is invalid in the given context".

The Java code is as follows:

public static void restartSequence(String sequenceName, String
tableName, String idColumnName) throws SQLException {
int nextId = 1;
String sql;
ResultSet rs;

// Get connection to the database
Connection con = DriverManager.getConnection("jdbc:default:connecti on");
// restart the sequence
Statement stmt = con.createStatement();
sql = "SELECT COALESCE(MAX("+idColumnName+"),0) FROM " + tableName;
rs = stmt.executeQuery(sql);
if (rs.next()) {
nextId = rs.getInt(1) + 1;
}
rs.close();
stmt.close();

Statement stmt2 = con.createStatement();
sql = "ALTER SEQUENCE " + sequenceName + " RESTART WITH " + nextId;
stmt2.executeUpdate(sql);
stmt2.close();

con.commit();
con.close();
}

The procedure declaration is as follows:

CREATE PROCEDURE restartSequence (
IN sequenceName varchar(4000),
IN tableName varchar(4000),
IN idColumnName varchar(4000)
)
EXTERNAL NAME 'fis01.dipf_sp:com.anaxima.dipf.sp.Helper.restartS equence'
RESULT SETS 0
LANGUAGE JAVA PARAMETER STYLE JAVA
NOT DETERMINISTIC
FENCED
NO DBINFO
NULL CALL
MODIFIES SQL DATA ;

I call the procedure as follows:

call dipf.restartSequence("dipf.AENDERUNGSLISTE_SEQ",
"dipf.AENDERUNGSLISTE", "AENDERUNGSLISTE_ID");

Note that the schema is "dipf".

Any help is highly appreciated.
Nov 12 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a
Hans-Dieter,

Try DIPF instead of dipf. I'm a bit confused that the error message
didn't uppercase the name.
On a similar note: Is it correct to use double-quote in teh CALL
statement in Java? Shouldn't these be single quotes?
It could be that java binds teh string in including the doupel quotes.
So DB2 is looking for <currentschema>."dipf.AENDERUNGSLISTE_SEQ"
instead of "DIPF"."AENDERUNGSLISTE_SEQ".

Cheers
Serge

--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

Nov 12 '05 #2

P: n/a
Not sure about your double-quotes... should they be single quotes on the
call?

also,
FP2 changed some auth. requirements.
The privileges held by the authorization ID of the statement must include at
least one of the following:

a.. 2ALTER privilege on the sequence to be altered **** verify this one
****
b.. The ALTERIN privilege for the schema implicitly or explicitly
specified
c.. SYSADM or DBADM authority
PM
"Hans-Dieter Franz" <hd******@anaxima.com> a écrit dans le message de
news:dc**************************@posting.google.c om...
Hello,

I have a problem with a Java user-defined stored procedure
and the "alter sequence" statement. I use DB2 8.1.2 and had the
same problem with 8.1.0, but not with 7.*.
I get the following message:

SQL0206N "dipf.AENDERUNGSLISTE_SEQ" ist in dem verwendeten Kontext
ungültig.

SQLSTATE=42703
This is German meaning "X is invalid in the given context".

The Java code is as follows:

public static void restartSequence(String sequenceName, String
tableName, String idColumnName) throws SQLException {
int nextId = 1;
String sql;
ResultSet rs;

// Get connection to the database
Connection con = DriverManager.getConnection("jdbc:default:connecti on");
// restart the sequence
Statement stmt = con.createStatement();
sql = "SELECT COALESCE(MAX("+idColumnName+"),0) FROM " + tableName;
rs = stmt.executeQuery(sql);
if (rs.next()) {
nextId = rs.getInt(1) + 1;
}
rs.close();
stmt.close();

Statement stmt2 = con.createStatement();
sql = "ALTER SEQUENCE " + sequenceName + " RESTART WITH " + nextId;
stmt2.executeUpdate(sql);
stmt2.close();

con.commit();
con.close();
}

The procedure declaration is as follows:

CREATE PROCEDURE restartSequence (
IN sequenceName varchar(4000),
IN tableName varchar(4000),
IN idColumnName varchar(4000)
)
EXTERNAL NAME 'fis01.dipf_sp:com.anaxima.dipf.sp.Helper.restartS equence'
RESULT SETS 0
LANGUAGE JAVA PARAMETER STYLE JAVA
NOT DETERMINISTIC
FENCED
NO DBINFO
NULL CALL
MODIFIES SQL DATA ;

I call the procedure as follows:

call dipf.restartSequence("dipf.AENDERUNGSLISTE_SEQ",
"dipf.AENDERUNGSLISTE", "AENDERUNGSLISTE_ID");

Note that the schema is "dipf".

Any help is highly appreciated.

Nov 12 '05 #3

P: n/a
"PM \(pm3iinc-nospam\)" <PM(pm3iinc-nospam)@sympatico.ca> wrote in message news:<BX*******************@news20.bellglobal.com> ...
Not sure about your double-quotes... should they be single quotes on the
call?

also,
FP2 changed some auth. requirements.
The privileges held by the authorization ID of the statement must include at
least one of the following:

a.. 2ALTER privilege on the sequence to be altered **** verify this one
****
b.. The ALTERIN privilege for the schema implicitly or explicitly
specified
c.. SYSADM or DBADM authority
PM
"Hans-Dieter Franz" <hd******@anaxima.com> a écrit dans le message de
news:dc**************************@posting.google.c om...
Hello,

I have a problem with a Java user-defined stored procedure
and the "alter sequence" statement. I use DB2 8.1.2 and had the
same problem with 8.1.0, but not with 7.*.
I get the following message:

SQL0206N "dipf.AENDERUNGSLISTE_SEQ" ist in dem verwendeten Kontext
ungültig.

SQLSTATE=42703
This is German meaning "X is invalid in the given context".

The Java code is as follows:

public static void restartSequence(String sequenceName, String
tableName, String idColumnName) throws SQLException {
int nextId = 1;
String sql;
ResultSet rs;

// Get connection to the database
Connection con = DriverManager.getConnection("jdbc:default:connecti on");
// restart the sequence
Statement stmt = con.createStatement();
sql = "SELECT COALESCE(MAX("+idColumnName+"),0) FROM " + tableName;
rs = stmt.executeQuery(sql);
if (rs.next()) {
nextId = rs.getInt(1) + 1;
}
rs.close();
stmt.close();

Statement stmt2 = con.createStatement();
sql = "ALTER SEQUENCE " + sequenceName + " RESTART WITH " + nextId;
stmt2.executeUpdate(sql);
stmt2.close();

con.commit();
con.close();
}

The procedure declaration is as follows:

CREATE PROCEDURE restartSequence (
IN sequenceName varchar(4000),
IN tableName varchar(4000),
IN idColumnName varchar(4000)
)
EXTERNAL NAME 'fis01.dipf_sp:com.anaxima.dipf.sp.Helper.restartS equence'
RESULT SETS 0
LANGUAGE JAVA PARAMETER STYLE JAVA
NOT DETERMINISTIC
FENCED
NO DBINFO
NULL CALL
MODIFIES SQL DATA ;

I call the procedure as follows:

call dipf.restartSequence("dipf.AENDERUNGSLISTE_SEQ",
"dipf.AENDERUNGSLISTE", "AENDERUNGSLISTE_ID");

Note that the schema is "dipf".

Any help is highly appreciated.


Hello,

changing from double quotes to single quotes made the difference!
Coming from Sybase and ORACLE, I am not used to take a closer look at
the type of string quotation.

Many thanks for your hint.

Hans
Nov 12 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.