summary refs log tree commit diff stats
path: root/compiler/semstmts.nim
Commit message (Expand)AuthorAgeFilesLines
...
* | better typeToString; fixes #340Araq2013-02-201-0/+3
* | system.fields|fieldPairs for objectsAraq2013-02-181-32/+97
|/
* merged upstream masterZahary Karadjov2013-01-271-46/+71
|\
| * bootstrapping works againAraq2013-01-221-1/+1
| * fixed some closure related bugsAraq2013-01-221-17/+21
| * implemented generic multi methodsAraq2013-01-161-14/+15
| * fixes #293Araq2013-01-081-4/+21
| * implements #295Araq2013-01-081-4/+4
| * don't invoke destructors for .global. variablesAraq2012-12-161-1/+2
| * some fixes for generic first class iteratorsAraq2012-12-151-5/+7
| * fixes #269Araq2012-12-051-0/+2
| * fixes #266Araq2012-12-051-2/+0
| * implemented generic convertersAraq2012-12-051-2/+2
* | temporary debugging code for the memory leak investigationZahary Karadjov2012-11-281-1/+1
* | CaaS in-memory cachingZahary Karadjov2012-11-281-6/+5
|/
* 'return' for first class iteratorsAraq2012-11-261-1/+2
* next steps for first class iteratorsAraq2012-11-261-1/+2
* almost every pragma is allowed in a 'push' pragmaAraq2012-11-201-0/+2
* doc2 improvements; small lexer bugfix: backslashes in commentsAraq2012-11-201-0/+4
* fixes #250Araq2012-11-191-1/+3
* added system.finished for first class iteratorsAraq2012-11-171-1/+1
* next steps for first class iteratorsAraq2012-11-151-1/+4
* bugfix: stack traces; first class iterators almost workingAraq2012-11-151-4/+9
* nimbuild should work againAraq2012-11-011-0/+1
* fixes #226Araq2012-10-191-2/+2
* fixes #217Araq2012-10-131-1/+1
* bugfix: tests should be green againAraq2012-10-131-1/+10
* bugfix: fixed broken expr proc bodiesAraq2012-10-121-2/+6
* syntax compatibility between do blocks and stmt blocksZahary Karadjov2012-10-041-14/+30
* fixes #73Araq2012-09-231-1/+1
* bugfix: 'result' cannot be captured in a closureAraq2012-09-231-2/+3
* proc bodies can be expressions with a typeAraq2012-09-221-1/+1
* semExpr/semStmt mergedAraq2012-09-131-157/+45
* small bugfixes; reactivated tests/compileAraq2012-09-111-0/+2
* first steps to deprecate 'nil' statementAraq2012-09-091-4/+9
* term rewriting improvementsAraq2012-09-081-4/+4
* term rewriting macros fully implemented; still buggyAraq2012-09-031-0/+2
* next steps towards term rewriting macros; simple examples workAraq2012-08-301-11/+5
* first steps towards term rewriting macrosAraq2012-08-301-2/+13
* parameter passing works the same for macros and templates; use callsite magic...Araq2012-08-281-3/+0
* implemented generic templatesAraq2012-08-271-6/+5
* bindSym suffices; no 'bind' for macros anymoreAraq2012-08-251-18/+0
* implemented 'bind' for macrosAraq2012-08-241-0/+18
* next steps to hygienic templatesAraq2012-08-201-10/+6
* first steps to make templates hygienicAraq2012-08-201-12/+14
* next steps for tyVarargs/tyOpenArray splitAraq2012-08-141-1/+1
* made tests green againAraq2012-08-141-1/+3
* top level closures should work; transf is not a pass anymore; next steps for ...Araq2012-08-131-27/+36
* idetools: 'usages' and 'def' should work now; documented js backendAraq2012-08-051-3/+6
* idetools improvements; preparation of first class iterators; fixes #183Araq2012-08-021-9/+12
ding */ .highlight .gi { color: #000000; background-color: #ddffdd } /* Generic.Inserted */ .highlight .go { color: #888888 } /* Generic.Output */ .highlight .gp { color: #555555 } /* Generic.Prompt */ .highlight .gs { font-weight: bold } /* Generic.Strong */ .highlight .gu { color: #666666 } /* Generic.Subheading */ .highlight .gt { color: #aa0000 } /* Generic.Traceback */ .highlight .kc { color: #008800; font-weight: bold } /* Keyword.Constant */ .highlight .kd { color: #008800; font-weight: bold } /* Keyword.Declaration */ .highlight .kn { color: #008800; font-weight: bold } /* Keyword.Namespace */ .highlight .kp { color: #008800 } /* Keyword.Pseudo */ .highlight .kr { color: #008800; font-weight: bold } /* Keyword.Reserved */ .highlight .kt { color: #888888; font-weight: bold } /* Keyword.Type */ .highlight .m { color: #0000DD; font-weight: bold } /* Literal.Number */ .highlight .s { color: #dd2200; background-color: #fff0f0 } /* Literal.String */ .highlight .na { color: #336699 } /* Name.Attribute */ .highlight .nb { color: #003388 } /* Name.Builtin */ .highlight .nc { color: #bb0066; font-weight: bold } /* Name.Class */ .highlight .no { color: #003366; font-weight: bold } /* Name.Constant */ .highlight .nd { color: #555555 } /* Name.Decorator */ .highlight .ne { color: #bb0066; font-weight: bold } /* Name.Exception */ .highlight .nf { color: #0066bb; font-weight: bold } /* Name.Function */ .highlight .nl { color: #336699; font-style: italic } /* Name.Label */ .highlight .nn { color: #bb0066; font-weight: bold } /* Name.Namespace */ .highlight .py { color: #336699; font-weight: bold } /* Name.Property */ .highlight .nt { color: #bb0066; font-weight: bold } /* Name.Tag */ .highlight .nv { color: #336699 } /* Name.Variable */ .highlight .ow { color: #008800 } /* Operator.Word */ .highlight .w { color: #bbbbbb } /* Text.Whitespace */ .highlight .mb { color: #0000DD; font-weight: bold } /* Literal.Number.Bin */ .highlight .mf { color: #0000DD; font-weight: bold } /* Literal.Number.Float */ .highlight .mh { color: #0000DD; font-weight: bold } /* Literal.Number.Hex */ .highlight .mi { color: #0000DD; font-weight: bold } /* Literal.Number.Integer */ .highlight .mo { color: #0000DD; font-weight: bold } /* Literal.Number.Oct */ .highlight .sa { color: #dd2200; background-color: #fff0f0 } /* Literal.String.Affix */ .highlight .sb { color: #dd2200; background-color: #fff0f0 } /* Literal.String.Backtick */ .highlight .sc { color: #dd2200; background-color: #fff0f0 } /* Literal.String.Char */ .highlight .dl { color: #dd2200; background-color: #fff0f0 } /* Literal.String.Delimiter */ .highlight .sd { color: #dd2200; background-color: #fff0f0 } /* Literal.String.Doc */ .highlight .s2 { color: #dd2200; background-color: #fff0f0 } /* Literal.String.Double */ .highlight .se { color: #0044dd; background-color: #fff0f0 } /* Literal.String.Escape */ .highlight .sh { color: #dd2200; background-color: #fff0f0 } /* Literal.String.Heredoc */ .highlight .si { color: #3333bb; background-color: #fff0f0 } /* Literal.String.Interpol */ .highlight .sx { color: #22bb22; background-color: #f0fff0 } /* Literal.String.Other */ .highlight .sr { color: #008800; background-color: #fff0ff } /* Literal.String.Regex */ .highlight .s1 { color: #dd2200; background-color: #fff0f0 } /* Literal.String.Single */ .highlight .ss { color: #aa6600; background-color: #fff0f0 } /* Literal.String.Symbol */ .highlight .bp { color: #003388 } /* Name.Builtin.Pseudo */ .highlight .fm { color: #0066bb; font-weight: bold } /* Name.Function.Magic */ .highlight .vc { color: #336699 } /* Name.Variable.Class */ .highlight .vg { color: #dd7700 } /* Name.Variable.Global */ .highlight .vi { color: #3333bb } /* Name.Variable.Instance */ .highlight .vm { color: #336699 } /* Name.Variable.Magic */ .highlight .il { color: #0000DD; font-weight: bold } /* Literal.Number.Integer.Long */
#
#
#            Nim's Runtime Library
#        (c) Copyright 2015 Nim Contributors
#
#    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 against ODBC drivers for
## Teradata, Oracle, Sybase, MSSqlvSvr, et. al.  databases.
##
## Currently all queries are ANSI calls, not Unicode.
##
## See also: `db_postgres <db_postgres.html>`_, `db_sqlite <db_sqlite.html>`_,
## `db_mysql <db_mysql.html>`_.
##
## Parameter substitution
## ----------------------
##
## All ``db_*`` modules support the same form of parameter substitution.
## That is, using the ``?`` (question mark) to signify the place where a
## value should be placed. For example:
##
## .. code-block:: Nim
##     sql"INSERT INTO myTable (colA, colB, colC) VALUES (?, ?, ?)"
##
##
## Examples
## --------
##
## Opening a connection to a database
## ==================================
##
## .. code-block:: Nim
##     import db_odbc
##     var db = open("localhost", "user", "password", "dbname")
##     db.close()
##
## Creating a table
## ================
##
## .. code-block:: Nim
##      db.exec(sql"DROP TABLE IF EXISTS myTable")
##      db.exec(sql("""CREATE TABLE myTable (
##                       id integer,
##                       name varchar(50) not null)"""))
##
## Inserting data
## ==============
##
## .. code-block:: Nim
##     db.exec(sql"INSERT INTO myTable (id, name) VALUES (0, ?)",
##             "Andreas")
##
## Large example
## =============
##
## .. code-block:: Nim
##
##  import db_odbc, math
##
##  var 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
import db_common
export db_common

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
                       ## converted to nil.
  InstantRow* = tuple[row: seq[string], len: int]  ## a handle that can be
                                                    ## used to get a row's
                                                    ## column text on demand

{.deprecated: [TRow: Row, TSqlQuery: SqlQuery, TDbConn: DbConn].}

var
  buf: array[0..4096, char]

proc properFreeResult(hType: int, sqlres: var SqlHandle) {.
          tags: [WriteDbEffect], raises: [].} =
  try:
    discard SQLFreeHandle(hType.TSqlSmallInt, sqlres)
    sqlres = nil
  except: discard

proc getErrInfo(db: var DbConn): tuple[res: int, ss, ne, msg: string] {.
          tags: [ReadDbEffect], 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: [ReadDbEffect, WriteDbEffect], raises: [DbError] .} =
  ## Raises an `[DbError]` exception with ODBC error information
  var
    e: ref DbError
    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 sqlCheck(db: var DbConn, resVal: TSqlSmallInt) {.raises: [DbError]} =
  ## Wrapper that raises [EDb] if ``resVal`` is neither SQL_SUCCESS or SQL_NO_DATA
  if resVal notIn [SQL_SUCCESS, SQL_NO_DATA]: dbError(db)

proc sqlGetDBMS(db: var DbConn): string {.
        tags: [ReadDbEffect, WriteDbEffect], 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, `$`]): TSqlSmallInt {.
                tags: [ReadDbEffect, WriteDbEffect], raises: [DbError].} =
  # 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))
  result = SQLFetch(db.stmt)
  db.sqlCheck(result)

