001: /*
002: * SqlUtil.java
003: *
004: * This file is part of SQL Workbench/J, http://www.sql-workbench.net
005: *
006: * Copyright 2002-2008, Thomas Kellerer
007: * No part of this code maybe reused without the permission of the author
008: *
009: * To contact the author please send an email to: support@sql-workbench.net
010: *
011: */
012: package workbench.util;
013:
014: import java.sql.ResultSet;
015: import java.sql.ResultSetMetaData;
016: import java.sql.SQLException;
017: import java.sql.SQLWarning;
018: import java.sql.Statement;
019: import java.sql.Types;
020: import java.util.ArrayList;
021: import java.util.Collections;
022: import java.util.HashSet;
023: import java.util.LinkedList;
024: import java.util.List;
025: import java.util.Set;
026: import java.util.regex.Matcher;
027: import java.util.regex.Pattern;
028: import workbench.db.ColumnIdentifier;
029: import workbench.db.DbMetadata;
030: import workbench.db.DbSettings;
031: import workbench.db.TableIdentifier;
032: import workbench.db.WbConnection;
033: import workbench.log.LogMgr;
034: import workbench.sql.formatter.SQLLexer;
035: import workbench.sql.formatter.SQLToken;
036: import workbench.sql.formatter.SqlFormatter;
037: import workbench.storage.ResultInfo;
038:
039: /**
040: * Methods for manipulating and analyzing SQL statements.
041: */
042: public class SqlUtil {
043: private static final Pattern SQL_IDENTIFIER = Pattern
044: .compile("[a-zA-Z_][\\w\\$#@]*");
045:
046: /**
047: * Removes the SQL verb of this command. The verb is defined
048: * as the first "word" in the SQL string that is not a comment.
049: *
050: * @see #getSqlVerb(CharSequence)
051: */
052: public static String stripVerb(String sql) {
053: String result = "";
054: try {
055: SQLLexer l = new SQLLexer(sql);
056: SQLToken t = l.getNextToken(false, false);
057: int pos = -1;
058: if (t != null)
059: pos = t.getCharEnd();
060: if (pos > -1)
061: result = sql.substring(pos).trim();
062: } catch (Exception e) {
063: LogMgr.logError("SqlCommand.stripVerb()",
064: "Error cleaning up SQL", e);
065: }
066: return result;
067: }
068:
069: public static String quoteObjectname(String object) {
070: return quoteObjectname(object, false);
071: }
072:
073: public static String quoteObjectname(String aColname,
074: boolean quoteAlways) {
075: if (aColname == null)
076: return null;
077: if (aColname.length() == 0)
078: return "";
079: aColname = aColname.trim();
080:
081: boolean doQuote = quoteAlways;
082:
083: if (!quoteAlways) {
084: Matcher m = SQL_IDENTIFIER.matcher(aColname);
085: //doQuote = m.find() || Character.isDigit(aColname.charAt(0));;
086: doQuote = !m.matches();
087: }
088: if (!doQuote)
089: return aColname;
090: StringBuilder col = new StringBuilder(aColname.length() + 2);
091: col.append('"');
092: col.append(aColname);
093: col.append('"');
094: return col.toString();
095: }
096:
097: /**
098: * Returns the type that is beeing created e.g. TABLE, VIEW, PROCEDURE
099: */
100: public static String getCreateType(CharSequence sql) {
101: try {
102: SQLLexer lexer = new SQLLexer(sql);
103: SQLToken t = lexer.getNextToken(false, false);
104: String v = t.getContents();
105: if (!v.equals("CREATE") && !v.equals("RECREATE")
106: && !v.equals("CREATE OR REPLACE"))
107: return null;
108: SQLToken type = lexer.getNextToken(false, false);
109: if (type == null)
110: return null;
111:
112: // check for CREATE FORCE VIEW
113: if (type.getContents().equals("FORCE")) {
114: SQLToken t2 = lexer.getNextToken(false, false);
115: if (t2 == null)
116: return null;
117: return t2.getContents();
118: }
119: return type.getContents();
120: } catch (Exception e) {
121: return null;
122: }
123: }
124:
125: /**
126: * If the given SQL is a DELETE [FROM] returns
127: * the table from which rows will be deleted
128: */
129: public static String getDeleteTable(CharSequence sql) {
130: try {
131: SQLLexer lexer = new SQLLexer(sql);
132: SQLToken t = lexer.getNextToken(false, false);
133: if (!t.getContents().equals("DELETE"))
134: return null;
135: t = lexer.getNextToken(false, false);
136: // If the next token is not the FROM keyword (which is optional)
137: // then it must be the table name.
138: if (t == null)
139: return null;
140: if (!t.getContents().equals("FROM"))
141: return t.getContents();
142: t = lexer.getNextToken(false, false);
143: if (t == null)
144: return null;
145: return t.getContents();
146: } catch (Exception e) {
147: return null;
148: }
149: }
150:
151: /**
152: * If the given SQL is an INSERT INTO...
153: * returns the target table, otherwise null
154: */
155: public static String getInsertTable(CharSequence sql) {
156: try {
157: SQLLexer lexer = new SQLLexer(sql);
158: SQLToken t = lexer.getNextToken(false, false);
159: if (t == null || !t.getContents().equals("INSERT"))
160: return null;
161: t = lexer.getNextToken(false, false);
162: if (t == null || !t.getContents().equals("INTO"))
163: return null;
164: t = lexer.getNextToken(false, false);
165: if (t == null)
166: return null;
167: return t.getContents();
168: } catch (Exception e) {
169: return null;
170: }
171: }
172:
173: /**
174: * If the given SQL command is an UPDATE command, return
175: * the table that is updated, otherwise return null;
176: */
177: public static String getUpdateTable(CharSequence sql) {
178: try {
179: SQLLexer lexer = new SQLLexer(sql);
180: SQLToken t = lexer.getNextToken(false, false);
181: if (t == null || !t.getContents().equals("UPDATE"))
182: return null;
183: t = lexer.getNextToken(false, false);
184: if (t == null)
185: return null;
186: return t.getContents();
187: } catch (Exception e) {
188: return null;
189: }
190: }
191:
192: /**
193: * Returns the SQL Verb for the given SQL string.
194: */
195: public static String getSqlVerb(CharSequence sql) {
196: if (StringUtil.isEmptyString(sql))
197: return "";
198:
199: SQLLexer l = new SQLLexer(sql);
200: try {
201: SQLToken t = l.getNextToken(false, false);
202: if (t == null)
203: return "";
204:
205: // The SQLLexer does not recognize @ as a keyword (which is basically
206: // correct, but to support the Oracle style includes we'll treat it
207: // as a keyword here.
208: String v = t.getContents();
209: if (v.charAt(0) == '@')
210: return "@";
211:
212: return t.getContents().toUpperCase();
213: } catch (Exception e) {
214: return "";
215: }
216: }
217:
218: /**
219: * Returns the columns for the result set defined by the passed
220: * query.
221: * This method will actually execute the given SQL query, but will
222: * not retrieve any rows (using setMaxRows(1).
223: */
224: public static List<ColumnIdentifier> getResultSetColumns(
225: String sql, WbConnection conn) throws SQLException {
226: if (conn == null)
227: return null;
228:
229: ResultInfo info = getResultInfoFromQuery(sql, conn);
230: if (info == null)
231: return null;
232:
233: int count = info.getColumnCount();
234: ArrayList<ColumnIdentifier> result = new ArrayList<ColumnIdentifier>(
235: count);
236: for (int i = 0; i < count; i++) {
237: result.add(info.getColumn(i));
238: }
239: return result;
240: }
241:
242: public static ResultInfo getResultInfoFromQuery(String sql,
243: WbConnection conn) throws SQLException {
244: if (conn == null)
245: return null;
246:
247: ResultSet rs = null;
248: Statement stmt = null;
249: ResultInfo result = null;
250:
251: try {
252: stmt = conn.createStatementForQuery();
253: stmt.setMaxRows(1);
254: rs = stmt.executeQuery(sql);
255: ResultSetMetaData meta = rs.getMetaData();
256: result = new ResultInfo(meta, conn);
257: List tables = getTables(sql, false);
258: if (tables.size() == 1) {
259: String table = (String) tables.get(0);
260: TableIdentifier tbl = new TableIdentifier(table);
261: result.setUpdateTable(tbl);
262: }
263: } finally {
264: closeAll(rs, stmt);
265: }
266: return result;
267: }
268:
269: private static String getTableDefinition(String table,
270: boolean keepAlias) {
271: if (keepAlias)
272: return table;
273: int pos = StringUtil.findFirstWhiteSpace(table);
274: if (pos > -1)
275: return table.substring(0, pos);
276: return table;
277: }
278:
279: /**
280: * Parse the given SQL SELECT query and return the columns defined
281: * in the column list. If the SQL string does not start with SELECT
282: * returns an empty List
283: * @param select the SQL String to parse
284: * @param includeAlias if false, the "raw" column names will be returned, otherwise
285: * the column name including the alias (e.g. "p.name AS person_name"
286: * @return a List of String objecs.
287: */
288: public static List<String> getSelectColumns(String select,
289: boolean includeAlias) {
290: List<String> result = new LinkedList<String>();
291: try {
292: SQLLexer lex = new SQLLexer(select);
293: SQLToken t = lex.getNextToken(false, false);
294: if (!"SELECT".equalsIgnoreCase(t.getContents()))
295: return Collections.emptyList();
296: t = lex.getNextToken(false, false);
297: int lastColStart = t.getCharBegin();
298: int bracketCount = 0;
299: boolean nextIsCol = true;
300: while (t != null) {
301: String v = t.getContents();
302: if ("(".equals(v)) {
303: bracketCount++;
304: } else if (")".equals(v)) {
305: bracketCount--;
306: } else if (bracketCount == 0
307: && (",".equals(v) || SqlFormatter.SELECT_TERMINAL
308: .contains(v))) {
309: String col = select.substring(lastColStart, t
310: .getCharBegin());
311: if (includeAlias) {
312: result.add(col.trim());
313: } else {
314: result.add(striptColumnAlias(col));
315: }
316: if (SqlFormatter.SELECT_TERMINAL.contains(v)) {
317: nextIsCol = false;
318: lastColStart = -1;
319: break;
320: }
321: nextIsCol = true;
322: } else if (nextIsCol) {
323: lastColStart = t.getCharBegin();
324: nextIsCol = false;
325: }
326: t = lex.getNextToken(false, false);
327: }
328: if (lastColStart > -1) {
329: // no FROM was found, so assume it's a partial SELECT x,y,z
330: String col = select.substring(lastColStart);
331: if (includeAlias) {
332: result.add(col.trim());
333: } else {
334: result.add(striptColumnAlias(col));
335: }
336: }
337: } catch (Exception e) {
338: LogMgr.logError("SqlUtil.getColumnsFromSelect()",
339: "Error parsing SELECT statement", e);
340: return Collections.emptyList();
341: }
342:
343: return result;
344: }
345:
346: public static String striptColumnAlias(String expression) {
347: if (expression == null)
348: return null;
349:
350: List elements = StringUtil.stringToList(expression, " ", true,
351: true, true);
352:
353: return (String) elements.get(0);
354: }
355:
356: public static List getTables(String aSql) {
357: return getTables(aSql, false);
358: }
359:
360: public static final Set<String> JOIN_KEYWORDS = new HashSet<String>(
361: 6);
362: static {
363: JOIN_KEYWORDS.add("INNER JOIN");
364: JOIN_KEYWORDS.add("LEFT JOIN");
365: JOIN_KEYWORDS.add("RIGHT JOIN");
366: JOIN_KEYWORDS.add("LEFT OUTER JOIN");
367: JOIN_KEYWORDS.add("RIGHT OUTER JOIN");
368: JOIN_KEYWORDS.add("CROSS JOIN");
369: JOIN_KEYWORDS.add("FULL JOIN");
370: JOIN_KEYWORDS.add("FULL OUTER JOIN");
371: }
372:
373: /**
374: * Returns a List of tables defined in the SQL query. If the
375: * query is not a SELECT query the result is undefined
376: */
377: public static List<String> getTables(String sql,
378: boolean includeAlias) {
379: String from = SqlUtil.getFromPart(sql);
380: if (from == null || from.trim().length() == 0)
381: return Collections.emptyList();
382: List<String> result = new LinkedList<String>();
383: try {
384: SQLLexer lex = new SQLLexer(from);
385: SQLToken t = lex.getNextToken(false, false);
386:
387: boolean collectTable = true;
388: StringBuilder currentTable = new StringBuilder();
389: int bracketCount = 0;
390: boolean subSelect = false;
391: int subSelectBracketCount = -1;
392:
393: while (t != null) {
394: String s = t.getContents();
395:
396: if (s.equals("SELECT") && bracketCount > 0) {
397: subSelect = true;
398: subSelectBracketCount = bracketCount;
399: }
400:
401: if ("(".equals(s)) {
402: bracketCount++;
403: } else if (")".equals(s)) {
404: if (subSelect
405: && bracketCount == subSelectBracketCount) {
406: subSelect = false;
407: }
408: bracketCount--;
409: t = lex.getNextToken(false, false);
410: continue;
411: }
412:
413: if (!subSelect) {
414: if (JOIN_KEYWORDS.contains(s)) {
415: collectTable = true;
416: if (currentTable.length() > 0) {
417: result.add(getTableDefinition(currentTable
418: .toString(), includeAlias));
419: currentTable = new StringBuilder();
420: }
421: } else if (",".equals(s)) {
422: collectTable = true;
423: result.add(getTableDefinition(currentTable
424: .toString(), includeAlias));
425: currentTable = new StringBuilder();
426: } else if ("ON".equals(s)) {
427: collectTable = false;
428: result.add(getTableDefinition(currentTable
429: .toString(), includeAlias));
430: currentTable = new StringBuilder();
431: } else if (collectTable && !s.equals("(")) {
432: int size = currentTable.length();
433: if (size > 0 && !s.equals(".")
434: && currentTable.charAt(size - 1) != '.')
435: currentTable.append(' ');
436: currentTable.append(s);
437: }
438: }
439: t = lex.getNextToken(false, false);
440: }
441:
442: if (currentTable.length() > 0) {
443: result.add(getTableDefinition(currentTable.toString(),
444: includeAlias));
445: }
446: } catch (Exception e) {
447: LogMgr.logError("SqlUtil.getTable()", "Error parsing sql",
448: e);
449: }
450: return result;
451: }
452:
453: /**
454: * Extract the FROM part of a SQL statement. That is anything after the FROM
455: * up to (but not including) the WHERE, GROUP BY, ORDER BY, whichever comes first
456: */
457: public static String getFromPart(String sql) {
458: int fromPos = getFromPosition(sql);
459: if (fromPos == -1)
460: return null;
461: fromPos += "FROM".length();
462: if (fromPos >= sql.length())
463: return null;
464: int fromEnd = getKeywordPosition(SqlFormatter.FROM_TERMINAL,
465: sql);
466: if (fromEnd == -1) {
467: return sql.substring(fromPos);
468: }
469: return sql.substring(fromPos, fromEnd);
470: }
471:
472: /**
473: * Return the position of the FROM keyword in the given SQL
474: */
475: public static int getFromPosition(String sql) {
476: Set<String> s = new HashSet<String>();
477: s.add("FROM");
478: return getKeywordPosition(s, sql);
479: }
480:
481: public static int getWherePosition(String sql) {
482: Set<String> s = new HashSet<String>();
483: s.add("WHERE");
484: return getKeywordPosition(s, sql);
485: }
486:
487: public static int getKeywordPosition(String keyword,
488: CharSequence sql) {
489: if (keyword == null)
490: return -1;
491: Set<String> s = new HashSet<String>();
492: s.add(keyword.toUpperCase());
493: return getKeywordPosition(s, sql);
494: }
495:
496: public static int getKeywordPosition(Set<String> keywords,
497: CharSequence sql) {
498: int pos = -1;
499: try {
500: SQLLexer lexer = new SQLLexer(sql);
501:
502: SQLToken t = lexer.getNextToken(false, false);
503: int bracketCount = 0;
504: while (t != null) {
505: String value = t.getContents();
506: if ("(".equals(value)) {
507: bracketCount++;
508: } else if (")".equals(value)) {
509: bracketCount--;
510: } else if (bracketCount == 0) {
511: if (keywords.contains(value)) {
512: pos = t.getCharBegin();
513: break;
514: }
515: }
516:
517: t = lexer.getNextToken(false, false);
518: }
519: } catch (Exception e) {
520: pos = -1;
521: }
522: return pos;
523: }
524:
525: public static String makeCleanSql(String aSql, boolean keepNewlines) {
526: return makeCleanSql(aSql, keepNewlines, '\'');
527: }
528:
529: public static String makeCleanSql(String aSql,
530: boolean keepNewlines, char quote) {
531: return makeCleanSql(aSql, keepNewlines, false, quote);
532: }
533:
534: /**
535: * Replaces all white space characters with ' ' (But not inside
536: * string literals) and removes -- style and Java style comments
537: * @param aSql The sql script to "clean out"
538: * @param keepNewlines if true, newline characters (\n) are kept
539: * @param keepComments if true, comments (single line, block comments) are kept
540: * @param quote The quote character
541: * @return String
542: */
543: public static String makeCleanSql(String aSql,
544: boolean keepNewlines, boolean keepComments, char quote) {
545: if (aSql == null)
546: return null;
547: aSql = aSql.trim();
548: int count = aSql.length();
549: if (count == 0)
550: return aSql;
551: boolean inComment = false;
552: boolean inQuotes = false;
553: boolean lineComment = false;
554:
555: StringBuilder newSql = new StringBuilder(count);
556:
557: char last = ' ';
558:
559: for (int i = 0; i < count; i++) {
560: char c = aSql.charAt(i);
561:
562: if (c == quote) {
563: inQuotes = !inQuotes;
564: }
565:
566: if (inQuotes) {
567: newSql.append(c);
568: last = c;
569: continue;
570: }
571:
572: if ((last == '\n' || last == '\r' || i == 0) && (c == '#')) {
573: lineComment = true;
574: }
575:
576: if (!(inComment || lineComment) || keepComments) {
577: if (c == '/' && i < count - 1
578: && aSql.charAt(i + 1) == '*') {
579: inComment = true;
580: i++;
581: } else if (c == '-' && i < count - 1
582: && aSql.charAt(i + 1) == '-') {
583: // ignore rest of line for -- style comments
584: while (c != '\n' && i < count - 1) {
585: i++;
586: c = aSql.charAt(i);
587: }
588: } else {
589: if ((c == '\n' || c == '\r') && !keepNewlines) {
590: // only replace the \n, \r are simply removed
591: // thus replacing \r\n with only one space
592: if (c == '\n')
593: newSql.append(' ');
594: } else if (c != '\n'
595: && (c < 32 || (c > 126 && c < 145) || c == 255)) {
596: newSql.append(' ');
597: } else {
598: newSql.append(c);
599: }
600: }
601: } else {
602: if (c == '*' && i < count - 1
603: && aSql.charAt(i + 1) == '/') {
604: inComment = false;
605: i++;
606: } else if (c == '\n' || c == '\r' && lineComment) {
607: lineComment = false;
608: }
609: }
610: last = c;
611: }
612: String s = newSql.toString().trim();
613: if (s.endsWith(";"))
614: s = s.substring(0, s.length() - 1).trim();
615: return s;
616: }
617:
618: /**
619: * returns true if the passed data type (from java.sql.Types)
620: * indicates a data type which can hold numeric values with
621: * decimals
622: */
623: public static final boolean isDecimalType(int aSqlType, int aScale,
624: int aPrecision) {
625: if (aSqlType == Types.DECIMAL || aSqlType == Types.DOUBLE
626: || aSqlType == Types.FLOAT || aSqlType == Types.NUMERIC
627: || aSqlType == Types.REAL) {
628: return (aScale > 0);
629: } else {
630: return false;
631: }
632: }
633:
634: /**
635: * returns true if the passed JDBC data type (from java.sql.Types)
636: * indicates a data type which maps to a integer type
637: */
638: public static final boolean isIntegerType(int aSqlType) {
639: return (aSqlType == Types.BIGINT || aSqlType == Types.INTEGER
640: || aSqlType == Types.SMALLINT || aSqlType == Types.TINYINT);
641: }
642:
643: /**
644: * Returns true if the given JDBC type maps to the String class. This
645: * returns fals for CLOB data.
646: */
647: public static final boolean isStringType(int aSqlType) {
648: return (aSqlType == Types.VARCHAR || aSqlType == Types.CHAR || aSqlType == Types.LONGVARCHAR);
649: }
650:
651: /**
652: * Returns true if the given JDBC type indicates some kind of
653: * character data (including CLOBs)
654: */
655: public static final boolean isCharacterType(int aSqlType) {
656: return (aSqlType == Types.VARCHAR || aSqlType == Types.CHAR
657: || aSqlType == Types.CLOB || aSqlType == Types.LONGVARCHAR);
658: }
659:
660: /**
661: * Returns true if the passed datatype (from java.sql.Types)
662: * can hold a numeric value (either with or without decimals)
663: */
664: public static final boolean isNumberType(int aSqlType) {
665: return (aSqlType == Types.BIGINT || aSqlType == Types.INTEGER
666: || aSqlType == Types.DECIMAL
667: || aSqlType == Types.DOUBLE || aSqlType == Types.FLOAT
668: || aSqlType == Types.NUMERIC || aSqlType == Types.REAL
669: || aSqlType == Types.SMALLINT || aSqlType == Types.TINYINT);
670: }
671:
672: public static final boolean isDateType(int aSqlType) {
673: return (aSqlType == Types.DATE || aSqlType == Types.TIMESTAMP);
674: }
675:
676: public static final boolean isClobType(int aSqlType) {
677: return (aSqlType == Types.CLOB);
678: }
679:
680: public static final boolean isClobType(int aSqlType,
681: DbSettings dbInfo) {
682: if (dbInfo == null || !dbInfo.longVarcharIsClob())
683: return (aSqlType == Types.CLOB);
684: return (aSqlType == Types.CLOB || aSqlType == Types.LONGVARCHAR);
685: }
686:
687: public static final boolean isBlobType(int aSqlType) {
688: return (aSqlType == Types.BLOB || aSqlType == Types.BINARY
689: || aSqlType == Types.LONGVARBINARY || aSqlType == Types.VARBINARY);
690: }
691:
692: /**
693: * Convenience method to close a ResultSet without a possible
694: * SQLException
695: */
696: public static void closeResult(ResultSet rs) {
697: if (rs == null)
698: return;
699: try {
700: rs.close();
701: } catch (Throwable th) {
702: }
703: }
704:
705: /**
706: * Convenience method to close a Statement without a possible
707: * SQLException
708: */
709: public static void closeStatement(Statement stmt) {
710: if (stmt == null)
711: return;
712: try {
713: stmt.close();
714: } catch (Throwable th) {
715: }
716: }
717:
718: /**
719: * Convenience method to close a ResultSet and a Statement without
720: * a possible SQLException
721: */
722: public static void closeAll(ResultSet rs, Statement stmt) {
723: closeResult(rs);
724: closeStatement(stmt);
725: }
726:
727: public static final String getTypeName(int aSqlType) {
728: if (aSqlType == Types.ARRAY)
729: return "ARRAY";
730: else if (aSqlType == Types.BIGINT)
731: return "BIGINT";
732: else if (aSqlType == Types.BINARY)
733: return "BINARY";
734: else if (aSqlType == Types.BIT)
735: return "BIT";
736: else if (aSqlType == Types.BLOB)
737: return "BLOB";
738: else if (aSqlType == Types.BOOLEAN)
739: return "BOOLEAN";
740: else if (aSqlType == Types.CHAR)
741: return "CHAR";
742: else if (aSqlType == Types.CLOB)
743: return "CLOB";
744: else if (aSqlType == Types.DATALINK)
745: return "DATALINK";
746: else if (aSqlType == Types.DATE)
747: return "DATE";
748: else if (aSqlType == Types.DECIMAL)
749: return "DECIMAL";
750: else if (aSqlType == Types.DISTINCT)
751: return "DISTINCT";
752: else if (aSqlType == Types.DOUBLE)
753: return "DOUBLE";
754: else if (aSqlType == Types.FLOAT)
755: return "FLOAT";
756: else if (aSqlType == Types.INTEGER)
757: return "INTEGER";
758: else if (aSqlType == Types.JAVA_OBJECT)
759: return "JAVA_OBJECT";
760: else if (aSqlType == Types.LONGVARBINARY)
761: return "LONGVARBINARY";
762: else if (aSqlType == Types.LONGVARCHAR)
763: return "LONGVARCHAR";
764: else if (aSqlType == Types.NULL)
765: return "NULL";
766: else if (aSqlType == Types.NUMERIC)
767: return "NUMERIC";
768: else if (aSqlType == Types.OTHER)
769: return "OTHER";
770: else if (aSqlType == Types.REAL)
771: return "REAL";
772: else if (aSqlType == Types.REF)
773: return "REF";
774: else if (aSqlType == Types.SMALLINT)
775: return "SMALLINT";
776: else if (aSqlType == Types.STRUCT)
777: return "STRUCT";
778: else if (aSqlType == Types.TIME)
779: return "TIME";
780: else if (aSqlType == Types.TIMESTAMP)
781: return "TIMESTAMP";
782: else if (aSqlType == Types.TINYINT)
783: return "TINYINT";
784: else if (aSqlType == Types.VARBINARY)
785: return "VARBINARY";
786: else if (aSqlType == Types.VARCHAR)
787: return "VARCHAR";
788: // The following values are JDBC 4.0 /Java6 constants
789: // but as I want to be able to compile with Java 5, I cannot
790: // reference the constant declarations from java.sql.Types
791: else if (aSqlType == 2011)
792: return "NCLOB";
793: else if (aSqlType == 2009)
794: return "SQLXML";
795: else if (aSqlType == -15)
796: return "NCHAR";
797: else if (aSqlType == -9)
798: return "NVARCHAR";
799: else if (aSqlType == -16)
800: return "LONGNVARCHAR";
801: else if (aSqlType == -8)
802: return "ROWID";
803: else
804: return "UNKNOWN";
805: }
806:
807: /**
808: * Construct the SQL display name for the given SQL datatype.
809: * This is used when re-recreating the source for a table
810: */
811: public static String getSqlTypeDisplay(String aTypeName,
812: int sqlType, int size, int digits) {
813: String display = aTypeName;
814:
815: switch (sqlType) {
816: case Types.VARCHAR:
817: case Types.CHAR:
818: if ("text".equalsIgnoreCase(aTypeName)
819: && size == Integer.MAX_VALUE)
820: return aTypeName;
821: if (size > 0) {
822: display = aTypeName + "(" + size + ")";
823: } else {
824: display = aTypeName;
825: }
826: break;
827: case Types.DECIMAL:
828: case Types.DOUBLE:
829: case Types.NUMERIC:
830: case Types.FLOAT:
831: if ("money".equalsIgnoreCase(aTypeName)) // SQL Server
832: {
833: display = aTypeName;
834: } else if ((aTypeName.indexOf('(') == -1)) {
835: if (digits > 0 && size > 0) {
836: display = aTypeName + "(" + size + "," + digits
837: + ")";
838: } else if (size <= 0 && digits > 0) {
839: display = aTypeName + "(" + digits + ")";
840: } else if (size > 0 && digits <= 0) {
841: display = aTypeName + "(" + size + ")";
842: }
843: }
844: break;
845:
846: case Types.OTHER:
847: // Oracle specific datatypes
848: if (aTypeName.toUpperCase().startsWith("NVARCHAR")) {
849: display = aTypeName + "(" + size + ")";
850: } else if ("NCHAR".equalsIgnoreCase(aTypeName)) {
851: display = aTypeName + "(" + size + ")";
852: } else if ("UROWID".equalsIgnoreCase(aTypeName)) {
853: display = aTypeName + "(" + size + ")";
854: } else if ("RAW".equalsIgnoreCase(aTypeName)) {
855: display = aTypeName + "(" + size + ")";
856: }
857: break;
858: default:
859: display = aTypeName;
860: break;
861: }
862: return display;
863: }
864:
865: public static CharSequence getWarnings(WbConnection con,
866: Statement stmt) {
867: try {
868: // some DBMS return warnings on the connection rather then on the
869: // statement. We need to check them here as well. Then some of
870: // the DBMS return the same warnings on the Statement AND the
871: // Connection object (and MySQL returns an error as the Exception itself
872: // and additionally as a warning on the Statement...)
873: // For this we keep a list of warnings which have been added
874: // from the statement. They will not be added when the Warnings from
875: // the connection are retrieved
876: Set<String> added = new HashSet<String>();
877: StringBuilder msg = null;
878: String s = null;
879: SQLWarning warn = (stmt == null ? null : stmt.getWarnings());
880: boolean hasWarnings = warn != null;
881: int count = 0;
882:
883: while (warn != null) {
884: count++;
885: s = warn.getMessage();
886: if (s != null && s.length() > 0) {
887: msg = append(msg, s);
888: if (!s.endsWith("\n"))
889: msg.append('\n');
890: added.add(s);
891: }
892: if (count > 15)
893: break; // prevent endless loop
894: warn = warn.getNextWarning();
895: }
896:
897: warn = (con == null ? null : con.getSqlConnection()
898: .getWarnings());
899: hasWarnings = hasWarnings || (warn != null);
900: count = 0;
901: while (warn != null) {
902: s = warn.getMessage();
903: // Some JDBC drivers duplicate the warnings between
904: // the statement and the connection.
905: // This is to prevent adding them twice
906: if (!added.contains(s)) {
907: msg = append(msg, s);
908: if (!s.endsWith("\n"))
909: msg.append('\n');
910: }
911: if (count > 25)
912: break; // prevent endless loop
913: warn = warn.getNextWarning();
914: }
915:
916: // make sure the warnings are cleared from both objects!
917: con.clearWarnings();
918: stmt.clearWarnings();
919: StringUtil.trimTrailingWhitespace(msg);
920: return msg;
921: } catch (Exception e) {
922: return null;
923: }
924: }
925:
926: private static StringBuilder append(StringBuilder msg,
927: CharSequence s) {
928: if (msg == null)
929: msg = new StringBuilder(100);
930: msg.append(s);
931: return msg;
932: }
933:
934: public static String buildExpression(WbConnection conn,
935: String catalog, String schema, String name) {
936: StringBuilder result = new StringBuilder(30);
937: DbMetadata meta = conn.getMetadata();
938: if (!StringUtil.isEmptyString(catalog)) {
939: result.append(meta.quoteObjectname(catalog));
940: result.append('.');
941: }
942: if (!StringUtil.isEmptyString(schema)) {
943: result.append(meta.quoteObjectname(schema));
944: result.append('.');
945: }
946: result.append(meta.quoteObjectname(name));
947: return result.toString();
948: }
949:
950: }
|