附近有语法错误sys.objects中“

Question

我得到一个错误信息说

com.microsoft.sqlserver.jdbc.SQLServerException：附近有语法错误 'SYS'。

而这通常意味着我输入的SQL查询是错误的或者我缺少语法。而且我很肯定，我使用这个SQL查询是正确的：

public static Vector<Vector<String>> getKeys() throws SQLException { 
    Vector<Vector<String>> vs = new Vector<Vector<String>>(); 
    Connection con = Dal2.getConnection(); 
    Statement stm = con.createStatement(); 
    ResultSet rSet; 
    String sqlString = "SELECT OBJECT_NAME(OBJECT_ID) AS NameofConstraint," 
      +"SCHEMA_NAME(schema_id) AS SchemaName," 
      + "OBJECT_NAME(parent_object_id) AS TableName," 
      +"type_desc AS ConstraintType" 
      +"FROM sys.objects" 
      +"WHERE type_desc IN ('FOREIGN_KEY_CONSTRAINT','PRIMARY_KEY_CONSTRAINT')"; 

    try{ 
     rSet = stm.executeQuery(sqlString); 
     while(rSet.next()){ 
      Vector<String> v = new Vector<String>(); 
      for(int i=1; i <= v.size(); i++){ 
       System.out.println(rSet.getString(i) + "\n"); 
     //     v.add(rSet.getString(i)); 
      } 
      } 
     } 
     catch(Exception e){ 
      e.printStackTrace(); 
     } 
    return vs; 

正如你可以看到我想要打印出来的SQL时，我使用的方法的Java控制台上的结果集。但我不断收到语法错误。我试着在SQL Server中的SQL查询和正常工作：

SELECT OBJECT_NAME(OBJECT_ID) AS NameofConstraint, 
     SCHEMA_NAME(schema_id) AS SchemaName, 
     OBJECT_NAME(parent_object_id) AS TableName, 
     type_desc AS ConstraintType 
FROM sys.objects 
WHERE type_desc IN ('FOREIGN_KEY_CONSTRAINT','PRIMARY_KEY_CONSTRAINT') 

Answer 1

你sqlString缺少空白字符在两个地方为您的查询

String sqlString = "SELECT OBJECT_NAME(OBJECT_ID) AS NameofConstraint," 
     + "SCHEMA_NAME(schema_id) AS SchemaName," 
     + "OBJECT_NAME(parent_object_id) AS TableName," 
     + "type_desc AS ConstraintType " // <-- here 
     + "FROM sys.objects " // <-- here 
     + "WHERE type_desc IN ('FOREIGN_KEY_CONSTRAINT','PRIMARY_KEY_CONSTRAINT')"; 

TL;博士目前您的查询会评估（部分）ConstraintTypeFROM sys.objectsWHERE。

Answer 2

连接后的字符串看起来是这样的：

SELECT OBJECT_NAME(OBJECT_ID) AS NameofConstraint,SCHEMA_NAME(schema_id) AS SchemaName,OBJECT_NAME(parent_object_id) AS TableName,type_desc AS ConstraintTypeFROM sys.objectsWHERE type_desc IN ('FOREIGN_KEY_CONSTRAINT','PRIMARY_KEY_CONSTRAINT') 

注意ConstraintTypeFROM sys.objectsWHERE部分。它缺少空格，导致SQL语法错误。

通常，将每个SQL行的最后一个空格放入最终查询中是个不错的主意。这样您就不必考虑换行符处的空格：

String sqlString = "SELECT OBJECT_NAME(OBJECT_ID) AS NameofConstraint, " // <<== Space 
     +"SCHEMA_NAME(schema_id) AS SchemaName, "      // <<== Space 
     + "OBJECT_NAME(parent_object_id) AS TableName, "     // <<== Space 
     +"type_desc AS ConstraintType "         // <<== Space 
     +"FROM sys.objects "            // <<== Space 
     +"WHERE type_desc IN ('FOREIGN_KEY_CONSTRAINT','PRIMARY_KEY_CONSTRAINT')";