proc prepareFetchDirect(db: var DbConn, query: SqlQuery,
                args: varargs[string, `$`]) {.
                tags: [ReadDbEffect, WriteDbEffect], raises: [DbError].} =
  # 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: [ReadDbEffect, WriteDbEffect], 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)
    properFreeResult(SQL_HANDLE_STMT, db.stmt)
    if res != SQL_SUCCESS: dbError(db)
  except: discard
  return res == SQL_SUCCESS

proc rawExec(db: var DbConn, query: SqlQuery, args: varargs[string, `$`]) {.
            tags: [ReadDbEffect, WriteDbEffect], raises: [DbError].} =
  db.prepareFetchDirect(query, args)

proc exec*(db: var DbConn, query: SqlQuery, args: varargs[string, `$`]) {.
            tags: [ReadDbEffect, WriteDbEffect], raises: [DbError].} =
  ## 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: [ReadDbEffect, WriteDbEffect], raises: [DbError].} =
  ## 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
    res: TSqlSmallInt = 0.TSqlSmallInt
    tempcCnt: TSqlSmallInt # temporary cCnt,Fix the field values to be null when the release schema is compiled.
    # tempcCnt,A field to store the number of temporary variables, for unknown reasons,
    # after performing a sqlgetdata function and circulating variables cCnt value will be changed to 0,
    # so the values of the temporary variable to store the cCnt.
    # After every cycle and specified to cCnt. To ensure the traversal of all fields.
  res = db.prepareFetch(query, args)
  if res == SQL_NO_DATA:
    discard
  elif res == SQL_SUCCESS:
    res = SQLNumResultCols(db.stmt, cCnt)
    rowRes = newRow(cCnt)
    rowRes.setLen(max(cCnt,0))
    tempcCnt = cCnt
    while res == SQL_SUCCESS:
      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
        cCnt = tempcCnt
      yield rowRes
      res = SQLFetch(db.stmt)
  properFreeResult(SQL_HANDLE_STMT, db.stmt)
  db.sqlCheck(res)

