// +build go1.9 package mssql import ( "context" "database/sql" "log" "reflect" "testing" "time" ) const ( crateSchema = `create schema TestTVPSchema;` dropSchema = `drop schema TestTVPSchema;` createTVP = ` CREATE TYPE TestTVPSchema.exempleTVP AS TABLE ( message NVARCHAR(100) )` dropTVP = `DROP TYPE TestTVPSchema.exempleTVP;` procedureWithTVP = ` CREATE PROCEDURE ExecTVP @param1 TestTVPSchema.exempleTVP READONLY AS BEGIN SET NOCOUNT ON; SELECT * FROM @param1; END; ` dropProcedure = `drop PROCEDURE ExecTVP` execTvp = `exec ExecTVP @param1;` ) type TvptableRow struct { PBinary []byte `db:"p_binary"` PVarchar string `db:"p_varchar"` PVarcharNull *string `db:"p_varcharNull"` PNvarchar string `db:"p_nvarchar"` PNvarcharNull *string `db:"p_nvarcharNull"` PID UniqueIdentifier `db:"p_id"` PIDNull *UniqueIdentifier `db:"p_idNull"` PVarbinary []byte `db:"p_varbinary"` PTinyint int8 `db:"p_tinyint"` PTinyintNull *int8 `db:"p_tinyintNull"` PSmallint int16 `db:"p_smallint"` PSmallintNull *int16 `db:"p_smallintNull"` PInt int32 `db:"p_int"` PIntNull *int32 `db:"p_intNull"` PBigint int64 `db:"p_bigint"` PBigintNull *int64 `db:"p_bigintNull"` PBit bool `db:"p_bit"` PBitNull *bool `db:"p_bitNull"` PFloat32 float32 `db:"p_float32"` PFloatNull32 *float32 `db:"p_floatNull32"` PFloat64 float64 `db:"p_float64"` PFloatNull64 *float64 `db:"p_floatNull64"` DTime time.Time `db:"p_timeNull"` DTimeNull *time.Time `db:"p_time"` Pint int `db:"pInt"` PintNull *int `db:"pIntNull"` } type TvptableRowWithSkipTag struct { PBinary []byte `db:"p_binary"` SkipPBinary []byte `json:"-"` PVarchar string `db:"p_varchar"` SkipPVarchar string `tvp:"-"` PVarcharNull *string `db:"p_varcharNull"` SkipPVarcharNull *string `json:"-" tvp:"-"` PNvarchar string `db:"p_nvarchar"` SkipPNvarchar string `json:"-"` PNvarcharNull *string `db:"p_nvarcharNull"` SkipPNvarcharNull *string `json:"-"` PID UniqueIdentifier `db:"p_id"` SkipPID UniqueIdentifier `json:"-"` PIDNull *UniqueIdentifier `db:"p_idNull"` SkipPIDNull *UniqueIdentifier `tvp:"-"` PVarbinary []byte `db:"p_varbinary"` SkipPVarbinary []byte `json:"-" tvp:"-"` PTinyint int8 `db:"p_tinyint"` SkipPTinyint int8 `tvp:"-"` PTinyintNull *int8 `db:"p_tinyintNull"` SkipPTinyintNull *int8 `tvp:"-" json:"any"` PSmallint int16 `db:"p_smallint"` SkipPSmallint int16 `json:"-"` PSmallintNull *int16 `db:"p_smallintNull"` SkipPSmallintNull *int16 `tvp:"-"` PInt int32 `db:"p_int"` SkipPInt int32 `json:"-"` PIntNull *int32 `db:"p_intNull"` SkipPIntNull *int32 `tvp:"-"` PBigint int64 `db:"p_bigint"` SkipPBigint int64 `tvp:"-"` PBigintNull *int64 `db:"p_bigintNull"` SkipPBigintNull *int64 `json:"any" tvp:"-"` PBit bool `db:"p_bit"` SkipPBit bool `json:"-"` PBitNull *bool `db:"p_bitNull"` SkipPBitNull *bool `json:"-"` PFloat32 float32 `db:"p_float32"` SkipPFloat32 float32 `tvp:"-"` PFloatNull32 *float32 `db:"p_floatNull32"` SkipPFloatNull32 *float32 `tvp:"-"` PFloat64 float64 `db:"p_float64"` SkipPFloat64 float64 `tvp:"-"` PFloatNull64 *float64 `db:"p_floatNull64"` SkipPFloatNull64 *float64 `tvp:"-"` DTime time.Time `db:"p_timeNull"` SkipDTime time.Time `tvp:"-"` DTimeNull *time.Time `db:"p_time"` SkipDTimeNull *time.Time `tvp:"-"` Pint int `db:"p_int_null"` SkipPint int `tvp:"-"` PintNull *int `db:"p_int_"` SkipPintNull *int `tvp:"-"` } func TestTVP(t *testing.T) { checkConnStr(t) SetLogger(testLogger{t}) db, err := sql.Open("sqlserver", makeConnStr(t).String()) if err != nil { t.Fatalf("failed to open driver sqlserver") } defer db.Close() ctx, cancel := context.WithCancel(context.Background()) defer cancel() sqltextcreatetable := ` CREATE TYPE tvptable AS TABLE ( p_binary BINARY(3), p_varchar VARCHAR(500), p_varcharNull VARCHAR(500), p_nvarchar NVARCHAR(100), p_nvarcharNull NVARCHAR(100), p_id UNIQUEIDENTIFIER, p_idNull UNIQUEIDENTIFIER, p_varbinary VARBINARY(MAX), p_tinyint TINYINT, p_tinyintNull TINYINT, p_smallint SMALLINT, p_smallintNull SMALLINT, p_int INT, p_intNull INT, p_bigint BIGINT, p_bigintNull BIGINT, p_bit BIT, p_bitNull BIT, p_float32 FLOAT, p_floatNull32 FLOAT, p_float64 FLOAT, p_floatNull64 FLOAT, p_time datetime2, p_timeNull datetime2, pInt INT, pIntNull INT ); ` sqltextdroptable := `DROP TYPE tvptable;` sqltextcreatesp := ` CREATE PROCEDURE spwithtvp @param1 tvptable READONLY, @param2 tvptable READONLY, @param3 NVARCHAR(10) AS BEGIN SET NOCOUNT ON; SELECT * FROM @param1; SELECT * FROM @param2; SELECT @param3; END;` sqltextdropsp := `DROP PROCEDURE spwithtvp;` db.ExecContext(ctx, sqltextdropsp) db.ExecContext(ctx, sqltextdroptable) _, err = db.ExecContext(ctx, sqltextcreatetable) if err != nil { t.Fatal(err) } defer db.ExecContext(ctx, sqltextdroptable) _, err = db.ExecContext(ctx, sqltextcreatesp) if err != nil { t.Fatal(err) } defer db.ExecContext(ctx, sqltextdropsp) varcharNull := "aaa" nvarchar := "bbb" bytesMock := []byte("ddd") i8 := int8(1) i16 := int16(2) i32 := int32(3) i64 := int64(4) i := int(5) bFalse := false floatValue64 := 0.123 floatValue32 := float32(-10.123) // SQL Server's datetime2 has maximum precision of 7 digits, so for end-to-end // equality comparison we must not test with any finer resolution than 100 nsec. datetime2Value := time.Date(2020, 8, 26, 23, 59, 39, 100, time.UTC) param1 := []TvptableRow{ { PBinary: []byte("ccc"), PVarchar: varcharNull, PNvarchar: nvarchar, PID: UniqueIdentifier{0x01, 0x23, 0x45, 0x67, 0x89, 0xAB, 0xCD, 0xEF, 0x01, 0x23, 0x45, 0x67, 0x89, 0xAB, 0xCD, 0xEF}, PVarbinary: bytesMock, PTinyint: i8, PSmallint: i16, PInt: i32, PBigint: i64, PBit: bFalse, PFloat32: floatValue32, PFloat64: floatValue64, DTime: datetime2Value, Pint: 355, }, { PBinary: []byte("www"), PVarchar: "eee", PNvarchar: "lll", PID: UniqueIdentifier{0x01, 0x23, 0x45, 0x67, 0x89, 0xAB, 0xCD, 0xEF, 0x01, 0x23, 0x45, 0x67, 0x89, 0xAB, 0xCD, 0xEF}, PVarbinary: []byte("zzz"), PTinyint: 5, PSmallint: 16000, PInt: 20000000, PBigint: 2000000020000000, PBit: true, PFloat32: -123.45, PFloat64: -123.45, DTime: time.Date(2001, 11, 16, 23, 59, 39, 0, time.UTC), Pint: 455, }, { PBinary: nil, PVarcharNull: &varcharNull, PNvarcharNull: &nvarchar, PIDNull: &UniqueIdentifier{0x01, 0x23, 0x45, 0x67, 0x89, 0xAB, 0xCD, 0xEF, 0x01, 0x23, 0x45, 0x67, 0x89, 0xAB, 0xCD, 0xEF}, PTinyintNull: &i8, PSmallintNull: &i16, PIntNull: &i32, PBigintNull: &i64, PBitNull: &bFalse, PFloatNull32: &floatValue32, PFloatNull64: &floatValue64, DTime: datetime2Value, DTimeNull: &datetime2Value, PintNull: &i, }, { PBinary: []byte("www"), PVarchar: "eee", PNvarchar: "lll", PIDNull: &UniqueIdentifier{}, PVarbinary: []byte("zzz"), PTinyint: 5, PSmallint: 16000, PInt: 20000000, PBigint: 2000000020000000, PBit: true, PFloat64: 123.45, DTime: time.Date(2001, 11, 16, 23, 59, 39, 0, time.UTC), PVarcharNull: &varcharNull, PNvarcharNull: &nvarchar, PTinyintNull: &i8, PSmallintNull: &i16, PIntNull: &i32, PBigintNull: &i64, PBitNull: &bFalse, PFloatNull32: &floatValue32, PFloatNull64: &floatValue64, DTimeNull: &datetime2Value, PintNull: &i, }, } tvpType := TVP{ TypeName: "tvptable", Value: param1, } tvpTypeEmpty := TVP{ TypeName: "tvptable", Value: []TvptableRow{}, } rows, err := db.QueryContext(ctx, "exec spwithtvp @param1, @param2, @param3", sql.Named("param1", tvpType), sql.Named("param2", tvpTypeEmpty), sql.Named("param3", "test"), ) if err != nil { t.Fatal(err) } var result1 []TvptableRow for rows.Next() { var val TvptableRow err := rows.Scan( &val.PBinary, &val.PVarchar, &val.PVarcharNull, &val.PNvarchar, &val.PNvarcharNull, &val.PID, &val.PIDNull, &val.PVarbinary, &val.PTinyint, &val.PTinyintNull, &val.PSmallint, &val.PSmallintNull, &val.PInt, &val.PIntNull, &val.PBigint, &val.PBigintNull, &val.PBit, &val.PBitNull, &val.PFloat32, &val.PFloatNull32, &val.PFloat64, &val.PFloatNull64, &val.DTime, &val.DTimeNull, &val.Pint, &val.PintNull, ) if err != nil { t.Fatalf("scan failed with error: %s", err) } result1 = append(result1, val) } if !reflect.DeepEqual(param1, result1) { t.Logf("expected: %+v", param1) t.Logf("actual: %+v", result1) t.Errorf("first resultset did not match param1") } if !rows.NextResultSet() { t.Errorf("second resultset did not exist") } if rows.Next() { t.Errorf("second resultset was not empty") } if !rows.NextResultSet() { t.Errorf("third resultset did not exist") } if !rows.Next() { t.Errorf("third resultset was empty") } var result3 string if err := rows.Scan(&result3); err != nil { t.Errorf("error scanning third result set: %s", err) } if result3 != "test" { t.Errorf("third result set had wrong value expected: %s actual: %s", "test", result3) } } func TestTVP_WithTag(t *testing.T) { checkConnStr(t) SetLogger(testLogger{t}) db, err := sql.Open("sqlserver", makeConnStr(t).String()) if err != nil { t.Fatalf("failed to open driver sqlserver") } defer db.Close() ctx, cancel := context.WithCancel(context.Background()) defer cancel() sqltextcreatetable := ` CREATE TYPE tvptable AS TABLE ( p_binary BINARY(3), p_varchar VARCHAR(500), p_varcharNull VARCHAR(500), p_nvarchar NVARCHAR(100), p_nvarcharNull NVARCHAR(100), p_id UNIQUEIDENTIFIER, p_idNull UNIQUEIDENTIFIER, p_varbinary VARBINARY(MAX), p_tinyint TINYINT, p_tinyintNull TINYINT, p_smallint SMALLINT, p_smallintNull SMALLINT, p_int INT, p_intNull INT, p_bigint BIGINT, p_bigintNull BIGINT, p_bit BIT, p_bitNull BIT, p_float32 FLOAT, p_floatNull32 FLOAT, p_float64 FLOAT, p_floatNull64 FLOAT, p_time datetime2, p_timeNull datetime2, pInt INT, pIntNull INT ); ` sqltextdroptable := `DROP TYPE tvptable;` sqltextcreatesp := ` CREATE PROCEDURE spwithtvp @param1 tvptable READONLY, @param2 tvptable READONLY, @param3 NVARCHAR(10) AS BEGIN SET NOCOUNT ON; SELECT * FROM @param1; SELECT * FROM @param2; SELECT @param3; END;` sqltextdropsp := `DROP PROCEDURE spwithtvp;` db.ExecContext(ctx, sqltextdropsp) db.ExecContext(ctx, sqltextdroptable) _, err = db.ExecContext(ctx, sqltextcreatetable) if err != nil { t.Fatal(err) } defer db.ExecContext(ctx, sqltextdroptable) _, err = db.ExecContext(ctx, sqltextcreatesp) if err != nil { t.Fatal(err) } defer db.ExecContext(ctx, sqltextdropsp) varcharNull := "aaa" nvarchar := "bbb" bytesMock := []byte("ddd") i8 := int8(1) i16 := int16(2) i32 := int32(3) i64 := int64(4) i := int(355) bFalse := false floatValue64 := 0.123 floatValue32 := float32(-10.123) // SQL Server's datetime2 has maximum precision of 7 digits, so for end-to-end // equality comparison we must not test with any finer resolution than 100 nsec. datetime2Value := time.Date(2020, 8, 26, 23, 59, 39, 100, time.UTC) param1 := []TvptableRowWithSkipTag{ { PBinary: []byte("ccc"), PVarchar: varcharNull, PNvarchar: nvarchar, PID: UniqueIdentifier{0x01, 0x23, 0x45, 0x67, 0x89, 0xAB, 0xCD, 0xEF, 0x01, 0x23, 0x45, 0x67, 0x89, 0xAB, 0xCD, 0xEF}, PVarbinary: bytesMock, PTinyint: i8, PSmallint: i16, PInt: i32, PBigint: i64, PBit: bFalse, PFloat32: floatValue32, PFloat64: floatValue64, DTime: datetime2Value, Pint: i, PintNull: &i, }, { PBinary: []byte("www"), PVarchar: "eee", PNvarchar: "lll", PID: UniqueIdentifier{0x01, 0x23, 0x45, 0x67, 0x89, 0xAB, 0xCD, 0xEF, 0x01, 0x23, 0x45, 0x67, 0x89, 0xAB, 0xCD, 0xEF}, PVarbinary: []byte("zzz"), PTinyint: 5, PSmallint: 16000, PInt: 20000000, PBigint: 2000000020000000, PBit: true, PFloat32: -123.45, PFloat64: -123.45, DTime: time.Date(2001, 11, 16, 23, 59, 39, 0, time.UTC), Pint: 3669, PintNull: &i, }, { PBinary: nil, PVarcharNull: &varcharNull, PNvarcharNull: &nvarchar, PIDNull: &UniqueIdentifier{0x01, 0x23, 0x45, 0x67, 0x89, 0xAB, 0xCD, 0xEF, 0x01, 0x23, 0x45, 0x67, 0x89, 0xAB, 0xCD, 0xEF}, PTinyintNull: &i8, PSmallintNull: &i16, PIntNull: &i32, PBigintNull: &i64, PBitNull: &bFalse, PFloatNull32: &floatValue32, PFloatNull64: &floatValue64, DTime: datetime2Value, DTimeNull: &datetime2Value, Pint: 969, }, { PBinary: []byte("www"), PVarchar: "eee", PNvarchar: "lll", PIDNull: &UniqueIdentifier{}, PVarbinary: []byte("zzz"), PTinyint: 5, PSmallint: 16000, PInt: 20000000, PBigint: 2000000020000000, PBit: true, PFloat64: 123.45, DTime: time.Date(2001, 11, 16, 23, 59, 39, 0, time.UTC), PVarcharNull: &varcharNull, PNvarcharNull: &nvarchar, PTinyintNull: &i8, PSmallintNull: &i16, PIntNull: &i32, PBigintNull: &i64, PBitNull: &bFalse, PFloatNull32: &floatValue32, PFloatNull64: &floatValue64, DTimeNull: &datetime2Value, PintNull: &i, }, } tvpType := TVP{ TypeName: "tvptable", Value: param1, } tvpTypeEmpty := TVP{ TypeName: "tvptable", Value: []TvptableRowWithSkipTag{}, } rows, err := db.QueryContext(ctx, "exec spwithtvp @param1, @param2, @param3", sql.Named("param1", tvpType), sql.Named("param2", tvpTypeEmpty), sql.Named("param3", "test"), ) if err != nil { t.Fatal(err) } var result1 []TvptableRowWithSkipTag for rows.Next() { var val TvptableRowWithSkipTag err := rows.Scan( &val.PBinary, &val.PVarchar, &val.PVarcharNull, &val.PNvarchar, &val.PNvarcharNull, &val.PID, &val.PIDNull, &val.PVarbinary, &val.PTinyint, &val.PTinyintNull, &val.PSmallint, &val.PSmallintNull, &val.PInt, &val.PIntNull, &val.PBigint, &val.PBigintNull, &val.PBit, &val.PBitNull, &val.PFloat32, &val.PFloatNull32, &val.PFloat64, &val.PFloatNull64, &val.DTime, &val.DTimeNull, &val.Pint, &val.PintNull, ) if err != nil { t.Fatalf("scan failed with error: %s", err) } result1 = append(result1, val) } if !reflect.DeepEqual(param1, result1) { t.Logf("expected: %+v", param1) t.Logf("actual: %+v", result1) t.Errorf("first resultset did not match param1") } if !rows.NextResultSet() { t.Errorf("second resultset did not exist") } if rows.Next() { t.Errorf("second resultset was not empty") } if !rows.NextResultSet() { t.Errorf("third resultset did not exist") } if !rows.Next() { t.Errorf("third resultset was empty") } var result3 string if err := rows.Scan(&result3); err != nil { t.Errorf("error scanning third result set: %s", err) } if result3 != "test" { t.Errorf("third result set had wrong value expected: %s actual: %s", "test", result3) } } type TvpExample struct { Message string } func TestTVPSchema(t *testing.T) { checkConnStr(t) SetLogger(testLogger{t}) conn, err := sql.Open("sqlserver", makeConnStr(t).String()) if err != nil { log.Fatal("Open connection failed:", err.Error()) } defer conn.Close() _, err = conn.Exec(crateSchema) if err != nil { log.Println(err) return } defer conn.Exec(dropSchema) _, err = conn.Exec(createTVP) if err != nil { log.Println(err) return } defer conn.Exec(dropTVP) _, err = conn.Exec(procedureWithTVP) if err != nil { log.Println(err) return } defer conn.Exec(dropProcedure) exempleData := []TvpExample{ { Message: "Hello", }, { Message: "World", }, { Message: "TVP", }, } tvpType := TVP{ TypeName: "TestTVPSchema.exempleTVP", Value: exempleData, } rows, err := conn.Query(execTvp, sql.Named("param1", tvpType), ) if err != nil { log.Println(err) return } tvpResult := make([]TvpExample, 0) for rows.Next() { tvpExemple := TvpExample{} err = rows.Scan(&tvpExemple.Message) if err != nil { log.Println(err) return } tvpResult = append(tvpResult, tvpExemple) } log.Println(tvpResult) } func TestTVPObject(t *testing.T) { checkConnStr(t) SetLogger(testLogger{t}) conn, err := sql.Open("sqlserver", makeConnStr(t).String()) if err != nil { log.Fatal("Open connection failed:", err.Error()) } defer conn.Close() tests := []struct { name string tvp TVP wantErr bool }{ { name: "empty name", wantErr: true, tvp: TVP{TypeName: ""}, }, { name: "value is wrong type", wantErr: true, tvp: TVP{TypeName: "type", Value: "wrong type"}, }, { name: "tvp type is wrong", wantErr: true, tvp: TVP{TypeName: "[type", Value: []TvpExample{{}}}, }, { name: "tvp type is wrong", wantErr: true, tvp: TVP{TypeName: "[type", Value: []TestFieldsUnsupportedTypes{{}}}, }, } for _, tt := range tests { t.Run(tt.name, func(t *testing.T) { _, err := conn.Exec("somequery", tt.tvp) if (err != nil) != tt.wantErr { t.Errorf("TVP.encode() error = %v, wantErr %v", err, tt.wantErr) return } }) } }