src/odbcn/private/core

    Dark Mode
Search:
Group by:
  Source   Edit

Contains the whole ODBC abstraction. See main module odbcn for how-to.

Types

DataSourcesFilter = enum
  dsfAll, dsfUser, dsfSystem
  Source   Edit 
OdbcAnyConn = concept typeof(T)
    hasCustomPragma(T, dbcKind)
    distinctBase(T) is SqlHandle
  Source   Edit 
OdbcAnyPrepared = concept typeof(T)
    hasCustomPragma(T, preparedKind)
    distinctBase(T) is SqlHandle
  Source   Edit 
OdbcAnyResult = concept typeof(T)
    hasCustomPragma(T, resultKind)
    distinctBase(T) is SqlHandle
  Source   Edit 
OdbcAnyStmt = concept typeof(T)
    hasCustomPragma(T, stmtKind)
    distinctBase(T) is SqlHandle
  Source   Edit 
OdbcConn {.dbcKind.} = distinct OdbcNoConn
  Source   Edit 
OdbcConnectionPooling = enum
  ocpOff, ocpOnePerDriver,  ## All environments share a connection pool
  ocpOnePerEnv               ## Each environment has their own connection pool
Valid connection pooling methods.   Source   Edit 
OdbcEnv = distinct SqlHEnv
ODBC environment handle. This is the only handle type that can have nil values. Usually nil values are used when setting process-level attributes that affect all environments.   Source   Edit 
OdbcFastResultSet {.stmtKind, resultKind.} = distinct OdbcStmt
  Source   Edit 
OdbcHandle = OdbcEnv | OdbcAnyConn | OdbcAnyStmt
Represents a handle allocated through ODBC Driver Manager. Any instance of these types except OdbcEnv are guaranteed non-nil.   Source   Edit 
OdbcNoConn {.dbcKind, requiresInit.} = distinct SqlHDBC
  Source   Edit 
OdbcPreparedResultSet {.stmtKind, resultKind.} = distinct OdbcStmt
  Source   Edit 
OdbcPreparedStmt {.stmtKind, preparedKind.} = distinct OdbcStmt
  Source   Edit 
OdbcRowSet = object
  vals: seq[OdbcValue]
  names: seq[string]
  Source   Edit 
OdbcStmt {.stmtKind, requiresInit.} = distinct SqlHStmt
  Source   Edit 
OdbcVer = enum
  ver2_00, ver3_00, ver3_80 ## Valid ODBC versions. This is used by the Driver Manager to handle
                            ## possible incompatibilities between application and driver. Always
                            ## use latest version `ver3_80` for new applications.
  Source   Edit

Vars

globalOdbcEnv: OdbcEnv

ODBC environment used as default environment to ODBC functions. It is initialized at application initialization with reasonable configuration, but this initialization can be prevented with odbcNoEnvInit compiler define.

It is recommended to use a global environment, because all functions except allocation/freeing of the actual environment is thread-safe. Manual initialization is preferred if exceptions during initialization is problematic.

  Source   Edit

Procs

proc `$`(x: OdbcDate): string {....raises: [], tags: [].}
  Source   Edit 
proc `$`(x: OdbcTime): string {....raises: [], tags: [].}
  Source   Edit 
proc `$`(x: OdbcTimestamp): string {....raises: [], tags: [].}
  Source   Edit 
func `$`(x: OdbcValue): string {....raises: [], tags: [].}
  Source   Edit 
proc `==`(x, y: GUID): bool {....raises: [], tags: [].}
  Source   Edit 
proc `==`(x, y: OdbcDate): bool {....raises: [], tags: [].}
  Source   Edit 
proc `==`(x, y: OdbcTime): bool {....raises: [], tags: [].}
  Source   Edit 
proc `==`(x, y: OdbcTimestamp): bool {....raises: [], tags: [].}
  Source   Edit 
proc `==`(x, y: Utf16Char): bool {.borrow, ...raises: [], tags: [].}
  Source   Edit 
proc `[]`(x: OdbcRowSet; idx: int): lent OdbcValue {....raises: [], tags: [].}
Index for a value in a row set.   Source   Edit 
proc `[]`(x: OdbcRowSet; key: string): lent OdbcValue {....raises: [], tags: [].}
Index for a value in a row set by searching for the column's name.   Source   Edit 
proc affectedRows(ds: OdbcAnyResult): int

