[Zope-DB] Connect to ms sql server from zope 2.8 on windows server

[M.-A. Lemburg]

Jaroslav Lukesh wrote:
> Dne úterý, 21. června 2005 5:14 ЦЗЛ УЭХК <czl at ueip.ru> napsal(a):
> 
>>I use zope 2.8 on windows server and ms sql server.
>>What kind of database adapter could connect zope to ms sql server?
>>
>>P.S. (How i can conect to ms sql server from zope)
> 
> 
> It is few ways to go.
> 
> One is ODBC (and here are few ways to go too).
> Second is TDS (FreeTDS)
> 
> Both I has some problems with parallel access and with big tables.
> 
> Third and the best one is the native access to MSSQL.
> 
> http://mail.zope.org/pipermail/zope/2004-May/149810.html

... for some definition of "best" (see the comments on the MSSQL
page: http://www.object-craft.com.au/projects/mssql/) ;-)

Just to clarify: The MSSQL module links against the Sybase ct-lib
or FreeTDS (which an effort to create a free implementation of the
same lib).

Getting the Sybase ct-lib to work on Linux is a nightmare and is
not well-supported, which is why the FreeTDS project was started.

However, the original question was "how to connect to MS SQL Server
from Windows": on Windows, ODBC is the easiest to setup and
administer and it is well-supported by all database vendors, with
regular updates, etc.

> Note, that I was sometime mentioned 4k text limit which I could not get rid 
> off - it is limit of MSSQL server via TCP interface, which could be overriden 
> with sql command (right in ZSQL method)
> 
> 	set textsize 65535
> 	<dtml-var sql_delimiter>
> 	select datetype_text from table...

If you do things like this (ie. use SET VARIABLE value), you should
be aware that the setting will in many cases apply to the connection,
not only the cursor you're using to run the query.

As a result, other completely unrelated queries on the same
connection will use the same settings. Things get even more
complicated if you use connection pooling - the results
are intermittent errors that are extremely hard to track
down.

In short: Don't do this :-)

Note that database drivers usually don't have any problem using
bound parameters. The 4k limit usually only refers to the SQL
statement itself - if you use bound parameters, only the parameter
markers count up to that limit, so in most cases, 4k is well
more than enough.

The downside is that Zope's RDB machinery always tries to
quote everything into the SQL statement, so you can only
use bound parameters when talking to the database driver
or connection directly, e.g. via an External Method.

-- 
Marc-Andre Lemburg
eGenix.com

Professional Python Services directly from the Source  (#1, Jul 20 2005)
 >>> Python/Zope Consulting and Support ...        http://www.egenix.com/
 >>> mxODBC.Zope.Database.Adapter ...             http://zope.egenix.com/
 >>> mxODBC, mxDateTime, mxTextTools ...        http://python.egenix.com/
________________________________________________________________________
2005-07-18: Released mxODBC.Zope.DA for Zope 2.8

::: Try mxODBC.Zope.DA for Windows,Linux,Solaris,FreeBSD for free ! ::::