Ignore:
Timestamp:
02/03/2008 08:14:49 PM (18 years ago)
Author:
raverkamp
revision id:
svn-v3-trunk1:1c22b0a8-4d0b-0410-a296-af6a2e6f35e3:plain-odbc%2Ftrunk:10
Message:

adding exec-sql, changes to documentation, more tests

File:
1 edited

Legend:

Unmodified
Added
Removed

  • 64-bit/doc/notes.html

    r2.1.2 r2.1.3  
    11<html>
    2 <!-- ;;;-*- Mode: HTML; -*- -->   
     2<!-- -*- Mode: HTML; -*- -->   
     3  <head>
     4    <title>Notes on Plain-Odbc</title>
     5  </head>
     6<body>
    37    <h3>Notes</h3>
    4    
     8    <p> This documents contains some observations and pitfalls encountered
     9        when using different drivers and databases.
     10    </p>
    511    <h4> driver-connect </h4>
    612    Using driver-connect one does not have to create an ODBC datasource for every
    … …  
    4046   
    4147   
    42     <h4> performances </h4>
     48    <h4> performance </h4>
    4349    It seems that with clisp storing and retrieving C datatypes from memory take a lot time.
    4450    This should be possible without consing, maybe I have to change my clisp FFC module.
    … …  
    9096MYODBC (3.51?) does not return unicode character data as datatype SQL_WVARCHAR. If for SQL_VARCHAR data the return datatype is set to SQLWCHAR, the length in the indicator is 0. It seems that the buffer is also empty (= #x000000000 ...). There is some talk about better support for unicode in another version (3.52?).
    9197
     98<h4>
     99<p>
     100More Information about Oracle ODBC Driver</h4>
     101There is documentation for the  Oracle  ODBC driver, it is at</p>
     102<tt>
     103Oracle® Database Administrator's Reference<br>
     10410g Release 2 (10.2) for UNIX-Based Operating Systems<br>
     105Part Number B15658-05<br>
     106G Using Oracle ODBC Driver<br>
     107</tt>
     108or better under the link
     109<a href="http://download-uk.oracle.com/docs/cd/B19306_01/server.102/b15658/app_odbc.htm"> G Using Oracle ODBC Driver</a>, but this can change any time.
     110
    92111<h4>Oracle 9.2 and unicode</h4>
    93112<p>
    94 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.
     113I can not make Oracle return unicode. I am using a 9.2 DB I have created a nchar column which is a
     114unicode datatype for 9.2.
    95115<p>
    96116I am using the oracle driver. There is a workaround tab where one can force the driver to
    … …  
    99119Only selecting from  NLS_SESSION_PARAMETERS returns a 16bit charcater.
    100120Or do I have this problem since NLS_CHARACTERSET=WE8ISO8859P15?
     121<h4> Oracle 10gR2 and unicode</h4>
     122It works now (with Oracle driver!):
     123<pre>
     124[13]> (schar (caar (exec-query *con* "select nCHR(232) from dual")) 0)
     125
     126#\LATIN_SMALL_LETTER_E_WITH_GRAVE
     127[14]>
     128
     129[16]> (char-code (schar (caar (exec-query *con* "select ? from dual"  (list (string (code-char 1234)) :unicode-string))  ) 0))
     130
     1311234
     132</pre>
     133
    101134
    102135<h4>Microsoft ODBC driver for Oracle</h4>
    … …  
    107140
    108141<h4>Oracle and ref cursors</h4>
    109 This is easy with the oracle odbc driver and an 9.2 database.
     142This works with 9.2 and 10g and 10gR2. Make sure that the ODBC datasource is
     143configured correctly. On the "Oracle ODBC Driver Configuration" window check the
     144checkbox with label "Enable Result Sets".<br>
    110145Example:
    111146<pre>
    … …  
    127162(with-prepared-statement (stm con
    128163                                "{call test99_pkg.test_cursor(?,?)}"
    129                                 '((:string :in )))
     164                                '(:string :in ))
    130165    (let ((str "just a string"))
    131       (exec-prepared-query stm (list str))))
    132 </pre>
    133 Note that the cursor parameter must be declared in/out. If a parameter ist supplied for the cursor parameter, it still works. <em>This needs more investigations.</em>
    134 
    135 <h4>Oracle TIMESTAMP datatype in 9.2, 10g</h4>
     166      (exec-prepared-query stm str)))
     167</pre>
     168Note that the cursor parameter must be declared in/out.
     169If a parameter ist supplied for the cursor parameter, it still works.
     170</em>
     171<em>This needs more investigations.</em>
     172
     173<h4>Oracle TIMESTAMP datatype</h4>
     174<h5> 9.2, 10g</h4>
    136175Windows: For the Oracle-Driver, queries on TIMESTAMP columns return SQL_NULL_TYPE. Normally this would be retrieved
    137176as String. But the program coredumps at the first fetch.
    … …  
    140179At least we prevent the coredump of the Oracle driver.<br>
    141180I am pretty sure that this is a driver bug, Microsoft Access coredumps as well in similar situations.
    142 
    143 
    144 <h4>MYSQL</h4>
     181<h5>10g R2</h5>
     182It works now, a timestamp column is retrieved as SQL_TIMESTAMP.
     183Of course the sub second precision is not supported.
     184
     185<h4>MySQL</h4>
    145186This is not one would expect!
    146187<pre>
    147 mysql> create view bla as select  date_add('2007-8-1',interval 1 day) as a;
     188MySQL> create view bla as select  date_add('2007-8-1',interval 1 day) as a;
    148189Query OK, 0 rows affected (0.01 sec)
    149190
    150 mysql> desc bla
     191MySQL> desc bla
    151192    -> ;
    152193+-------+---------------+------+-----+---------+-------+
    … …  
    159200how to do it right:
    160201<pre>
    161 mysql> create view bla2 as select  date_add(cast('2007-8-1' as datetime),interval 1 day) as a;
     202MySQL> create view bla2 as select  date_add(cast('2007-8-1' as datetime),interval 1 day) as a;
    162203Query OK, 0 rows affected (0.00 sec)
    163204
    164 mysql> desc bla2;
     205MySQL> desc bla2;
    165206+-------+----------+------+-----+---------+-------+
    166207| Field | Type     | Null | Key | Default | Extra |
    … …  
    179220server settings:
    180221<pre>
    181 mysql> select cast(cast('12345678901' as decimal) as char) as a, cast(cast('1234567890' as decimal) as char) as b;
     222MySQL> select cast(cast('12345678901' as decimal) as char) as a, cast(cast('1234567890' as decimal) as char) as b;
    182223+------------+------------+
    183224| a          | b          |
    … …  
    191232Division by zero is handled, but only for an insert:
    192233<pre>
    193 mysql> set @a=1;
     234MySQL> set @a=1;
    194235Query OK, 0 rows affected (0.00 sec)
    195236
    196 mysql> set @b=0;
     237MySQL> set @b=0;
    197238Query OK, 0 rows affected (0.00 sec)
    198239
    199 mysql> select @a/@b;
     240MySQL> select @a/@b;
    200241+-------+
    201242| @a/@b |
    … …  
    2052461 row in set, 1 warning (0.00 sec)
    206247
    207 mysql> insert into blu select @a/@b;
     248MySQL> insert into blu select @a/@b;
    208249ERROR 1365 (22012): Division by 0
    209250</pre>
    210251
     252<h5>Handling of numbers</h5>
     253<pre>
     254[130]> (exec-query *con* "select cast(1.5e0/3.9e0 as char(200)) as a")
     255
     256(("0.38461538461538")) ;
     257("a")
     258[131]> (exec-query *con* "select cast(cast(1.5e0/3.9e0 as decimal(40,20)) as char(200)) as a")
     259
     260(("0.38461538461538460000")) ;
     261("a")
     262</pre>
     263In clisp on x86 I get:
     264<pre>
     265[132]> (/ 1.5d0 3.9d0)
     266
     2670.38461538461538464d0
     268</pre>
     269With sql server I get the same value, as with clisp.
     270<p>
     271And another example :
     272<pre>
     273MySQL> select 1e14/7e0,1e14/7e0-14285714285714;
     274+----------------+-------------------------+
     275| 1e14/7e0       | 1e14/7e0-14285714285714 |
     276+----------------+-------------------------+
     277| 14285714285714 |              0.28515625 |
     278+----------------+-------------------------+
     2791 row in set (0.00 sec)
     280</pre>
     281The decimal expansion of 1/7 is periodic, so the digits 0.285 are correct. Thus
     282MySQL uses the precision of double, but does not return all digits.
     283
     284<h3>Parameters</h3>
     285<p>
     286As mentioned in the documentation, MySQL does not support out and in/out
     287parameters for sql statements. As the documentation of MySQL suggest
     288one should declare variables, pass them as parameters to the stored
     289procedures. After the procedure call one get there contents with a select
     290statement, example: <tt>select @param1 as param1,@param2 as param2</tt>.
     291
     292</p>
     293<p>
     294The problem with variables on MySQL is that one can only set them. It is
     295not possible to declare a datatype for them.
     296Another option is to select the parameters directly in the stored procedure.
     297Thus instead of having out or in/out parameters, the procedure returns
     298return values as a result set. Note that <tt>exec-query<tt> accepts parameters
     299and can return more than one result set. One example, assume
     300<tt>*con*</tt> is a MySQL connection.
     301<pre>
     302[33]>  (exec-command *con* "
     303    create procedure test99(a1 varchar(200),b1 int,c1 date)
     304    begin
     305    declare a2 varchar(200);
     306    declare b2 int;
     307    declare c2 date;
     308     set a2=concat(a1,'x');
     309      set b2=b1+3;
     310      set c2=c1+ interval 1 day;
     311      select a2 as a2,b2 as b2, c2 as c2;
     312    end;")
     313
     314
     315[34]> (exec-query *con* "call test99(?,?,?)" "abc"
     316                          (list 12 :integer) (list  (encode-universal-time 12 3 5 12 11 2007) :date))
     317
     318(("abcx" 15 3403897200)) ;
     319("a2" "b2" "c2")
     320[35]>
     321</pre>
     322</body>
    211323</html>
Note: See TracChangeset for help on using the changeset viewer.