V support to Microsoft SQL Server or to ODBC connection

[jrcribb]

I'm requesting a module to Microsoft SQL Server or to ODBC connection.
I'll try to contribute with that module, but i'm not sure I can for a while.

[medvednikov]

yes, mssql will be supported
github.com/vlang/mssql
v install mssql

[jrcribb]

Accesing the url you have sended, error 404 appear El lun., 20 abr. 2020 a las 17:52, Alexander Medvednikov (< notifications@github.com>) escribió:

…

yes, mssql will be supported github.com/vlang/mssql v install mssql — You are receiving this because you authored the thread. Reply to this email directly, view it on GitHub <#4531 (comment)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AARV42ECMWQHKU6RXTO5RVTRNSYZJANCNFSM4MMXOCVA> .

[medvednikov]

Sorry, I meant the future module :)

[aisbergde]

Is there any practical way to connect to MS SQL Server at the moment? The marketing of the language reads great. However, for me it's all about the SQL Server.

[medvednikov]

@aisbergde there's no ms sql support yet, connecting can be done via a wrapper on top of C:

https://docs.microsoft.com/en-us/azure/azure-sql/database/develop-cplusplus-simple

like in vlib/pg/pg.v

[mvenditto]

Hi,

I've been recently experimenting in this way too.

Here is a snippet of (rather messy) code that can maybe be useful,
i succesfully tested it on an Ubuntu 20.04 box with SQLServer 2019.

#flag linux -lodbc
#flag linux -lmsodbcsql-17

#include <sql.h>
#include <sqltypes.h>
#include <sqlext.h>

/*
	libodbc should be linked BEFORE
	libmsodbcsql otherwise SQLDriverConnect returns -2
	Some funcs, including SQLDriverConnect, seems
	to be redefined in libmsodbcsql
*/

type SQLChar = byte
type SQLCharPtr = charptr
type SQLInteger = int
type SQLSmallInt = i16
type SQLUSmallInt = u16
type SQLReturn = i16	// SQLSmallInt
type SQLHandle = voidptr
type SQLHenv = voidptr  // SQLHandle
type SQLHstmt = voidptr // SQLHandle
type SQLHdbc = voidptr  // SQLHandle
type SQLPointer = voidptr
type SQLHwnd = voidptr

fn sql_succeded(rc SQLReturn) bool {
	return (rc & ~1) == 0
}

fn C.SQLAllocHandle(SQLSmallInt, SqlHandle, &SQLHenv) SQLReturn
fn C.SQLFreeHandle(SQLSmallInt, &SQLHenv) SQLReturn
fn C.SQLSetEnvAttr(SQLHenv, SQLInteger, SQLPointer, SQLInteger) SQLReturn
fn C.SQLSetConnectAttr(SQLHandle, SQLInteger, SQLPointer, SQLInteger) SQLReturn
fn C.SQLDisconnect(SQLHdbc) SQLReturn
fn C.SQLExecDirect(SQLHdbc, SQLCharPtr, SQLSmallInt) SQLReturn
fn C.SQLBindCol(SQLHstmt, SQLUSmallInt, SQLSmallInt, SQLPointer, C.SQLLEN, &C.SQLLEN) SQLReturn
fn C.SQLFetch(SQLHstmt) SQLReturn
fn C.SQLDriverConnect(
	SQLHandle,
	SQLHwnd,
	SQLCharPtr,
	SQLSmallInt,
	SQLCharPtr,
	SQLSmallInt,
	SQLCharPtr,
	SQLSmallInt,
	&SQLSmallInt,
	SQLUSmallint) SQLReturn
fn C.SQLGetDiagRec(
	SQLSmallInt, 
	SQLHandle, 
	SQLSmallInt, 
	SQLCharPtr,
	&SQLInteger,
	SQLCharPtr,
	SQLSmallInt,
	&SQLSmallInt) SQLReturn

fn check_error(rc SQLReturn, label string) {
	if rc != C.SQL_SUCCESS && rc != C.SQL_SUCCESS_WITH_INFO {
		println('[*] $label: ERROR $rc')
	} else {
		println('[*] $label: SUCCESS')
	}
}

const (
	ResultLength = 256
)