iterator instantRows*(db: var DbConn, query: SqlQuery,
                      args: varargs[string, `$`]): InstantRow
                {.tags: [ReadDbEffect, WriteDbEffect].} =
  ## 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
    res: TSqlSmallInt = 0.TSqlSmallInt
    tempcCnt: TSqlSmallInt # temporary cCnt,Fix the field values to be null when the release schema is compiled.
    # tempcCnt,A field to store the number of temporary variables, for unknown reasons,
    # after performing a sqlgetdata function and circulating variables cCnt value will be changed to 0,
    # so the values of the temporary variable to store the cCnt.
    # After every cycle and specified to cCnt. To ensure the traversal of all fields.
  res = db.prepareFetch(query, args)
  if res == SQL_NO_DATA:
    discard
  elif res == SQL_SUCCESS:
    res = SQLNumResultCols(db.stmt, cCnt)
    rowRes = newRow(cCnt)
    rowRes.setLen(max(cCnt,0))
    tempcCnt = cCnt
    while res == SQL_SUCCESS:
      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
        cCnt = tempcCnt
      yield (row: rowRes, len: cCnt.int)
      res = SQLFetch(db.stmt)
  properFreeResult(SQL_HANDLE_STMT, db.stmt)
  db.sqlCheck(res)

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: [ReadDbEffect, WriteDbEffect], raises: [DbError].} =
  ## 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
    rowRes: Row
    sz: TSqlSmallInt = 0.TSqlSmallInt
    cCnt: TSqlSmallInt = 0.TSqlSmallInt
    res: TSqlSmallInt = 0.TSqlSmallInt
    tempcCnt: TSqlSmallInt # temporary cCnt,Fix the field values to be null when the release schema is compiled.
    ## tempcCnt,A field to store the number of temporary variables, for unknown reasons,
    ## after performing a sqlgetdata function and circulating variables cCnt value will be changed to 0,
    ## so the values of the temporary variable to store the cCnt.
    ## After every cycle and specified to cCnt. To ensure the traversal of all fields.
  res = db.prepareFetch(query, args)
  if res == SQL_NO_DATA:
    result = @[]
  elif res == SQL_SUCCESS:
    res = SQLNumResultCols(db.stmt, cCnt)
    rowRes = newRow(cCnt)
    rowRes.setLen(max(cCnt,0))
    tempcCnt = cCnt
    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
      cCnt = tempcCnt
    res = SQLFetch(db.stmt)
    result = rowRes
  properFreeResult(SQL_HANDLE_STMT, db.stmt)
  db.sqlCheck(res)

