suckless/dwm - Unnamed repository; edit this file 'description' to name the repository.

diff options

Diffstat (limited to 'Makefile')

0 files changed, 0 insertions, 0 deletions

# # # 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 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) except: discard return (res.int, $(addr sqlState), $(addr nativeErr), $(addr 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 $(addr buf) 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 == '?': 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: TSqlInteger = 0 cCnt: TSqlSmallInt = 0 res: TSqlSmallInt = 0 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)) 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] = $(addr buf) 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: TSqlInteger = 0 cCnt: TSqlSmallInt = 0 res: TSqlSmallInt = 0 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)) 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] = $(addr buf) 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: TSqlInteger = 0 cCnt: TSqlSmallInt = 0 res: TSqlSmallInt = 0 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)) 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] = $(addr buf) 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: TSqlInteger = 0 cCnt: TSqlSmallInt = 0 res: TSqlSmallInt = 0 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)) 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] = $(addr buf) 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")


context:
space:
mode: