This documents contains some observations and pitfalls encountered when using different drivers and databases. Of course this list is not complete. Also some of these problems might have vanished in the mean time.
[Oracle][ODBC][Ora]ORA-01460: Nicht implementierte oder sinnlose Umwandlung gefordert , error code 1460, State: 37000.When doing an insert,update there is no problem. I have found he following in V$SQL:
[24]> (exec-command *con* (concatenate 'string "create or replace procedure b99 (x integer) as begin null;" (string (code-char 32)) " end;")) NIL [25]> (exec-command *con* (concatenate 'string "create or replace procedure b99 (x integer) as begin null;" (string (code-char 13)) " end;")) WARNING: [Oracle][ODBC][Ora]Trigger, procedure or function created with PL/SQL compilation error(s)., error code 24344, State: S1000. NIL [26]> (exec-command *con* (concatenate 'string "create or replace procedure b99 (x integer) as begin null;" (string (code-char 10)) " end;")) NIL [27]> (exec-query *con* (concatenate 'string "select " (string (code-char 13)) " * from dual")) (("X")) ; ("DUMMY")
More Information about Oracle ODBC Driver There is documentation for the Oracle ODBC driver, it is at
OracleŽ Database Administrator's ReferenceI 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
return string data as SQL_WCHAR.
Storing unicode strings with parameters of type :unicode-string inserts some dummy character (a '?' standing on its head). Selecting nchr(1000) returns the same charcater.
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
*** - [Microsoft][ODBC driver for Oracle][Oracle], error code 0, State: NA000.Visual Studio 6.0 has the same problem. Is 9.2 supported by Microsoft ODBC-Driver?
create or replace package test99_pkg as type refcursor is ref cursor; procedure test_cursor(v varchar2,c in out refcursor); end; create or replace package body test99_pkg as procedure test_cursor(v varchar2,c in out refcursor) is begin open c for select v as a,'1234567890' as b from dual; end; end;The cursor can be retrieved with following lisp code:
(with-prepared-statement (stm con "{call test99_pkg.test_cursor(?,?)}" '(:string :in )) (let ((str "just a string")) (exec-prepared-query stm 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; Query OK, 0 rows affected (0.01 sec) MySQL> desc bla -> ; +-------+---------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+---------------+------+-----+---------+-------+ | a | varbinary(29) | YES | | NULL | | +-------+---------------+------+-----+---------+-------+ 1 row in set (0.00 sec)how to do it right:
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; +-------+----------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+----------+------+-----+---------+-------+ | a | datetime | YES | | NULL | | +-------+----------+------+-----+---------+-------+ 1 row in set (0.00 sec)
Be carefull with variables, declared with something like set @a =1. The types are automatically determined, and a type for dates does not exist.
Of course error handling in MySQL is not existent, maybe I should change the server settings:
MySQL> select cast(cast('12345678901' as decimal) as char) as a, cast(cast('1234567890' as decimal) as char) as b; +------------+------------+ | a | b | +------------+------------+ | 9999999999 | 1234567890 | +------------+------------+ 1 row in set, 1 warning (0.00 sec)
So there is no overflow error.
Division by zero is handled, but only for an insert:
MySQL> set @a=1; Query OK, 0 rows affected (0.00 sec) MySQL> set @b=0; Query OK, 0 rows affected (0.00 sec) MySQL> select @a/@b; +-------+ | @a/@b | +-------+ | NULL | +-------+ 1 row in set, 1 warning (0.00 sec) MySQL> insert into blu select @a/@b; ERROR 1365 (22012): Division by 0
[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.38461538461538464d0With 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.
19]> (exec-query *con3* "select 1 as a /*bla") ((1)) ; ("a")Maybe this depends on the version of MySQL or some system parameter.
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]>
*** - ERROR: type "lo" does not exist; Error while executing the query, error code 7, State: 42704.According to the documentation one has to run lo.sql from the contrib section. Blobs Columns have to be declared as of type lo.
(plain-odbc:exec-query *con* "select 'a' as a,? as b " 1213) (("a" 1213)) ; ("a" "b") [77]> (plain-odbc:exec-query *con* "select 'a' as a,/* comment */ ? as b " 1213) (("a" 1213)) ; ("a" "b") [78]> (plain-odbc:exec-query *con* "select 'a' as a,/* comment ? */ ? as b " 1213) *** - The # of binded parameters < the # of parameter markers, error code 32, State: 07002. The following restarts are available: ABORT :R1 Abort main loop Break 1 [79]> [80]> (plain-odbc:exec-query *con* "select 'a' as a,/* comment '?' */ ? as b " 1213) (("a" 1213)) ; ("a" "b") ]]>Maybe this the ODBC driver and not the database itself.
create function test99 ( a in integer ) returns void as $$ begin insert into testtab99 (a) values(a+1); end; $$ language plpgsql;gives an error. PG Sql does not like that the name of the column is the same as the name of the parameter. This is a well known feature.