SQL Server ODBC

• This module wraps the ODBC C API for use with SQL Server.
• It can also be used with any ODBC data source by passing a raw ODBC connection string.

Scope

• db.mssql can connect to any ODBC data source when mssql.open(...) or Connection.connect(...) receives a valid ODBC connection string.
• Config can build connection strings from driver, server, port, uid/user, pwd/password, dbname, dsn, and extra ODBC options.
• For non-MSSQL ODBC sources, pass a DSN or raw ODBC string directly.

Dependencies

• ODBC driver manager development headers/libraries (sql.h, sqlext.h).
  • Linux:
    • Install unixODBC development packages (unixodbc, unixodbc-dev, or distro equivalent).
  • macOS:
    • Recommended: install unixODBC + pkg-config:brew install unixodbc pkg-config
    • Then install your DB vendor ODBC driver (for SQL Server: msodbcsql18).
    • Details: see the Microsoft SQL Server ODBC driver installation guide.
  • Windows:
    • odbc32 is included with the Windows SDK on most systems.
    • Details:https://learn.microsoft.com/en-us/sql/connect/odbc/microsoft-odbc-driver-for-sql-server

Windows Notes

Using msvc

• Make sure cl.exe of msvc is accessible from command line. You can run v commands in Visual Studio 2019 Developer Command Prompt to be safe.
• C Headers and dlls can be automatically resolved by msvc.

Using tcc

• Copy those headers to @VEXEROOT\thirdparty\mssql\include. The version number 10.0.18362.0 might differ on your system. Command Prompt commands:

copy "C:\P'C:\Program Files (x86)\Windows Kits\10\Include\10.0.18362.0l.h' thirdparty

• dlls can be automatically resolved by tcc

TODO

• Support ORM

Usage

import db.mssql

fn test_example() ? {
    // connect to server
    mut conn := mssql.connect(
        driver:   'ODBC Driver 18 for SQL Server'
        server:   '127.0.0.1'
        port:     1433
        user:     '<your username>'
        password: '<your password>'
        dbname:   'master'
        options: {
            'Encrypt':                'yes'
            'TrustServerCertificate': 'yes'
        }
    )?

    defer {
        conn.close()
    }

    // get current db name
    mut query := 'SELECT DB_NAME()'
    mut res := conn.query(query)?
    assert res == mssql.Result{
        rows: [mssql.Row{
            vals: ['master']
        }]
        num_rows_affected: -1
    }
}

You can also connect with a raw DSN or ODBC string:

mut conn := mssql.open('DSN=Reporting;Trusted_Connection=Yes')?

fn connect(config Config) !Connection

connect creates a new connection using the provided configuration.

fn open(conn_str string) !Connection

open creates a new connection using a raw ODBC connection string.

struct Config {
pub:
	conn_str string
	dsn      string
	driver   string
	server   string
	port     int
	uid      string
	user     string
	pwd      string
	password string
	// if dbname empty, conn str will not contain Database info,
	// and it is up to the server to choose which db to connect to.
	dbname  string
	options map[string]string
}

fn (cfg Config) get_conn_str() string

get_conn_str builds an ODBC connection string from the configured fields.

struct Connection {
mut:
	henv C.SQLHENV = C.SQLHENV(C.SQL_NULL_HENV) // Environment
	hdbc C.SQLHDBC = C.SQLHDBC(C.SQL_NULL_HDBC) // Connection handle
pub mut:
	conn_str string
}

fn (mut conn Connection) connect(conn_str string) !bool

connect to db

fn (mut conn Connection) close()

close - closes the connection.

fn (mut conn Connection) query(q string) !Result

query executes a sql query

struct Result {
pub mut:
	rows []Row
	// the number of rows affected by sql statement
	num_rows_affected int
}

struct Row {
pub mut:
	vals []string
}

fn (row Row) val(index int) string

val returns the value at index.

fn (row Row) values() []string

values returns all row values.