Repository: snowflakedb/gosnowflake
Pull request #330 (2020-09-08 16:33:12)

SNOW-177692 Simple array binding
### Description
Please explain the changes you made here.

### Checklist
- [x] Code compiles correctly
- [x] Run ``make fmt`` to fix inconsistent formats
- [x] Run ``make lint`` to get lint errors and fix all of them
- [x] Created tests which fail without the change (if possible)
- [x] All tests passing
- [ ] Extended the README / documentation, if necessary


sfc-gh-stakeda: Looks good. can you add all of the array types tests?

Patch:
diff --git a/connection.go b/connection.go
index 26dc36d..e8c588c 100644
--- a/connection.go
+++ b/connection.go
@@ -10,6 +10,7 @@ import (
 	"fmt"
 	"github.com/google/uuid"
 	"net/url"
+	"reflect"
 	"strconv"
 	"strings"
 	"sync/atomic"
@@ -87,7 +88,12 @@ func (sc *snowflakeConn) exec(
 					return nil, err
 				}
 			} else {
-				v1, err := valueToString(bindings[i].Value, tsmode)
+				var v1 interface{}
+				if t == "ARRAY" {
+					t, v1 = arrayToString(bindings[i].Value)
+				} else {
+					v1, err = valueToString(bindings[i].Value, tsmode)
+				}
 				if err != nil {
 					return nil, err
 				}
@@ -417,6 +423,16 @@ func (sc *snowflakeConn) Ping(ctx context.Context) error {
 	return err
 }
 
+func (sc *snowflakeConn) CheckNamedValue(nv *driver.NamedValue) error {
+	switch reflect.TypeOf(nv.Value) {
+	case reflect.TypeOf([]int{0}), reflect.TypeOf([]int64{0}), reflect.TypeOf([]float64{0}),
+		reflect.TypeOf([]bool{false}), reflect.TypeOf([]string{""}):
+		return nil
+	default:
+		return driver.ErrSkip
+	}
+}
+
 func (sc *snowflakeConn) populateSessionParameters(parameters []nameValueParameter) {
 	// other session parameters (not all)
 	glog.V(2).Infof("params: %#v", parameters)
diff --git a/converter.go b/converter.go
index e50dc5f..dff7d5f 100644
--- a/converter.go
+++ b/converter.go
@@ -1,4 +1,4 @@
-// Copyright (c) 2017-2019 Snowflake Computing Inc. All right reserved.
+// Copyright (c) 2017-2020 Snowflake Computing Inc. All right reserved.
 
 package gosnowflake
 
@@ -40,6 +40,8 @@ func goTypeToSnowflake(v driver.Value, tsmode string) string {
 			return "TEXT" // not supported dataType
 		}
 		return "CHANGE_TYPE"
+	case []int, []int64, []float64, []bool, []string:
+		return "ARRAY"
 	case time.Time:
 		return tsmode
 	}
@@ -245,6 +247,37 @@ func stringToValue(dest *driver.Value, srcColumnMeta execResponseRowType, srcVal
 	return nil
 }
 
+func arrayToString(v driver.Value) (string, []string) {
+	var t string
+	var arr []string
+	switch a := v.(type) {
+	case []int:
+		t = "FIXED"
+		for _, x := range a {
+			arr = append(arr, strconv.Itoa(x))
+		}
+	case []int64:
+		t = "FIXED"
+		for _, x := range a {
+			arr = append(arr, strconv.Itoa(int(x)))
+		}
+	case []float64:
+		t = "REAL"
+		for _, x := range a {
+			arr = append(arr, fmt.Sprintf("%g", x))
+		}
+	case []bool:
+		t = "BOOLEAN"
+		for _, x := range a {
+			arr = append(arr, strconv.FormatBool(x))
+		}
+	case []string:
+		t = "TEXT"
+		arr = a
+	}
+	return t, arr
+}
+
 var decimalShift = new(big.Int).Exp(big.NewInt(2), big.NewInt(64), nil)
 
 func intToBigFloat(val int64, scale int64) *big.Float {
diff --git a/query.go b/query.go
index dd3c180..7f70ff0 100644
--- a/query.go
+++ b/query.go
@@ -9,8 +9,8 @@ import (
 const arrowFormat = "arrow"
 
 type execBindParameter struct {
-	Type  string  `json:"type"`
-	Value *string `json:"value"`
+	Type  string      `json:"type"`
+	Value interface{} `json:"value"`
 }
 
 type execRequest struct {


Test patch:
diff --git a/converter_test.go b/converter_test.go
index 40f5f7d..bed6457 100644
--- a/converter_test.go
+++ b/converter_test.go
@@ -1,4 +1,4 @@
-// Copyright (c) 2017-2019 Snowflake Computing Inc. All right reserved.
+// Copyright (c) 2017-2020 Snowflake Computing Inc. All right reserved.
 
 package gosnowflake
 
@@ -28,6 +28,7 @@ func TestGoTypeToSnowflake(t *testing.T) {
 		{in: true, tmode: "", out: "BOOLEAN"},
 		{in: "teststring", tmode: "", out: "TEXT"},
 		{in: nil, tmode: "", out: "TEXT"}, // nil is taken as TEXT
+		{in: []int{1}, tmode: "", out: "ARRAY"},
 		{in: DataTypeBinary, tmode: "", out: "CHANGE_TYPE"},
 		{in: DataTypeTimestampLtz, tmode: "", out: "CHANGE_TYPE"},
 		{in: DataTypeTimestampNtz, tmode: "", out: "CHANGE_TYPE"},
@@ -184,6 +185,33 @@ func TestStringToValue(t *testing.T) {
 	}
 }
 
+type tcArrayToString struct {
+	in  interface{}
+	typ string
+	out []string
+}
+
+func TestArrayToString(t *testing.T) {
+	testcases := []tcArrayToString{
+		{in: []int{1, 2}, typ: "FIXED", out: []string{"1", "2"}},
+		{in: []int64{3, 4, 5}, typ: "FIXED", out: []string{"3", "4", "5"}},
+		{in: []float64{6.7}, typ: "REAL", out: []string{"6.7"}},
+		{in: []bool{true, false}, typ: "BOOLEAN", out: []string{"true", "false"}},
+		{in: []string{"foo", "bar", "baz"}, typ: "TEXT", out: []string{"foo", "bar", "baz"}},
+	}
+	for _, test := range testcases {
+		s, a := arrayToString(test.in)
+		if s != test.typ {
+			t.Errorf("failed. in: %v, expected: %v, got: %v", test.in, test.typ, s)
+		}
+		for i, v := range a {
+			if v != test.out[i] {
+				t.Errorf("failed. in: %v, expected: %v, got: %v", test.in, test.out[i], a)
+			}
+		}
+	}
+}
+
 func TestArrowToValue(t *testing.T) {
 	dest := make([]snowflakeValue, 2)
 
diff --git a/driver_test.go b/driver_test.go
index 815d80d..5c043b4 100644
--- a/driver_test.go
+++ b/driver_test.go
@@ -1,4 +1,4 @@
-// Copyright (c) 2017-2019 Snowflake Computing Inc. All right reserved.
+// Copyright (c) 2017-2020 Snowflake Computing Inc. All right reserved.
 
 package gosnowflake
 
@@ -1811,6 +1811,59 @@ func testArray(t *testing.T, arrow bool) {
 	})
 }
 
+func TestArrayBind(t *testing.T) {
+	runTests(t, dsn, func(dbt *DBTest) {
+		dbt.mustExec("create or replace table test(c1 int, c2 float, c3 boolean, c4 string)")
+		intArray := []int{1, 2, 3}
+		fltArray := []float64{0.1, 2.34, 5.678}
+		boolArray := []bool{true, false, true}
+		strArray := []string{"test1", "test2", "test3"}
+		dbt.mustExec(`insert into test values (?, ?, ?, ?)`, intArray, fltArray, boolArray, strArray)
+		rows := dbt.mustQuery("select * from test order by 1")
+		defer rows.Close()
+
+		var v1 int
+		var v2 float64
+		var v3 bool
+		var v4 string
+		if rows.Next() {
+			err := rows.Scan(&v1, &v2, &v3, &v4)
+			if err != nil {
+				t.Fatal(err)
+			}
+			if v1 != 1 && v2 != 0.1 && v3 != true && v4 != "test1" {
+				t.Fatalf("failed to fetch. expected: 1, 0.1, true, test1. got: %v, %v, %v, %v", v1, v2, v3, v4)
+			}
+		} else {
+			t.Error("failed to query")
+		}
+
+		if rows.Next() {
+			err := rows.Scan(&v1, &v2, &v3, &v4)
+			if err != nil {
+				t.Fatal(err)
+			}
+			if v1 != 2 && v2 != 2.34 && v3 != false && v4 != "test2" {
+				t.Fatalf("failed to fetch. expected: 2, 2.34, false, test2. got: %v, %v, %v, %v", v1, v2, v3, v4)
+			}
+		} else {
+			t.Error("failed to query")
+		}
+
+		if rows.Next() {
+			err := rows.Scan(&v1, &v2, &v3, &v4)
+			if err != nil {
+				t.Fatal(err)
+			}
+			if v1 != 3 && v2 != 5.678 && v3 != true && v4 != "test3" {
+				t.Fatalf("failed to fetch. expected: 3, test3. got: %v, %v, %v, %v", v1, v2, v3, v4)
+			}
+		} else {
+			t.Error("failed to query")
+		}
+	})
+}
+
 func TestLargeSetResult(t *testing.T) {
 	CustomJSONDecoderEnabled = false
 	testLargeSetResult(t, 100000, false)