Data type 0x1F is an untyped NULL

[Fank]

If i try to run following statement ill get this error mssql: The incoming tabular data stream (TDS) remote procedure call (RPC) protocol stream is incorrect. Parameter 5 ("@ErrorCode"): Data type 0x1F is an untyped NULL but is marked as an output parameter.

    order := struct {
        OrderID     int
        ErrorCode   *int
        Error       *string
        WarningCode *int
        Warning     *string
    }{}

    _, err := n.MSSQL.ExecContext(
        ctx,
        "sao.NegSoft_Proc_OrderCreate",
        sql.Named("ContactId", request.ContactID),
        sql.Named("OrderDescription", "Test"),
        sql.Named("OrderId", sql.Out{Dest: &order.OrderID}),
        sql.Named("OperatorId", n.Operator.UserID),
        sql.Named("ErrorCode", sql.Out{Dest: &order.ErrorCode}),
        sql.Named("ErrorDescription", sql.Out{Dest: &order.Error}),
        sql.Named("WarningCode", sql.Out{Dest: &order.WarningCode}),
        sql.Named("WarningDescription", sql.Out{Dest: &order.Warning}),
    )
    if err != nil {
        return err
    }

ALTER PROCEDURE [sao].[NegSoft_Proc_OrderCreate]
    -- required parameters (OfferId or ContactId)
    @OfferId INT = NULL,
    @ContactId INT = NULL,
    -- optional parameters
    @OrderDescription VARCHAR(50) = '',
    -- return parameters
    @OrderId INT OUTPUT,
    -- 
    @OperatorId INT,
    @ErrorCode INT = NULL OUTPUT,
    @ErrorDescription VARCHAR(8000) = NULL OUTPUT,
    @WarningCode INT = NULL OUTPUT,
    @WarningDescription VARCHAR(8000) = NULL OUTPUT
AS

if i remove the pointer from the order struct so it will looks like:

    order := struct {
        OrderID     int
        ErrorCode   int
        Error       string
        WarningCode int
        Warning     string
    }{}

ill get

scan error unsupported scan into type *int for server type int64
scan error unsupported scan into type *int for server type int64
scan error unsupported scan into type int for server type int64
scan error unsupported scan into type string for server type string
scan error unsupported scan into type string for server type string

go version go1.10.2 linux/amd64

- name: github.com/denisenkom/go-mssqldb
  version: c12642a8f7884ab01719704032c36ff300ff52c6

[Fank]

I rebuild everything to variables and it will show a different error

    orderID := 0
    errorCode := 0
    errorString := ""
    warningCode := 0
    warningString := ""

    _, err = tx.ExecContext(
        ctx,
        "sao.NegSoft_Proc_OrderCreate",
        sql.Named("ContactId", request.ContactID),
        sql.Named("OrderDescription", "Test"),
        sql.Named("OrderId", sql.Out{Dest: &orderID}),
        sql.Named("OperatorId", n.Operator.UserID),
        sql.Named("ErrorCode", sql.Out{Dest: &errorCode}),
        sql.Named("ErrorDescription", sql.Out{Dest: &errorString}),
        sql.Named("WarningCode", sql.Out{Dest: &warningCode}),
        sql.Named("WarningDescription", sql.Out{Dest: &warningString}),
    )
    if err != nil {
        return err
    }

Error reflect: call of reflect.Value.Type on zero Value

[Fank]

It works if i ensure that the output parameter in the stored prodecure can not be null, but on some procedures they are null and i need to handle it.

[denisenkom]

I will take a look

[denisenkom]

I cannot reproduce, I tried following stored procedure:

CREATE PROCEDURE abassign
   @aid INT,
   @bid INT OUTPUT,
   @cstr NVARCHAR(2000) OUTPUT,
   @p4 INT = NULL OUTPUT
AS
BEGIN
   SELECT @bid = @aid, @cstr = 'OK';
END;

Can you provide an example stored procedure which would trigger an error?

[denisenkom]

Try again on the latest version, I believe this is fixed now

[Fank]

Nope still error with latest code.

Example:

CREATE PROCEDURE [sao].ASD
    @A AS INT OUTPUT,
    @B AS VARCHAR(8000) OUTPUT
AS
IF @@TranCount > 0
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from interfering with SELECT statements.
    SET NOCOUNT ON;

END ELSE BEGIN
  RaisError('no Transaction',16,1);
  RETURN(-100000)
END
GOl

type Test struct {
    A *int
    B *string
}

func foo() {
    ....
    t := Test{}
    _, err = tx.ExecContext(
        ctx,
        "sao.ASD",
        sql.Named("A", sql.Out{Dest: &t.A}),
        sql.Named("B", sql.Out{Dest: &t.B}),
    )
    if err != nil {
        return nil, err
    }
}

