How to fix error mssql: Error converting data type decimal to nvarchar. in Go using go-mssqldb

1

I am having trouble figuring out why I am not able to execute a stored procedure with the parameters that I have defined while using the go-mssqldb driver. If someone would be so kind as to point out where I have gone wrong in defining the values that I am passing as parameters which results in receiving the error from the microsoft sql server database "mssql: Error converting data type decimal to nvarchar." in Go using go-mssqldb it would be very much appreciated!

I have included the code that I am using and even the parameter definitions in the stored procedure below.

Go code:

package main

import (
    "context"
    "database/sql"
    "flag"
    "fmt"
    _ "github.com/denisenkom/go-mssqldb"
    "github.com/shopspring/decimal"
    "log"
)

var (
    debug         = flag.Bool("debug", true, "enable debugging")
    userid        = flag.String("U", "user", "the database user")
    password      = flag.String("P", "password", "the database password")
    server        = flag.String("S", "address", "the database server")
    port     *int = flag.Int("port", 1433, "the database port")
    database      = flag.String("d", "database", "the database")
    encrypt       = flag.String("e", "disable", "the encryption flag")
)

func main() {

    flag.Parse()

    if *debug {
        fmt.Printf(" port:%d\n", *port)
        fmt.Printf(" server:%s\n", *server)
        fmt.Printf(" user:%s\n", *userid)
        fmt.Printf(" database:%s\n", *database)
    }

    ctx, cancel := context.WithCancel(context.Background())
    defer cancel()

    connString := fmt.Sprintf("server=%s;port=%d;user id=%s;password=%s;database=%s;encrypt=%s;", *server, *port, *userid, *password, *database, *encrypt)
    if *debug {
        fmt.Printf(" connString:%s\n", connString)
    }
    db, err := sql.Open("sqlserver", connString)
    if err != nil {
        log.Fatal("Open connection failed:", err.Error())
    }

    defer db.Close()

    // bigint
    WarehouseID := 1

    // nvarchar(7)
    TaxCode := "Vertex"

    // nvarchar(max)
    LineItemTotals := "16.35"

    // decimal(19,5)
    TotalTax, _ := decimal.NewFromString("00.00")

    // nvarchar(50)
    ShipToAddress1 := "Address1"

    // nvarchar(50)
    ShipToAddress2 := "Address2"

    // nvarchar(40)
    ShipToCity := "Seattle"

    // nvarchar(5)
    ShipToState := "WA"

    // nvarchar(10)
    ShipToZip := "zip"

    // nchar(3)
    ISOCountryCode := "USA"

    // decimal(19,5)
    FreightCharge, _ := decimal.NewFromString("12.10")

    // bigInt
    CustomerID := 456118

    // bigint
    Timeout := 5000

    rows, err := db.QueryContext(ctx, "EAPI_CalculateTax",

        sql.Named("WarehouseID", WarehouseID),

        sql.Named("TaxCode", TaxCode),

        sql.Named("LineItemTotals", LineItemTotals),
        sql.Named("ShipToAddress1", ShipToAddress1),
        sql.Named("ShipToAddress2", ShipToAddress2),
        sql.Named("ShipToCity", ShipToCity),
        sql.Named("ShipToState", ShipToState),
        sql.Named("ShipToZip", ShipToZip),
        sql.Named("ISOCountryCode", ISOCountryCode),
        sql.Named("FreightCharge", FreightCharge),
        sql.Named("CustomerID", CustomerID),
        sql.Named("Timeout", Timeout),
        sql.Named("TotalTax", sql.Out{Dest: &TotalTax}),
    )

    if err != nil {
        log.Fatal("Prepare failed: ", err.Error())
    }
    defer rows.Close()

    var strrow string
    for rows.Next() {
        err = rows.Scan(&strrow)
    }
    fmt.Printf("TotalTax is %d", TotalTax)
}

sql stored procedure parameters:

@WarehouseID bigint,
@TaxCode nvarchar(7),
@LineItemTotals nvarchar(max),
@TotalTax decimal(19,5) OUTPUT,
@ShipToAddress1 nvarchar(50) = '', 
@ShipToAddress2 nvarchar(50) = '', 
@ShipToCity nvarchar(40) = '', 
@ShipToState nvarchar(5) = '', 
@ShipToZip nvarchar(10) = '', 
@ISOCountryCode nchar(3) = 'USA',
@FreightCharge decimal(19,5) = 0,
@CustomerID bigInt = 0,
@Timeout bigint = 0

EDIT:

I added in the errorx library and recieved a better stack trace. The stack trace is below:

mssql: Error converting data type decimal to nvarchar.
panic: runtime error: invalid memory address or nil pointer dereference
[signal 0xc0000005 code=0x0 addr=0x0 pc=0x4cd337]

goroutine 1 [running]:
database/sql.(*Rows).Next(0x0, 0xc0000468a0)
        C:/Go/src/database/sql/sql.go:2640 +0x37
main.main()
        C:/Users/chris/go/src/RAPI/main.go:99 +0xc18
sql
sql-server
go
asked on Stack Overflow Jun 29, 2019 by ozfive • edited Jun 29, 2019 by ozfive

1 Answer

1

Check if this is an order issue.

Meaning: try and call db.QueryContext(ctx, with sql.Named() parameters listed in the same order as your sql stored procedure parameters are defined.

answered on Stack Overflow Jun 29, 2019 by VonC

User contributions licensed under CC BY-SA 3.0