If there are multiple SQL statements in a SQL file. General SQL Parser will check the syntax of the SQL statements one by one, and continue processing a file even if a parsing error occurs.
If there are multiple SQL syntax errors in a SQL file that includes multiple SQL statements, GSP will identify all the SQL syntax errors in this file so we can fix them all at once.
Take this SQL file for example
CREATE VIEW vsal as select * from emp;
-- syntax error
SELECT Count() total_count,
SUM(sal) total_sal
FROMs scott.emp
WHERE city = 'NYC';
SELECT deptno,
Count() num_emp,
SUM(sal) sal_sum
FROM scott.emp
WHERE city = 'NYC'
GROUP BY deptno;
-- syntax error
SELECT deptno,
Count() num_emp,
SUM(sal) sal_sum
FROM scott.emp
WHEREs city = 'NYC'
GROUP BY deptno;
SELECT a.deptno "Department",
a.num_emp / b.total_count "Employees",
a.sal_sum / b.total_sal "Salary"
FROM (SELECT deptno,
Count() num_emp,
SUM(sal) sal_sum
FROM scott.emp
WHERE city = 'NYC'
GROUP BY deptno) a,
(SELECT Count() total_count,
SUM(sal) total_sal
FROM scott.emp
WHERE city = 'NYC') b
Here is the Java code illustrates how to check syntax of each SQL statement and report all syntax errors at once.
static TStatementList parse1(EDbVendor db, String file) {
int ret = 0;
TGSqlParser sqlparser = new TGSqlParser(db);
System.out.println("Parsing with " + db + " ...");
sqlparser.sqlfilename = file;
ret = sqlparser.parse();
int totalError = 0;
for (int j = 0; j < sqlparser.sqlstatements.size(); j++) {
String syntaxSign = "Success\t";
if (sqlparser.sqlstatements.get(j).getSyntaxErrors().size() > 0){
syntaxSign = "Failed\t";
totalError++;
}
System.out.print(syntaxSign+"sql type: " + sqlparser.sqlstatements.get(j).sqlstatementtype + "("
+ sqlparser.sqlstatements.get(j).getClass().toString() + ")" + ", "
+ sqlparser.sqlstatements.get(j).getStartToken().toString() + "("
+ sqlparser.sqlstatements.get(j).getStartToken().lineNo + ","
+ sqlparser.sqlstatements.get(j).getStartToken().columnNo + ")");
System.out.print("\n");
for(int i=0;i<sqlparser.sqlstatements.get(j).getSyntaxHints().size();i++){
System.out.println( String.format("syntax hint:%s(%d,%d)"
,sqlparser.sqlstatements.get(j).getSyntaxHints().get(i).tokentext
,sqlparser.sqlstatements.get(j).getSyntaxHints().get(i).lineNo
,sqlparser.sqlstatements.get(j).getSyntaxHints().get(i).columnNo)
+sqlparser.sqlstatements.get(j).getSyntaxHints().get(i).hint);
}
}
if (ret == 0) {
System.out.println("sqls: " + sqlparser.sqlstatements.size());
return sqlparser.sqlstatements;
} else {
System.out.println("error: " + sqlparser.getErrormessage());
System.out.println("syntax arrays: "+sqlparser.getSyntaxErrors().size());
System.out.println("total errors: "+totalError+"/"+sqlparser.sqlstatements.size());
return null;
}
}
Report generated after parsing the SQL file:
Success sql type: sstcreateview(class gudusoft.gsqlparser.stmt.TCreateViewSqlStatement), CREATE(1,1)
Failed sql type: sstselect(class gudusoft.gsqlparser.stmt.TSelectSqlStatement), SELECT(5,1)
Success sql type: sstselect(class gudusoft.gsqlparser.stmt.TSelectSqlStatement), SELECT(11,1)
Failed sql type: sstselect(class gudusoft.gsqlparser.stmt.TSelectSqlStatement), SELECT(19,1)
Success sql type: sstselect(class gudusoft.gsqlparser.stmt.TSelectSqlStatement), SELECT(27,1)