sql log bit 127:

2018/06/12 15:07:06 initiating response reading
2018/06/12 15:07:06 got token tokenEnvChange
2018/06/12 15:07:06 got token tokenDone
2018/06/12 15:07:06 got DONE or DONEPROC status=0
2018/06/12 15:07:06 response finished
2018/06/12 15:07:06 initiating response reading
2018/06/12 15:07:06 got token tokenEnvChange
2018/06/12 15:07:06 BEGIN TRANSACTION 6500000002
2018/06/12 15:07:06 got token tokenDone
2018/06/12 15:07:06 got DONE or DONEPROC status=0
2018/06/12 15:07:06 response finished
2018/06/12 15:07:06 sao.ASD
2018/06/12 15:07:06     @A  {{} <nil> false}
2018/06/12 15:07:06     @B  {{} <nil> false}
2018/06/12 15:07:06 initiating response reading
2018/06/12 15:07:06 got token tokenError
2018/06/12 15:07:06 got ERROR 8015 The incoming tabular data stream (TDS) remote procedure call (RPC) protocol stream is incorrect. Parameter 1 ("@A"): Data type 0x1F is an untyped NULL but is marked as an output parameter.
2018/06/12 15:07:06 The incoming tabular data stream (TDS) remote procedure call (RPC) protocol stream is incorrect. Parameter 1 ("@A"): Data type 0x1F is an untyped NULL but is marked as an output parameter.
2018/06/12 15:07:06 got token tokenEnvChange
2018/06/12 15:07:06 ROLLBACK TRANSACTION 6500000002
2018/06/12 15:07:06 got token tokenDone
2018/06/12 15:07:06 got DONE or DONEPROC status=2
2018/06/12 15:07:06 response finished
2018/06/12 15:07:06 initiating response reading
2018/06/12 15:07:06 got token tokenError
2018/06/12 15:07:06 got ERROR 3903 The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION.
2018/06/12 15:07:06 The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION.
2018/06/12 15:07:06 got token tokenDone
2018/06/12 15:07:06 got DONE or DONEPROC status=2
2018/06/12 15:07:06 response finished
ERRO[0000] mssql: The incoming tabular data stream (TDS) remote procedure call (RPC) protocol stream is incorrect. Parameter 1 ("@A"): Data type 0x1F is an untyped NULL but is marked as an output parameter. 
2018/06/12 15:07:06 

[denisenkom]

You are passing pointer to a pointer, you should instead do: sql.Out{Dest: t.A}

[Fank]

If i remove the pointer so it looks like:

    _, err := n.tx.ExecContext(
        ctx,
        "sao.ASD",
        sql.Named("A", sql.Out{Dest: t.A}),
        sql.Named("B", sql.Out{Dest: t.B}),
    )

i get:

2018/06/19 09:46:02 http: panic serving 127.0.0.1:52088: reflect: call of reflect.Value.Interface on zero Value
goroutine 242 [running]:
net/http.(*conn).serve.func1(0xc4207beaa0)
    /usr/local/go/src/net/http/server.go:1726 +0xd0
panic(0xe42ce0, 0xc4205fa3c0)
    /usr/local/go/src/runtime/panic.go:502 +0x229
reflect.valueInterface(0x0, 0x0, 0x0, 0x1, 0x0, 0x0)
    /usr/local/go/src/reflect/value.go:953 +0x1a3
reflect.Value.Interface(0x0, 0x0, 0x0, 0x0, 0x0)
    /usr/local/go/src/reflect/value.go:948 +0x44
github.com/denisenkom/go-mssqldb.(*Conn).CheckNamedValue(0xc42001d7a0, 0xc420857950, 0xdf0a01, 0x0)
    /home/fkinder/go/src/v/github.com/denisenkom/go-mssqldb@v0.0.0-20180612031952-fffa5add55b3/mssql_go19.go:60 +0x431
database/sql/driver.(NamedValueChecker).CheckNamedValue-fm(0xc420857950, 0x1, 0x0)
    /usr/local/go/src/database/sql/convert.go:169 +0x39
database/sql.driverArgsConnLocked(0x11a1760, 0xc42001d7a0, 0xc420511150, 0xc4205113d0, 0x2, 0x2, 0x42bd30, 0xc420511018, 0xc4201c5bf0, 0xc420802480, ...)
    /usr/local/go/src/database/sql/convert.go:175 +0x1a4
