ExtractingDataFromAccess

Before doing any database work in Plone and Zope, make sure you complete Lessons 11 and 12 of the ZopeTutorial?. The basic principles are the same and it familiarises you with the way of getting things working in Zope. You'll also need a simple database file with table and some data to test your installation.

Install the New Zope ODBC Database Adapter ZODBCDA into the products directory. The ZODBCDA is a Zope Open DataBase Connectivity Data Adapter. We installed the binary distribution available from Zope.org

Unpack the distribution into the following location:

    /Zope/lib/python/Products/ZODBCDA

You must also install the latest version of Mark Hammond's Python Win32 Extensions atSource Forge as this version of the ZODBCDA requires them.

Restart Zope.

Got to the ZMI from Plone, the ZMI page should indicate that you are here --> "Plone Site at /". I call this the / Contents location from within Plone.

See the Add button at the top right-hand side of the / Contents location? Add a Z ODBC Database Connection, this brings you to the Add ODBC Connectiondialog below:

    Id                    ODBC_database_connection    <-- change to a meaningful name
    Title                 Z ODBC Database Connection  <-- change to a meaningful description
    Connection String     misstaff                    <-- this is the DSN of your database
    Connect Immediately?  check yes

For Connection String, enter the name of the database you want to connect with. This is the same Data Source Name (DSN) tnat you should have already entered in the System DSN on Windows; on my installation you register the system DSN via the ODBC Data Source Administrator control panel in Administrative Tools.

Test your connection using the Test tab, e.g. using the following simple SQL (in this example I'm selecting all elements from the table named misstaff:

    SELECT * from mis-staff-table
    ORDER BY "Last Name"

The result of your test displays within the ZMI and should look something like;

You now need to demonstrate that data can be pulled and displayed in Plone. For this example I created a new folder in the site root called Staff.

From inside Staff add a Z SQL method from the drop down menu.

Give this method a name (ID) and description (title) and provide a query template (e.g. see the simple SQL you used in the earlier connection test.

Click Add and Test to confirm the report is what you require. The Staff folder should now hold a copy of your new, working Z SQL method

You now need to create a ZPT "Zope Page Template" to display the results of the Z SQL method created previously.

From inside Staff add a Z Search Interface from the drop down menu. The Add Search Interface dialog appears. This is a handy tool for automatically writing the TAL scripts for the new ZPT.

Complete the appropriate fields; select the Z SQL method you created previously, provide a report name (ID) and description (Title). Choos Generate Page Templates and click Add.

The new ZPT (page template) is saved into the Staff directory. Select the template and click on the Test tab to verify that it works correctly. The result of this test should be very similar to the earlier test of the database connection. An example similar to this is available at Plone.org

Did you notice from the test that the page does not have the Plone look and feel? Seems to only be one way of doing this in Plone, via the ZMI, rename the ZPT to index_html and ensure that your generated ZPT script is copied between the div metal tags, you may also need to change the tag pair containing the tal define from body to span ordiv