SybaseListDatabaseUtils


1   /*
2    * Copyright (c) 2003 - 2007 OpenSubsystems s.r.o. Slovak Republic. All rights reserved.
3    * 
4    * Project: OpenSubsystems
5    * 
6    * $Id: SybaseListDatabaseUtils.java,v 1.18 2007/01/07 06:14:23 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.patterns.listdata.persist.db;
23  
24  import java.sql.CallableStatement  ;
25  import java.sql.Connection  ;
26  import java.sql.PreparedStatement  ;
27  import java.sql.ResultSet  ;
28  import java.sql.SQLException  ;
29  import java.util.List  ;
30  
31  import org.opensubsystems.core.error.OSSDatabaseAccessException;
32  import org.opensubsystems.core.error.OSSException;
33  import org.opensubsystems.core.persist.db.DatabaseConnectionFactoryImpl;
34  import org.opensubsystems.core.persist.db.DatabaseImpl;
35  import org.opensubsystems.core.util.DatabaseUtils;
36  import org.opensubsystems.core.util.GlobalConstants;
37  import org.opensubsystems.patterns.listdata.data.ListOptions;
38  
39  /**
40   * This class is used for common list retrieval and manipulation routines 
41   * specific for Sybase ASE. 
42   * 
43   * @version $Id: SybaseListDatabaseUtils.java,v 1.18 2007/01/07 06:14:23 bastafidli Exp $
44   * @author Julo Legeny
45   * @code.reviewer Miro Halas
46   * @code.reviewed 1.14 2006/03/14 23:56:56 jlegeny
47   */
48  public class SybaseListDatabaseUtils extends ListDatabaseUtils
49  {
50     // Constructors /////////////////////////////////////////////////////////////
51  
52     /**
53      * Constructor
54      */
55     public SybaseListDatabaseUtils()
56     {
57        super();
58     }
59  
60     // Public methods ///////////////////////////////////////////////////////////
61     
62     /**
63      * {@inheritDoc}
64      */
65     public List   getObjectList(
66        ListOptions         options,
67        String                query,
68        ListDatabaseFactory factory,
69        ListDatabaseSchema schema       
70     ) throws OSSException
71     {
72        List                lstObjects      = null;
73        Connection          cntConnection   = null;
74        ResultSet           rsQueryResults  = null;
75        PreparedStatement   pstmQuery       = null;
76        // MyTimer           timer           = new MyTimer();
77        CallableStatement   selectStatement = null;
78        try
79        {
80           // Request autocommit true since we are just reading data from the database
81           cntConnection = DatabaseConnectionFactoryImpl.getInstance().requestConnection(true);
82        
83           int iCount = 0;
84  
85           if ((!DatabaseImpl.getInstance().hasAbsolutePositioningSupport())
86               || (DatabaseImpl.getInstance().preferCountToLast()))
87           {
88              // --------------------------------------------------------------
89              // 1. If the count() is preferred to last() to determine how
90              // many results are there available or if the last() is not supported
91              // then execute count separately
92              // --------------------------------------------------------------
93              
94              // get number of all items retrieved by count(*)
95              iCount = getSelectCount(cntConnection, query, schema, options.getDomainId());
96     
97              // set option positioning, at this position we know the real number of all records
98              setOptionPositioning(iCount, options);
99              
100             // allocate object list
101             lstObjects = allocateObjectList(options, iCount);
102    
103             // default implementation doesn't do anything
104             // if there is supported LIMIT, process changes on original query and use them
105             query = preprocessSelectQuery(query, options, schema);
106 /*
107             pstmQuery = cntConnection.prepareStatement("set rowcount 20");
108             pstmQuery.addBatch();
109             // declare cursor
110             // timer.reset();
111             pstmQuery = cntConnection.prepareStatement(query);
112             if (schema.isInDomain())
113             {
114                pstmQuery.setInt(1, options.getDomainId());
115             }
116             // s_logger.info("Duration for prepare statement = " + timer.toString());
117             pstmQuery.addBatch();
118             pstmQuery = cntConnection.prepareStatement("set rowcount 0");
119             pstmQuery.addBatch();
120             timer.reset();
121             int[]      arrInsertedReturn;
122             arrInsertedReturn = pstmQuery.executeBatch();
123 */
124             selectStatement = cntConnection.prepareCall("EXEC GET_LIST ?, ?");
125             selectStatement.setInt(1, options.getEndPosition());
126             selectStatement.setString(2, query);
127 
128             rsQueryResults = selectStatement.executeQuery();
129 
130             if (lstObjects != null)
131             {
132                // If query with list range support will be used then the result
133                // is the first record in the result set
134                // timer.reset();
135                if (DatabaseImpl.getInstance().hasSelectListRangeSupport())
136                {
137                   rsQueryResults.next();
138                }
139                else
140                {
141                   rsQueryResults.absolute(options.getBeginPosition());                  
142                }
143                // s_logger.info("Duration for absolute/first() for load data = " 
144                //               + timer.toString());
145             }
146          }
147          else
148          {      
149             // --------------------------------------------------------------
150             // 2. If the last() is preferred to count() so determine how many
151             // items are available by executing the query and checking how 
152             // many items it returned
153             // --------------------------------------------------------------
154    
155             // s_logger.info("Preparing query " + query);
156             // timer.reset();
157             pstmQuery = cntConnection.prepareStatement(
158                query,
159                DatabaseImpl.getInstance().getSelectListResultSetType(), 
160                DatabaseImpl.getInstance().getSelectListResultSetConcurrency()
161             );
162             if (schema.isInDomain())
163             {
164                pstmQuery.setInt(1, options.getDomainId());
165             }
166             // s_logger.info("Duration for prepare statement = " + timer.toString());
167 
168 
169             // timer.reset();
170             //rsQueryResults = pstmQuery.executeQuery();
171             // s_logger.info("Duration for executing query = " + timer.toString());
172    
173             // We should use last() to determine total number of items
174             // timer.reset();
175             iCount = getTotalRecords(rsQueryResults, options);
176             // s_logger.info("Duration for last() = " + timer.toString());
177 
178             // set option positioning, at this position we know the real number of all records
179             setOptionPositioning(iCount, options);
180             
181             // allocate object list
182             lstObjects = allocateObjectList(options, iCount);
183 
184             if (lstObjects != null)
185             {
186                // timer.reset();
187                rsQueryResults.absolute(options.getBeginPosition());                  
188                // s_logger.info("Duration for absolute() for load data = " + timer.toString());
189             }
190          }
191 
192          // =====================================================================
193          
194          // At this point we have already constructed and executed query, 
195          // allocated object list and set up options. Now load particular 
196          // data from the result set.
197          if (lstObjects != null)
198          {                     
199             if (GlobalConstants.ERROR_CHECKING)
200             {        
201                assert iCount > 0 
202                       : "List should be null for empty result";
203             }
204             
205             int   iHelpCounter = 0; 
206             // Cache this so we don't have to call it always inside of loop
207             int[] columnCodes = options.getRetrieveColumnCodes();
208 
209             // not to call rsQueryResults.next() for first loop
210             // timer.reset();
211             while ((iHelpCounter == 0 || rsQueryResults.next())
212                && ((iHelpCounter < options.getPageSize()) 
213                || (options.getPageSize() == ListOptions.PAGE_SIZE_ALL)))
214             {
215                iHelpCounter++;
216                lstObjects.add(
217                factory.load(rsQueryResults, columnCodes, 1));
218             }
219             // s_logger.info("Duration for load data = " + timer.toString());
220          }
221 
222          // =====================================================================
223 
224          // Call method that check if can be processed list keepeng selected and
225          // if yes, do it.
226          lstObjects = getObjectListKeepSelected(factory, cntConnection, query, 
227                                                 options, lstObjects, schema);
228       }
229       catch (SQLException   sqleExc)
230       {
231          throw new OSSDatabaseAccessException(
232                       "Failed to retrieve specified list of data objects" +
233                       " from the database.", sqleExc);
234       }
235       finally
236       {
237          DatabaseUtils.closeStatement(selectStatement);
238          DatabaseUtils.closeResultSetAndStatement(rsQueryResults, pstmQuery);
239          DatabaseConnectionFactoryImpl.getInstance().returnConnection(cntConnection);
240       }
241    
242       return lstObjects; 
243    }
244 
245    /**
246     * {@inheritDoc}
247     */
248    public String   preprocessSelectQuery(
249       String   inputQuery,
250       ListOptions options,
251       ListDatabaseSchema schema
252    ) throws OSSException
253    {
254       // if there is not supported ROWCOUNT, process changes on original query and use them
255       if (DatabaseImpl.getInstance().hasSelectListRangeSupport())
256       {
257          // =============================================================================
258          // 1. Used stored procedure and constructed select into temporary table. 
259          // =============================================================================
260          // For Sybase ASE there will be used stored procedure for retrieving particular 
261          // limited data into the list. There will be set up ROWCOUNT parameter and called 
262          // query that will select particular data and insert them into the temporary table.
263          // Final required data will be selected from the temporary table by rownumber.
264 
265          // Stored procedure has following structure:
266          //    create proc GET_LIST @end_index int, 
267          //                         @command varchar(500) 
268          //    as 
269          //       set rowcount @end_index 
270          //       exec (@command) 
271          //       set rowcount 0
272 
273          // Parameter @end_index is the EndPosition from the options and defines maximal number
274          // of rows that will be selected into the temporary table.
275          // Parameter @command is the particular preprocessed query that will be executed by 
276          // procedure.
277          // Stored procedure call:  exec test1 ?, ?
278          
279          // IMPORTANT: There has to be executed command: exec sp_procxmode "GET_LIST", 'anymode'
280          //            after stored procedure 'GET_LIST' was created.
281          // Executing a Sybase stored procedure causes an exception if it was created with 
282          // the "unchained" transaction mode.
283          // A Sybase stored procedure gets tagged as "chained" or "unchained" depending on the 
284          // environment in which it was created. It will cause an exception if it is invoked 
285          // within a different transaction mode. EOF always turns off autocommit, which means 
286          // it always operates in what Sybase calls "chained" mode. Stored procedures declared 
287          // in an "unchained" environment will not work with EOF. The error message will look 
288          // like this:
289          // SQL State:ZZZZZ -- error code: 7713 -- msg: Stored procedure 'MY_PROCEDURE' may be 
290          //           run only in unchained transaction mode. The 'SET CHAINED OFF' command will 
291          //           cause the current session to use unchained transaction mode.
292          // The solution is to change the transaction mode of the stored procedure to either 
293          // chained or anymode - use: sp_procxmode "sp_myproc", 'anymode' 
294 
295          // There have to be converted all IDENTITY columns, because while constructing temporary
296          // table, there is created IDENTITY column for 'rownum'. There is not allowed to have
297          // more identity columns within the one table. We are using IDENTITY for all ID columns.
298          // We just convert them into the NUMERIC(10, 0).
299          // Form of the final query, that will be constructed:
300          //    select convert(NUMERIC(10,0), <table>.ID) as ID, 
301          //           <table>.<column2>, 
302          //           rownum = identity(9) into #tempA from <table> 
303          //    where <column1> = <value> and ID not in(<value>) 
304          //    order by <table>.<column> 
305          //    select * from #tempA where rownum between <start_position> and <end_position>
306          
307          // IMPORTANT: for executing command that creates temporary table within the Sybase
308          //            'tempdb' database, there has to be set up "ddl in tran" database option 
309          //            to true: sp_dboption database_name,"ddl in tran", true
310          //            Read more at:  http://manuals.sybase.com/onlinebooks/
311          //                           group-as/asg1250e/sqlug/@Generic__BookTextView/53037
312 
313          int iFromStart      = 0;
314          int iActualPosition = -1;
315          int iIDCount        = 1;
316 
317          StringBuffer   buffer                 = new StringBuffer  ();
318          StringBuffer   bufferRetrievedColumns = new StringBuffer  ();
319          StringBuffer   strAllColumnNames      = new StringBuffer  ();
320 
321          // We need to search as case sensitive. But carefull, we cannot change the case
322          // of the original query since some databases are case sensitive when
323          // using columns names, etc. So we will convert original query to lower case and
324          // we will provide search on this new query.
325          String   strQueryLowerCase = inputQuery.toLowerCase();
326 
327          iFromStart = strQueryLowerCase.indexOf(" from ");
328          String   strColumnName = "";
329          String   strTemp = "";
330          // Initialize string that will contain all retrieved columns.
331          strAllColumnNames.append(inputQuery.substring(
332                                      strQueryLowerCase.indexOf("select ") + "select ".length(), 
333                                      iFromStart));
334          
335          // If there will be retrieved column with name ID, we have to convert it because
336          // this is IDENTITY column and there is not allowed to have 2 identity columns within
337          // one table.
338          while (strAllColumnNames.indexOf(",") != -1)
339          {
340             // There are more columns that will be retrieved. Find out if there will be
341             // retrieved some column with name ID. If YES, convert it to NUMERIC(10, 0).
342             iActualPosition = strAllColumnNames.indexOf(",");
343             strTemp = strAllColumnNames.substring(0, iActualPosition);
344             // Column can be defined as TABLE.COLUMN_NAME, so we need to parse table name 
345             // and also column name separately.
346             if (strTemp.indexOf(".") != -1)
347             {
348                // there is used TABLE.COLUMN
349                strColumnName = strTemp.substring(strTemp.indexOf(".") + 1, strTemp.length());
350             }
351             else
352             {
353                // there is used only COLUMN name (without TABLE specification)
354                strColumnName = strTemp;
355             }
356 
357             if (strColumnName.equalsIgnoreCase("ID"))
358             {
359                // If the column name = ID, add CONVERT clause to the SQL command
360                bufferRetrievedColumns.append("convert(NUMERIC(10, 0), ");
361                bufferRetrievedColumns.append(strTemp);
362                bufferRetrievedColumns.append(") as ID");
363                bufferRetrievedColumns.append(iIDCount++);
364             }
365             else
366             {
367                bufferRetrievedColumns.append(strTemp);
368             }
369             bufferRetrievedColumns.append(", ");
370             
371             // delete used columns from the all columns
372             strAllColumnNames.delete(0, iActualPosition + 1);
373          }
374 
375          // Column can be defined as TABLE.COLUMN_NAME, so we need to parse table name 
376          // and also column name separately.
377          if (strAllColumnNames.indexOf(".") != -1)
378          {
379             // there is used TABLE.COLUMN
380             strColumnName = strAllColumnNames.substring(strAllColumnNames.indexOf(".") + 1, 
381                                                         strAllColumnNames.length());
382          }
383          else
384          {
385             // there is used only COLUMN name (without TABLE specification)
386             strColumnName = strAllColumnNames.toString();
387          }
388 
389          if (strColumnName.equalsIgnoreCase("ID"))
390          {
391             // If the column name = ID, add CONVERT clause to the SQL command
392             bufferRetrievedColumns.append("convert(NUMERIC(10, 0), ");
393             bufferRetrievedColumns.append(strTemp);
394             bufferRetrievedColumns.append(") as ID");
395             bufferRetrievedColumns.append(iIDCount++);
396          }
397          else
398          {
399             bufferRetrievedColumns.append(strAllColumnNames);
400          }
401 
402          
403          buffer.append("select ");
404          buffer.append(bufferRetrievedColumns);
405          buffer.append(", rownum = identity(9) into #tempA");
406          buffer.append(inputQuery.substring(iFromStart, inputQuery.length()));
407          buffer.append(" select * from #tempA where rownum between ");
408          buffer.append(options.getBeginPosition());
409          buffer.append(" and ");
410          buffer.append(options.getEndPosition());
411          buffer.append(" order by rownum ");
412          
413          // if there is occured ? within the query it has to be substituted into
414          // particular DOMAIN_ID (from options). It is therefore the quey is sent
415          // as parameter into stored procedure and there cannot be parametrized
416          // value (? as used for prepared statement)
417          int iIndex = -1;
418          iIndex = buffer.indexOf("?");
419          if (iIndex > -1)
420          {
421             buffer.replace(iIndex, iIndex + 1, String.valueOf(options.getDomainId()));
422          }
423 
424          return buffer.toString();
425 
426          // =============================================================================
427          // 2. Used stored procedure and constructed siple select limited with ROWCOUNT.
428          //    Particular limited records will be retrieved by last() or absolute()
429          // =============================================================================
430       }
431       else
432       {
433          // if there is not supported ROWCOUNT, just call super
434          return super.preprocessSelectQuery(inputQuery, options, schema);
435       }
436    }
437 }
438
A to Z: JavaDoc & Examples Daily Java News & Articles Open Source Projects Open Source Codes Free Computer Books Remove Frame
Popular Tags