Return number of rows affected by an "INSERT", "UPDATE" or "DELETE" statement.

This may return number of rows in result set returned from a "SELECT" operation, but this support depends on the driver.

  Source   Edit 
proc beginTran(conn: OdbcConn) {....raises: [OdbcException], tags: [].}
Start a transaction. Commit with commitTran or rollback with rollbackTran.   Source   Edit 
proc bindCols[T](stmt: OdbcAnyStmt; ret: var T)
Bind all columns of the result set (or result set to be) stmt to fields in ret. Binding lasts until the result set is unbinded. This is different from ds.next(row), where the binding lasts only for the next function call itself. This can increase performance when iterating over a result set larger than a few rows.
Warning: Avoid using this in more complex situations than on stack-objects in the current scope or objects that don't change memory addresses. This can easily break memory-safety as it makes ODBC store raw pointers to program-owned memory.

This can be used with ret being an object on the stack/heap, like e.g.

type ATabObj = object
  AnInt: int
  AStr: array[256, char] # string SQL types are 0-terminated
let ds = conn.exec"select AnInt, AStr from ATab"
var row: ATabObj
ds.bindCols row
while ds.next:
  # `row` is filled here, and updated for each iteration

Note that it is an error to call ds.next(rowSet) where rowSet is OdbcRowSet after having called bindCols on ds. ODBC will raise an error because it is generally an error to mix permanent binding and per-row binding.

  Source   Edit 
proc catalog(conn: OdbcAnyConn): string
Get the catalog used for the connection. In SQL Server this is the database active.   Source   Edit 
proc commitTran(conn: OdbcConn) {....raises: [OdbcException], tags: [].}
Commit a transaction started with beginTran.   Source   Edit 
proc connect(disConn: sink OdbcNoConn; connString: string): OdbcConn {.
    ...raises: [OdbcException], tags: [].}
Connect with a connection string. Syntax is key-value pairs with '=' separator, and each pair delimited by ';'. Available keys are the attributes documented by Microsoft on SQLDriverConnect, and attributes that the driver supports.   Source   Edit 
proc connect(disConn: sink OdbcNoConn; dsn, user, pass: string): OdbcConn {.
    ...raises: [OdbcException], tags: [].}
Connect with a DSN (data source name), user and password.   Source   Edit 
proc connectionPooling(env: OdbcEnv): OdbcConnectionPooling {.
    ...raises: [OdbcException], tags: [].}
Get the current connection pooling attribute of env.   Source   Edit 
proc dbmsName(conn: OdbcConn): string {....raises: [OdbcException], tags: [].}
  Source   Edit 
proc dbmsVersion(conn: OdbcConn): string {....raises: [OdbcException], tags: [].}
  Source   Edit 
proc destroy(x: OdbcEnv) {....raises: [OdbcException], tags: [].}
  Source   Edit 
proc discardResults(ds: OdbcAnyResult)
Discards extra result sets associated with the batch. This is required for certain queries to fully execute, such as create backup.   Source   Edit 
proc disconnect(conn: sink OdbcConn): OdbcNoConn {....raises: [OdbcException],
    tags: [].}
  Source   Edit 
proc driverName(conn: OdbcConn): string {....raises: [OdbcException], tags: [].}
  Source   Edit 
proc driverOdbcVersion(conn: OdbcConn): string {....raises: [OdbcException],
    tags: [].}
  Source   Edit 
proc driverVersion(conn: OdbcConn): string {....raises: [OdbcException], tags: [].}
  Source   Edit 
proc execOnlyKeepParams(stmt: OdbcAnyPrepared)
Execute the prepared statement and discard the result set. The parameters are expected to be bound earlier with bindParams. This proc does not unbind these parameters. This is therefore ideal for reusing the bound parameters for multiple executions.   Source   Edit 
proc first(ds: sink OdbcAnyResult): Option[OdbcRowSet]
  Source   Edit 
