Select Example Require Preparestatement

[secoba]

Need more select query with preparestatement ....

[jkaflik]

Hi @secoba

Not sure I understand your need. Could you elaborate?

[fungaren]

package main

import (
	"database/sql"
	"log"
	"time"

	clickhouse "github.com/ClickHouse/clickhouse-go/v2"
)

func ConnectCH(addr string, database string, username string, password string) *sql.DB {
	conn := clickhouse.OpenDB(&clickhouse.Options{
		Addr: []string{addr},
		Auth: clickhouse.Auth{
			Database: database,
			Username: username,
			Password: password,
		},
		Settings: clickhouse.Settings{
			"max_execution_time": 60,
		},
		DialTimeout: time.Second * 30,
		Compression: &clickhouse.Compression{
			Method: clickhouse.CompressionLZ4,
		},
		Debug:                false,
		BlockBufferSize:      10,
		MaxCompressionBuffer: 10240,
		ClientInfo: clickhouse.ClientInfo{
			Products: []struct {
				Name    string
				Version string
			}{{Name: "123", Version: ""}},
		},
	})
	conn.SetMaxIdleConns(10)
	conn.SetMaxOpenConns(20)
	conn.SetConnMaxLifetime(time.Hour)
	if err := conn.Ping(); err != nil {
		conn.Close()
		log.Fatalln(err)
	}
	return conn
}

func q1(db *sql.DB) error {
	stmt, err := db.Prepare(`SELECT name FROM funds WHERE symbol=? LIMIT 1`)
	if err != nil {
		return err
	}
	defer stmt.Close()

	var name string
	rows, err := stmt.Query("abc")
	if err != nil {
		return err
	}
	if rows.Next() {
		if err := rows.Scan(&name); err != nil {
			_ = rows.Close()
			return err
		}
	}
	_ = rows.Close()
	return nil
}

func q2(db *sql.DB) error {
	stmt, err := db.Prepare(`SELECT name FROM funds WHERE symbol={symbol:str} LIMIT 1`)
	if err != nil {
		return err
	}
	defer stmt.Close()

	var name string
	rows, err := stmt.Query(clickhouse.Named("symbol", "abc"))
	if err != nil {
		return err
	}
	if rows.Next() {
		if err := rows.Scan(&name); err != nil {
			_ = rows.Close()
			return err
		}
	}
	_ = rows.Close()
	return nil
}

func main() {
	log.SetFlags(log.LstdFlags | log.Lshortfile)
	db := ConnectCH("10.43.55.162:9000", "test", "test", "test")
	log.Println(q1(db))
	log.Println(q2(db))
}

2023/08/03 00:18:21 test.go:93: code: 62, message: Syntax error: failed at position 37 ('?'): ? LIMIT 1 VALUES. Expected one of: CAST operator, ANY, ALL, NOT, INTERVAL, CASE, DATE, TIMESTAMP, tuple, collection of literals, array, number, literal, NULL, Bool, true, false, string literal, asterisk, qualified asterisk, compound identifier, list of elements, identifier, COLUMNS matcher, COLUMNS, qualified COLUMNS matcher, substitution, MySQL-style global variable
2023/08/03 00:18:21 test.go:94: code: 62, message: Syntax error: failed at position 58 ('VALUES'): VALUES. Expected one of: token, DoubleColon, OR, AND, IS NULL, IS NOT NULL, BETWEEN, NOT BETWEEN, LIKE, ILIKE, NOT LIKE, NOT ILIKE, REGEXP, IN, NOT IN, GLOBAL IN, GLOBAL NOT IN, MOD, DIV, alias, AS, Comma, OFFSET, WITH TIES, BY, FETCH, LIMIT, SETTINGS, UNION, EXCEPT, INTERSECT, INTO OUTFILE, FORMAT, end of query