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 Connection
dialog 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;
MisstaffID |
FirstName |
LastName |
Title |
EmailName |
Extension |
Address |
---|---|---|---|---|---|---|
ID1 |
Allen |
Higgins |
Mr. |
ahigginsATucd.ie |
ext4775 |
Quinn School |
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