db_odbc raises errors when fetching large Oracle CLOBs

[alistairkeys]

The db_odbc package produces errors when fetching Oracle CLOB fields larger than 4K. Note that I've not confirmed the behaviour on other database types with equivalent large character types (e.g. TEXT) so they may also have the same issue.

Note that I'm not experienced with ODBC so take my findings below with a pinch of salt.

Example

Given a table with a CLOB field:

create table some_table ( some_big_clob CLOB );
insert into some_table values ( rpad('*', 4000, '*') ); 
update some_table set some_big_clob = some_big_clob || some_big_clob; 
commit;

import std/db_odbc

var db = open("foo", "bar", "baz", "qux")
try:
  echo db.getValue(sql"select some_big_clob from some_table")
finally:
  db.close()

Current Output

Error: 01004 [Oracle][ODBC]String data, right truncated.

Expected Output

The CLOB value in its entirety

Possible Solution

I think I've tracked it down to db_odbc not handling SQL_SUCCESS_WITH_INFO. This constant is specified in odbcsql but it's not referenced in db_odbc. The following seems to do the trick for the getValue call I'm using:

1. change the sqlCheck procedure to check for [SQL_SUCCESS, SQL_SUCCESS_WITH_INFO, SQL_NO_DATA] (i.e. add SQL_SUCCESS_WITH_INFO in there).

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_SUCCESS_WITH_INFO, SQL_NO_DATA]: dbError(db)

2. amend the getRow proc to fetch again while it receives SQL_SUCCESS_WITH_INFO

      var fetchRes: TSqlSmallInt = SQL_SUCCESS_WITH_INFO
      while fetchRes == SQL_SUCCESS_WITH_INFO:
        buf[0] = '\0'
        fetchRes = SQLGetData(db.stmt, colId.SqlUSmallInt, SQL_C_CHAR,
                               cast[cstring](buf.addr), 4095, sz.addr)
        db.sqlCheck(fetchRes)
        rowRes[colId-1] &= $(addr buf)

I can't guarantee the code above is reliable as I don't know if SQL_SUCCESS_WITH_INFO is exclusively used to indicate "you need to fetch again". I found ODBC documentation suggesting there are functions (SQLGetDiagRec or SQLGetDiagField) that can be called to get further information so the changes may be more involved than my example code suggests.

The sqlCheck proc is used in quite a few places and SQL_SUCCESS_WITH_INFO probably isn't relevant to most invocations.

There are several places in db_odbc that fetch rows. My example change above is simply the procedure I happened to be using when the error occurred and you may have to change other places in a similar fashion to fix them.

Additional Information

c:\>nim -v
Nim Compiler Version 1.4.0 [Windows: amd64]
Compiled at 2020-10-18
Copyright (c) 2006-2020 by Andreas Rumpf

active boot switches: -d:release

I'm using Windows 10 / Oracle 11g / Oracle Instant Client 19.