add db_odbc library module

1 files changed, 480 insertions, 0 deletions

diff --git a/lib/impure/db_odbc.nim b/lib/impure/db_odbc.nim
new file mode 100644
index 000000000..d7038303c
--- /dev/null
+++ b/lib/impure/db_odbc.nim
@@ -0,0 +1,480 @@
+#
+#    See the file "copying.txt", included in this
+#    distribution, for details about the copyright.
+#
+#
+## A higher level `ODBC` database wrapper.
+##
+## This is the same interface that is implemented for other databases.
+##
+## This has NOT yet been (extensively) tested agains ODBC drivers for
+## Teradata, Oracle, Sybase, MSSqlvSvr, et. al.  databases
+##
+## Currently all queries are ANSI calls, not Unicode.
+##
+## Example:
+##
+## .. code-block:: Nim
+##
+##  import db_odbc, math
+##
+##  let theDb = open("localhost", "nim", "nim", "test")
+##
+##  theDb.exec(sql"Drop table if exists myTestTbl")
+##  theDb.exec(sql("create table myTestTbl (" &
+##      " Id    INT(11)     NOT NULL AUTO_INCREMENT PRIMARY KEY, " &
+##      " Name  VARCHAR(50) NOT NULL, " &
+##      " i     INT(11), " &
+##      " f     DECIMAL(18,10))"))
+##
+##  theDb.exec(sql"START TRANSACTION")
+##  for i in 1..1000:
+##    theDb.exec(sql"INSERT INTO myTestTbl (name,i,f) VALUES (?,?,?)",
+##          "Item#" & $i, i, sqrt(i.float))
+##  theDb.exec(sql"COMMIT")
+##
+##  for x in theDb.fastRows(sql"select * from myTestTbl"):
+##    echo x
+##
+##  let id = theDb.tryInsertId(sql"INSERT INTO myTestTbl (name,i,f) VALUES (?,?,?)",
+##          "Item#1001", 1001, sqrt(1001.0))
+##  echo "Inserted item: ", theDb.getValue(sql"SELECT name FROM myTestTbl WHERE id=?", id)
+##
+##  theDb.close()
+
+
+import strutils, odbcsql
+
+type
+  OdbcConnTyp = tuple[hDb: SqlHDBC, env: SqlHEnv, stmt: SqlHStmt]
+  DbConn* = OdbcConnTyp    ## encapsulates a database connection
+  Row* = seq[string]   ## a row of a dataset. NULL database values will be
+                       ## transformed always to the empty string.
+  InstantRow* = tuple[row: seq[string], len: int]  ## a handle that can be
+                                                    ## used to get a row's
+                                                    ## column text on demand
+  EDb* = object of IOError ## exception that is raised if a database error occurs
+
+  SqlQuery* = distinct string ## an SQL query string
+
+  FDb* = object of IOEffect ## effect that denotes a database operation
+  FReadDb* = object of FDb   ## effect that denotes a read operation
+  FWriteDb* = object of FDb  ## effect that denotes a write operation
+  FReadODBC* = object of FDb   ## effect that denotes a read of ODBC driver
+  FWriteODBC* = object of FDb  ## effect that denotes a write of ODBC driver
+{.deprecated: [TRow: Row, TSqlQuery: SqlQuery, TDbConn: DbConn].}
+
+var
+  buf: array[0..4096, char]
+
+proc properFreeResult(hType: int, sqlres: var SqlHandle) {.
+          tags: [FWriteODBC], raises: [].} =
+  try:
+    discard SQLFreeHandle(hType.TSqlSmallInt, sqlres)
+    sqlres = nil
+  except: discard
+
+proc sql*(query: string): SqlQuery {.noSideEffect, inline.} =
+  ## Constructs a SqlQuery from the string `query`. This is supposed to be
+  ## used as a raw-string-literal modifier:
+  ## ``sql"update user set counter = counter + 1"``
+  ##
+  ## If assertions are turned off, it does nothing. If assertions are turned
+  ## on, later versions will check the string for valid syntax.
+  result = SqlQuery(query)
+
+proc getErrInfo(db: var DbConn): tuple[res: int, ss, ne, msg: string] {.
+          tags: [FReadODBC], raises: [].} =
+  ## Returns ODBC error information
+  var
+    sqlState: array[0..512, char]
+    nativeErr: array[0..512, char]
+    errMsg: array[0..512, char]
+    retSz: TSqlSmallInt = 0
+    res: TSqlSmallInt = 0
+  try:
+    sqlState[0] = '\0'
+    nativeErr[0] = '\0'
+    errMsg[0] = '\0'
+    res = SQLErr(db.env, db.hDb, db.stmt,
+              cast[PSQLCHAR](sqlState.addr),
+              cast[PSQLCHAR](nativeErr.addr),
+              cast[PSQLCHAR](errMsg.addr),
+              511.TSqlSmallInt, retSz.addr.PSQLSMALLINT)
+  except:
+    discard
+  return (res.int, $sqlState, $nativeErr, $errMsg)
+
+proc dbError*(db: var DbConn) {.
+          tags: [FReadODBC,FWriteODBC], raises: [EDb] .} =
+  ## Raises an `[EDb]` exception with ODBC error information
+  var
+    e: ref EDb
+    ss, ne, msg: string = ""
+    isAnError = false
+    res: int = 0
+    prevSs = ""
+  while true:
+    prevSs = ss
+    (res, ss, ne, msg) = db.getErrInfo()
+    if prevSs == ss:
+      break
+    # sqlState of 00000 is not an error
+    elif ss == "00000":
+      break
+    elif ss == "01000":
+      echo "\nWarning: ", ss, " ", msg
+      continue
+    else:
+      isAnError = true
+      echo "\nError: ", ss, " ", msg
+  if isAnError:
+    new(e)
+    e.msg = "ODBC Error"
+    if db.stmt != nil:
+      properFreeResult(SQL_HANDLE_STMT, db.stmt)
+    properFreeResult(SQL_HANDLE_DBC, db.hDb)
+    properFreeResult(SQL_HANDLE_ENV, db.env)
+    raise e
+
+proc dbError*(msg: string) {.noreturn.} =
+  ## Raises an EDb exception with message `msg`.
+  var e: ref EDb
+  new(e)
+  e.msg = msg
+  raise e
+
+proc SqlCheck(db: var DbConn, resVal: TSqlSmallInt) {.raises: [EDb]} =
+  ## Wrapper that checks if ``resVal`` is not SQL_SUCCESS and if so, raises [EDb]
+  if resVal != SQL_SUCCESS: dbError(db)
+
+proc SqlGetDBMS(db: var DbConn): string {.
+        tags: [FReadODBC, FWriteODBC], raises: [] .} =
+  ## Returns the ODBC SQL_DBMS_NAME string
+  const
+    SQL_DBMS_NAME = 17.SqlUSmallInt
+  var
+    sz: TSqlSmallInt = 0
+  buf[0] = '\0'
+  try:
+    db.SqlCheck(SQLGetInfo(db.hDb, SQL_DBMS_NAME, cast[SqlPointer](buf.addr),
+                        4095.TSqlSmallInt, sz.addr))
+  except: discard
+  return $buf.cstring
+
+proc dbQuote*(s: string): string {.noSideEffect.} =
+  ## DB quotes the string.
+  result = "'"
+  for c in items(s):
+    if c == '\'': add(result, "''")
+    else: add(result, c)
+  add(result, '\'')
+
+proc dbFormat(formatstr: SqlQuery, args: varargs[string]): string {.
+                  noSideEffect.} =
+  ## Replace any ``?`` placeholders with `args`,
+  ## and quotes the arguments
+  result = ""
+  var a = 0
+  for c in items(string(formatstr)):
+    if c == '?':
+      if args[a] == nil:
+        add(result, "NULL")
+      else:
+        add(result, dbQuote(args[a]))
+      inc(a)
+    else:
+      add(result, c)
+
+proc prepareFetch(db: var DbConn, query: SqlQuery,
+                args: varargs[string, `$`]) {.
+                tags: [FReadDb, FReadODBC, FWriteODBC], raises: [EDb].} =
+  # Prepare a statement, execute it and fetch the data to the driver
+  # ready for retrieval of the data
+  # Used internally by iterators and retrieval procs
+  # requires calling
+  #      properFreeResult(SQL_HANDLE_STMT, db.stmt)
+  # when finished
+  db.SqlCheck(SQLAllocHandle(SQL_HANDLE_STMT, db.hDb, db.stmt))
+  var q = dbFormat(query, args)
+  db.SqlCheck(SQLPrepare(db.stmt, q.PSQLCHAR, q.len.TSqlSmallInt))
+  db.SqlCheck(SQLExecute(db.stmt))
+  db.SqlCheck(SQLFetch(db.stmt))
+
+proc prepareFetchDirect(db: var DbConn, query: SqlQuery,
+                args: varargs[string, `$`]) {.
+                tags: [FReadDb, FWriteDb, FReadODBC, FWriteODBC], raises: [EDb].} =
+  # Prepare a statement, execute it and fetch the data to the driver
+  # ready for retrieval of the data
+  # Used internally by iterators and retrieval procs
+  # requires calling
+  #      properFreeResult(SQL_HANDLE_STMT, db.stmt)
+  # when finished
+  db.SqlCheck(SQLAllocHandle(SQL_HANDLE_STMT, db.hDb, db.stmt))
+  var q = dbFormat(query, args)
+  db.SqlCheck(SQLExecDirect(db.stmt, q.PSQLCHAR, q.len.TSqlSmallInt))
+  db.SqlCheck(SQLFetch(db.stmt))
+
+proc tryExec*(db: var DbConn, query: SqlQuery, args: varargs[string, `$`]): bool {.
+  tags: [FReadDB, FWriteDb, FReadODBC, FWriteODBC], raises: [].} =
+  ## Tries to execute the query and returns true if successful, false otherwise.
+  var
+    res:TSqlSmallInt = -1
+  try:
+    db.prepareFetchDirect(query, args)
+    var
+      rCnt = -1
+    res = SQLRowCount(db.stmt, rCnt)
+    if res != SQL_SUCCESS: dbError(db)
+    properFreeResult(SQL_HANDLE_STMT, db.stmt)
+  except: discard
+  return res == SQL_SUCCESS
+
+proc rawExec(db: var DbConn, query: SqlQuery, args: varargs[string, `$`]) {.
+            tags: [FReadDb, FWriteDb, FReadODBC, FWriteODBC], raises: [EDb].} =
+  db.prepareFetchDirect(query, args)
+
+proc exec*(db: var DbConn, query: SqlQuery, args: varargs[string, `$`]) {.
+            tags: [FReadDb, FWriteDb, FReadODBC, FWriteODBC], raises: [EDb].} =
+  ## Executes the query and raises EDB if not successful.
+  db.prepareFetchDirect(query, args)
+  properFreeResult(SQL_HANDLE_STMT, db.stmt)
+
+proc newRow(L: int): Row {.noSideEFfect.} =
+  newSeq(result, L)
+  for i in 0..L-1: result[i] = ""
+
+iterator fastRows*(db: var DbConn, query: SqlQuery,
+                   args: varargs[string, `$`]): Row {.
+                tags: [FReadDB, FReadODBC, FWriteODBC], raises: [EDb].} =
+  ## Executes the query and iterates over the result dataset.
+  ##
+  ## This is very fast, but potentially dangerous.  Use this iterator only
+  ## if you require **ALL** the rows.
+  ##
+  ## Breaking the fastRows() iterator during a loop may cause a driver error
+  ## for subsequenct queries
+  ##
+  ## Rows are retrieved from the server at each iteration.
+  var
+    rowRes: Row
+    sz: TSqlSmallInt = 0
+    cCnt: TSqlSmallInt = 0.TSqlSmallInt
+    rCnt = -1
+
+  db.prepareFetch(query, args)
+  db.SqlCheck(SQLNumResultCols(db.stmt, cCnt))
+  db.SqlCheck(SQLRowCount(db.stmt, rCnt))
+  rowRes = newRow(cCnt)
+  for rNr in 1..rCnt:
+    for colId in 1..cCnt:
+      buf[0] = '\0'
+      db.SqlCheck(SQLGetData(db.stmt, colId.SqlUSmallInt, SQL_C_CHAR,
+                               cast[cstring](buf.addr), 4095.TSqlSmallInt, sz.addr))
+      rowRes[colId-1] = $buf.cstring
+    db.SqlCheck(SQLFetchScroll(db.stmt, SQL_FETCH_NEXT, 1))
+    yield rowRes
+  properFreeResult(SQL_HANDLE_STMT, db.stmt)
+
+iterator instantRows*(db: var DbConn, query: SqlQuery,
+                      args: varargs[string, `$`]): InstantRow
+                {.tags: [FReadDb, FReadODBC, FWriteODBC].} =
+  ## Same as fastRows but returns a handle that can be used to get column text
+  ## on demand using []. Returned handle is valid only within the interator body.
+  var
+    rowRes: Row
+    sz: TSqlSmallInt = 0
+    cCnt: TSqlSmallInt = 0.TSqlSmallInt
+    rCnt = -1
+  db.prepareFetch(query, args)
+  db.SqlCheck(SQLNumResultCols(db.stmt, cCnt))
+  db.SqlCheck(SQLRowCount(db.stmt, rCnt))
+  rowRes = newRow(cCnt)
+  for rNr in 1..rCnt:
+    for colId in 1..cCnt:
+      buf[0] = '\0'
+      db.SqlCheck(SQLGetData(db.stmt, colId.SqlUSmallInt, SQL_C_CHAR,
+                               cast[cstring](buf.addr), 4095.TSqlSmallInt, sz.addr))
+      rowRes[colId-1] = $buf.cstring
+    db.SqlCheck(SQLFetchScroll(db.stmt, SQL_FETCH_NEXT, 1))
+    yield (row: rowRes, len: cCnt.int)
+  properFreeResult(SQL_HANDLE_STMT, db.stmt)
+
+proc `[]`*(row: InstantRow, col: int): string {.inline.} =
+  ## Returns text for given column of the row
+  row.row[col]
+
+proc len*(row: InstantRow): int {.inline.} =
+  ## Returns number of columns in the row
+  row.len
+
+proc getRow*(db: var DbConn, query: SqlQuery,
+             args: varargs[string, `$`]): Row {.
+          tags: [FReadDB, FReadODBC, FWriteODBC], raises: [EDb].} =
+  ## Retrieves a single row. If the query doesn't return any rows, this proc
+  ## will return a Row with empty strings for each column.
+  var
+    sz: TSqlSmallInt = 0.TSqlSmallInt
+    cCnt: TSqlSmallInt = 0.TSqlSmallInt
+    rCnt = -1
+  result = @[]
+  db.prepareFetch(query, args)
+  db.SqlCheck(SQLNumResultCols(db.stmt, cCnt))
+
+  db.SqlCheck(SQLRowCount(db.stmt, rCnt))
+  for colId in 1..cCnt:
+    db.SqlCheck(SQLGetData(db.stmt, colId.SqlUSmallInt, SQL_C_CHAR,
+                             cast[cstring](buf.addr), 4095.TSqlSmallInt, sz.addr))
+    result.add($buf.cstring)
+  db.SqlCheck(SQLFetchScroll(db.stmt, SQL_FETCH_NEXT, 1))
+  properFreeResult(SQL_HANDLE_STMT, db.stmt)
+
+proc getAllRows*(db: var DbConn, query: SqlQuery,
+                 args: varargs[string, `$`]): seq[Row] {.
+           tags: [FReadDB, FReadODBC, FWriteODBC], raises: [EDb].} =
+  ## Executes the query and returns the whole result dataset.
+  var
+    rowRes: Row
+    sz: TSqlSmallInt = 0
+    cCnt: TSqlSmallInt = 0.TSqlSmallInt
+    rCnt = -1
+  db.prepareFetch(query, args)
+  db.SqlCheck(SQLNumResultCols(db.stmt, cCnt))
+  db.SqlCheck(SQLRowCount(db.stmt, rCnt))
+  result = @[]
+  for rNr in 1..rCnt:
+    rowRes = @[]
+    buf[0] = '\0'
+    for colId in 1..cCnt:
+      db.SqlCheck(SQLGetData(db.stmt, colId.SqlUSmallInt, SQL_C_CHAR,
+                               cast[SqlPointer](buf.addr), 4095.TSqlSmallInt, sz.addr))
+      rowRes.add($buf.cstring)
+    db.SqlCheck(SQLFetchScroll(db.stmt, SQL_FETCH_NEXT, 1))
+    result.add(rowRes)
+  properFreeResult(SQL_HANDLE_STMT, db.stmt)
+
+iterator rows*(db: var DbConn, query: SqlQuery,
+               args: varargs[string, `$`]): Row {.
+         tags: [FReadDB, FReadODBC, FWriteODBC], raises: [EDb].} =
+  ## Same as `fastRows`, but slower and safe.
+  ##
+  ## This retrieves ALL rows into memory before
+  ## iterating through the rows.
+  ## Large dataset queries will impact on memory usage.
+  for r in items(getAllRows(db, query, args)): yield r
+
+proc getValue*(db: var DbConn, query: SqlQuery,
+               args: varargs[string, `$`]): string {.
+           tags: [FReadDB, FReadODBC, FWriteODBC], raises: [].} =
+  ## Executes the query and returns the first column of the first row of the
+  ## result dataset. Returns "" if the dataset contains no rows or the database
+  ## value is NULL.
+  result = ""
+  try:
+    result = getRow(db, query, args)[0]
+  except: discard
+
+proc tryInsertId*(db: var DbConn, query: SqlQuery,
+                  args: varargs[string, `$`]): int64 {.
+            tags: [FReadDb, FWriteDb, FReadODBC, FWriteODBC], raises: [].} =
+  ## Executes the query (typically "INSERT") and returns the
+  ## generated ID for the row or -1 in case of an error.
+  if not tryExec(db, query, args):
+    result = -1'i64
+  else:
+    echo "DBMS: ",SqlGetDBMS(db).toLower()
+    result = -1'i64
+    try:
+      case SqlGetDBMS(db).toLower():
+      of "postgresql":
+        result = getValue(db, sql"SELECT LASTVAL();", []).parseInt
+      of "mysql":
+        result = getValue(db, sql"SELECT LAST_INSERT_ID();", []).parseInt
+      of "sqlite":
+        result = getValue(db, sql"SELECT LAST_INSERT_ROWID();", []).parseInt
+      of "microsoft sql server":
+        result = getValue(db, sql"SELECT SCOPE_IDENTITY();", []).parseInt
+      of "oracle":
+        result = getValue(db, sql"SELECT id.currval FROM DUAL;", []).parseInt
+      else: result = -1'i64
+    except: discard
+
+proc insertId*(db: var DbConn, query: SqlQuery,
+               args: varargs[string, `$`]): int64 {.
+         tags: [FReadDb, FWriteDb, FReadODBC, FWriteODBC], raises: [EDb].} =
+  ## Executes the query (typically "INSERT") and returns the
+  ## generated ID for the row.
+  result = tryInsertID(db, query, args)
+  if result < 0: dbError(db)
+
+proc execAffectedRows*(db: var DbConn, query: SqlQuery,
+                       args: varargs[string, `$`]): int64 {.
+             tags: [FReadDB, FWriteDb, FReadODBC, FWriteODBC], raises: [EDb].} =
+  ## Runs the query (typically "UPDATE") and returns the
+  ## number of affected rows
+  result = -1
+  var res = SQLAllocHandle(SQL_HANDLE_STMT, db.hDb, db.stmt.SqlHandle)
+  if res != SQL_SUCCESS: dbError(db)
+  var q = dbFormat(query, args)
+  res = SQLPrepare(db.stmt, q.PSQLCHAR, q.len.TSqlSmallInt)
+  if res != SQL_SUCCESS: dbError(db)
+  rawExec(db, query, args)
+  var rCnt = -1
+  result = SQLRowCount(db.hDb, rCnt)
+  if res != SQL_SUCCESS: dbError(db)
+  properFreeResult(SQL_HANDLE_STMT, db.stmt)
+  result = rCnt
+
+proc close*(db: var DbConn) {.
+      tags: [FWriteODBC], raises: [].} =
+  ## Closes the database connection.
+  if db.hDb != nil:
+    try:
+      var res = SQLDisconnect(db.hDb)
+      if db.stmt != nil:
+        res = SQLFreeHandle(SQL_HANDLE_STMT, db.stmt)
+      res = SQLFreeHandle(SQL_HANDLE_DBC, db.hDb)
+      res = SQLFreeHandle(SQL_HANDLE_ENV, db.env)
+      db = (hDb: nil, env: nil, stmt: nil)
+    except:
+      discard
+
+proc open*(connection, user, password, database: string): DbConn {.
+  tags: [FReadODBC, FWriteODBC], raises: [EDb].} =
+  ## Opens a database connection.
+  ##
+  ## Raises `EDb` if the connection could not be established.
+  ##
+  ## Currently the database parameter is ignored,
+  ## but included to match ``open()`` in the other db_xxxxx library modules.
+  var
+    val: TSqlInteger = SQL_OV_ODBC3
+    resLen = 0
+  result = (hDb: nil, env: nil, stmt: nil)
+  # allocate environment handle
+  var res = SQLAllocHandle(SQL_HANDLE_ENV, result.env, result.env)
+  if res != SQL_SUCCESS: dbError("Error: unable to initialise ODBC environment.")
+  res = SQLSetEnvAttr(result.env,
+                      SQL_ATTR_ODBC_VERSION.TSqlInteger,
+                      val, resLen.TSqlInteger)
+  if res != SQL_SUCCESS: dbError("Error: unable to set ODBC driver version.")
+  # allocate hDb handle
+  res = SQLAllocHandle(SQL_HANDLE_DBC, result.env, result.hDb)
+  if res != SQL_SUCCESS: dbError("Error: unable to allocate connection handle.")
+
+  # Connect: connection = dsn str,
+  res = SQLConnect(result.hDb,
+                  connection.PSQLCHAR , connection.len.TSqlSmallInt,
+                  user.PSQLCHAR, user.len.TSqlSmallInt,
+                  password.PSQLCHAR, password.len.TSqlSmallInt)
+  if res != SQL_SUCCESS:
+    result.dbError()
+
+proc setEncoding*(connection: DbConn, encoding: string): bool {.
+  tags: [FReadODBC, FWriteODBC], raises: [EDb].} =
+  ## Currently not implemented for ODBC.
+  ##
+  ## Sets the encoding of a database connection, returns true for
+  ## success, false for failure.
+  #result = set_character_set(connection, encoding) == 0
+  dbError("setEncoding() is currently not implemented by the db_odbc module")