@@ -21,16 +19,24 @@ ODBC documentation from Microsoft.
-- Plain-odbc uses CFFI to interface to the odbc libaries. I have tested it on - Windows LispWorks Personal Edition (4.4) and - CLISP 2.38. I am too lazy to download the new version of - an "Allegro CL Trial Edition", but I am confident that this should also work. - On Windows ODBC connections to Oracle and SQL-Server work. - On Linux plain-odbc has been tested with CLISP and MySQL. -
- ++ Plain-odbc uses CFFI to interface to the ODBC libaries. + The development platform ist clisp on Windows XP. + On this system accessing Microsoft SQL-Server, Oracle 10g and MySQL 5.0 works. + I do not expect big problems with other databases which have an ODBC driver. + If there are problems they can also originate from the implementation of the + ODBC driver, e.g. MyODBC does not support out parameters. + At one time plain-odbc worked on Windows with LispWorks Personal Edition (4.4) + and Allegro. + On Linux it worked with CMUCL and MySQL. Since the interface to the ODBC library + is done with + CFFI, plain-odbc should still with on these platforms/systems. +
++ David Owen made the necessary changes to the code to make it run on + 64-bit Linux CMUCL and MySQL. +
(asdf:oos 'asdf:load-op :plain-odbc)
@@ -75,5 +81,5 @@
Oracle does not really have integer columns, integer is just a short hand
for number(37,0), and in a query this is returned as decimal.
- And plain-odbc converts decimals to doubles.
+ And plain-odbc converts decimals to doubles.
[9]> (exec-update *con* "update test1 set y=? where x=?" "text2" 1)
@@ -223,4 +229,20 @@
actual parameter.
+ Note: MySql does not support out and in/out parameters. There are no
+ return parameters. A citation from the documentation of MySql:
+
+
+For programs written in a language that provides a MySQL interface, there is no native method for directly retrieving the results of OUT or INOUT parameters from CALL statements. To get the parameter values, pass user-defined variables to the procedure in the CALL statement and then execute a SELECT statement to produce a result set containing the variable values. The following example illustrates the technique (without error checking) for a stored procedure p1 that has two OUT parameters.
+
+mysql_query(mysql, "CALL p1(@param1, @param2)");
+mysql_query(mysql, "SELECT @param1, @param2");
+result = mysql_store_result(mysql);
+row = mysql_fetch_row(result);
+mysql_free_result(result);
+
+
+
+
+
The function prepare-statement is called with the parameters
@@ -236,6 +258,6 @@
Currently the only parameter is the maximal length parameter for
the string and binary (= Oracle's raw) datatypes. Instead of a list for
- a parameter description only the name of the parameter type can be given. This is
- equivalent to a list with two elements: the symbol and the direction :in.
+ a parameter description only the name of the parameter type can be given.
+ This is equivalent to a list with two elements: the symbol and the direction :in.
The following parameter types ares supported:
@@ -337,5 +359,5 @@
the value returns true then the value is passed as :date. Of course
*date-datatype-to-universal-time* must be able to convert
- the value universal time.
+ the value to universal time.
@@ -387,12 +409,12 @@
[Function]
- connect-sql-server server database &optional user password
- Connect to sql server named server, the initial database is database.
- If user and password are supplied the connection is made with sql server
- authentication, if the parameters are not supplied then the connection is made with
- integrated security. The name of the template odbc datasource for this function is
- default-sql-server-dsn.
-
-
+ connect-sql-server server database &optional user password
+ Connect to sql server named server, the initial database is database.
+ If user and password are supplied the connection is made with sql server
+ authentication, if the parameters are not supplied then the connection is made with
+ integrated security. The name of the template odbc datasource for this function is
+ default-sql-server-dsn.
+
+
[Function]
connect-oracle server user password
@@ -401,12 +423,21 @@
default-oracle-dsn.
-
-
[Function]
- connect-access filename
+
+
[Function]
+ connect-access filename
Connect to the access database (a .mdb file) with name filename.
The name of the template odbc datasource for this function is
default-access-dsn.
-
+
+
[Function]
+ connect-mysql server database user password
+ Connect to the MySql server on server connecting as user
+ user with password password. The default database is
+ database. If database is NIL no database is chosen.
+ The name of the template odbc datasource for this function is
+ default-mysql-dsn.
+
+
Working with Connections
@@ -428,5 +459,5 @@
If a column is a LOB (BLOB or CLOB) then the data is retrieved
with the ODBC function SQLGetData. This has the consequence that
- thr following columns must also be retrieved with SQLGetData,
+ the following columns must also be retrieved with SQLGetData,
otherwise an error is raised by the driver.
Retrieving the following columns via
@@ -440,4 +471,9 @@
But be carefull with the conversion format (1.23 vs. 1,23).
+
+
[Function]
+ exec-query*connection sqlstring parameters
+ The same as exec-query, but the ODBC parameters are passed as a list.
+
[Function]
@@ -447,4 +483,9 @@
list of parameter descriptions.
+
+
[Function]
+ exec-update*connection sqlstring parameters
+ The same as exec-update, but the ODBC parameters are passed as a list.
+
[Function]
@@ -453,5 +494,11 @@
parameters is a list of parameter descriptions.
Returns the list of out and in-out parameters.
-
+
+
+
[Function]
+ exec-command*connection sqlstring parameters
+ The same as exec-command, but the ODBC parameters are passed as a list.
+
+
NOTE
@@ -460,6 +507,26 @@
One can execute a select statement with exec-update and
exec-command. But the command for exec-query
- must return a resultset.
-
+ must return a resultset. The following functions offers the functionality
+ of all three functions:
+
+
[Function]
+ exec-sql connection sqlstring &rest parameters
+ Execute an sql statement (select, dml or stored procedure call) on connection
+ with sql sqlstring. parameters is a list of parameter descriptions.
+ This function returns three values
+
+
[Function]
+ exec-sql*connection sqlstring parameters
+ The same as exec-sql, but the ODBC parameters are passed as a list.
+
[Function]