This documents contains some observations and pitfalls encountered + when using different drivers and databases. +
+More Information about Oracle ODBC Driver +There is documentation for the Oracle ODBC driver, it is at
+ +Oracle® Database Administrator's Reference-I can not make Oracle return unicode. I am using a 9.2 DB I have created a nchar column which is a unicode datatype for 9.2. +I can not make Oracle return unicode. I am using a 9.2 DB I have created a nchar column which is a +unicode datatype for 9.2.
I am using the oracle driver. There is a workaround tab where one can force the driver to @@ -99,4 +119,17 @@ Only selecting from NLS_SESSION_PARAMETERS returns a 16bit charcater. Or do I have this problem since NLS_CHARACTERSET=WE8ISO8859P15? +
+[13]> (schar (caar (exec-query *con* "select nCHR(232) from dual")) 0) + +#\LATIN_SMALL_LETTER_E_WITH_GRAVE +[14]> + +[16]> (char-code (schar (caar (exec-query *con* "select ? from dual" (list (string (code-char 1234)) :unicode-string)) ) 0)) + +1234 ++
@@ -127,11 +162,15 @@
(with-prepared-statement (stm con
"{call test99_pkg.test_cursor(?,?)}"
- '((:string :in )))
+ '(:string :in ))
(let ((str "just a string"))
- (exec-prepared-query stm (list str))))
-
-Note that the cursor parameter must be declared in/out. If a parameter ist supplied for the cursor parameter, it still works. This needs more investigations.
-
-
-mysql> create view bla as select date_add('2007-8-1',interval 1 day) as a;
+MySQL> create view bla as select date_add('2007-8-1',interval 1 day) as a;
Query OK, 0 rows affected (0.01 sec)
-mysql> desc bla
+MySQL> desc bla
-> ;
+-------+---------------+------+-----+---------+-------+
@@ -159,8 +200,8 @@
how to do it right:
-mysql> create view bla2 as select date_add(cast('2007-8-1' as datetime),interval 1 day) as a;
+MySQL> create view bla2 as select date_add(cast('2007-8-1' as datetime),interval 1 day) as a;
Query OK, 0 rows affected (0.00 sec)
-mysql> desc bla2;
+MySQL> desc bla2;
+-------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
@@ -179,5 +220,5 @@
server settings:
-mysql> select cast(cast('12345678901' as decimal) as char) as a, cast(cast('1234567890' as decimal) as char) as b;
+MySQL> select cast(cast('12345678901' as decimal) as char) as a, cast(cast('1234567890' as decimal) as char) as b;
+------------+------------+
| a | b |
@@ -191,11 +232,11 @@
Division by zero is handled, but only for an insert:
-mysql> set @a=1;
+MySQL> set @a=1;
Query OK, 0 rows affected (0.00 sec)
-mysql> set @b=0;
+MySQL> set @b=0;
Query OK, 0 rows affected (0.00 sec)
-mysql> select @a/@b;
+MySQL> select @a/@b;
+-------+
| @a/@b |
@@ -205,7 +246,78 @@
1 row in set, 1 warning (0.00 sec)
-mysql> insert into blu select @a/@b;
+MySQL> insert into blu select @a/@b;
ERROR 1365 (22012): Division by 0
+Handling of numbers
+
+[130]> (exec-query *con* "select cast(1.5e0/3.9e0 as char(200)) as a")
+
+(("0.38461538461538")) ;
+("a")
+[131]> (exec-query *con* "select cast(cast(1.5e0/3.9e0 as decimal(40,20)) as char(200)) as a")
+
+(("0.38461538461538460000")) ;
+("a")
+
+In clisp on x86 I get:
+
+[132]> (/ 1.5d0 3.9d0)
+
+0.38461538461538464d0
+
+With sql server I get the same value, as with clisp.
+
+And another example :
+
+MySQL> select 1e14/7e0,1e14/7e0-14285714285714;
++----------------+-------------------------+
+| 1e14/7e0 | 1e14/7e0-14285714285714 |
++----------------+-------------------------+
+| 14285714285714 | 0.28515625 |
++----------------+-------------------------+
+1 row in set (0.00 sec)
+
+The decimal expansion of 1/7 is periodic, so the digits 0.285 are correct. Thus
+MySQL uses the precision of double, but does not return all digits.
+
+Parameters
+
+As mentioned in the documentation, MySQL does not support out and in/out
+parameters for sql statements. As the documentation of MySQL suggest
+one should declare variables, pass them as parameters to the stored
+procedures. After the procedure call one get there contents with a select
+statement, example: select @param1 as param1,@param2 as param2.
+
+
+
+The problem with variables on MySQL is that one can only set them. It is
+not possible to declare a datatype for them.
+Another option is to select the parameters directly in the stored procedure.
+Thus instead of having out or in/out parameters, the procedure returns
+return values as a result set. Note that exec-query accepts parameters
+and can return more than one result set. One example, assume
+*con* is a MySQL connection.
+
+[33]> (exec-command *con* "
+ create procedure test99(a1 varchar(200),b1 int,c1 date)
+ begin
+ declare a2 varchar(200);
+ declare b2 int;
+ declare c2 date;
+ set a2=concat(a1,'x');
+ set b2=b1+3;
+ set c2=c1+ interval 1 day;
+ select a2 as a2,b2 as b2, c2 as c2;
+ end;")
+
+
+[34]> (exec-query *con* "call test99(?,?,?)" "abc"
+ (list 12 :integer) (list (encode-universal-time 12 3 5 12 11 2007) :date))
+
+(("abcx" 15 3403897200)) ;
+("a2" "b2" "c2")
+[35]>
+
+