Examples with SQLServerSchemaStatVisitor com.alibaba.druid.sql.dialect.sqlserver.visitor.SQLServerSchemaStatVisitor used on opensource projects

Example 51 with SQLServerSchemaStatVisitor

use of com.alibaba.druid.sql.dialect.sqlserver.visitor.SQLServerSchemaStatVisitor in project druid by alibaba.

the class SQLServerInsertTest8 method test_1.

public void test_1() throws Exception {
    String sql = "INSERT TOP(5)INTO dbo.EmployeeSales  OUTPUT inserted.EmployeeID, inserted.FirstName, inserted.LastName, inserted.YearlySales SELECT sp.BusinessEntityID, c.LastName, c.FirstName, sp.SalesYTD FROM Sales.SalesPerson AS sp INNER JOIN Person.Person AS c ON sp.BusinessEntityID = c.BusinessEntityID WHERE sp.SalesYTD > 250000.00 ORDER BY sp.SalesYTD DESC;";
    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;
    Assert.assertEquals(0, insertStmt.getValuesList().size());
    Assert.assertEquals(0, insertStmt.getColumns().size());
    Assert.assertEquals(1, statementList.size());
    SQLServerSchemaStatVisitor visitor = new SQLServerSchemaStatVisitor();
    stmt.accept(visitor);
    String formatSql = // 
    "INSERT TOP (5) INTO dbo.EmployeeSales" + // 
    "\nOUTPUT inserted.EmployeeID, inserted.FirstName, inserted.LastName, inserted.YearlySales" + // 
    "\nSELECT sp.BusinessEntityID, c.LastName, c.FirstName, sp.SalesYTD" + // 
    "\nFROM Sales.SalesPerson sp" + // 
    "\nINNER JOIN Person.Person c ON sp.BusinessEntityID = c.BusinessEntityID" + // 
    "\nWHERE sp.SalesYTD > 250000.00" + "\nORDER BY sp.SalesYTD DESC;";
    Assert.assertEquals(formatSql, SQLUtils.toSQLServerString(insertStmt));
}

Example 52 with SQLServerSchemaStatVisitor

use of com.alibaba.druid.sql.dialect.sqlserver.visitor.SQLServerSchemaStatVisitor in project druid by alibaba.

the class SQLServerInsertTest8 method test_0.

public void test_0() throws Exception {
    String sql = "INSERT Production.ScrapReason OUTPUT INSERTED.ScrapReasonID, INSERTED.Name, INSERTED.ModifiedDate INTO @MyTableVar VALUES (N'Operator error', GETDATE());";
    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;
    Assert.assertEquals(1, insertStmt.getValuesList().size());
    Assert.assertEquals(2, insertStmt.getValues().getValues().size());
    Assert.assertEquals(0, insertStmt.getColumns().size());
    Assert.assertEquals(1, statementList.size());
    SQLServerSchemaStatVisitor visitor = new SQLServerSchemaStatVisitor();
    stmt.accept(visitor);
    String formatSql = // 
    "INSERT INTO Production.ScrapReason" + // 
    "\nOUTPUT INSERTED.ScrapReasonID, INSERTED.Name, INSERTED.ModifiedDate" + // 
    "\n\tINTO @MyTableVar" + "\nVALUES (N'Operator error', GETDATE());";
    Assert.assertEquals(formatSql, SQLUtils.toSQLServerString(insertStmt));
}

Example 53 with SQLServerSchemaStatVisitor

use of com.alibaba.druid.sql.dialect.sqlserver.visitor.SQLServerSchemaStatVisitor in project druid by alibaba.

the class SQLServerMerge_0 method test_0.

public void test_0() throws Exception {
    String sql = "MERGE INTO tb_device_category t1\n" + "USING ( select distinct device_category, factory_id from temp_device where device_category is not null\n" + ") t2\n" + "on (t1.name = t2.device_category and t1.factory_id = t2.factory_id)\n" + "WHEN NOT MATCHED THEN\n" + "INSERT (name, factory_id) values (t2.device_category, t2.factory_id);";
    SQLServerStatementParser parser = new SQLServerStatementParser(sql);
    parser.setParseCompleteValues(false);
    parser.setParseValuesSize(3);
    List<SQLStatement> statementList = parser.parseStatementList();
    SQLStatement stmt = statementList.get(0);
    SQLMergeStatement mergeStmt = (SQLMergeStatement) stmt;
    SQLServerSchemaStatVisitor visitor = new SQLServerSchemaStatVisitor();
    stmt.accept(visitor);
    String formatSql = "MERGE INTO tb_device_category t1\n" + "USING (\n" + "\tSELECT DISTINCT device_category, factory_id\n" + "\tFROM temp_device\n" + "\tWHERE device_category IS NOT NULL\n" + ") t2 ON (t1.name = t2.device_category\n" + "AND t1.factory_id = t2.factory_id) \n" + "WHEN NOT MATCHED THEN INSERT (name, factory_id) VALUES (t2.device_category, t2.factory_id);";
    assertEquals(formatSql, SQLUtils.toSQLServerString(mergeStmt));
}

