KickJava   Java API By Example, From Geeks To Geeks.

Java > Open Source Codes > org > opensubsystems > core > persist > db > driver > sapdb > SapDBDatabaseTestSchema


1 /*
2  * Copyright (c) 2003 - 2007 OpenSubsystems s.r.o. Slovak Republic. All rights reserved.
3  *
4  * Project: OpenSubsystems
5  *
6  * $Id: SapDBDatabaseTestSchema.java,v 1.15 2007/01/07 06:14:43 bastafidli Exp $
7  *
8  * This program is free software; you can redistribute it and/or modify
9  * it under the terms of the GNU General Public License as published by
10  * the Free Software Foundation; version 2 of the License.
11  *
12  * This program is distributed in the hope that it will be useful,
13  * but WITHOUT ANY WARRANTY; without even the implied warranty of
14  * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
15  * GNU General Public License for more details.
16  *
17  * You should have received a copy of the GNU General Public License
18  * along with this program; if not, write to the Free Software
19  * Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA
20  */

21
22 package org.opensubsystems.core.persist.db.driver.sapdb;
23
24 import java.sql.CallableStatement JavaDoc;
25 import java.sql.Connection JavaDoc;
26 import java.sql.SQLException JavaDoc;
27 import java.sql.Statement JavaDoc;
28 import java.sql.Types JavaDoc;
29 import java.util.logging.Level JavaDoc;
30 import java.util.logging.Logger JavaDoc;
31
32 import org.opensubsystems.core.util.DatabaseUtils;
33 import org.opensubsystems.core.util.Log;
34 import org.opensubsystems.core.error.OSSException;
35 import org.opensubsystems.core.persist.db.driver.DatabaseTestSchema;
36
37 /**
38  * This class encapsulates details about creation and upgrade of database schema
39  * required to test database driver functionality for tables which are SAP DB
40  * database specific.
41  *
42  * @version $Id: SapDBDatabaseTestSchema.java,v 1.15 2007/01/07 06:14:43 bastafidli Exp $
43  * @author Miro Halas
44  * @code.reviewer Miro Halas
45  * @code.reviewed 1.11 2005/02/26 21:13:42 bastafidli
46  */

