New to python/Zope, old-timer on PHP.... I'm using MySQL and a ZSQL method to insert data into the database. ex: insert into org (org_name, org_phone) values ('x', 'y') What I'm trying to figure out is that org_id (also part of the org table, but not specified in the sql statement) is an auto_increment primary key field and in PHP I can use mysql_insert_id() to find out what org_id was set to after the mysql_query() call. Is there a way to retrieve that value with ZSQL method, or must I use some other mechanism? Thanks! -- Ben Menking RHCE IT Security Consulting (864) 420-5603 (864) 751-1638 fax IT and Business Solutions
when you do an insert with a ZSQL method you do not get back any info from mysql, you need to do a subsequent search to get 'autogenerated' columns. So do your insert, then do an ZSQL search. The search returns a 'Results' object which has several methods you can use to get at the search results. In a python script try something like: rstat=context.UserActivity.SQL_SearchEvents(userId='111') #substitute your own zsql search print 'rstat= ', rstat print 'rstat.names= ',rstat.names() print 'rstat.tuples= ',rstat.tuples() print 'rstat.asRDB= ',rstat.asRDB() print 'rstat.data_dictionary= ',rstat.data_dictionary() print 'rstat.dictionaries= ',rstat.dictionaries() hth Jonathan ----- Original Message ----- From: Benjamin Menking To: zope@zope.org Sent: Monday, July 17, 2006 1:01 PM Subject: [Zope] ZSQL Method Question New to python/Zope, old-timer on PHP.... I'm using MySQL and a ZSQL method to insert data into the database. ex: insert into org (org_name, org_phone) values ('x', 'y') What I'm trying to figure out is that org_id (also part of the org table, but not specified in the sql statement) is an auto_increment primary key field and in PHP I can use mysql_insert_id() to find out what org_id was set to after the mysql_query() call. Is there a way to retrieve that value with ZSQL method, or must I use some other mechanism? Thanks! -- Ben Menking RHCE IT Security Consulting (864) 420-5603 (864) 751-1638 fax IT and Business Solutions ------------------------------------------------------------------------------ _______________________________________________ Zope maillist - Zope@zope.org http://mail.zope.org/mailman/listinfo/zope ** No cross posts or HTML encoding! ** (Related lists - http://mail.zope.org/mailman/listinfo/zope-announce http://mail.zope.org/mailman/listinfo/zope-dev )
This is how it works for MySQL: insert into org (org_name, org_phone) values ('x', 'y') <dtml-var sql_delimiter> select LAST_INSERT_ID() as org_id You have to have the select LAST_INSERT_ID call in the same query as the insert, and you have to have the sql_delimiter. I assume you know that the x and y values should be <sql-var ...> types. Cliff Benjamin Menking wrote:
New to python/Zope, old-timer on PHP....
I'm using MySQL and a ZSQL method to insert data into the database. ex:
insert into org (org_name, org_phone) values ('x', 'y')
What I'm trying to figure out is that org_id (also part of the org table, but not specified in the sql statement) is an auto_increment primary key field and in PHP I can use mysql_insert_id() to find out what org_id was set to after the mysql_query() call.
Is there a way to retrieve that value with ZSQL method, or must I use some other mechanism?
Thanks!
------------------------------------------------------------------------
_______________________________________________ Zope maillist - Zope@zope.org http://mail.zope.org/mailman/listinfo/zope ** No cross posts or HTML encoding! ** (Related lists - http://mail.zope.org/mailman/listinfo/zope-announce http://mail.zope.org/mailman/listinfo/zope-dev )
What if you have 1,000,000 requests/sec? What if between the INSERT and the LAST_INSERT_ID() another INSERT is made? I use PostgreSQL and with postgres you can always ask the sequence what the next id is going to be. It goes something like this:: next_id = context.GetNextId()[0].next_id context.SQLInsertUser(uid=next_id, name='Peter') where 'GetNextId' is a ZSQL method that looks like this:: <params></params> SELECT NEXTVAL('users_uid_seq') AS next_id Cliff Ford wrote:
This is how it works for MySQL:
insert into org (org_name, org_phone) values ('x', 'y') <dtml-var sql_delimiter> select LAST_INSERT_ID() as org_id
You have to have the select LAST_INSERT_ID call in the same query as the insert, and you have to have the sql_delimiter.
I assume you know that the x and y values should be <sql-var ...> types.
Cliff
Benjamin Menking wrote:
New to python/Zope, old-timer on PHP....
I'm using MySQL and a ZSQL method to insert data into the database. ex:
insert into org (org_name, org_phone) values ('x', 'y')
What I'm trying to figure out is that org_id (also part of the org table, but not specified in the sql statement) is an auto_increment primary key field and in PHP I can use mysql_insert_id() to find out what org_id was set to after the mysql_query() call.
Is there a way to retrieve that value with ZSQL method, or must I use some other mechanism?
Thanks!
------------------------------------------------------------------------
_______________________________________________ Zope maillist - Zope@zope.org http://mail.zope.org/mailman/listinfo/zope ** No cross posts or HTML encoding! ** (Related lists - http://mail.zope.org/mailman/listinfo/zope-announce http://mail.zope.org/mailman/listinfo/zope-dev )
Zope maillist - Zope@zope.org http://mail.zope.org/mailman/listinfo/zope ** No cross posts or HTML encoding! ** (Related lists - http://mail.zope.org/mailman/listinfo/zope-announce http://mail.zope.org/mailman/listinfo/zope-dev )
-- Peter Bengtsson, work www.fry-it.com home www.peterbe.com hobby www.issuetrackerproduct.com
Peter Bengtsson wrote:
What if you have 1,000,000 requests/sec? What if between the INSERT and the LAST_INSERT_ID() another INSERT is made?
I use PostgreSQL and with postgres you can always ask the sequence what the next id is going to be. It goes something like this::
next_id = context.GetNextId()[0].next_id context.SQLInsertUser(uid=next_id, name='Peter')
where 'GetNextId' is a ZSQL method that looks like this::
<params></params> SELECT NEXTVAL('users_uid_seq') AS next_id
Its even easier: one ZSQL Method: INSERT INTO foo (foo_id,blah,bar) VALUES (nextval('foo_foo_id_seq'),<dtml-sqlvar blah> ... ); SELECT CURRVAL('foo_foo_id_seq') as foo_id; But your above solution is valid too. Regards Tino
Aloha, Is this even easier solution only for postgreSQL, or is it for MySQL? I am going to have to start working with integrating MySQL into zope-based stuff soon so I'm trying to get a head start... :-) thanks, John S. Tino Wildenhain wrote:
Peter Bengtsson wrote:
What if you have 1,000,000 requests/sec? What if between the INSERT and the LAST_INSERT_ID() another INSERT is made?
I use PostgreSQL and with postgres you can always ask the sequence what the next id is going to be. It goes something like this::
next_id = context.GetNextId()[0].next_id context.SQLInsertUser(uid=next_id, name='Peter')
where 'GetNextId' is a ZSQL method that looks like this::
<params></params> SELECT NEXTVAL('users_uid_seq') AS next_id
Its even easier: one ZSQL Method:
INSERT INTO foo (foo_id,blah,bar) VALUES (nextval('foo_foo_id_seq'),<dtml-sqlvar blah> ... ); SELECT CURRVAL('foo_foo_id_seq') as foo_id;
But your above solution is valid too.
Regards Tino _______________________________________________ Zope maillist - Zope@zope.org http://mail.zope.org/mailman/listinfo/zope ** No cross posts or HTML encoding! ** (Related lists - http://mail.zope.org/mailman/listinfo/zope-announce http://mail.zope.org/mailman/listinfo/zope-dev )
-- John Schinnerer - MA, Whole Systems Design ------------------------------------------ - Eco-Living - Whole Systems Design Services People - Place - Learning - Integration john@eco-living.net http://eco-living.net
Comment on Peter's suggestion: I am no expert on these things, but it is my understanding that for MySQL LAST_INSERT_ID() fetches the last autoincrement value made by the current insert, so the outcome is not affected by virtually simultaneous requests. And I don't think MySQL accepts a value for the autoincrement key. I am a bit surprised that the code you quote is reliable - surely there is a possibility of an insert after calling GetNextID and before calling SQLInsert in the python code? Comment on Tino's suggestion: I think that LAST_INSERT_ID() is MySQL specific and I guess CURRVAL() is Postgres specific. But the principle is the same: calling within the same Z SQL Method ought to be safe. Anyway, I hope John has had is question answered. Cliff Tino Wildenhain wrote:
Peter Bengtsson wrote:
What if you have 1,000,000 requests/sec? What if between the INSERT and the LAST_INSERT_ID() another INSERT is made?
I use PostgreSQL and with postgres you can always ask the sequence what the next id is going to be. It goes something like this::
next_id = context.GetNextId()[0].next_id context.SQLInsertUser(uid=next_id, name='Peter')
where 'GetNextId' is a ZSQL method that looks like this::
<params></params> SELECT NEXTVAL('users_uid_seq') AS next_id
Its even easier: one ZSQL Method:
INSERT INTO foo (foo_id,blah,bar) VALUES (nextval('foo_foo_id_seq'),<dtml-sqlvar blah> ... ); SELECT CURRVAL('foo_foo_id_seq') as foo_id;
But your above solution is valid too.
Regards Tino _______________________________________________ Zope maillist - Zope@zope.org http://mail.zope.org/mailman/listinfo/zope ** No cross posts or HTML encoding! ** (Related lists - http://mail.zope.org/mailman/listinfo/zope-announce http://mail.zope.org/mailman/listinfo/zope-dev )
Aloha, I think so, for the moment anyhow! Thanks all, John S. Cliff Ford wrote:
Comment on Peter's suggestion: I am no expert on these things, but it is my understanding that for MySQL LAST_INSERT_ID() fetches the last autoincrement value made by the current insert, so the outcome is not affected by virtually simultaneous requests. And I don't think MySQL accepts a value for the autoincrement key. I am a bit surprised that the code you quote is reliable - surely there is a possibility of an insert after calling GetNextID and before calling SQLInsert in the python code?
Comment on Tino's suggestion: I think that LAST_INSERT_ID() is MySQL specific and I guess CURRVAL() is Postgres specific. But the principle is the same: calling within the same Z SQL Method ought to be safe.
Anyway, I hope John has had is question answered.
Cliff
Tino Wildenhain wrote:
Peter Bengtsson wrote:
What if you have 1,000,000 requests/sec? What if between the INSERT and the LAST_INSERT_ID() another INSERT is made?
I use PostgreSQL and with postgres you can always ask the sequence what the next id is going to be. It goes something like this::
next_id = context.GetNextId()[0].next_id context.SQLInsertUser(uid=next_id, name='Peter')
where 'GetNextId' is a ZSQL method that looks like this::
<params></params> SELECT NEXTVAL('users_uid_seq') AS next_id
Its even easier: one ZSQL Method:
INSERT INTO foo (foo_id,blah,bar) VALUES (nextval('foo_foo_id_seq'),<dtml-sqlvar blah> ... ); SELECT CURRVAL('foo_foo_id_seq') as foo_id;
But your above solution is valid too.
Regards Tino _______________________________________________ Zope maillist - Zope@zope.org http://mail.zope.org/mailman/listinfo/zope ** No cross posts or HTML encoding! ** (Related lists - http://mail.zope.org/mailman/listinfo/zope-announce http://mail.zope.org/mailman/listinfo/zope-dev )
_______________________________________________ Zope maillist - Zope@zope.org http://mail.zope.org/mailman/listinfo/zope ** No cross posts or HTML encoding! ** (Related lists - http://mail.zope.org/mailman/listinfo/zope-announce http://mail.zope.org/mailman/listinfo/zope-dev )
-- John Schinnerer - MA, Whole Systems Design ------------------------------------------ - Eco-Living - Whole Systems Design Services People - Place - Learning - Integration john@eco-living.net http://eco-living.net
participants (6)
-
Benjamin Menking -
Cliff Ford -
John Schinnerer -
Jonathan -
Peter Bengtsson -
Tino Wildenhain