[Zope-DB] Solved: ZODBC DA locking external applications

Julián Muñoz jmunoz at softhome.net
Wed Apr 7 18:47:55 EDT 2004


I had a problem with ZODBC DA, because it locked the database, even when
the connector was closed. So I had to shut down Zope in order to launch
another application who had to access to a ".mdb" file.

In db.py, there is no invocation to the destructors of the connection
object when close() is called. Adding this solves the problem (complete
file attached):


    # Disconnect the database when the zope database connector is closed
    # or updated. (Usefull to free a database which can only be used by one
    # application at the same time)
    def close(self):
        del self.stmt
        del self.connection



Hope this can help someone...

Best regards, Julian
Madrid, Spain

-------------- next part --------------
##############################################################################

# 

# Zope Public License (ZPL) Version 1.0

# -------------------------------------

# 

# Copyright (c) Digital Creations.  All rights reserved.

# 

# This license has been certified as Open Source(tm).

# 

# Redistribution and use in source and binary forms, with or without

# modification, are permitted provided that the following conditions are

# met:

# 

# 1. Redistributions in source code must retain the above copyright

#    notice, this list of conditions, and the following disclaimer.

# 

# 2. Redistributions in binary form must reproduce the above copyright

#    notice, this list of conditions, and the following disclaimer in

#    the documentation and/or other materials provided with the

#    distribution.

# 

# 3. Digital Creations requests that attribution be given to Zope

#    in any manner possible. Zope includes a "Powered by Zope"

#    button that is installed by default. While it is not a license

#    violation to remove this button, it is requested that the

#    attribution remain. A significant investment has been put

#    into Zope, and this effort will continue if the Zope community

#    continues to grow. This is one way to assure that growth.

# 

# 4. All advertising materials and documentation mentioning

#    features derived from or use of this software must display

#    the following acknowledgement:

# 

#      "This product includes software developed by Digital Creations

#      for use in the Z Object Publishing Environment

#      (http://www.zope.org/)."

# 

#    In the event that the product being advertised includes an

#    intact Zope distribution (with copyright and license included)

#    then this clause is waived.

# 

# 5. Names associated with Zope or Digital Creations must not be used to

#    endorse or promote products derived from this software without

#    prior written permission from Digital Creations.

# 

# 6. Modified redistributions of any form whatsoever must retain

#    the following acknowledgment:

# 

#      "This product includes software developed by Digital Creations

#      for use in the Z Object Publishing Environment

#      (http://www.zope.org/)."

# 

#    Intact (re-)distributions of any official Zope release do not

#    require an external acknowledgement.

# 

# 7. Modifications are encouraged but must be packaged separately as

#    patches to official Zope releases.  Distributions that do not

#    clearly separate the patches from the original work must be clearly

#    labeled as unofficial distributions.  Modifications which do not

#    carry the name Zope may be packaged in any form, as long as they

#    conform to all of the clauses above.

# 

# 

# Disclaimer

# 

#   THIS SOFTWARE IS PROVIDED BY DIGITAL CREATIONS ``AS IS'' AND ANY

#   EXPRESSED OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE

#   IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR

#   PURPOSE ARE DISCLAIMED.  IN NO EVENT SHALL DIGITAL CREATIONS OR ITS

#   CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL,

#   SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT

#   LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF

#   USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND

#   ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY,

#   OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT

#   OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF

#   SUCH DAMAGE.

# 

# 

# This software consists of contributions made by Digital Creations and

# many individuals on behalf of Digital Creations.  Specific

# attributions are listed in the accompanying credits file.

# 

###############################################################################

        

from sql import *

from string import join, split, find, strip

import sys

from Shared.DC.ZRDB.THUNK import THUNKED_TM

rc=sys.getrefcount



error=DatabaseError='ODBC Database Error'



def manage_ODBCDataSources(*args):

    r=[]

    status, name, desc = SQLDataSources(SQL_FETCH_FIRST)

    while status==SQL_SUCCESS:

        r.append((name, desc))

        status, name, desc = SQLDataSources(SQL_FETCH_NEXT)

    return r



defs={

    SQL_BIGINT: 'i',

    SQL_BINARY: 't',

    SQL_BIT: 'i',

    SQL_CHAR: 't',

    SQL_DATE: 'd',

    SQL_DECIMAL: 'N',

    SQL_DOUBLE: 'n',

    SQL_FLOAT: 'n',

    SQL_INTEGER: 'i',

    SQL_LONGVARBINARY: 't',

    SQL_LONGVARCHAR: 't',

    SQL_NUMERIC: 'N',

    SQL_REAL: 'n',

    SQL_SMALLINT: 'i',

    SQL_TIME: 't',

    SQL_TIMESTAMP: 'd',

    SQL_TINYINT: 'i',

    SQL_VARBINARY: 't',

    SQL_VARCHAR: 't',

    }

tpnames={

    SQL_BIGINT: 'BIGINT',

    SQL_BINARY: 'BINARY',

    SQL_BIT: 'BIT',

    SQL_CHAR: 'CHAR',

    SQL_DATE: 'DATE',

    SQL_DECIMAL: 'DECIMAL',

    SQL_DOUBLE: 'DOUBLE',

    SQL_FLOAT: 'FLOAT',

    SQL_INTEGER: 'INTEGER',

    SQL_LONGVARBINARY: 'LONGVARBINARY',

    SQL_LONGVARCHAR: 'LONGVARCHAR',

    SQL_NUMERIC: 'NUMERIC',

    SQL_REAL: 'REAL',

    SQL_SMALLINT: 'SMALLINT',

    SQL_TIME: 'TIME',

    SQL_TIMESTAMP: 'TIMESTAMP',

    SQL_TINYINT: 'TINYINT',

    SQL_VARBINARY: 'VARBINARY',

    SQL_VARCHAR: 'VARCHAR',

    }



