Examples with SQLServerStatementParser com.alibaba.druid.sql.dialect.sqlserver.parser.SQLServerStatementParser used on opensource projects

Example 61 with SQLServerStatementParser

use of com.alibaba.druid.sql.dialect.sqlserver.parser.SQLServerStatementParser in project druid by alibaba.

the class SQLServerRollbackTest method test_2.

public void test_2() {
    String sql = "ROLLBACK TRANSACTION @tran_name_variable";
    SQLServerStatementParser parser = new SQLServerStatementParser(sql);
    List<SQLStatement> stmtList = parser.parseStatementList();
    Assert.assertEquals(1, stmtList.size());
    String text = SQLUtils.toSQLString(stmtList, JdbcUtils.SQL_SERVER);
    Assert.assertEquals("ROLLBACK TRANSACTION @tran_name_variable", text);
}

Example 62 with SQLServerStatementParser

use of com.alibaba.druid.sql.dialect.sqlserver.parser.SQLServerStatementParser in project druid by alibaba.

the class SQLServerRowNumberTest2 method test_isEmpty.

public void test_isEmpty() throws Exception {
    String sql = "SELECT * FROM (" + // 
    "   SELECT ROW_NUMBER() OVER (ORDER BY FAlertDate Desc, FAlertLevel, FAlertType)  AS RowNumber, *" + // 
    "        from monitor_business" + // 
    "   where FRemoveAlert = ?" + // 
    " ) AS temp_table" + // 
    "   WHERE RowNumber BETWEEN ? AND ?";
    SQLServerStatementParser parser = new SQLServerStatementParser(sql);
    SQLStatement stmt = parser.parseStatementList().get(0);
    assertEquals("SELECT *\n" + "FROM (\n" + "\tSELECT ROW_NUMBER() OVER (ORDER BY FAlertDate DESC, FAlertLevel, FAlertType) AS RowNumber, *\n" + "\tFROM monitor_business\n" + "\tWHERE FRemoveAlert = ?\n" + ") temp_table\n" + "WHERE RowNumber BETWEEN ? AND ?", SQLUtils.toSQLServerString(stmt));
    assertEquals("select *\n" + "from (\n" + "\tselect row_number() over (order by FAlertDate desc, FAlertLevel, FAlertType) as RowNumber, *\n" + "\tfrom monitor_business\n" + "\twhere FRemoveAlert = ?\n" + ") temp_table\n" + "where RowNumber between ? and ?", SQLUtils.toSQLServerString(stmt, SQLUtils.DEFAULT_LCASE_FORMAT_OPTION));
}

Example 63 with SQLServerStatementParser

use of com.alibaba.druid.sql.dialect.sqlserver.parser.SQLServerStatementParser in project druid by alibaba.

the class SQLServerRowNumberTest2 method test_isEmpty_2.

public void test_isEmpty_2() throws Exception {
    String sql = "SELECT * FROM (" + // 
    "   SELECT ROW_NUMBER() OVER (ORDER BY FAlertDate Desc, FAlertLevel, FAlertType)  AS RowNumber, *" + // 
    "        from monitor_business" + // 
    "   where FRemoveAlert = ?" + // 
    " ) AS temp_table" + // 
    "   WHERE RowNumber NOT BETWEEN ? AND ?";
    SQLServerStatementParser parser = new SQLServerStatementParser(sql);
    SQLStatement stmt = parser.parseStatementList().get(0);
    Assert.assertEquals("SELECT *\n" + "FROM (\n" + "\tSELECT ROW_NUMBER() OVER (ORDER BY FAlertDate DESC, FAlertLevel, FAlertType) AS RowNumber, *\n" + "\tFROM monitor_business\n" + "\tWHERE FRemoveAlert = ?\n" + ") temp_table\n" + "WHERE RowNumber NOT BETWEEN ? AND ?", SQLUtils.toSQLServerString(stmt));
    assertEquals("select *\n" + "from (\n" + "\tselect row_number() over (order by FAlertDate desc, FAlertLevel, FAlertType) as RowNumber, *\n" + "\tfrom monitor_business\n" + "\twhere FRemoveAlert = ?\n" + ") temp_table\n" + "where RowNumber not between ? and ?", SQLUtils.toSQLServerString(stmt, SQLUtils.DEFAULT_LCASE_FORMAT_OPTION));
}

Example 64 with SQLServerStatementParser

use of com.alibaba.druid.sql.dialect.sqlserver.parser.SQLServerStatementParser in project druid by alibaba.

the class SQLServerInsertTest10 method test_0.

public void test_0() throws Exception {
    String sql = "INSERT INTO DataDB..TBL_TEST_7 VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
    SQLServerStatementParser parser = new SQLServerStatementParser(sql);
    parser.setParseCompleteValues(false);
    parser.setParseValuesSize(3);
    List<SQLStatement> statementList = parser.parseStatementList();
    SQLStatement stmt = statementList.get(0);
    SQLServerInsertStatement insertStmt = (SQLServerInsertStatement) stmt;
    assertEquals(1, insertStmt.getValuesList().size());
    assertEquals(33, insertStmt.getValues().getValues().size());
    assertEquals(0, insertStmt.getColumns().size());
    assertEquals(1, statementList.size());
    SQLServerSchemaStatVisitor visitor = new SQLServerSchemaStatVisitor();
    stmt.accept(visitor);
    String formatSql = "INSERT INTO DataDB..TBL_TEST_7\n" + "VALUES (?, ?, ?, ?, ?\n" + "\t, ?, ?, ?, ?, ?\n" + "\t, ?, ?, ?, ?, ?\n" + "\t, ?, ?, ?, ?, ?\n" + "\t, ?, ?, ?, ?, ?\n" + "\t, ?, ?, ?, ?, ?\n" + "\t, ?, ?, ?)";
    assertEquals(formatSql, SQLUtils.toSQLServerString(insertStmt));
}

Example 65 with SQLServerStatementParser

use of com.alibaba.druid.sql.dialect.sqlserver.parser.SQLServerStatementParser in project druid by alibaba.

the class SQLServerSelectTest14 method test_simple.

public void test_simple() throws Exception {
    String sql = // 
    "SELECT " + // 
    "    a.* " + // 
    "FROM " + // 
    "    ( " + // 
    "            SELECT " + // 
    "                    row_number () over (ORDER BY a.time_add DESC) ROW, " + // 
    "                    a.detail_no AS detailNo, " + // 
    "                    a.ba_id AS baId, " + // 
    "                    a.ba_name AS baName, " + // 
    "                    a.tran_no AS tranNo, " + // 
    "                    a.tran_name AS tranName, " + // 
    "                    a.tran_type AS tranType, " + // 
    "                    a.balance_type AS balanceType, " + // 
    "                    a.detail_income AS detailIncome, " + // 
    "                    a.detail_payout AS detailPayout, " + // 
    "                    a.before_balance AS beforeBalance, " + // 
    "                    a.after_balance AS afterBalance, " + // 
    "                    a.time_add AS timeAdd, " + // 
    "                    a.user_add AS userAdd, " + // 
    "                    a.remark AS remark, " + // 
    "                    ( " + // 
    "                            SELECT " + // 
    "                                    top 1 t.param_name " + // 
    "                            FROM " + // 
    "                                    config.sys_params t " + // 
    "                            WHERE " + // 
    "                                    t.param_type = 2 " + // 
    "                            AND t.param_value = a.tran_type " + // 
    "                    ) AS tranTypeName " + // 
    "            FROM " + // 
    "                    bussiness.account_detail a " + // 
    "            WHERE " + // 
    "                    1 = 1 " + // 
    "            AND a.time_add >= 2 " + // 
    "            AND a.time_add <= 3 " + // 
    "    ) a " + // 
    "WHERE " + // 
    "    a.ROW BETWEEN (10+2) AND 20 ";
    String expect = "SELECT a.*\n" + "FROM (\n" + "\tSELECT row_number() OVER (ORDER BY a.time_add DESC) AS ROW, a.detail_no AS detailNo, a.ba_id AS baId, a.ba_name AS baName\n" + "\t\t, a.tran_no AS tranNo, a.tran_name AS tranName, a.tran_type AS tranType, a.balance_type AS balanceType, a.detail_income AS detailIncome\n" + "\t\t, a.detail_payout AS detailPayout, a.before_balance AS beforeBalance, a.after_balance AS afterBalance, a.time_add AS timeAdd, a.user_add AS userAdd\n" + "\t\t, a.remark AS remark\n" + "\t\t, (\n" + "\t\t\tSELECT TOP 1 t.param_name\n" + "\t\t\tFROM config.sys_params t\n" + "\t\t\tWHERE t.param_type = 2\n" + "\t\t\t\tAND t.param_value = a.tran_type\n" + "\t\t) AS tranTypeName\n" + "\tFROM bussiness.account_detail a\n" + "\tWHERE 1 = 1\n" + "\t\tAND a.time_add >= 2\n" + "\t\tAND a.time_add <= 3\n" + ") a\n" + "WHERE a.ROW BETWEEN (10 + 2) AND 20";
    SQLServerStatementParser parser = new SQLServerStatementParser(sql);
    SQLStatement stmt = parser.parseStatementList().get(0);
    String text = TestUtils.outputSqlServer(stmt);
    assertEquals(expect, text);
// System.out.println(text);
}