Error when connecting to sqlserver database

[ilanni2460]

Use the latest version of docker version 0.5.7, use the following method to connect to the sqlserver database and report an error.
`
connections:

• 'sqlserver://saread:[email protected]/sh?database=spdb'

`

The error message is as follows:
`
sql_exporter | 2024/10/18 17:55:29 maxprocs: Leaving GOMAXPROCS=32: CPU quota undefined
sql_exporter | {"build_context":"(go=go1.21.13, platform=linux/amd64, user=, date=, tags=unknown)","caller":"main.go:58","msg":"Starting sql_exporter","ts":"2024-10-18T09:55:29.251726806Z","version_info":"(version=, branch=, revision=e69d565d5ad3b09c002040f3462399e1022de2a3)"}
sql_exporter | {"caller":"exporter.go:103","interval":"5m0s","level":"info","msg":"Started periodically execution of job","name":"example","ts":"2024-10-18T09:55:29.252221881Z"}
sql_exporter | {"caller":"main.go:81","level":"info","listenAddress":":9237","msg":"Listening","ts":"2024-10-18T09:55:29.252336169Z"}
sql_exporter | {"caller":"job.go:408","job":"example","level":"debug","msg":"Starting","ts":"2024-10-18T09:55:29.252356894Z"}
sql_exporter | {"Query:":"SET lock_timeout = 1000","caller":"job.go:551","job":"example","level":"debug","msg":"StartupSQL","ts":"2024-10-18T09:55:30.529707916Z"}
sql_exporter | panic: mssql: Incorrect syntax near '='.
sql_exporter |
sql_exporter | goroutine 24 [running]:
sql_exporter | github.com/jmoiron/sqlx.MustExec(...)
sql_exporter | /src/vendor/github.com/jmoiron/sqlx/sqlx.go:725
sql_exporter | github.com/jmoiron/sqlx.(*DB).MustExec(0xc000918730?, {0xc000964048?, 0x4?}, {0x0?, 0xc0000eedb0?, 0xc0000eede0?})
sql_exporter | /src/vendor/github.com/jmoiron/sqlx/sqlx.go:369 +0x45
sql_exporter | main.(*connection).connect(0xc00097ae00, 0xc0009740b0)
sql_exporter | /src/job.go:552 +0x8e5
sql_exporter | main.(*Job).runOnceConnection(0xc0009740b0, 0xc00097ae00, 0xc0009740b0?)
sql_exporter | /src/job.go:423 +0xa5
sql_exporter | created by main.(*Job).runOnce in goroutine 41
sql_exporter | /src/job.go:474 +0x5d
sql_exporter exited with code 0

`

[dewey]

1. I hope that's not your real username / password
2. Can you replicate it in just a simple Go file with sqlx and your connection string? I think it sounds like a faulty connection string and not related to the exporter.

[ilanni2460]

1. I hope that's not your real username / password

   2. Can you replicate it in just a simple Go file with sqlx and your connection string? I think it sounds like a faulty connection string and not related to the exporter.

Thank you very much for your reply. This password is not real. How to use sqlx?

[ilanni2460]

`

package main

import (
"log"
_ "github.com/denisenkom/go-mssqldb" // SQL Server driver for Go
"github.com/jmoiron/sqlx"
)

func main() {
// Replace the connection string with your actual database connection details.
// Example: "server=localhost;instance=MSSQLSERVER;user id=myuser;password=mypassword;database=mydb;"
dbInfo := "server=10.49.3.80;instance=sh;user id=saread;password=26weercbmjYE;database=spdb;"

// Connect to the database.
db, err := sqlx.Connect("sqlserver", dbInfo)
if err != nil {
	log.Fatalf("Error connecting to the database: %v", err)
}
defer db.Close()

// Define the SQL query.
query := `SELECT t_cjob AS job_name FROM ttt5000`

// Execute the query and handle results.
var results []struct {
	JobName string `db:"job_name"`
}
if err := db.Select(&results, query); err != nil {
	log.Fatalf("Error executing the query: %v", err)
}

// Print results.
for _, r := range results {
	log.Printf("Job Name: %s", r.JobName)
}

}

`

I wrote a simple go program and then executed it and reported an error

this is error：
2024/10/18 19:23:41 Error connecting to the database: mssql: login error: Login failed for user 'saread'.

But using the same authentication information, you can log in normally using the navicat client.

[ilanni2460]

`
package main

import (
"database/sql"
"fmt"
"log"
"net/http"
"time"

"github.com/prometheus/client_golang/prometheus"
"github.com/prometheus/client_golang/prometheus/promauto"
"github.com/prometheus/client_golang/prometheus/promhttp"

_ "github.com/denisenkom/go-mssqldb"

)

func main() {
// 初始化 Prometheus Gauge Vec
jobStateGauge := promauto.NewGaugeVec(prometheus.GaugeOpts{
Name: "job_exec_state",
Help: "Job execution states from the database.",
}, []string{"job_name", "cloud_region", "company", "project", "OS", "table_name", "user"}) // 添加 table_name 和 user 标签

// 设置 HTTP 服务器以导出指标
http.Handle("/metrics", promhttp.Handler())

// 启动 HTTP 服务器
go func() {
	if err := http.ListenAndServe(":2112", nil); err != nil {
		log.Fatalf("Could not start metrics server: %v", err)
	}
}()

// 连接字符串，增加端口号1433
connString := "server=10.49.3.80;port=1433;instance=sh;user id=saread;password=26weercbmjYE;database=spdb"

// 连接数据库
db, err := sql.Open("mssql", connString)
if err != nil {
	log.Fatalf("Error creating connection pool: %v", err)
}
defer db.Close()

// 测试连接
if err := db.Ping(); err != nil {
	log.Fatalf("Error pinging database: %v", err)
}
fmt.Println("Successfully connected!")

// 查询语句
query := `
SELECT t_cjob AS job_name, t_jsta AS job_exec_state, t_user AS [user], 'ttt5000' AS table_name
FROM ttt5000;`

// 定义定时任务
ticker := time.NewTicker(15 * time.Second)

for {
	select {
	case <-ticker.C:
		log.Println("Starting new query...")
		// 执行查询
		rows, err := db.Query(query)
		if err != nil {
			log.Fatalf("Error executing query: %v", err)
		}
		defer rows.Close()

		// 清空指标
		jobStateGauge.Reset()

		// 遍历查询结果
		var jobName string
		var jobExecState int // 假设 job_exec_state 是整数类型的字段
		var tableName string
		var user string
		for rows.Next() {
			if err := rows.Scan(&jobName, &jobExecState, &user, &tableName); err != nil { // 调整 Scan 的顺序
				log.Fatalf("Error scanning row: %v", err)
			}
			jobStateGauge.WithLabelValues(jobName, "cloud_online", "MXG", "MXG-INFRA", "windows", tableName, user).Set(float64(jobExecState))
			log.Printf("Set job_name=%s, table_name=%s, user=%s, and job_exec_state=%d\n", jobName, tableName, user, jobExecState)
			fmt.Printf("Job Name: %s, Table Name: %s, User: %s, Job Execution State: %d\n", jobName, tableName, user, jobExecState)
		}

		// 检查遍历过程中是否有错误发生
		if err := rows.Err(); err != nil {
			log.Fatalf("%v", err)
		}

		log.Println("Query completed.")
	}
}

}

`

@dewey

I used this code and it was ok. I could connect to the sqlserver database normally and output metrics normally.