Module index

source download

Module: odbc.lsp

ODBC database interface

Version: 1.7 - comments redone for automatic documentation
Version: 1.8 - doc changes
Author: Lutz Mueller, 2003-2010

OCBC Interface functions

This module has only been tested on Win32 but should work on UNIX too with few modifications. At the beginning of the program file include a load statement for the module:
 (load "c:/Program Files/newlisp/modules/odbc.lsp")
 ; or shorter
 (module "odbc.lsp")
Some of the code assumes Intel (low -> high) little-endian byte order.

See the end of file for a test function test-odbc, which demonstrates the usage of the module and can be used to test a correct ODBC installation and data source setup.


On Win32 platforms required odbc32.dll is part of the OS's installations. There is no UNIX function import tested or adapted for this ODBC module.

Function overview

  (ODBC:connect data-source-name-str user-name-str password-str) ; connect to a data source
  (ODBC:query sql-str)          ; perform a SQL statement
  (ODBC:num-cols)               ; number of columns in a query result set from select
  (ODBC:column-atts col)        ; retrieve columns attributes
  (ODBC:fetch-row)              ; fetch a row of data after a sql query with select
  (ODBC:affected-rows)          ; number of rows affected by a sql query: delete, update etc.
  (ODBC:tables)                 ; return a list of tables in the current database
  (ODBC:columns table-name)     ; return an array of column attributes in table-name
  (ODBC:close-db)               ; close database connection



syntax: (ODBC:connect str-data-source str-user str-password)
parameter: str-data-source - The ODBC dara source.
parameter: str-user - The user name.
parameter: str-password - The password of the user.

return: true on success, nil on failure.

Connect to a data-source with a user name and password. The data-source name must be configured first via ODBC administrative tools, i.e. a control applet on Win32.

 (ODBC:connect "mydatabase" "johndoe" "secret")



syntax: (ODBC:query str-sql)
parameter: str-sql - The SQL statement string.

return: true on success, nil on failure.

Send and SQL string for database manipulation

 (query "select * from someTable")
 (query "delete from addresses")
 (query "insert into fruits values ('apples', 11)")



syntax: (ODBC:num-cols)

return: Number of columns in the result set.



syntax: (ODBC:columns-atts num-col)
parameter: num-col - The number of the column, starting witth 1 for the first.

return: A list of attributes for a column in a result set.

Returns a list with the columname SQL, data type number and required column size when displaying in a string. For the data type number and SQL data type see the file sql.h on your platform OS, i.e. SQL_VARCHAR, SQL_INTEGER etc.

before using ODBC:column-atts a query has to be performed.

 (ODBC:column-atts 1)  => ("name" 12 20)
The first column has the header "name" with data type SQL_VARCHAR (12) and a maximum display width of 20 characters.



syntax: (ODBC:fetch-row)

return: A list of items of a result set row.

Fetches a row of data after a previously executed ODBC:query. Each data is formatted as a string, and can be converted using newLISP conversion functions like: int, float or string.

If data types are unknown then ODBC:column-atts can be used to retrieve the data type number.

 (ODBC:fetch-row) => ("apples" "11")



syntax: (ODBC:affected-rows)

return: Number of rows affected by the last SQL statement.

Returns the number of rows affected by an insert, update or delete, ODBX:query operation. After a select operation the number -1 will be returned.



syntax: (ODBC:tables)

return: A list of tables in the current database connection.



syntax: (ODBC:columns str-table-name)
parameter: str-table-name - The name of the table.

return: A list of list of columns and their attributes.



syntax: (ODBC:close-db)

return: true on success, nil on failure.

Closes a database connection.

- ∂ -

generated with newLISP  and newLISPdoc