Extracting Data from Microsoft SQL using ZPT

by Alan Runyan — last modified Dec 30, 2008 03:01 PM

The following should hopefully advise on how to extract data from MS SQL into a plone site.

Please be aware this is the way i do it, although it doesn't mean its the correct way, however it works.

Ensure you have installed ZODBCDA into products, there are also other adapters out there and apparently much better.

Create a table in MS SQL
Create an appropriate DSN in (ODBC data sources) within control panel pointing to a table created

As an example i have a table named isstaff with the following structure

surname
firstname
jobtitle
email
extension
area

From the drop down menu in the ZMI select Z ODBC database connection, give it a title and set the connection string appropriately. Click the test tab to ensure it works.

Right thats the hard part done.

Create a folder in Plone and from the drop down menu (using the ZMI) select Z SQL method. Add a title and for the connection id ensure it points to the Z ODBC connection you created and in the box enter a sql query, such as -

select * from isstaff

  order by surname

Click the test tab again, to ensure the data is being pulled and displayed.

There are now two ways to ensure the data is displayed in a ZPT. Either do it manually (and if you can, no point in reading this is there?) or for a quick fix from the drop down menu select Z Search Interface

Compete the appropriate fields -

Select one or more searchable objects - choose the Z SQL method you created

Complete the other fields and choose Generate Page Templates

<html>
  <body tal:define="results  here/allstaff;
                    start request/start|python:0;
                    batch python:modules['ZTUtils'].Batch(results, 
                                                          size=20, 
                                                          start=start);
                    previous python:batch.previous;
                    next python:batch.next">
  <p>
    <a tal:condition="previous"
       tal:attributes="href string:${request/URL0}?start:int=${previous/first}"
       href="previous_url">previous <span tal:replace="previous/length">20</span> results</a>
    <a tal:condition="next"
       tal:attributes="href string:${request/URL0}?start:int=${next/first}"
       href="next_url">next <span tal:replace="next/length">20</span> results</a>
  </p>
  <table border>
        <tr>
          <th>Id</th>
          <th>Surname</th>
          <th>Firstname</th>
          <th>Jobtitle</th>
          <th>Email</th>
          <th>Extension</th>
          <th>Area</th>
        </tr>

  <div tal:repeat="result batch" >

         <tr>
          <td><span tal:replace="result/id">id goes here</span></td>
          <td><span tal:replace="result/surname">surname goes here</span></td>
          <td><span tal:replace="result/firstname">firstname goes here</span></td>
          <td><span tal:replace="result/jobtitle">jobtitle goes here</span></td>
          <td><span tal:replace="result/email">email goes here</span></td>
          <td><span tal:replace="result/extension">extension goes here</span></td>
          <td><span tal:replace="result/area">area goes here</span></td>
        </tr>
  </div>
      </table>
  <p>
    <a tal:condition="previous"
       tal:attributes="href string:${request/URL0}?start:int=${previous/first}"
       href="previous_url">previous <span tal:replace="previous/length">20</span> results</a>
    <a tal:condition="next"
       tal:attributes="href string:${request/URL0}?start:int=${next/first}"
       href="next_url">next <span tal:replace="next/length">20</span> results</a>
  </p>
  </body>
</html>

Click on the title you chose for the report id then the test tab and the results should now be displayed (as above), even does a nice batching example for you. As you can see the page does not yet have the plone look. To ensure it does copy index_html from portal_skins/plone_templates and paste in the appropriate content from above. Just be aware of the body part of the code if you get any errors.

You need to modify it so it looks similar to (bit in bold is the bit to watch out for)-

<?xml version="1.0" encoding="iso-8859-1"?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
"http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en-US"
      lang="en-US"
      metal:use-macro="here/main_template/macros/master">
<head>
<title>Untitled Document</title>
</head>
<body>
<div metal:fill-slot="main">
  <span tal:define="results here/stplrc_list;
                    startat python:path(request/start | nothing) or 0;
                    batch python:modules['ZTUtils'].Batch(results, size=10, 
   start=int(startat));  
                     previous python:batch.previous;
                     next python:batch.next">
<h1>St. Peter's Learning Resource Centre Staff</h1>

to see it in action visit

Staff Contacts
and a more complex example ignoring nulls etc

Warning

Extracting Data from Microsoft SQL using ZPT

Contribute