database/sql.resultFromStatement(0x11a4860, 0xc4201c5bf0, 0x11a1760, 0xc42001d7a0, 0xc420511150, 0xc4205113d0, 0x2, 0x2, 0x0, 0x0, ...)
    /usr/local/go/src/database/sql/sql.go:2260 +0xe0
database/sql.(*DB).execDC(0xc4205e5680, 0x11a4860, 0xc4201c5bf0, 0xc4201ad780, 0xc420825a50, 0xfbaea5, 0x7, 0xc4205113d0, 0x2, 0x2, ...)
    /usr/local/go/src/database/sql/sql.go:1410 +0x2bc
database/sql.(*Tx).ExecContext(0xc4201ac400, 0x11a4860, 0xc4201c5bf0, 0xfbaea5, 0x7, 0xc4205113d0, 0x2, 0x2, 0xc4206784d0, 0x0, ...)
    /usr/local/go/src/database/sql/sql.go:2112 +0xef
git.mcl.de/negsoft/api/src/negsoft.(*NegSoft).CreateOrder(0xc4200b4880, 0x11a4860, 0xc4201c5bf0, 0x597d7, 0xc4205be6a0, 0x7, 0x0, 0xf4b860, 0xc4205da368)
    /home/fkinder/go/src/git.mcl.de/negsoft/api/src/negsoft/erp_sales_order.go:49 +0x2b2
git.mcl.de/negsoft/api/src/negsoft.(*NegSoft).CreateOrderWithPositions(0xc4200b4880, 0x11a4860, 0xc4201c5bf0, 0x597d7, 0xc4205be6a0, 0x7, 0x0, 0xc4205be6a7, 0x3, 0xc4200fb380, ...)
    /home/fkinder/go/src/git.mcl.de/negsoft/api/src/negsoft/erp_sales_order.go:113 +0x220
git.mcl.de/negsoft/api/src/controller.CreateOrderWithPositions(0x11a1660, 0xc420610100, 0xc420776400)
    /home/fkinder/go/src/git.mcl.de/negsoft/api/src/controller/erp_sales_order.go:21 +0x182
net/http.HandlerFunc.ServeHTTP(0x10d1770, 0x11a1660, 0xc420610100, 0xc420776400)
    /usr/local/go/src/net/http/server.go:1947 +0x44
git.mcl.de/negsoft/api/src/middleware.MetricHandler.func1(0x11a3ae0, 0xc42068aa80, 0xc420776400)
    /home/fkinder/go/src/git.mcl.de/negsoft/api/src/middleware/metric.go:46 +0x19f
net/http.HandlerFunc.ServeHTTP(0xc42060b1a0, 0x11a3ae0, 0xc42068aa80, 0xc420776400)
    /usr/local/go/src/net/http/server.go:1947 +0x44
git.mcl.de/negsoft/api/src/middleware.Limiter.func1(0x11a3ae0, 0xc42068aa80, 0xc420776400)
    /home/fkinder/go/src/git.mcl.de/negsoft/api/src/middleware/limiter.go:78 +0x81
net/http.HandlerFunc.ServeHTTP(0xc42060b1c0, 0x11a3ae0, 0xc42068aa80, 0xc420776400)
    /usr/local/go/src/net/http/server.go:1947 +0x44
git.mcl.de/negsoft/api/src/middleware.AuthorizationHandler.func1(0x11a3ae0, 0xc42068aa80, 0xc420776400)
    /home/fkinder/go/src/git.mcl.de/negsoft/api/src/middleware/authorization.go:74 +0x4c0
net/http.HandlerFunc.ServeHTTP(0xc42060b1e0, 0x11a3ae0, 0xc42068aa80, 0xc420776400)
    /usr/local/go/src/net/http/server.go:1947 +0x44
git.mcl.de/negsoft/api/src/middleware.SessionHandler.func1(0x11a3ae0, 0xc42068aa80, 0xc420776400)
    /home/fkinder/go/src/git.mcl.de/negsoft/api/src/middleware/session.go:113 +0x627
net/http.HandlerFunc.ServeHTTP(0xc42060b200, 0x11a3ae0, 0xc42068aa80, 0xc420776400)
    /usr/local/go/src/net/http/server.go:1947 +0x44
github.com/gorilla/mux.(*Router).ServeHTTP(0xc42041c070, 0x11a3ae0, 0xc42068aa80, 0xc420776400)
    /home/fkinder/go/src/v/github.com/gorilla/mux@v0.0.0-20180513032233-e3702bed27f0/mux.go:162 +0xed
github.com/rs/cors.(*Cors).Handler.func1(0x11a3ae0, 0xc42068aa80, 0xc420776000)
    /home/fkinder/go/src/v/github.com/rs/cors@v0.0.0-20170608165155-8dd4211afb5d/cors.go:200 +0xfe