proc first[T](ds: sink OdbcAnyResult; _: typedesc[T]): Option[T]
Get first row of the result set. The result set is discarded.   Source   Edit 
proc firstOrDefault[T](ds: sink OdbcAnyResult; _: typedesc[T]): T
Get first row of the result set, or the default value of T. The result set is discarded.   Source   Edit 
proc get(x: OdbcRowSet; idx: int): Option[OdbcValue] {....raises: [], tags: [].}
Try to get column idx from the rowset, otherwise none(OdbcValue).   Source   Edit 
proc getDatas[T](ds: OdbcAnyResult; ret: var T)
Get all data from the current row in the result set ds, and put it into fields of ret. It is an ODBC error if this is called twice since the previous next call. This also fails if next has been called instead of next.   Source   Edit 
proc getInt16Info(conn: OdbcConn; attr: uint): uint16 {....raises: [OdbcException],
    tags: [].}
Low-level connection information getter. See Microsoft docs for SQLGetInfo function for available attrs and their expected return type.   Source   Edit 
proc getInt32Info(conn: OdbcConn; attr: uint): uint32 {....raises: [OdbcException],
    tags: [].}
Low-level connection information getter. See Microsoft docs for SQLGetInfo function for available attrs and their expected return type.   Source   Edit 
proc getIntAttr(h`gensym27: OdbcEnv; attr`gensym27: int): uint {.
    ...raises: [OdbcException], tags: [].}
Low-level attribute getter on a handle. See Microsoft docs for SQLGet*Attr functions for available attrs and their expected return type.   Source   Edit 
proc getIntAttr(h`gensym35: OdbcAnyConn; attr`gensym35: int): uint
Low-level attribute getter on a handle. See Microsoft docs for SQLGet*Attr functions for available attrs and their expected return type.   Source   Edit 
proc getIntAttr(h`gensym42: OdbcAnyStmt; attr`gensym42: int): uint
Low-level attribute getter on a handle. See Microsoft docs for SQLGet*Attr functions for available attrs and their expected return type.   Source   Edit 
proc getOrDefault(x: OdbcRowSet; idx: int): OdbcValue {....raises: [], tags: [].}
Try to get column idx from the rowset, otherwise default(OdbcValue).   Source   Edit 
proc getOrDefault(x: OdbcRowSet; key: string): OdbcValue {....raises: [], tags: [].}
  Source   Edit 
proc getStringAttr(h`gensym27: OdbcEnv; attr`gensym27: int): string {.
    ...raises: [OdbcException], tags: [].}
Low-level attribute getter on a handle. See Microsoft docs for SQLGet*Attr functions for available attrs and their expected return type.   Source   Edit 
proc getStringAttr(h`gensym35: OdbcAnyConn; attr`gensym35: int): string
Low-level attribute getter on a handle. See Microsoft docs for SQLGet*Attr functions for available attrs and their expected return type.   Source   Edit 
proc getStringAttr(h`gensym42: OdbcAnyStmt; attr`gensym42: int): string
Low-level attribute getter on a handle. See Microsoft docs for SQLGet*Attr functions for available attrs and their expected return type.   Source   Edit 
proc getStringInfo(conn: OdbcConn; attr: uint): string {.
    ...raises: [OdbcException], tags: [].}
Low-level connection information getter. See Microsoft docs for SQLGetInfo function for available attrs and their expected return type.   Source   Edit 
proc index(x: OdbcRowSet; key: string): int {....raises: [], tags: [].}
Find the column index of a column name key.   Source   Edit 
proc initRowSet(ds: OdbcAnyResult or OdbcAnyPrepared; ret: var OdbcRowSet)
Initalize a row set for use when iterating over each row in the dataset. This is always run by next if it is not already initialized, so the user does not need to care about this proc. This is however useful for checking the column names and column types before fetching or supplying parameter values in prepared statements, which is an expensive action.   Source   Edit 
proc inTran(conn: OdbcConn): bool {....raises: [OdbcException], tags: [].}
true if connection is currently in a transaction.   Source   Edit 
func isDefault(x: OdbcValue): bool {....raises: [], tags: [].}
Whether x is the default value, which odbcn determines to mean "uninitialized" for the purpose of lazily initializing it. Motivation for this proc is that default(OdbcValue) is not a valid expression due to the object case statement.   Source   Edit 
proc len(x: OdbcRowSet): int {....raises: [], tags: [].}
Get number of columns in the row set.   Source   Edit 
proc maxLen(stmt: OdbcAnyStmt): Natural
Maximum size of data returned from variable size data (like varchar) in any given SQLGetData (and maybe others too). This affects retrieving row sets.   Source   Edit 
proc maxLen=(stmt: OdbcAnyStmt; n: Natural)
Maximum size of data returned from variable size data (like varchar) in any given SQLGetData (and maybe others too). This affects retrieving row sets.   Source   Edit 
proc maxRows=(stmt: OdbcAnyStmt; maxRows: Natural)
Set the maximum number of rows to return in a result set. This must be set before statement execution.
Note: Not always supported by drivers. Known not to work on FreeTDS. In this case use top X in query text instead.
  Source   Edit 
