From f5c4671bfbad96bf346bd7e9a21fc4317b4959df Mon Sep 17 00:00:00 2001
From: Indrajith K L
Date: Sat, 3 Dec 2022 17:00:20 +0530
Subject: Adds most of the tools

---
 v_windows/v/old/vlib/mssql/README.md         |  69 +++++++++++++++
 v_windows/v/old/vlib/mssql/_cdef_nix.c.v     |   6 ++
 v_windows/v/old/vlib/mssql/_cdef_windows.c.v |  12 +++
 v_windows/v/old/vlib/mssql/_cdefs.c.v        |  27 ++++++
 v_windows/v/old/vlib/mssql/config.v          |  20 +++++
 v_windows/v/old/vlib/mssql/mssql.v           | 125 ++++++++++++++++++++++++++
 v_windows/v/old/vlib/mssql/result.v          |  13 +++
 v_windows/v/old/vlib/mssql/stmt_handle.v     | 127 +++++++++++++++++++++++++++
 8 files changed, 399 insertions(+)
 create mode 100644 v_windows/v/old/vlib/mssql/README.md
 create mode 100644 v_windows/v/old/vlib/mssql/_cdef_nix.c.v
 create mode 100644 v_windows/v/old/vlib/mssql/_cdef_windows.c.v
 create mode 100644 v_windows/v/old/vlib/mssql/_cdefs.c.v
 create mode 100644 v_windows/v/old/vlib/mssql/config.v
 create mode 100644 v_windows/v/old/vlib/mssql/mssql.v
 create mode 100644 v_windows/v/old/vlib/mssql/result.v
 create mode 100644 v_windows/v/old/vlib/mssql/stmt_handle.v

(limited to 'v_windows/v/old/vlib/mssql')