proc getAllRows*(db: var DbConn, query: SqlQuery,
                 args: varargs[string, `$`]): seq[Row] {.
           tags: [ReadDbEffect, WriteDbEffect], raises: [DbError] .} =
  ## Executes the query and returns the whole result dataset.
  var
    rows: seq[Row] = @[]
    rowRes: Row
    sz: TSqlSmallInt = 0
    cCnt: TSqlSmallInt = 0.TSqlSmallInt
    res: TSqlSmallInt = 0.TSqlSmallInt
    tempcCnt: TSqlSmallInt # temporary cCnt,Fix the field values to be null when the release schema is compiled.
    ## tempcCnt,A field to store the number of temporary variables, for unknown reasons,
    ## after performing a sqlgetdata function and circulating variables cCnt value will be changed to 0,
    ## so the values of the temporary variable to store the cCnt.
    ## After every cycle and specified to cCnt. To ensure the traversal of all fields.
  res = db.prepareFetch(query, args)
  if res == SQL_NO_DATA:
    result = @[]
  elif res == SQL_SUCCESS:
    res = SQLNumResultCols(db.stmt, cCnt)
    rowRes = newRow(cCnt)
    rowRes.setLen(max(cCnt,0))
    tempcCnt = cCnt
    while res == SQL_SUCCESS:
      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
        cCnt = tempcCnt
      rows.add(rowRes)
      res = SQLFetch(db.stmt)
    result = rows
  properFreeResult(SQL_HANDLE_STMT, db.stmt)
  db.sqlCheck(res)

iterator rows*(db: var DbConn, query: SqlQuery,
               args: varargs[string, `$`]): Row {.
         tags: [ReadDbEffect, WriteDbEffect], raises: [DbError].} =
  ## 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: [ReadDbEffect, WriteDbEffect], 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: [ReadDbEffect, WriteDbEffect], 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:
    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: [ReadDbEffect, WriteDbEffect], raises: [DbError].} =
  ## 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: [ReadDbEffect, WriteDbEffect], raises: [DbError].} =
  ## Runs the query (typically "UPDATE") and returns the
  ## number of affected rows
  result = -1
  db.sqlCheck(SQLAllocHandle(SQL_HANDLE_STMT, db.hDb, db.stmt.SqlHandle))
  var q = dbFormat(query, args)
  db.sqlCheck(SQLPrepare(db.stmt, q.PSQLCHAR, q.len.TSqlSmallInt))
  rawExec(db, query, args)
  var rCnt = -1
  db.sqlCheck(SQLRowCount(db.hDb, rCnt))
  properFreeResult(SQL_HANDLE_STMT, db.stmt)
  result = rCnt

proc close*(db: var DbConn) {.
      tags: [WriteDbEffect], 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: [ReadDbEffect, WriteDbEffect], raises: [DbError].} =
  ## 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: [ReadDbEffect, WriteDbEffect], raises: [DbError].} =
  ## 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")