Rick Morrison wrote:
> Is the column you're having issues with really a VARCHAR, or is the
> message misleading? How did you create the table, pre-existing or via
> SQLAlchemy? Can you show the schema and the code you're trying to access
> it with?
It's a preexisting table. The column in the DB is of type BINARY. The
type used in the python code is Binary(). I'm trying to insert a Binary
value from a python program into a MS SQL database BINARY column using
SQLAlchemy.
Below is the schema from the table as dumped via the SQL Enterprise
Manager, the declarative SQLAlchemy class used and the sample code used,
plus the traceback:
------------------ MS SQL TABLE -----------------------------
CREATE TABLE [SY00500] (
[GLPOSTDT] [datetime] NOT NULL ,
[BCHSOURC] [char] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[BACHNUMB] [char] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[SERIES] [smallint] NOT NULL ,
[MKDTOPST] [tinyint] NOT NULL ,
[NUMOFTRX] [int] NOT NULL ,
[RECPSTGS] [smallint] NOT NULL ,
[DELBACH] [tinyint] NOT NULL ,
[MSCBDINC] [smallint] NOT NULL ,
[BACHFREQ] [smallint] NOT NULL ,
[RCLPSTDT] [datetime] NOT NULL ,
[NOFPSTGS] [smallint] NOT NULL ,
[BCHCOMNT] [char] (61) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[BRKDNALL] [tinyint] NOT NULL ,
[CHKSPRTD] [tinyint] NOT NULL ,
[RVRSBACH] [tinyint] NOT NULL ,
[USERID] [char] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[CHEKBKID] [char] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[BCHTOTAL] [numeric](19, 5) NOT NULL ,
[BCHEMSG1] [binary] (4) NOT NULL ,
[BCHEMSG2] [binary] (4) NOT NULL ,
[BACHDATE] [datetime] NOT NULL ,
[BCHSTRG1] [char] (21) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[BCHSTRG2] [char] (21) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[POSTTOGL] [tinyint] NOT NULL ,
[MODIFDT] [datetime] NOT NULL ,
[CREATDDT] [datetime] NOT NULL ,
[NOTEINDX] [numeric](19, 5) NOT NULL ,
[CURNCYID] [char] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[BCHSTTUS] [smallint] NOT NULL ,
[CNTRLTRX] [int] NOT NULL ,
[CNTRLTOT] [numeric](19, 5) NOT NULL ,
[PETRXCNT] [smallint] NOT NULL ,
[APPROVL] [tinyint] NOT NULL ,
[APPRVLDT] [datetime] NOT NULL ,
[APRVLUSERID] [char] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
,
[ORIGIN] [smallint] NOT NULL ,
[ERRSTATE] [int] NOT NULL ,
[GLBCHVAL] [binary] (4) NOT NULL ,
[Computer_Check_Doc_Date] [datetime] NOT NULL ,
[Sort_Checks_By] [smallint] NOT NULL ,
[SEPRMTNC] [tinyint] NOT NULL ,
[REPRNTED] [smallint] NOT NULL ,
[CHKFRMTS] [smallint] NOT NULL ,
[TRXSORCE] [char] (13) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[PmtMethod] [smallint] NOT NULL ,
[EFTFileFormat] [smallint] NOT NULL ,
[Workflow_Approval_Status] [smallint] NOT NULL ,
[Workflow_Priority] [smallint] NOT NULL ,
[TIME1] [datetime] NOT NULL ,
[DEX_ROW_ID] [int] IDENTITY (1, 1) NOT NULL ,
CONSTRAINT [PKSY00500] PRIMARY KEY NONCLUSTERED
(
[BCHSOURC],
[BACHNUMB]
) ON [PRIMARY] ,
CHECK (datepart(hour,[APPRVLDT]) = 0 and datepart(minute,[APPRVLDT]) =
0 and datepart(second,[APPRVLDT]) = 0 and
datepart(millisecond,[APPRVLDT]) = 0),
CHECK (datepart(hour,[BACHDATE]) = 0 and datepart(minute,[BACHDATE]) =
0 and datepart(second,[BACHDATE]) = 0 and
datepart(millisecond,[BACHDATE]) = 0),
CHECK (datepart(hour,[Computer_Check_Doc_Date]) = 0 and
datepart(minute,[Computer_Check_Doc_Date]) = 0 and
datepart(second,[Computer_Check_Doc_Date]) = 0 and
datepart(millisecond,[Computer_Check_Doc_Date]) = 0),
CHECK (datepart(hour,[CREATDDT]) = 0 and datepart(minute,[CREATDDT]) =
0 and datepart(second,[CREATDDT]) = 0 and
datepart(millisecond,[CREATDDT]) = 0),
CHECK (datepart(hour,[GLPOSTDT]) = 0 and datepart(minute,[GLPOSTDT]) =
0 and datepart(second,[GLPOSTDT]) = 0 and
datepart(millisecond,[GLPOSTDT]) = 0),
CHECK (datepart(hour,[MODIFDT]) = 0 and datepart(minute,[MODIFDT]) = 0
and datepart(second,[MODIFDT]) = 0 and datepart(millisecond,[MODIFDT]) = 0),
CHECK (datepart(hour,[RCLPSTDT]) = 0 and datepart(minute,[RCLPSTDT]) =
0 and datepart(second,[RCLPSTDT]) = 0 and
datepart(millisecond,[RCLPSTDT]) = 0),
CHECK (datepart(day,[TIME1]) = 1 and datepart(month,[TIME1]) = 1 and
datepart(year,[TIME1]) = 1900)
) ON [PRIMARY]
-------------------- SQLAlchemy Class -------------------------
class BatchHeader(gp_Base):
__tablename__ = 'sy00500'
GLPOSTDT = Column(DateTime, nullable=False)
BCHSOURC = Column(String(15), nullable=False, primary_key=True)
BACHNUMB = Column(String(15), nullable=False, primary_key=True
SERIES = Column(Integer, nullable=False)
MKDTOPST = Column(Integer, nullable=False)
NUMOFTRX = Column(Integer, nullable=False)
RECPSTGS = Column(Integer, nullable=False)
DELBACH = Column(Integer, nullable=False)
MSCBDINC = Column(Integer, nullable=False)
BACHFREQ = Column(Integer, nullable=False)
RCLPSTDT = Column(DateTime, nullable=False)
NOFPSTGS = Column(Integer, nullable=False)
BCHCOMNT = Column(String(61), nullable=False)
BRKDNALL = Column(Integer, nullable=False)
CHKSPRTD = Column(Integer, nullable=False)
RVRSBACH = Column(Integer, nullable=False)
USERID = Column(String(15), nullable=False)
CHEKBKID = Column(String(15), nullable=False)
BCHTOTAL = Column(Numeric(19, 5), nullable=False)
#Total of all transactions dollars
BCHEMSG1 = Column(Binary(4), nullable=False)
BCHEMSG2 = Column(Binary(4), nullable=False)
BACHDATE = Column(DateTime, nullable=False)
BCHSTRG1 = Column(String(21), nullable=False)
BCHSTRG2 = Column(String(21), nullable=False)
POSTTOGL = Column(Integer, nullable=False)
MODIFDT = Column(DateTime, nullable=False)
CREATDDT = Column(DateTime, nullable=False)
NOTEINDX = Column(Numeric(19, 5), nullable=False)
CURNCYID = Column(String(15), nullable=False)
BCHSTTUS = Column(Integer, nullable=False)
CNTRLTRX = Column(Integer, nullable=False)
CNTRLTOT = Column(Numeric(19, 5), nullable=False)
PETRXCNT = Column(Integer, nullable=False)
APPROVL = Column(Integer, nullable=False)
APPRVLDT = Column(DateTime, nullable=False)
APRVLUSERID = Column(String(15), nullable=False)
ORIGIN = Column(Integer, nullable=False)
ERRSTATE = Column(Integer, nullable=False)
GLBCHVAL = Column(MSBinary(4), nullable=False)
Computer_Check_Doc_Date = Column(DateTime, nullable=False)
Sort_Checks_By = Column(Integer, nullable=False)
SEPRMTNC = Column(Integer, nullable=False)
REPRNTED = Column(Integer, nullable=False)
CHKFRMTS = Column(Integer, nullable=False)
TRXSORCE = Column(String(13), nullable=False)
PmtMethod = Column(Integer, nullable=False)
EFTFileFormat = Column(Integer, nullable=False)
Workflow_Approval_Status = Column(Integer, nullable=False)
Workflow_Priority = Column(Integer, nullable=False)
TIME1 = Column(DateTime, nullable=False)
def __init__(self, bachnum):
currenttime = datetime(*datetime.now().timetuple()[:3])
bchtotal = 0.0
self.GLPOSTDT = currenttime
self.BCHSOURC = "Rcvg Trx Entry"
self.BACHNUMB = bachnum
self.SERIES = 4
self.MKDTOPST = 0
self.NUMOFTRX = 1
self.RECPSTGS = 0
self.DELBACH = 0
self.MSCBDINC = 0
self.BACHFREQ = 1
self.RCLPSTDT = datetime(1900, 1, 1)
self.NOFPSTGS = 0
self.BCHCOMNT = ''
self.BRKDNALL = 0
self.CHKSPRTD = 0
self.RVRSBACH = 0
self.USERID = 'sa'
self.CHEKBKID = 'CB01'
self.BCHTOTAL = bchtotal
self.BCHEMSG1 = '\x00\x00\x00\x00' #bin
self.BCHEMSG2 = '\x00\x00\x00\x00' #bin
self.BACHDATE = datetime(1900, 1, 1)
self.BCHSTRG1 = ''
self.BCHSTRG2 = ''
self.POSTTOGL = 0
self.MODIFDT = currenttime
self.CREATDDT = currenttime
self.NOTEINDX = 0.0
self.CURNCYID = ''
self.BCHSTTUS = 0
self.CNTRLTRX = 0
self.CNTRLTOT = 0
self.PETRXCNT = 0
self.APPROVL = 0
self.APPRVLDT = datetime(1900, 1, 1)
self.APRVLUSERID = ''
self.ORIGIN = 1
self.ERRSTATE = 0
self.GLBCHVAL = '\x00\x00\x00\x00' #bin
self.Computer_Check_Doc_Date = datetime(1900, 1, 1)
self.Sort_Checks_By = 0
self.SEPRMTNC = 0
self.REPRNTED = 0
self.CHKFRMTS = 0
self.TRXSORCE = ''
self.PmtMethod = 0
self.EFTFileFormat = 0
self.Workflow_Approval_Status = 0
self.Workflow_Priority = 0
self.TIME1 = datetime(1900, 1, 1)
------------ Sample Code -------------------------
>>> import sqlalchemy as sa
>>> engine = sa.create_engine('mssql://user:pass@/?dsn=GPTest',
... encoding='latin-1', convert_unicode=True)
>>> from myproj import model
>>> from myproj.model import BatchHeader
>>> model.meta.Session = sa.orm.sessionmaker(bind=engine)
>>> s = model.meta.Session()
>>> s.add(BatchHeader('foo'))
>>> s.commit()
------------------ Traceback ------------------
Traceback (most recent call last):
File "<stdin>", line 1, in <module>
File
"/srv/gartersnake/lib/python2.5/site-packages/SQLAlchemy-0.5.0rc1-py2.5.egg/sqlalchemy/orm/session.py",
line 669, in commit
self.transaction.commit()
File
"/srv/gartersnake/lib/python2.5/site-packages/SQLAlchemy-0.5.0rc1-py2.5.egg/sqlalchemy/orm/session.py",
line 375, in commit
self._prepare_impl()
File
"/srv/gartersnake/lib/python2.5/site-packages/SQLAlchemy-0.5.0rc1-py2.5.egg/sqlalchemy/orm/session.py",
line 359, in _prepare_impl
self.session.flush()
File
"/srv/gartersnake/lib/python2.5/site-packages/SQLAlchemy-0.5.0rc1-py2.5.egg/sqlalchemy/orm/session.py",
line 1367, in flush
self._flush(objects)
File
"/srv/gartersnake/lib/python2.5/site-packages/SQLAlchemy-0.5.0rc1-py2.5.egg/sqlalchemy/orm/session.py",
line 1437, in _flush
flush_context.execute()
File
"/srv/gartersnake/lib/python2.5/site-packages/SQLAlchemy-0.5.0rc1-py2.5.egg/sqlalchemy/orm/unitofwork.py",
line 263, in execute
UOWExecutor().execute(self, tasks)
File
"/srv/gartersnake/lib/python2.5/site-packages/SQLAlchemy-0.5.0rc1-py2.5.egg/sqlalchemy/orm/unitofwork.py",
line 757, in execute
self.execute_save_steps(trans, task)
File
"/srv/gartersnake/lib/python2.5/site-packages/SQLAlchemy-0.5.0rc1-py2.5.egg/sqlalchemy/orm/unitofwork.py",
line 772, in execute_save_steps
self.save_objects(trans, task)
File
"/srv/gartersnake/lib/python2.5/site-packages/SQLAlchemy-0.5.0rc1-py2.5.egg/sqlalchemy/orm/unitofwork.py",
line 763, in save_objects
task.mapper._save_obj(task.polymorphic_tosave_objects, trans)
File
"/srv/gartersnake/lib/python2.5/site-packages/SQLAlchemy-0.5.0rc1-py2.5.egg/sqlalchemy/orm/mapper.py",
line 1215, in _save_obj
c = connection.execute(statement.values(value_params), params)
File
"/srv/gartersnake/lib/python2.5/site-packages/SQLAlchemy-0.5.0rc1-py2.5.egg/sqlalchemy/engine/base.py",
line 848, in execute
return Connection.executors[c](self, object, multiparams, params)
File
"/srv/gartersnake/lib/python2.5/site-packages/SQLAlchemy-0.5.0rc1-py2.5.egg/sqlalchemy/engine/base.py",
line 899, in execute_clauseelement
return self._execute_compiled(elem.compile(dialect=self.dialect,
column_keys=keys, inline=len(params) > 1), distilled_params=params)
File
"/srv/gartersnake/lib/python2.5/site-packages/SQLAlchemy-0.5.0rc1-py2.5.egg/sqlalchemy/engine/base.py",
line 911, in _execute_compiled
self.__execute_raw(context)
File
"/srv/gartersnake/lib/python2.5/site-packages/SQLAlchemy-0.5.0rc1-py2.5.egg/sqlalchemy/engine/base.py",
line 920, in __execute_raw
self._cursor_execute(context.cursor, context.statement,
context.parameters[0], context=context)
File
"/srv/gartersnake/lib/python2.5/site-packages/SQLAlchemy-0.5.0rc1-py2.5.egg/sqlalchemy/engine/base.py",
line 964, in _cursor_execute
self._handle_dbapi_exception(e, statement, parameters, cursor)
File
"/srv/gartersnake/lib/python2.5/site-packages/SQLAlchemy-0.5.0rc1-py2.5.egg/sqlalchemy/engine/base.py",
line 946, in _handle_dbapi_exception
raise exc.DBAPIError.instance(statement, parameters, e,
connection_invalidated=is_disconnect)
sqlalchemy.exc.ProgrammingError: (ProgrammingError) ('42000', "[42000]
[FreeTDS][SQL Server]Disallowed implicit conversion from data type
varchar to data type binary, table 'TWO.dbo.SY00500', column 'BCHEMSG1'.
Use the CONVERT function to run this query. (260) (SQLPrepare)") 'INSERT
INTO sy00500 ([GLPOSTDT], [BCHSOURC], [BACHNUMB], [SERIES], [MKDTOPST],
[NUMOFTRX], [RECPSTGS], [DELBACH], [MSCBDINC], [BACHFREQ], [RCLPSTDT],
[NOFPSTGS], [BCHCOMNT], [BRKDNALL], [CHKSPRTD], [RVRSBACH], [USERID],
[CHEKBKID], [BCHTOTAL], [BCHEMSG1], [BCHEMSG2], [BACHDATE], [BCHSTRG1],
[BCHSTRG2], [POSTTOGL], [MODIFDT], [CREATDDT], [NOTEINDX], [CURNCYID],
[BCHSTTUS], [CNTRLTRX], [CNTRLTOT], [PETRXCNT], [APPROVL], [APPRVLDT],
[APRVLUSERID], [ORIGIN], [ERRSTATE], [GLBCHVAL],
[Computer_Check_Doc_Date], [Sort_Checks_By], [SEPRMTNC], [REPRNTED],
[CHKFRMTS], [TRXSORCE], [PmtMethod], [EFTFileFormat],
[Workflow_Approval_Status], [Workflow_Priority], [TIME1]) VALUES (?, ?,
?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,
?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)'
[datetime.datetime(2008, 9, 19, 0, 0), 'Rcvg Trx Entry', 'foo', 4, 0, 1,
0, 0, 0, 1, datetime.datetime(1900, 1, 1, 0, 0), 0, '', 0, 0, 0, 'sa',
'CB01', '0.0', <read-only buffer for 0xb74738a0, size -1, offset 0 at
0x8387560>, <read-only buffer for 0xb74738a0, size -1, offset 0 at
0x8387580>, datetime.datetime(1900, 1, 1, 0, 0), '', '', 0,
datetime.datetime(2008, 9, 19, 0, 0), datetime.datetime(2008, 9, 19, 0,
0), '0.0', '', 0, 0, '0', 0, 0, datetime.datetime(1900, 1, 1, 0, 0), '',
1, 0, <read-only buffer for 0xb74738a0, size -1, offset 0 at 0x83875c0>,
datetime.datetime(1900, 1, 1, 0, 0), 0, 0, 0, 0, '', 0, 0, 0, 0,
datetime.datetime(1900, 1, 1, 0, 0)]
-John
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to [email protected]
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~----------~----~----~----~------~----~------~--~---
