MSQQL Server quotes should be [ ] instead of " "

[lasley]

MSSQL Server allows you to quote table/db names with ", but it is finnicky on Linux systems using FreeTDS. My recommended fix is to use [ ] quoting instead. I will upload a patch once I finish.

I also have a test case below, the issue is on Linux UnixODBC using FreeTDS driver with SQL Server 2008.

TEST CASE

In sql:

CREATE TABLE [test_quote] (
  test_col INT,
);
GO

Xorm:

type TestQuote struct{
  TestCol int
}
func main(){
  db, err := xorm.NewEngine("odbc", "DSN= ;DATABASE= ;Uid= ;Pwd= ")
  db.ShowSQL = true
  test := make([]TestQuote, 0)
  err := db.Find(&test)
}

SQL Generated:

SELECT "test_col" FROM "test_quote"

Error Generated:

[SQLExecute: {42000} [FreeTDS][SQL Server]Incorrect syntax near 'test_quote'.]

Issue replicated within iSql Console - Also notice the col value when I fix the table name but not the cols:

SQL> SELECT "test_col" FROM "test_quote";
[37000][unixODBC][FreeTDS][SQL Server]Incorrect syntax near 'test_quote'.
[ISQL]ERROR: Could not SQLExecute

SQL> SELECT "test_col" FROM test_quote;
+------+
|      |
+------+
| test_col|
+------+
SQLRowCount returns 1
1 rows fetched

SQL> SELECT test_col FROM test_quote;
+------------+
| test_col      |
+------------+
| 1          |
+------------+
SQLRowCount returns 1
1 rows fetched

SQL> SELECT [test_col] FROM [test_quote];
+------------+
| test_col      |
+------------+
| 1          |
+------------+
SQLRowCount returns 1
1 rows fetched

[lunny]

A good idea. Expecting your pull request.

[lasley]

I'm still working on this, it is slightly more complex than I was originally expecting due to the fact that the quote characters are different on each side. With my current solution, this minor change affects almost every file in the repo; so I am trying to find something a bit more creative.

[lunny]

@dlasley, We have a new plan that will avoid most of quote strings. The quote only display before or after a reserved word. So I think maybe you can continue your work after our changes. @nashtsai

[lasley]

@nashtsai - Sounds great, I can't wait to see the changes. Would you mind updating this thread when complete so that I may resume any work that will be necessary to complete this?

[lunny]

@dlasley Of course.

[nashtsai]

@dlasley, sorry about things has gone slow recently, I am not able to verify MSSQL myself, would you mind switch to tidy-up/quotes branch for "core", "xorm", and "tests" repos to check it out

[lasley]

@nashtsai - no worries, we all get busy! I have pulled the branches and tests are passing from the Linux box. Thanks for the hard work