Re: [Zope] ZSQL Method Question

2006-07-19 Thread John Schinnerer

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

2006-07-18 Thread Cliff Ford

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

2006-07-18 Thread Peter Bengtsson

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

2006-07-18 Thread Tino Wildenhain
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

2006-07-18 Thread John Schinnerer


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

2006-07-18 Thread Cliff Ford
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 )


Re: [Zope] ZSQL Method Question

2006-07-17 Thread Jonathan



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 )