Revision 2560
Added by sgarg over 19 years ago
| src/edu/ucsb/nceas/metacat/DBUtil.java | ||
|---|---|---|
| 345 | 345 |
serialNumber=dbConn.getCheckOutSerialNumber(); |
| 346 | 346 |
pstmt = |
| 347 | 347 |
dbConn.prepareStatement( |
| 348 |
"SELECT docid, rev, acc FROM " +
|
|
| 348 |
"SELECT docid, max(rev) FROM " +
|
|
| 349 | 349 |
"( " + |
| 350 |
"SELECT docid, rev, acc FROM " + |
|
| 351 |
"(" +
|
|
| 352 |
"SELECT docid, rev, " + |
|
| 353 |
"SUBSTR(docid, INSTR(docid, '" + sep + "', 1)+1)+0 acc " + |
|
| 350 |
"SELECT docid, rev " + |
|
| 354 | 351 |
"FROM xml_documents " + |
| 355 | 352 |
"WHERE docid LIKE ? " + |
| 356 |
"ORDER BY acc DESC " + |
|
| 357 |
") " + |
|
| 358 |
"WHERE rownum = 1 " + |
|
| 359 | 353 |
"UNION " + |
| 360 |
"SELECT docid, rev, acc FROM " + |
|
| 361 |
"(" +
|
|
| 362 |
"SELECT docid, rev, " + |
|
| 363 |
"SUBSTR(docid, INSTR(docid, '" + sep + "', 1)+1)+0 acc " + |
|
| 354 |
"SELECT docid, rev " + |
|
| 364 | 355 |
"FROM xml_revisions " + |
| 365 | 356 |
"WHERE docid LIKE ? " + |
| 366 |
"ORDER BY acc DESC " + |
|
| 367 |
") " + |
|
| 368 |
"WHERE rownum = 1 " + |
|
| 369 |
") " + |
|
| 370 |
"ORDER BY acc DESC" |
|
| 357 |
") AS subQueryResult" + |
|
| 358 |
" GROUP BY docid" |
|
| 371 | 359 |
); |
| 372 | 360 |
|
| 373 | 361 |
pstmt.setString(1,scope + sep + "%"); |
| 374 | 362 |
pstmt.setString(2,scope + sep + "%"); |
| 375 | 363 |
pstmt.execute(); |
| 376 | 364 |
ResultSet rs = pstmt.getResultSet(); |
| 377 |
boolean tableHasRows = rs.next(); |
|
| 378 |
// 0, 1 or 2 possible num of rows |
|
| 379 |
// get the first one which is the max accnum |
|
| 380 |
if (tableHasRows) {
|
|
| 381 |
accnum = rs.getString(1) + sep + rs.getString(2); |
|
| 382 |
//tableHasRows = rs.next(); |
|
| 365 |
|
|
| 366 |
int max = 0; |
|
| 367 |
String temp = null; |
|
| 368 |
|
|
| 369 |
while(rs.next()){
|
|
| 370 |
temp = rs.getString(1); |
|
| 371 |
if(temp != null){
|
|
| 372 |
temp = temp.substring(temp.indexOf(scope) + scope.length() + 1); |
|
| 373 |
try {
|
|
| 374 |
int localid = Integer.parseInt(temp); |
|
| 375 |
if (localid > max){
|
|
| 376 |
max = localid; |
|
| 377 |
accnum = rs.getString(1) + sep + rs.getString(2); |
|
| 378 |
} |
|
| 379 |
} catch (NumberFormatException nfe){
|
|
| 380 |
// ignore the exception as it is possible that the |
|
| 381 |
// localid in the identifier is not an integer |
|
| 382 |
} |
|
| 383 |
} |
|
| 383 | 384 |
} |
| 384 | 385 |
|
| 385 | 386 |
pstmt.close(); |
Also available in: Unified diff
Replaced SQL queries in getMaxDocid() which used INSTR with queries which dont use INSTR. Now the INSTR related computation is done in Java code