MaxDB + JDBC

[Former Member]

Hi,

we actually migrate from Oracle DB to MaxDB Rel. 7.5.00.32 on Red Hat Linux OS.

What to consider for a local WIN test (testing web applications locally, JDK and Tomcat)?

That's what I found so far:

1. Change environment settings (e.g. database URL, user, password...) in TOMCAT (server.xml) - if any!

2. Change connection:

Class.forName ("com.sap.dbtech.jdbc.DriverSapDB");

conn = java.sql.DriverManager.getConnection(conURL, userName, password);

I used the following JDBC driver: sapdbc-7_6_00_16_4753.jar ???

3. Put GRANTS on all relevant DB tables (grant all on table ... to ...)

Unfortunately, MaxDB will stack several sessions and hang somewhere and raises some messages 'Update not allowed'; even if grants are OK and it seems there is a problem with SQL sub-selects and distincts...

So, could you pls. advise how to setup a complete test-environment for local JSP/JDBC testing???

Thanks.

[alexander_schroeder]

Hello Rainer,

point 2 needs only to be applied only if you establish connections 'by hand', not if you use a data source configuration from tomcat.

Furthermore, you seemed to get everything right, so you possibly then stumbled over incompatibilities.

So what are the exact problems you run into?

Regards

Alexander Schröder

Message was edited by: Alexander Schroeder

[Former Member]

OK, Alexander.

Here's the message I can't understand:

com.sap.dbtech.jdbc.exceptions.DatabaseException: [-7008] (at 169): Updates of this table not allowed

---

...raised by stmt-execution:

sNoOfRecs = "SELECT count(*) from " + ((String)session.getAttribute("sessTableOwner")).trim() +

".mara m " +

" where m.mandt = '" + session.getAttribute("sessMandt") +

"' and m.matnr = (select matnr from " + ((String)session.getAttribute("sessTableOwner")).trim() + ".makt n where n.mandt = '" + session.getAttribute("sessMandt") + "' and n.spras = 'E' and n.maktg = '" + ((String)session.getAttribute("sessDevice")).toUpperCase() + "')";

...

try {

// Select the number of rows in the table

rs = stmt.executeQuery(sNoOfRecs);

...

---

It's a simple SQL statement and it worked without probs under Oracle.

[alexander_schroeder]

I assume the current user executing the statement is

not the owner of the table. Could you send the definitions of the tables mara and makt?

[Former Member]

What exactly do you need?

[alexander_schroeder]

Just the CREATE TABLE statements for these two tables,

and all CREATE INDEX statements for these two tables.

You can find my mail address in my 'Business Card', and

send it to me this way.

[Former Member]

We x-checked authorizations with SQL studio and DB SELECT worked fine for both SAP user and application user.

Furthermore, we investigated the connection module (JSP):

// MAXDB

// Registrieren des JDBC-Treibers

Class.forName ("com.sap.dbtech.jdbc.DriverSapDB");

// Establish connection

conn = java.sql.DriverManager.getConnection(conURL, userName, password);

// Aufruf der Methode java.sql.DriverManager.getConnection

connRead = java.sql.DriverManager.getConnection (conURL, userName, password);

//define scrollable and updatable statement

//stmt = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);

stmt = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);

...

We are not very serious about the CONCUR_UPDATABLE cursor. Is it supported by MaxDB???

We replaced CONCUR_UPDATABLE with CONCUR_READ_ONLY and the application went further - but did no update of course!!!

[alexander_schroeder]

Hello Rainer,

you cannot issue a

SELECT COUNT(*) FROM TABLE FOR UPDATE

as due to the aggregate functions the result cannot be

updated. Maybe Oracle is more lazy here ...

[Former Member]

Mmh, maybe I didn't get your point...

It's not an update but an select statement?!

[alexander_schroeder]

Your said that your query

sNoOfRecs = "SELECT count(*) from " + ((String)session.getAttribute("sessTableOwner")).trim() +
".mara m " +
" where m.mandt = '" + session.getAttribute("sessMandt") +
"' and m.matnr = (select matnr from " + ((String)session.getAttribute("sessTableOwner")).trim() + ".makt n where n.mandt = '" + session.getAttribute("sessMandt") + "' and n.spras = 'E' and n.maktg = '" + ((String)session.getAttribute("sessDevice")).toUpperCase() + "')";
...
try {
// Select the number of rows in the table
rs = stmt.executeQuery(sNoOfRecs);

was executed with a statement that had the CONCUR_UPDATABLE property set. This will lead to

a SELECT ... FOR UPDATE statement executed, which is not supported if you have aggregate functions (COUNT, AVG, ...) in your query.