SQL

PURPOSE

This macro was developed to query either an Oracle of MySQL database.

Download & Release Notes

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:
    • set the environment variable FORCE_RPATH to anything

    • 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 - <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:

   1     # print column headers
   2     h = c.description
   3     result += formatter.table_row(True)
   4     for i in range(0, len(h)):
   5         result += formatter.table_cell(True)
   6         result += formatter.strong(True)
   7         result += unicode(str(h[i][0]), 'ISO-8859-1') 
   8         result += formatter.strong(False)
   9         result += formatter.table_cell(False)
  10     result += formatter.table_row(False)

-- AnkeHeinrich 2007-11-04 14:24:00

Hi

may be use for most of that the string substitutions. e.g. result = "%s %s %d:%d" % ("Hello", "Anke", 18, 11)

-- ReimarBauer 2007-11-04 17:12:26

• 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?

     1     # print column headers
     2     h = c.description
     3     result += formatter.table_row(True)
     4     for i in range(0, len(h)):
     5         result += "%s%s%s%s%s" % (
     6             formatter.table_cell(True),
     7             formatter.strong(True),
     8             unicode(str(h[i][0]), 'ISO-8859-1'),
     9             formatter.strong(False),
    10             formatter.table_cell(False)
    11         )
    12     formatter.table_row(False)
  

  Doesn't look too convincing to me in terms of readability. -- AnkeHeinrich 2007-11-04 19:00:00

  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 2007-11-04 19:47:10

HISTORY

Based heavily on MySQLOutput.py by Sergio

BUG LIST

Think of a good WikiName for the bug. It will be used to create a subpage under this page. Enter it here:

OTHER DATABASE PLUGINS

MODIFICATION HISTORY