diff --git a/v_windows/v/old/vlib/mssql/README.md b/v_windows/v/old/vlib/mssql/README.md
new file mode 100644
index 0000000..ff4fefc
--- /dev/null
+++ b/v_windows/v/old/vlib/mssql/README.md
@@ -0,0 +1,69 @@
+# SQL Server ODBC
+
+* This is a V wrapper of SQL Server ODBC C/C++ library
+
+## Dependencies
+* ODBC C/C++ library
+    * Linux Install: 
+		* Details: https://docs.microsoft.com/en-us/sql/connect/odbc/linux-mac/installing-the-microsoft-odbc-driver-for-sql-server
+        * `msodbcsql17` and `unixodbc-dev` packages are needed
+    * Windows Install:
+		* `odbc` lib is included in windows sdk for most of distributions,
+			so there is no need to install it separately
+		* Details: https://docs.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:
+```cmd
+copy "C:\Program Files (x86)\Windows Kits\10\Include\10.0.18362.0\um\sql.h" thirdparty\mssql\include
+copy "C:\Program Files (x86)\Windows Kits\10\Include\10.0.18362.0\um\sqlext.h" thirdparty\mssql\include
+copy "C:\Program Files (x86)\Windows Kits\10\Include\10.0.18362.0\um\sqltypes.h" thirdparty\mssql\include
+copy "C:\Program Files (x86)\Windows Kits\10\Include\10.0.18362.0\um\sqlucode.h" thirdparty\mssql\include
+copy "C:\Program Files (x86)\Windows Kits\10\Include\10.0.18362.0\shared\sal.h" thirdparty\mssql\include
+copy "C:\Program Files (x86)\Windows Kits\10\Include\10.0.18362.0\shared\concurrencysal.h" thirdparty\mssql\include
+```
+* dlls can be automatically resolved by `tcc`
+
+## TODO
+* Support Mac 
+* Support ORM
+
+## Usage
+```v ignore
+import mssql
+
+fn test_example() ? {
+	// connect to server
+	config := mssql.Config{
+		driver: 'ODBC Driver 17 for SQL Server'
+		server: 'tcp:localhost'
+		uid: '<your username>'
+		pwd: '<your password>'
+	}
+
+	mut conn := mssql.Connection{}
+
+	conn.connect(config.get_conn_str()) ?
+
+	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
+	}
+}
+```
diff --git a/v_windows/v/old/vlib/mssql/_cdef_nix.c.v b/v_windows/v/old/vlib/mssql/_cdef_nix.c.v
new file mode 100644
index 0000000..0a9ec00
--- /dev/null
+++ b/v_windows/v/old/vlib/mssql/_cdef_nix.c.v
@@ -0,0 +1,6 @@
+module mssql
+
+#flag -lodbc
+
+#include <sql.h>
+#include <sqlext.h>
diff --git a/v_windows/v/old/vlib/mssql/_cdef_windows.c.v b/v_windows/v/old/vlib/mssql/_cdef_windows.c.v
new file mode 100644
index 0000000..61724ee
--- /dev/null
+++ b/v_windows/v/old/vlib/mssql/_cdef_windows.c.v
@@ -0,0 +1,12 @@
+module mssql
+
+// mssql module does not support tcc on windows
+
+// odbc32 lib comes with windows sdk and does not need to be installed separately.
+// v builder for msvc can resolve the sdk includes search path, so no need to repeat here.
+#flag windows -lodbc32
+
+// Special handling of sql headers on windows.
+// Source is in v third party folder.
+#flag windows -I@VEXEROOT/thirdparty/mssql/include
+#include <mssql.h>
diff --git a/v_windows/v/old/vlib/mssql/_cdefs.c.v b/v_windows/v/old/vlib/mssql/_cdefs.c.v
new file mode 100644
index 0000000..0f8e205
--- /dev/null
+++ b/v_windows/v/old/vlib/mssql/_cdefs.c.v
@@ -0,0 +1,27 @@
+module mssql
+
+fn C.SQLAllocHandle(HandleType C.SQLSMALLINT, InputHandle C.SQLHANDLE, OutputHandle &C.SQLHANDLE) C.SQLRETURN
+
+fn C.SQLSetEnvAttr(EnvironmentHandle C.SQLHENV, Attribute C.SQLINTEGER, Value C.SQLPOINTER, StringLength C.SQLINTEGER) C.SQLRETURN
+
+fn C.SQLGetDiagRec(HandleType C.SQLSMALLINT, Handle C.SQLHANDLE, RecNumber C.SQLSMALLINT, Sqlstate &C.SQLCHAR, NativeError &C.SQLINTEGER, MessageText &C.SQLCHAR, BufferLength C.SQLSMALLINT, TextLength &C.SQLSMALLINT) C.SQLRETURN
+
+fn C.SQLSetConnectAttr(ConnectionHandle C.SQLHDBC, Attribute C.SQLINTEGER, Value C.SQLPOINTER, StringLength C.SQLINTEGER) C.SQLRETURN
+
+fn C.SQLDriverConnect(hdbc C.SQLHDBC, hwnd C.SQLHWND, szConnStrIn &C.SQLCHAR, cbConnStrIn C.SQLSMALLINT, szConnStrOut &C.SQLCHAR, cbConnStrOutMax C.SQLSMALLINT, pcbConnStrOut &C.SQLSMALLINT, fDriverCompletion C.SQLUSMALLINT) C.SQLRETURN
+
+fn C.SQLDisconnect(ConnectionHandle C.SQLHDBC) C.SQLRETURN
+
+fn C.SQLExecDirect(StatementHandle C.SQLHSTMT, StatementText &C.SQLCHAR, TextLength C.SQLINTEGER) C.SQLRETURN
+
+fn C.SQLBindCol(StatementHandle C.SQLHSTMT, ColumnNumber C.SQLUSMALLINT, TargetType C.SQLSMALLINT, TargetValue C.SQLPOINTER, BufferLength C.SQLLEN, StrLen_or_Ind &C.SQLLEN) C.SQLRETURN
+
+fn C.SQLFetch(StatementHandle C.SQLHSTMT) C.SQLRETURN
+
+fn C.SQLFreeHandle(HandleType C.SQLSMALLINT, Handle C.SQLHANDLE) C.SQLRETURN
+
+fn C.SQLNumResultCols(StatementHandle C.SQLHSTMT, ColumnCount &C.SQLSMALLINT) C.SQLRETURN
+
+fn C.SQLColAttribute(StatementHandle C.SQLHSTMT, ColumnNumber C.SQLUSMALLINT, FieldIdentifier C.SQLUSMALLINT, CharacterAttribute C.SQLPOINTER, BufferLength C.SQLSMALLINT, StringLength C.SQLSMALLINT, NumericAttribute &C.SQLLEN) C.SQLRETURN
+
+fn C.SQLRowCount(StatementHandle C.SQLHSTMT, RowCount &C.SQLLEN) C.SQLRETURN
diff --git a/v_windows/v/old/vlib/mssql/config.v b/v_windows/v/old/vlib/mssql/config.v
new file mode 100644
index 0000000..7f26d6f
--- /dev/null
+++ b/v_windows/v/old/vlib/mssql/config.v
@@ -0,0 +1,20 @@
+module mssql
+
+pub struct Config {
+pub:
+	driver string
+	server string
+	uid    string
+	pwd    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
+}
+
+pub fn (cfg Config) get_conn_str() string {
+	mut str := 'Driver=$cfg.driver;Server=$cfg.server;UID=$cfg.uid;PWD=$cfg.pwd'
+	if cfg.dbname != '' {
+		str += ';Database=$cfg.dbname'
+	}
+	return str
+}
diff --git a/v_windows/v/old/vlib/mssql/mssql.v b/v_windows/v/old/vlib/mssql/mssql.v
new file mode 100644
index 0000000..a885e4b
--- /dev/null
+++ b/v_windows/v/old/vlib/mssql/mssql.v
@@ -0,0 +1,125 @@
+module mssql
+
+pub 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
+}
+
+// connect to db
+pub fn (mut conn Connection) connect(conn_str string) ?bool {
+	conn_str_c := unsafe { &C.SQLCHAR(conn_str.str) }
+	mut retcode := C.SQLRETURN(C.SQL_SUCCESS)
+	// Allocate environment handle
+	retcode = C.SQLAllocHandle(C.SQLSMALLINT(C.SQL_HANDLE_ENV), C.SQLHANDLE(C.SQL_NULL_HANDLE),
+		unsafe { &C.SQLHANDLE(&conn.henv) })
+	check_error(retcode, 'SQLAllocHandle(SQL_HANDLE_ENV)', C.SQLHANDLE(conn.henv), C.SQLSMALLINT(C.SQL_HANDLE_ENV)) ?
+
+	// Set the ODBC version environment attribute
+	retcode = C.SQLSetEnvAttr(conn.henv, C.SQLINTEGER(C.SQL_ATTR_ODBC_VERSION), &C.SQLPOINTER(C.SQL_OV_ODBC3),
+		C.SQLINTEGER(0))
+	check_error(retcode, 'SQLSetEnvAttr(SQL_ATTR_ODBC_VERSION)', C.SQLHANDLE(conn.henv),
+		C.SQLSMALLINT(C.SQL_HANDLE_ENV)) ?
+
+	// Allocate connection handle
+	retcode = C.SQLAllocHandle(C.SQLSMALLINT(C.SQL_HANDLE_DBC), C.SQLHANDLE(conn.henv),
+		unsafe { &C.SQLHANDLE(&conn.hdbc) })
+	check_error(retcode, 'SQLAllocHandle(SQL_HANDLE_DBC)', C.SQLHANDLE(conn.hdbc), C.SQLSMALLINT(C.SQL_HANDLE_DBC)) ?
+
+	// Set login timeout to 5 seconds
+	retcode = C.SQLSetConnectAttr(conn.hdbc, C.SQLINTEGER(C.SQL_LOGIN_TIMEOUT), C.SQLPOINTER(5),
+		C.SQLINTEGER(0))
+	check_error(retcode, 'SQLSetConnectAttr(SQL_LOGIN_TIMEOUT)', C.SQLHANDLE(conn.hdbc),
+		C.SQLSMALLINT(C.SQL_HANDLE_DBC)) ?
+
+	// Connect to data source
+	mut outstr := [1024]char{}
+	mut outstrlen := C.SQLSMALLINT(0)
+	retcode = C.SQLDriverConnect(conn.hdbc, C.SQLHWND(0), conn_str_c, C.SQLSMALLINT(C.SQL_NTS),
+		&C.SQLCHAR(&outstr[0]), C.SQLSMALLINT(sizeof(outstr)), &outstrlen, C.SQLUSMALLINT(C.SQL_DRIVER_NOPROMPT))
+	check_error(retcode, 'SQLDriverConnect()', C.SQLHANDLE(conn.hdbc), C.SQLSMALLINT(C.SQL_HANDLE_DBC)) ?
+	conn.conn_str = conn_str
+	return true
+}
+
+// close - closes the connection.
+pub fn (mut conn Connection) close() {
+	// Connection
+	if conn.hdbc != C.SQLHDBC(C.SQL_NULL_HDBC) {
+		C.SQLDisconnect(conn.hdbc)
+		C.SQLFreeHandle(C.SQLSMALLINT(C.SQL_HANDLE_DBC), C.SQLHANDLE(conn.hdbc))
+		conn.hdbc = C.SQLHDBC(C.SQL_NULL_HDBC)
+	}
+	// Environment
+	if conn.henv != C.SQLHENV(C.SQL_NULL_HENV) {
+		C.SQLFreeHandle(C.SQLSMALLINT(C.SQL_HANDLE_ENV), C.SQLHANDLE(conn.henv))
+		conn.henv = C.SQLHENV(C.SQL_NULL_HENV)
+	}
+}
+
+// query executes a sql query
+pub fn (mut conn Connection) query(q string) ?Result {
+	mut hstmt := new_hstmt(conn.hdbc) ?
+	defer {
+		hstmt.close()
+	}
+
+	hstmt.exec(q) ?
+
+	affected := hstmt.retrieve_affected_rows() ?
+
+	hstmt.prepare_read() ?
+	raw_rows := hstmt.read_rows() ?
+
+	mut res := Result{
+		rows: []Row{}
+		num_rows_affected: affected
+	}
+
+	for rr in raw_rows {
+		res.rows << Row{
+			vals: rr
+		}
+	}
+
+	return res
+}
+
+// check_error checks odbc return code and extract error string if available
+fn check_error(e C.SQLRETURN, s string, h C.SQLHANDLE, t C.SQLSMALLINT) ? {
+	if e != C.SQLRETURN(C.SQL_SUCCESS) && e != C.SQLRETURN(C.SQL_SUCCESS_WITH_INFO) {
+		err_str := extract_error(s, h, t)
+		return error(err_str)
+	}
+}
+
+// extract_error extracts error string from odbc
+fn extract_error(fnName string, handle C.SQLHANDLE, tp C.SQLSMALLINT) string {
+	mut err_str := fnName
+	mut i := 0
+	mut native_error := C.SQLINTEGER(0)
+	mut sql_state := [7]char{}
+	mut message_text := [256]char{}
+	mut text_length := C.SQLSMALLINT(0)
+	mut ret := C.SQLRETURN(C.SQL_SUCCESS)
+
+	for ret == C.SQLRETURN(C.SQL_SUCCESS) {
+		i++
+		ret = C.SQLGetDiagRec(tp, handle, C.SQLSMALLINT(i), &C.SQLCHAR(&sql_state[0]),
+			&native_error, &C.SQLCHAR(&message_text[0]), C.SQLSMALLINT(sizeof(message_text)),
+			&text_length)
+
+		// add driver error string
+		if ret == C.SQLRETURN(C.SQL_SUCCESS) || ret == C.SQLRETURN(C.SQL_SUCCESS_WITH_INFO) {
+			unsafe {
+				state_str := (&sql_state[0]).vstring()
+				native_error_code := int(native_error)
+				txt_str := (&message_text[0]).vstring()
+				err_str += '\n\todbc=$state_str:$i:$native_error_code:$txt_str'
+			}
+		}
+	}
+	return err_str
+}
diff --git a/v_windows/v/old/vlib/mssql/result.v b/v_windows/v/old/vlib/mssql/result.v
new file mode 100644
index 0000000..f5483a2
--- /dev/null
+++ b/v_windows/v/old/vlib/mssql/result.v
@@ -0,0 +1,13 @@
+module mssql
+
+pub struct Row {
+pub mut:
+	vals []string
+}
+
+pub struct Result {
+pub mut:
+	rows []Row
+	// the number of rows affected by sql statement
+	num_rows_affected int
+}
diff --git a/v_windows/v/old/vlib/mssql/stmt_handle.v b/v_windows/v/old/vlib/mssql/stmt_handle.v
new file mode 100644
index 0000000..8e0b792
--- /dev/null
+++ b/v_windows/v/old/vlib/mssql/stmt_handle.v
@@ -0,0 +1,127 @@
+module mssql
+
+// HStmt is handle for sql statement
+struct HStmt {
+mut:
+	// db connection reference. Owner is Connection struct.
+	hdbc C.SQLHDBC = C.SQLHDBC(C.SQL_NULL_HDBC)
+	// statement handle
+	hstmt C.SQLHSTMT = C.SQLHSTMT(C.SQL_NULL_HSTMT)
+	// fields used for computation
+	column_count int = -1
+	// columns
+	buffers [][]char
+	// indicators for each column
+	indicators []C.SQLLEN
+}
+
+// new_hstmt constructs a new statement handle
+fn new_hstmt(hdbc C.SQLHDBC) ?HStmt {
+	mut retcode := C.SQLRETURN(C.SQL_SUCCESS)
+	mut hstmt := C.SQLHSTMT(C.SQL_NULL_HSTMT)
+	// Allocate statement handle
+	retcode = C.SQLAllocHandle(C.SQLSMALLINT(C.SQL_HANDLE_STMT), C.SQLHANDLE(hdbc), unsafe { &C.SQLHANDLE(&hstmt) })
+	check_error(retcode, 'SQLAllocHandle(SQL_HANDLE_STMT)', C.SQLHANDLE(hstmt), C.SQLSMALLINT(C.SQL_HANDLE_STMT)) ?
+
+	return HStmt{
+		hdbc: hdbc
+		hstmt: hstmt
+	}
+}
+
+// close the statement handle
+fn (mut h HStmt) close() {
+	// Deallocate handle
+	if h.hstmt != C.SQLHSTMT(C.SQL_NULL_HSTMT) {
+		// check error code?
+		C.SQLFreeHandle(C.SQLSMALLINT(C.SQL_HANDLE_STMT), C.SQLHANDLE(h.hstmt))
+		h.hstmt = C.SQLHSTMT(C.SQL_NULL_HSTMT)
+	}
+}
+
+// exec executes a Sql statement. Result is stored in odbc driver, and not yet read.
+fn (h HStmt) exec(sql string) ? {
+	retcode := C.SQLExecDirect(h.hstmt, sql.str, C.SQLINTEGER(C.SQL_NTS))
+	check_error(retcode, 'SQLExecDirect()', C.SQLHANDLE(h.hstmt), C.SQLSMALLINT(C.SQL_HANDLE_STMT)) ?
+}
+
+// retrieve_affected_rows returns number of rows affected/modified by the last operation. -1 if not applicable.
+fn (h HStmt) retrieve_affected_rows() ?int {
+	count_ret := C.SQLLEN(0)
+	retcode := C.SQLRowCount(h.hstmt, &count_ret)
+	check_error(retcode, 'SQLRowCount()', C.SQLHANDLE(h.hstmt), C.SQLSMALLINT(C.SQL_HANDLE_STMT)) ?
+	return int(count_ret)
+}
+
+fn (h HStmt) retrieve_column_count() ?int {
+	mut retcode := C.SQLRETURN(C.SQL_SUCCESS)
+	col_count_buff := C.SQLSMALLINT(0)
+	retcode = C.SQLNumResultCols(h.hstmt, &col_count_buff)
+	check_error(retcode, 'SQLNumResultCols()', C.SQLHANDLE(h.hstmt), C.SQLSMALLINT(C.SQL_HANDLE_STMT)) ?
+	return int(col_count_buff)
+}
+
+// allocate buffers and bind them to drivers
+fn (mut h HStmt) prepare_read() ? {
+	mut retcode := C.SQLRETURN(C.SQL_SUCCESS)
+
+	column_count := h.retrieve_column_count() ?
+	h.column_count = column_count // remember the count because read will need it
+
+	h.buffers = [][]char{len: h.column_count, cap: h.column_count}
+	h.indicators = []C.SQLLEN{len: h.column_count, cap: h.column_count}
+
+	for i := 0; i < h.column_count; i++ {
+		i_col := C.SQLUSMALLINT(i + 1) // col number starts with 1
+		size_ret := C.SQLLEN(0)
+		// find out buffer size needed to read data in this column
+		retcode = C.SQLColAttribute(h.hstmt, i_col, C.SQLUSMALLINT(C.SQL_DESC_LENGTH),
+			C.SQLPOINTER(0), C.SQLSMALLINT(0), C.SQLSMALLINT(0), &size_ret)
+		check_error(retcode, 'SQLColAttribute()', C.SQLHANDLE(h.hstmt), C.SQLSMALLINT(C.SQL_HANDLE_STMT)) ?
+
+		// buffer allocation is the size + 1 to include termination char, since SQL_DESC_LENGTH does not include it.
+		allocate_size := size_ret + C.SQLLEN(1)
+		allocate_size_int := int(allocate_size)
+		buff := []char{len: allocate_size_int, cap: allocate_size_int}
+
+		// bind the buffer
+		retcode = C.SQLBindCol(h.hstmt, C.SQLUSMALLINT(i_col), C.SQLSMALLINT(C.SQL_C_CHAR),
+			C.SQLPOINTER(&buff[0]), allocate_size, &h.indicators[i])
+		check_error(retcode, 'SQLBindCol()', C.SQLHANDLE(h.hstmt), C.SQLSMALLINT(C.SQL_HANDLE_STMT)) ?
+
+		// record the buffer in HStmt
+		h.buffers[i] = buff
+	}
+}
+
+// fetch all rows
+fn (h HStmt) read_rows() ?[][]string {
+	mut retcode := C.SQLRETURN(C.SQL_SUCCESS)
+
+	mut res := [][]string{}
+
+	if h.column_count <= 0 {
+		// there is nothing in the driver to read from
+		return res
+	}
+
+	// Fetch and print each row of data until SQL_NO_DATA returned.
+	for {
+		mut row := []string{}
+		retcode = C.SQLFetch(h.hstmt)
+		if retcode == C.SQLRETURN(C.SQL_SUCCESS) || retcode == C.SQLRETURN(C.SQL_SUCCESS_WITH_INFO) {
+			// copy buffered result to res
+			for content in h.buffers {
+				row << string(content)
+			}
+		} else {
+			if retcode != C.SQLRETURN(C.SQL_NO_DATA) {
+				check_error(retcode, 'SQLFetch()', C.SQLHANDLE(h.hstmt), C.SQLSMALLINT(C.SQL_HANDLE_STMT)) ?
+			} else {
+				break
+			}
+		}
+		res << row
+	}
+	return res
+}
-- 
cgit v1.2.3