modules/mysql.lsp

   1 ;; @module mysql.lsp
   2 ;; @description MySQL v.4 interface
   3 ;; @version v2.31 - addition for mysql_escape_string (Jeff)
   4 ;; @author Lutz Mueller 2003, Gordon Fischer 2005, Jeff 2007
   5 ;;
   6 ;; newLISP connection to the MySQL v 4.1 database
   7 ;;
   8 ;; Current tested MySQL version is 4.1.14
   9 ;;
  10 ;; See also @link http://www.mysql.com www.mysql.com
  11 ;;
  12 ;; This file is tested with MySQL 4.0/4.1  and will not work on
  13 ;; previous versions 3.22 and 3.23 which need different offsets
  14 ;; into the MYSQL result data structure.
  15 ;;
  16 ;; <h3>Implemented functions</h3>
  17 ;; <pre>
  18 ;;     MySQL:init ................ get a database handle MYSQL
  19 ;;     MySQL:connect ............. connect to a database
  20 ;;     MySQL:query ............... execute a SQL statement
  21 ;;     MySQL:num-rows ............ rows in result of query
  22 ;;     MySQL:num-fields .......... columns in result of query
  23 ;;     MySQL:fetch-row ........... get row from the query result
  24 ;;     MySQL:fetch-all ........... get all rows from the last query
  25 ;;     MySQL:database ............ return all database names
  26 ;;     MySQL:tables .............. return all tables names
  27 ;;     MySQL:fields .............. return all fields in a table
  28 ;;     MySQL:data-seek ........... position in result for fetching
  29 ;;     MySQL:affected-rows ....... number of affected rows from operation
  30 ;;     MySQL:inserted-id ......... last value of auto increment id operation
  31 ;;     MySQL:escape .............. escapes SQL input string using mysql_escape_string
  32 ;;     MySQL:error ............... get error message
  33 ;;     MySQL:close-db ............ close database connection
  34 ;; </pre>
  35 ;;
  36 ;; For details on usage see the documentation for the mysql5.lsp module.
  37
  38 (context 'MySQL)
  39
  40 (set 'libmysqlclient "/usr/lib/libmysqlclient.so.14")
  41
  42 (import libmysqlclient "mysql_init")
  43 (import libmysqlclient "mysql_real_connect")
  44 (import libmysqlclient "mysql_get_host_info")
  45 (import libmysqlclient "mysql_escape_string")
  46 (import libmysqlclient "mysql_query")
  47 (import libmysqlclient "mysql_real_query")
  48 (import libmysqlclient "mysql_store_result")
  49 (import libmysqlclient "mysql_free_result")
  50 (import libmysqlclient "mysql_data_seek")
  51 (import libmysqlclient "mysql_fetch_row")
  52 (import libmysqlclient "mysql_close")
  53 (import libmysqlclient "mysql_fetch_field_direct")
  54 (import libmysqlclient "mysql_insert_id")
  55
  56 ; initialize database access, get an access handle MYSQL
  57 ;
  58 (define (init)
  59   (set 'MYSQL (mysql_init 0))
  60   (if (= MYSQL 0) (set 'MYSQL nil))
  61   (not (= MYSQL nil)))
  62
  63 ; connect to a specific database
  64 ;
  65 (define (connect host user passw database)
  66   (not(= (mysql_real_connect MYSQL host user passw database 0 0 0) 0)))
  67
  68 ; send a SQL string for evaluation
  69 ;
  70 (define (query sql)
  71   (if MYSQL_RES (mysql_free_result MYSQL_RES))
  72   (set 'result  (= (mysql_query MYSQL sql) 0))
  73   (set 'MYSQL_RES (mysql_store_result MYSQL))
  74   (if (= MYSQL_RES 0) (set 'MYSQL_RES nil))
  75   (if (and result (find "insert into" sql 1)) (set 'result (inserted-id)))
  76   result)
  77
  78 ; number of rows from last query
  79 ;
  80 (define (num-rows)
  81   (if MYSQL_RES (get-int MYSQL_RES)))
  82
  83 ; number of columns from last query
  84 ;
  85 (define (num-fields)
  86   (if MYSQL_RES (get-int (int (+ MYSQL_RES 60)))))
  87
  88
  89 ; format the result based on the field type.
  90 ;
  91
  92 (define (keep-type res_ptr field_addr column_num, data)
  93   (set 'type_ptr (mysql_fetch_field_direct res_ptr (int column_num)))
  94   ; The field type is the 20th field of the MySQL_FIELD structure
  95   ; since fields 1-19 are all 4 byte fields we get the enum value
  96   ; like so
  97   (set 'data (get-int (int (+ type_ptr (* 19 4)))))
  98   ; Consult 'enum_field_types' in mysql_com.h for values
  99   (if (= data 1) ;; boolean
 100         (get-string field_addr)
 101       (= data 3) ;; integer
 102         (int (get-string field_addr))
 103       (= data 12) ;; datetime
 104         (apply date-value (map int (parse (get-string field_addr) "[-: ]" 0)))
 105       (= data 4) ;; float
 106         (float (get-string field_addr))
 107       ; else
 108       (get-string field_addr)
 109   )
 110 )
 111
 112 ; fetch a row from a query, subsequent calls go trough the table
 113 ; until the bottom is reached
 114 ;
 115 (define (fetch-row)
 116   (if MYSQL_RES
 117     (set 'rdata (mysql_fetch_row MYSQL_RES))
 118     (set 'rdata 0))
 119   (if (!= rdata 0)
 120     (begin
 121       (set 'row '())
 122       (dotimes (field (num-fields))
 123             (set 'field_addr (get-int (int (+ rdata (* field 4)))))
 124             (if (= field_addr 0)
 125               (push nil row -1) ;; what to do when the field contains NULL
 126               (push (keep-type MYSQL_RES field_addr field) row -1)))
 127     row)))
 128
 129 ; return all rows/columns from the last query
 130 ;
 131 (define (fetch-all)
 132   (set 'all '())
 133   (dotimes (x (num-rows)) (push (fetch-row) all))
 134   (reverse all))
 135
 136 ; get a list of databases
 137 ;
 138 (define (databases)
 139   (query "show databases;")
 140   (fetch-all))
 141
 142 ; get a list of tables
 143 ;
 144 (define (tables)
 145   (query "show tables;")
 146   (fetch-all))
 147
 148 ; get a list of fields
 149 ;
 150 (define (fields table)
 151   (query (append "show fields from " table ";"))
 152   (fetch-all))
 153
 154
 155 ; position to row in result set
 156 ;
 157 (define (data-seek offset)
 158   (if MYSQL_RES
 159     (begin
 160     (mysql_data_seek MYSQL_RES offset)
 161     true)))
 162
 163 ; Escapes input value using mysql_escape_string
 164 (define (escape value , safe-value)
 165   (set 'safe-value (dup " " (+ 1 (length value))))
 166   (MySQL:mysql_escape_string safe-value value (length value))
 167   safe-value)
 168
 169 ; text info about error
 170 ;
 171 (define (error)
 172   (if MYSQL (get-string (+ MYSQL 60))))
 173
 174 ; number of affected rows by last MySQL:query operation
 175 ;
 176 (define (affected-rows)
 177   (if MYSQL
 178     (get-int (int (+ MYSQL 368)))))
 179
 180 ; last insert_id from auto increment field
 181 ;
 182 (define (inserted-id)
 183   (if MYSQL (mysql_insert_id MYSQL)))
 184
 185 ; close database access
 186 ;
 187 (define (close-db)
 188   (if MYSQL_RES (mysql_free_result MYSQL_RES))
 189   (if MYSQL (mysql_close MYSQL))
 190   (set 'MYSQL nil)
 191   (set 'MYSQL_RES nil)
 192   true)
 193
 194 (context 'MAIN)
 195
 196 ; ;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;
 197 ;
 198 ; test data base functions
 199 ;
 200
 201 (define (test-mysql)
 202   (MySQL:init)
 203   (MySQL:connect 0 "" 0 "test")
 204
 205   (println "databases:")
 206   (MySQL:query "show databases;")
 207   (dotimes (x (MySQL:num-rows)) (println (MySQL:fetch-row)))
 208   (println)
 209
 210   (MySQL:query "create table fruits (name CHAR(20),
 211                                      qty INT(3),
 212                                      num INT(4) AUTO_INCREMENT UNIQUE);")
 213
 214   (MySQL:query "insert into fruits values ('apples', 11, null);")
 215   (println "inserted-id: " (MySQL:inserted-id))
 216   (MySQL:query "insert into fruits values ('oranges', 22, null);")
 217   (println "inserted-id: " (MySQL:inserted-id))
 218   (MySQL:query "insert into fruits values ('bananas', 33, null);")
 219   (println "inserted-id: " (MySQL:inserted-id))
 220
 221   (println "\n" (MySQL:affected-rows) " affected rows in query")
 222
 223   (println "inserted into fruits:")
 224   (MySQL:query "select * from fruits;")
 225   (dotimes (x (MySQL:num-rows)) (println (MySQL:fetch-row)))
 226
 227   (println "rows = " (MySQL:num-rows) " fields = " (MySQL:num-fields))
 228   (println)
 229
 230   (println "tables:")
 231   (MySQL:query "show tables;")
 232   (dotimes (x (MySQL:num-rows)) (println (MySQL:fetch-row)))
 233   (println)
 234
 235   (MySQL:query "select * from fruits;")
 236   (MySQL:data-seek 2)
 237   (println "data-seek to offset 2:")
 238   (println (MySQL:fetch-row))
 239   (println)
 240
 241   (println "fetch-all:")
 242   (println (MySQL:query "select * from fruits;"))
 243   (println (MySQL:fetch-all))
 244
 245   (MySQL:query "drop table fruits;")
 246   (MySQL:close-db)
 247 )
 248
 249 ; eof