1;; @module odbc.lsp 2;; @description ODBC database interface 3;; @version 1.7 - comments redone for automatic documentation 4;; @version 1.8 - doc changes 5;; @author Lutz Mueller, 2003-2010 6;; 7;; <h2>OCBC Interface functions</h2> 8;; This module has only been tested on Win32 but should work on UNIX too 9;; with few modifications. At the beginning of the program file include 10;; a 'load' statement for the module: 11;; <pre> 12;; (load "c:/Program Files/newlisp/modules/odbc.lsp") 13;; ; or shorter 14;; (module "odbc.lsp") 15;; </pre> 16;; Some of the code assumes Intel (low -> high) little-endian byte order. 17;; 18;; See the end of file for a test function 'test-odbc', which demonstrates the 19;; usage of the module and can be used to test a correct ODBC installation and 20;; data source setup. 21;; <h2>Requirements</h2> 22;; On Win32 platforms required 'odbc32.dll' is part of the OS's installations. 23;; There is no UNIX function import tested or adapted for this ODBC module. 24;; <h2>Function overview</h2> 25;; <pre> 26;; (ODBC:connect data-source-name-str user-name-str password-str) ; connect to a data source 27;; (ODBC:query sql-str) ; perform a SQL statement 28;; (ODBC:num-cols) ; number of columns in a query result set from 'select' 29;; (ODBC:column-atts col) ; retrieve columns attributes 30;; (ODBC:fetch-row) ; fetch a row of data after a sql query with 'select' 31;; (ODBC:affected-rows) ; number of rows affected by a sql query: 'delete', 'update' etc. 32;; (ODBC:tables) ; return a list of tables in the current database 33;; (ODBC:columns table-name) ; return an array of column attributes in table-name 34;; (ODBC:close-db) ; close database connection 35;; </pre> 36 37(context 'ODBC) 38 39; ----------------- import functions from DLL ------------------- 40 41 42; set to the appropiate library on Unix or Win32 43(define ODBC-library "odbc32.dll") 44 45; Constants used, make sure these constants are Ok on your Operating System or Platform. 46; Note, that (define var value) is the same as as saying (set 'var value), it is here more 47; of a visual distinction, documenting that values are constants and shouldn't be changed. 48; Most of these are defned in sql.h, sqltypes.h and sqlext.h of your platform. 49; The following definitions come from c:\Borland\BCC\Include 50 51(define SQL_HANDLE_ENV 1) 52(define SQL_HANDLE_DBC 2) 53(define SQL_HANDLE_STMT 3) 54(define SQL_HANDLE_DESC 4) 55 56(define SQL_NULL_HANDLE 0) 57 58(define SQL_SUCCESS 0) 59(define SQL_SUCCESS_WITH_INFO 1) 60 61(define SQL_OV_ODBC3 3) 62(define SQL_ATTR_ODBC_VERSION 200) 63 64(define SQL_LOGIN_TIMEOUT 103) 65 66(define SQL_NTS -3) 67 68(define SQL_CHAR 1) 69(define SQL_C_CHAR SQL_CHAR) 70 71 72; Import functions 73; there are many more, which are not used here, goto microsoft.com and unixodbc.org for 74; more information on ODBC SQLxxx API 75 76 77(set 'funcs '( 78 "SQLAllocHandle" 79 "SQLSetEnvAttr" 80 "SQLFreeHandle" 81 "SQLSetConnectAttr" 82 "SQLConnect" 83 "SQLDisconnect" 84 "SQLGetDiagRec" 85 "SQLExecDirect" 86 "SQLNumResultCols" 87 "SQLRowCount" 88 "SQLBindCol" 89 "SQLFetch" 90 "SQLDescribeCol" 91 "SQLTables" 92 "SQLColumns")) 93 94(dolist (fun funcs) 95 (import ODBC-library fun)) 96 97; ------------------------------- reserve space for global pointers ---------------------------- 98 99(set 'ptr-odbc-env " ") ; pointer to environment handle 100(set 'ptr-odbc-conn " ") ; pointer to connection handle 101(set 'ptr-result-cols " ") ; pointer to number of columns in result 102(set 'ptr-odbc-stmt " ") ; pointer to handle for sql statement 103(set 'ptr-result-rows " ") ; pointer to number of affected rows from sql statement 104 105(set 'odbc-stmt nil) ; statement handle 106(set 'odbc-conn nil) ; connection handle 107(set 'result-cols 0) ; contains the number of rows resulting from a 'select' qery 108 109; -------------------------------------- AUXILIARY ROUTINES ------------------------------------ 110 111; check result code 112 113(define (is-error-result) 114 ;result is 16bit, disregard upper 16 bits 115 (set 'odbc-result (& 0xFFFF odbc-result)) 116 (and (!= odbc-result SQL_SUCCESS) (!= odbc-result SQL_SUCCESS_WITH_INFO))) 117 118; initialize and make connection 119 120(define (init) 121 (and 122 ; get environment handle 123 (set 'odbc-result (SQLAllocHandle SQL_HANDLE_ENV SQL_NULL_HANDLE ptr-odbc-env)) 124 125 (if (is-error-result) 126 (begin 127 (println "Error allocating env handle") 128 nil) true) 129 130 (set 'odbc-env (get-int ptr-odbc-env)) 131 132 ; register version 133 (set 'odbc-result (SQLSetEnvAttr odbc-env SQL_ATTR_ODBC_VERSION SQL_OV_ODBC3 0)) 134 135 (if (is-error-result) 136 (begin 137 (println "Error setting ODBC environment") 138 (SQLFreeHandle SQL_HANDLE_ENV odbc-env) 139 nil) true)) 140 ) 141 142; get diagnostic record 143; 144; retrieve error info after last failed ODBC request 145; 146; type is one of the following: 147; 148; SQL_HANDLE_ENV, SQL_HANDLE_DBC, SQL_HANDLE_STMT, SQL_HANDLE_DESC 149; 150 151(define (error type) 152 (set 'diag-status " ") 153 (set 'diag-err " ") 154 (set 'diag-mlen " ") 155 (set 'diag-message " ") 156 (SQLGetDiagRec type odbc-conn 1 diag-status diag-err diag-message 64 diag-mlen) 157 (string diag-message " " diag-status (get-int diag-err))) 158 159; bind all columns to string output 160; 161; before fetching rows string variables are configured with sufficient long string buffers 162; for the 'fetch' statement. 163; 164 165(set 'vars '(var0 var1 var2 var3 var4 var5 var6 var7 var8 var9 166 var10 var11 var12 var13 var14 var15 var16 var17 var18 var19 167 var20 var21 var22 var23 var24 var25 var26 var27 var28 var29 168 var30 var32 var32 var33 var34 var35 var36 var37 var38 var39 169 var40 var41 var42 var43 var44 var45 var46 var47 var48 var49 170 var50 var51 var52 var53 var54 var55 var56 var57 var58 var59 171 var60 var51 var62 var63 var64)) 172 173 174(define (bind-columns) 175 (set 'ptr-result-err " ") 176 (for (v 1 result-cols) 177 (set 'w (+ (last (column-atts v)) 1)) 178 (set (nth v vars) (format (string "%" w "s") "")) 179 (SQLBindCol odbc-stmt (int v) SQL_C_CHAR (eval (nth v vars)) w ptr-result-err)) 180 181 true) 182 183 184;==================================== USER ROUTINES ======================================== 185 186 187;; @syntax (ODBC:connect <str-data-source> <str-user> <str-password>) 188;; @param <str-data-source> The ODBC dara source. 189;; @param <str-user> The user name. 190;; @param <str-password> The password of the user. 191;; @return 'true' on success, 'nil' on failure. 192;; Connect to a data-source with a user name and password. 193;; The data-source name must be configured first via ODBC 194;; administrative tools, i.e. a control applet on Win32. 195;; 196;; @example 197;; (ODBC:connect "mydatabase" "johndoe" "secret") 198 199(define (ODBC:connect data-source user password) 200 201 (and 202 (init) 203 204 ; allocate connection handle 205 (set 'odbc-result (SQLAllocHandle SQL_HANDLE_DBC odbc-env ptr-odbc-conn)) 206 207 (if (is-error-result) 208 (begin 209 (println "Error allocating conn handle") 210 (SQLFreeHandle SQL_HANDLE_ENV odbc-env) 211 nil) true) 212 213 (set 'odbc-conn (get-int ptr-odbc-conn)) 214 215 ; set timeout for connection 216 (SQLSetConnectAttr odbc-conn SQL_LOGIN_TIMEOUT 5 0) 217 218 ; connect to a data source 219 (set 'odbc-result (SQLConnect odbc-conn data-source SQL_NTS 220 user SQL_NTS 221 password SQL_NTS)) 222 223 (if (is-error-result) 224 (begin 225 (println "Could not connect") 226 (SQLFreeHandle SQL_HANDLE_DBC odbc-conn) 227 (SQLFreeHandle SQL_HANDLE_ENV odbc-env) 228 nil) true)) 229 ) 230 231 232;; @syntax (ODBC:query <str-sql>) 233;; @param <str-sql> The SQL statement string. 234;; @return 'true' on success, 'nil' on failure. 235;; Send and SQL string for database manipulation 236;; 237;; @example 238;; (query "select * from someTable") 239;; (query "delete from addresses") 240;; (query "insert into fruits values ('apples', 11)") 241 242(define (ODBC:query sql-string) 243 (and 244 ; is stmt handle exists free it 245 (if odbc-stmt (begin 246 (SQLFreeHandle SQL_HANDLE_STMT odbc-stmt) 247 (set 'odbc-stmt nil) 248 true) true) 249 250 ; allocate statement handle 251 (set 'odbc-result (SQLAllocHandle SQL_HANDLE_STMT odbc-conn ptr-odbc-stmt)) 252 253 (if (is-error-result) 254 (begin 255 (println "could not allocate statement handle") 256 nil) 257 (set 'odbc-stmt (get-int ptr-odbc-stmt))) 258 259 ; do the query 260 (set 'odbc-result (SQLExecDirect odbc-stmt sql-string SQL_NTS)) 261 (if (is-error-result) 262 (begin 263 (println "query failed") 264 nil) 265 true) 266 267 ; find number of columns in result set 268 (SQLNumResultCols odbc-stmt ptr-result-cols) 269 (set 'result-cols (& 0xFFFF (get-int ptr-result-cols))) 270 271 ; bind colums to string vars for fetching 272 (if (not (= result-cols 0)) (bind-columns) true) 273 true 274 ) 275 276 ) 277 278 279;; @syntax (ODBC:num-cols) 280;; @return Number of columns in the result set. 281 282(define (num-cols) result-cols) 283 284 285;; @syntax (ODBC:columns-atts <num-col>) 286;; @param <num-col> The number of the column, starting witth 1 for the first. 287;; @return A list of attributes for a column in a result set. 288;; Returns a list with the columname SQL, data type number and required column size 289;; when displaying in a string. For the data type number and SQL data type see 290;; the file 'sql.h' on your platform OS, i.e. 'SQL_VARCHAR', 'SQL_INTEGER' etc. 291;; 292;; before using 'ODBC:column-atts' a query has to be performed. 293;; 294;; @example 295;; (ODBC:column-atts 1) => ("name" 12 20) 296 297;; The first column has the header '"name"' with data type 'SQL_VARCHAR' (12) 298;; and a maximum display width of 20 characters. 299 300(define (column-atts col) 301 (set 'col-name-out " ") 302 (set 'ptr-name-len " ") 303 (set 'ptr-data-type " ") 304 (set 'ptr-col-size " ") 305 (set 'ptr-dec-dig " ") 306 (set 'ptr-nullable " ") 307 308 (set 'odbc-result (& 0xFFFF (SQLDescribeCol odbc-stmt (int col) 309 col-name-out 32 310 ptr-name-len 311 ptr-data-type 312 ptr-col-size 313 ptr-dec-dig 314 ptr-nullable))) 315 (list col-name-out (& 0xFFFF (get-int ptr-data-type)) (get-int ptr-col-size))) 316 317 318 319;; @syntax (ODBC:fetch-row) 320;; @return A list of items of a result set row. 321;; Fetches a row of data after a previously executed 'ODBC:query'. Each data is formatted as 322;; a string, and can be converted using newLISP conversion functions 323;; like: 'int', 'float' or 'string'. 324;; 325;; If data types are unknown then 'ODBC:column-atts' can be used to retrieve the data type 326;; number. 327;; 328;; @example 329;; (ODBC:fetch-row) => ("apples" "11") 330 331(define (fetch-row , row) 332 (bind-columns) 333 (set 'odbc-result (& 0xFFFF (SQLFetch odbc-stmt))) 334 (if (is-error-result) 335 nil 336 (begin 337 (for (x result-cols 1) (push (eval (nth x vars)) row)) 338 row))) ; not necessary starting 9.9.5 because push returns the list 339 340 341;; @syntax (ODBC:affected-rows) 342;; @return Number of rows affected by the last SQL statement. 343;; Returns the number of rows affected by an 'insert', 'update' or 'delete', 'ODBX:query' 344;; operation. After a 'select' operation the number -1 will be returned. 345 346(define (affected-rows) 347 (set 'odbc-result (& 0xFFFF (SQLRowCount odbc-stmt ptr-result-rows))) 348 (if (is-error-result) 0 (get-int ptr-result-rows))) 349 350 351;; @syntax (ODBC:tables) 352;; @return A list of tables in the current database connection. 353 354(define (tables) 355 (if (and 356 ; is stmt handle exists free it 357 (if odbc-stmt (begin 358 (SQLFreeHandle SQL_HANDLE_STMT odbc-stmt) 359 (set 'odbc-stmt nil) 360 true) true) 361 362 ; allocate statement handle 363 (set 'odbc-result (SQLAllocHandle SQL_HANDLE_STMT odbc-conn ptr-odbc-stmt)) 364 (if (is-error-result) 365 (begin 366 (println "could not allocate statement handle") 367 nil) 368 (set 'odbc-stmt (get-int ptr-odbc-stmt))) 369 370 ; do the query 371 (set 'odbc-result (SQLTables odbc-stmt 0 SQL_NTS 0 SQL_NTS "%" SQL_NTS 0 SQL_NTS)) 372 (if (is-error-result) 373 (begin 374 (println "query failed") 375 nil) 376 true) 377 378 ;; find number of columns in result set 379 (SQLNumResultCols odbc-stmt ptr-result-cols) 380 (set 'result-cols (& 0xFFFF (get-int ptr-result-cols))) 381 382 ;; bind colums to string vars for fetching 383 (if (not (= result-cols 0)) (bind-columns) true) 384 385 (begin 386 (set 'names nil) 387 (while (set 'row (ODBC:fetch-row)) 388 (push (nth 2 row) names -1)) 389 true) 390 ) names) 391 ) 392 393;; @syntax (ODBC:columns <str-table-name>) 394;; @param <str-table-name> The name of the table. 395;; @return A list of list of columns and their attributes. 396 397(define (ODBC:columns table) 398 (if (and 399 ; is stmt handle exists free it 400 (if odbc-stmt (begin 401 (SQLFreeHandle SQL_HANDLE_STMT odbc-stmt) 402 (set 'odbc-stmt nil) 403 true) true) 404 405 ; allocate statement handle 406 (set 'odbc-result (SQLAllocHandle SQL_HANDLE_STMT odbc-conn ptr-odbc-stmt)) 407 408 (if (is-error-result) 409 (begin 410 (println "could not allocate statement handle") 411 nil) 412 (set 'odbc-stmt (get-int ptr-odbc-stmt))) 413 414 ; do the query 415 (set 'odbc-result (SQLColumns odbc-stmt 0 SQL_NTS 0 SQL_NTS 416 table SQL_NTS 0 SQL_NTS)) 417 (if (is-error-result) 418 (begin 419 (println "query failed") 420 nil) 421 true) 422 423 ; find number of columns in result set 424 (SQLNumResultCols odbc-stmt ptr-result-cols) 425 (set 'result-cols (& 0xFFFF (get-int ptr-result-cols))) 426 427 ; bind colums to string vars for fetching 428 (if (not (= result-cols 0)) (bind-columns) true) 429 430 (begin 431 (set 'names nil) 432 (while (set 'col (ODBC:fetch-row)) 433 (set 'attr (list (nth 3 col) (nth 5 col) (nth 6 col) (nth 8 col))) 434 (push attr names -1)) 435 true) 436 ) names) 437 ) 438 439 440;; @syntax (ODBC:close-db) 441;; @return 'true' on success, 'nil' on failure. 442;; Closes a database connection. 443 444(define (close-db) 445 (if odbc-stmt (SQLFreeHandle SQL_HANDLE_STMT odbc-stmt)) 446 (set 'odbc-stmt nil) 447 (if odbc-conn (begin 448 (SQLDisconnect odbc-conn) 449 (SQLFreeHandle SQL_HANDLE_DBC odbc-conn) 450 (set 'odbc-conn nil))) 451 true) 452 453 454(context 'MAIN) 455;=================================== test ================================================= 456; 457; Note: before performing this test a database with name 'test' 458; and data source name 'test' should be created. The data base 459; should contain a table described by the following SQL statement: 460; 461; create table fruits (name CHAR(20), qty INT(3)) 462; 463; For this configure an Access database: 'test-db' with table 'fruits' 464; and a text field 'name' width 20 and field 'qty' as type integer. 465; Make the 'User Data Source' connection with the ODBC control applet 466; in control-panel/administrative-tools for the MS Access *.mdb driver 467; and pick as a data source name and database location the test-db.mdb i 468; created. 469; 470; On some systems the table can also be created with an SQL statement 471; (ODBC:query "create ....") 472; On MS-Acces this will not work and the table has to be created 473; manually. 474; 475; A sample of test-db.mdb can be found at: 476; http://newlisp.org/downloads/Other/ 477; 478; example: 479; (test-odbc) 480; 481 482 483 484(define (test-odbc) 485 486 ; Note, on MS-Access must create table fruits manually first 487 ; else you could do: 488 ; (ODBC:query "create table fruits (name CHAR(20), qty INT(3))") 489 ; for "aUser" and "secret" you may just put empty strings "" 490 ; i.e. (ODBC:connect "test" "" "") 491 ; when on Windows on the same machine 492 493 (if (not (ODBC:connect "test-db" "" "")) (exit)) 494 495 (println "connected ...") 496 497 (ODBC:query "insert into fruits values ('apples', 11)") 498 (ODBC:query "insert into fruits values ('oranges', 22)") 499 (ODBC:query "insert into fruits values ('bananas', 33)") 500 501 (println "inserted 3 records") 502 503 (ODBC:query "select * from fruits") 504 505 (println "performed a query") 506 507 (println (ODBC:num-cols) " columns in result set") 508 (println "fetching rows ...") 509 (while (set 'row (ODBC:fetch-row)) 510 (set 'row (map trim row)) 511 (println row)) 512 (println) 513 514 515 (ODBC:query "delete from fruits") 516 (println "rows deleted: " (ODBC:affected-rows)) 517 518 (println "\nclosing database") 519 (ODBC:close-db) 520 ) 521 522 523 524; eof ; 525