diff options
Diffstat (limited to 'lib/impure/db_odbc.nim')
| -rw-r--r-- | lib/impure/db_odbc.nim | 463 |
1 files changed, 0 insertions, 463 deletions
diff --git a/lib/impure/db_odbc.nim b/lib/impure/db_odbc.nim deleted file mode 100644 index 6af69d842..000000000 --- a/lib/impure/db_odbc.nim +++ /dev/null @@ -1,463 +0,0 @@ -# -# -# 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 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 - -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 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: [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, `$`]) {. - 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)) - db.SqlCheck(SQLFetch(db.stmt)) - -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) - 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: [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 - 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: [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 - 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: [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 - 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: [ReadDbEffect, WriteDbEffect], raises: [DbError].} = - ## 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: [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: - 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: [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 - 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: [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") |