Golang:- Microsoft SQL Server Sample – “Quickstart: Use Golang to query a database”

Background

Will like to touch on a very subtle error that I sheepishly encountered in one of Microsoft’s generous sample code.

Sample

Here is the sample code:-

Azure / Azure SQL / SQL Database
Quickstart: Use Golang to query a database in Azure SQL Database or Azure SQL Managed Instance
Link

 

Lineage

Here are other posts along this subject material:

  1. Golang – Package – denisenkom – go-mssqldb – Sample – Microsoft
    Link

 

Error

Here is the error:-

Error Item

Image

Text


Error creating Employee: sql: Scan error on column index 0, name "": converting NULL to int64 is unsupported

Offending Code


tsql := "INSERT INTO TestSchema.Employees (Name, Location) VALUES (@Name, @Location); select convert(bigint, SCOPE_IDENTITY());"

 

Issues

SCOPE_IDENTITY

One of the issues is that the code assumes that the insert statement succeeds.  And, that the subsequent “SCOPE_IDENTITY” will return an actual value.

If the insert fails, “select convert(bigint, SCOPE_IDENTITY())” returns NULL.

SCAN into int64

Here is the code that captures the value of the previously assigned identity value.


var newID int64

err = row.Scan(&newID)

The preceding code fails as an attempt to cast null into a variable declared as an int64 data type fails.

It is a guiding error:-

converting NULL to int64 is unsupported

 

Remediation

Code Change

Original


tsql := "INSERT INTO TestSchema.Employees (Name, Location) VALUES (@Name, @Location); select convert(bigint, SCOPE_IDENTITY());"

Revised


tsql := "INSERT INTO TestSchema.Employees (Name, Location) VALUES (@Name, @Location); "
tsql += "select isNull(SCOPE_IDENTITY(), -1);"

 

Summary

Nothing to life.

Avoid unnecessary convert.

And, if one needs to, please check beforehand for null or use one of the safe data convert functions.

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s