MSQQL Server quotes should be [ ] instead of " " #157

New Issue

lunny · 2014-08-17T23:10:10Z

lasley commented

2014-08-17 23:10:10 +00:00

(Migrated from github.com)

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

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: ``` sql CREATE TABLE [test_quote] ( test_col INT, ); GO ``` Xorm: ``` golang 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: ``` sql 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 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 commented

2014-08-18 02:06:24 +00:00

A good idea. Expecting your pull request.

lasley commented

2014-09-05 14:49:40 +00:00

(Migrated from github.com)

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 commented

2014-09-05 23:50:09 +00:00

@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 commented

2014-09-06 16:00:48 +00:00

(Migrated from github.com)

@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 commented

2014-09-07 01:09:13 +00:00

@dlasley Of course.

nashtsai commented

2014-09-27 13:38:06 +00:00

(Migrated from github.com)

@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 commented

2014-09-30 02:45:58 +00:00

(Migrated from github.com)

@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

lunny referenced a pull request that will close this issue

2020-02-21 03:35:29 +00:00

Fix mssql quote #1535

lunny modified the milestone from 0.8.2 to 1.1.1

2020-02-21 12:41:51 +00:00

lunny closed this issue

2020-02-24 10:03:52 +00:00

lunny modified the milestone from 1.1.1 to 1.0.0