A DECLARE CURSOR is required for the keywords 'FOR UPDATE", but I'm
not sure that they do the same thing as FOR UPDATE in MySQL. On the
other hand, the "(UPDLOCK)" keyword attached to the FROM clause
doesn't require a DECLARE CURSOR.
On May 11, 9:34 am, "Michael Bayer" <mike...@zzzcomputing.com> wrote:
> but is the comment in the code correct ? is "DECLARE CURSOR" required ?
>
> Daniel wrote:
>
> > I mentioned this originally, but maybe it wasn't clear. In order to
> > have MSSQL perform a select for update the FROM clause must be
> > modified. This is comparable to appending FOR UPDATE to the entire
> > query in other DBMSs. For example, the following two queries have the
> > same effect and show the difference between MySQL and MSSQL
>
> > MySQL: SELECT id, name FROM employee WHERE id = 1 FOR UPDATE
> > MSSQL: SELECT id, name FROM employee (UPDLOCK) WHERE id=1
>
> > In SQLAlchemy 0.5.3, I've noticed that the 'for_update_clause(self,
> > select)' function appears to simply append something for the languages
> > where it is supported. How/Where would I add the ability in MSSQL to
> > modify the FROM clause to have '(UPDLOCK)'?
>
> > At the moment the only way I've found to accomplish this is to hand
> > write my queries and execute them all directly, as in session.execute
> > (query). I'd prefer not to do this since it's functionality that's
> > available in MSSQL and appears to have a sensible insertion point in
> > SQLAlchemy.
>
> > Thanks.
>
> > On May 11, 8:40 am, Daniel <daniel.watr...@gmail.com> wrote:
> >> Can someone give me an idea about this? Should this be submitted as a
> >> bug or feature request?
>
> >> Thanks.
>
> >> On May 7, 3:50 pm, Daniel <daniel.watr...@gmail.com> wrote:
>
> >> > Hello,
>
> >> > I have a transaction that involves a SELECT and subsequent UPDATE. It
> >> > is operating against MSSQL. I need to make sure that the row locks so
> >> > that other processes may not access it until I have completed my
> >> > update, or that they at least fail when trying to UPDATE after the
> >> > first transaction commits.
>
> >> > I think that either FOR UPDATE or UPDLOCK would work, but I can't find
> >> > a way to make either of them work. In the mmsql.py file I find this
> >> > code:
> >> > def for_update_clause(self, select):
> >> > # "FOR UPDATE" is only allowed on "DECLARE CURSOR" which
> >> > SQLAlchemy doesn't use
> >> > return ''
>
> >> > This leads me to believe that FOR UPDATE will not work.
>
> >> > I've also tried this
> >> > s = select(table.c, table.c.field>0, [text("(UPDLOCK)")])
> >> > conn.execute(s)
>
> >> > Rather than producing "SELECT * FROM table (UPDLOCK) where field > 0"
> >> > it instead produces "SELECT * FROM table, (UPDLOCK) where field > 0"
>
> >> > That little comman throws the whole thing off. Can anyone suggest a
> >> > way for me to accomplish what I'm trying to do in sqlalchemy.
>
> >> > Thanks in advance,
> >> > Daniel
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~----------~----~----~----~------~----~------~--~---
