1 21 22 package org.opensubsystems.core.persist.db.driver.sapdb; 23 24 import java.sql.CallableStatement ; 25 import java.sql.Connection ; 26 import java.sql.SQLException ; 27 import java.sql.Statement ; 28 import java.sql.Types ; 29 import java.util.logging.Level ; 30 import java.util.logging.Logger ; 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 47 public class SapDBDatabaseTestSchema extends DatabaseTestSchema 48 { 49 97 98 100 103 private static Logger s_logger = Log.getInstance(SapDBDatabaseTestSchema.class); 104 105 107 112 public SapDBDatabaseTestSchema( 113 ) throws OSSException 114 { 115 super(); 116 } 117 118 120 123 public void create( 124 Connection cntDBConnection, 125 String strUserName 126 ) throws SQLException 127 { 128 super.create(cntDBConnection, strUserName); 130 131 Statement 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 stmQuery.getMoreResults(Statement.CLOSE_ALL_RESULTS); 142 } 143 s_logger.log(Level.FINEST, "Sequence GENERATEDKEY_TEST_SEQ created."); 144 154 155 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 stmQuery.getMoreResults(Statement.CLOSE_ALL_RESULTS); 165 } 166 s_logger.log(Level.FINEST, "Table GENERATEDKEY_TEST created."); 167 177 178 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 stmQuery.getMoreResults(Statement.CLOSE_ALL_RESULTS); 196 } 197 s_logger.log(Level.FINEST, "Procedure INSERT_GENERATEDKEY_TEST created."); 198 208 209 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 stmQuery.getMoreResults(Statement.CLOSE_ALL_RESULTS); 227 } 228 s_logger.log(Level.FINEST, "Procedure INSERT_ROW_COUNT_TEST created."); 229 239 240 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 stmQuery.getMoreResults(Statement.CLOSE_ALL_RESULTS); 257 } 258 s_logger.log(Level.FINEST, "Procedure UPDATE_TRANSACTION_TEST_VALUE created."); 259 269 } 270 catch (SQLException 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 285 public String getInsertGeneratedKey( 286 ) 287 { 288 return "insert into generatedkey_test(test_key, test_value) " + 289 "values (generatedkey_test_seq.NEXTVAL, ?)"; 290 } 291 292 295 public int[] executeInsertGeneratedKey2( 296 Connection dbConnection, 297 String strValue 298 ) throws SQLException 299 { 300 CallableStatement 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 330 public int executeUpdateTestValue( 331 Connection dbConnection, 332 String strOldValue, 333 String strNewValue 334 ) throws SQLException 335 { 336 CallableStatement 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 iUpdateCount = updateStatement.executeUpdate(); 348 } 349 finally 350 { 351 DatabaseUtils.closeStatement(updateStatement); 352 } 353 354 return iUpdateCount; 355 } 356 357 360 public int[] executeInsertRow( 361 Connection dbConnection, 362 String strValue) 363 throws SQLException 364 { 365 CallableStatement 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 returnValues[0] = iInsertCount; 384 385 returnValues[1] = iInsertCountReturnedFromSP; 388 } 389 finally 390 { 391 DatabaseUtils.closeStatement(insertStatement); 392 } 393 394 return returnValues; 395 } 396 397 400 public void createTestUser( 401 Connection cntAdminDBConnection, 402 String strDatabaseURL, 403 String strUserName, 404 String strUserPassword 405 ) throws SQLException 406 { 407 Statement stmQuery = null; 408 try 409 { 410 String strCreateUserQuery = "CREATE USER " + strUserName + " PASSWORD " 411 + strUserPassword + " RESOURCE NOT EXCLUSIVE"; 412 413 stmQuery = cntAdminDBConnection.createStatement(); 414 415 if (stmQuery.execute(strCreateUserQuery)) 416 { 417 stmQuery.getMoreResults(Statement.CLOSE_ALL_RESULTS); 419 } 420 } 421 finally 422 { 423 DatabaseUtils.closeStatement(stmQuery); 424 } 425 } 426 427 430 public void dropTestUser( 431 Connection cntAdminDBConnection, 432 String strDatabaseURL, 433 String strUserName 434 ) throws SQLException 435 { 436 Statement stmQuery = null; 437 try 438 { 439 String strDropUserQuery = "DROP USER " + strUserName; 440 441 stmQuery = cntAdminDBConnection.createStatement(); 442 443 if (stmQuery.execute(strDropUserQuery)) 444 { 445 stmQuery.getMoreResults(Statement.CLOSE_ALL_RESULTS); 447 } 448 } 449 finally 450 { 451 DatabaseUtils.closeStatement(stmQuery); 452 } 453 } 454 } 455 | Popular Tags |