Repository: snowflakedb/gosnowflake
Pull request #421 (2021-05-10 20:42:01)

SNOW-292862 Additional Array Bind Types
### Description
Extension of array binds. Adding support for timestamp, time, date and byte array bindings.

### 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
- [x] Extended the README / documentation, if necessary



Patch:
diff --git a/bind_uploader.go b/bind_uploader.go
index c3fbd00..08ad6a3 100644
--- a/bind_uploader.go
+++ b/bind_uploader.go
@@ -125,7 +125,7 @@ func (bu *bindUploader) buildRowsAsBytes(columns []driver.NamedValue) ([][]byte,
 		}
 	}
 
-	_, column := snowflakeArrayToString(&columns[0])
+	_, column := snowflakeArrayToString(&columns[0], true)
 	numRows := len(column)
 	csvRows := make([][]byte, 0)
 	rows := make([][]string, 0)
@@ -134,10 +134,10 @@ func (bu *bindUploader) buildRowsAsBytes(columns []driver.NamedValue) ([][]byte,
 	}
 
 	for rowIdx := 0; rowIdx < numRows; rowIdx++ {
-		rows[rowIdx][0] = column[rowIdx]
+		rows[rowIdx][0] = *column[rowIdx]
 	}
 	for colIdx := 1; colIdx < numColumns; colIdx++ {
-		_, column = snowflakeArrayToString(&columns[colIdx])
+		_, column = snowflakeArrayToString(&columns[colIdx], true)
 		iNumRows := len(column)
 		if iNumRows != numRows {
 			return nil, &SnowflakeError{
@@ -147,7 +147,7 @@ func (bu *bindUploader) buildRowsAsBytes(columns []driver.NamedValue) ([][]byte,
 			}
 		}
 		for rowIdx := 0; rowIdx < numRows; rowIdx++ {
-			rows[rowIdx][colIdx] = column[rowIdx] // length of column = number of rows
+			rows[rowIdx][colIdx] = *column[rowIdx] // length of column = number of rows
 		}
 	}
 	for _, row := range rows {
@@ -185,7 +185,7 @@ func getBindValues(bindings []driver.NamedValue) (map[string]execBindParameter,
 			var val interface{}
 			if t == sliceType {
 				// retrieve array binding data
-				t, val = snowflakeArrayToString(&binding)
+				t, val = snowflakeArrayToString(&binding, false)
 			} else {
 				val, err = valueToString(binding.Value, tsmode)
 				if err != nil {
@@ -209,7 +209,7 @@ func arrayBindValueCount(bindValues []driver.NamedValue) int {
 	if !isArrayBind(bindValues) {
 		return 0
 	}
-	_, arr := snowflakeArrayToString(&bindValues[0])
+	_, arr := snowflakeArrayToString(&bindValues[0], false)
 	return len(bindValues) * len(arr)
 }
 
@@ -230,10 +230,22 @@ func supportedArrayBind(nv *driver.NamedValue) bool {
 	case reflect.TypeOf(&intArray{}), reflect.TypeOf(&int32Array{}),
 		reflect.TypeOf(&int64Array{}), reflect.TypeOf(&float64Array{}),
 		reflect.TypeOf(&float32Array{}), reflect.TypeOf(&boolArray{}),
-		reflect.TypeOf(&stringArray{}), reflect.TypeOf(&byteArray{}):
+		reflect.TypeOf(&stringArray{}), reflect.TypeOf(&byteArray{}),
+		reflect.TypeOf(&timestampNtzArray{}), reflect.TypeOf(&timestampLtzArray{}),
+		reflect.TypeOf(&timestampTzArray{}), reflect.TypeOf(&dateArray{}),
+		reflect.TypeOf(&timeArray{}):
 		return true
+	case reflect.TypeOf([]uint8{}):
+		// internal binding ts mode
+		val, _ := nv.Value.([]uint8)
+		if len(val) == 0 {
+			return true // for null binds
+		}
+		if fixedType <= snowflakeType(val[0]) && snowflakeType(val[0]) <= unSupportedType {
+			return true
+		}
+		return false
 	default:
-		// TODO SNOW-292862 date, timestamp, time
 		// TODO SNOW-176486 variant, object, array
 		return false
 	}
diff --git a/converter.go b/converter.go
index 468d49f..ac3e4b8 100644
--- a/converter.go
+++ b/converter.go
@@ -18,6 +18,8 @@ import (
 	"github.com/apache/arrow/go/arrow/decimal128"
 )
 
+const format = "2006-01-02 15:04:05.999999999"
+
 // goTypeToSnowflake translates Go data type to Snowflake data type.
 func goTypeToSnowflake(v driver.Value, tsmode snowflakeType) snowflakeType {
 	switch t := v.(type) {
@@ -490,18 +492,25 @@ func arrowToValue(destcol *[]snowflakeValue, srcColumnMeta execResponseRowType,
 	return err
 }
 
-type intArray []int
-type int32Array []int32
-type int64Array []int64
-type float64Array []float64
-type float32Array []float32
-type boolArray []bool
-type stringArray []string
-type byteArray [][]byte
+type (
+	intArray          []int
+	int32Array        []int32
+	int64Array        []int64
+	float64Array      []float64
+	float32Array      []float32
+	boolArray         []bool
+	stringArray       []string
+	byteArray         [][]byte
+	timestampNtzArray []time.Time
+	timestampLtzArray []time.Time
+	timestampTzArray  []time.Time
+	dateArray         []time.Time
+	timeArray         []time.Time
+)
 
 // Array takes in a column of a row to be inserted via array binding, bulk or
 // otherwise, and converts it into a native snowflake type for binding
-func Array(a interface{}) interface{} {
+func Array(a interface{}, typ ...snowflakeType) interface{} {
 	switch t := a.(type) {
 	case []int:
 		return (*intArray)(&t)
@@ -519,6 +528,24 @@ func Array(a interface{}) interface{} {
 		return (*stringArray)(&t)
 	case [][]byte:
 		return (*byteArray)(&t)
+	case []time.Time:
+		if len(typ) < 1 {
+			return a
+		}
+		switch typ[0] {
+		case timestampNtzType:
+			return (*timestampNtzArray)(&t)
+		case timestampLtzType:
+			return (*timestampLtzArray)(&t)
+		case timestampTzType:
+			return (*timestampTzArray)(&t)
+		case dateType:
+			return (*dateArray)(&t)
+		case timeType:
+			return (*timeArray)(&t)
+		default:
+			return a
+		}
 
 	case *[]int:
 		return (*intArray)(t)
@@ -536,55 +563,142 @@ func Array(a interface{}) interface{} {
 		return (*stringArray)(t)
 	case *[][]byte:
 		return (*byteArray)(t)
+	case *[]time.Time:
+		if len(typ) < 1 {
+			return a
+		}
+		switch typ[0] {
+		case timestampNtzType:
+			return (*timestampNtzArray)(t)
+		case timestampLtzType:
+			return (*timestampLtzArray)(t)
+		case timestampTzType:
+			return (*timestampTzArray)(t)
+		case dateType:
+			return (*dateArray)(t)
+		case timeType:
+			return (*timeArray)(t)
+		default:
+			return a
+		}
 	default:
 		return a
 	}
 }
 
-func snowflakeArrayToString(nv *driver.NamedValue) (snowflakeType, []string) {
+// snowflakeArrayToString converts the array binding to snowflake's native
+// string type. The string value differs whether it's directly bound or
+// uploaded via stream.
+func snowflakeArrayToString(nv *driver.NamedValue, stream bool) (snowflakeType, []*string) {
 	var t snowflakeType
-	var arr []string
+	var arr []*string
 	switch reflect.TypeOf(nv.Value) {
 	case reflect.TypeOf(&intArray{}):
 		t = fixedType
 		a := nv.Value.(*intArray)
 		for _, x := range *a {
-			arr = append(arr, strconv.Itoa(x))
+			v := strconv.Itoa(x)
+			arr = append(arr, &v)
 		}
 	case reflect.TypeOf(&int64Array{}):
 		t = fixedType
 		a := nv.Value.(*int64Array)
 		for _, x := range *a {
-			arr = append(arr, strconv.Itoa(int(x)))
+			v := strconv.FormatInt(x, 10)
+			arr = append(arr, &v)
 		}
 	case reflect.TypeOf(&int32Array{}):
 		t = fixedType
 		a := nv.Value.(*int32Array)
 		for _, x := range *a {
-			arr = append(arr, strconv.Itoa(int(x)))
+			v := strconv.Itoa(int(x))
+			arr = append(arr, &v)
 		}
 	case reflect.TypeOf(&float64Array{}):
 		t = realType
 		a := nv.Value.(*float64Array)
 		for _, x := range *a {
-			arr = append(arr, fmt.Sprintf("%g", x))
+			v := fmt.Sprintf("%g", x)
+			arr = append(arr, &v)
 		}
 	case reflect.TypeOf(&float32Array{}):
 		t = realType
 		a := nv.Value.(*float32Array)
 		for _, x := range *a {
-			arr = append(arr, fmt.Sprintf("%g", x))
+			v := fmt.Sprintf("%g", x)
+			arr = append(arr, &v)
 		}
 	case reflect.TypeOf(&boolArray{}):
 		t = booleanType
 		a := nv.Value.(*boolArray)
 		for _, x := range *a {
-			arr = append(arr, strconv.FormatBool(x))
+			v := strconv.FormatBool(x)
+			arr = append(arr, &v)
 		}
 	case reflect.TypeOf(&stringArray{}):
 		t = textType
 		a := nv.Value.(*stringArray)
-		arr = *a
+		for _, x := range *a {
+			v := x // necessary for address to be not overwritten
+			arr = append(arr, &v)
+		}
+	case reflect.TypeOf(&byteArray{}):
+		t = binaryType
+		a := nv.Value.(*byteArray)
+		for _, x := range *a {
+			v := hex.EncodeToString(x)
+			arr = append(arr, &v)
+		}
+	case reflect.TypeOf(&timestampNtzArray{}):
+		t = timestampNtzType
+		a := nv.Value.(*timestampNtzArray)
+		for _, x := range *a {
+			v := strconv.FormatInt(x.UnixNano(), 10)
+			arr = append(arr, &v)
+		}
+	case reflect.TypeOf(&timestampLtzArray{}):
+		t = timestampLtzType
+		a := nv.Value.(*timestampLtzArray)
+		for _, x := range *a {
+			v := strconv.FormatInt(x.UnixNano(), 10)
+			arr = append(arr, &v)
+		}
+	case reflect.TypeOf(&timestampTzArray{}):
+		t = timestampTzType
+		a := nv.Value.(*timestampTzArray)
+		for _, x := range *a {
+			var v string
+			if stream {
+				v = x.Format(format)
+			} else {
+				_, offset := x.Zone()
+				v = fmt.Sprintf("%v %v", x.UnixNano(), offset/60+1440)
+			}
+			arr = append(arr, &v)
+		}
+	case reflect.TypeOf(&dateArray{}):
+		t = dateType
+		a := nv.Value.(*dateArray)
+		for _, x := range *a {
+			_, offset := x.Zone()
+			x = x.Add(time.Second * time.Duration(offset))
+			v := fmt.Sprintf("%d", x.Unix()*1000)
+			arr = append(arr, &v)
+		}
+	case reflect.TypeOf(&timeArray{}):
+		t = timeType
+		a := nv.Value.(*timeArray)
+		for _, x := range *a {
+			var v string
+			if stream {
+				v = x.Format(format[11:19])
+			} else {
+				h, m, s := x.Clock()
+				tm := int64(h)*int64(time.Hour) + int64(m)*int64(time.Minute) + int64(s)*int64(time.Second) + int64(x.Nanosecond())
+				v = strconv.FormatInt(tm, 10)
+			}
+			arr = append(arr, &v)
+		}
 	default:
 		return unSupportedType, nil
 	}


Test patch:
diff --git a/bindings_test.go b/bindings_test.go
index c065895..7cd0cc3 100644
--- a/bindings_test.go
+++ b/bindings_test.go
@@ -11,10 +11,11 @@ import (
 )
 
 const (
-	createTableSQL = "create or replace table test_prep_statement(c1 INTEGER, " +
-		"c2 FLOAT, c3 BOOLEAN, c4 STRING)"
+	createTableSQL = `create or replace table test_prep_statement(c1 INTEGER,
+		c2 FLOAT, c3 BOOLEAN, c4 STRING, C5 BINARY, C6 TIMESTAMP_NTZ,
+		C7 TIMESTAMP_LTZ, C8 TIMESTAMP_TZ, C9 DATE, C10 TIME)`
 	deleteTableSQL   = "drop table if exists TEST_PREP_STATEMENT"
-	insertSQL        = "insert into TEST_PREP_STATEMENT values(?, ?, ?, ?)"
+	insertSQL        = "insert into TEST_PREP_STATEMENT values(?, ?, ?, ?, ?, ?, ?, ?, ?, ?)"
 	selectAllSQL     = "select * from TEST_PREP_STATEMENT ORDER BY 1"
 	updateSQL        = "update TEST_PREP_STATEMENT set C4 = 'newString' where C1 = ?"
 	deleteSQL        = "delete from TEST_PREP_STATEMENT where C1 = ?"
@@ -67,7 +68,7 @@ func TestBindingUint64(t *testing.T) {
 }
 
 func TestBindingDateTimeTimestamp(t *testing.T) {
-	createDSN("America/Los_Angeles")
+	createDSN(PSTLocation)
 	runTests(t, dsn, func(dbt *DBTest) {
 		expected := time.Now()
 		dbt.mustExec(
@@ -77,12 +78,11 @@ func TestBindingDateTimeTimestamp(t *testing.T) {
 			dbt.Fatal(err.Error())
 		}
 		defer stmt.Close()
-		_, err = stmt.Exec(
+		if _, err = stmt.Exec(
 			DataTypeTimestampNtz, expected,
 			DataTypeTimestampLtz, expected,
 			DataTypeDate, expected,
-			DataTypeTime, expected)
-		if err != nil {
+			DataTypeTime, expected); err != nil {
 			dbt.Fatal(err)
 		}
 		rows := dbt.mustQuery("SELECT ntz,ltz,dt,tm FROM tztest WHERE id=?", 1)
@@ -169,8 +169,7 @@ func TestBindingTimestampTZ(t *testing.T) {
 			dbt.Fatal(err.Error())
 		}
 		defer stmt.Close()
-		_, err = stmt.Exec(DataTypeTimestampTz, expected)
-		if err != nil {
+		if _, err = stmt.Exec(DataTypeTimestampTz, expected); err != nil {
 			dbt.Fatal(err)
 		}
 		rows := dbt.mustQuery("SELECT tz FROM tztest WHERE id=?", 1)
@@ -182,7 +181,6 @@ func TestBindingTimestampTZ(t *testing.T) {
 				dbt.Errorf("returned value didn't match. expected: %v:%v, got: %v:%v",
 					expected.UnixNano(), expected, v.UnixNano(), v)
 			}
-			// fmt.Printf("returned value: %v, %v, %v\n", v, v.UnixNano(), expected.UnixNano())
 		} else {
 			dbt.Error("no data")
 		}
@@ -200,8 +198,7 @@ func TestBindingInterface(t *testing.T) {
 			dbt.Error("failed to query")
 		}
 		var v1, v2, v3, v4, v5, v6 interface{}
-		err = rows.Scan(&v1, &v2, &v3, &v4, &v5, &v6)
-		if err != nil {
+		if err = rows.Scan(&v1, &v2, &v3, &v4, &v5, &v6); err != nil {
 			dbt.Errorf("failed to scan: %#v", err)
 		}
 		var s string
@@ -227,7 +224,7 @@ func TestBindingInterface(t *testing.T) {
 
 func TestBindingArrowInterface(t *testing.T) {
 	runTests(t, dsn, func(dbt *DBTest) {
-		dbt.mustExec(enableArrow)
+		dbt.mustExec(forceArrow)
 		var err error
 		rows := dbt.mustQuery(
 			"SELECT 1.0::NUMBER(30,2) as C1, 2::NUMBER(38,0) AS C2, 't3' AS C3, 4.2::DOUBLE AS C4, 'abcd'::BINARY AS C5, true AS C6")
@@ -236,8 +233,7 @@ func TestBindingArrowInterface(t *testing.T) {
 			dbt.Error("failed to query")
 		}
 		var v1, v2, v3, v4, v5, v6 interface{}
-		err = rows.Scan(&v1, &v2, &v3, &v4, &v5, &v6)
-		if err != nil {
+		if err = rows.Scan(&v1, &v2, &v3, &v4, &v5, &v6); err != nil {
 			dbt.Errorf("failed to scan: %#v", err)
 		}
 		var s1 *big.Float
@@ -267,55 +263,7 @@ func TestBindingArrowInterface(t *testing.T) {
 // TestBindingArray tests basic array binding via the usage of the Array
 // function that converts the passed Golang slice to a Snowflake array type
 func TestBindingArray(t *testing.T) {
-	intArray := []int{1, 2, 3}
-	fltArray := []float64{0.1, 2.34, 5.678}
-	boolArray := []bool{true, false, true}
-	strArray := []string{"test1", "test2", "test3"}
-
-	runTests(t, dsn, func(dbt *DBTest) {
-		dbt.mustExec(createTableSQL)
-		defer dbt.mustExec(deleteTableSQL)
-		dbt.mustExec(insertSQL, Array(&intArray), Array(&fltArray), Array(&boolArray), Array(&strArray))
-		rows := dbt.mustQuery(selectAllSQL)
-		defer rows.Close()
-
-		var v1 int
-		var v2 float64
-		var v3 bool
-		var v4 string
-		if rows.Next() {
-			if err := rows.Scan(&v1, &v2, &v3, &v4); 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() {
-			if err := rows.Scan(&v1, &v2, &v3, &v4); 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() {
-			if err := rows.Scan(&v1, &v2, &v3, &v4); 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")
-		}
-	})
+	testBindingArray(t, false)
 }
 
 // TestBindingBulkArray tests bulk array binding via the usage of the Array
@@ -324,58 +272,89 @@ func TestBindingBulkArray(t *testing.T) {
 	if runningOnGithubAction() {
 		t.Skip("client_stage_array_binding_threshold value is internal")
 	}
+	testBindingArray(t, true)
+}
 
+func testBindingArray(t *testing.T, bulk bool) {
+	tz := time.Now()
+	createDSN(PSTLocation)
 	intArray := []int{1, 2, 3}
 	fltArray := []float64{0.1, 2.34, 5.678}
 	boolArray := []bool{true, false, true}
 	strArray := []string{"test1", "test2", "test3"}
+	byteArray := [][]byte{{0x01, 0x02, 0x03}, {0x04, 0x05, 0x06}, {0x07, 0x08, 0x09}}
+
+	now := time.Now()
+	loc, _ := time.LoadLocation(PSTLocation)
+	ntzArray := []time.Time{now, now.Add(1), now.Add(2)}
+	ltzArray := []time.Time{now.Add(3).In(loc), now.Add(4).In(loc), now.Add(5).In(loc)}
+	tzArray := []time.Time{tz.Add(6).In(loc), tz.Add(7).In(loc), tz.Add(8).In(loc)}
+	dtArray := []time.Time{now.Add(9), now.Add(10), now.Add(11)}
+	tmArray := []time.Time{now.Add(12), now.Add(13), now.Add(14)}
 
 	runTests(t, dsn, func(dbt *DBTest) {
 		dbt.mustExec(createTableSQL)
 		defer dbt.mustExec(deleteTableSQL)
-		if _, err := dbt.db.Exec("ALTER SESSION SET CLIENT_STAGE_ARRAY_BINDING_THRESHOLD = 1"); err != nil {
-			t.Error(err)
+		if bulk {
+			if _, err := dbt.db.Exec("ALTER SESSION SET CLIENT_STAGE_ARRAY_BINDING_THRESHOLD = 1"); err != nil {
+				t.Error(err)
+			}
 		}
 
-		dbt.mustExec(insertSQL, Array(&intArray), Array(&fltArray), Array(&boolArray), Array(&strArray))
+		dbt.mustExec(insertSQL, Array(&intArray), Array(&fltArray),
+			Array(&boolArray), Array(&strArray), Array(&byteArray),
+			Array(&ntzArray, timestampNtzType), Array(&ltzArray, timestampLtzType),
+			Array(&tzArray, timestampTzType), Array(&dtArray, dateType),
+			Array(&tmArray, timeType))
 		rows := dbt.mustQuery(selectAllSQL)
 		defer rows.Close()
 
-		var v1 int
-		var v2 float64
-		var v3 bool
-		var v4 string
-		if rows.Next() {
-			if err := rows.Scan(&v1, &v2, &v3, &v4); err != nil {
+		var v0 int
+		var v1 float64
+		var v2 bool
+		var v3 string
+		var v4 []byte
+		var v5, v6, v7, v8, v9 time.Time
+		cnt := 0
+		for i := 0; rows.Next(); i++ {
+			if err := rows.Scan(&v0, &v1, &v2, &v3, &v4, &v5, &v6, &v7, &v8, &v9); 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)
+			if v0 != intArray[i] {
+				t.Fatalf("failed to fetch. expected %v, got: %v", intArray[i], v0)
 			}
-		} else {
-			t.Error("failed to query")
-		}
-
-		if rows.Next() {
-			if err := rows.Scan(&v1, &v2, &v3, &v4); err != nil {
-				t.Fatal(err)
+			if v1 != fltArray[i] {
+				t.Fatalf("failed to fetch. expected %v, got: %v", fltArray[i], v1)
 			}
-			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)
+			if v2 != boolArray[i] {
+				t.Fatalf("failed to fetch. expected %v, got: %v", boolArray[i], v2)
 			}
-		} else {
-			t.Error("failed to query")
-		}
-
-		if rows.Next() {
-			if err := rows.Scan(&v1, &v2, &v3, &v4); err != nil {
-				t.Fatal(err)
+			if v3 != strArray[i] {
+				t.Fatalf("failed to fetch. expected %v, got: %v", strArray[i], v3)
 			}
-			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)
+			if !bytes.Equal(v4, byteArray[i]) {
+				t.Fatalf("failed to fetch. expected %v, got: %v", byteArray[i], v4)
 			}
-		} else {
-			t.Error("failed to query")
+			if v5.UnixNano() != ntzArray[i].UnixNano() {
+				t.Fatalf("failed to fetch. expected %v, got: %v", ntzArray[i], v5)
+			}
+			if v6.UnixNano() != ltzArray[i].UnixNano() {
+				t.Fatalf("failed to fetch. expected %v, got: %v", ltzArray[i], v6)
+			}
+			if v7.UnixNano() != tzArray[i].UnixNano() {
+				t.Fatalf("failed to fetch. expected %v, got: %v", tzArray[i], v7)
+			}
+			if v8.Year() != dtArray[i].Year() || v8.Month() != dtArray[i].Month() || v8.Day() != dtArray[i].Day() {
+				t.Fatalf("failed to fetch. expected %v, got: %v", dtArray[i], v8)
+			}
+			if v9.Hour() != tmArray[i].Hour() || v9.Minute() != tmArray[i].Minute() || v9.Second() != tmArray[i].Second() {
+				t.Fatalf("failed to fetch. expected %v, got: %v", tmArray[i], v9)
+			}
+			cnt++
+		}
+		if cnt != len(intArray) {
+			t.Fatal("failed to query")
 		}
 	})
+	createDSN("UTC")
 }
diff --git a/converter_test.go b/converter_test.go
index 9f2de53..a1b4baa 100644
--- a/converter_test.go
+++ b/converter_test.go
@@ -201,12 +201,12 @@ func TestArrayToString(t *testing.T) {
 		{in: driver.NamedValue{Value: &stringArray{"foo", "bar", "baz"}}, typ: textType, out: []string{"foo", "bar", "baz"}},
 	}
 	for _, test := range testcases {
-		s, a := snowflakeArrayToString(&test.in)
+		s, a := snowflakeArrayToString(&test.in, false)
 		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] {
+			if *v != test.out[i] {
 				t.Errorf("failed. in: %v, expected: %v, got: %v", test.in, test.out[i], a)
 			}
 		}
diff --git a/driver_test.go b/driver_test.go
index b01a26d..414ba31 100644
--- a/driver_test.go
+++ b/driver_test.go
@@ -40,7 +40,8 @@ var (
 )
 
 const (
-	enableArrow = "ALTER SESSION SET GO_QUERY_RESULT_FORMAT = ARROW_FORCE "
+	forceArrow  = "ALTER SESSION SET GO_QUERY_RESULT_FORMAT = ARROW_FORCE"
+	PSTLocation = "America/Los_Angeles"
 )
 
 // The tests require the following parameters in the environment variables.
@@ -613,7 +614,7 @@ func testInt(t *testing.T, arrow bool) {
 		// SIGNED
 		for _, v := range types {
 			if arrow {
-				dbt.mustExec(enableArrow)
+				dbt.mustExec(forceArrow)
 			}
 			dbt.mustExec("CREATE TABLE test (value " + v + ")")
 			dbt.mustExec("INSERT INTO test VALUES (?)", in)
@@ -657,7 +658,7 @@ func TestArrowBigInt(t *testing.T) {
 	}
 
 	for _, tc := range testcases {
-		dbt.mustExec(enableArrow)
+		dbt.mustExec(forceArrow)
 		rows := dbt.mustQuery(fmt.Sprintf("SELECT %s::NUMBER(%v, %v) AS C", tc.num, tc.prec, tc.sc))
 		if !rows.Next() {
 			dbt.Error("failed to query")
@@ -695,7 +696,7 @@ func testFloat32(t *testing.T, arrow bool) {
 		var rows *RowsExtended
 		for _, v := range types {
 			if arrow {
-				dbt.mustExec(enableArrow)
+				dbt.mustExec(forceArrow)
 			}
 			dbt.mustExec("CREATE TABLE test (value " + v + ")")
 			dbt.mustExec("INSERT INTO test VALUES (?)", in)
@@ -733,7 +734,7 @@ func testFloat64(t *testing.T, arrow bool) {
 		var rows *RowsExtended
 		for _, v := range types {
 			if arrow {
-				dbt.mustExec(enableArrow)
+				dbt.mustExec(forceArrow)
 			}
 			dbt.mustExec("CREATE TABLE test (value " + v + ")")
 			dbt.mustExec("INSERT INTO test VALUES (42.23)")
@@ -771,7 +772,7 @@ func TestArrowBigFloat(t *testing.T) {
 	}
 
 	for _, tc := range testcases {
-		dbt.mustExec(enableArrow)
+		dbt.mustExec(forceArrow)
 		rows := dbt.mustQuery(fmt.Sprintf("SELECT %s::NUMBER(%v, %v) AS C", tc.num, tc.prec, tc.sc))
 		if !rows.Next() {
 			dbt.Error("failed to query")
@@ -907,7 +908,7 @@ func TestVariousTypes(t *testing.T) {
 
 func TestArrowVariousTypes(t *testing.T) {
 	runTests(t, dsn, func(dbt *DBTest) {
-		dbt.mustExec(enableArrow)
+		dbt.mustExec(forceArrow)
 		rows := dbt.mustQuery(
 			"SELECT 1.0::NUMBER(30,2) as C1, 2::NUMBER(38,0) AS C2, 't3' AS C3, 4.2::DOUBLE AS C4, 'abcd'::BINARY AS C5, true AS C6")
 		defer rows.Close()
@@ -1028,7 +1029,7 @@ func TestArrowString(t *testing.T) {
 func testString(t *testing.T, arrow bool) {
 	runTests(t, dsn, func(dbt *DBTest) {
 		if arrow {
-			dbt.mustExec(enableArrow)
+			dbt.mustExec(forceArrow)
 		}
 		types := []string{"CHAR(255)", "VARCHAR(255)", "TEXT", "STRING"}
 		in := "κόσμε üöäßñóùéàâÿœ'îë Árvíztűrő いろはにほへとちりぬるを イロハニホヘト דג סקרן чащах  น่าฟังเอย"
@@ -1172,7 +1173,7 @@ func testSimpleDateTimeTimestampFetch(t *testing.T, arrow bool) {
 	}
 	runTests(t, dsn, func(dbt *DBTest) {
 		if arrow {
-			dbt.mustExec(enableArrow)
+			dbt.mustExec(forceArrow)
 		}
 		for _, f := range fetchTypes {
 			rows := dbt.mustQuery("SELECT CURRENT_DATE(), CURRENT_TIME(), CURRENT_TIMESTAMP()")
@@ -1202,7 +1203,6 @@ func testDateTime(t *testing.T, arrow bool) {
 		}
 		return t.Add(dur)
 	}
-	format := "2006-01-02 15:04:05.999999999"
 	t0 := time.Time{}
 	tstr0 := "0000-00-00 00:00:00.000000000"
 	testcases := []tcDateTimeTimestamp{
@@ -1243,7 +1243,7 @@ func testDateTime(t *testing.T, arrow bool) {
 	}
 	runTests(t, dsn, func(dbt *DBTest) {
 		if arrow {
-			dbt.mustExec(enableArrow)
+			dbt.mustExec(forceArrow)
 		}
 		for _, setups := range testcases {
 			for _, setup := range setups.tests {
@@ -1266,7 +1266,6 @@ func TestArrowTimestampLTZ(t *testing.T) {
 }
 
 func testTimestampLTZ(t *testing.T, arrow bool) {
-	format := "2006-01-02 15:04:05.999999999"
 	// Set session time zone in Los Angeles, same as machine
 	createDSN("America/Los_Angeles")
 	location, err := time.LoadLocation("America/Los_Angeles")
@@ -1317,7 +1316,7 @@ func testTimestampLTZ(t *testing.T, arrow bool) {
 	}
 	runTests(t, dsn, func(dbt *DBTest) {
 		if arrow {
-			dbt.mustExec(enableArrow)
+			dbt.mustExec(forceArrow)
 		}
 		for _, setups := range testcases {
 			for _, setup := range setups.tests {
@@ -1349,7 +1348,6 @@ func testTimestampTZ(t *testing.T, arrow bool) {
 		}
 		return r
 	}
-	format := "2006-01-02 15:04:05.999999999"
 	testcases := []tcDateTimeTimestamp{
 		{
 			dbtype:  "TIMESTAMP_TZ(9)",
@@ -1370,7 +1368,7 @@ func testTimestampTZ(t *testing.T, arrow bool) {
 	}
 	runTests(t, dsn, func(dbt *DBTest) {
 		if arrow {
-			dbt.mustExec(enableArrow)
+			dbt.mustExec(forceArrow)
 		}
 		for _, setups := range testcases {
 			for _, setup := range setups.tests {
@@ -1395,7 +1393,7 @@ func TestArrowNULL(t *testing.T) {
 func testNULL(t *testing.T, arrow bool) {
 	runTests(t, dsn, func(dbt *DBTest) {
 		if arrow {
-			dbt.mustExec(enableArrow)
+			dbt.mustExec(forceArrow)
 		}
 		nullStmt, err := dbt.db.Prepare("SELECT NULL")
 		if err != nil {
@@ -1558,7 +1556,7 @@ func TestArrowVariant(t *testing.T) {
 func testVariant(t *testing.T, arrow bool) {
 	runTests(t, dsn, func(dbt *DBTest) {
 		if arrow {
-			dbt.mustExec(enableArrow)
+			dbt.mustExec(forceArrow)
 		}
 		rows := dbt.mustQuery(`select parse_json('[{"id":1, "name":"test1"},{"id":2, "name":"test2"}]')`)
 		defer rows.Close()
@@ -1585,7 +1583,7 @@ func TestArrowArray(t *testing.T) {
 func testArray(t *testing.T, arrow bool) {
 	runTests(t, dsn, func(dbt *DBTest) {
 		if arrow {
-			dbt.mustExec(enableArrow)
+			dbt.mustExec(forceArrow)
 		}
 		rows := dbt.mustQuery(`select as_array(parse_json('[{"id":1, "name":"test1"},{"id":2, "name":"test2"}]'))`)
 		defer rows.Close()
@@ -1619,7 +1617,7 @@ func TestLargeSetResultWithArrowDecoder(t *testing.T) {
 func testLargeSetResult(t *testing.T, numrows int, arrow bool) {
 	runTests(t, dsn, func(dbt *DBTest) {
 		if arrow {
-			dbt.mustExec(enableArrow)
+			dbt.mustExec(forceArrow)
 		}
 		rows := dbt.mustQuery(fmt.Sprintf("SELECT SEQ8(), RANDSTR(1000, RANDOM()) FROM TABLE(GENERATOR(ROWCOUNT=>%v))", numrows))
 		defer rows.Close()
diff --git a/prepared_statement_test.go b/prepared_statement_test.go
index 853056e..d0c4c9c 100644
--- a/prepared_statement_test.go
+++ b/prepared_statement_test.go
@@ -9,17 +9,18 @@ import (
 // TestPreparedStatement creates a basic prepared statement, inserting values
 // after the statement has been prepared
 func TestPreparedStatement(t *testing.T) {
-
 	runTests(t, dsn, func(dbt *DBTest) {
-		dbt.mustExec(createTableSQL)
+		dbt.mustExec("create or replace table test_prep_statement(c1 INTEGER, c2 FLOAT, c3 BOOLEAN, c4 STRING)")
 		defer dbt.mustExec(deleteTableSQL)
 
 		intArray := []int{1, 2, 3}
 		fltArray := []float64{0.1, 2.34, 5.678}
 		boolArray := []bool{true, false, true}
 		strArray := []string{"test1", "test2", "test3"}
-		stmt := dbt.mustPrepare(insertSQL)
-		stmt.Exec(Array(&intArray), Array(&fltArray), Array(&boolArray), Array(&strArray))
+		stmt := dbt.mustPrepare("insert into TEST_PREP_STATEMENT values(?, ?, ?, ?)")
+		if _, err := stmt.Exec(Array(&intArray), Array(&fltArray), Array(&boolArray), Array(&strArray)); err != nil {
+			t.Fatal(err)
+		}
 		rows := dbt.mustQuery(selectAllSQL)
 		defer rows.Close()