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

  • combined/doc/documentation.html

    r4 r5  
    55    <title>Plain-odbc Documentation</title>
    66  </head>
    7 
    87  <body>
    98    <h1>plain-odbc documentation</h1>
    … …  
    1211      This is the version of plain-odbc which I am using right now.
    1312      The API could stand some improvements, currently it is rather
    14       simple. Hopefully it suffices for many uses. I thought I make
    15       the current version available before I make my mind up about
    16       another API.
     13      simple. But I think it is good enough for many uses.
     14      A higher level API is not a goal.
    1715    </p>
    1816    <p>
    … …  
    2119        ODBC documentation from Microsoft</a>.
    2220    </p>
    23     <h2>Platforms, not up to date</h2>
    24     <p>
    25       Plain-odbc uses CFFI to interface to the odbc libaries. I have tested it on
    26       Windows LispWorks Personal Edition (4.4) and
    27       CLISP 2.38. I am too lazy to download the new version of
    28       an "Allegro CL Trial Edition", but I am confident that this should also work.
    29       On Windows ODBC connections to Oracle and SQL-Server work.
    30       On Linux plain-odbc has been tested with CLISP and MySQL.
    31     </p>
    32    
     21 <h3>Platforms</h3>
     22    <p>
     23      Plain-odbc uses CFFI to interface to the ODBC libaries.
     24      The development platform ist clisp on Windows XP.
     25      On this system accessing Microsoft SQL-Server, Oracle 10g and MySQL 5.0 works.
     26      I do not expect big problems with other databases which have an ODBC driver.
     27      If there are problems they can also originate from the implementation of the
     28      ODBC driver, e.g. MyODBC does not support out parameters.
     29      At one time plain-odbc worked on Windows with LispWorks Personal Edition (4.4)
     30      and Allegro.
     31      On Linux it worked with CMUCL and MySQL. Since the interface to the ODBC library
     32      is done with
     33      CFFI, plain-odbc should still with on these platforms/systems.
     34      </p>
     35    <p>
     36      David Owen made the necessary changes to the code to make it run on
     37      64-bit Linux CMUCL and MySQL.
     38    </p>
    3339    <h2> Using plain-odbc, Examples </h2>
    34     You must load plain-odbc into lisp. plain-odbc is an asdf module,
     40    You must load plain-odbc into lisp. Plain-odbc is an asdf module,
    3541    so you need asdf. Make sure that asdf is able to find CFFI and plain-odbc.
    3642    <pre>(asdf:oos 'asdf:load-op :plain-odbc)
    … …  
    7581    Oracle does not really have integer columns, integer is just a short hand
    7682    for <tt>number(37,0)</tt>, and in a query this is returned as decimal.
    77     And plain-odbc converts decimals to doubles.
     83    And plain-odbc converts decimals to doubles. 
    7884    <pre>
    7985[9]> (exec-update *con* "update test1 set y=? where x=?" "text2" 1)
    … …  
    223229      actual parameter.
    224230    </p>
     231    <p><b>Note: MySql does not support out and in/out parameters. There are no
     232      return parameters. A citation from the documentation of MySql:</b>
     233</p>
     234      <p><em>
     235For 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.
     236</em><tt><br>
     237mysql_query(mysql, "CALL p1(@param1, @param2)");<br>
     238mysql_query(mysql, "SELECT @param1, @param2");<br>
     239result = mysql_store_result(mysql);<br>
     240row = mysql_fetch_row(result);<br>
     241mysql_free_result(result);<br>
     242</tt>
     243
     244
     245      </p>
     246
    225247    <p>
    226248      The function  <tt>prepare-statement</tt> is called with the parameters
    … …  
    236258    Currently the only parameter is the maximal length parameter for
    237259    the string and binary (= Oracle's raw) datatypes. Instead of a list for
    238     a parameter description only the name of the parameter type can be given. This is
    239     equivalent to a list with two elements: the symbol and the direction <tt>:in</tt>.
     260    a parameter description only the name of the parameter type can be given.
     261    This is equivalent to a list with two elements: the symbol and the direction <tt>:in</tt>.
    240262    The following parameter types ares supported:
    241263  </p>
    … …  
    337359        the value returns true then the value is passed as :date. Of course
    338360        <tt>*date-datatype-to-universal-time*</tt> must be able to convert
    339         the value universal time.
     361        the value to universal time.
    340362    </ul>
    341363
    … …  
    387409    <p>
    388410      <br><b>[Function]</b><br>
    389       <tt>connect-sql-server <i>server database</i> &optional <i>user password</i></tt><br>
    390       Connect to sql server named <i>server</i>, the initial database is <i>database</i>.
    391       If <i>user</i> and <i>password</i> are supplied the connection is made with sql server
    392       authentication, if the parameters are not supplied then the connection is made with
    393       integrated security. The name of the template odbc datasource for this function is
    394       <tt>default-sql-server-dsn</tt>.
    395     </p>
    396     <p>
     411        <tt>connect-sql-server <i>server database</i> &optional <i>user password</i></tt><br>
     412          Connect to sql server named <i>server</i>, the initial database is <i>database</i>.
     413          If <i>user</i> and <i>password</i> are supplied the connection is made with sql server
     414          authentication, if the parameters are not supplied then the connection is made with
     415          integrated security. The name of the template odbc datasource for this function is
     416          <tt>default-sql-server-dsn</tt>.
     417      </p>
     418      <p>
    397419      <br><b>[Function]</b><br>
    398420      <tt>connect-oracle <i>server user password</i></tt><br>
    … …  
    401423      <tt>default-oracle-dsn</tt>.
    402424    </p>
    403     <p>
    404       <br><b>[Function]</b><br>
    405       <tt>connect-access <i>filename</i></tt><br>
     425      <p>
     426        <br><b>[Function]</b><br>
     427            <tt>connect-access <i>filename</i></tt><br>
    406428      Connect to the access database (a .mdb file) with name filename.
    407429      The name of the template odbc datasource for this function is
    408430      <tt>default-access-dsn</tt>.
    409431    </p>
    410      
     432      <p>
     433        <br><b>[Function]</b><br>
     434            <tt>connect-mysql <i>server database user password</i></tt><br>
     435         Connect to the MySql server on <i>server</i> connecting as user
     436        <i>user</i> with password <i>password</i>. The default database is
     437       <i>database</i>. If database is <tt>NIL</tt> no database is chosen.
     438        The name of the template odbc datasource for this function is
     439        <tt>default-mysql-dsn</tt>.
     440      </p>
     441
    411442    <h3>Working with Connections</h3>
    412443    <p>
    … …  
    428459     If a column is a LOB (BLOB or CLOB) then the data is retrieved
    429460      with the ODBC function SQLGetData. This has the consequence that
    430       thr following columns must also be retrieved with SQLGetData,
     461      the following columns must also be retrieved with SQLGetData,
    431462      otherwise an error is raised by the driver.
    432463      Retrieving the following columns via
    … …  
    440471      But be carefull with  the conversion format (1.23 vs. 1,23).
    441472    </p>
     473      <p>
     474     <br><b>[Function]</b><br>
     475      <tt>exec-query*<i>connection sqlstring parameters</i></tt><br>
     476      The same as <tt>exec-query</tt>, but the ODBC parameters are passed as a list.
     477      </p>
    442478    <p>
    443479      <br><b>[Function]</b><br>
    … …  
    447483      list of parameter descriptions.
    448484    </p>
     485     <p>
     486      <br><b>[Function]</b><br>
     487      <tt>exec-update*<i>connection sqlstring parameters</i></tt><br>
     488      The same as <tt>exec-update</tt>, but the ODBC parameters are passed as a list.
     489    </p>
    449490    <p>
    450491      <br><b>[Function]</b><br>
    … …  
    453494      <i>parameters</i> is a list of parameter descriptions.
    454495      Returns the list of out and in-out parameters.
    455     </p>
     496      </p>
     497      <p>
     498      <br><b>[Function]</b><br>
     499      <tt>exec-command*<i>connection sqlstring parameters</i></tt><br>
     500      The same as <tt>exec-command</tt>, but the ODBC parameters are passed as a list.
     501      </p>
     502
    456503    <p>
    457504      <br><b>NOTE</b><br>
    … …  
    460507      One can execute a select statement with <tt>exec-update</tt> and
    461508      <tt>exec-command</tt>. But the command for <tt>exec-query</tt>
    462       must return a resultset.
    463     </p>
     509      must return a resultset. The following functions offers the functionality
     510      of all three functions:
     511    </p>
     512     <br><b>[Function]</b><br>
     513      <tt>exec-sql <i>connection sqlstring &amp;rest parameters</i></tt></br>
     514      Execute an sql statement (select, dml or stored procedure call) on <i>connection</i>
     515      with sql <i>sqlstring</i>. <i>parameters</i> is a list of parameter descriptions.
     516        This function returns three values
     517        <ul>
     518          <li> the number of affected records, i.e. what is normally returend by
     519            <tt>exec-update</tt>.</li>
     520            <li> the list of the resultsets, each resultset is a list of two elements:
     521            the list of rows and a list of the column names.</li>
     522          <li>the list of returned parameters</li>         
     523        </ul>
     524      </p>
     525    <p>
     526      <br><b>[Function]</b><br>
     527          <tt>exec-sql*<i>connection sqlstring parameters</i></tt><br>
     528            The same as <tt>exec-sql</tt>, but the ODBC parameters are passed as a list.
     529    </p>
     530   
    464531    <P>
    465532      <br><b>[Function]</b><br>
Note: See TracChangeset for help on using the changeset viewer.