001: package com.xoetrope.service;
002:
003: import java.io.StringWriter;
004: import java.sql.ResultSet;
005: import java.sql.ResultSetMetaData;
006: import java.sql.SQLException;
007: import java.sql.Timestamp;
008: import java.sql.Types;
009: import java.util.Hashtable;
010:
011: import net.n3.nanoxml.IXMLElement;
012: import net.n3.nanoxml.XMLElement;
013: import net.n3.nanoxml.XMLWriter;
014: import net.xoetrope.debug.DebugLogger;
015: import net.xoetrope.optional.data.sql.DataConnection;
016: import net.xoetrope.optional.service.ServiceProxy;
017: import net.xoetrope.optional.service.ServiceProxyException;
018: import com.xoetrope.carousel.build.BuildProperties;
019: import java.io.StringReader;
020: import java.sql.PreparedStatement;
021: import java.sql.Time;
022: import java.util.Enumeration;
023: import java.util.Vector;
024: import net.xoetrope.optional.service.ServiceContext;
025: import net.xoetrope.xml.XmlElement;
026: import net.xoetrope.xml.nanoxml.NanoXmlParser;
027: import net.xoetrope.xui.XProject;
028: import net.xoetrope.xui.XProjectManager;
029:
030: /**
031: * A service proxy to support replication of data to a client side database.
032: * This object must be setup as an application object if used in a servlet
033: * context. If a call for more data is made and the context of a previous
034: * request cannot be found then an exception will be thrown.
035: *
036: * <p> Copyright (c) Xoetrope Ltd., 2001-2006, This software is licensed under
037: * the GNU Public License (GPL), please see license.txt for more details. If
038: * you make commercial use of this software you must purchase a commercial
039: * license from Xoetrope.</p>
040: * <p> $Revision: 1.14 $</p>
041: */
042: public abstract class XReplicationService extends ServiceProxy {
043: private static int nestedCalls;
044: public static final String TARGET_DATABASE_ATTRIBUTE = "target:database";
045:
046: public static final int DERBY = 0;
047: public static final int SQL_SERVER = 1;
048: public static final int HSQLDB = 2;
049:
050: private int targetDatabase = DERBY;
051:
052: private static Hashtable results = new Hashtable();
053: protected DataConnection connObj = null;
054: private ResultSet resultSet = null;
055: private long fetchSize;
056: private boolean recordsDeleted = false;
057:
058: private static String fieldAttrName[] = { "a", "b", "c", "d", "e",
059: "f", "g", "h", "i", "j", "k", "l", "m", "n", "o", "p", "q",
060: "r", "s", "t", "u", "v", "w", "x", "y", "z", "1", "2", "3",
061: "4", "5", "6", "7", "8", "9", "0", "A", "B", "C", "D" };
062:
063: protected XProject currentProject = XProjectManager
064: .getCurrentProject();
065:
066: public XReplicationService() {
067: fetchSize = 500L;
068: DataConnection.setReplicationEnabled(false);
069: setupConnection(null);
070: }
071:
072: public void setupConnection(String connName) {
073: connObj = new DataConnection(currentProject, connName, false);
074: }
075:
076: /**
077: * Call this proxy with the specified arguments
078: * @return the result of the call
079: * @param context The ServiceContext contain pass and return parameters
080: * @param method the name of the service being called
081: * @throws net.xoetrope.optional.service.ServiceProxyException Throw an exception if there is a problem with the call
082: */
083: public synchronized Object call(String method,
084: ServiceContext context) throws ServiceProxyException {
085: //String tableName = ( String )args.getParam( 0 );
086: Hashtable passArgs = context.getPassArgs();
087: /** @todo this is fragile - pull the table name by name */
088: String tableName = ((String) passArgs.get("table"))
089: .toUpperCase();
090:
091: nestedCalls++;
092:
093: try {
094: if (method.equals("getDDL")) {
095: if (tableName.equals("XSYSSERVERTIMESTAMPS"))
096: getTimestamp(tableName);
097: String result = getDDL(tableName);
098: context.setReturnValue(result);
099: return status = new Integer(ServiceProxy.COMPLETE);
100: } else if (method.equals("getData"))
101: return getData(context, tableName);
102: else if (method.equals("postInserts"))
103: return postInserts(context, tableName);
104: else if (method.equals("postUpdates"))
105: return postUpdates(context, tableName);
106: else if (method.equals("postDeletes"))
107: return postDeletes(context, tableName);
108: else if (method.equals("getNextId"))
109: return getNextId(context, tableName);
110: } catch (Exception ioex) {
111: results.remove(resultSet);
112:
113: status = FAILED;
114: if (BuildProperties.DEBUG)
115: ioex.printStackTrace();
116: } finally {
117: nestedCalls--;
118: }
119:
120: return null;
121: }
122:
123: private String getDDL(String tableName)
124: throws java.sql.SQLException {
125: if (tableName.startsWith("XSYS"))
126: return "";
127:
128: String ddl = "CREATE TABLE " + tableName + "(";
129:
130: ResultSet columnMetaData = connObj.getMetaData(tableName);
131:
132: try {
133: String fields = "";
134: while (columnMetaData.next()) {
135: String columnName = columnMetaData
136: .getString("COLUMN_NAME");
137: int dataType = columnMetaData.getInt("DATA_TYPE");
138: String dataTypeName = columnMetaData
139: .getString("TYPE_NAME");
140: int dataSize = columnMetaData.getInt("COLUMN_SIZE");
141: int digits = columnMetaData.getInt("DECIMAL_DIGITS");
142: int nullable = columnMetaData.getInt("NULLABLE");
143: boolean isNullable = (nullable == 1);
144:
145: if (fields.length() > 0)
146: fields += ", ";
147:
148: fields += columnName.replace(" ", "_")
149: .replace("#", "_").replace("/", "_").replace(
150: "(", "_").replace(")", "_")
151: + " ";
152: switch (dataType) {
153: case -10: // NTEXT
154: fields += "VARCHAR(255) ";
155: break;
156: case -9: // NVARCHAR
157: fields += "VARCHAR(" + dataSize + ") ";
158: break;
159: case Types.VARCHAR:
160: case Types.CHAR:
161: case Types.LONGVARCHAR:
162: fields += dataTypeName + "(" + dataSize + ") ";
163: break;
164: case Types.DECIMAL:
165: fields += "DECIMAL(" + dataSize + "," + digits
166: + ") ";
167: break;
168: case Types.BIT:
169: case Types.BOOLEAN:
170: case Types.DATE:
171: case Types.DOUBLE:
172: case Types.FLOAT:
173: case Types.INTEGER:
174: case Types.NUMERIC:
175: case Types.REAL:
176: case Types.SMALLINT:
177: case Types.TIME:
178: case Types.TINYINT:
179: fields += replaceKeyword(dataTypeName);
180: break;
181:
182: case Types.TIMESTAMP:
183: fields += "TIMESTAMP";
184: break;
185: }
186: if (!isNullable)
187: fields += " NOT NULL";
188:
189: }
190: ddl += fields + ")";
191: } catch (SQLException ex) {
192: ex.printStackTrace();
193: }
194:
195: columnMetaData.close();
196:
197: return ddl;
198: }
199:
200: /**
201: * Get the new data from the database
202: */
203: private Integer getData(ServiceContext context, String tableName) {
204: Hashtable passArgs = context.getPassArgs();
205:
206: StringWriter writer = new StringWriter(4096);
207: String header = "<?xml version=\"1.0\" encoding=\"UTF-8\"?>";
208: try {
209: header += "<results ";
210: //XMLElement data = new XMLElement( "results" );
211:
212: String key = null;
213: Timestamp timestamp = null;
214:
215: // If the key is present then look up the result set in the results table
216: // it should be stored from a previous call.
217: key = (String) context.getArgs().getPassParam("key");
218: PreparedStatement ps = null;
219: if (key != null) {
220: // Get data from the hashtable.
221: //key = ( String )args.getParam( 1 );
222: Object cachedData = results.get(key);
223: if (cachedData == null) {
224: status = ServiceProxy.FAILED;
225: throw new ServiceProxyException(
226: "The replication context is not available");
227: }
228: resultSet = ((CacheDatasetRecord) cachedData).resultSet;
229: timestamp = ((CacheDatasetRecord) cachedData).timestamp;
230: ps = ((CacheDatasetRecord) cachedData).ps;
231: //data.setAttribute( "key", key );
232: header += "key=\"" + key + "\" ";
233: } else {
234: String param1, param2, param3;
235: String key1, key2, key3;
236: Enumeration e = passArgs.elements();
237: param1 = (String) e.nextElement();
238: param2 = (String) e.nextElement();
239:
240: e = passArgs.keys();
241: key1 = (String) e.nextElement();
242: key2 = (String) e.nextElement();
243:
244: tableName = (key1.equals("ts") ? param2 : param1);
245: tableName = tableName.toUpperCase();
246: String ts = key1.equals("ts") ? param1 : param2;
247:
248: long maxID = 0L;
249: Object mid = passArgs.get("maxid");
250: if ((mid != null) && (((String) mid).length() > 0))
251: maxID = Long.parseLong((String) mid);
252:
253: if (tableName.equals("XSYSSERVERTIMESTAMPS"))
254: recordsDeleted = hasRecordDeletion(tableName,
255: new Timestamp(Long.parseLong(ts)));
256: else
257: recordsDeleted = false;// The postDeletes should pull this data! hasRecordDeletion( tableName, Timestamp.valueOf( ts ));
258: timestamp = getTimestamp(tableName);
259:
260: long records = 0L;
261: ResultSet rsMax = doQuery("SELECT COUNT(*) FROM "
262: + tableName);
263: if (rsMax != null) {
264: rsMax.next();
265: records = rsMax.getLong(1);
266: }
267:
268: String sql = "SELECT * FROM " + tableName;
269: boolean setTs = false;
270: if (!tableName.equals("XSYSSERVERTIMESTAMPS")
271: && !recordsDeleted) {
272: sql += " WHERE " + tableName + "_ts>?";
273: setTs = true;
274: }
275:
276: if (records > fetchSize) {
277: // rsMax = doQuery( "SELECT MAX(PSEUDOID) FROM " + tableName );
278: // rsMax.next();
279: // long maxPseudoId = rsMax.getLong( 1 );
280: if (maxID > 0L)
281: sql += " AND (PSEUDOID>" + maxID + ")";
282: }
283:
284: ps = connObj.createPreparedStatement(sql, "default",
285: false);
286: if (BuildProperties.DEBUG)
287: DebugLogger.trace("creating prepared statement: "
288: + sql);
289: if (setTs)
290: ps.setTimestamp(1,
291: new Timestamp(Long.parseLong(ts)));
292:
293: if (BuildProperties.DEBUG)
294: DebugLogger.trace(sql);
295:
296: resultSet = ps.executeQuery();
297:
298: key = new Long(new java.util.Date().getTime())
299: .toString();
300: CacheDatasetRecord cdr = new CacheDatasetRecord();
301: cdr.resultSet = resultSet;
302: cdr.timestamp = timestamp;
303: results.put(key, cdr);
304: //data.setAttribute( "key", key );
305: header += "key=\"" + key + "\" ";
306: }
307:
308: // Return the next set of rows.
309: ResultSetMetaData rsmd = resultSet.getMetaData();
310: int recordCount = 0;
311: int numFields = rsmd.getColumnCount();
312: while (!resultSet.isAfterLast() && resultSet.next()) {
313: //IXMLElement child = data.createElement( "row" );
314: writer.write("<row ");
315: for (int i = 1; i <= numFields; i++) {
316: int fieldType = rsmd.getColumnType(i);
317: switch (fieldType) {
318: case java.sql.Types.BIGINT:
319: case java.sql.Types.DECIMAL:
320: case java.sql.Types.INTEGER:
321: case java.sql.Types.NUMERIC:
322: case java.sql.Types.REAL:
323: case java.sql.Types.SMALLINT:
324: case java.sql.Types.DOUBLE:
325: case java.sql.Types.FLOAT:
326: //child.setAttribute( fieldAttrName[ i ], resultSet.getString( i ) );
327: String s = resultSet.getString(i);
328: writer.write(fieldAttrName[i] + "=\""
329: + (s == null ? "null" : s) + "\" ");
330: break;
331: case java.sql.Types.TIMESTAMP:
332: //child.setAttribute( fieldAttrName[ i ], "'" + sqlEscape( resultSet.getTimestamp( i ).toGMTString()) + "'" );
333: // SQL Server returns the UTC time
334: //child.setAttribute( fieldAttrName[ i ], Long.toString( resultSet.getTimestamp( i ).getTime()) );
335: Timestamp ts = resultSet.getTimestamp(i);
336: if (ts != null)
337: writer.write(fieldAttrName[i] + "=\""
338: + Long.toString(ts.getTime())
339: + "\" ");
340: else
341: writer.write(fieldAttrName[i]
342: + "=\"null\" ");
343: break;
344: default:
345: //child.setAttribute( fieldAttrName[ i ], sqlEscape( resultSet.getString( i )) );
346: writer.write(fieldAttrName[i] + "=\""
347: + sqlEscape(resultSet.getString(i))
348: + "\" ");
349: break;
350: }
351: }
352: writer.write("/>\n");
353: //data.addChild( child );
354: if (++recordCount == fetchSize)
355: break;
356: }
357:
358: // if all the data has been returned no need to maintain it.
359: if (recordCount < fetchSize) {
360: //data.setAttribute( "complete", "true" );
361: header += "complete=\"true\" ";
362: results.remove(key);
363: resultSet.close();
364: connObj.closePreparedStatement(ps);
365:
366: /**
367: * @todo a timeout value may be needed on this cache. if the key then
368: * represents data that has been removed from the cache an expired
369: * flag could be returned to the client causing it to reinitiate the
370: * request with the timestamp of it newest record so as to get the
371: * remaining records?
372: */
373: }
374:
375: //data.setAttribute( "timestamp", Long.toString( timestamp.getTime()));
376: header += "timestamp=\""
377: + Long.toString(timestamp.getTime()) + "\" ";
378: if (recordsDeleted
379: || tableName.equals("XSYSSERVERTIMESTAMPS"))
380: header += "delete=\"true\" ";
381: //data.setAttribute( "delete", "true" );
382:
383: // XMLWriter xmlWriter = new XMLWriter( writer );
384: // xmlWriter.write( data, true, 2 );
385:
386: header += ">";
387: writer.flush();
388: String res = header + "\n" + writer.getBuffer().toString()
389: + "</results>";
390: context.setReturnValue(res);
391:
392: status = ServiceProxy.COMPLETE;
393: } catch (Exception ex) {
394: status = ServiceProxy.FAILED;
395: ex.printStackTrace();
396: }
397:
398: return status;
399: }
400:
401: /**
402: * Get the next ID for a field in a table
403: */
404: private Integer getNextId(ServiceContext context, String tableName) {
405: Hashtable passArgs = context.getPassArgs();
406:
407: try {
408: String res = "-1";
409:
410: String managedField = null;
411: long nextId = -1;
412: try {
413: ResultSet rsMax = doQuery("SELECT FIELDNAME, NEXTID FROM XSYSNEXTIDS WHERE TABLENAME=\'"
414: + tableName + "\'");
415: rsMax.next();
416: managedField = rsMax.getString(1);
417: nextId = rsMax.getLong(2);
418: rsMax.close();
419: } catch (Exception ex) {
420: if (BuildProperties.DEBUG)
421: DebugLogger.logWarning("The IDs of table "
422: + tableName + " are not a managed table");
423: } finally {
424: connObj.closeQuery();
425: }
426:
427: if (nextId >= 0) {
428: long tableMax = 0;
429: try {
430: ResultSet rsMax = doQuery("SELECT MAX("
431: + managedField + ") FROM " + tableName);
432: rsMax.next();
433: tableMax = rsMax.getLong(1);
434: rsMax.close();
435:
436: nextId = Math.max(nextId, tableMax + 1L);
437: } catch (Exception ex) {
438: DebugLogger.logError("Unable to max ID for table "
439: + tableName);
440: } finally {
441: connObj.closeQuery();
442: }
443:
444: try {
445: doUpdate("UPDATE XSYSNEXTIDS SET NEXTID="
446: + (nextId + 1L) + " WHERE TABLENAME=\'"
447: + tableName + "\'");
448:
449: res = Long.toString(nextId);
450: } catch (Exception ex) {
451: DebugLogger
452: .logError("Unable to getMaxPseudoId for table "
453: + tableName);
454: } finally {
455: connObj.closeQuery();
456: }
457: }
458:
459: context.setReturnValue(res);
460:
461: status = ServiceProxy.COMPLETE;
462: } catch (Exception ex) {
463: status = ServiceProxy.FAILED;
464: ex.printStackTrace();
465: }
466:
467: return status;
468: }
469:
470: private Integer postDeletes(ServiceContext context, String tableName) {
471: // 1 Decode the request
472: Hashtable passArgs = context.getPassArgs();
473: String data = (String) passArgs.get("data");
474: String dateStr = (String) passArgs.get("lastUpdateDate");
475:
476: try {
477: XmlElement rsRemote = null;
478: if (data != null) {
479: StringReader sr = new StringReader(data);
480: NanoXmlParser parser = new NanoXmlParser();
481: rsRemote = parser.parse(sr);
482:
483: String ids = "";
484: Vector rows = rsRemote.getChildren();
485: int numRows = rows.size();
486: if (numRows > 0) {
487: for (int i = 0; i < numRows; i++) {
488: if (i > 0)
489: ids += ",";
490:
491: XmlElement row = (XmlElement) rows.elementAt(i);
492: ids += row.getAttribute("b");
493: }
494:
495: // 2 Delete the appropriate records
496: doUpdate("DELETE FROM " + tableName
497: + " WHERE PSEUDOID IN(" + ids + ")");
498: }
499: }
500:
501: // 3 Find deleted records
502: XMLElement results = new XMLElement("deletions");
503: Timestamp lastUpdateDate = new Timestamp(Long
504: .parseLong(dateStr));
505: PreparedStatement ps = connObj
506: .createPreparedStatement(
507: "SELECT PSEUDOID FROM XSYSDELETIONS WHERE tablename=? AND deleteDate>?",
508: "default", false);
509: if (BuildProperties.DEBUG)
510: DebugLogger
511: .trace("creating prepared statment: SELECT PSEUDOID FROM XSYSDELETIONS WHERE tablename=? AND deleteDate>?");
512: ps.setString(1, tableName);
513: ps.setTimestamp(2, lastUpdateDate);
514: ResultSet resultSet = ps.executeQuery();
515:
516: while (!resultSet.isAfterLast() && resultSet.next()) {
517: IXMLElement child = results.createElement("row");
518: child.setAttribute("b", Long.toString(resultSet
519: .getLong(1)));
520: results.addChild(child);
521: }
522: resultSet.close();
523: ps.close();
524: if (BuildProperties.DEBUG)
525: DebugLogger.trace("closing prepared statment");
526:
527: // 4 Return the server side deletes
528: StringWriter writer = new StringWriter();
529: XMLWriter xmlWriter = new XMLWriter(writer);
530: xmlWriter.write(results, true, 2);
531:
532: context.setReturnValue(writer.getBuffer().toString());
533:
534: status = ServiceProxy.COMPLETE;
535: } catch (Exception ex) {
536: ex.printStackTrace();
537: }
538:
539: return status;
540: }
541:
542: private Integer postUpdates(ServiceContext context, String tableName) {
543: Hashtable passArgs = context.getPassArgs();
544: String data = (String) passArgs.get("data");
545:
546: try {
547: StringReader sr = new StringReader(data);
548: NanoXmlParser parser = new NanoXmlParser();
549: XmlElement rsRemote = parser.parse(sr);
550:
551: appendLocalData(tableName, rsRemote, true);
552: context.setReturnValue("success");
553:
554: status = ServiceProxy.COMPLETE;
555: } catch (Exception ex) {
556: ex.printStackTrace();
557: }
558:
559: return status;
560: }
561:
562: private Integer postInserts(ServiceContext context, String tableName) {
563: Hashtable passArgs = context.getPassArgs();
564: String data = (String) passArgs.get("data");
565:
566: try {
567: StringReader sr = new StringReader(data);
568: NanoXmlParser parser = new NanoXmlParser();
569: XmlElement rsRemote = parser.parse(sr);
570:
571: appendLocalData(tableName, rsRemote, false);
572: context.setReturnValue("success");
573:
574: status = ServiceProxy.COMPLETE;
575: } catch (Exception ex) {
576: ex.printStackTrace();
577: }
578:
579: return status;
580: }
581:
582: private String replaceKeyword(String dataTypeName) {
583: if (targetDatabase == DERBY) {
584: return dataTypeName.toUpperCase().replace("IDENTITY", "");//GENERATED ALWAYS AS IDENTITY (START WITH 1,INCREMENT BY 1)" );
585: }
586:
587: return dataTypeName;
588: }
589:
590: /**
591: * Escape the ' character in SQL values
592: * @return the escaped string
593: */
594: private String sqlEscape(String s) {
595: if (s == null)
596: return null;
597:
598: return s.replaceAll("'", "''").replaceAll("\"", """)
599: .replaceAll("&", "&");
600: }
601:
602: /**
603: * Check to see if the tables has had any records deleted since the client
604: * was last updated.
605: * @param tableName the table name
606: * @param clientTimestamp the client last update time
607: * @return true if deletions have occurred
608: */
609: private boolean hasRecordDeletion(String tableName,
610: Timestamp clientTimestamp) {
611: long serverDeleteTimeStamp = 0l;
612: ResultSet rs = doQuery("SELECT lastDelete FROM XSYSSERVERTIMESTAMPS WHERE tableName='"
613: + tableName + "'");
614: if (rs != null) {
615: try {
616: rs.next();
617: serverDeleteTimeStamp = rs.getTimestamp(1).getTime();
618: rs.close();
619: } catch (SQLException ex) {
620: }
621: }
622:
623: // Check the clientTimestamp against the lastDelete timestamp
624: long cts = clientTimestamp.getTime();
625: if ((serverDeleteTimeStamp > 0)
626: && (cts < serverDeleteTimeStamp))
627: return true;
628:
629: return false;
630: }
631:
632: /**
633: * Get a tables timestamp. This timestamp represents the server time
634: * when the server's data was last updated
635: * @param tableName
636: * @param sysTable the name of the system table to access
637: * @return the timestamp
638: */
639: private Timestamp getTimestamp(String tableName) {
640: // Remove the local data
641: ResultSet rs = doQuery("SELECT lastUpdate FROM XSYSSERVERTIMESTAMPS WHERE tableName='"
642: + tableName + "'");
643: if (rs != null) {
644: try {
645: rs.next();
646: Timestamp ts = rs.getTimestamp(1);
647: rs.close();
648:
649: return ts;
650: } catch (SQLException ex) {
651: if (BuildProperties.DEBUG)
652: DebugLogger.trace("No timestampe available for "
653: + tableName);
654: }
655: } else {
656: // Setup the timestamp table if it doesn't already exist
657: setupTimestampTables();
658: }
659:
660: // Add the tag fields to the table
661: if (!tableName.equals("XSYSSERVERTIMESTAMPS"))
662: tagTable(tableName);
663:
664: // Insert the new record.
665: java.util.Date now = new java.util.Date();
666: java.sql.Timestamp date = new java.sql.Timestamp(now.getTime());
667: String updateTime = date.toString();
668: // String firstTime = new java.sql.Timestamp( 0 ).toString();
669: java.sql.Timestamp firstTime = new java.sql.Timestamp(0);
670: try {
671: PreparedStatement ps = connObj
672: .createPreparedStatement(
673: "INSERT INTO XSYSSERVERTIMESTAMPS (tableName,lastUpdate,lastDelete) VALUES(?,?,?)",
674: "default", false);
675: if (BuildProperties.DEBUG)
676: DebugLogger
677: .trace("creating prepared statment: INSERT INTO XSYSSERVERTIMESTAMPS (tableName,lastUpdate,lastDelete) VALUES(?,?,?)");
678: ps.setString(1, tableName);
679: ps.setTimestamp(2, date);
680: ps.setTimestamp(3, new java.sql.Timestamp(0));
681: ps.executeUpdate();
682: connObj.closePreparedStatement(ps);
683: if (BuildProperties.DEBUG)
684: DebugLogger.trace("closing prepared statment");
685: } catch (Exception ex) {
686: ex.printStackTrace();
687: }
688:
689: return date;
690: }
691:
692: /**
693: * Creates the timestamps tables.
694: */
695: protected abstract void setupTimestampTables();
696:
697: /**
698: * Add the required pseudo column and timestamp column to the named table
699: * @param tableName the table to tag.
700: */
701: protected abstract void tagTable(String tableName);
702:
703: /**
704: * Invoke a SQL query and return the result set
705: * @param sql the query string
706: * @return the result set or null on failure
707: */
708: private ResultSet doQuery(String sql) {
709: try {
710: if (BuildProperties.DEBUG)
711: DebugLogger.trace(sql);
712: return connObj.executeQuery(sql);
713: } catch (SQLException ex1) {
714: if (BuildProperties.DEBUG)
715: DebugLogger.logError("Query failed");
716: }
717: return null;
718: }
719:
720: /**
721: * Invoke a SQL query
722: * @param sql the query string
723: */
724: protected void doUpdate(String sql) {
725: try {
726: if (BuildProperties.DEBUG)
727: DebugLogger.trace(sql);
728: connObj.executeUpdate(sql);
729: } catch (Exception ex1) {
730: ex1.printStackTrace();
731: if (BuildProperties.DEBUG)
732: DebugLogger.logError("Update failed");
733: }
734: }
735:
736: /**
737: * Set the attributes for this service proxy.
738: * @param t The Hashtable of attributes as found in the XML declaration
739: */
740: public void setAttributes(Hashtable t) {
741: Object value = t.get(TARGET_DATABASE_ATTRIBUTE);
742: if ((value != null)
743: && (value.toString().equalsIgnoreCase("derby")))
744: targetDatabase = DERBY;
745: }
746:
747: /**
748: * Append or replace data in the local table
749: * @param tableName the name of the local table
750: * @param rsRemote the data from the remote table
751: * @param maxPseudoId the max ID in the local table
752: * @throws SQLException
753: */
754: private void appendLocalData(String tableName, XmlElement rsRemote,
755: boolean doUpdate) throws SQLException {
756: // Statement statement = connObj.createStatement( ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE );
757: // statement.setEscapeProcessing( true );
758: //
759: // // The XML data does not contain the proper field names so they need to be
760: // // retrieved from the local database
761: // Statement stmt = connObj.createStatement( ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE );
762: //ResultSet rs = ps.executeQuery( "SELECT TOP 1 * FROM " + tableName ); // TOP is notANSI SQL
763: if (BuildProperties.DEBUG)
764: DebugLogger.trace("SELECT * FROM " + tableName);
765:
766: ResultSet rs = doQuery("SELECT * FROM " + tableName);
767: ResultSetMetaData rsmd = rs.getMetaData();
768: int numCols = rsmd.getColumnCount();
769:
770: // Store the column types
771: int[] columnTypes = new int[numCols];
772: String[] columnNames = new String[numCols];
773: for (int i = 1; i <= columnTypes.length; i++) {
774: columnTypes[i - 1] = rsmd.getColumnType(i);
775: columnNames[i - 1] = rsmd.getColumnName(i);
776: }
777: connObj.closeQuery();
778:
779: int idColIdx = numCols;
780: String idColName = null;
781:
782: // Create the PreparedStatement
783: String updateSql, insertSql;
784: insertSql = "INSERT INTO " + tableName + "(";
785: updateSql = "UPDATE " + tableName + " SET ";
786:
787: String values = "";
788: String updateValues = "";
789: String fields = "";
790: int idCol = 0;
791: for (int colIdx = 1; colIdx <= numCols; colIdx++) {
792: String fieldName = columnNames[colIdx - 1];
793: if (fieldName.equalsIgnoreCase("pseudoid")) {
794: idCol = colIdx;
795: idColName = fieldAttrName[idCol];
796: continue;
797: }
798:
799: if (values.length() > 0) {
800: updateValues += ",";
801: values += ",";
802: fields += ",";
803: }
804:
805: values += "?";
806: if (!fieldName.equalsIgnoreCase("pseudoid"))
807: updateValues += fieldName + "=?";
808: fields += fieldName;
809: }
810:
811: updateSql += updateValues + " WHERE PseudoId=?";
812: insertSql += fields + ") VALUES(" + values + ")";
813:
814: // Save the data locally
815: Vector rows = rsRemote.getChildren();
816: int numRows = rows.size();
817: boolean updatesReceived = false;
818: for (int i = 0; i < numRows; i++) {
819: XmlElement row = (XmlElement) rows.elementAt(i);
820:
821: // If the record already exists in the table an update is needed instead of
822: // an insert
823: boolean bUpdate = false;
824: String rowId = row.getAttribute(idColName);
825: PreparedStatement ps = null;
826: if (!doUpdate) {
827: bUpdate = false;
828: ps = connObj.createPreparedStatement(insertSql,
829: "default", true);
830: if (BuildProperties.DEBUG)
831: DebugLogger.trace("creating prepared statment: "
832: + insertSql);
833: } else {
834: bUpdate = true;
835: ps = connObj.createPreparedStatement(updateSql,
836: "default", true);
837: if (BuildProperties.DEBUG)
838: DebugLogger.trace("creating prepared statment: "
839: + updateSql);
840: }
841:
842: int paramIdx = 1;
843: for (int colIdx = 1; colIdx <= numCols; colIdx++) {
844: String fieldValue = row
845: .getAttribute(fieldAttrName[colIdx]);
846: int fieldType = columnTypes[colIdx - 1];
847:
848: // The colIdx has already been incremented, so it's 1 after rge actual index
849: if (colIdx == idCol)
850: continue;
851:
852: if ("null".equals(fieldValue))
853: ps.setNull(paramIdx, fieldType);
854: else {
855: switch (fieldType) {
856: case java.sql.Types.BIGINT:
857: ps
858: .setLong(paramIdx, Long
859: .parseLong(fieldValue));
860: break;
861: case java.sql.Types.SMALLINT:
862: case java.sql.Types.INTEGER:
863: ps.setInt(paramIdx, Integer
864: .parseInt(fieldValue));
865: break;
866: case java.sql.Types.DOUBLE:
867: ps.setDouble(paramIdx, Double
868: .parseDouble(fieldValue));
869: break;
870: case java.sql.Types.NUMERIC:
871: case java.sql.Types.REAL:
872: case java.sql.Types.DECIMAL:
873: case java.sql.Types.FLOAT:
874: ps.setFloat(paramIdx, Float
875: .parseFloat(fieldValue));
876: break;
877: case java.sql.Types.TIME:
878: ps.setTime(paramIdx, new Time(Long
879: .parseLong(fieldValue)));
880: break;
881: case java.sql.Types.DATE:
882: ps.setDate(paramIdx, new java.sql.Date(Long
883: .parseLong(fieldValue)));
884: break;
885: case java.sql.Types.TIMESTAMP:
886: ps.setTimestamp(paramIdx, new Timestamp(Long
887: .parseLong(fieldValue)));
888: break;
889: default:
890: ps.setString(paramIdx, fieldValue);
891: break;
892: }
893: }
894: paramIdx++;
895: }
896:
897: if (bUpdate) {
898: ps.setLong(numCols, Long.parseLong(rowId));
899: if (BuildProperties.DEBUG)
900: DebugLogger.trace(updateSql);
901: }
902:
903: ps.executeUpdate();
904: connObj.closePreparedStatement(ps);
905: if (BuildProperties.DEBUG)
906: DebugLogger.trace("closing prepared statment");
907: // replicationStatementCount++;
908: updatesReceived = true;
909: }
910: // rs.close();
911: // stmt.close();
912: // statement.close();
913:
914: // if ( updatesReceived )
915: // recordMaxId( tableName.toUpperCase());
916: }
917: }
918:
919: class CacheDatasetRecord {
920: ResultSet resultSet;
921: Timestamp timestamp;
922: PreparedStatement ps;
923: }
|