Repository: denisenkom/go-mssqldb
Pull request #404 (2018-08-03 14:37:48)

Fix bulkcopy of DateTimeOffsets and precise Decimals
- Converting numeric types via float64 risks losing precision, so add direct conversions for ints and strings
- DateTimeOffset values were being rejected by the server due to a typo in the code
- Also support dates/times expressed as strings for consistency with regular inserts, and make the 'invalid type' errors more helpful

codecov[bot]: # [Codecov](https://codecov.io/gh/denisenkom/go-mssqldb/pull/404?src=pr&el=h1) Report
> Merging [#404](https://codecov.io/gh/denisenkom/go-mssqldb/pull/404?src=pr&el=desc) into [master](https://codecov.io/gh/denisenkom/go-mssqldb/commit/242fa5aa1b45aeb9fcdfeee88822982e3f548e22?src=pr&el=desc) will **increase** coverage by `0.21%`.
> The diff coverage is `61.86%`.

[![Impacted file tree graph](https://codecov.io/gh/denisenkom/go-mssqldb/pull/404/graphs/tree.svg?height=150&width=650&token=MoMB2u5avp&src=pr)](https://codecov.io/gh/denisenkom/go-mssqldb/pull/404?src=pr&el=tree)

```diff
@@            Coverage Diff             @@
##           master     #404      +/-   ##
==========================================
+ Coverage   67.87%   68.09%   +0.21%     
==========================================
  Files          21       21              
  Lines        5043     5121      +78     
==========================================
+ Hits         3423     3487      +64     
- Misses       1407     1418      +11     
- Partials      213      216       +3
```


| [Impacted Files](https://codecov.io/gh/denisenkom/go-mssqldb/pull/404?src=pr&el=tree) | Coverage Δ | |
|---|---|---|
| [bulkcopy.go](https://codecov.io/gh/denisenkom/go-mssqldb/pull/404/diff?src=pr&el=tree#diff-YnVsa2NvcHkuZ28=) | `57.74% <45.31%> (+1.43%)` | :arrow_up: |
| [decimal.go](https://codecov.io/gh/denisenkom/go-mssqldb/pull/404/diff?src=pr&el=tree#diff-ZGVjaW1hbC5nbw==) | `93.05% <81.48%> (-6.95%)` | :arrow_down: |

------

[Continue to review full report at Codecov](https://codecov.io/gh/denisenkom/go-mssqldb/pull/404?src=pr&el=continue).
> **Legend** - [Click here to learn more](https://docs.codecov.io/docs/codecov-delta)
> `Δ = absolute <relative> (impact)`, `ø = not affected`, `? = missing data`
> Powered by [Codecov](https://codecov.io/gh/denisenkom/go-mssqldb/pull/404?src=pr&el=footer). Last update [242fa5a...bcb7c74](https://codecov.io/gh/denisenkom/go-mssqldb/pull/404?src=pr&el=lastupdated). Read the [comment docs](https://docs.codecov.io/docs/pull-request-comments).

jwebb: Agree this could do with a larger rework. The `[4]int32` at present is just adding complication with no benefit, as all output conversions go via `big.Int` anyway. The simplistic float64 conversions also have undesirable properties (e.g. round-tripping 1.3 will become 1.2999999999999999245025280). Go provides Grisu3/Dragon4 implementations for this reason.

Thing is, it's not obvious to me that this library needs any sort of decimal type at all. It's public, but it doesn't look like something a client would ever use? Unless there are future plans? Right now AFAICT it's only used momentarily for conversions, and there's very little sharing of code paths. Given that, it would probably be better to implement each of those conversions as a function using whatever library facilities are most appropriate, and ditch the intermediate representation.

(But I still think this PR should be merged in the meantime, as it fixes observable issues.)
SQLServerIO: I am using github.com/shopspring/decimal to handle all my decimal needs.
dagss: Nice work I'd really like this in.. 

NOTE: When implementing #430 I first tried introducing a dedicated mssql.Money type for the bulk import. However, the golang `database/sql` package didn't like that -- it seems that arguments has to be converted to primitive types before they hit the driver.

So I think this PR would need to work with string -- any decimal type would need to serialize to string before it is allowed to hit this driver code.

*On tests*:

I notice that the strategy used for testing conflicts with the one I did in #430 . I did that what I did so that it would be possible to test for bulk import of NULL values, but I can rewrite the test to follow the pattern here if required.

j0hnwhyte: Is it possible to fix at least the `datetimeoffset` issue? I can see that using `datetime2` works when using the bulk insert feature, and I'm currently using it as a workaround.
denisenkom: Yes, please send PR which contains fix for datetime2 issue.  Or provide details about this issue, preferably by opening issue or linking issue, if one already open.
jwebb: This PR already contains the fix. As well as another. Can I please understand why you will not merge it?

I'm happy to rebase, if it's going to be merged.

I'm also happy to spend some time on refactoring to remove the Decimal type entirely as I mentioned previously. But I'd first need some feedback from you that this is a direction you want to go in, and I don't think that reworking internals should hold up a fix for a data corruption issue.

In the meantime, we have been using this branch in production for the past year.
denisenkom: This PR is too big.  Maybe you can break it down into individual fixes.  Lets start with datetime fix. I still don't know what is wrong with it, but with smaller PR I might be able to understand.
There is no good solution for decimals yet, so lets postpone it.  I believe newer golang will have necessary functionality, once it is ready we can revisit.
j0hnwhyte: The problem I'm having is that if I use that SQL type for the column definition, the bulk insert operation fails, while if the column uses the `datetime2` type everything works.
jwebb: Datetimeoffsets are this commit - a trivial typo: https://github.com/denisenkom/go-mssqldb/pull/404/commits/bcb7c743efe7c0e8f456e166990d79d4c107d34b

I could PR these separately if necessary, and if it will result in everything going in. But I'm afraid that much as I would prefer to contribute fixes back, I can't reasonably allocate time to work on this further if we're going to have to continue to use our own fork anyway.

I must say I'm a little confused by the position that it is better not to apply a fix for a data corruption bug now (or indeed a year ago!), just because the code might be deleted later if something better comes along. Nothing in this fix would make that work any harder. Easier, in fact, given that it also improves error reporting and test coverage.
denisenkom: Ok merged datetimeoffsets fix.  I will look more into the reset of this PR.
In general it is better when PR contains single fix, especially when it introduces lots of changes.  Otherwise it make is much harder to review.
denisenkom: I reviewed the rest of the changes, and I approve this PR.  @jwebb can you re-base it?
jwebb: Great, thanks!

Rebasing a branch this old was slightly nontrivial, so I'm going to do some more testing before updating this PR (rebase is at https://github.com/XTXMarkets/go-mssqldb/commits/bulk-insert2 if anyone's curious). Will check back in, in a day or so.
jwebb: Seems fine. Updated.

Patch:
diff --git a/bulkcopy.go b/bulkcopy.go
index 3b7bbb4..1d5eacb 100644
--- a/bulkcopy.go
+++ b/bulkcopy.go
@@ -7,7 +7,6 @@ import (
 	"fmt"
 	"math"
 	"reflect"
-	"strconv"
 	"strings"
 	"time"
 
@@ -44,6 +43,11 @@ type BulkOptions struct {
 
 type DataValue interface{}
 
+const (
+	sqlDateFormat = "2006-01-02"
+	sqlTimeFormat = "2006-01-02 15:04:05.999999999Z07:00"
+)
+
 func (cn *Conn) CreateBulk(table string, columns []string) (_ *Bulk) {
 	b := Bulk{ctx: context.Background(), cn: cn, tablename: table, headerSent: false, columnsName: columns}
 	b.Debug = false
@@ -336,7 +340,7 @@ func (b *Bulk) makeParam(val DataValue, col columnStruct) (res param, err error)
 		case int64:
 			intvalue = val
 		default:
-			err = fmt.Errorf("mssql: invalid type for int column")
+			err = fmt.Errorf("mssql: invalid type for int column: %T", val)
 			return
 		}
 
@@ -363,7 +367,7 @@ func (b *Bulk) makeParam(val DataValue, col columnStruct) (res param, err error)
 		case int64:
 			floatvalue = float64(val)
 		default:
-			err = fmt.Errorf("mssql: invalid type for float column: %s", val)
+			err = fmt.Errorf("mssql: invalid type for float column: %T %s", val, val)
 			return
 		}
 
@@ -382,7 +386,7 @@ func (b *Bulk) makeParam(val DataValue, col columnStruct) (res param, err error)
 		case []byte:
 			res.buffer = val
 		default:
-			err = fmt.Errorf("mssql: invalid type for nvarchar column: %s", val)
+			err = fmt.Errorf("mssql: invalid type for nvarchar column: %T %s", val, val)
 			return
 		}
 		res.ti.Size = len(res.buffer)
@@ -394,14 +398,14 @@ func (b *Bulk) makeParam(val DataValue, col columnStruct) (res param, err error)
 		case []byte:
 			res.buffer = val
 		default:
-			err = fmt.Errorf("mssql: invalid type for varchar column: %s", val)
+			err = fmt.Errorf("mssql: invalid type for varchar column: %T %s", val, val)
 			return
 		}
 		res.ti.Size = len(res.buffer)
 
 	case typeBit, typeBitN:
 		if reflect.TypeOf(val).Kind() != reflect.Bool {
-			err = fmt.Errorf("mssql: invalid type for bit column: %s", val)
+			err = fmt.Errorf("mssql: invalid type for bit column: %T %s", val, val)
 			return
 		}
 		res.ti.TypeId = typeBitN
@@ -415,8 +419,15 @@ func (b *Bulk) makeParam(val DataValue, col columnStruct) (res param, err error)
 		case time.Time:
 			res.buffer = encodeDateTime2(val, int(col.ti.Scale))
 			res.ti.Size = len(res.buffer)
+		case string:
+			var t time.Time
+			if t, err = time.Parse(sqlTimeFormat, val); err != nil {
+				return res, fmt.Errorf("bulk: unable to convert string to date: %v", err)
+			}
+			res.buffer = encodeDateTime2(t, int(col.ti.Scale))
+			res.ti.Size = len(res.buffer)
 		default:
-			err = fmt.Errorf("mssql: invalid type for datetime2 column: %s", val)
+			err = fmt.Errorf("mssql: invalid type for datetime2 column: %T %s", val, val)
 			return
 		}
 	case typeDateTimeOffsetN:
@@ -424,9 +435,15 @@ func (b *Bulk) makeParam(val DataValue, col columnStruct) (res param, err error)
 		case time.Time:
 			res.buffer = encodeDateTimeOffset(val, int(col.ti.Scale))
 			res.ti.Size = len(res.buffer)
-
+		case string:
+			var t time.Time
+			if t, err = time.Parse(sqlTimeFormat, val); err != nil {
+				return res, fmt.Errorf("bulk: unable to convert string to date: %v", err)
+			}
+			res.buffer = encodeDateTimeOffset(t, int(col.ti.Scale))
+			res.ti.Size = len(res.buffer)
 		default:
-			err = fmt.Errorf("mssql: invalid type for datetimeoffset column: %s", val)
+			err = fmt.Errorf("mssql: invalid type for datetimeoffset column: %T %s", val, val)
 			return
 		}
 	case typeDateN:
@@ -434,69 +451,79 @@ func (b *Bulk) makeParam(val DataValue, col columnStruct) (res param, err error)
 		case time.Time:
 			res.buffer = encodeDate(val)
 			res.ti.Size = len(res.buffer)
+		case string:
+			var t time.Time
+			if t, err = time.ParseInLocation(sqlDateFormat, val, time.UTC); err != nil {
+				return res, fmt.Errorf("bulk: unable to convert string to date: %v", err)
+			}
+			res.buffer = encodeDate(t)
+			res.ti.Size = len(res.buffer)
 		default:
-			err = fmt.Errorf("mssql: invalid type for date column: %s", val)
+			err = fmt.Errorf("mssql: invalid type for date column: %T %s", val, val)
 			return
 		}
 	case typeDateTime, typeDateTimeN, typeDateTim4:
+		var t time.Time
 		switch val := val.(type) {
 		case time.Time:
-			if col.ti.Size == 4 {
-				res.buffer = encodeDateTim4(val)
-				res.ti.Size = len(res.buffer)
-			} else if col.ti.Size == 8 {
-				res.buffer = encodeDateTime(val)
-				res.ti.Size = len(res.buffer)
-			} else {
-				err = fmt.Errorf("mssql: invalid size of column")
+			t = val
+		case string:
+			if t, err = time.Parse(sqlTimeFormat, val); err != nil {
+				return res, fmt.Errorf("bulk: unable to convert string to date: %v", err)
 			}
-
 		default:
-			err = fmt.Errorf("mssql: invalid type for datetime column: %s", val)
+			err = fmt.Errorf("mssql: invalid type for datetime column: %T %s", val, val)
+			return
+		}
+
+		if col.ti.Size == 4 {
+			res.buffer = encodeDateTim4(t)
+			res.ti.Size = len(res.buffer)
+		} else if col.ti.Size == 8 {
+			res.buffer = encodeDateTime(t)
+			res.ti.Size = len(res.buffer)
+		} else {
+			err = fmt.Errorf("mssql: invalid size of column %d", col.ti.Size)
 		}
 
 	// case typeMoney, typeMoney4, typeMoneyN:
 	case typeDecimal, typeDecimalN, typeNumeric, typeNumericN:
-		var value float64
+		prec := col.ti.Prec
+		scale := col.ti.Scale
+		var dec decimal.Decimal
 		switch v := val.(type) {
 		case int:
-			value = float64(v)
+			dec = decimal.Int64ToDecimalScale(int64(v), 0)
 		case int8:
-			value = float64(v)
+			dec = decimal.Int64ToDecimalScale(int64(v), 0)
 		case int16:
-			value = float64(v)
+			dec = decimal.Int64ToDecimalScale(int64(v), 0)
 		case int32:
-			value = float64(v)
+			dec = decimal.Int64ToDecimalScale(int64(v), 0)
 		case int64:
-			value = float64(v)
+			dec = decimal.Int64ToDecimalScale(int64(v), 0)
 		case float32:
-			value = float64(v)
+			dec, err = decimal.Float64ToDecimalScale(float64(v), scale)
 		case float64:
-			value = v
+			dec, err = decimal.Float64ToDecimalScale(float64(v), scale)
 		case string:
-			if value, err = strconv.ParseFloat(v, 64); err != nil {
-				return res, fmt.Errorf("bulk: unable to convert string to float: %v", err)
-			}
+			dec, err = decimal.StringToDecimalScale(v, scale)
 		default:
-			return res, fmt.Errorf("unknown value for decimal: %#v", v)
+			return res, fmt.Errorf("unknown value for decimal: %T %#v", v, v)
 		}
 
-		perc := col.ti.Prec
-		scale := col.ti.Scale
-		var dec decimal.Decimal
-		dec, err = decimal.Float64ToDecimalScale(value, scale)
 		if err != nil {
 			return res, err
 		}
-		dec.SetPrec(perc)
+		dec.SetPrec(prec)
 
 		var length byte
 		switch {
-		case perc <= 9:
+		case prec <= 9:
 			length = 4
-		case perc <= 19:
+		case prec <= 19:
 			length = 8
-		case perc <= 28:
+		case prec <= 28:
 			length = 12
 		default:
 			length = 16
@@ -506,7 +533,7 @@ func (b *Bulk) makeParam(val DataValue, col columnStruct) (res param, err error)
 		// first byte length written by typeInfo.writer
 		res.ti.Size = int(length) + 1
 		// second byte sign
-		if value < 0 {
+		if !dec.IsPositive() {
 			buf[0] = 0
 		} else {
 			buf[0] = 1
@@ -529,7 +556,7 @@ func (b *Bulk) makeParam(val DataValue, col columnStruct) (res param, err error)
 			res.ti.Size = len(val)
 			res.buffer = val
 		default:
-			err = fmt.Errorf("mssql: invalid type for Binary column: %s", val)
+			err = fmt.Errorf("mssql: invalid type for Binary column: %T %s", val, val)
 			return
 		}
 	case typeGuid:
@@ -538,7 +565,7 @@ func (b *Bulk) makeParam(val DataValue, col columnStruct) (res param, err error)
 			res.ti.Size = len(val)
 			res.buffer = val
 		default:
-			err = fmt.Errorf("mssql: invalid type for Guid column: %s", val)
+			err = fmt.Errorf("mssql: invalid type for Guid column: %T %s", val, val)
 			return
 		}
 
diff --git a/internal/decimal/decimal.go b/internal/decimal/decimal.go
index 68f790a..7da0375 100644
--- a/internal/decimal/decimal.go
+++ b/internal/decimal/decimal.go
@@ -3,19 +3,25 @@ package decimal
 import (
 	"encoding/binary"
 	"errors"
+	"fmt"
 	"math"
 	"math/big"
+	"strings"
 )
 
 // Decimal represents decimal type in the Microsoft Open Specifications: http://msdn.microsoft.com/en-us/library/ee780893.aspx
 type Decimal struct {
-	integer  [4]uint32
+	integer  [4]uint32 // Little-endian
 	positive bool
 	prec     uint8
 	scale    uint8
 }
 
-var scaletblflt64 [39]float64
+var (
+	scaletblflt64 [39]float64
+	int10         big.Int
+	int1e5        big.Int
+)
 
 func init() {
 	var acc float64 = 1
@@ -23,6 +29,9 @@ func init() {
 		scaletblflt64[i] = acc
 		acc *= 10
 	}
+
+	int10.SetInt64(10)
+	int1e5.SetInt64(1e5)
 }
 
 const autoScale = 100
@@ -47,6 +56,11 @@ func (d *Decimal) SetScale(scale uint8) {
 	d.scale = scale
 }
 
+// IsPositive returns true if the Decimal is positive
+func (d *Decimal) IsPositive() bool {
+	return d.positive
+}
+
 // ToFloat64 converts decimal to a float64
 func (d Decimal) ToFloat64() float64 {
 	val := float64(0)
@@ -137,6 +151,82 @@ func Float64ToDecimalScale(f float64, scale uint8) (Decimal, error) {
 	return dec, nil
 }
 
+// Int64ToDecimalScale converts float64 to decimal; user can specify the scale
+func Int64ToDecimalScale(v int64, scale uint8) Decimal {
+	positive := v >= 0
+	if !positive {
+		if v == math.MinInt64 {
+			// Special case - can't negate
+			return Decimal{
+				integer:  [4]uint32{0, 0x80000000, 0, 0},
+				positive: false,
+				prec:     20,
+				scale:    0,
+			}
+		}
+		v = -v
+	}
+	return Decimal{
+		integer:  [4]uint32{uint32(v), uint32(v >> 32), 0, 0},
+		positive: positive,
+		prec:     20,
+		scale:    scale,
+	}
+}
+
+// StringToDecimalScale converts string to decimal
+func StringToDecimalScale(v string, outScale uint8) (Decimal, error) {
+	var r big.Int
+	var unscaled string
+	var inScale int
+
+	point := strings.LastIndexByte(v, '.')
+	if point == -1 {
+		inScale = 0
+		unscaled = v
+	} else {
+		inScale = len(v) - point - 1
+		unscaled = v[:point] + v[point+1:]
+	}
+	if inScale > math.MaxUint8 {
+		return Decimal{}, fmt.Errorf("can't parse %q as a decimal number: scale too large", v)
+	}
+
+	_, ok := r.SetString(unscaled, 10)
+	if !ok {
+		return Decimal{}, fmt.Errorf("can't parse %q as a decimal number", v)
+	}
+
+	if inScale > int(outScale) {
+		return Decimal{}, fmt.Errorf("can't parse %q as a decimal number: scale %d is larger than the scale %d of the target column", v, inScale, outScale)
+	}
+	for inScale < int(outScale) {
+		if int(outScale)-inScale >= 5 {
+			r.Mul(&r, &int1e5)
+			inScale += 5
+		} else {
+			r.Mul(&r, &int10)
+			inScale++
+		}
+	}
+
+	bytes := r.Bytes()
+	if len(bytes) > 16 {
+		return Decimal{}, fmt.Errorf("can't parse %q as a decimal number: precision too large", v)
+	}
+	var out [4]uint32
+	for i, b := range bytes {
+		pos := len(bytes) - i - 1
+		out[pos/4] += uint32(b) << uint(pos%4*8)
+	}
+	return Decimal{
+		integer:  out,
+		positive: r.Sign() >= 0,
+		prec:     20,
+		scale:    uint8(inScale),
+	}, nil
+}
+
 // ScaleBytes converts a stringified decimal to a scaled byte slice
 func ScaleBytes(s string, scale uint8) []byte {
 	z := make([]byte, 0, len(s)+1)


Test patch:
diff --git a/bulkcopy_test.go b/bulkcopy_test.go
index fdce384..9ceefea 100644
--- a/bulkcopy_test.go
+++ b/bulkcopy_test.go
@@ -20,60 +20,58 @@ func TestBulkcopy(t *testing.T) {
 	}
 	type testValue struct {
 		colname string
-		val     interface{}
+		in, out interface{}
 	}
 
 	tableName := "#table_test"
 	geom, _ := hex.DecodeString("E6100000010C00000000000034400000000000004440")
 	bin, _ := hex.DecodeString("ba8b7782168d4033a299333aec17bd33")
+	uid := []byte{0x6F, 0x96, 0x19, 0xFF, 0x8B, 0x86, 0xD0, 0x11, 0xB4, 0x2D, 0x00, 0xC0, 0x4F, 0xC9, 0x64, 0xFF}
 	testValues := []testValue{
-
-		{"test_nvarchar", "ab©ĎéⒻghïjklmnopqЯ☀tuvwxyz"},
-		{"test_nvarchar_4000", strings.Repeat("Ⓕ", 4000)}, // edge case
-		{"test_nvarchar_max", strings.Repeat("Ⓕ", 4001)},
-		{"test_varchar", "abcdefg"},
-		{"test_varchar_8000", strings.Repeat("a", 8000)}, // edge case
-		{"test_varchar_max", strings.Repeat("a", 8001)},
-		{"test_char", "abcdefg   "},
-		{"test_nchar", "abcdefg   "},
-		{"test_text", "abcdefg"},
-		{"test_ntext", "abcdefg"},
-		{"test_float", 1234.56},
-		{"test_floatn", 1234.56},
-		{"test_real", 1234.56},
-		{"test_realn", 1234.56},
-		{"test_bit", true},
-		{"test_bitn", nil},
-		{"test_smalldatetime", time.Date(2010, 11, 12, 13, 14, 0, 0, time.UTC)},
-		{"test_smalldatetimen", time.Date(2010, 11, 12, 13, 14, 0, 0, time.UTC)},
-		{"test_datetime", time.Date(2010, 11, 12, 13, 14, 15, 120000000, time.UTC)},
-		{"test_datetimen", time.Date(2010, 11, 12, 13, 14, 15, 120000000, time.UTC)},
-		{"test_datetimen_1", time.Date(4010, 11, 12, 13, 14, 15, 120000000, time.UTC)},
-		{"test_datetime2_1", time.Date(2010, 11, 12, 13, 14, 15, 0, time.UTC)},
-		{"test_datetime2_3", time.Date(2010, 11, 12, 13, 14, 15, 123000000, time.UTC)},
-		{"test_datetime2_7", time.Date(2010, 11, 12, 13, 14, 15, 123000000, time.UTC)},
-		{"test_datetimeoffset_7", time.Date(2010, 11, 12, 13, 14, 15, 123000000, time.UTC)},
-		{"test_date", time.Date(2010, 11, 12, 00, 00, 00, 0, time.UTC)},
-		{"test_tinyint", 255},
-		{"test_smallint", 32767},
-		{"test_smallintn", nil},
-		{"test_int", 2147483647},
-		{"test_bigint", int64(9223372036854775807)},
-		{"test_bigintn", nil},
-		{"test_geom", geom},
-		{"test_uniqueidentifier", []byte{0x6F, 0x96, 0x19, 0xFF, 0x8B, 0x86, 0xD0, 0x11, 0xB4, 0x2D, 0x00, 0xC0, 0x4F, 0xC9, 0x64, 0xFF}},
-		// {"test_smallmoney", 1234.56},
-		// {"test_money", 1234.56},
-		{"test_decimal_18_0", 1234.0001},
-		{"test_decimal_18_2", 264.9},
-		{"test_decimal_9_2", 1234.560001},
-		{"test_decimal_20_0", 1234.0001},
-		{"test_numeric_30_10", 1234567.1234567},
-		{"test_varbinary", []byte("1")},
-		{"test_varbinary_16", bin},
-		{"test_varbinary_max", bin},
-		{"test_binary", []byte("1")},
-		{"test_binary_16", bin},
+		{"test_nvarchar", "ab©ĎéⒻghïjklmnopqЯ☀tuvwxyz", nil},
+		{"test_varchar", "abcdefg", nil},
+		{"test_char", "abcdefg   ", nil},
+		{"test_nchar", "abcdefg   ", nil},
+		{"test_text", "abcdefg", nil},
+		{"test_ntext", "abcdefg", nil},
+		{"test_float", 1234.56, nil},
+		{"test_floatn", 1234.56, nil},
+		{"test_real", 1234.56, nil},
+		{"test_realn", 1234.56, nil},
+		{"test_bit", true, nil},
+		{"test_bitn", nil, nil},
+		{"test_smalldatetime", time.Date(2010, 11, 12, 13, 14, 0, 0, time.UTC), nil},
+		{"test_smalldatetimen", time.Date(2010, 11, 12, 13, 14, 0, 0, time.UTC), nil},
+		{"test_datetime", time.Date(2010, 11, 12, 13, 14, 15, 120000000, time.UTC), nil},
+		{"test_datetimen", time.Date(2010, 11, 12, 13, 14, 15, 120000000, time.UTC), nil},
+		{"test_datetimen_1", time.Date(4010, 11, 12, 13, 14, 15, 120000000, time.UTC), nil},
+		{"test_datetime2_1", "2010-11-12 13:14:15Z", time.Date(2010, 11, 12, 13, 14, 15, 0, time.UTC)},
+		{"test_datetime2_3", time.Date(2010, 11, 12, 13, 14, 15, 123000000, time.UTC), nil},
+		{"test_datetime2_7", time.Date(2010, 11, 12, 13, 14, 15, 123000000, time.UTC), nil},
+		{"test_datetimeoffset_7", time.Date(2010, 11, 12, 13, 14, 15, 123000000, time.UTC), nil},
+		{"test_date", time.Date(2010, 11, 12, 0, 0, 0, 0, time.UTC), nil},
+		{"test_date_2", "2015-06-07", time.Date(2015, 6, 7, 0, 0, 0, 0, time.UTC)},
+		{"test_tinyint", 255, nil},
+		{"test_smallint", 32767, nil},
+		{"test_smallintn", nil, nil},
+		{"test_int", 2147483647, nil},
+		{"test_bigint", 9223372036854775807, nil},
+		{"test_bigintn", nil, nil},
+		{"test_geom", geom, string(geom)},
+		{"test_uniqueidentifier", uid, string(uid)},
+		// {"test_smallmoney", 1234.56, nil},
+		// {"test_money", 1234.56, nil},
+		{"test_decimal_18_0", 1234.0001, "1234"},
+		{"test_decimal_9_2", -1234.560001, "-1234.56"},
+		{"test_decimal_20_0", 1234, "1234"},
+		{"test_decimal_20_0_2", math.MinInt64, "-9223372036854775808"},
+		{"test_decimal_20_10", "1234.1", "1234.1000000000"},
+		{"test_numeric_30_10", "66666666666666666666.6666666666", nil},
+		{"test_varbinary", []byte("1"), nil},
+		{"test_varbinary_16", bin, nil},
+		{"test_varbinary_max", bin, nil},
+		{"test_binary", []byte("1"), nil},
+		{"test_binary_16", bin, nil},
 	}
 
 	columns := make([]string, len(testValues))
@@ -83,7 +81,7 @@ func TestBulkcopy(t *testing.T) {
 
 	values := make([]interface{}, len(testValues))
 	for i, val := range testValues {
-		values[i] = val.val
+		values[i] = val.in
 	}
 
 	pool := open(t)
@@ -155,8 +153,16 @@ func TestBulkcopy(t *testing.T) {
 			t.Fatal(err)
 		}
 		for i, c := range testValues {
-			if !compareValue(container[i], c.val) {
-				t.Errorf("columns %s : expected: %v, got: %v\n", c.colname, c.val, container[i])
+			expected := c.out
+			if expected == nil {
+				expected = c.in
+			}
+			if !compareValue(container[i], expected) {
+				v := container[i]
+				if s, ok := v.([]uint8); ok {
+					v = string(s)
+				}
+				t.Errorf("columns %s : expected: %T %v, got: %T %v\n", c.colname, expected, expected, container[i], v)
 			}
 		}
 	}
@@ -166,19 +172,20 @@ func TestBulkcopy(t *testing.T) {
 }
 
 func compareValue(a interface{}, expected interface{}) bool {
+	if got, ok := a.([]uint8); ok {
+		if _, ok := expected.([]uint8); !ok {
+			a = string(got)
+		}
+	}
+
 	switch expected := expected.(type) {
 	case int:
 		return int64(expected) == a
 	case int32:
 		return int64(expected) == a
 	case int64:
-		return int64(expected) == a
+		return expected == a
 	case float64:
-		if got, ok := a.([]uint8); ok {
-			var nf sql.NullFloat64
-			nf.Scan(got)
-			a = nf.Float64
-		}
 		return math.Abs(expected-a.(float64)) < 0.0001
 	case time.Time:
 		if got, ok := a.(time.Time); ok {
@@ -221,6 +228,7 @@ func setupTable(ctx context.Context, t *testing.T, conn *sql.Conn, tableName str
 	[test_datetime2_7] [datetime2](7) NULL,
 	[test_datetimeoffset_7] [datetimeoffset](7) NULL,
 	[test_date] [date] NULL,
+	[test_date_2] [date] NULL,
 	[test_smallmoney] [smallmoney] NULL,
 	[test_money] [money] NULL,
 	[test_tinyint] [tinyint] NULL,
@@ -237,13 +245,15 @@ func setupTable(ctx context.Context, t *testing.T, conn *sql.Conn, tableName str
 	[test_decimal_18_2] [decimal](18, 2) NULL,
 	[test_decimal_9_2] [decimal](9, 2) NULL,
 	[test_decimal_20_0] [decimal](20, 0) NULL,
+	[test_decimal_20_0_2] [decimal](20, 0) NULL,
+	[test_decimal_20_10] [decimal](20, 10) NULL,
 	[test_numeric_30_10] [decimal](30, 10) NULL,
 	[test_varbinary] VARBINARY NOT NULL,
 	[test_varbinary_16] VARBINARY(16) NOT NULL,
 	[test_varbinary_max] VARBINARY(max) NOT NULL,
 	[test_binary] BINARY NOT NULL,
 	[test_binary_16] BINARY(16) NOT NULL,
- CONSTRAINT [PK_` + tableName + `_id] PRIMARY KEY CLUSTERED 
+ CONSTRAINT [PK_` + tableName + `_id] PRIMARY KEY CLUSTERED
 (
 	[id] ASC
 )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
diff --git a/internal/decimal/decimal_test.go b/internal/decimal/decimal_test.go
index f59b06c..6b93ec8 100644
--- a/internal/decimal/decimal_test.go
+++ b/internal/decimal/decimal_test.go
@@ -105,3 +105,54 @@ func TestFromFloat64(t *testing.T) {
 		t.Error("Expected to get error for conversion from too big number, but didn't")
 	}
 }
+
+func TestFromInt64(t *testing.T) {
+	values := []struct {
+		in    int64
+		scale uint8
+		out   string
+	}{
+		{0, 0, "0"},
+		{12345, 3, "12.345"},
+		{math.MaxInt64, 0, "9223372036854775807"},
+		{math.MinInt64, 0, "-9223372036854775808"},
+	}
+	for _, v := range values {
+		dec := Int64ToDecimalScale(v.in, v.scale)
+		if dec.String() != v.out {
+			t.Error("Int64ToDecimalScale values don't match ", v.in, dec, v.scale, v.out)
+		}
+	}
+}
+
+func TestFromString(t *testing.T) {
+	values := []struct {
+		in    string
+		scale uint8
+		out   string
+	}{
+		{"0", 0, "0"},
+		{"-000.000", 3, "0.000"},
+		{"-000.000", 5, "0.00000"},
+		{"1", 0, "1"},
+		{"-01.0", 1, "-1.0"},
+		{"0.01", 2, "0.01"},
+		{"-0.1", 10, "-0.1000000000"},
+		{"1.00", 2, "1.00"},
+		{"-1.00", 2, "-1.00"},
+		{"4294967296", 0, "4294967296"},
+		{"18446744073709551616", 0, "18446744073709551616"},
+		{"18446744078004.518912", 6, "18446744078004.518912"},
+		{"-18446744078004518912.12345", 5, "-18446744078004518912.12345"},
+		{"1844674407800451891212345", 0, "1844674407800451891212345"},
+		{"79228162532711081671548469248", 2, "79228162532711081671548469248.00"},
+	}
+	for _, v := range values {
+		dec, err := StringToDecimalScale(v.in, v.scale)
+		if err != nil {
+			t.Error("StringToDecimal failed with error:", err.Error())
+		} else if dec.String() != v.out {
+			t.Error("StringToDecimal values don't match ", v.in, dec.String(), v.out)
+		}
+	}
+}