On Fri, Apr 3, 2009 at 3:59 PM, mdipierro <mdipie...@cs.depaul.edu> wrote:
>
> I do not see a problem with this but is GO a mssql only keyword?
In MSSQL - "GO" runs the accumulated batch of lines (it's like "enter").
The problem is, I think, that you cannot depend on the order of statement
exectution when "GO" executes the SQL statements - so you must "complete"
table alterations before you use them, and the only way to be sure of it
is.... to break up the execution batches.
>
>
> On Apr 3, 3:29 pm, DenesL <denes1...@yahoo.ca> wrote:
> > Changing a MSSQL table definition with migrate=True in the models
> > produces this error ('escalated' is the original field name in my
> > table):
> >
> > ProgrammingError: ('42S22', "[42S22] [Microsoft][ODBC SQL Server
> > Driver][SQL Server]Invalid column name 'escalated__tmp'. (207)
> > (SQLExecDirectW)")
> >
> > I traced down the error to sql.py, class SQLTable, function _migrate
> > where the following batch of statements is built:
> >
> > ALTER TABLE calls ADD escalated__tmp DATETIME;
> > UPDATE calls SET escalated__tmp=escalated;
> > ALTER TABLE calls DROP COLUMN escalated;
> > ALTER TABLE calls ADD escalated DATETIME;
> > UPDATE calls SET escalated=escalated__tmp;
> > ALTER TABLE calls DROP COLUMN escalated__tmp;
> >
> > The problem is that batches in MSSQL (2005) have these rules and the
> > batch above breaks rule number 2:
> >
> > 1) CREATE DEFAULT, CREATE FUNCTION, CREATE PROCEDURE, CREATE RULE,
> > CREATE TRIGGER, and CREATE VIEW statements cannot be combined with
> > other statements in a batch. The CREATE statement must start the
> > batch. All other statements that follow in that batch will be
> > interpreted as part of the definition of the first CREATE statement.
> >
> > 2) A table cannot be changed and then the new columns referenced in
> > the same batch.
> >
> > 3) If an EXECUTE statement is the first statement in a batch, the
> > EXECUTE keyword is not required. The EXECUTE keyword is required if
> > the EXECUTE statement is not the first statement in the batch.
> >
> > The correct batch should be (note the GO on the second line, it has to
> > sit on a line by itself):
> >
> > ALTER TABLE calls ADD escalated__tmp DATETIME;
> > GO
> > UPDATE calls SET escalated__tmp=escalated;
> > ALTER TABLE calls DROP COLUMN escalated;
> > ALTER TABLE calls ADD escalated DATETIME;
> > UPDATE calls SET escalated=escalated__tmp;
> > ALTER TABLE calls DROP COLUMN escalated__tmp;
> >
> > The fix is apparently easy but I am not sure about the implications of
> > the other rules so I will leave it to Massimo.
> >
> > Denes.
> >
>
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"web2py Web Framework" group.
To post to this group, send email to web2py@googlegroups.com
To unsubscribe from this group, send email to
web2py+unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/web2py?hl=en
-~----------~----~----~----~------~----~------~--~---