binary_types=SQL_BINARY, SQL_LONGVARBINARY,  SQL_VARBINARY



class DB(THUNKED_TM):



    def __init__(self, connection_string=''):

        self.connection_string=connection_string

        d=split(connection_string)

        if len(d) < 1 or len(d) > 3:

            raise 'Invalid Connection String', d

        while len(d) < 3: d.append('')

        self.connection=c=SQLConnection(d[0],d[1],d[2])

        try: SQLSetConnectOption(c,SQL_AUTOCOMMIT,SQL_AUTOCOMMIT_OFF)

        except: pass # Oh well

        self.stmt=c()



    # Disconnect the database when the zope database connector is closed

    # or updated. (Usefull to free a database which can only be used by one

    # application at the same time)

    def close(self):

        del self.stmt

        del self.connection

        

    def table_info(self):

        SQLTables(self.stmt, None, None, None, 'TABLE')

        r=[]

        for row in split(self.query(), '\n')[2:-1]:

            q, owner, name, type, remarks = tuple(split(row,'\t'))

            if owner: owner="(owned by %s) " % owner

            owner=owner+remarks

            if owner: owner=', '+owner

            r.append((name, owner))

            

        return r



    def tables(self, Qualifier=None, Owner=None, Name=None, Type=None, rdb=1):

        SQLTables(self.stmt, Qualifier, Owner, Name, Type)

        return self.query(rdb=rdb)



    def query(self, src=None, max_rows=99999999, rdb=1):

        stmt=self.stmt

        self._register()

        try:

            if src is not None:

                r=filter(strip,split(src,'\0'))

                if not r: raise ValueError, 'null sql'

                if len(r) > 1:

                    res=None

                    for s in r:

                        SQLExecDirect(stmt,s)

                        status, ncol = SQLNumResultCols(stmt)

                        if ncol:

                            if res is not None:

                                raise ValueError, (

                                    'multiple selects are not allowed')

                            res=self.query(None, max_rows, rdb)

                    if res==None:

                        if rdb: res="x\n8s\n"

                        else: res=()

                    return res



                SQLExecDirect(stmt,src)



        except error, v:

            state, native, mess = v

            raise error, "%s (%s)" % (mess, state)



        status, ncol = SQLNumResultCols(stmt)

        if ncol==0:

            if rdb: return "x\n8s\n"

            else: return ()

        r=[]

        row=['']*ncol

        names=['']*ncol

        rdbdefs=['']*ncol

        indexes=range(ncol)

        binary_flags=[0]*ncol

        for i in range(ncol):

            status, name, tp, prec, scale, nullable=SQLDescribeCol(stmt,i+1)

            names[i]=name

            if prec < 1: prec=8

            binary_flags[i]=tp in binary_types

            if defs.has_key(tp): tp=defs[tp]

            else: tp='t'

            if tp=='N' and scale==0: tp='i'

            rdbdefs[i]=tp

            row[i]="%s%s" % (prec,tp)



        if rdb:

            r.append(join(names,'\t'))

            r.append(join(row,'\t'))



        status=SQLFetch(stmt)

        while status==SQL_SUCCESS:

            for i in range(ncol):

                v=SQLGetString(stmt,i+1,binary_flags[i])

                if rdbdefs[i]=='t':

                    if find(v,'\\') >= 0: v=join(split(v,'\\'),'\\\\')

                    if find(v,'\n') >= 0: v=join(split(v,'\n'),'\\n')

                    if find(v,'\t') >= 0: v=join(split(v,'\t'),'\\t')

                row[i]=v

            if rdb: rd=join(row,'\t')

            else:

                rd={}

                for i in indexes: rd[names[i]]=row[i]

            r.append(rd)

            status=SQLFetch(stmt)



        SQLFreeStmt(stmt, SQL_CLOSE);

        if rdb: r=join(r,'\n')+'\n'

        return r



    def columns(self, table_name):

        stmt=self.stmt

        try: SQLExecDirect(stmt, 'select * from %s where 1=2' % table_name)

        except error, v:

            state, native, mess = v

            raise error, "%s (%s)" % (mess, state)



        status, ncol = SQLNumResultCols(stmt)

        r=[]

        standard_type=tpnames.has_key

        for i in range(ncol):

            status, name, tp, prec, scale, nullable=SQLDescribeCol(stmt,i+1)

            if standard_type(tp): tp=tpnames[tp]

            else: tp="Non-standard type %s" % tp

            r.append({'Name': name, 'Type': tp, 'Precision': prec,

                      'Scale': scale,

                      'Nullable': nullable and 'with Null' or ''})



        SQLFreeStmt(stmt, SQL_CLOSE);

        return r



    def _begin(self):

        pass



    def _finish(self):

        SQLTransact(self.connection, SQL_COMMIT)



    def _abort(self):

        SQLTransact(self.connection, SQL_ROLLBACK)





if __name__=='__main__':

    #print manage_ODBCDataSources()

    db=DB('test')

    #print db.table_info()

    if len(sys.argv)==2: print db.query(sys.argv[1])

    else: print db.tables(rdb=0)

    #print db.columns('f')



      



More information about the Zope-DB mailing list