Example 54 with SQLServerSchemaStatVisitor

use of com.alibaba.druid.sql.dialect.sqlserver.visitor.SQLServerSchemaStatVisitor in project druid by alibaba.

the class SQLServerInsertTest5 method test_parseCompleteValues_true.

public void test_parseCompleteValues_true() throws Exception {
    String sql = "insert into t(a,b) values ('a1','b1'),('a2','b2'),('a3','b3'),('a4','b4');";
    SQLServerStatementParser parser = new SQLServerStatementParser(sql);
    parser.setParseCompleteValues(true);
    List<SQLStatement> statementList = parser.parseStatementList();
    SQLStatement stmt = statementList.get(0);
    SQLServerInsertStatement insertStmt = (SQLServerInsertStatement) stmt;
    Assert.assertEquals(4, insertStmt.getValuesList().size());
    Assert.assertEquals(2, insertStmt.getValues().getValues().size());
    Assert.assertEquals(2, insertStmt.getColumns().size());
    Assert.assertEquals(1, statementList.size());
    SQLServerSchemaStatVisitor visitor = new SQLServerSchemaStatVisitor();
    stmt.accept(visitor);
    String formatSql = // 
    "INSERT INTO t (a, b)" + // 
    "\nVALUES ('a1', 'b1')," + // 
    "\n('a2', 'b2')," + // 
    "\n('a3', 'b3')," + "\n('a4', 'b4');";
    Assert.assertEquals(formatSql, SQLUtils.toSQLServerString(insertStmt));
}

Example 55 with SQLServerSchemaStatVisitor

use of com.alibaba.druid.sql.dialect.sqlserver.visitor.SQLServerSchemaStatVisitor in project druid by alibaba.

the class SQLServerInsertTest7 method test_0.

public void test_0() throws Exception {
    String sql = "INSERT INTO MMS_SETTLEMENT_COM(handler,handleTime,MID,MERCHANTNAME,TOTALAMT,ACTUALAMT," + "     paymentMoney,STATUS,SERIAL_NUM,REMARKS)" + "SELECT 'admin',getdate(),MID,MERCHANTNAME,SUM(CONVERT(DECIMAL(18,2),isnull(TOTALAMT,0))) " + " TOTALAMT,SUM(CONVERT(DECIMAL(18,2),isnull(ACTUALAMT,0))) ACTUALAMT,SUM(CONVERT(DECIMAL(18,2)," + " isnull(paymentMoney,0))) paymentMoney,2,126,(" + " SELECT REMARKS+'' " + " FROM MMS_SETTLEMENT_COM " + " WHERE MID=A.MID " + " FOR XML PATH('')" + ") AS REMARKS FROM MMS_SETTLEMENT_COM A WHERE ID IN (304,305,306,297,108,184) GROUP BY MID ,MERCHANTNAME";
    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(0, insertStmt.getValuesList().size());
    assertEquals(10, insertStmt.getColumns().size());
    assertEquals(1, statementList.size());
    SQLServerSchemaStatVisitor visitor = new SQLServerSchemaStatVisitor();
    stmt.accept(visitor);
    String formatSql = "INSERT INTO MMS_SETTLEMENT_COM\n" + "\t(handler, handleTime, MID, MERCHANTNAME, TOTALAMT\n" + "\t, ACTUALAMT, paymentMoney, STATUS, SERIAL_NUM, REMARKS)\n" + "SELECT 'admin', getdate(), MID, MERCHANTNAME\n" + "\t, SUM(CONVERT(DECIMAL(18, 2), isnull(TOTALAMT, 0))) AS TOTALAMT\n" + "\t, SUM(CONVERT(DECIMAL(18, 2), isnull(ACTUALAMT, 0))) AS ACTUALAMT\n" + "\t, SUM(CONVERT(DECIMAL(18, 2), isnull(paymentMoney, 0))) AS paymentMoney\n" + "\t, 2, 126\n" + "\t, (\n" + "\t\tSELECT REMARKS + ''\n" + "\t\tFROM MMS_SETTLEMENT_COM\n" + "\t\tWHERE MID = A.MID\n" + "\t\tFOR XML PATH('')\n" + "\t) AS REMARKS\n" + "FROM MMS_SETTLEMENT_COM A\n" + "WHERE ID IN (304, 305, 306, 297, 108, 184)\n" + "GROUP BY MID, MERCHANTNAME";
    assertEquals(formatSql, SQLUtils.toSQLServerString(insertStmt));
}