Re: [Zope] ZSQL Method Question
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
[EMAIL PROTECTED]
http://eco-living.net
___
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 )
Re: [Zope] ZSQL Method Question
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 )
Re: [Zope] ZSQL Method Question
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
___
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 )
Re: [Zope] ZSQL Method Question
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 )
Re: [Zope] ZSQL Method Question
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
[EMAIL PROTECTED]
http://eco-living.net
___
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 )
Re: [Zope] ZSQL Method Question
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 )
[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 )
Re: [Zope] ZSQL Method Question
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= ', rstatprint '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
PHPI'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.orghttp://mail.zope.org/mailman/listinfo/zope**
No cross posts or HTML encoding! **(Related lists -
http://mail.zope.org/mailman/listinfo/zope-announcehttp://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 )