47 public class SapDBDatabaseTestSchema extends DatabaseTestSchema
48 {
49    /*
50       These tables are database specific
51       
52       CREATE SEQUENCE GENERATEDKEY_TEST_SEQ INCREMENT BY 1 START WITH 1 NOCYCLE
53       
54       CREATE TABLE GENERATEDKEY_TEST
55       (
56          TEST_KEY INTEGER NOT NULL PRIMARY KEY,
57          TEST_VALUE VARCHAR(50) NOT NULL
58       )
59       
60       CREATE DBPROCEDURE INSERT_GENERATEDKEY_TEST (
61          IN IN_VALUE VARCHAR(50),
62          OUT OUT_KEY INTEGER)
63       AS
64       TRY
65          SET OUT_KEY = GENERATEDKEY_TEST_SEQ.NEXTVAL;
66          INSERT INTO GENERATEDKEY_TEST(TEST_KEY, TEST_VALUE) VALUES (:OUT_KEY, :IN_VALUE);
67       CATCH
68          IF $RC <> 0 THEN STOP ($RC, $ERRMSG);
69
70       CREATE DBPROCEDURE INSERT_ROW_COUNT_TEST (
71          IN IN_VALUE VARCHAR(50),
72          OUT OUT_ROWS INTEGER)
73       AS
74       TRY
75          -- value OUT_ROWS is initialized to 0. There is not supported by DB
76          -- getting number of affected rows. Stored procedure will return always value 0
77          -- and it has to demonstrate that it's not supported.
78          SET OUT_ROWS = 0;
79          INSERT INTO GENERATEDKEY_TEST(TEST_KEY, TEST_VALUE)
80             VALUES (GENERATEDKEY_TEST_SEQ.NEXTVAL, :IN_VALUE);
81       CATCH
82          IF $RC <> 0 THEN STOP ($RC, $ERRMSG);
83
84       CREATE DBPROCEDURE UPDATE_TRANSACTION_TEST_VALUE (
85          IN IN_OLD_TEST_VALUE VARCHAR(50),
86          IN IN_NEW_TEST_VALUE VARCHAR(50)
87       )
88       AS
89       TRY
90          UPDATE BASTA.TRANSACTION_TEST SET
91                 TEST_VALUE = :IN_NEW_TEST_VALUE
92          WHERE TEST_VALUE = :IN_OLD_TEST_VALUE;
93       CATCH
94          IF $RC <> 0 THEN STOP ($RC, $ERRMSG);
95     
96    */

97    
98    // Cached values ////////////////////////////////////////////////////////////
99

100    /**
101     * Logger for this class
102     */

103    private static Logger JavaDoc s_logger = Log.getInstance(SapDBDatabaseTestSchema.class);
104
105    // Constructors /////////////////////////////////////////////////////////////
106

107    /**
108     * Default constructor.
109     *
110     * @throws OSSException - error occured.
111     */

112    public SapDBDatabaseTestSchema(
113    ) throws OSSException
114    {
115       super();
116    }
117
118    // Lifecycle events /////////////////////////////////////////////////////////
119

120    /**
121     * {@inheritDoc}
122     */

123    public void create(
124       Connection JavaDoc cntDBConnection,
125       String JavaDoc strUserName
126    ) throws SQLException JavaDoc
127    {
128       // First create any generic tables
129
super.create(cntDBConnection, strUserName);
130
131       // Now try to create any database specific tables
132
Statement JavaDoc stmQuery = null;
133       try
134       {
135          stmQuery = cntDBConnection.createStatement();
136
137          if (stmQuery.execute("CREATE SEQUENCE GENERATEDKEY_TEST_SEQ " +
138                               "INCREMENT BY 1 START WITH 1 NOCYCLE"))
139          {
140             // Close any results
141
stmQuery.getMoreResults(Statement.CLOSE_ALL_RESULTS);
142          }
143          s_logger.log(Level.FINEST, "Sequence GENERATEDKEY_TEST_SEQ created.");
144          /*
145          if (stmQuery.execute("grant all on GENERATEDKEY_TEST_SEQ to " + strUserName))
146          {
147             // Close any results
148             stmQuery.getMoreResults(Statement.CLOSE_ALL_RESULTS);
149          }
150          Log.getLogger().log(Level.FINEST,
151                              "Access for sequence GENERATEDKEY_TEST_SEQ set for user "
152                              + strUserName);
153          */

154
155          ///////////////////////////////////////////////////////////////////////
156

157          if (stmQuery.execute("CREATE TABLE GENERATEDKEY_TEST" + NL +
158                               "(" + NL +
159                               " TEST_KEY INTEGER NOT NULL PRIMARY KEY," + NL +
160                               " TEST_VALUE VARCHAR(50) NOT NULL" + NL +
161                               ")"))
162          {
163             // Close any results
164
stmQuery.getMoreResults(Statement.CLOSE_ALL_RESULTS);
165          }
166          s_logger.log(Level.FINEST, "Table GENERATEDKEY_TEST created.");
167          /*
168          if (stmQuery.execute("grant all on GENERATEDKEY_TEST to " + strUserName))
169          {
170             // Close any results
171             stmQuery.getMoreResults(Statement.CLOSE_ALL_RESULTS);
172          }
173          Log.getLogger().log(Level.FINEST,
174                              "Access for table GENERATEDKEY_TEST set for user "
175                              + strUserName);
176          */

177
178          ///////////////////////////////////////////////////////////////////////
179

180          if (stmQuery.execute("CREATE DBPROCEDURE INSERT_GENERATEDKEY_TEST " + NL +
181                               "(" + NL +
182                               " IN IN_VALUE VARCHAR(50), " + NL +
183                               " OUT OUT_KEY INTEGER" + NL +
184                               ") " + NL +
185                               "AS" + NL +
186                               "TRY " + NL +
187                               " SET OUT_KEY = GENERATEDKEY_TEST_SEQ.NEXTVAL;" + NL +
188                               " INSERT INTO " + strUserName +
189                               ".GENERATEDKEY_TEST(TEST_KEY, TEST_VALUE) " + NL +
190                               " VALUES (:OUT_KEY, :IN_VALUE); " + NL +
191                               "CATCH" + NL +
192                               " IF $RC <> 0 THEN STOP ($RC, $ERRMSG);"))
193          {
194             // Close any results
195
stmQuery.getMoreResults(Statement.CLOSE_ALL_RESULTS);
196          }
197          s_logger.log(Level.FINEST, "Procedure INSERT_GENERATEDKEY_TEST created.");
198          /*
199          if (stmQuery.execute("grant execute on INSERT_GENERATEDKEY_TEST to " + strUserName))
200          {
201             // Close any results
202             stmQuery.getMoreResults(Statement.CLOSE_ALL_RESULTS);
203          }
204          Log.getLogger().log(Level.FINEST,
205                              "Access for procedure INSERT_GENERATEDKEY_TEST set for user "
206                              + strUserName);
207          */

208
209          ///////////////////////////////////////////////////////////////////////
210

211          if (stmQuery.execute("CREATE DBPROCEDURE INSERT_ROW_COUNT_TEST " + NL +
212                               "(" + NL +
213                               " IN IN_VALUE VARCHAR(50), " + NL +
214                               " OUT OUT_ROWS INTEGER" + NL +
215                               ") " + NL +
216                               "AS" + NL +
217                               "TRY " + NL +
218                               " SET OUT_ROWS = 0;" + NL +
219                               " INSERT INTO " + strUserName +
220                               ".GENERATEDKEY_TEST(TEST_KEY, TEST_VALUE) " + NL +
221                               " VALUES (GENERATEDKEY_TEST_SEQ.NEXTVAL, :IN_VALUE); " + NL +
222                               "CATCH" + NL +
223                               " IF $RC <> 0 THEN STOP ($RC, $ERRMSG);"))
224          {
225             // Close any results
226
stmQuery.getMoreResults(Statement.CLOSE_ALL_RESULTS);
227          }
228          s_logger.log(Level.FINEST, "Procedure INSERT_ROW_COUNT_TEST created.");
229          /*
230          if (stmQuery.execute("grant execute on INSERT_ROW_COUNT_TEST to " + strUserName))
231          {
232             // Close any results
233             stmQuery.getMoreResults(Statement.CLOSE_ALL_RESULTS);
234          }
235          Log.getLogger().log(Level.FINEST,
236                              "Access for procedure INSERT_ROW_COUNT_TEST set for user "
237                              + strUserName);
238          */

239
240          ///////////////////////////////////////////////////////////////////////
241

242          if (stmQuery.execute("CREATE DBPROCEDURE UPDATE_TRANSACTION_TEST_VALUE" + NL +
243                               "(" + NL +
244                               " IN IN_OLD_TEST_VALUE VARCHAR(50)," + NL +
245                               " IN IN_NEW_TEST_VALUE VARCHAR(50)" + NL +
246                               ")" + NL +
247                               "AS " + NL +
248                               "TRY " + NL +
249                               " UPDATE " + strUserName + ".TRANSACTION_TEST SET " + NL +
250                               " TEST_VALUE = :IN_NEW_TEST_VALUE " + NL +
251                               " WHERE TEST_VALUE = :IN_OLD_TEST_VALUE; " + NL +
252                               "CATCH" + NL +
253                               " IF $RC <> 0 THEN STOP ($RC, $ERRMSG);"))
254          {
255             // Close any results
256
stmQuery.getMoreResults(Statement.CLOSE_ALL_RESULTS);
257          }
258          s_logger.log(Level.FINEST, "Procedure UPDATE_TRANSACTION_TEST_VALUE created.");
259          /*
260          if (stmQuery.execute("grant all on UPDATE_TRANSACTION_TEST_VALUE to " + strUserName))
261          {
262             // Close any results
263             stmQuery.getMoreResults(Statement.CLOSE_ALL_RESULTS);
264          }
265          Log.getLogger().log(Level.FINEST,
266                              "Access for procedure UPDATE_TRANSACTION_TEST_VALUE set for user "
267                              + strUserName);
268          */

269       }
270       catch (SQLException JavaDoc sqleExc)
271       {
272          s_logger.log(Level.WARNING, "Failed to create database test schema.",
273                              sqleExc);
274          throw sqleExc;
275       }
276       finally
277       {
278          DatabaseUtils.closeStatement(stmQuery);
279       }
280    }
281
282    /**
283     * {@inheritDoc}
284     */

285    public String JavaDoc getInsertGeneratedKey(
286    )
287    {
288       return "insert into generatedkey_test(test_key, test_value) " +
289               "values (generatedkey_test_seq.NEXTVAL, ?)";
290    }
291
292    /**
293     * {@inheritDoc}
294     */

295    public int[] executeInsertGeneratedKey2(
296       Connection JavaDoc dbConnection,
297       String JavaDoc strValue
298    ) throws SQLException JavaDoc
299    {
300       CallableStatement JavaDoc insertStatement = null;
301       int iInsertCount;
302       int iGeneratedKey = 0;
303       int[] returnValues = null;
304       
305       try
306       {
307          insertStatement = dbConnection.prepareCall(
308                               "call INSERT_GENERATEDKEY_TEST (?, ?)");
309          insertStatement.setString(1, strValue);
310          insertStatement.registerOutParameter(2, Types.INTEGER);
311
312          iInsertCount = insertStatement.executeUpdate();
313          iGeneratedKey = insertStatement.getInt(2);
314
315          returnValues = new int[2];
316          returnValues[0] = iInsertCount;
317          returnValues[1] = iGeneratedKey;
318       }
319       finally
320       {
321          DatabaseUtils.closeStatement(insertStatement);
322       }
323       
324       return returnValues;
325    }
326
327    /**
328     * {@inheritDoc}
329     */

330    public int executeUpdateTestValue(
331       Connection JavaDoc dbConnection,
332       String JavaDoc strOldValue,
333       String JavaDoc strNewValue
334    ) throws SQLException JavaDoc
335    {
336       CallableStatement JavaDoc updateStatement = null;
337       int iUpdateCount = 0;
338
339       try
340       {
341          updateStatement = dbConnection.prepareCall("call UPDATE_TRANSACTION_TEST_VALUE (?, ?)");
342          updateStatement.setString(1, strOldValue);
343          updateStatement.setString(2, strNewValue);
344             
345          // here is the bug in SAP DB, if there is called stored procedure without
346
// output parameters, there is not returned number of updated records
347
iUpdateCount = updateStatement.executeUpdate();
348       }
349       finally
350       {
351          DatabaseUtils.closeStatement(updateStatement);
352       }
353       
354       return iUpdateCount;
355    }
356
357    /**
358     * {@inheritDoc}
359     */

360    public int[] executeInsertRow(
361       Connection JavaDoc dbConnection,
362       String JavaDoc strValue)
363    throws SQLException JavaDoc
364    {
365       CallableStatement JavaDoc insertStatement = null;
366       int iInsertCount;
367       int iInsertCountReturnedFromSP = 0;
368       int[] returnValues = null;
369       
370       try
371       {
372          insertStatement = dbConnection.prepareCall(
373                               "call INSERT_ROW_COUNT_TEST (?, ?)");
374          insertStatement.setString(1, strValue);
375          insertStatement.registerOutParameter(2, Types.INTEGER);
376
377          iInsertCount = insertStatement.executeUpdate();
378          iInsertCountReturnedFromSP = insertStatement.getInt(2);
379
380          returnValues = new int[2];
381          
382          // value (number of affected rows) returned from insertStatement.executeUpdate();
383
returnValues[0] = iInsertCount;
384          
385          // value (number of inserted rows) returned from stored procedure. It will be always 0
386
// here, because there is not supported by DB getting nunber of affected rows.
387
returnValues[1] = iInsertCountReturnedFromSP;
388       }
389       finally
390       {
391          DatabaseUtils.closeStatement(insertStatement);
392       }
393       
394       return returnValues;
395    }
396
397    /**
398     * {@inheritDoc}
399     */

400    public void createTestUser(
401       Connection JavaDoc cntAdminDBConnection,
402       String JavaDoc strDatabaseURL,
403       String JavaDoc strUserName,
404       String JavaDoc strUserPassword
405    ) throws SQLException JavaDoc
406    {
407       Statement JavaDoc stmQuery = null;
408       try
409       {
410          String JavaDoc strCreateUserQuery = "CREATE USER " + strUserName + " PASSWORD "
411                                      + strUserPassword + " RESOURCE NOT EXCLUSIVE";
412
413          stmQuery = cntAdminDBConnection.createStatement();
414
415          if (stmQuery.execute(strCreateUserQuery))
416          {
417             // Close any results
418
stmQuery.getMoreResults(Statement.CLOSE_ALL_RESULTS);
419          }
420       }
421       finally
422       {
423          DatabaseUtils.closeStatement(stmQuery);
424       }
425    }
426
427    /**
428     * {@inheritDoc}
429     */

430    public void dropTestUser(
431       Connection JavaDoc cntAdminDBConnection,
432       String JavaDoc strDatabaseURL,
433       String JavaDoc strUserName
434    ) throws SQLException JavaDoc
435    {
436       Statement JavaDoc stmQuery = null;
437       try
438       {
439          String JavaDoc strDropUserQuery = "DROP USER " + strUserName;
440
441          stmQuery = cntAdminDBConnection.createStatement();
442
443          if (stmQuery.execute(strDropUserQuery))
444          {
445             // Close any results
446
stmQuery.getMoreResults(Statement.CLOSE_ALL_RESULTS);
447          }
448       }
449       finally
450       {
451          DatabaseUtils.closeStatement(stmQuery);
452       }
453    }
454 }
455
Popular Tags