proc names(x: OdbcRowSet): lent seq[string] {....raises: [], tags: [].}
Get a view into the list of column names.   Source   Edit 
proc newOdbcConn(connString: string; env = globalOdbcEnv): OdbcConn {.
    ...raises: [OdbcException], tags: [].}
Connect with a connection string. Syntax is key-value pairs with '=' separator, and each pair delimited by ';'. Available keys are the attributes documented by Microsoft on SQLDriverConnect, and attributes that the driver supports.   Source   Edit 
proc newOdbcConn(dsn, user, pass: string; env = globalOdbcEnv): OdbcConn {.
    ...raises: [OdbcException], tags: [].}
Connect with a DSN (data source name), user and password.   Source   Edit 
proc newOdbcEnv(odbcVer: OdbcVer = ver3_80): OdbcEnv {....raises: [OdbcException],
    tags: [].}
Initialize a new ODBC environment. This is required to create connections if globalOdbcEnv is not used.   Source   Edit 
proc newOdbcNoConn(env = globalOdbcEnv): OdbcNoConn {....raises: [OdbcException],
    tags: [].}
Create a new disconnected connection handle. This is used to customize the handle before connecting.   Source   Edit 
proc newStmt(conn: OdbcConn): OdbcStmt {....raises: [OdbcException], tags: [].}
  Source   Edit 
proc next(ds: OdbcAnyResult): bool {....tags: [ReadIOEffect].}
Advance the result set cursor one row.   Source   Edit 
proc next(ds: OdbcAnyResult; ret: var OdbcRowSet): bool
Advance the result set cursor one row and retrieve all data in the row into row.   Source   Edit 
proc next(ds: OdbcAnyResult; ret: var OdbcValue): bool
Advance the result set cursor one row and retrieve the data in the first column of the row into ret.   Source   Edit 
proc next[T](ds: OdbcAnyResult; ret: var T): bool
  Source   Edit 
proc nextResultSet(ds: OdbcAnyResult): bool {....tags: [IOEffect].}

Attempts to move cursor to the next result set. Returns false if no other result set exist. Returns true if a result set is found and cursor is successfully moved to that result set.

It's important to reset the rowset object with initRowSet, or create a new rowset when fetching from this new result set.

  Source   Edit 
proc odbcVer(conn: OdbcAnyConn): string
  Source   Edit 
proc prep(conn: OdbcConn; qry: string): OdbcPreparedStmt {.
    ...raises: [OdbcException], tags: [ReadIOEffect].}
  Source   Edit 
proc prep(stmt: sink OdbcStmt; qry: string): OdbcPreparedStmt {.
    ...raises: [OdbcException], tags: [ReadIOEffect].}
  Source   Edit 
proc rollbackTran(conn: OdbcConn) {....raises: [OdbcException], tags: [].}
Rollback a transaction started with beginTran.   Source   Edit 
proc serverName(conn: OdbcConn): string {....raises: [OdbcException], tags: [].}
  Source   Edit 
proc setAttr(h`gensym27: OdbcEnv; attr`gensym27, n`gensym27: int) {.
    ...raises: [OdbcException], tags: [].}
Low-level attribute setter on a handle. See Microsoft docs for SQLSet*Attr functions for available attrs and their expected type of n.   Source   Edit 
proc setAttr(h`gensym27: OdbcEnv; attr`gensym27: int; n`gensym27: string) {.
    ...raises: [OdbcException], tags: [].}
