support sqlserver parameters syntax

marpio · marpio · commit fb9af317362c · 2018-04-15T20:56:12.000+02:00
diff --git a/bind.go b/bind.go
@@ -16,6 +16,7 @@ const (
 	QUESTION
 	DOLLAR
 	NAMED
+	AT
 )
 
 // BindType returns the bindtype for a given database given a drivername.
@@ -29,6 +30,8 @@ func BindType(driverName string) int {
 		return QUESTION
 	case "oci8", "ora", "goracle":
 		return NAMED
+	case "sqlserver":
+		return AT
 	}
 	return UNKNOWN
 }
@@ -56,6 +59,8 @@ func Rebind(bindType int, query string) string {
 			rqb = append(rqb, '$')
 		case NAMED:
 			rqb = append(rqb, ':', 'a', 'r', 'g')
+		case AT:
+			rqb = append(rqb, '@', 'p')
 		}
 
 		j++
diff --git a/named.go b/named.go
@@ -287,6 +287,12 @@ func compileNamedQuery(qs []byte, bindType int) (query string, names []string, e
 					rebound = append(rebound, byte(b))
 				}
 				currentVar++
+			case AT:
+				rebound = append(rebound, '@', 'p')
+				for _, b := range strconv.Itoa(currentVar) {
+					rebound = append(rebound, byte(b))
+				}
+				currentVar++
 			}
 			// add this byte to string unless it was not part of the name
 			if i != last {
diff --git a/named_test.go b/named_test.go
@@ -7,14 +7,15 @@ import (
 
 func TestCompileQuery(t *testing.T) {
 	table := []struct {
-		Q, R, D, N string
-		V          []string
+		Q, R, D, T, N string
+		V             []string
 	}{
 		// basic test for named parameters, invalid char ',' terminating
 		{
 			Q: `INSERT INTO foo (a,b,c,d) VALUES (:name, :age, :first, :last)`,
 			R: `INSERT INTO foo (a,b,c,d) VALUES (?, ?, ?, ?)`,
 			D: `INSERT INTO foo (a,b,c,d) VALUES ($1, $2, $3, $4)`,
+			T: `INSERT INTO foo (a,b,c,d) VALUES (@p1, @p2, @p3, @p4)`,
 			N: `INSERT INTO foo (a,b,c,d) VALUES (:name, :age, :first, :last)`,
 			V: []string{"name", "age", "first", "last"},
 		},
@@ -23,20 +24,23 @@ func TestCompileQuery(t *testing.T) {
 			Q: `SELECT * FROM a WHERE first_name=:name1 AND last_name=:name2`,
 			R: `SELECT * FROM a WHERE first_name=? AND last_name=?`,
 			D: `SELECT * FROM a WHERE first_name=$1 AND last_name=$2`,
+			T: `SELECT * FROM a WHERE first_name=@p1 AND last_name=@p2`,
 			N: `SELECT * FROM a WHERE first_name=:name1 AND last_name=:name2`,
 			V: []string{"name1", "name2"},
 		},
 		{
 			Q: `SELECT "::foo" FROM a WHERE first_name=:name1 AND last_name=:name2`,
 			R: `SELECT ":foo" FROM a WHERE first_name=? AND last_name=?`,
 			D: `SELECT ":foo" FROM a WHERE first_name=$1 AND last_name=$2`,
+			T: `SELECT ":foo" FROM a WHERE first_name=@p1 AND last_name=@p2`,
 			N: `SELECT ":foo" FROM a WHERE first_name=:name1 AND last_name=:name2`,
 			V: []string{"name1", "name2"},
 		},
 		{
 			Q: `SELECT 'a::b::c' || first_name, '::::ABC::_::' FROM person WHERE first_name=:first_name AND last_name=:last_name`,
 			R: `SELECT 'a:b:c' || first_name, '::ABC:_:' FROM person WHERE first_name=? AND last_name=?`,
 			D: `SELECT 'a:b:c' || first_name, '::ABC:_:' FROM person WHERE first_name=$1 AND last_name=$2`,
+			T: `SELECT 'a:b:c' || first_name, '::ABC:_:' FROM person WHERE first_name=@p1 AND last_name=@p2`,
 			N: `SELECT 'a:b:c' || first_name, '::ABC:_:' FROM person WHERE first_name=:first_name AND last_name=:last_name`,
 			V: []string{"first_name", "last_name"},
 		},
@@ -74,6 +78,11 @@ func TestCompileQuery(t *testing.T) {
 			t.Errorf("\nexpected: `%s`\ngot:      `%s`", test.D, qd)
 		}
 
+		qt, _, _ := compileNamedQuery([]byte(test.Q), AT)
+		if qt != test.T {
+			t.Errorf("\nexpected: `%s`\ngot:      `%s`", test.T, qt)
+		}
+
 		qq, _, _ := compileNamedQuery([]byte(test.Q), NAMED)
 		if qq != test.N {
 			t.Errorf("\nexpected: `%s`\ngot:      `%s`\n(len: %d vs %d)", test.N, qq, len(test.N), len(qq))
diff --git a/sqlx_test.go b/sqlx_test.go
@@ -1320,6 +1320,17 @@ func TestRebind(t *testing.T) {
 		t.Errorf("q2 failed")
 	}
 
+	s1 = Rebind(AT, q1)
+	s2 = Rebind(AT, q2)
+
+	if s1 != `INSERT INTO foo (a, b, c, d, e, f, g, h, i) VALUES (@p1, @p2, @p3, @p4, @p5, @p6, @p7, @p8, @p9, @p10)` {
+		t.Errorf("q1 failed")
+	}
+
+	if s2 != `INSERT INTO foo (a, b, c) VALUES (@p1, @p2, "foo"), ("Hi", @p3, @p4)` {
+		t.Errorf("q2 failed")
+	}
+
 	s1 = Rebind(NAMED, q1)
 	s2 = Rebind(NAMED, q2)
 

Original file line number	Diff line number	Diff line change
`@@ -16,6 +16,7 @@ const (`
`16`	`16`	`QUESTION`
`17`	`17`	`DOLLAR`
`18`	`18`	`NAMED`
	`19`	`+ AT`
`19`	`20`	`)`
`20`	`21`
`21`	`22`	`// BindType returns the bindtype for a given database given a drivername.`
`@@ -29,6 +30,8 @@ func BindType(driverName string) int {`
`29`	`30`	`return QUESTION`
`30`	`31`	`case "oci8", "ora", "goracle":`
`31`	`32`	`return NAMED`
	`33`	`+ case "sqlserver":`
	`34`	`+ return AT`
`32`	`35`	`}`
`33`	`36`	`return UNKNOWN`
`34`	`37`	`}`
`@@ -56,6 +59,8 @@ func Rebind(bindType int, query string) string {`
`56`	`59`	`rqb = append(rqb, '$')`
`57`	`60`	`case NAMED:`
`58`	`61`	`rqb = append(rqb, ':', 'a', 'r', 'g')`
	`62`	`+ case AT:`
	`63`	`+ rqb = append(rqb, '@', 'p')`
`59`	`64`	`}`
`60`	`65`
`61`	`66`	`j++`
Original file line number	Diff line number	Diff line change
`@@ -287,6 +287,12 @@ func compileNamedQuery(qs []byte, bindType int) (query string, names []string, e`
`287`	`287`	`rebound = append(rebound, byte(b))`
`288`	`288`	`}`
`289`	`289`	`currentVar++`
	`290`	`+ case AT:`
	`291`	`+ rebound = append(rebound, '@', 'p')`
	`292`	`+ for _, b := range strconv.Itoa(currentVar) {`
	`293`	`+ rebound = append(rebound, byte(b))`
	`294`	`+ }`
	`295`	`+ currentVar++`
`290`	`296`	`}`
`291`	`297`	`// add this byte to string unless it was not part of the name`
`292`	`298`	`if i != last {`