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 )
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 )
Re: [Zope] ZSQL Method AUTHENTICATED_USER
I'm pretty sure that all variables have to be passed in explicitly with SQLMethods. Namespaces are not passed in. Try specifying username as an argument in the ZSQLMethods edit form and pass it in like this: dtml-in expr= "lookup_by_username(username=REQUEST.AUTHENTICATED_USER.getUserName()" (Untested) -jon Aaron Straup Cope [EMAIL PROTECTED] writes: Hi, I'm trying to perform a simple MySQL lookup based on the AUTHENTICATED_USER. When I test the ZSQL method from the management interface, everything works fine. However, when I try to call the method from a DTML document, [it] returns nothing. Is this a namespace issue? I'm stumped and hoping that I'm not staring right past the problem. Can anyone point out what it is I am doing wrong? Thanks, a) DTML Document : foo dtml-with people dtml-in lookup_by_username dtml-var "username" /dtml-in /dtml-with b) ZSQL Method : people.lookup_by_username select * from people where username = "dtml-var "REQUEST.AUTHENTICATED_USER.getUserName()"" ___ Zope maillist - [EMAIL PROTECTED] http://lists.zope.org/mailman/listinfo/zope ** No cross posts or HTML encoding! ** (Related lists - http://lists.zope.org/mailman/listinfo/zope-announce http://lists.zope.org/mailman/listinfo/zope-dev ) ___ Zope maillist - [EMAIL PROTECTED] http://lists.zope.org/mailman/listinfo/zope ** No cross posts or HTML encoding! ** (Related lists - http://lists.zope.org/mailman/listinfo/zope-announce http://lists.zope.org/mailman/listinfo/zope-dev )
Re: [Zope] ZSQL Method AUTHENTICATED_USER
sigh / Thanks. It was a syntax issue. I think I tried every single variation on passing variables to the method except that one. Sorry for the bother, folks... :-) On 18 Oct 2000, Jon Prettyman wrote: I'm pretty sure that all variables have to be passed in explicitly with SQLMethods. Namespaces are not passed in. Try specifying username as an argument in the ZSQLMethods edit form and pass it in like this: dtml-in expr= "lookup_by_username(username=REQUEST.AUTHENTICATED_USER.getUserName()" (Untested) -jon Aaron Straup Cope [EMAIL PROTECTED] writes: Hi, I'm trying to perform a simple MySQL lookup based on the AUTHENTICATED_USER. When I test the ZSQL method from the management interface, everything works fine. However, when I try to call the method from a DTML document, [it] returns nothing. Is this a namespace issue? I'm stumped and hoping that I'm not staring right past the problem. Can anyone point out what it is I am doing wrong? Thanks, a) DTML Document : foo dtml-with people dtml-in lookup_by_username dtml-var "username" /dtml-in /dtml-with b) ZSQL Method : people.lookup_by_username select * from people where username = "dtml-var "REQUEST.AUTHENTICATED_USER.getUserName()"" ___ Zope maillist - [EMAIL PROTECTED] http://lists.zope.org/mailman/listinfo/zope ** No cross posts or HTML encoding! ** (Related lists - http://lists.zope.org/mailman/listinfo/zope-announce http://lists.zope.org/mailman/listinfo/zope-dev ) ___ Zope maillist - [EMAIL PROTECTED] http://lists.zope.org/mailman/listinfo/zope ** No cross posts or HTML encoding! ** (Related lists - http://lists.zope.org/mailman/listinfo/zope-announce http://lists.zope.org/mailman/listinfo/zope-dev ) ___ Zope maillist - [EMAIL PROTECTED] http://lists.zope.org/mailman/listinfo/zope ** No cross posts or HTML encoding! ** (Related lists - http://lists.zope.org/mailman/listinfo/zope-announce http://lists.zope.org/mailman/listinfo/zope-dev )
RE: [Zope] ZSQL Method AUTHENTICATED_USER
I have an approach that works for me, but it may be a bit awkward. I have a SQL method (dbGetUserID) which takes AUTHENTICATED_USER as an argument and finds the value from the "userid" field in the database: select userid from users where shortname = dtml-sqlvar AUTHENTICATED_USER type=nb When I need to perform database ops on the user, I just call this method and use the resulting id, eg, insert into sometable (userid, otherfield) values (dtml-in dbGetUserIDdtml-sqlvar userid type=int/dtml-in, dtml-sqlvar sequence-item type=int) I have AUTHENTICATED_USER in the parameter list for this SQL method as well, though it may not need to be... Steve -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of Jon Prettyman Sent: Thursday, 19 October 2000 8:18 To: [EMAIL PROTECTED] Subject: Re: [Zope] ZSQL Method AUTHENTICATED_USER I'm pretty sure that all variables have to be passed in explicitly with SQLMethods. Namespaces are not passed in. Try specifying username as an argument in the ZSQLMethods edit form and pass it in like this: dtml-in expr= "lookup_by_username(username=REQUEST.AUTHENTICATED_USER.getUserName()" (Untested) -jon Aaron Straup Cope [EMAIL PROTECTED] writes: Hi, I'm trying to perform a simple MySQL lookup based on the AUTHENTICATED_USER. When I test the ZSQL method from the management interface, everything works fine. However, when I try to call the method from a DTML document, [it] returns nothing. Is this a namespace issue? I'm stumped and hoping that I'm not staring right past the problem. Can anyone point out what it is I am doing wrong? Thanks, a) DTML Document : foo dtml-with people dtml-in lookup_by_username dtml-var "username" /dtml-in /dtml-with b) ZSQL Method : people.lookup_by_username select * from people where username = "dtml-var "REQUEST.AUTHENTICATED_USER.getUserName()"" ___ Zope maillist - [EMAIL PROTECTED] http://lists.zope.org/mailman/listinfo/zope ** No cross posts or HTML encoding! ** (Related lists - http://lists.zope.org/mailman/listinfo/zope-announce http://lists.zope.org/mailman/listinfo/zope-dev ) ___ Zope maillist - [EMAIL PROTECTED] http://lists.zope.org/mailman/listinfo/zope ** No cross posts or HTML encoding! ** (Related lists - http://lists.zope.org/mailman/listinfo/zope-announce http://lists.zope.org/mailman/listinfo/zope-dev ) ___ Zope maillist - [EMAIL PROTECTED] http://lists.zope.org/mailman/listinfo/zope ** No cross posts or HTML encoding! ** (Related lists - http://lists.zope.org/mailman/listinfo/zope-announce http://lists.zope.org/mailman/listinfo/zope-dev )
Re: [Zope] ZSQL Method Timings/Profiling or MySQL SQL ExecutionTiming Statistics
I'm answering myself because I found a solution to my problem, and I
wanted to share the results with the list, in case someone is struggling
to try to do the same thing...
The problem with mysql (go easy, we're not talking about the fact that
mysql is primitive and overall does not match up to the known
transactional rdbms) is that it does not provide any sql statement
profiling or timing statistics. If you use the mysql client to check a
query, you can see a time. You can even write a perl script utilizing the
benchmark module that comes with mysql to get more sql timings.
However, neither of these methods work with Zope (i.e. do you really feel
like interfacing to perl from Zope, and then calling the benchmark module
for EVERY one of your queries?). A more "automated" approach is desirable,
that compares to Oracle's profiling that logs ALL queries to a file with
timing statistics.
I finally decided after searching for a few hours for information, that I
would modify Zope to handle this for me. The logical place was to put it
in ZMySQLDA. I'm attaching a patch of a really _simple_ logging
mechanism. The only activity that really occurs is the time is noted
before and after the query gets processed and fetched, and the difference
is noted. Finally, it gets logged to /tmp/mysql_timing.log --- You need to
turn off MYSQL_TIMING in db.py of ZMySQL after applying the patch should
you not want the timing to occur any longer. And as usual, a Zope restart
is required after patching your ZMySQLDA.
The patch was written for ZMySQLDA 1.2.0 (nothing below).
Knight
[EMAIL PROTECTED]
On Mon, 9 Oct 2000, knight wrote:
Greetings,
I have a lot of past experience with tuning and timing on Oracle (quite
simple), but I seem to have hit a hurdle with timing my sql statements in
MySQL. Getting these timings are _incredibly_ important to finding
bottlenecks in a web site's performance...
Now, first, is it possible to turn on profiling for ZSQL methods? I've
turned on my Zope's profiling, and I can see statistics on how many times
sqlvar was called, but not specific zsql methods. Second, anyone familiar
with any mysql settings that will dump a log displaying real-time sql
executions and the corresponding execute time?
I like to turn sql timing on, then run around the site for a while, send
some people to test it out, or throw up a http load tester script up, and
then check the timing logs to see how things worked out.
Regards,
Knight
[EMAIL PROTECTED]
___
Zope maillist - [EMAIL PROTECTED]
http://lists.zope.org/mailman/listinfo/zope
** No cross posts or HTML encoding! **
(Related lists -
http://lists.zope.org/mailman/listinfo/zope-announce
http://lists.zope.org/mailman/listinfo/zope-dev )
--- db.py Thu Jun 15 02:10:41 2000
+++ db.py Mon Oct 9 18:06:53 2000
@@ -142,6 +142,12 @@
def __init__(self,connection):
self.connection=connection
+self.MYSQL_TIMING = 1
+if self.MYSQL_TIMING:
+ self.timinglog = open('/tmp/mysql_timing.log', 'w+')
+ if not self.timinglog:
+print "Error: Can't open /tmp/mysql_timing.log for writing"
+self.MYSQL_TIMING = None
info=split(connection)
if info and len(info) 4:
@@ -174,6 +180,7 @@
select_m=regex.compile('[\0- ]*select[\0- ]+',
regex.casefold).match,
):
+import time
db=self.db
try:
queries=filter(None, map(strip,split(query_string, '\0')))
@@ -181,11 +188,22 @@
result=()
desc=None
for qs in queries:
+if self.MYSQL_TIMING:
+ start_time = time.time()
db.query(qs)
+
c=db.store_result()
try:
desc=c.describe()
r=c.fetch_all_rows()
+if self.MYSQL_TIMING:
+ end_time = time.time()
+ tot_time = end_time - start_time
+ tmpstr = "SQL Statement: " + qs + "\n"
+ self.timinglog.write(tmpstr)
+ tmpstr = "Execution Time: " + str(tot_time*1000) + "
+miliseconds (ms)\n\n"
+ self.timinglog.write(tmpstr)
+ self.timinglog.flush()
except: r=None
if not r: continue
if result:
Re: [Zope] ZSQL method broken on 2.1.6
Andreas writes: ZSQLMethod's "advanced" property 'Maximum number of rows retrieved' makes no sense to me. It prevents stupid queries to kill Zope. Assume, such a query returns 100.000.000 records. This probably will let Zope require several GB memory. As I know so far there is no way to disable this property or to set a value like 'maximum' What about a value of 2.000.000.000. Should be enough for most purposes. Dieter ___ Zope maillist - [EMAIL PROTECTED] http://lists.zope.org/mailman/listinfo/zope ** No cross posts or HTML encoding! ** (Related lists - http://lists.zope.org/mailman/listinfo/zope-announce http://lists.zope.org/mailman/listinfo/zope-dev )
RE: [Zope] ZSQL method broken on 2.1.6
-Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of Andreas Sent: Saturday, June 10, 2000 1:59 AM To: Umesh Soni Cc: Phill Hugo; [EMAIL PROTECTED] Subject: Re: [Zope] ZSQL method broken on 2.1.6 (using zope 2.1.6 with ZMySQLDA-2.0.0a1 and MySQLdb-0.2.1) As I know so far there is no way to disable this property or to set a value like 'maximum' This version of ZMySQLDA (and possibly others) doesn't put the LIMIT statement in at all if you set the maximum number of rows to 0 (zero). Like someone else said, it's a "safety net" for queries that may accidently return way too many records. ___ Ron Bickers Logic Etc, Inc. [EMAIL PROTECTED] ___ Zope maillist - [EMAIL PROTECTED] http://lists.zope.org/mailman/listinfo/zope ** No cross posts or HTML encoding! ** (Related lists - http://lists.zope.org/mailman/listinfo/zope-announce http://lists.zope.org/mailman/listinfo/zope-dev )
Re: [Zope] ZSQL method broken on 2.1.6
Umesh Soni wrote: (using zope 2.1.6 with ZMySQLDA-2.0.0a1 and MySQLdb-0.2.1) Hi there, I had this ZSQL method (simplified for clarity) working on 2.1.4 select id, nickname from bboard where id=50 order by id desc limit 30 Remove either the limit 30 or the LIMIT 1000 Advanced property of the ZSQL method (advanced tab). To speed things up, the limit 1000 appended to the query to stop MySQL giving Zope lots of lines when it only wants 1000 anyway. Its not too clever though. There should maybe be a check to see if "limit x" exists in the query. Phill ___ Zope maillist - [EMAIL PROTECTED] http://lists.zope.org/mailman/listinfo/zope ** No cross posts or HTML encoding! ** (Related lists - http://lists.zope.org/mailman/listinfo/zope-announce http://lists.zope.org/mailman/listinfo/zope-dev )
Re: [Zope] ZSQL method broken on 2.1.6
Umesh Soni wrote: (using zope 2.1.6 with ZMySQLDA-2.0.0a1 and MySQLdb-0.2.1) Hi there, I had this ZSQL method (simplified for clarity) working on 2.1.4 select id, nickname from bboard where id=50 order by id desc limit 30 Remove either the limit 30 or the LIMIT 1000 Advanced property of the ZSQL method (advanced tab). To speed things up, the limit 1000 appended to the query to stop MySQL giving Zope lots of lines when it only wants 1000 anyway. Its not too clever though. There should maybe be a check to see if "limit x" exists in the query. Phill Thanks Phill, that did the trick --strange thing though the 'Maximum number of rows retrieved attribute' was set to 100 not 1000 as the error reported --I changed it to 30. Anyway it's working now --cheers. Umesh Soni. ___ Zope maillist - [EMAIL PROTECTED] http://lists.zope.org/mailman/listinfo/zope ** No cross posts or HTML encoding! ** (Related lists - http://lists.zope.org/mailman/listinfo/zope-announce http://lists.zope.org/mailman/listinfo/zope-dev )
Re: [Zope] ZSQL method broken on 2.1.6
Umesh Soni wrote: Umesh Soni wrote: (using zope 2.1.6 with ZMySQLDA-2.0.0a1 and MySQLdb-0.2.1) Hi there, I had this ZSQL method (simplified for clarity) working on 2.1.4 select id, nickname from bboard where id=50 order by id desc limit 30 Remove either the limit 30 or the LIMIT 1000 Advanced property of the ZSQL method (advanced tab). To speed things up, the limit 1000 appended to the query to stop MySQL giving Zope lots of lines when it only wants 1000 anyway. Its not too clever though. There should maybe be a check to see if "limit x" exists in the query. Phill Thanks Phill, that did the trick --strange thing though the 'Maximum number of rows retrieved attribute' was set to 100 not 1000 as the error reported --I changed it to 30. I`m interested in your thoughts about ZSQLMethod's advanced property 'Maximum number of rows retrieved'. It`s default value is 1000. Some DA's like PyGreSQLDA takes no notice on this value while PoPyDA pays attention of this property. Assumed there is a table foo on my DB with actually 2000 records some DA's return only 1000 on table scans like 'select * from foo' and 10 records on 'select * from foo limit 10 offset 1100' (if offset is supported by the RDBMS). How can I know how many records will match at max to my select statement? ZSQLMethod's "advanced" property 'Maximum number of rows retrieved' makes no sense to me. As I know so far there is no way to disable this property or to set a value like 'maximum' -- _ Andreas Heckel[EMAIL PROTECTED] ___ Zope maillist - [EMAIL PROTECTED] http://lists.zope.org/mailman/listinfo/zope ** No cross posts or HTML encoding! ** (Related lists - http://lists.zope.org/mailman/listinfo/zope-announce http://lists.zope.org/mailman/listinfo/zope-dev )
RE: [Zope] zsql method??/
Oops, I forgot to mention that the CONV function is used in your ZSQL method. Here's the ZSQL Method I use (returns upcoming birthdays falling within this, and the next, month): SELECT FirstName, LastName, MONTH(Birthday) AS Month, DAYOFMONTH(Birthday)AS Day, DATE_FORMAT(Birthday, '%M %D, %Y') AS BDay, CONV(ROUND((TO_DAYS(NOW())-TO_DAYS(Birthday))/365.25), 10, 10) as WillBeAge FROM users WHERE MONTH(Birthday) = MOD(MONTH(NOW()), 12) + 1 OR (MONTH(Birthday) = MONTH(NOW()) AND DAYOFMONTH(Birthday) = DAYOFMONTH(NOW())) ORDER BY Month, Day // -Original Message- // From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of // Jonathan Park // Sent: Thursday, May 18, 2000 1:35 PM // To: [EMAIL PROTECTED] // Subject: [Zope] zsql method??/ // // // Hello everyone! // // I have a dtml method that calls in the sql method like this //dtml-in sql method // the result I get is what I would expect except in the unique_# // field 'L' // is added at the end of the number // ex: // // unique_#blahblah blah // 1L data data data // 2L data data data // // any reason why it is adding the 'L'??? // // thanks // -jon // // Have a great afternoon! // // // ___ // Zope maillist - [EMAIL PROTECTED] // http://lists.zope.org/mailman/listinfo/zope // ** No cross posts or HTML encoding! ** // (Related lists - // http://lists.zope.org/mailman/listinfo/zope-announce // http://lists.zope.org/mailman/listinfo/zope-dev ) // ___ Zope maillist - [EMAIL PROTECTED] http://lists.zope.org/mailman/listinfo/zope ** No cross posts or HTML encoding! ** (Related lists - http://lists.zope.org/mailman/listinfo/zope-announce http://lists.zope.org/mailman/listinfo/zope-dev )
