001: /*
002: * Copyright 2004-2008 H2 Group. Licensed under the H2 License, Version 1.0
003: * (license2)
004: * Initial Developer: H2 Group
005: */
006: package org.h2.test.jdbc;
007:
008: import java.sql.Connection;
009: import java.sql.SQLException;
010: import java.sql.Statement;
011:
012: import org.h2.test.TestBase;
013:
014: /**
015: * Tests the Connection.nativeSQL method.
016: */
017: public class TestNativeSQL extends TestBase {
018:
019: public void test() throws Exception {
020: deleteDb("nativeSql");
021: Connection conn = getConnection("nativeSql");
022:
023: for (int i = 0; i < PAIRS.length; i += 2) {
024: test(conn, PAIRS[i], PAIRS[i + 1]);
025: }
026: conn.nativeSQL("TEST");
027: conn.nativeSQL("TEST--testing");
028: conn.nativeSQL("TEST--testing{oj }");
029: conn.nativeSQL("TEST/*{fn }*/");
030: conn.nativeSQL("TEST//{fn }");
031: conn.nativeSQL("TEST-TEST/TEST/*TEST*/TEST--\rTEST--{fn }");
032: conn.nativeSQL("TEST-TEST//TEST");
033: conn.nativeSQL("'{}' '' \"1\" \"\"\"\"");
034: conn.nativeSQL("{?= call HELLO{t '10'}}");
035: conn
036: .nativeSQL("TEST 'test'{OJ OUTER JOIN}'test'{oj OUTER JOIN}");
037: conn.nativeSQL("{call {ts '2001-01-10'}}");
038: conn.nativeSQL("call ? { 1: '}' };");
039: conn.nativeSQL("TEST TEST TEST TEST TEST 'TEST' TEST \"TEST\"");
040: conn.nativeSQL("TEST TEST TEST 'TEST' TEST \"TEST\"");
041: Statement stat = conn.createStatement();
042: stat.setEscapeProcessing(true);
043: stat.execute("CALL {d '2001-01-01'}");
044: stat.setEscapeProcessing(false);
045: try {
046: stat.execute("CALL {d '2001-01-01'} // this is a test");
047: error("expected error if setEscapeProcessing=false");
048: } catch (SQLException e) {
049: checkNotGeneralException(e);
050: }
051: checkFalse(conn.isClosed());
052: conn.close();
053: check(conn.isClosed());
054: }
055:
056: static final String[] PAIRS = new String[] {
057: "CREATE TABLE TEST(ID INT PRIMARY KEY)",
058: "CREATE TABLE TEST(ID INT PRIMARY KEY)",
059:
060: "INSERT INTO TEST VALUES(1)",
061: "INSERT INTO TEST VALUES(1)",
062:
063: "SELECT '{nothing}' FROM TEST",
064: "SELECT '{nothing}' FROM TEST",
065:
066: "SELECT '{fn ABS(1)}' FROM TEST",
067: "SELECT '{fn ABS(1)}' FROM TEST",
068:
069: "SELECT {d '2001-01-01'} FROM TEST",
070: "SELECT '2001-01-01' FROM TEST",
071:
072: "SELECT {t '20:00:00'} FROM TEST",
073: "SELECT '20:00:00' FROM TEST",
074:
075: "SELECT {ts '2001-01-01 20:00:00'} FROM TEST",
076: "SELECT '2001-01-01 20:00:00' FROM TEST",
077:
078: "SELECT {fn CONCAT('{fn x}','{oj}')} FROM TEST",
079: "SELECT CONCAT('{fn x}','{oj}') FROM TEST",
080:
081: "SELECT * FROM {oj TEST T1 LEFT OUTER JOIN TEST T2 ON T1.ID=T2.ID}",
082: "SELECT * FROM TEST T1 LEFT OUTER JOIN TEST T2 ON T1.ID=T2.ID ",
083:
084: "SELECT * FROM TEST WHERE '{' LIKE '{{' {escape '{'}",
085: "SELECT * FROM TEST WHERE '{' LIKE '{{' escape '{' ",
086:
087: "SELECT * FROM TEST WHERE '}' LIKE '}}' {escape '}'}",
088: "SELECT * FROM TEST WHERE '}' LIKE '}}' escape '}' ",
089:
090: "{call TEST('}')}", " call TEST('}') ",
091:
092: "{?= call TEST('}')}", " call TEST('}') ",
093:
094: "{? = call TEST('}')}", " call TEST('}') ",
095:
096: "{{{{this is a bug}", null, };
097:
098: void test(Connection conn, String original, String expected)
099: throws Exception {
100: trace("original: <" + original + ">");
101: trace("expected: <" + expected + ">");
102: try {
103: String result = conn.nativeSQL(original);
104: trace("result: <" + result + ">");
105: check(expected, result);
106: } catch (SQLException e) {
107: check(expected, null);
108: checkNotGeneralException(e);
109: trace("got exception, good");
110: }
111: }
112:
113: }
|