diff options
Diffstat (limited to 'v_windows/v/old/vlib/mssql')
| -rw-r--r-- | v_windows/v/old/vlib/mssql/README.md | 69 | ||||
| -rw-r--r-- | v_windows/v/old/vlib/mssql/_cdef_nix.c.v | 6 | ||||
| -rw-r--r-- | v_windows/v/old/vlib/mssql/_cdef_windows.c.v | 12 | ||||
| -rw-r--r-- | v_windows/v/old/vlib/mssql/_cdefs.c.v | 27 | ||||
| -rw-r--r-- | v_windows/v/old/vlib/mssql/config.v | 20 | ||||
| -rw-r--r-- | v_windows/v/old/vlib/mssql/mssql.v | 125 | ||||
| -rw-r--r-- | v_windows/v/old/vlib/mssql/result.v | 13 | ||||
| -rw-r--r-- | v_windows/v/old/vlib/mssql/stmt_handle.v | 127 |
8 files changed, 399 insertions, 0 deletions
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 +} |