On 07/08/2011 09:32 AM, Philippe Makowski wrote:
> Hi,
>
> I'll try to work on the ticket:
>
> http://www.sqlalchemy.org/trac/ticket/2125
>
> I'm not a sqlalchemy user, but I know well Firebird and I'm helping to
> support the pyfirebirdslq driver for Firebird
>
> I have one question about the kinterbasdb dialect inside sqlalchemy :
> I saw that you are using commit and rollback retain
> is there any good reason for that ?
>
> from the Firebird point of vue, it is not a very good way to work with
> transaction
> is that only because after a commit or rollback sqlalchemy need a
> transaction handle ? or is there any other reason for this choice ?
I did a bit of testing using my own application and so far found one
place where I run into the following exception if I change
"sa/dialect/firebird/base.py" to use "False" for "do_commit" and
"do_rollback".

Traceback (most recent call last):
File "saRETAINTesting.py", line 42, in <module>
'countryid')
File "/home/wbruhin/devProjectsU/twcb/Program/utils.py", line 425, in
CreateSingleLangTableSA
for item in dbItemsMultiLang:
File
"/usr/local/lib/python2.6/dist-packages/SQLAlchemy-0.7.1-py2.6-linux-x86_64.egg/sqlalchemy/engine/base.py",
line 2790, in __iter__
row = self.fetchone()
File
"/usr/local/lib/python2.6/dist-packages/SQLAlchemy-0.7.1-py2.6-linux-x86_64.egg/sqlalchemy/engine/base.py",
line 2981, in fetchone
self.cursor, self.context)
File
"/usr/local/lib/python2.6/dist-packages/SQLAlchemy-0.7.1-py2.6-linux-x86_64.egg/sqlalchemy/engine/base.py",
line 2972, in fetchone
row = self._fetchone_impl()
File
"/usr/local/lib/python2.6/dist-packages/SQLAlchemy-0.7.1-py2.6-linux-x86_64.egg/sqlalchemy/engine/base.py",
line 2887, in _fetchone_impl
return self.cursor.fetchone()
sqlalchemy.exc.ProgrammingError: (ProgrammingError) (-504, 'fetch: \n
Dynamic SQL Error\n SQL error code = -504\n Invalid cursor reference\n
Cursor is not open') None None

The code in "CreateSingleLangTableSA" is this:

def CreateSingleLangTableSA(engine, MultiLang, SingleLang, NewLangId,
ItemKey):
"""Create/Refresh single language table with language records for
NewLangId.
Using SA engine directly
MultiLang = table containing all languages
SingleLang = table containing single language (language used by user)
NewLangId = primary key of new language to use
ItemKey = string containing field name for primary key
"""
# get all english records for multi lang table
selML = 'select %s from %s where fk_langid=%i' % (ItemKey,
MultiLang, 1)
conn = engine.connect()

dbItemsMultiLang = conn.execute(selML)
fields, fieldCount = GetDbColNames(engine, MultiLang)

qMarks = '?,'
for x in range(fieldCount):
qMarks += '?,'

qMarks = qMarks.rstrip(',')

selMLdata = "select %s from %s" % (fields, MultiLang)
updSLdata = "update or insert into %s (%s) values (%s)" %
(SingleLang, fields, qMarks)

for item in dbItemsMultiLang: # line 425 as shown in traceback
where = ' where %s=%i and %s=%i' % (ItemKey, item[0],
'fk_langid', NewLangId)
dbItemNewLang = conn.execute(selMLdata + where).fetchone()


if dbItemNewLang == None:
# put english into single lang table
where = ' where %s=%i and %s=%i' % (ItemKey, item[0],
'fk_langid', 1)
dbItemEnglish = conn.execute(selMLdata + where).fetchone()
conn.execute(updSLdata, dbItemEnglish.values())

else:
# put NewLangID info into single lang table
conn.execute(updSLdata, dbItemNewLang.values())

conn.close()


If I set "echo=True" I get this output:
SQLAlchemy version: 0.7.1
/home/wbruhin/devProjectsU/twcb/Data/twcb.fdb
2011-07-13 12:49:00,540 INFO sqlalchemy.engine.base.Engine select
countryid from country_lm where fk_langid=1
2011-07-13 12:49:00,540 INFO sqlalchemy.engine.base.Engine ()
2011-07-13 12:49:00,583 INFO sqlalchemy.engine.base.Engine select * from
country_lm
2011-07-13 12:49:00,583 INFO sqlalchemy.engine.base.Engine ()
2011-07-13 12:49:00,608 INFO sqlalchemy.engine.base.Engine select
COUNTRYID, NAME, CREATED, UPDATED, FK_LANGID, SHORTNAME, CENTRALKEY, ID
from country_lm where countryid=1 and fk_langid=1
2011-07-13 12:49:00,609 INFO sqlalchemy.engine.base.Engine ()
2011-07-13 12:49:00,612 INFO sqlalchemy.engine.base.Engine update or
insert into country_ls (COUNTRYID, NAME, CREATED, UPDATED, FK_LANGID,
SHORTNAME, CENTRALKEY, ID) values (?,?,?,?,?,?,?,?)
2011-07-13 12:49:00,612 INFO sqlalchemy.engine.base.Engine (1,
u'Afghanistan', datetime.date(2003, 10, 1), datetime.date(2005, 8, 23),
1, None, 1, 1)
2011-07-13 12:49:00,616 INFO sqlalchemy.engine.base.Engine COMMIT
2011-07-13 12:49:00,641 INFO sqlalchemy.engine.base.Engine ROLLBACK


Is there a better/different way to do this, so not to run into the
"retaining" issue on FB?

Werner