The flag preexecute_autoincrement_sequences was including the identity
column automatically. I set it to False in my dialect and the None id
value was removed from the INSERT statement.
Thanks for the info.
On Tuesday, January 13, 2015 at 12:57:56 PM UTC-8, Lycovian wrote:
>
> For a custom dialect I am working on I need to rewrite the INSERT
> statement that SA generates sometimes. In this particularly case if a
> column is defined as an identity column and the insert statement doesn't
> specify a value for the identity field I need to remove the reference to
> the column and its associated None value that is passed to ODBC for the
> INSERT statement. This is because of an issue (bug?) with the vendors ODBC
> implementation where if you attempt to insert a None value into an Identity
> column it throws an error.
>
> For example given this table def:
> class Foo(Base):
> __tablename__ = 'foo'
> id = Column(sqlalchemy.Integer, primary_key=True, autoincrement=True)
> txt = Column(sqlalchemy.String(50), nullable=False)
>
>
> Which generates this DDL:
> 2015-01-13 12:31:47,631 INFO sqlalchemy.engine.base.Engine
> CREATE TABLE foo (
> id INTEGER NOT NULL GENERATED BY DEFAULT AS IDENTITY (START WITH 1
> INCREMENT BY 1 NO CYCLE),
> txt VARCHAR(50) NOT NULL,
> PRIMARY KEY (id)
> )
>
> If I attempt to do an ODBC parameterized INSERT using SA, it generates
> this SQL which in this specific case is invalid for the database:
> rec = Foo(txt='c')
> session.add(rec)
> session.commit()
>
> SA generates this SQL and passes it through to ODBC:
> INSERT INTO foo (id, txt) VALUES (None, 'c')
>
> This unfortunately is invalid SQL for this database if the id column is an
> Identity column and pyodbc throws an error:
> sqlalchemy.exc.DBAPIError: (pyodbc.Error) ('HY000', '[HY000]
> [Teradata][ODBC Teradata Driver][Teradata Database] The source parcel
> length does not match data that was defined. (-2673) (SQLExecDirectW)')
> [SQL: u'INSERT INTO foo (id, txt) VALUES (?, ?)'] [parameters: (None, 'c')]
>
> So only in this case I am looking for a way to rewrite the above query and
> remove the reference to the id column. For example if the id column is an
> Identity column the parameterized query that is sent to pyodbc should be:
> INSERT INTO foo (txt) VALUES ('c')
>
> Can someone point me to any examples to rewrite my query to remove the
> reference to the id column (and it's None parameter) at the field level for
> my custom dialect?
>
>
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
