<<TableOfContents>>

= SQL =

== PURPOSE ==
This macro was developed to query either an Oracle of MySQL database.

== Download & Release Notes ==
|| Download || Release Version || Moin Version || Release Notes ||
|| [[attachment:SQL.py]] ||  || 1.3 ||  ||

== CALLING SEQUENCE ==
{{{
[[SQL(host|user|password|database|type|query)]]
}}}

== KEYWORD PARAMETERS ==
{{{
host:              system hostname were the database server can be contacted
user:              user name
password:          password - NOTE: Protect page with ACL if this password user/password supplies more
                                    than read only connection to the database.
database:          database name on the server
type:              right now can only be one of 'oracle' or 'mysql'
query:             the SELECT SQL query
}}}

== INSTALLATION ==
 * Oracle prerequesites:
  * Oracle client libraries (http://www.oracle.com)
  * set '_oracle_home' variable in script to ORACLE_HOME environment variable path
  * cx_Oracle - http://www.cxtools.net/default.aspx?nav=cxorlb
          you have to compile it yourself, or to use the pre-compiled
          binaries you have to have control over the environment
          variables within the web server environment.  I don't
          have control over the server, but I think all you need to
          do is set LD_LIBRARY_PATH to $ORACLE_HOME/lib.

          When compiling cx_Oracle:

          1. set the environment variable FORCE_RPATH to anything
          2. {{{python setup.py install}}}

             cx_Oracle needs to find a couple of libraries and the
             only way it works is for cx_Oracle to be compiled with
             the run time paths to the libraries it needs.  The
             compilation flags to include the run time paths are
             triggered by the FORCE_RPATH environment variable.
 * MySQL:
        I don't know what the requirements are here, but I think 
        everything is unchanged from MySQLOutput.py 
 


== RESTRICTIONS ==

== EXAMPLES ==

=== Query Oracle for list of tables in database ===
{{{
[[SQL(oraprod1|username|password|prod1|oracle|select * from user_tables)]]
}}}

== Copyright ==
TimCera - <<MailTo(timcera AT earthlink DOT net)>>

== License ==

= DISCUSSION =

I have installed this macro with the Oracle driver for my intranet wiki which runs on Windows and Moin V1.5.8. Everything works fine. Thank you very much for this macro. I think this is a very useful extension.

My SQL queries have become fairly complex though, so I started to wrap them into site-specific macros which then call your SQL macro. That was a nice way for me get some insights on macros.

Maybe it would be also a good idea to convert the macro to a parser, because then the long SQL queries don't get so ugly in the source code. I will try to figure out how to do this.

Oh, just one more suggestion: I have modified the existing SQL macro to also display the column headers and not just the results. This is done by the following code in the execute method just before iterating the retrieved columns:
{{{#!python
    # print column headers
    h = c.description
    result += formatter.table_row(True)
    for i in range(0, len(h)):
        result += formatter.table_cell(True)
        result += formatter.strong(True)
        result += unicode(str(h[i][0]), 'ISO-8859-1') 
        result += formatter.strong(False)
        result += formatter.table_cell(False)
    result += formatter.table_row(False)
}}}

-- AnkeHeinrich <<DateTime(2007-11-04T09:24:00-0500)>>

Hi
 
may be use for most of that the string substitutions.
e.g. result = "%s %s %d:%d" % ("Hello", "Anke", 18, 11)

-- ReimarBauer <<DateTime(2007-11-04T18:12:26+0100)>>

 Hi Reimar, I'm not sure if I understand your comment. I have used string substitutions before but in the code above I was just following the existing coding style used in the SQL macro. Are you suggesting to optimize it similar to this?
 {{{#!python
    # print column headers
    h = c.description
    result += formatter.table_row(True)
    for i in range(0, len(h)):
        result += "%s%s%s%s%s" % (
            formatter.table_cell(True),
            formatter.strong(True),
            unicode(str(h[i][0]), 'ISO-8859-1'),
            formatter.strong(False),
            formatter.table_cell(False)
        )
    formatter.table_row(False)
}}}

 Doesn't look too convincing to me in terms of readability. -- AnkeHeinrich <<DateTime(2007-11-04T14:00:00-0500)>>

 ''Of course you could use named substitution variables too. e.g. ''{{{
result = "%(greeting)s %(name)s %(hour)d:%(minute)d" % {'greeting': "Hello", 
                                                        'name': "Anke", 
                                                        'hour': 18, 
                                                        'minute':11}
}}} -- ReimarBauer <<DateTime(2007-11-04T20:47:10+0100)>>

= HISTORY =
Based heavily on MySQLOutput.py by Sergio

= BUG LIST =

<<FullSearch(t:MacroMarket/SQL/ r:CategoryMoinMoinBug\b)>>

Think of a good WikiName for the bug. It will be used to create a subpage under this page. Enter it here:
  <<NewPage(BugTemplate, Create, MacroMarket/SQL)>>


= OTHER DATABASE PLUGINS =

= MODIFICATION HISTORY =