SQL Connections

Zope ADO Database Adapter

Zope is an object database and stores transactional data in the /var/Data.fs file. But you can also configure a database adapter to allow Zope/Plone to talk to relational databases like SQL Server and MySQL.

Zope ADO Database Adapter

Zadoa is a product installed by default with Enfold Server that provides an ADO adaptor to relational databases. This allows you to connect your Enfold Server to external databases such as SQL Server, Access or any server that supports ActiveX Data Objects. More information on ADO is available at: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/ado270/htm/dasdkadooverview.asp

This adapter allows you to create connections to relational databases. It is then up to your site administrator or developer to create the appropriate methods to allow users to interact with the content.

Zadoa is not a ODBC connector. It is an ADO connector. As such, it can connect to ODBC, but that's only one of the things it can do. It can also connect directly to an ADO source without having to go through ODBC. However, using ODBC is recommended because it provides better error handling.

Creating a database connection

Suppose you have a SQL database on Windows. Create an ODBC connection for it. Go to Start > Settings > Control Panel > Administrative Tools > Data Sources (ODBC).

After doing this, create a connection within the Zope Management Interface at the root of your site.

From the Add drop down (the dropdown list from the top right of ZMI's root directory), select Z ADO Database Connection.
Enter a connection string to a server.
Click Add.

An example of a connection string that uses a dsn is dsn=dsnname;User ID=username;Password=secret

Another example of a connection string is:

Provider=sqloledb;Data Source=server_name.mydomain.com;Initial Catalog=pubs;User Id=username;Password=secret

To test that it works, you can access the database connection and click on the Test tab in the ZMI. This should give you a browsable view of the data source that you have connected to. If not you can click on the Properties tab to alter your database connection string.

ZSQLMethods

After you set up a database connection, the next step is creating methods within Zope to communicate to the database connection. In Zope these are called ZSQLMethods. They are created and edited by site administrators or developers. For more information on ZSQL and how they can be used within Plone and Zope, look at the following document: http://www.plope.com/Books/2_7Edition/RelationalDatabases.stx

Go to ZMI, and select Z SQL Method from the ADD dropdown list. At this point, you should see the ADO connection you added. From this interface you can write and test your queries,

Details of support

The zadoa module supports the following:

Data types:

Integer
CHAR/VARCHAR
DateTime
Boolean
BLOB
Float
Decimal

Transaction support Zadoa works fully with transactional databases and nontransactional databases.

Known Issues with Zadoa

Aliases for Count(*)

When you make a query from a SQL command like:

select count(*) from item

you must create an alias for "count(*)" . Otherwise an error will occur.

Workaround: 'select count(*) as count from table' .

Conflict with Savepoint

Neither SQL Server .NET Data Provider nor the OLE DB Provider for SQL Server support nested transactions. Add-on products which use ZODB's savepoint() feature might conflict with using the ADO adapter if they are both called within the same transaction. The symptom for this failure is that you will receive an exception saying that no new transactions can be started.