Fix row reading procs to not use SQLRowCount, but SQLFetch until returns SQL_NO_DATA

change SqlCheck() and SqlGetDBMS() to sqlCheck() and sqlGetDBMS() (camelCase consistency)

1 files changed, 49 insertions, 54 deletions

diff --git a/lib/impure/db_odbc.nim b/lib/impure/db_odbc.nim
index 4f0b0469d..1b72c6241 100644
--- a/lib/impure/db_odbc.nim
+++ b/lib/impure/db_odbc.nim
@@ -169,11 +169,11 @@ proc dbError*(db: var DbConn) {.
     properFreeResult(SQL_HANDLE_ENV, db.env)
     raise e
 
-proc SqlCheck(db: var DbConn, resVal: TSqlSmallInt) {.raises: [DbError]} =
-  ## Wrapper that checks if ``resVal`` is not SQL_SUCCESS and if so, raises [EDb]
-  if resVal != SQL_SUCCESS: dbError(db)
+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 {.
+proc sqlGetDBMS(db: var DbConn): string {.
         tags: [ReadDbEffect, WriteDbEffect], raises: [] .} =
   ## Returns the ODBC SQL_DBMS_NAME string
   const
@@ -182,7 +182,7 @@ proc SqlGetDBMS(db: var DbConn): string {.
     sz: TSqlSmallInt = 0
   buf[0] = '\0'
   try:
-    db.SqlCheck(SQLGetInfo(db.hDb, SQL_DBMS_NAME, cast[SqlPointer](buf.addr),
+    db.sqlCheck(SQLGetInfo(db.hDb, SQL_DBMS_NAME, cast[SqlPointer](buf.addr),
                         4095.TSqlSmallInt, sz.addr))
   except: discard
   return $buf.cstring
@@ -220,11 +220,11 @@ proc prepareFetch(db: var DbConn, query: SqlQuery,
   # requires calling
   #      properFreeResult(SQL_HANDLE_STMT, db.stmt)
   # when finished
-  db.SqlCheck(SQLAllocHandle(SQL_HANDLE_STMT, db.hDb, db.stmt))
+  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))
+  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, `$`]) {.
@@ -235,10 +235,10 @@ proc prepareFetchDirect(db: var DbConn, query: SqlQuery,
   # requires calling
   #      properFreeResult(SQL_HANDLE_STMT, db.stmt)
   # when finished
-  db.SqlCheck(SQLAllocHandle(SQL_HANDLE_STMT, db.hDb, db.stmt))
+  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))
+  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: [].} =
@@ -285,20 +285,19 @@ iterator fastRows*(db: var DbConn, query: SqlQuery,
     rowRes: Row
     sz: TSqlSmallInt = 0
     cCnt: TSqlSmallInt = 0.TSqlSmallInt
-    rCnt = -1
-
+    res: TSqlSmallInt = 0.TSqlSmallInt
   db.prepareFetch(query, args)
-  db.SqlCheck(SQLNumResultCols(db.stmt, cCnt))
-  db.SqlCheck(SQLRowCount(db.stmt, rCnt))
-  rowRes = newRow(cCnt)
-  for rNr in 1..rCnt:
+  res = SQLNumResultCols(db.stmt, cCnt)
+  rowRes.setLen(cCnt)
+  while res == SQL_SUCCESS:
     for colId in 1..cCnt:
       buf[0] = '\0'
-      db.SqlCheck(SQLGetData(db.stmt, colId.SqlUSmallInt, SQL_C_CHAR,
+      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
+    res = SQLFetch(db.stmt)
+  db.sqlCheck(res)
   properFreeResult(SQL_HANDLE_STMT, db.stmt)
 
 iterator instantRows*(db: var DbConn, query: SqlQuery,
@@ -307,22 +306,22 @@ iterator instantRows*(db: var DbConn, query: SqlQuery,
   ## 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
+    rowRes: Row = @[]
     sz: TSqlSmallInt = 0
     cCnt: TSqlSmallInt = 0.TSqlSmallInt
-    rCnt = -1
+    res: TSqlSmallInt = 0.TSqlSmallInt
   db.prepareFetch(query, args)
-  db.SqlCheck(SQLNumResultCols(db.stmt, cCnt))
-  db.SqlCheck(SQLRowCount(db.stmt, rCnt))
-  rowRes = newRow(cCnt)
-  for rNr in 1..rCnt:
+  res = SQLNumResultCols(db.stmt, cCnt)
+  rowRes.setLen(cCnt)
+  while res == SQL_SUCCESS:
     for colId in 1..cCnt:
       buf[0] = '\0'
-      db.SqlCheck(SQLGetData(db.stmt, colId.SqlUSmallInt, SQL_C_CHAR,
+      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)
+    res = SQLFetch(db.stmt)
+  db.sqlCheck(res)
   properFreeResult(SQL_HANDLE_STMT, db.stmt)
 
 proc `[]`*(row: InstantRow, col: int): string {.inline.} =
@@ -341,17 +340,17 @@ proc getRow*(db: var DbConn, query: SqlQuery,
   var
     sz: TSqlSmallInt = 0.TSqlSmallInt
     cCnt: TSqlSmallInt = 0.TSqlSmallInt
-    rCnt = -1
-  result = @[]
+    res: TSqlSmallInt = 0.TSqlSmallInt
   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))
+  res = SQLNumResultCols(db.stmt, cCnt)
+  result.setLen(max(cCnt,0))
+  if res == SQL_SUCCESS:
+    for colId in 1..cCnt:
+      db.sqlCheck(SQLGetData(db.stmt, colId.SqlUSmallInt, SQL_C_CHAR,
+                               cast[cstring](buf.addr), 4095.TSqlSmallInt, sz.addr))
+      result[colId-1] = $buf.cstring
+    res = SQLFetch(db.stmt)
+  db.sqlCheck(res)
   properFreeResult(SQL_HANDLE_STMT, db.stmt)
 
 proc getAllRows*(db: var DbConn, query: SqlQuery,
@@ -362,20 +361,20 @@ proc getAllRows*(db: var DbConn, query: SqlQuery,
     rowRes: Row
     sz: TSqlSmallInt = 0
     cCnt: TSqlSmallInt = 0.TSqlSmallInt
-    rCnt = -1
+    res: TSqlSmallInt = 0.TSqlSmallInt
   db.prepareFetch(query, args)
-  db.SqlCheck(SQLNumResultCols(db.stmt, cCnt))
-  db.SqlCheck(SQLRowCount(db.stmt, rCnt))
+  res = SQLNumResultCols(db.stmt, cCnt)
   result = @[]
-  for rNr in 1..rCnt:
-    rowRes = @[]
+  rowRes.setLen(max(cCnt,0))
+  while res == SQL_SUCCESS:
     buf[0] = '\0'
     for colId in 1..cCnt:
-      db.SqlCheck(SQLGetData(db.stmt, colId.SqlUSmallInt, SQL_C_CHAR,
+      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))
+      rowRes[colId-1] = $buf.cstring
     result.add(rowRes)
+    res = SQLFetch(db.stmt)
+  db.sqlCheck(res)
   properFreeResult(SQL_HANDLE_STMT, db.stmt)
 
 iterator rows*(db: var DbConn, query: SqlQuery,
@@ -407,10 +406,9 @@ proc tryInsertId*(db: var DbConn, query: SqlQuery,
   if not tryExec(db, query, args):
     result = -1'i64
   else:
-    echo "DBMS: ",SqlGetDBMS(db).toLower()
     result = -1'i64
     try:
-      case SqlGetDBMS(db).toLower():
+      case sqlGetDBMS(db).toLower():
       of "postgresql":
         result = getValue(db, sql"SELECT LASTVAL();", []).parseInt
       of "mysql":
@@ -438,15 +436,12 @@ proc execAffectedRows*(db: var DbConn, query: SqlQuery,
   ## 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)
+  db.sqlCheck(SQLAllocHandle(SQL_HANDLE_STMT, db.hDb, db.stmt.SqlHandle))
   var q = dbFormat(query, args)
-  res = SQLPrepare(db.stmt, q.PSQLCHAR, q.len.TSqlSmallInt)
-  if res != SQL_SUCCESS: dbError(db)
+  db.sqlCheck(SQLPrepare(db.stmt, q.PSQLCHAR, q.len.TSqlSmallInt))
   rawExec(db, query, args)
   var rCnt = -1
-  result = SQLRowCount(db.hDb, rCnt)
-  if res != SQL_SUCCESS: dbError(db)
+  db.sqlCheck(SQLRowCount(db.hDb, rCnt))
   properFreeResult(SQL_HANDLE_STMT, db.stmt)
   result = rCnt