This module provides methods for access ADO DB compatible databases. SQL string literal interpolation and timestamp literal processing also included. Some useful routines, like NZ or DSum are provided also.
Nim implementation was strongly inspired from nodeJS npm module node-adodb and uses almost the same idea to access ADO DB with query and exec methods.
(adodb uses winim for low-level access to Microsoft ADO)
Usage:
import adodb # Connect to Microsoft Access 2003 DB let connect = r"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\data\test.mdb" let adoDb = newADODB(connect) # Execute SQL statement in db adoDb.exec("DELETE * FROM Users WHERE ((UserID)>100)") let user = "Test User" let id = 1 adoDb.exec(sql"INSERT INTO Users ( user_id, user_name ) VALUES ({id}, '{user} 1')") adoDb.exec(sql"UPDATE Users SET user_bday=#31.12.1999# WHERE ((user_id)={id})") # Retrieve data from db let rst = adoDb.query("SELECT * FROM Users") # Access data for rowIdx, row in rst: for idx, name, fld in row.fields: echo "Row ", rowIdx, ", Fld[", idx," | ", name, "] = ", $fld
To compile:
nim c source.nim add -d:useWinXP for Windows XP compatibility.
Database connection strings:
- For Access 2000-2003 (*.mdb):
connect = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=<path-to-mdb-file>;"
- For Access > 2007 (*.accdb):
connect = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=<path-to-accdb-file>;Persist Security Info=False;"
Types
ADODB = ref object connection: string
ADORecordset = ref object fieldNames: OrderedTableRef[string, int] rows*: seq[ADODataRow]
ADORow = ref object parent: ADORecordset fields: ADODataRow
Procs
proc len(self: ADORecordset): int {...}{.raises: [], tags: [].}
- returns row count of ADORecordset
proc `[]`(self: ADORecordset; index: Natural): ADORow {...}{.raises: [], tags: [].}
- return index-th (from 0 to len-1) row of recordset
proc `[]`(row: ADORow; index: Natural): ADOField {...}{.raises: [], tags: [].}
- returns value by index of column/field
proc `[]`(row: ADORow; field: string): ADOField {...}{.raises: [KeyError], tags: [].}
- returns value by name of column/field
proc len(row: ADORow): int {...}{.raises: [], tags: [].}
- returns count of fields
proc value[T](fld: ADOField): T
proc `$`(fld: ADOField): string {...}{.raises: [], tags: [].}
proc query(adoDb: ADODB; sql: string): ADORecordset {...}{. raises: [VariantConversionError, Exception, COMError, COMException], tags: [RootEffect].}
proc exec(adoDb: ADODB; sql: string) {...}{.raises: [VariantConversionError, Exception, COMError, COMException], tags: [RootEffect].}
proc newADODB(connection: string): ADODB {...}{.raises: [], tags: [].}
- Constructor
proc dMax[T](adoDb: ADODB; field, domain: string; criteria: string = ""): T
- return Max value of field in domain, null if no data in domain (optional criteria can be applied)
proc dMin[T](adoDb: ADODB; field, domain: string; criteria: string = ""): T
- return Min value of field in domain, null if no data in domain (optional criteria can be applied)
proc dFirst[T](adoDb: ADODB; field, domain: string; criteria: string = ""): T
- return first value of field in domain, null if no data in domain (optional criteria can be applied)
proc dLast[T](adoDb: ADODB; field, domain: string; criteria: string = ""): T
- return last value of field in domain, null if no data in domain (optional criteria can be applied)
proc dLookup[T](adoDb: ADODB; field, domain: string; criteria: string = ""): T
- return value of field in domain, null if no data in domain (optional criteria can be applied)
proc dCount(adoDb: ADODB; field, domain: string; criteria: string = ""): int {...}{. raises: [VariantConversionError, Exception, COMError, COMException], tags: [RootEffect].}
- return count of rows by field in domain, 0 if no data in domain (optional criteria can be applied)
proc dSum(adoDb: ADODB; field, domain: string; criteria: string = ""): float {...}{. raises: [VariantConversionError, Exception, COMError, COMException], tags: [RootEffect].}
- return sum of all values of field in domain, null if no data in domain (optional criteria can be applied)
proc dAvg(adoDb: ADODB; field, domain: string; criteria: string = ""): float {...}{. raises: [VariantConversionError, Exception, COMError, COMException], tags: [RootEffect].}
- return average value of all values of field in domain, null if no data in domain (optional criteria can be applied)
Iterators
iterator items(self: ADORecordset): ADORow {...}{.raises: [], tags: [].}
- iterates through recordset rows
iterator pairs(self: ADORecordset): (int, ADORow) {...}{.raises: [], tags: [].}
- iterates through rows, returns pair (index, ADORow)
iterator items(row: ADORow): ADOField {...}{.raises: [], tags: [].}
- iterates all columns of row
iterator pairs(row: ADORow): (int, ADOField) {...}{.raises: [], tags: [].}
- iterates all columns, return pair (index, value)
iterator fields(row: ADORow): (int, string, ADOField) {...}{.raises: [], tags: [].}
- iterates all columns, return (index, field name, value)
Converters
converter adoFieldToDateTime(fld: ADOField): DateTime {...}{.raises: [], tags: [].}
converter adoFieldToString(fld: ADOField): string {...}{.raises: [], tags: [].}
converter adoFieldToInt(fld: ADOField): int {...}{.raises: [], tags: [].}
converter adoFieldToBiggestInt(fld: ADOField): BiggestInt {...}{.raises: [], tags: [].}
converter adoFieldToFloat(fld: ADOField): float {...}{.raises: [], tags: [].}
converter adoFieldToBool(fld: ADOField): bool {...}{.raises: [], tags: [].}
Templates
template rowCount(self: ADORecordset): int
- alias of len
template fieldCount(row: ADORow): int
- alias of len