Variables update to Azure SQL Server

Hi,
I am a Newbie to GO language , working on Beacon(Bluetooth Low Energy) Receivers on aRaspberry Pi(Linux Board). Got to work to receive data from beacons , and my next task is to upload the same to azure sql server, which i am unable to insert the received variables into the sql statement . I am posting my code (sql code from azure samples) . Kindly provide the way to insert variables … recvid, recvtype, b.uuid, rssi . etc.

package main

import (
“fmt”
“log”
“context”
“strconv”

"github.com/paypal/gatt"
"github.com/paypal/gatt/examples/option"
    _ "github.com/denisenkom/go-mssqldb"
    "database/sql"

)

var db *sql.DB

var recvid string = “D1” – TO BE INSERTED INTO SQL
var recvtype string =“SLAVE” – TO BE INSERTED INTO SQL

func onPeripheralDiscovered(periphera gatt.Peripheral, advretisement *gatt.Advertisement, rssi int) {
b, err := NewiBeacon(advretisement.ManufacturerData)
if err == nil {
fmt.Println("UUID: ", b.uuid) — – TO BE INSERTED INTO SQL
fmt.Println("Major: ", b.major)
fmt.Println("Minor: ", b.minor)
fmt.Println("RSSI: ", rssi) — – TO BE INSERTED INTO SQL

          rssival := strconv.Itoa(rssi)

          fmt.Println("----RSSI STING: ", rssival)  

insertvalues()

}}

func insertvalues(){
ctx := context.Background()

result, err := db.ExecContext(ctx,“INSERT INTO LOCATION_PAYLOAD (RSSI) VALUES (rssival)”) – NOT WORKING

if err != nil {
log.Fatal("Error creating connection pool: ", err.Error())
}
fmt.Println(“RESULT”,result)
}

func main() {

    // Build connection string
connString := fmt.Sprintf("server=%s;user id=%s;password=%s;port=%d;database=%s;",
    server, user, password, port, database)

var err error

// Create connection pool
db, err = sql.Open("sqlserver", connString)
if err != nil {
    log.Fatal("Error creating connection pool: ", err.Error())
}
ctx := context.Background()
err = db.PingContext(ctx)
if err != nil {
    log.Fatal(err.Error())
}
fmt.Printf("Connected!\n")

}

Please, put your entire example code into code box to be more readable and try this:

VALUES(?)", rssival)

Also is possible on some servers to have ([RSSI]) instead (RSSI). Did you tried your commands in some mssql client (vscode,ssms,etc) before?

Not tried on any IDE as the Linux board only has support to work on the terminal. On the above suggestion of using “?”, is not working. Kindly provide the way or support link to prepare the insert statement. No specific need of context, any prepared method or function type is also will solve my problem.

Thank you.

Here is the complete code

////////////////////////////////////////////////////////////////////////////////////////////////////////////////////

var db *sql.DB
var recvid string = “D1”  -- VALUE TO BE INSERTED -- 
var recvtype string =“SLAVE” -- VALUE TO BE INSERTED -- 


func onPeripheralDiscovered(periphera gatt.Peripheral, advretisement *gatt.Advertisement, rssi int) {
	b, err := NewiBeacon(advretisement.ManufacturerData)
	if err == nil {
		fmt.Println("UUID: ", b.uuid)    -- VALUE TO BE INSERTED -- 
		fmt.Println("Major: ", b.major)  -- VALUE TO BE INSERTED -- 
		fmt.Println("Minor: ", b.minor)  -- VALUE TO BE INSERTED -- 
		fmt.Println("RSSI: ", rssi)     -- VALUE TO BE INSERTED -- 
              
              rssival := strconv.Itoa(rssi)
              fmt.Println("----RSSI STING: ", rssival)  

  ctx := context.Background()
  result, err := db.ExecContext(ctx,"INSERT INTO LOCATION_PAYLOAD (RSSI) VALUES (rssival)")
  if err != nil {
    log.Fatal("Error creating connection pool: ", err.Error())
    }
  fmt.Println("RESULT",result)
 
}}


func main() {

        // Build connection string
    connString := fmt.Sprintf("server=%s;user id=%s;password=%s;port=%d;database=%s;",
        server, user, password, port, database)

    var err error

    // Create connection pool
    db, err = sql.Open("sqlserver", connString)
    if err != nil {
        log.Fatal("Error creating connection pool: ", err.Error())
    }
    ctx := context.Background()
    err = db.PingContext(ctx)
    if err != nil {
        log.Fatal(err.Error())
    }
    fmt.Printf("Connected!\n")
     
}

/////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////

_, err := db.Exec("INSERT INTO location_payload ([RSSI]) VALUES(?)", rssival)
if err != nil {
   log.Print(err.Error())
}

Basically this should work. I used exactly this syntax in my existing projects, of course i put your variable into. ? is important because it will put the value of the variable from arguments in the query. I also used Exec not ExecContext.

However, is better to test from a regular computer if the commands realy works with your server and after that codes the commands in Go because is very possible to have other issues than an incorect sintax. Also be sure if the types of fields from the table are the same with your program types of variables which are inserted.

LE: I’m not an mssql expert and i can’t say if the server version or other specific setups of the server counts here.

On the drivers page: https://github.com/denisenkom/go-mssqldb#parameters it says this:

Parameters

The sqlserver driver uses normal MS SQL Server syntax and expects parameters in the sql query to be in the form of either @Name or @p1 to @pN (ordinal position).

db.QueryContext(ctx, `select * from t where ID = @ID and Name = @p2;`, sql.Named("ID", 6), "Bob")

So your code would then be

result, err := db.ExecContext(ctx,"INSERT INTO LOCATION_PAYLOAD (RSSI) VALUES (@p1)", rssival)
1 Like

Seems that this is why the things goes wrong…

https://github.com/denisenkom/go-mssqldb#query-parameter-token-replace-driver-mssql

Query Parameter Token Replace (driver “mssql”)

If you use the driver name “mssql” (rather then “sqlserver”) the SQL text will be loosly parsed and an attempt to extract identifiers using one of

  • ?
  • ?nnn
  • :nnn
  • $nnn

will be used. This is not recommended with SQL Server. There is at least one existing won't fix issue with the query parsing.

Use the native “@Name” parameters instead with the “sqlserver” driver name.

I guess i used an obsolete syntax… :roll_eyes:

This topic was automatically closed 90 days after the last reply. New replies are no longer allowed.