001    /**
002     * Copyright (c) 2003 Daffodil Software Ltd all rights reserved,
003     * Modifications Copyright (c) 2008 Regiscope Digital Imaging Co, LLC, All rights reserved.
004     * This program is free software; you can redistribute it and/or modify
005     * it under the terms of version 2 of the GNU General Public License as
006     * published by the Free Software Foundation.
007     * There are special exceptions to the terms and conditions of the GPL
008     * as it is applied to this software. See the GNU General Public License for more details.
009     *
010     * This program is distributed in the hope that it will be useful,
011     * but WITHOUT ANY WARRANTY; without even the implied warranty of
012     * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
013     * GNU General Public License for more details.
014     *
015     * You should have received a copy of the GNU General Public License
016     * along with this program; if not, write to the Free Software
017     * Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA
018     */
019    
020    package org.dbreplicator.replication.synchronize;
021    
022    import java.math.*;
023    import java.sql.*;
024    import java.util.*;
025    
026    import org.dbreplicator.replication.*;
027    import org.dbreplicator.replication.DBHandler.*;
028    import org.dbreplicator.replication.column.*;
029    import org.dbreplicator.replication.xml.*;
030    import java.io.BufferedWriter;
031    
032    /**
033     * This is the main class which handles the synchronization operations on main table
034     * when merge handler reads Update operation from the XML file.
035     */
036    
037    public class OperationUpdate extends AbstractSynchronize {
038    
039     private PreparedStatement psForInitialRecordInShadowTable, psForActualUpdateInTable,
040          preparedStatementForLastRecord, preparedStatementForCommonId,
041          preparedStatementForDelete, updatePreparedStatement, psForOtherCommonId,
042          psForInsertIntoActualTable, psForUpdateInShadowTableForRemoteServer,
043          updatePreparedStatementSetNull;
044      private ArrayList voilationPkKeys;
045      private boolean NO_OPERATION = false;
046    
047    
048    
049      /**
050       * OperationUpdate handles the case related to update operation i.e. when a
051       * record with update operation is found in XML File.
052       * @param repTable0
053       * @param clientConnection0
054       * @param columnObjectTreeMap0
055       * @param conisderedId0
056       * @param lastSyncId0
057       * @param remoteServerName0
058       * @param dbHandler0                          AbstractDataBaseHandler
059       * @param bw0                                         BufferedWriter
060       * @param replicationType0            String
061       * @param transactionLogType0         String
062       * @param mdi0                                        MetaDataInfo
063       * @param isFirstPass0                        boolean
064       * @param isCurrentTableCyclic0       boolean
065       * @throws Exception
066       */
067      public OperationUpdate(RepTable repTable0, Connection clientConnection0,
068                             TreeMap columnObjectTreeMap0, Object conisderedId0,
069                             Object lastSyncId0, String remoteServerName0, /* bjt add last sync id */
070                             AbstractDataBaseHandler dbHandler0, BufferedWriter bw0,
071                             String replicationType0, String transactionLogType0,
072                             MetaDataInfo mdi0, boolean isFirstPass0,
073                             boolean isCurrentTableCyclic0) throws SQLException {
074        repTable = repTable0;
075        allColumnsMap = repTable.getAllColumns();
076        connection = clientConnection0;
077        columnObjectTreeMap = columnObjectTreeMap0;
078        remoteServerName = remoteServerName0;
079        conisderedId = conisderedId0;
080            lastSyncId = lastSyncId0; /* bjt - add last sync id */
081        tableName = repTable.getSchemaQualifiedName().toString();
082        dbHandler = dbHandler0;
083        mdi = mdi0;
084        shadowTable = dbHandler.getShadowTableName(tableName);
085        bw = bw0;
086        replicationType = replicationType0;
087        transactionLogType = transactionLogType0;
088    
089    // so as to include the last record of the shadow table in the search.
090    // when traceOriginalRecord is called, then we dont want the last record searched to be included in the search.
091    // so we are increasing the Last UID by 1.
092        Object lastShadowUidObj = getLastUIDFromShadowTable();
093        if ((lastShadowUidObj instanceof Integer)) { //for firebird
094          lastShadowUid = new Long( ( (Integer) lastShadowUidObj).longValue()+1);
095        }
096        else if ((lastShadowUidObj instanceof String)) { //for MySQL
097         lastShadowUid = new Long((Long.parseLong((String)lastShadowUidObj)+1));
098        }
099        else {
100          lastShadowUid = new Long(((Long)lastShadowUidObj).longValue()+1);
101        }
102        xmlElement_NULL = new XMLElement("NULL");
103        xmlElement_NULL.elementValue = "NULL";
104        isFirstPass = isFirstPass0;
105        isCurrentTableCyclic = isCurrentTableCyclic0;
106      }
107    
108      /**
109       * searches the record for pk_key in shadow table  and updates it in the corresponding main table.
110       * @param currentElement
111       * @throws SQLException
112       * @throws RepException
113       */
114      public void execute(XMLElement currentElement) throws SQLException, RepException {
115        try {
116          ArrayList updateElements = currentElement.getChildElements();
117          ArrayList rowElements = ( (XMLElement) updateElements.get(0)).getChildElements();
118          ArrayList changedColumns = ( (XMLElement) updateElements.get(1)).getChildElements();
119          ArrayList primaryColumns = ( (XMLElement) updateElements.get(2)).getChildElements();
120          tableColumnNames = new String[rowElements.size()];
121          String[] tableColumnValues = new String[rowElements.size()];
122          AbstractColumnObject[] tableColumnObj = new AbstractColumnObject[rowElements.size()];
123          primaryColumnNames = repTable.getPrimaryColumns();
124          int noOfPrimaryCol = primaryColumnNames.length;
125          Object[] newPrimaryColumnValues = new Object[primaryColumnNames.length];
126    
127          String columnNam;
128          for (int i = 0; i < rowElements.size(); i++) {
129            columnNam = ( (XMLElement) rowElements.get(i)).getAttribute();
130            columnNam =(String)allColumnsMap.get(columnNam);
131            tableColumnNames[i] = columnNam;
132            tableColumnValues[i] = ( (XMLElement) rowElements.get(i)).elementValue;
133            tableColumnObj[i] = ( (AbstractColumnObject) columnObjectTreeMap.get(tableColumnNames[i]));
134            for (int j = 0; j < noOfPrimaryCol; j++) {
135              if (tableColumnNames[i].equalsIgnoreCase(primaryColumnNames[j])) {
136                newPrimaryColumnValues[j] = ( (AbstractColumnObject) tableColumnObj[i]).getObject(tableColumnValues[i]);
137                break;
138              }
139            }
140          }
141          Object[] objArray = getObjectArrayForTable(tableColumnValues,tableColumnObj);
142          Object[] tableColumnObjects = (Object[]) objArray[0];
143          ArrayList nullTableColumnObjects = (ArrayList) objArray[1];
144    
145          // When No changes are done in updated column,i.e old row and new updated row is same ("NO_OPERATION")
146          boolean changedCol = true;
147          if (changedColumns.size() == 0) {
148            if (repTable.isLocalServerWinner()) {
149              return;
150            }
151            changedCol = false; // special handelling for NO_Operation ... same record with same pk willl be updated  again
152          }
153          int noOfChangedColumns = changedColumns.size();
154          changedColumnNames = new String[noOfChangedColumns];
155          changedColumnValues = new String[noOfChangedColumns];
156          XMLElement[] changedColElement = new XMLElement[noOfChangedColumns];
157          AbstractColumnObject[] changedColumnObject = new AbstractColumnObject[noOfChangedColumns];
158          for (int i = 0; i < noOfChangedColumns; i++) {
159            changedColElement[i] = (XMLElement) changedColumns.get(i);
160            changedColumnNames[i] = changedColElement[i].getAttribute();
161                    for (int j = 0; j < rowElements.size(); j++) {
162                            if (changedColumnNames[i].equalsIgnoreCase(tableColumnNames[j])) {
163                                    changedColumnValues[i] = tableColumnValues[j];
164                                    changedColElement[i].elementValue = tableColumnValues[j];
165                                    break;
166                            }
167                    }
168            changedColumnObject[i] = ( (AbstractColumnObject) columnObjectTreeMap.get(changedColumnNames[i]));
169          }
170    
171          String[] primaryColumnValues = new String[noOfPrimaryCol];
172          primaryKeyColumnsObject = new AbstractColumnObject[noOfPrimaryCol];
173    
174          for (int i = 0; i < noOfPrimaryCol; i++) {
175            primaryColumnValues[i] = ( (XMLElement) primaryColumns.get(i)).elementValue;
176            primaryKeyColumnsObject[i] = ( (AbstractColumnObject)columnObjectTreeMap.get(primaryColumnNames[i]));
177          }
178          Object[] primaryValueObjects = getObjectArrayForPrimaryKey(primaryColumnValues);
179    
180          // SPECIAL CASE ONLY FOR NO_OPERATION
181          if (!changedCol) {
182            // special handelling for NO_OPERATION ... assigning changedColumns to current primary keys
183            int pkLength = primaryColumnNames.length;
184            changedColElement = new XMLElement[pkLength];
185            changedColumnValues = new String[pkLength];
186            changedColumnNames = new String[pkLength];
187            changedColumnObject = new AbstractColumnObject[pkLength];
188            for (int i = 0; i < primaryColumnNames.length; i++) {
189              changedColumnNames[i] = primaryColumnNames[i];
190              changedColumnValues[i] = primaryColumnValues[i];
191              changedColumnObject[i] = primaryKeyColumnsObject[i];
192              XMLElement temp = new XMLElement(changedColumnNames[i]);
193              temp.setElementValue(changedColumnValues[i]);
194              changedColElement[i] = temp;
195            }
196            noOfChangedColumns = pkLength;
197            NO_OPERATION = true;
198          }
199    
200          if (psForInitialRecordInShadowTable == null) {
201            psForInitialRecordInShadowTable = makePSForInitialRecordInShadowTable();
202    
203            //    if (   == null )
204            //    because we can get diffrent number of changed columns for all or other rows
205            //    so we have to create ps every time for this.
206          }
207          psForActualUpdateInTable = makePSForActualUpdateInTable();
208    
209          if (psForUpdateInShadowTableForRemoteServer == null) {
210            psForUpdateInShadowTableForRemoteServer =
211                makePSForUpdateInShadowTableForRemoteServer();
212          }
213    
214          psForUpdateInShadowTableForRemoteServer.setString(1, remoteServerName);
215          int k = 0;
216          if (isFirstPass) {
217            for (int i = 0; i < noOfChangedColumns; i++) {
218              if (repTable.isIgnoredColumn(changedColumnNames[i]))
219                continue;
220              /** @todo  Changes made by sube For cyclic handling */
221              AbstractColumnObject columnOject = ( (AbstractColumnObject)columnObjectTreeMap.get(changedColumnNames[i]));
222    
223              /*
224               Once setAutoCommitFlag is set to true,we shouldn't change it to false
225               by checking for other columns for that 'if' check is used
226               */
227              if (setAutoCommitFlag) {
228                setAutoCommitFlag = checkAutocommit(dbHandler, columnOject);
229                connection.setAutoCommit(setAutoCommitFlag);
230              }
231    
232              if (isCurrentTableCyclic &&
233                  repTable.isForeignKeyColumn(changedColumnNames[i])) {
234                columnOject.setColumnObject(psForActualUpdateInTable, "NULL", k + 1);
235                k++;
236              }
237              else {
238                columnOject.setColumnObject(psForActualUpdateInTable,changedColElement[i], k + 1);
239                k++;
240              }
241    
242            }
243    
244            for (int i = 0; i < noOfPrimaryCol; i++) {
245              psForActualUpdateInTable.setObject(noOfChangedColumns + i + 1,primaryValueObjects[i]);
246              psForUpdateInShadowTableForRemoteServer.setObject(3 + i,primaryValueObjects[i]);
247            }
248    
249            Tracer tracer = new Tracer();
250            //getLastRecord(primaryValueObjects, conisderedId, tracer); /* bjt - should use last_sync_id ? */
251                    /* bjt try using greater of consideredid or lastsyncid */
252                    if (Integer.parseInt(conisderedId.toString()) > Integer.parseInt(lastSyncId.toString())) {
253                    getLastRecord(primaryValueObjects, conisderedId, tracer);
254                    } else {
255                            getLastRecord(primaryValueObjects, lastSyncId, tracer);
256                    }
257            voilationPkKeys = new ArrayList();
258            if (!tracer.recordFound) {
259              // get max UID/syncId from Shadow Table
260              Object lastSyncId = getLastUIDFromShadowTable();
261              // setting Object values for Update query for Shadow TABLW for REMOTE SERVER
262              psForUpdateInShadowTableForRemoteServer.setObject(2, lastSyncId);
263    
264              try {
265                psForActualUpdateInTable.executeUpdate();
266                // STATUS - A  update remote server name for update case WHEN NO OPERATION IS PERFORMED ON CLIENT SIDE i.e. no entry is found in shadow table
267                // update the old record in shadow table with remoteServer STATUIS -B
268                psForUpdateInShadowTableForRemoteServer.executeUpdate(); // Status- B Update in shadow table to remoteserverName
269                for (int i = 0; i < newPrimaryColumnValues.length; i++) {
270                  psForUpdateInShadowTableForRemoteServer.setObject(i + 3,newPrimaryColumnValues[i]);
271                }
272                psForUpdateInShadowTableForRemoteServer.executeUpdate(); // Status- A Update in shadow table to remoteserverName
273                loggingUpdateOperation(tableName, primaryColumnNames,primaryValueObjects, changedColumnNames,changedColumnValues, replicationType);
274                writeUpdateOperationInTransactionLogFile(bw, tableName,primaryColumnNames, primaryValueObjects, changedColumnNames,changedColumnValues, replicationType, transactionLogType);
275                updateCount++;
276              }
277              catch (SQLException ex) {
278    
279                              //System.out.println(" Operation update   Error Code ::   "+ex.getErrorCode());
280    
281                  // ex.printStackTrace();
282                  // PRIMARY CONSTRAINT VOILATION
283                if (!dbHandler.isPrimaryKeyException(ex)) {
284                  RepConstants.writeERROR_FILE(ex);
285                  throw ex;
286                }
287    
288                /*
289                 =============================
290                 COMMENTED CODE FOR ROLLBACK
291                 IF WE DO NOT WANT TO DELETE,
292                 BUT THIS MAKES A CASE TO FAIL
293                 INVOLVING NO_OPERATION.
294                 =============================
295                        primaryViolation(newPrimaryColumnValues, new Tracer()); // no use of tracer over here
296                        lastSyncId = getLastUIDFromShadowTable();
297                        // setting Object values for Update query for Shadow TABLW for REMOTE SERVER
298                 psForUpdateInShadowTableForRemoteServer.setObject(2, lastSyncId);
299                        psForActualUpdateInTable.executeUpdate();
300                        psForUpdateInShadowTableForRemoteServer.executeUpdate();
301                        for (int i = 0; i < newPrimaryColumnValues.length; i++) {
302                          psForUpdateInShadowTableForRemoteServer.setObject(i + 3,newPrimaryColumnValues[i]);
303                        }
304                        psForUpdateInShadowTableForRemoteServer.executeUpdate(); // Status- A Update in shadow table to remoteserverName
305                 */
306                // deleting the record
307                if (preparedStatementForDelete == null) {
308                  preparedStatementForDelete =makePreparedStatementForDeleteOnTable();
309                }
310                for (int i = 0; i < newPrimaryColumnValues.length; i++) {
311                  preparedStatementForDelete.setObject(i + 1,newPrimaryColumnValues[i]);
312                }
313    
314                if (isCurrentTableCyclic) {
315                  setNullInColumnOfChildTableToUpdateRecord(primaryColumnNames,newPrimaryColumnValues);
316                }
317                preparedStatementForDelete.executeUpdate();
318                lastSyncId = getLastUIDFromShadowTable();
319    
320                // setting Object values for Update query for Shadow TABLW for REMOTE SERVER
321                psForUpdateInShadowTableForRemoteServer.setObject(2, lastSyncId);
322                psForActualUpdateInTable.executeUpdate();
323                loggingUpdateOperation(tableName, primaryColumnNames,primaryValueObjects, changedColumnNames,changedColumnValues, replicationType);
324                writeUpdateOperationInTransactionLogFile(bw, tableName, primaryColumnNames, primaryValueObjects, changedColumnNames,changedColumnValues, replicationType, transactionLogType);
325                updateCount++;
326                // remoteserver name is not updated so that operaion performed on subscriber is taken on the publisher node.
327              }
328    
329            }
330            else {
331              if (tracer.type.equals(RepConstants.delete_operation)) {
332                if (NO_OPERATION) {
333                  return;
334                }
335                if (repTable.isLocalServerWinner()) {
336                  return;
337                }
338                if (psForInsertIntoActualTable == null) {
339                  psForInsertIntoActualTable = connection.prepareStatement(repTable.createInsertQueryForSnapShot());
340                }
341                int nullCnt = 0;
342                int position = 0;
343                for (int i = 0; i < tableColumnObjects.length; i++) {
344                  Object obj = tableColumnObjects[i];
345                  String columnName = tableColumnNames[i];
346                  if (repTable.isIgnoredColumn(columnName))
347                    continue;
348    
349                  /**
350                   *  Following check has been added to handle the case of
351                   *  cyclic tables.Set value of all columns to null that are refering
352                   *  to other columns.
353                   */
354    
355                  if (isCurrentTableCyclic &&
356                      repTable.isForeignKeyColumn(columnName)) {
357                    AbstractColumnObject aco = ( (AbstractColumnObject)columnObjectTreeMap.get(columnName));
358                    aco.setColumnObject(psForInsertIntoActualTable, "NULL",position + 1);
359                  }
360                  else if (obj != null) {
361                    psForInsertIntoActualTable.setObject(position + 1, obj);
362                  }
363                  else {
364                    ( (AbstractColumnObject) nullTableColumnObjects.get(nullCnt)).
365                        setColumnObject(psForInsertIntoActualTable, xmlElement_NULL,position + 1);
366                    nullCnt++;
367                  }
368                  position++;
369                }
370                Object lastSyncId;
371                try {
372                  lastSyncId = getLastUIDFromShadowTable();
373                  psForInsertIntoActualTable.execute();
374                }
375                catch (SQLException ex) { // primary constrant voilation
376                  if (!dbHandler.isPrimaryKeyException(ex)) {
377                    throw ex;
378                  }
379                  primaryViolation(newPrimaryColumnValues, new Tracer()); // no operation found for tracer till now
380                  lastSyncId = getLastUIDFromShadowTable();
381                  psForInsertIntoActualTable.execute();
382                }
383    
384                // update shadow table with remoteserverNAme FOR INSERT
385                // get max UID/syncId from Shadow Table
386                psForUpdateInShadowTableForRemoteServer.setObject(2, lastSyncId);
387                for (int i = 0; i < newPrimaryColumnValues.length; i++) {
388                  psForUpdateInShadowTableForRemoteServer.setObject(i + 3,newPrimaryColumnValues[i]);
389                }
390                psForUpdateInShadowTableForRemoteServer.executeUpdate(); // updating new Inserted Record in shadow table with remoteServerName
391              }
392              else {
393                // primary key values to be updated with  newPrimaryColumnValues
394                            /*
395                             * bjt - if only foreign key fields are updated and foreign key is of type update...cascade
396                             * and table referenced by foreign key is a replicated table, then 
397                             * we might not need to do any updating.  Instead, for now do a select to see if fields have already been 
398                             * updated, and if so do nothing.  We can comment the select out or make it a config option
399                             * later.
400                             *
401                             */
402                Object[] primaryKeyObj = tracer.primaryKeyValues;
403                if (!repTable.isLocalServerWinner()) {
404                  for (int i = 0; i < primaryKeyObj.length; i++) {
405                    psForActualUpdateInTable.setObject(noOfChangedColumns + i + 1,primaryKeyObj[i]);
406                    psForUpdateInShadowTableForRemoteServer.setObject(3 + i,primaryKeyObj[i]);
407                  }
408                  Object lastSyncId = getLastUIDFromShadowTable();
409                  try {
410                    psForActualUpdateInTable.executeUpdate();
411                    loggingUpdateOperation(tableName, primaryColumnNames,primaryValueObjects, changedColumnNames,changedColumnValues, replicationType);
412                    writeUpdateOperationInTransactionLogFile(bw, tableName,primaryColumnNames, primaryValueObjects, changedColumnNames, changedColumnValues, replicationType, transactionLogType);
413                    updateCount++;
414                  }
415                  catch (SQLException ex) { // primary key constrant voilation
416                    if (!dbHandler.isPrimaryKeyException(ex)) {
417                      throw ex;
418                    }
419    
420                    Tracer trForRollback = new Tracer();
421                    trForRollback.primaryKeyValues = primaryKeyObj;
422                    primaryViolation(newPrimaryColumnValues, trForRollback);
423                    Object[] trPkValues = trForRollback.primaryKeyValues;
424                    // setting the rollbacked pk values if original pk values has also been rollbacked.
425                    for (int i = 0; i < trPkValues.length; i++) {
426                      psForActualUpdateInTable.setObject(noOfChangedColumns + i + 1,trPkValues[i]);
427                      psForUpdateInShadowTableForRemoteServer.setObject(3 + i,trPkValues[i]);
428                    }
429                    lastSyncId = getLastUIDFromShadowTable();
430                    psForActualUpdateInTable.executeUpdate();
431                  }
432    
433                  // update shadow table with remoteserverNAme FOR UODATE
434                  psForUpdateInShadowTableForRemoteServer.setObject(2, lastSyncId);
435                  psForUpdateInShadowTableForRemoteServer.executeUpdate(); // Status -B Updating record with remoteSErverNAme on shadowTable
436    
437                  for (int i = 0; i < newPrimaryColumnValues.length; i++) {
438                    psForUpdateInShadowTableForRemoteServer.setObject(i + 3,newPrimaryColumnValues[i]);
439                  }
440                  psForUpdateInShadowTableForRemoteServer.executeUpdate(); // Status- A Updating record with remoteSErverNAme on shadowTable
441                }
442                else {
443                  // case when both the records are updated on both the sides
444                  Object[] oldRow = tracer.oldRow;
445                  ResultSet trRS = tracer.rs;
446                  ArrayList localChangedColumnsName = new ArrayList();
447                  for (int i = 0; i < tableColumnNames.length; i++) {
448                    String columnName = tableColumnNames[i];
449                    Object newRecord = trRS.getObject(columnName);
450                    if (newRecord != null || oldRow[i] != null) {
451                      try {
452                        if (!newRecord.equals(oldRow[i])) {
453                          localChangedColumnsName.add(columnName);
454                        }
455                      }
456                      catch (NullPointerException ex1) {
457                        localChangedColumnsName.add(columnName);
458                      }
459                    }
460                    else {
461                      continue;
462                    }
463                  }
464                  ArrayList actualListOFColumns = new ArrayList();
465                  ArrayList actualListOFValues = new ArrayList();
466                  for (int i = 0; i < changedColumnNames.length; i++) {
467                    if (repTable.isIgnoredColumn(changedColumnNames[i]))
468                      continue;
469                    if (!localChangedColumnsName.contains(changedColumnNames[i])) {
470                      actualListOFColumns.add(changedColumnNames[i]);
471                      actualListOFValues.add(changedColumnValues[i]);
472                    }
473                  }
474                  if (actualListOFColumns.size() > 0) {
475                    Object lastSyncId = getLastUIDFromShadowTable();
476                    String query = repTable.getUpdatePreStmt(actualListOFColumns, primaryColumnNames);
477                    PreparedStatement pst = connection.prepareStatement(query);
478                    int index = actualListOFColumns.size();
479                    for (int i = 0; i < index; i++) {
480                      AbstractColumnObject columnObject = (AbstractColumnObject)columnObjectTreeMap.get(actualListOFColumns.get(i));
481                      //once setAutoCommitFlag is set to false,we shouldn't change it to true
482                      // by checking for other columns for that 'if' check is used
483                      if (setAutoCommitFlag) {
484                        setAutoCommitFlag = checkAutocommit(dbHandler, columnObject);
485                        connection.setAutoCommit(setAutoCommitFlag);
486                      }
487    
488                      //  Changes required to handle the case of update for clob and blob data type
489                      columnObject.setColumnObject(pst,(String) actualListOFValues.get(i), i + 1);
490                    }
491                    for (int j = 0; j < primaryKeyObj.length; j++) {
492                      pst.setObject(index + j + 1, primaryKeyObj[j]);
493                    }
494                    try {
495                      pst.executeUpdate();
496                    }
497                    catch (SQLException ex) {
498                      if (!dbHandler.isPrimaryKeyException(ex)) {
499                        throw ex;
500                      }
501                      // primary Constrant Violation
502                      primaryViolation(newPrimaryColumnValues, new Tracer()); // no case found till now for tracer
503                      try {
504                        pst.executeUpdate();
505                      }
506                      catch (SQLException ex10) {
507                        if (!dbHandler.isPrimaryKeyException(ex)) {
508                          throw ex;
509                        }
510    
511                        /**
512                         * In primary key voilation method we try to rollback
513                         * the record upto its original record.After rollback
514                         * operation we try to update the record. During update
515                         * operation if primary key voilation occure then we delete
516                         * the record in case of cyclic table.
517                         */
518    
519                        if (isCurrentTableCyclic) {
520                          deleteRecord(newPrimaryColumnValues);
521                          pst.executeUpdate();
522                        }
523                      }
524                    }
525                    finally {
526                      if (setAutoCommitFlag == false)
527                        setAutocomitTrueAndCommitRecord(connection);
528                    }
529    
530                    if (pst != null) {
531                      pst.close();
532                    }
533    // update in  shadow table not done for remoteServerName because of this case will be true only in case of LOCAL SERVER WINNER AND
534    // THIS RECORD SHOULD BE SENT TO REMOTE SERVER FOR UPDATE...
535                  }
536                }
537              }
538            }
539          }
540          else if (isCurrentTableCyclic) {
541    
542    // System.out.println("<----------------------SECOND PASS CURRENT TABLE FOUND CYCLIC --------------------->");
543            /**
544             * Code given below is executed in second pass for cyclic tables.
545             * Update all the columns with actual values that are set to null in first pass.
546             */
547            boolean isForeignKeycolumn = false;
548            int columnIndex = 0;
549            String updateQueryForSecondPass = makeQueryToUpdateTheRecordInSecodPass(changedColumnNames, primaryColumnNames);
550    //  System.out.println("^^^^^^^^^^^^^^^^^ UPDATE QUERY SECOND PASS ::" +updateQueryForSecondPass);
551            if (!updateQueryForSecondPass.equalsIgnoreCase("")) {
552              psForActualUpdateInTable = connection.prepareStatement(updateQueryForSecondPass);
553              for (int i = 0; i < noOfChangedColumns; i++) {
554                if (repTable.isIgnoredColumn(changedColumnNames[i]))
555                  continue;
556    
557                // Set value of foriegn key columns
558                if (isCurrentTableCyclic &&
559                    repTable.isForeignKeyColumn(changedColumnNames[i])) {
560                  AbstractColumnObject columnOject = ( (AbstractColumnObject)columnObjectTreeMap.get(changedColumnNames[i]));
561                  isForeignKeycolumn = true;
562                  //once setAutoCommitFlag is set to false,we shouldn't change it to true
563                  // by checking for other columns for that 'if' check is used
564                  if (setAutoCommitFlag) {
565                    setAutoCommitFlag = checkAutocommit(dbHandler, columnOject);
566                    connection.setAutoCommit(setAutoCommitFlag);
567                  }
568                  columnOject.setColumnObject(psForActualUpdateInTable,changedColElement[i], columnIndex + 1);
569                  columnIndex++;
570                }
571              }
572              // set the value of primary columns
573              for (int i = 0; i < primaryValueObjects.length; i++) {
574                psForActualUpdateInTable.setObject(columnIndex + 1,primaryValueObjects[i]);
575              }
576              // Update the records with actual values which are set to null in first pass
577              if (isForeignKeycolumn) {
578                Object lastSyncId = getLastUIDFromShadowTable();
579                psForActualUpdateInTable.execute();
580                // update the old record in shadow table with remoteServer STATUIS -B
581                // setting Object values for Update query for Shadow TABLW for REMOTE SERVER
582                psForUpdateInShadowTableForRemoteServer.setObject(2, lastSyncId);
583                for (int i = 0; i < newPrimaryColumnValues.length; i++) {
584                  psForUpdateInShadowTableForRemoteServer.setObject(i + 3,newPrimaryColumnValues[i]);
585                }
586                psForUpdateInShadowTableForRemoteServer.executeUpdate();
587              }
588            }
589          }
590        }
591        catch (SQLException ex2) {
592          if (!dbHandler.isPrimaryKeyException(ex2)) {
593            throw ex2;
594          }
595          /**
596           * This catch was added on 22 march 04 .
597           * There is Primary Key Violation occure in Scripttestcases 6 , 7 and 8
598           * All these are complex cases.So the Exception has been dumped for
599           * temporary solution as suggested.
600           */
601        }
602        finally {
603          if (setAutoCommitFlag == false) {
604            setAutocomitTrueAndCommitRecord(connection);
605          }
606    
607          if (psForActualUpdateInTable != null) {
608            try {
609              psForActualUpdateInTable.close();
610            }
611            catch (SQLException ex3) {
612              //sqleception must be ignored
613            }
614          }
615        }
616      }
617    
618      /**
619       * @param rst
620       * @return ResultSet containing only one record corresponding to commonId
621       * record may be of status 'A' or 'B'.
622       * @throws SQLException
623       */
624      private ResultSet getOtherCommonIdResultSet(ResultSet rst) throws SQLException {
625        if (psForOtherCommonId == null) {
626          psForOtherCommonId = makePSForOtherCommonId();
627        }
628    
629        // showCurrentRow(rst);
630    
631        Object commonId = rst.getObject(RepConstants.shadow_common_id2);
632        Object syncId = rst.getObject(RepConstants.shadow_sync_id1);
633            /* bjt - add .toString() to commonId and syncId to make postgres quote it and thus use the index */
634        psForOtherCommonId.setObject(1, commonId.toString());
635        psForOtherCommonId.setObject(2, syncId.toString());
636        for (int i = 0; i < primaryColumnNames.length; i++) {
637          psForOtherCommonId.setObject(i + 3,rst.getObject("REP_OLD_" +primaryColumnNames[i]));
638        }
639        return psForOtherCommonId.executeQuery();
640      }
641    
642      /**
643       * Handles the case when primary key voilation occurs due to synchronisation.
644       * Records may be rollbacked to their original status as before synchronization
645       * @param conflictingPrimaryKeyValues
646       * @param trForRollback - a tracer holds the information related to the records.
647       * @throws RepException
648       * @throws SQLException
649       */
650      private void primaryViolation(Object[] conflictingPrimaryKeyValues, Tracer trForRollback) throws RepException, SQLException {
651        Object lastShadowUidObj = getLastUIDFromShadowTable();
652        if ((lastShadowUidObj instanceof Integer)) { //for firebird
653          lastShadowUid = new Long( ( (Integer) lastShadowUidObj).longValue() + 1);
654        }
655        else  if ((lastShadowUidObj instanceof String)) { //for MySQL
656          lastShadowUid = new Long( ( Long.parseLong((String)lastShadowUidObj) + 1));
657        }
658        else {
659          lastShadowUid = new Long( ( (Long) getLastUIDFromShadowTable()).longValue() +1);
660        }
661    
662        Tracer tracer = new Tracer();
663        // trace the orignal record inserted/updated in main table for which Primary voilation has occured
664        traceOriginalRecord(conflictingPrimaryKeyValues, tracer, lastShadowUid);
665    
666        if (tracer.type == null) {
667          return;
668        }
669    
670        if (tracer.type.equals(RepConstants.insert_operation)) { // Insert Case
671    
672          if (preparedStatementForDelete == null) {
673            preparedStatementForDelete = makePreparedStatementForDeleteOnTable();
674          }
675          Object[] primaryValues = tracer.primaryKeyValues;
676          for (int i = 0; i < primaryValues.length; i++) {
677            preparedStatementForDelete.setObject(i + 1, primaryValues[i]);
678          }
679          if (isCurrentTableCyclic) {
680            setNullInColumnOfChildTableToUpdateRecord(primaryColumnNames,primaryValues);
681          }
682          // possibility of deleting the record which is to be updated afterwards
683          preparedStatementForDelete.executeUpdate();
684        }
685        else if (tracer.type.equals(RepConstants.update_operation)) { // Update Case
686          // special handlling if swapping has been done on primary keys and which due to which
687          // we get stuck in recurrsion .
688          boolean conflictingKeyAllreadyPresent =
689              checkForPresenceOfConflictingPkKeyinArray(voilationPkKeys,
690              conflictingPrimaryKeyValues);
691          if (!conflictingKeyAllreadyPresent) {
692            voilationPkKeys.add(conflictingPrimaryKeyValues);
693          }
694          else {
695            if (preparedStatementForDelete == null) {
696              preparedStatementForDelete = makePreparedStatementForDeleteOnTable();
697            }
698            Object[] primaryValuesForDelete = (Object[]) voilationPkKeys.get(
699                voilationPkKeys.size());
700            for (int i = 0; i < primaryValuesForDelete.length; i++) {
701              preparedStatementForDelete.setObject(i + 1, primaryValuesForDelete[i]);
702            }
703            // possibility of deleting the record which is to be updated afterwards
704            preparedStatementForDelete.executeUpdate();
705            Object lastIdForInsert = getLastUIDFromShadowTable();
706            return;
707          }
708    
709          ResultSet newRS = tracer.rs;
710    
711          if (updatePreparedStatement == null) {
712            updatePreparedStatement = makeUpdatePreparedStatement();
713    
714          }
715          Object[] primaryValues = tracer.primaryKeyValues;
716    
717          // checking for record to be updated / rollbacked if it is the one to be updated originally
718          // i.e after primary voilation has been done
719          boolean flag = checkForConflictingRecord(trForRollback.primaryKeyValues,conflictingPrimaryKeyValues);
720          if (flag) {
721            trForRollback.primaryKeyValues = primaryValues;
722    
723            // setting old values for the current row // updating current row to its prev version.
724          }
725          ArrayList[] listArray = setParametersInUpdatePS(conflictingPrimaryKeyValues, newRS);
726          ArrayList list = listArray[0];
727          ArrayList nullColumnObj = listArray[1];
728    
729          try {
730            updatePreparedStatement.executeUpdate();
731          }
732          catch (SQLException ex) {
733            /**
734             * Use foriegn key error code for deleting the
735             * conflicting record.
736             */
737            if (ex.getErrorCode() == 547) {
738    //System.out.println("   primary key voilation :: ");
739              deleteRecord(conflictingPrimaryKeyValues);
740    //System.out.println(" record deleted successfully ");
741              return;
742            }
743            if (!dbHandler.isPrimaryKeyException(ex)) {
744              throw ex;
745            }
746            primaryViolation(primaryValues, trForRollback);
747            int nullCnt = 0;
748            for (int i = 0; i < list.size(); i++) {
749              Object obj = list.get(i);
750              if (obj != null) {
751                updatePreparedStatement.setObject(i + 1, obj);
752              }
753              else {
754                ( (AbstractColumnObject) nullColumnObj.get(nullCnt)).setColumnObject(updatePreparedStatement, xmlElement_NULL, i + 1);
755                nullCnt++;
756              }
757            }
758            //Updating Values to prev values
759            int countUpdatedR = updatePreparedStatement.executeUpdate();
760          }
761          //we will not  update the shadown table entries with remote server name in this case
762        }
763      }
764    
765      private ArrayList[] setParametersInUpdatePS(Object[] conflictingPrimaryKeyValues, ResultSet newRS) throws SQLException {
766        ArrayList list = new ArrayList();
767        ArrayList nullColumnObj = new ArrayList();
768        int cnt = tableColumnNames.length;
769        int k = 0;
770        for (int i = 0; i < cnt; i++) {
771          if (repTable.isIgnoredColumn(tableColumnNames[i])) {
772            continue;
773          }
774          Object obj = newRS.getObject(tableColumnNames[i]);
775          if (obj == null) {
776            AbstractColumnObject columnObj = ( (AbstractColumnObject)columnObjectTreeMap.get(tableColumnNames[i]));
777            columnObj.setColumnObject(updatePreparedStatement, xmlElement_NULL,k + 1);
778            list.add(obj);
779            nullColumnObj.add(columnObj);
780            k++;
781            continue;
782          }
783          updatePreparedStatement.setObject(k + 1, obj);
784          k++;
785          list.add(obj);
786        }
787    
788        for (int j = 0; j < conflictingPrimaryKeyValues.length; j++) {
789          Object obj = conflictingPrimaryKeyValues[j];
790          updatePreparedStatement.setObject(cnt + j + 1, obj);
791          list.add(obj);
792        }
793        return new ArrayList[] {list, nullColumnObj};
794      }
795    
796      /**
797       * Get actual values from ColumnObjects for corresponding to primary key.
798       * @param primaryKeyValues primary key values passed through XML file.
799       * @return Object[] of primary keys
800       * @throws SQLException
801       */
802      private Object[] getObjectArrayForPrimaryKey(String[] primaryKeyValues) throws
803          SQLException {
804        Object[] pkValues = new Object[primaryKeyColumnsObject.length];
805        for (int i = 0; i < primaryKeyColumnsObject.length; i++) {
806          pkValues[i] = primaryKeyColumnsObject[i].getObject(primaryKeyValues[i]);
807        }
808        return pkValues;
809      }
810    
811      /**
812       * Get actual values from ColumnObjects for corresponding to whole row passed in XML file.
813       * @param columnValues
814       * @param columnObj
815       * @return
816       * @throws SQLException
817       */
818      private Object[] getObjectArrayForTable(String[] columnValues,AbstractColumnObject[] columnObj) throws SQLException {
819        Object[] values = new Object[columnValues.length];
820        ArrayList nullColumnObj = new ArrayList();
821    
822        for (int i = 0; i < columnValues.length; i++) {
823          if (repTable.isIgnoredColumn(tableColumnNames[i])) {
824              continue;
825            }
826          AbstractColumnObject colObj = columnObj[i];
827          values[i] = colObj.getObject(columnValues[i]);
828          if (values[i] == null) {
829            nullColumnObj.add(colObj);
830          }
831        }
832        return new Object[] {values, nullColumnObj};
833      }
834    
835      /**
836       * tarce the record present in shadow table for the primary key passed and having syncId less than UID passed.
837       * @param primaryKeyValues
838       * @param tracer
839       * @param Uid
840       * @return
841       * @throws RepException, SQLException
842       */
843      private boolean traceOriginalRecord(Object[] primaryKeyValues, Tracer tracer,Object Uid) throws RepException, SQLException {
844        if (preparedStatementForLastRecord == null) {
845          preparedStatementForLastRecord = makePreparedStatementForLastRecord();
846        }
847        preparedStatementForLastRecord.setObject(1, Uid);
848        for (int i = 0; i < primaryKeyValues.length; i++) {
849          preparedStatementForLastRecord.setObject(i + 2, primaryKeyValues[i]);
850        }
851        ResultSet lastRow = preparedStatementForLastRecord.executeQuery();
852        lastRow = preparedStatementForLastRecord.executeQuery();
853        if (!lastRow.next()) {
854          lastRow.close();
855          return false;
856        }
857    
858        // finding operation performed for current record
859        String operation = lastRow.getString(RepConstants.shadow_operation3);
860        if (operation.equals(RepConstants.insert_operation)) {
861          tracer.type = RepConstants.insert_operation;
862          tracer.rs = lastRow;
863          tracer.primaryKeyValues = primaryKeyValues;
864          return true;
865        }
866        else {
867          // Operation should be UPDATE
868          Object commonId = lastRow.getObject(RepConstants.shadow_common_id2);
869          if (preparedStatementForCommonId == null) {
870            preparedStatementForCommonId = makePreparedStatementForCommonId_WithBeforeUpdate_Status();
871          }
872          preparedStatementForCommonId.setObject(1, commonId);
873          for (int i = 0; i < primaryColumnNames.length; i++) {
874            preparedStatementForCommonId.setObject(i + 2,lastRow.getObject("REP_OLD_" +primaryColumnNames[i]));
875          }
876          ResultSet rs = preparedStatementForCommonId.executeQuery();
877    
878          if (!rs.next()) { // other reocrd for common id and status before update not found 'B'
879            rs.close();
880            throw new RepException("REP051", new Object[] {commonId, shadowTable});
881          }
882    
883          Object[] primaryColValues = new Object[primaryColumnNames.length];
884          for (int j = 0; j < primaryColumnNames.length; j++) {
885            primaryColValues[j] = rs.getObject(primaryColumnNames[j]);
886    // System.out.println(" primaryColValues[j]  :: " + primaryColValues[j]);
887          }
888          Object UID = rs.getObject(RepConstants.shadow_sync_id1);
889          // finding prev. record
890          boolean gotRow = traceOriginalRecord(primaryColValues, tracer, UID);
891          if (gotRow == false) {
892            tracer.rs = rs;
893            tracer.type = RepConstants.update_operation;
894            tracer.primaryKeyValues = primaryColValues;
895          }
896          else {
897            rs.close();
898          }
899          return true;
900        }
901    //        throw new Exception("ERROR ERROR < NOT POSSIBLE");
902      }
903    
904      /**
905       * fetches the last records from the shadpw tbale correspondinf the the
906       * primary key passed i.e the original status of the record after
907       * updataion/Insertion/Deletion
908       * @param primaryColValues
909       * @param UID
910       * @param tracer
911       * @return
912       * @throws RepException
913       * @throws SQLException
914       */
915      private boolean getLastRecord(Object[] primaryColValues, Object UID,Tracer tracer) throws RepException, SQLException {
916        int noOfPrimaryColumns = primaryColValues.length;
917        for (int i = 0; i < noOfPrimaryColumns; i++) {
918          psForInitialRecordInShadowTable.setObject(i + 1, primaryColValues[i]);
919        }
920        psForInitialRecordInShadowTable.setObject(noOfPrimaryColumns + 1, UID);
921        psForInitialRecordInShadowTable.setObject(noOfPrimaryColumns + 2,remoteServerName);
922        ResultSet rs = psForInitialRecordInShadowTable.executeQuery();
923    
924        if (!rs.next()) {
925          rs.close();
926          tracer.recordFound = false;
927          return false;
928        }
929        else {
930          String operation = rs.getString(RepConstants.shadow_operation3);
931          if (operation.equals(RepConstants.delete_operation)) { // Delete Operation
932            tracer.recordFound = true;
933            tracer.type = RepConstants.delete_operation;
934            return true;
935          }
936          else {
937            tracer.setOldRow(rs);
938            ResultSet resultSet = getOtherCommonIdResultSet(rs);
939            boolean updatedREcord = resultSet.next();
940            if (!updatedREcord) { // other common record not found in shadow table, may be insert case
941              throw new RepException("REP051",new Object[] {rs.getObject(RepConstants.shadow_common_id2), shadowTable});
942            }
943            primaryColValues = new Object[noOfPrimaryColumns];
944            for (int i = 0; i < noOfPrimaryColumns; i++) {
945              primaryColValues[i] = resultSet.getObject(primaryColumnNames[i]);
946            }
947            Object currentUId = resultSet.getObject(RepConstants.shadow_sync_id1);
948    
949    
950            boolean nextFound = getLastRecord(primaryColValues, currentUId, tracer);
951            if (nextFound == false) {
952              tracer.rs = resultSet;
953              tracer.recordFound = true;
954              tracer.type = RepConstants.update_operation;
955              tracer.primaryKeyValues = primaryColValues;
956            }
957            else {
958              resultSet.close();
959    
960            }
961            return true;
962          }
963        }
964      }
965    
966      private PreparedStatement makePSForInitialRecordInShadowTable() throws SQLException {
967        StringBuffer query = new StringBuffer();
968        query.append("SELECT * FROM ");
969        query.append(shadowTable);
970        query.append(" WHERE ");
971        for (int i = 0; i < primaryColumnNames.length; i++) {
972          if (i != 0) {
973            query.append(" AND ");
974          }
975          query.append(primaryColumnNames[i]);
976          query.append("= ? ");
977        }
978        query.append(" AND ");
979        query.append(RepConstants.shadow_sync_id1);
980        query.append(" > ? "); // coincedered Id
981        query.append(" AND ");
982        query.append(RepConstants.shadow_serverName_n);
983        query.append(" != ? ORDER BY ").append(RepConstants.shadow_sync_id1); // remote servername
984        return connection.prepareStatement(query.toString());
985      }
986    
987      private PreparedStatement makePSForActualUpdateInTable() throws SQLException {
988        StringBuffer query = new StringBuffer();
989        query.append("UPDATE ");
990        query.append(tableName);
991        query.append(" SET ");
992        for (int i = 0; i < changedColumnNames.length; i++) {
993          if (repTable.isIgnoredColumn(changedColumnNames[i])) {
994            continue;
995          }
996          query.append(" , " + changedColumnNames[i]);
997          query.append(" = ? ");
998        }
999        int indexOfFirstComma = query.indexOf(",");
1000        if (indexOfFirstComma != -1) {
1001          query.deleteCharAt(indexOfFirstComma);
1002        }
1003    
1004        query.append("WHERE ");
1005        for (int i = 0; i < primaryColumnNames.length; i++) {
1006          if (i != 0) {
1007            query.append(" AND ");
1008          }
1009          query.append(primaryColumnNames[i]);
1010          query.append(" = ? ");
1011        }
1012        return connection.prepareStatement(query.toString());
1013      }
1014    
1015      private PreparedStatement makePreparedStatementForLastRecord() throws
1016          SQLException {
1017        StringBuffer query = new StringBuffer();
1018        query.append(" SELECT * FROM  ");
1019        query.append(shadowTable);
1020        query.append("  WHERE  ");
1021        query.append(RepConstants.shadow_sync_id1);
1022        // and not >= because this will include the last record in traceOriginal Record , which will give wrong result
1023        query.append(" > ");
1024        //query.append(conisderedId); /* bjt - change to lastSyncId */
1025            if (Integer.parseInt(conisderedId.toString()) > Integer.parseInt(lastSyncId.toString())) {
1026            query.append(conisderedId);
1027            } else {
1028            query.append(lastSyncId);
1029            }
1030        query.append(" AND ");
1031        query.append(RepConstants.shadow_sync_id1);
1032        query.append(" < ? ");
1033        for (int i = 0; i < primaryColumnNames.length; i++) {
1034          query.append("  AND  ");
1035          query.append(primaryColumnNames[i]);
1036          query.append(" = ? ");
1037        }
1038        query.append(" ORDER BY  ");
1039        query.append(RepConstants.shadow_sync_id1);
1040        query.append(" DESC ");
1041        return connection.prepareStatement(query.toString());
1042      }
1043    
1044      private PreparedStatement makePreparedStatementForCommonId_WithBeforeUpdate_Status() throws SQLException {
1045        StringBuffer query = new StringBuffer();
1046        query.append(" SELECT * FROM  ");
1047        query.append(shadowTable);
1048        query.append(" WHERE   ");
1049        query.append(RepConstants.shadow_sync_id1);
1050        query.append(" >= ");
1051        //query.append(conisderedId); /* bjt - change to lastSyncId */
1052            if (Integer.parseInt(conisderedId.toString()) > Integer.parseInt(lastSyncId.toString())) {
1053            query.append(conisderedId);
1054            } else {
1055            query.append(lastSyncId);
1056            }
1057        query.append(" AND ");
1058        query.append(RepConstants.shadow_common_id2);
1059        query.append(" = ? ");
1060    
1061        // for matching oldPrimary keys
1062        for (int i = 0; i < primaryColumnNames.length; i++) {
1063          query.append(" AND ");
1064          query.append("REP_OLD_" + primaryColumnNames[i]);
1065          query.append(" = ");
1066          query.append(" ? ");
1067        }
1068    
1069        query.append(" AND ");
1070        query.append(RepConstants.shadow_status4);
1071        query.append(" = '");
1072        query.append(RepConstants.beforeUpdate);
1073        query.append("'");
1074        return connection.prepareStatement(query.toString());
1075      }
1076    
1077      private PreparedStatement makePreparedStatementForDeleteOnTable() throws
1078          SQLException {
1079        StringBuffer query = new StringBuffer();
1080        query.append(" DELETE FROM ");
1081        query.append(tableName);
1082        query.append(" WHERE ");
1083        for (int i = 0; i < primaryColumnNames.length; i++) {
1084          if (i != 0) {
1085            query.append(" AND ");
1086          }
1087          query.append(primaryColumnNames[i]);
1088          query.append("= ?");
1089        }
1090        return connection.prepareStatement(query.toString());
1091      }
1092    
1093      private PreparedStatement makeUpdatePreparedStatement() throws SQLException {
1094        StringBuffer query = new StringBuffer();
1095        query.append("UPDATE  ");
1096        query.append(tableName);
1097        query.append(" SET ");
1098    
1099        for (int i = 0; i < tableColumnNames.length; i++) {
1100          if (repTable.isIgnoredColumn(tableColumnNames[i])) {
1101            continue;
1102          }
1103          query.append(" , " + tableColumnNames[i]);
1104          query.append("= ?");
1105        }
1106        int indexOfFirstComma = query.indexOf(",");
1107        if (indexOfFirstComma != -1) {
1108          query.deleteCharAt(indexOfFirstComma);
1109        }
1110        query.append(" WHERE ");
1111        for (int i = 0; i < primaryColumnNames.length; i++) {
1112          if (i != 0) {
1113            query.append(" AND ");
1114          }
1115          query.append(primaryColumnNames[i]);
1116          query.append("= ?");
1117        }
1118    //System.out.println("PK violation makeUpdatePreparedStatement line 1063:::" +query.toString());
1119        return connection.prepareStatement(query.toString());
1120      }
1121    
1122      private PreparedStatement makePSForOtherCommonId() throws SQLException {
1123        StringBuffer query = new StringBuffer();
1124        query.append("SELECT * FROM  ");
1125        query.append(shadowTable);
1126        query.append(" WHERE ");
1127        query.append(RepConstants.shadow_common_id2);
1128        query.append(" = ?  ");
1129        query.append(" and ");
1130        query.append(RepConstants.shadow_sync_id1);
1131        query.append(" != ? ");
1132    
1133    // for matching oldPrimary keys
1134        for (int i = 0; i < primaryColumnNames.length; i++) {
1135          query.append(" and ");
1136          query.append("REP_OLD_" + primaryColumnNames[i]);
1137          query.append(" = ");
1138          query.append(" ? ");
1139        }
1140        return connection.prepareStatement(query.toString());
1141      }
1142    
1143      /**
1144       * returns last synchronization id from shadopw table.
1145       * @return
1146       * @throws SQLException
1147       */
1148      private Object getLastUIDFromShadowTable() throws SQLException {
1149        StringBuffer query = new StringBuffer();
1150        ResultSet rs = null;
1151        try {
1152          query.append(" SELECT ").append(RepConstants.shadow_sync_id1).
1153                    append(" FROM ").append(shadowTable).
1154                            append(" order by ").append(RepConstants.shadow_sync_id1).
1155                            append(" desc limit 1");
1156          rs = connection.createStatement().executeQuery(query.toString());
1157          boolean flag = rs.next();
1158              Object lastId = new Long(0);
1159              if (flag) lastId = rs.getObject(1);
1160    //System.out.println("Operation update 1153 getLastUIDFromShadowTable  lastId  CLASS : "+lastId);
1161          if (lastId instanceof BigDecimal) {
1162            lastId = new Long( ( (BigDecimal) lastId).longValue());
1163          }
1164          else  if (lastId instanceof Double) {
1165            lastId = new Long( ( (Double) lastId).longValue());
1166          }
1167          else  if (lastId instanceof Long) {
1168            lastId = new Long( ( (Long) lastId).longValue());
1169          }
1170          else  if (lastId instanceof Integer) {
1171           lastId = new Long( ( (Integer) lastId).longValue());
1172         }
1173         else  if (lastId instanceof String) {
1174           lastId = new Long( ( Long.parseLong((String)lastId)));
1175         }
1176    
1177          return flag ? (lastId == null ? new Long(0) : lastId) : new Long(0);
1178        }
1179        finally {
1180          Statement st = rs.getStatement();
1181          rs.close();
1182          st.close();
1183        }
1184      }
1185    
1186      private PreparedStatement makePSForUpdateInShadowTableForRemoteServer() throws SQLException {
1187        StringBuffer query = new StringBuffer();
1188        query.append(" UPDATE ").append(shadowTable).append(" SET  ").append(
1189            RepConstants.shadow_serverName_n)
1190            .append(" = ? ").append(" WHERE  ").append(RepConstants.shadow_sync_id1).
1191            append("> ?");
1192        for (int i = 0; i < primaryColumnNames.length; i++) {
1193          query.append(" AND ");
1194          query.append(primaryColumnNames[i]);
1195          query.append(" = ?");
1196        }
1197        return connection.prepareStatement(query.toString());
1198      }
1199    
1200      /**
1201       * used for debugging purpose to show a resultSet
1202       * @param rs
1203       * @throws SQLException
1204       */
1205      /*  private void showCurrentRow(ResultSet rs) throws SQLException {
1206          ResultSetMetaData rsmd = rs.getMetaData();
1207          int count = rsmd.getColumnCount();
1208          System.out.print("[ ");
1209          for (int i = 1; i <= count; i++) {
1210            if (i != 1)
1211              System.out.print(" , ");
1212            System.out.print(rs.getObject(i));
1213          }
1214          System.out.println(" ] ");
1215          System.out.println("");
1216        }
1217       */
1218      /**
1219       * searches for the conflicting Key in search key values.
1220       * @param searchKeyValues
1221       * @param conflictingPk
1222       * @return
1223       */
1224      private boolean checkForConflictingRecord(Object[] searchKeyValues, Object[] conflictingPk) {
1225        try {
1226          for (int i = 0; i < searchKeyValues.length; i++) {
1227            if (searchKeyValues[i].equals(conflictingPk[i])) {
1228              continue;
1229            }
1230            else {
1231              return false;
1232            }
1233          }
1234          return true;
1235        }
1236        catch (NullPointerException ex) {
1237          return false;
1238        }
1239      }
1240    
1241      private PreparedStatement makePsForUpdateChangedColumns_ExceptPk(ArrayList exceptPk_ChangedColumnNames) throws SQLException {
1242        StringBuffer query = new StringBuffer();
1243        query.append("UPDATE ");
1244        query.append(tableName);
1245        query.append(" SET ");
1246        for (int i = 0; i < exceptPk_ChangedColumnNames.size(); i++) {
1247          if (i != 0) {
1248            query.append(" , ");
1249          }
1250          query.append(exceptPk_ChangedColumnNames.get(i));
1251          query.append(" = ? ");
1252        }
1253        query.append(" WHERE ");
1254        for (int i = 0; i < primaryColumnNames.length; i++) {
1255          if (i != 0) {
1256            query.append(" AND ");
1257          }
1258          query.append(primaryColumnNames[i]);
1259          query.append(" = ? ");
1260        }
1261        return connection.prepareStatement(query.toString());
1262      }
1263    
1264      private void updateChangedColumnsAtLocalExcept_Pk(Object[]
1265          changedColumnObject, int noOfPrimaryCol, Object[] primaryValueObjects) throws SQLException {
1266        if (changedColumnNames.length > 0) {
1267          ArrayList exceptPk_ChangedColumnNames = new ArrayList();
1268          ArrayList exceptPk_ChangedColumnValues = new ArrayList();
1269          boolean isPk = false;
1270          // getting changes column names and values except for primary key
1271          for (int i = 0; i < changedColumnNames.length; i++) {
1272            for (int j = 0; j < primaryColumnNames.length; j++) {
1273              isPk = false;
1274              if (changedColumnNames[i].equalsIgnoreCase(primaryColumnNames[j])) {
1275                isPk = true;
1276                continue;
1277              }
1278            }
1279            if (!isPk) {
1280              exceptPk_ChangedColumnNames.add(changedColumnNames[i]);
1281              exceptPk_ChangedColumnValues.add(changedColumnObject[i]);
1282            }
1283          }
1284          // making prepared statement for updating values at except for primary key
1285          if (exceptPk_ChangedColumnNames.size() > 0) {
1286            PreparedStatement ps = makePsForUpdateChangedColumns_ExceptPk(
1287                exceptPk_ChangedColumnNames);
1288            for (int i = 0; i < exceptPk_ChangedColumnValues.size(); i++) {
1289              ps.setObject(i + 1, exceptPk_ChangedColumnValues.get(i));
1290            }
1291            for (int i = 0; i < noOfPrimaryCol; i++) {
1292              ps.setObject(exceptPk_ChangedColumnValues.size() + i + 1, primaryValueObjects[i]);
1293            }
1294            ps.executeUpdate();
1295          }
1296        }
1297      }
1298    
1299      private boolean checkForPresenceOfConflictingPkKeyinArray(ArrayList
1300          voilationPkKeys, Object[] pkValues) {
1301        for (int i = 0; i < voilationPkKeys.size(); i++) {
1302          Object[] values = (Object[]) voilationPkKeys.get(i);
1303          boolean flag = true;
1304          for (int j = 0; j < values.length; j++) {
1305            if (!values[j].equals(pkValues[j])) {
1306              flag = false;
1307              break;
1308            }
1309          }
1310          if (flag) {
1311            return true;
1312          }
1313        }
1314        return false;
1315      }
1316    
1317      /*
1318       * used for debugging. shows values stored in a resultSet.
1319       * @param rs
1320       * @throws SQLException
1321       */
1322      /* public static void showResultSet(ResultSet rs) throws SQLException {
1323         ResultSetMetaData metaData = rs.getMetaData();
1324         int columnCount = metaData.getColumnCount();
1325         Object[] displayColumn = new Object[columnCount];
1326         for (int i = 1; i <= columnCount; i++)
1327           displayColumn[i - 1] = metaData.getColumnName(i);
1328         System.out.println(Arrays.asList(displayColumn));
1329         while (rs.next()) {
1330           Object[] columnValues = new Object[columnCount];
1331           for (int i = 1; i <= columnCount; i++)
1332             columnValues[i - 1] = rs.getObject(i);
1333           System.out.println(Arrays.asList(columnValues));
1334         }
1335       }*/
1336    
1337      public void closeAllStatments() {
1338        if (psForInitialRecordInShadowTable != null) {
1339          try {
1340            psForInitialRecordInShadowTable.close();
1341          }
1342          catch (SQLException ex) {
1343          }
1344        }
1345        if (psForActualUpdateInTable != null) {
1346          try {
1347            psForActualUpdateInTable.close();
1348          }
1349          catch (SQLException ex1) {
1350          }
1351        }
1352        if (preparedStatementForLastRecord != null) {
1353          try {
1354            preparedStatementForLastRecord.close();
1355          }
1356          catch (SQLException ex2) {
1357          }
1358        }
1359        if (preparedStatementForCommonId != null) {
1360          try {
1361            preparedStatementForCommonId.close();
1362          }
1363          catch (SQLException ex3) {
1364          }
1365        }
1366    
1367        if (preparedStatementForDelete != null) {
1368          try {
1369            preparedStatementForDelete.close();
1370          }
1371          catch (SQLException ex4) {
1372          }
1373        }
1374        if (updatePreparedStatement != null) {
1375          try {
1376            updatePreparedStatement.close();
1377          }
1378          catch (SQLException ex5) {
1379          }
1380        }
1381        if (psForOtherCommonId != null) {
1382          try {
1383            psForOtherCommonId.close();
1384          }
1385          catch (SQLException ex6) {
1386          }
1387        }
1388    
1389        if (psForInsertIntoActualTable != null) {
1390    
1391          try {
1392            psForInsertIntoActualTable.close();
1393          }
1394          catch (SQLException ex7) {
1395          }
1396        }
1397        if (psForUpdateInShadowTableForRemoteServer != null) {
1398          try {
1399            psForUpdateInShadowTableForRemoteServer.close();
1400          }
1401          catch (SQLException ex8) {
1402          }
1403        }
1404      }
1405    
1406      /**
1407       * Make a update query to update all the columns that refering
1408       * to other columns and and set null during first pass.If referencing
1409       * columns are not found then blank string return by the method.
1410       * @param changesColumns String[]
1411       * @param primaryColumns String[]
1412       * @return String
1413       */
1414      private String makeQueryToUpdateTheRecordInSecodPass(String[] changesColumns,String[] primaryColumns) {
1415        StringBuffer updateQuerySecondPass = new StringBuffer();
1416        updateQuerySecondPass.append("UPDATE ").append(tableName).append(" SET ");
1417        int size = changesColumns.length;
1418        boolean flag = false;
1419        for (int i = 0; i < size; i++) {
1420          if (repTable.isForeignKeyColumn(changedColumnNames[i])) {
1421            if (flag)
1422              updateQuerySecondPass.append(",");
1423            flag = true;
1424            updateQuerySecondPass.append(changedColumnNames[i]).append("= ? ");
1425          }
1426        }
1427        updateQuerySecondPass.append(" WHERE  ");
1428        for (int i = 0; i < primaryColumns.length; i++) {
1429          if (i != 0) {
1430            updateQuerySecondPass.append(" and ");
1431          }
1432          updateQuerySecondPass.append(primaryColumns[i]).append(" = ?");
1433        }
1434        log.debug(updateQuerySecondPass.toString());
1435        return flag ? updateQuerySecondPass.toString() : "";
1436      }
1437    
1438      /**
1439       * Set null in all column of child table that refering the
1440       * column of parent table for updated the record of parent
1441       * table.
1442       * @param parentPkCols String[]
1443       * @param parentPkColValue0 Object[]
1444       * @throws RepException
1445       * @throws SQLException
1446       */
1447      private void setNullInColumnOfChildTableToUpdateRecord(String[] parentPkCols,
1448          Object[] parentPkColValue0) throws RepException, SQLException {
1449        Statement stmt = null;
1450        String[] childFkColumns = null, parentReferedCols = null, parentPkColValue = null;
1451        Object[] object = null;
1452        Object obj = null;
1453        ArrayList childTableList = mdi.getChildTables(tableName);
1454        String childTableName = null;
1455        parentPkColValue = new String[parentPkColValue0.length];
1456        for (int j = 0; j < parentPkColValue0.length; j++) {
1457          obj = parentPkColValue0[j];
1458          parentPkColValue[j] = obj.toString();
1459        }
1460        for (int i = 0; i < childTableList.size(); i++) {
1461          childTableName = (String) childTableList.get(i);
1462          object = mdi.getImportedColsOfChildTable(tableName, childTableName);
1463          childFkColumns = (String[]) object[0];
1464          parentReferedCols = (String[]) object[1];
1465          updatePreparedStatementSetNull = makeupdateQueryToSetNull(childTableName,
1466              childFkColumns, parentReferedCols, parentPkCols, parentPkColValue);
1467          Object lastSynId = getLastSyncId(dbHandler.getShadowTableName(childTableName)); /* bjt - get rid of this */
1468          updatePreparedStatementSetNull.execute();
1469          String updateRemoteServerNameQuery =
1470              updateRemoteServerNameWhenSettingNullInChildTable(childTableName,
1471              childFkColumns, lastSynId, parentReferedCols, parentPkCols,parentPkColValue);
1472          stmt = connection.createStatement();
1473          int howMayServerNameupdated = stmt.executeUpdate(updateRemoteServerNameQuery);
1474          if (stmt != null) {
1475            stmt.close();
1476          }
1477        }
1478      }
1479    
1480      private PreparedStatement makeupdateQueryToSetNull(String tableName,
1481          String columns[], String[] parentReferedColumns, String[] parentPkCols, String[] parentPkColValue) throws SQLException {
1482        StringBuffer sb = new StringBuffer();
1483        sb.append("UPDATE ")
1484            .append(tableName)
1485            .append(" SET ");
1486        for (int i = 0; i < columns.length; i++) {
1487          if (i != 0) {
1488            sb.append(" , ");
1489          }
1490          sb.append(columns[i] + " = null")
1491              .append(" where  " + columns[i] + " = " +
1492                      getParentReferedColValue(parentPkCols, parentPkColValue,parentReferedColumns[i]));
1493        }
1494        return connection.prepareStatement(sb.toString());
1495      }
1496    
1497      /**
1498       * Get value of a column that is refer by other table
1499       * for creating a update query for setting null in child
1500       * tables.
1501       * @param primaryColsName String[]
1502       * @param primaryColsValues String[]
1503       * @param referdColName String
1504       * @throws SQLException
1505       * @return Object
1506       */
1507      private Object getParentReferedColValue(String[] primaryColsName, String[] primaryColsValues, String referdColName) throws SQLException {
1508        Statement stmt = connection.createStatement();
1509        StringBuffer sb = new StringBuffer();
1510        sb.append("SELECT ").append(referdColName)
1511            .append(" FROM " + tableName)
1512            .append(" where ");
1513        for (int i = 0; i < primaryColsName.length; i++) {
1514          if (i != 0) {
1515            sb.append(" , ");
1516          }
1517          sb.append(primaryColsName[i] + " = " + primaryColsValues[i]);
1518        }
1519        ResultSet rs = stmt.executeQuery(sb.toString());
1520        rs.next();
1521        return rs.getObject(1);
1522      }
1523    
1524      private String updateRemoteServerNameWhenSettingNullInChildTable(String
1525          childTableName, String[] childColumnName, Object lastSyncID,
1526          String[] parentReferedColumns, String[] parentPkCols,
1527          String[] parentPkColValue) throws SQLException {
1528        StringBuffer sb = new StringBuffer();
1529        String tableName = dbHandler.getShadowTableName(childTableName);
1530        sb.append(" UPDATE " + tableName)
1531            .append(" SET " + RepConstants.shadow_serverName_n + " = '")
1532            .append(remoteServerName + "'")
1533            .append(" WHERE ")
1534            .append(RepConstants.shadow_common_id2)
1535            .append(" = ")
1536            .append("( ")
1537            .append("SELECT ").append(RepConstants.shadow_common_id2).append(
1538            " FROM " + tableName)
1539            .append(" WHERE ")
1540            .append(RepConstants.shadow_sync_id1)
1541            .append(" > ")
1542            .append("" + lastSyncID + " AND ")
1543            .append(RepConstants.shadow_status4)
1544            .append(" = 'B' AND ");
1545        for (int i = 0; i < childColumnName.length; i++) {
1546          if (i != 0) {
1547            sb.append(" , ");
1548          }
1549          sb.append(childColumnName[i] + " = " +
1550                    getParentReferedColValue(parentPkCols, parentPkColValue,parentReferedColumns[i]));
1551        }
1552        sb.append(") ");
1553        return sb.toString();
1554      }
1555    
1556      /**
1557       * Get last Synchronization ID from shadow table.It
1558       * is used to used to get the original record.
1559       * @param shadowTableName String
1560       * @throws SQLException
1561       * @return Object
1562       */
1563      private Object getLastSyncId(String shadowTableName) throws SQLException {
1564        Statement statement = null;
1565        ResultSet rs = null;
1566        boolean flag = false;
1567        Object lastId = null;
1568        try {
1569          StringBuffer query = new StringBuffer();
1570          query.append("SELECT ").append(RepConstants.shadow_sync_id1).
1571                    append(" FROM ").append(shadowTableName).
1572                            append(" order by ").append(RepConstants.shadow_sync_id1).
1573                            append(" desc limit 1");
1574          statement = connection.createStatement();
1575          rs = statement.executeQuery(query.toString());
1576          flag = rs.next();
1577          lastId = rs.getObject(1);
1578        }
1579        finally {
1580          if (rs != null)
1581            rs.close();
1582          if (statement != null)
1583            statement.close();
1584        }
1585        return flag ? (lastId == null ? new Long(0) : lastId) : new Long(0);
1586      }
1587    
1588      /**
1589       * Delete record in case of cyclic table. First we try to rollback the record upto
1590       * original record.After that updated statement is executed.During execution of
1591       * statement if primary key voilation occurs then record is deleted to remove
1592       * the conflict.
1593       * @param primaryValues Object[]
1594       * @throws SQLException
1595       */
1596      private void deleteRecord(Object[] primaryValues) throws SQLException, RepException {
1597        if (preparedStatementForDelete == null) {
1598          preparedStatementForDelete = makePreparedStatementForDeleteOnTable();
1599        }
1600        for (int i = 0; i < primaryValues.length; i++) {
1601          preparedStatementForDelete.setObject(i + 1, primaryValues[i]);
1602        }
1603        // possibility of deleting the record which is to be updated afterwards
1604        setNullInColumnOfChildTableToUpdateRecord(primaryColumnNames, primaryValues);
1605        preparedStatementForDelete.executeUpdate();
1606      }
1607    
1608    }