[Zope-DB] ZSQL and last_insert_id

Harry Wilkinson harryw@nipltd.com
Wed, 20 Feb 2002 17:27:20 +0000


The problem is that ZSQL methods have a parameter called max_rows that is 
1000 by default, because that's what the default is for the underlying Python 
interface.  Adding the LIMIT bit to the LAST_INSERT_ID() call makes the 
syntax invalid, as you can tell.

The solution is to pass in max_rows as 0, which will prevent the appending of 
the 'LIMIT' bit to your SQL code.  I'm not sure if I should really say 
'parameter' and 'pass in', because it's not exactly a parameter like the 
normal parameters you pass in.  Whatever you want to call it, it's done 
something like this:

<dtml-comment>
connection_id : your_connection
arguments: arg1 arg2 some_more_args_if_you_want
max_rows: 0
</dtml-comment>
SELECT LAST_INSERT_ID() AS last_insert_id


Hope that helps :)


Harry


On Tuesday 19 February 2002 11:04 pm, Chad Nantais wrote:
> What is the right syntax for getting the id of the last recorded inserted
> with ZSQL methods? I tried this:
> SELECT LAST_INSERT_ID() AS last_insert_id
>
> The resulting error is:
> Traceback (innermost last):
>   File /usr/local/zope/2.4.4b1/lib/python/ZPublisher/Publish.py, line 223,
> in publish_module
>   File /usr/local/zope/2.4.4b1/lib/python/ZPublisher/Publish.py, line 187,
> in publish
>   File /usr/local/zope/2.4.4b1/lib/python/Zope/__init__.py, line 226, in
> zpublisher_exception_hook
>     (Object: last_insert_id)
>   File /usr/local/zope/2.4.4b1/lib/python/ZPublisher/Publish.py, line 171,
> in publish
>   File /usr/local/zope/2.4.4b1/lib/python/ZPublisher/mapply.py, line 160,
> in mapply
>     (Object: manage_test)
>   File /usr/local/zope/2.4.4b1/lib/python/ZPublisher/Publish.py, line 112,
> in call_object
>     (Object: manage_test)
>   File /usr/local/zope/2.4.4b1/lib/python/Shared/DC/ZRDB/DA.py, line 400,
> in manage_test
>     (Object: last_insert_id)
>   File /usr/local/zope/2.4.4b1/lib/python/Shared/DC/ZRDB/DA.py, line 378,
> in manage_test
>     (Object: last_insert_id)
>   File /usr/local/zope/2.4.4b1/lib/python/Shared/DC/ZRDB/DA.py, line 493,
> in __call__
>     (Object: last_insert_id)
>   File /usr/local/zope/instance/Products/ZMySQLDA/db.py, line 252, in query
> OperationalError: (1064, "You have an error in your SQL syntax near
> 'last_insert_id LIMIT 1000' at line 1"
>
>
>
>
>
>
>
> _______________________________________________
> Zope-DB mailing list
> Zope-DB@zope.org
> http://lists.zope.org/mailman/listinfo/zope-db