fn main() {
	mut henv := &SQLHandle(C.SQL_NULL_HENV)     // Environment handle
	mut hdbc := &SQLHandle(C.SQL_NULL_HDBC)    	// Connection handle
	mut hstmt := &SQLHandle(C.SQL_NULL_HSTMT)   // Statement handle
	mut rc := 0
	mut str_result := [ResultLength]SQLChar{}
	mut out_str := [1024]SQLChar{}
	mut out_str_len := SQLSmallInt(0)

	rc = C.SQLAllocHandle(C.SQL_HANDLE_ENV, C.SQL_NULL_HANDLE, &henv)
	check_error(rc, 'SQLAllocHandle(SQL_HANDLE_ENV)')

	rc = C.SQLSetEnvAttr(henv, C.SQL_ATTR_ODBC_VERSION, C.SQL_OV_ODBC3, sizeof(int))
	check_error(rc, 'SQLSetEnvAttr(SQL_ATTR_ODBC_VERSION)')

	rc = C.SQLAllocHandle(C.SQL_HANDLE_DBC, henv, &hdbc)
	check_error(rc, 'SQLAllocHandle(SQL_HANDLE_DBC)')

	rc = C.SQLSetConnectAttr(hdbc, C.SQL_LOGIN_TIMEOUT, 5, 0)
	check_error(rc, 'SQLSetConnectAttr(SQL_LOGIN_TIMEOUT)')

	conn_str := 'Driver={ODBC Driver 17 for SQL Server};Server=localhost;Database=TestDB;UID=your_uid;PWD=your_pwd;'

	rc = C.SQLDriverConnect(
		hdbc,
		0,
		conn_str.str,
		C.SQL_NTS,
		0,
		0,
		0,
		C.SQL_DRIVER_NOPROMPT
	)
	check_error(rc, 'SQLDriverConnect()')

	rc = C.SQLAllocHandle(C.SQL_HANDLE_STMT, hdbc, &hstmt)
	check_error(rc, 'SQLAllocHandle(SQL_HANDLE_STMT)')

	query := 'SELECT DB_NAME()'
	rc = C.SQLExecDirect(hstmt, query.str, C.SQL_NTS)
	check_error(rc, 'SQLExecDirect("$query")')

	rc = C.SQLBindCol(hstmt, 1, C.SQL_C_CHAR, &str_result, ResultLength, 0)
	check_error(rc, 'SQLBindCol()')

	mut i := 0
	println('[*] SQLFetch()')
	for {
		rc = C.SQLFetch(hstmt)
		if rc == C.SQL_SUCCESS || rc == C.SQL_SUCCESS_WITH_INFO {
			x := cstring_to_vstring(&str_result)
			println('\t($i) "$x"')
		} else {
			if rc != C.SQL_NO_DATA {
				check_error(rc, 'SQLFetch()')
			} else {
				break
			}
		}
		i = i + 1
	}

	if hstmt != C.SQL_NULL_HSTMT {
		rc = C.SQLFreeHandle(C.SQL_HANDLE_STMT, hstmt)
		check_error(rc, 'SQLFreeHandle(SQL_HANDLE_HSTMT)')
	}

	if hdbc != C.SQL_NULL_HDBC {
		rc = C.SQLDisconnect(hdbc)
		check_error(rc, 'SQLDisconnect')
		rc = C.SQLFreeHandle(C.SQL_HANDLE_DBC, hdbc)
		check_error(rc, 'SQLFreeHandle(SQL_HANDLE_DBC)')
	}

	if henv != C.SQL_NULL_HENV {
		rc = C.SQLFreeHandle(C.SQL_HANDLE_ENV, henv)
		check_error(rc, 'SQLFreeHandle(SQL_HANDLE_ENV)')
	}
}

output:

[*] SQLAllocHandle(SQL_HANDLE_ENV): SUCCESS
[*] SQLSetEnvAttr(SQL_ATTR_ODBC_VERSION): SUCCESS
[*] SQLAllocHandle(SQL_HANDLE_DBC): SUCCESS
[*] SQLSetConnectAttr(SQL_LOGIN_TIMEOUT): SUCCESS
[*] SQLDriverConnect(): SUCCESS
[*] SQLAllocHandle(SQL_HANDLE_STMT): SUCCESS
[*] SQLExecDirect("SELECT DB_NAME()"): SUCCESS
[*] SQLBindCol(): SUCCESS
[*] SQLFetch()
        (0) "TestDB"
[*] SQLFreeHandle(SQL_HANDLE_HSTMT): SUCCESS
[*] SQLDisconnect: SUCCESS
[*] SQLFreeHandle(SQL_HANDLE_DBC): SUCCESS
[*] SQLFreeHandle(SQL_HANDLE_ENV): SUCCESS

Here are a couple useful resources:

• https://docs.microsoft.com/it-it/sql/connect/odbc/linux-mac/installing-the-microsoft-odbc-driver-for-sql-server?view=sql-server-ver15#ubuntu17
• https://docs.microsoft.com/it-it/sql/linux/quickstart-install-connect-ubuntu?view=sql-server-ver15
• https://docs.microsoft.com/it-it/sql/odbc/reference/install/odbc-header-files?view=sql-server-ver15
• https://github.com/microsoft/VCSamples/tree/master/VC2015Samples/ODBC%20database%20sample%20(linux)
• https://github.com/Microsoft/VCSamples/tree/master/VC2015Samples/ODBC%20database%20sample%20%28windows%29
• https://github.com/mkleehammer/pyodbc