Actually, for sql server it looks like your update just deleted a
couple of lines that were already commented out. I've updated my
patch to work with trunk. This patch seems to solve the problem on ms
sql server:
diff --git a/gluon/sql.py b/gluon/sql.py
--- a/gluon/sql.py
+++ b/gluon/sql.py
@@ -3187,7 +3187,7 @@
elif self._db._dbname == 'mssql' or \
self._db._dbname == 'mssql2':
if not attributes.get('orderby', None) and
w2p_tablenames:
- sql_o += ' ORDER BY %s' % ', '.join(['%s.%s'%
(t,x) for t in w2p_tablenames for x in
((hasattr(self._db[t],'_primarykey') and self._db[t]._primarykey) or
['id'])])
+ sql_o += ' ORDER BY %s' % ', '.join(['%s.%s'%
(t,x) for t in w2p_tablenames for x in
((hasattr(self._db[t],'_primarykey') and self._db[t]._primarykey) or
[self._db[t]['id'].name] or ['id'])])
sql_s += ' TOP %i' % lmax
elif self._db._dbname == 'firebird':
if not attributes.get('orderby', None) and
w2p_tablenames:
On Sep 22, 10:16 am, mdipierro <mdipie...@cs.depaul.edu> wrote:
> actually I just fixed it in trunk for a few APIs which will cover the
> case you described.
> There are still a few cases where a custom ID name breaks something.
>
> Massimo
>
> On Sep 22, 9:05 am, mdipierro <mdipie...@cs.depaul.edu> wrote:
>
>
>
> > turns out this is very difficult to fix with current DAL (sql.py). I
> > will not happen overnight.
>
> > Massimo
>
> > On Sep 21, 4:28 pm, mwolfe02 <michael.joseph.wo...@gmail.com> wrote:
>
> > > Here's the patch I came up with. Massimo, if you've already got a fix
> > > in, just ignore...
>
> > > diff --git a/gluon/sql.py b/gluon/sql.py
> > > --- a/gluon/sql.py
> > > +++ b/gluon/sql.py
> > > @@ -3193,7 +3193,7 @@
> > > if not attributes.get('orderby', None) and
> > > w2p_tablenames:
> > > # sql_o += ' ORDER BY %s' % ', '.join([t + '.id'
> > > # for t in w2p_tablenames ])
> > > - sql_o += ' ORDER BY %s' % ', '.join(['%s.%s'%
> > > (t,x) for t in w2p_tablenames for x in
> > > ((hasattr(self._db[t],'_primarykey') and self._db[t]._primarykey) or
> > > ['id'])])
> > > + sql_o += ' ORDER BY %s' % ', '.join(['%s.%s'%
> > > (t,x) for t in w2p_tablenames for x in
> > > ((hasattr(self._db[t],'_primarykey') and self._db[t]._primarykey) or
> > > [self._db[t]['id'].name] or ['id'])])
> > > sql_s += ' TOP %i' % lmax
> > > elif self._db._dbname == 'firebird':
> > > if not attributes.get('orderby', None) and
> > > w2p_tablenames:
>
> > > On Sep 21, 5:10 pm, mwolfe02 <michael.joseph.wo...@gmail.com> wrote:
>
> > > > working on tracing error gets me to line 3196 of sql.py:
>
> > > > sql_o += ' ORDER BY %s' % ', '.join(['%s.%s'%(t,x) for t in
> > > > w2p_tablenames for x in ((hasattr(self._db[t],'_primarykey') and
> > > > self._db[t]._primarykey) or ['id'])])
>
> > > > Problem is that with this table definition:
> > > > db.define_table('mytable',
> > > > Field('my_id', 'id')
> > > > )
>
> > > > This line:
> > > > hasattr(db.mytable, '_primarykey') # returns False
>
> > > > It seems like the above line should return True and
> > > > db.mytable._primarykey should equal 'my_id'. Which makes me think the
> > > > problem actually goes back to define_table.
>
> > > > -Mike
>
> > > > On Sep 21, 4:44 pm, mwolfe02 <michael.joseph.wo...@gmail.com> wrote:
>
> > > > > I'm using a legacy tables and trying to use the shortcut method of
> > > > > returning a row/record by passing the value of the ID field directly
> > > > > to the table. It appears that 'id' is still hardcoded into the logic
> > > > > at some level, though. This works:
>
> > > > > db.mytable(db.mytable.my_id==1)
>
> > > > > But this does not:
>
> > > > > db.mytable(1)
>
> > > > > # returns ProgrammingError: ('42S22', "[42S22] [Microsoft][ODBC SQL
> > > > > Server Driver][SQL Server]Invalid column name 'id'. (207)
> > > > > (SQLExecDirectW)")
> > > > > # for a table defined as follows:
>
> > > > > db.define_table('mytable',
> > > > > Field('my_id', 'id')
> > > > > )
>
> > > > > db.mytable(1)
>
> > > > > # returns KeyError: 'id'
> > > > > # for a table defined as follows:
>
> > > > > db.define_table('mytable',
> > > > > Field('my_id', 'id'),
> > > > > primarykey=['my_id']
> > > > > )
>
> > > > > I can understand the second case failing, as primarykey seems like it
> > > > > would be usually used to define a multi-field key. In such a case, a
> > > > > single value would not be enough to identify a record, anyway. I
> > > > > tried this simply as a workaround for the original problem, but with
> > > > > no success.
