[PATCH FOR] compiled inserts broken if dbfuncs are bound at compile time

[Former user]

is all against sqlalchemy r1343. sqlite 3.3.5, MySQLdb 1.2.1g3, python2.4, gcc 4.0.1

The repro case is for sqlite. It outputs::

    self.execute(statement, parameters, connection=connection, cursor=cursor, return_raw=True)
  File "/home/robin/devel/sqlalchemy/lib/sqlalchemy/engine.py", line 692, in execute
    self._execute(cursor, statement, parameters)
  File "/home/robin/devel/sqlalchemy/lib/sqlalchemy/engine.py", line 712, in _execute
    raise exceptions.SQLError(statement, parameters, e)
sqlalchemy.exceptions.SQLError: (ProgrammingError) Incorrect number of bindings supplied. The current statement uses 2, and there are 3 supplied. 'INSERT INTO t1 (name, familyname, registered) VALUES (?, ?, current_timestamp)' ['simpson', '0000-00-00 00:00:00']('bart',)

After the patch I get::

(1, u'bart', u'simpson', datetime.datetime(2006, 4, 27, 2, 23, 5))

Also tried this on MySQL 4.1 with NOW() and behaviour is similar before & after patch.

The patch prevents SQLEngine._process_defaults from filling in column defaults for parameters that have been supplied at compile time. It's not quite right, objectstore.PKTest.testprimarykey fails, and it looks like something subtle is going on with multivalue, or single value, PK's that use Sequence. There is coupling between ansisql and engine that I just don't understand, the single additional test failure I got with this patch said::

objectstore.PKTest.testprimarykey

    SQLError: (IntegrityError) multi.multi_id may not be NULL 'INSERT INTO multi 
    (multi_rev, name, value) VALUES (?, ?, ?)' ['entry1', 'this is entry 1'](2,)

(can't find the attatchment button, I'll add the patch & repro as comment)

[Former user]

this patch fixes this issue, caveat: handling of multi-value, sequenced pks may need some work

[Former user]

repro case for sqlite 3.3.5

[Mike Bayer]

testprimarykey doesnt work on SQLIte since SQLite doesnt have very good support for composite primary keys. thats a separate issue, which is #159.

part of this issue is actually #120. youll notice if you change your function to a straight string:

insert=t1.insert().compile(e, dict(familyname='simpson', registered="1960-09-15 12:30:10"))

it works.

howver, you arent realy supposed to be using "compile" that way, youre supposed to use the "values" clause of the insert. and in that case, the default is clobbering the value:

insert=t1.insert(values=dict(familyname='simpson', registered="1960-09-15 12:30:10"))

however, your patch does not fix this problem either. so i will create a new ticket for the VALUES clause being broken, its #169