//go:build go1.9 // +build go1.9 package mssql import ( "context" "database/sql" "log" "reflect" "testing" "time" ) func TestTVPGoSQLTypesWithStandardType(t *testing.T) { checkConnStr(t) tl := testLogger{t: t} defer tl.StopLogging() SetLogger(&tl) c := makeConnStr(t).String() db, err := sql.Open("sqlserver", c) if err != nil { t.Fatalf("failed to open driver sqlserver") } defer db.Close() ctx, cancel := context.WithCancel(context.Background()) defer cancel() sqltextcreatetable := ` CREATE TYPE tvpGoSQLTypesWithStandardType AS TABLE ( p_bit BIT, p_bitNull BIT, s_bit BIT, s_bitNull BIT, p_float64 FLOAT, p_floatNull64 FLOAT, s_float64 FLOAT, s_floatNull64 FLOAT, p_bigint BIGINT, p_bigintNull BIGINT, s_bigint BIGINT, s_bigintNull BIGINT, p_nvarchar NVARCHAR(100), p_nvarcharNull NVARCHAR(100), s_nvarchar NVARCHAR(100), s_nvarcharNull NVARCHAR(100) ); ` sqltextdroptable := `DROP TYPE tvpGoSQLTypesWithStandardType;` sqltextcreatesp := ` CREATE PROCEDURE spwithtvpGoSQLTypesWithStandardType @param1 tvpGoSQLTypesWithStandardType READONLY, @param2 tvpGoSQLTypesWithStandardType READONLY, @param3 NVARCHAR(10) AS BEGIN SET NOCOUNT ON; SELECT * FROM @param1; SELECT * FROM @param2; SELECT @param3; END;` type TvpGoSQLTypes struct { PBool sql.NullBool PBoolNull sql.NullBool SBool bool SBoolNull *bool PFloat64 sql.NullFloat64 PFloat64Null sql.NullFloat64 SFloat64 float64 SFloat64Null *float64 PInt64 sql.NullInt64 PInt64Null sql.NullInt64 SInt64 int64 SInt64Null *int64 PString sql.NullString PStringNull sql.NullString SString string SStringNull *string } sqltextdropsp := `DROP PROCEDURE spwithtvpGoSQLTypesWithStandardType;` _, 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) nvarchar := "bbb" i64 := int64(4) bTrue := true floatValue64 := 0.123 param1 := []TvpGoSQLTypes{ { PBool: sql.NullBool{ Bool: true, Valid: true, }, PBoolNull: sql.NullBool{}, PFloat64: sql.NullFloat64{ Float64: 14.33, Valid: true, }, PFloat64Null: sql.NullFloat64{}, PInt64: sql.NullInt64{ Int64: 777, Valid: true, }, PInt64Null: sql.NullInt64{}, PString: sql.NullString{ String: "test=tvp", Valid: true, }, PStringNull: sql.NullString{}, }, { PBool: sql.NullBool{}, PBoolNull: sql.NullBool{}, SBool: true, SBoolNull: nil, PFloat64: sql.NullFloat64{}, PFloat64Null: sql.NullFloat64{}, SFloat64: 1.1, SFloat64Null: nil, PInt64: sql.NullInt64{}, PInt64Null: sql.NullInt64{}, SInt64: 1, SInt64Null: nil, PString: sql.NullString{}, PStringNull: sql.NullString{}, SString: "any", SStringNull: nil, }, { PBool: sql.NullBool{ Bool: false, Valid: true, }, PBoolNull: sql.NullBool{}, SBool: true, SBoolNull: &bTrue, PFloat64: sql.NullFloat64{ Float64: 1.2, Valid: true, }, PFloat64Null: sql.NullFloat64{}, SFloat64: 3.4, SFloat64Null: &floatValue64, PInt64: sql.NullInt64{ Int64: 32423, Valid: true, }, PInt64Null: sql.NullInt64{}, SInt64: 9738847389, SInt64Null: &i64, PString: sql.NullString{ String: "jifdijrgio", Valid: true, }, PStringNull: sql.NullString{}, SString: "geniefkl123", SStringNull: &nvarchar, }, } tvpType := TVP{ TypeName: "tvpGoSQLTypesWithStandardType", Value: param1, } tvpTypeEmpty := TVP{ TypeName: "tvpGoSQLTypesWithStandardType", Value: []TvpGoSQLTypes{}, } rows, err := db.QueryContext(ctx, "exec spwithtvpGoSQLTypesWithStandardType @param1, @param2, @param3", sql.Named("param1", tvpType), sql.Named("param2", tvpTypeEmpty), sql.Named("param3", "test"), ) if err != nil { t.Fatal(err) } var result1 []TvpGoSQLTypes for rows.Next() { var val TvpGoSQLTypes err := rows.Scan( &val.PBool, &val.PBoolNull, &val.SBool, &val.SBoolNull, &val.PFloat64, &val.PFloat64Null, &val.SFloat64, &val.SFloat64Null, &val.PInt64, &val.PInt64Null, &val.SInt64, &val.SInt64Null, &val.PString, &val.PStringNull, &val.SString, &val.SStringNull, ) 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 TestTVPGoSQLTypes(t *testing.T) { checkConnStr(t) tl := testLogger{t: t} defer tl.StopLogging() SetLogger(&tl) c := makeConnStr(t).String() db, err := sql.Open("sqlserver", c) if err != nil { t.Fatalf("failed to open driver sqlserver") } defer db.Close() ctx, cancel := context.WithCancel(context.Background()) defer cancel() sqltextcreatetable := ` CREATE TYPE tvpGoSQLTypes AS TABLE ( p_bit BIT, p_bitNull BIT, p_float64 FLOAT, p_floatNull64 FLOAT, p_bigint BIGINT, p_bigintNull BIGINT, p_nvarchar NVARCHAR(100), p_nvarcharNull NVARCHAR(100) ); ` sqltextdroptable := `DROP TYPE tvpGoSQLTypes;` sqltextcreatesp := ` CREATE PROCEDURE spwithtvpGoSQLTypes @param1 tvpGoSQLTypes READONLY, @param2 tvpGoSQLTypes READONLY, @param3 NVARCHAR(10) AS BEGIN SET NOCOUNT ON; SELECT * FROM @param1; SELECT * FROM @param2; SELECT @param3; END;` type TvpGoSQLTypes struct { PBool sql.NullBool PBoolNull sql.NullBool PFloat64 sql.NullFloat64 PFloat64Null sql.NullFloat64 PInt64 sql.NullInt64 PInt64Null sql.NullInt64 PString sql.NullString PStringNull sql.NullString } sqltextdropsp := `DROP PROCEDURE spwithtvpGoSQLTypes;` _, 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) param1 := []TvpGoSQLTypes{ { PBool: sql.NullBool{ Bool: true, Valid: true, }, PBoolNull: sql.NullBool{}, PFloat64: sql.NullFloat64{ Float64: 14.33, Valid: true, }, PFloat64Null: sql.NullFloat64{}, PInt64: sql.NullInt64{ Int64: 777, Valid: true, }, PInt64Null: sql.NullInt64{}, PString: sql.NullString{ String: "test=tvp", Valid: true, }, PStringNull: sql.NullString{}, }, } tvpType := TVP{ TypeName: "tvpGoSQLTypes", Value: param1, } tvpTypeEmpty := TVP{ TypeName: "tvpGoSQLTypes", Value: []TvpGoSQLTypes{}, } rows, err := db.QueryContext(ctx, "exec spwithtvpGoSQLTypes @param1, @param2, @param3", sql.Named("param1", tvpType), sql.Named("param2", tvpTypeEmpty), sql.Named("param3", "test"), ) if err != nil { t.Fatal(err) } var result1 []TvpGoSQLTypes for rows.Next() { var val TvpGoSQLTypes err := rows.Scan( &val.PBool, &val.PBoolNull, &val.PFloat64, &val.PFloat64Null, &val.PInt64, &val.PInt64Null, &val.PString, &val.PStringNull, ) 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 getNullableUniqueIdentifier(guidConversion bool) *UniqueIdentifier { result := UniqueIdentifier{} if !guidConversion { // if guidConversion is enabled nullable UUIDs can only be accessed using NullUniqueIdentifier result = UniqueIdentifier{0x01, 0x23, 0x45, 0x67, 0x89, 0xAB, 0xCD, 0xEF, 0x01, 0x23, 0x45, 0x67, 0x89, 0xAB, 0xCD, 0xEF} } return &result } func testTVP(t *testing.T, guidConversion bool) { checkConnStr(t) tl := testLogger{t: t} defer tl.StopLogging() SetLogger(&tl) c := makeConnStrSettingGuidConversion(t, guidConversion).String() db, err := sql.Open("sqlserver", c) 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;` 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"` } sqltextdropsp := `DROP PROCEDURE spwithtvp;` _, 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: getNullableUniqueIdentifier(guidConversion), 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) } defer rows.Close() 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_WithGuidConversion(t *testing.T) { testTVP(t, true /*guidConversion*/) } func TestTVP(t *testing.T) { testTVP(t, false /*guidConversion*/) } func testTVP_WithTag(t *testing.T, guidConversion bool) { checkConnStr(t) tl := testLogger{t: t} defer tl.StopLogging() SetLogger(&tl) db, err := sql.Open("sqlserver", makeConnStrSettingGuidConversion(t, guidConversion).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;` 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:"-" 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:"pIntNull"` SkipPint int `tvp:"-"` PintNull *int `db:"pInt"` SkipPintNull *int `tvp:"-"` } 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: getNullableUniqueIdentifier(guidConversion), 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) } defer rows.Close() 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) { 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;` ) checkConnStr(t) tl := testLogger{t: t} defer tl.StopLogging() SetLogger(&tl) 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 { t.Fatal(err) } defer rows.Close() tvpResult := make([]TvpExample, 0) for rows.Next() { tvpExemple := TvpExample{} err = rows.Scan(&tvpExemple.Message) if err != nil { t.Fatal(err) } tvpResult = append(tvpResult, tvpExemple) } log.Println(tvpResult) } func TestTVP_WithTagAndGuidConversion(t *testing.T) { testTVP_WithTag(t, true /*guidConversion*/) } func TestTVP_WithTag(t *testing.T) { testTVP_WithTag(t, false /*guidConversion*/) } func TestTVPObject(t *testing.T) { checkConnStr(t) tl := testLogger{t: t} defer tl.StopLogging() SetLogger(&tl) 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 } }) } } // fix pointer uint in tvp https://github.com/denisenkom/go-mssqldb/issues/703 func TestTVPUnsigned(t *testing.T) { checkConnStr(t) tl := testLogger{t: t} defer tl.StopLogging() SetLogger(&tl) c := makeConnStr(t).String() db, err := sql.Open("sqlserver", c) if err != nil { t.Fatalf("failed to open driver sqlserver") } defer db.Close() ctx, cancel := context.WithCancel(context.Background()) defer cancel() sqltextcreatetable := ` CREATE TYPE unsignedTvpTableTypes AS TABLE ( p_tinyint TINYINT, p_tinyintNull TINYINT, p_smallint SMALLINT, p_smallintNull SMALLINT, p_int INT, p_intNull INT, p_bigint BIGINT, p_bigintNull BIGINT, pInt INT, pIntNull INT ); ` sqltextdroptable := `DROP TYPE unsignedTvpTableTypes;` sqltextcreatesp := ` CREATE PROCEDURE spwithtvpUnsigned @param1 unsignedTvpTableTypes READONLY, @param2 unsignedTvpTableTypes READONLY, @param3 NVARCHAR(10) AS BEGIN SET NOCOUNT ON; SELECT * FROM @param1; SELECT * FROM @param2; SELECT @param3; END;` type TvptableRow struct { PTinyint uint8 `db:"p_tinyint"` PTinyintNull *uint8 `db:"p_tinyintNull"` PSmallint uint16 `db:"p_smallint"` PSmallintNull *uint16 `db:"p_smallintNull"` PInt uint32 `db:"p_int"` PIntNull *uint32 `db:"p_intNull"` PBigint uint64 `db:"p_bigint"` PBigintNull *uint64 `db:"p_bigintNull"` Pint uint `db:"pInt"` PintNull *uint `db:"pIntNull"` } sqltextdropsp := `DROP PROCEDURE spwithtvpUnsigned;` _, 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) i8 := uint8(1) i16 := uint16(2) i32 := uint32(3) i64 := uint64(4) i := uint(5) param1 := []TvptableRow{ { PTinyint: i8, PSmallint: i16, PInt: i32, PBigint: i64, Pint: 355, }, { PTinyint: 5, PSmallint: 16000, PInt: 20000000, PBigint: 2000000020000000, Pint: 455, }, { PTinyintNull: &i8, PSmallintNull: &i16, PIntNull: &i32, PBigintNull: &i64, PintNull: &i, }, { PTinyint: 5, PSmallint: 16000, PInt: 20000000, PBigint: 2000000020000000, PTinyintNull: &i8, PSmallintNull: &i16, PIntNull: &i32, PBigintNull: &i64, PintNull: &i, }, } tvpType := TVP{ TypeName: "unsignedTvpTableTypes", Value: param1, } tvpTypeEmpty := TVP{ TypeName: "unsignedTvpTableTypes", Value: []TvptableRow{}, } rows, err := db.QueryContext(ctx, "exec spwithtvpUnsigned @param1, @param2, @param3", sql.Named("param1", tvpType), sql.Named("param2", tvpTypeEmpty), sql.Named("param3", "test"), ) if err != nil { t.Fatal(err) } defer rows.Close() var result1 []TvptableRow for rows.Next() { var val TvptableRow err := rows.Scan( &val.PTinyint, &val.PTinyintNull, &val.PSmallint, &val.PSmallintNull, &val.PInt, &val.PIntNull, &val.PBigint, &val.PBigintNull, &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 TestTVPIdentity(t *testing.T) { type TvpIdentityExample struct { ID int `tvp:"@identity"` Message string } const ( crateSchema = `create schema TestTVPSchemaIdentity;` dropSchema = `drop schema TestTVPSchemaIdentity;` createTVP = ` CREATE TYPE TestTVPSchemaIdentity.exempleTVP AS TABLE ( id int identity(1,1) not null, message NVARCHAR(100) )` dropTVP = `DROP TYPE TestTVPSchemaIdentity.exempleTVP;` procedureWithTVP = ` CREATE PROCEDURE ExecIdentityTVP @param1 TestTVPSchemaIdentity.exempleTVP READONLY AS BEGIN SET NOCOUNT ON; SELECT * FROM @param1; END; ` dropProcedure = `drop PROCEDURE ExecIdentityTVP` execTvp = `exec ExecIdentityTVP @param1;` ) checkConnStr(t) tl := testLogger{t: t} defer tl.StopLogging() SetLogger(&tl) p := makeConnStr(t).String() conn, err := sql.Open("sqlserver", p) if err != nil { log.Fatal("Open connection failed:", err.Error()) } defer conn.Close() _, err = conn.Exec(crateSchema) if err != nil { t.Fatal(err) return } defer conn.Exec(dropSchema) _, err = conn.Exec(createTVP) if err != nil { t.Fatal(err) return } defer conn.Exec(dropTVP) _, err = conn.Exec(procedureWithTVP) if err != nil { t.Fatal(err) return } defer conn.Exec(dropProcedure) exempleData := []TvpIdentityExample{ { Message: "Hello", }, { Message: "World", }, { Message: "TVP", }, } tvpType := TVP{ TypeName: "TestTVPSchemaIdentity.exempleTVP", Value: exempleData, } rows, err := conn.Query(execTvp, sql.Named("param1", tvpType), ) if err != nil { t.Fatal(err) } defer rows.Close() tvpResult := make([]TvpIdentityExample, 0) for rows.Next() { tvpExemple := TvpIdentityExample{} err = rows.Scan(&tvpExemple.ID, &tvpExemple.Message) if err != nil { t.Fatal(err) } tvpResult = append(tvpResult, tvpExemple) } expectData := []TvpIdentityExample{ { ID: 1, Message: "Hello", }, { ID: 2, Message: "World", }, { ID: 3, Message: "TVP", }, } if len(expectData) != len(tvpResult) { t.Fatal("TestTVPIdentity have to be len") } if !reflect.DeepEqual(expectData, tvpResult) { t.Fatal("TestTVPIdentity have to be same") } }