Low-level attribute setter on a handle. See Microsoft docs for SQLSet*Attr functions for available attrs and their expected type of n.   Source   Edit 
proc setAttr(h`gensym27: OdbcEnv; attr`gensym27: int; n`gensym27: uint) {.
    ...raises: [OdbcException], tags: [].}
Low-level attribute setter on a handle. See Microsoft docs for SQLSet*Attr functions for available attrs and their expected type of n.   Source   Edit 
proc setAttr(h`gensym35: OdbcAnyConn; attr`gensym35, n`gensym35: int)
Low-level attribute setter on a handle. See Microsoft docs for SQLSet*Attr functions for available attrs and their expected type of n.   Source   Edit 
proc setAttr(h`gensym35: OdbcAnyConn; attr`gensym35: int; n`gensym35: string)
Low-level attribute setter on a handle. See Microsoft docs for SQLSet*Attr functions for available attrs and their expected type of n.   Source   Edit 
proc setAttr(h`gensym35: OdbcAnyConn; attr`gensym35: int; n`gensym35: uint)
Low-level attribute setter on a handle. See Microsoft docs for SQLSet*Attr functions for available attrs and their expected type of n.   Source   Edit 
proc setAttr(h`gensym42: OdbcAnyStmt; attr`gensym42, n`gensym42: int)
Low-level attribute setter on a handle. See Microsoft docs for SQLSet*Attr functions for available attrs and their expected type of n.   Source   Edit 
proc setAttr(h`gensym42: OdbcAnyStmt; attr`gensym42: int; n`gensym42: string)
Low-level attribute setter on a handle. See Microsoft docs for SQLSet*Attr functions for available attrs and their expected type of n.   Source   Edit 
proc setAttr(h`gensym42: OdbcAnyStmt; attr`gensym42: int; n`gensym42: uint)
Low-level attribute setter on a handle. See Microsoft docs for SQLSet*Attr functions for available attrs and their expected type of n.   Source   Edit 
proc setCatalog(conn: OdbcAnyConn; cat: string)

Set the catalog used for the connection. In SQL Server this is a USE <cat> statement.

This is the recommended way of changing database context when using connection pooling. Using USE <cat> statements directly will make the next owner of the connection inherit a different database than requested.

Note: Not all drivers support this function after a connection has been established.
  Source   Edit 
proc setConnectionPooling(env: OdbcEnv; value: OdbcConnectionPooling) {.
    ...raises: [OdbcException], tags: [].}
Affects the connection pooling attribute of env. env may be nil, in which case this setting affects all subsequently allocated environments for the computer process.   Source   Edit 
proc setConnTimeout(conn: OdbcAnyConn; n: Natural)
Timeout for any request.   Source   Edit 
proc setLoginTimeout(conn: OdbcNoConn; n: Natural) {....raises: [OdbcException],
    tags: [].}
Timeout for establishing initial connection (i.e. OdbcNoConn.connect)   Source   Edit 
proc setOdbcTraceFile(f: string) {.sideEffect, ...raises: [OdbcException], tags: [].}
Set the filename that the Driver Manager will write ODBC C function traces to after startOdbcTrace is called.   Source   Edit 
proc startedOdbcTrace(): bool {....raises: [OdbcException], tags: [].}
true if trace of ODBC C function calls from the Driver Manager is active.   Source   Edit 
proc startOdbcTrace() {.sideEffect, ...raises: [OdbcException], tags: [].}
Start trace of ODBC C functions being called from this application. The Driver Manager writes the trace to a file, which is the filename last specified in a setOdbcTraceFile call. If that has never happened, the default as specified in "system information" is used, otherwise "ODBC.LOG" in the root directory.
Warning: Passwords in connection strings used to connect to a server will be written to the trace file in plain text.
  Source   Edit 
proc stopOdbcTrace() {.sideEffect, ...raises: [OdbcException], tags: [].}
Stop trace of ODBC C functions calls by the Driver Manager.   Source   Edit 
proc timeout(stmt: OdbcAnyStmt): Natural
Time before a query being executes is timed out.   Source   Edit 
proc timeout=(stmt: OdbcAnyStmt; n: Natural)
Set the time before a query being executed is timed out. This can be set for any state, but only makes sense before execution.   Source   Edit 
proc unbind(ds: sink OdbcFastResultSet): OdbcStmt {....raises: [OdbcException],
    tags: [].}
  Source   Edit 
proc unbind(ds: sink OdbcPreparedResultSet): OdbcPreparedStmt {.
    ...raises: [OdbcException], tags: [].}
Unbind parameters so the prepared dataset is ready for next execution.   Source   Edit 
proc userName(conn: OdbcConn): string {....raises: [OdbcException], tags: [].}
  Source   Edit

Iterators

iterator items(ds: OdbcAnyResult): OdbcRowSet
  Source   Edit 
iterator items[T](ds: OdbcAnyResult; _: typedesc[T]): T
  Source   Edit 
iterator listDataSources(env: OdbcEnv = globalOdbcEnv;
                         filter: DataSourcesFilter = dsfAll): tuple[
    server, driver: string] {....tags: [ReadIOEffect], raises: [OdbcException].}

Iterate over all "data sources", or DSNs, on the system. On Windows, these are defined in the registry. On Linux with unixodbc, these are defined in "/etc/odbc.ini" or "~/.odbc.ini".

The server field is the DSN name. This is used as value to the DSN attribute when connecting to a server.

The driver field is the ODBC driver used when connecting to the DSN.

  Source   Edit 
iterator listDrivers(env = globalOdbcEnv): tuple[name, attrs: string] {.
    ...tags: [ReadIOEffect], raises: [OdbcException].}

Iterate over all ODBC drivers on the system. On Windows, these are defined in the registry. On Linux with unixodbc, these are defined in "/etc/odbcinst.ini".

The name field is the ODBC driver name. This is used as value to the "DRIVER" attribute when connecting to a server.

The attrs field is a list delimited by null-bytes describing the attributes of the driver. Each element is a key-value pair delimited by a =. The attrs field may look like this: FileUsage=1\0FileExtns=*.dbf\0. The \0 is a null-byte character.

  Source   Edit

Macros

macro bindParams(stmt: OdbcAnyStmt; order: openArray[string];
                 params: varargs[untyped])

Bind parameters to a query. Optimally call this after the statement is prepared, or executed if not prepared. order may be empty, in which case params is a list of values. If order is non-empty, its length must be the same as the number of parameters in the query. In this case params may be key-value pairs. If the length of order is incorrect, then the ODBC C library will raise an error.

params may be just an object or tuple, in which case their fields are bound to the parameters in the query in order. Specify a static order if only a subset of the fields must be bound, or the parameters go in a specific order.

  Source   Edit 
macro prep(stmt: sink OdbcStmt; qry: static string): untyped
Prepare a compile-time-known query.   Source   Edit 
macro withExec(stmt: var OdbcAnyPrepared; paramsAndCode: varargs[untyped])

Executes the prepared statement and then executes the code in the last argument to withExec where a result set named rs with type OdbcPreparedResultSet is injected into scope.

var stmt = conn.prep "select * from MyTable where MyCol = ?"
stmt.withExec(4):
  for row in rs:
    echo row[0], " and ", row[1]

The use-case for this is, when using multiple withExecs on a single statement, to avoid having to transition the stmt back and forth. This increases code tidiness.

  Source   Edit

Templates

template exec(conn: OdbcConn; qry: string; params: varargs[untyped]): OdbcFastResultSet
Execute a simple query without parameters. This uses SQLExecDirect which is the fastest way of executing the simplest queries.   Source   Edit 
template exec(stmt: sink OdbcPreparedStmt; params: varargs[untyped]): OdbcPreparedResultSet
Execute the prepared statement and transition into a result set.   Source   Edit 
template exec(stmt: sink OdbcStmt; qry: static string; params: varargs[untyped]): OdbcFastResultSet
  Source   Edit 
template exec(stmt: sink OdbcStmt; qry: string; params: varargs[untyped]): OdbcFastResultSet
Fastest way of executing a query.   Source   Edit 
template execOnly(stmt: OdbcPreparedStmt; params: varargs[untyped])
Execute the prepared statement and discarding the result set.   Source   Edit 
template odbcCheck(handle; stmt: untyped)
  Source   Edit 
template odbcCheckNoRaise(handle; stmt)
  Source   Edit 
template odbcCheckRet(handle, stmt): TSqlSmallInt
  Source   Edit 
template odbcCol(i: TSqlUSmallInt) {.pragma.}
Annotate this on object fields. See bindCols for usage.   Source   Edit 
template prep(conn: OdbcConn; qry: static string): untyped
Prepare a compile-time-known query.   Source   Edit

Made with Nim. Generated: 2024-01-09 19:09:52 UTC