net/http.HandlerFunc.ServeHTTP(0xc4205fa000, 0x11a3ae0, 0xc42068aa80, 0xc420776000)
    /usr/local/go/src/net/http/server.go:1947 +0x44
net/http.serverHandler.ServeHTTP(0xc4207441a0, 0x11a3ae0, 0xc42068aa80, 0xc420776000)
    /usr/local/go/src/net/http/server.go:2694 +0xbc
net/http.(*conn).serve(0xc4207beaa0, 0x11a47a0, 0xc4204a01c0)
    /usr/local/go/src/net/http/server.go:1830 +0x651
created by net/http.(*Server).Serve
    /usr/local/go/src/net/http/server.go:2795 +0x27b

It's an older code link to line here https://github.com/denisenkom/go-mssqldb/blob/fffa5add55b3dfb6132f82414ab423165a360a4f/mssql_go19.go#L60

[denisenkom]

I think you haven't initialized pointers in t.A and t.B, I added additional validation for uninitialized pointers, can you try again on latest version?

[Fank]

The example above works, but i think you misunderstand what i want to have.

What i want is to have an output parameter which comes from a struct and can be nil, this would help me to reduce code and complexity.

• Example 1: Where i have to define every single output variable in a own variable, if i want to return a struct with all that values. I need to build a struct after the SQL call and assign every single variable to a struct key and return it. Bad behavior.

  func foo() {
  ....
  orderID := 0
  errorCode := 0
  errorString := ""
  warningCode := 0
  warningString := ""
  
  _, err = tx.ExecContext(
      ctx,
      "sao.NegSoft_Proc_OrderCreate",
      sql.Named("ContactId", request.ContactID),
      sql.Named("OrderDescription", "Test"),
      sql.Named("OrderId", sql.Out{Dest: &orderID}),
      sql.Named("OperatorId", n.Operator.UserID),
      sql.Named("ErrorCode", sql.Out{Dest: &errorCode}),
      sql.Named("ErrorDescription", sql.Out{Dest: &errorString}),
      sql.Named("WarningCode", sql.Out{Dest: &warningCode}),
      sql.Named("WarningDescription", sql.Out{Dest: &warningString}),
  )
  if err != nil {
      return err
  }
  }

• Example 2: From your message above which works. I have to init every key which can be a pointer with a value, to use them (Still don't work with nil values). Better than example 1, but still bad behavior.

  
  type Test struct {
  A *int
  B *string
  }

func foo() { .... tA := 0 tB := "" t := Test{ A: &tA, B: &tB, }

_, err := n.tx.ExecContext(
    ctx,
    "sao.ASD",
    sql.Named("A", sql.Out{Dest: t.A}),
    sql.Named("B", sql.Out{Dest: t.B}),
)
if err != nil {
    return nil, err
}

}

- Example 3:
I just need to define a struct and assign the keys to it, so it will receive content if content exists, and will be nil if SQL returns `null`. No extra declaration or initialization needed. And after the SQL statement i can return the struct `t` without changing anything, good behavior.
```go
type Test struct {
    A *int
    B *string
}

func foo() {
    ....
    t := Test{}
    _, err = tx.ExecContext(
        ctx,
        "sao.ASD",
        sql.Named("A", sql.Out{Dest: &t.A}),
        sql.Named("B", sql.Out{Dest: &t.B}),
    )
    if err != nil {
        return nil, err
    }
}

So but know the problem i have, is that this example still causes the error i wanted to be fixed:

ALTER PROCEDURE [sao].[ASD]
    @A AS INT OUTPUT,
    @B AS VARCHAR(8000) OUTPUT
AS
IF @@TranCount > 0
BEGIN
    SET NOCOUNT ON;

    -- If i remove the next 2 lines everything works because i don't touche the output
    -- varsbut i need to change them, because these will be forwarded to other
    -- stored procedures, which i can't controll
    SET @A = NULL
    SET @B = NULL
END ELSE BEGIN
  RaisError('no Transaction',16,1);
  RETURN(-100000)
END

I know the behavior from other golang libraries that if i attach a pointer to something, it will set the type behind the pointer internally and attach it to the pointer, if value is provided otherwise it will be set to nil.

I hope it is now clear, what the problem is and which behavior i want to have.

[denisenkom]

It seems to me that the correct way to deal with such cases is to use Null* types, for example NullInt64, would you agree?

Currently Null* types are not supported by the driver for OUTPUT parameters. I am looking into adding support.

[denisenkom]

Support for Null* types is now available.

Adding support for pointer types would take much longer, I will probably look into that in next few weeks.