Re: [Zope] ZSQL not shown in list to add new method
Now i installed zope2 2.13.7 and just added products.zsqlmethods in buildout.cfg and also added entry as products.sqlmethods = 2.13.3 in versions.cfg. Then run the buildout and mkzopeinstance. After instance is created, checked to add new zsql method. But still there is no option to add it. Also checked the egg file and it is compiled and buildout has ot shown any error for zsql. What's going wrong? CPKulkarni On Sun, Jun 26, 2011 at 1:04 PM, Andreas Jung li...@zopyx.com wrote: Working for me as it should using Zope 2.13.6. -aj ___ Zope maillist - Zope@zope.org https://mail.zope.org/mailman/listinfo/zope ** No cross posts or HTML encoding! ** (Related lists - https://mail.zope.org/mailman/listinfo/zope-announce https://mail.zope.org/mailman/listinfo/zope-dev )
Re: [Zope] ZSQL not shown in list to add new method
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 06/27/2011 12:49 PM, c k wrote: Now i installed zope2 2.13.7 and just added products.zsqlmethods in buildout.cfg and also added entry as products.sqlmethods = 2.13.3 in versions.cfg. Then run the buildout and mkzopeinstance. After instance is created, checked to add new zsql method. But still there is no option to add it. Also checked the egg file and it is compiled and buildout has ot shown any error for zsql. What's going wrong? Are you including the ZCML for Products.ZSQLMethods in your buildout.cfg instance section? Tres. - -- === Tres Seaver +1 540-429-0999 tsea...@palladion.com Palladion Software Excellence by Designhttp://palladion.com -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.10 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/ iEYEARECAAYFAk4Izm8ACgkQ+gerLs4ltQ4cZQCfVikIOmxqACvKOjwRNULkm3mw o7kAn0K0oyihIx5OQNCDte6EQobiS+1s =IEoh -END PGP SIGNATURE- ___ Zope maillist - Zope@zope.org https://mail.zope.org/mailman/listinfo/zope ** No cross posts or HTML encoding! ** (Related lists - https://mail.zope.org/mailman/listinfo/zope-announce https://mail.zope.org/mailman/listinfo/zope-dev )
Re: [Zope] ZSQL not shown in list to add new method
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 06/27/2011 02:39 PM, Tres Seaver wrote: On 06/27/2011 12:49 PM, c k wrote: Now i installed zope2 2.13.7 and just added products.zsqlmethods in buildout.cfg and also added entry as products.sqlmethods = 2.13.3 in versions.cfg. Then run the buildout and mkzopeinstance. After instance is created, checked to add new zsql method. But still there is no option to add it. Also checked the egg file and it is compiled and buildout has ot shown any error for zsql. What's going wrong? Are you including the ZCML for Products.ZSQLMethods in your buildout.cfg instance section? FWIW, I just did the following:: - % --- $ /opt/Python-2.6.5/bin/virtualenv --no-site-package /tmp/zsql ... $ /tmp/zsql/bin/easy_install Zope==2.13.7 Products.ZSQLMethods==2.13.3 ... $ /tmp/zsql/bin/mkzopeinstance -d /tmp/zsqlinst ... $ /tmp/zsqlinst/zopectl fg - % --- and was able to add ZSQL methods TTW in the ZMI. Tres. - -- === Tres Seaver +1 540-429-0999 tsea...@palladion.com Palladion Software Excellence by Designhttp://palladion.com -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.10 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/ iEYEARECAAYFAk4I0CIACgkQ+gerLs4ltQ6AeACglTsnRwkY/8LjTL4mEvwNgoZw MZcAnRi4Ww47JVDLLYrk+viy2kWlDKxS =3bpR -END PGP SIGNATURE- ___ Zope maillist - Zope@zope.org https://mail.zope.org/mailman/listinfo/zope ** No cross posts or HTML encoding! ** (Related lists - https://mail.zope.org/mailman/listinfo/zope-announce https://mail.zope.org/mailman/listinfo/zope-dev )
Re: [Zope] ZSQL not shown in list to add new method
Now i installed zope2 2.13.7 and just added products.zsqlmethods in buildout.cfg and also added entry as products.sqlmethods = 2.13.3 in versions.cfg. Then run the buildout and mkzopeinstance. After instance is created, checked to add new zsql method. But still there is no option to add it. Also checked the egg file and it is compiled and buildout has ot shown any error for zsql. What's going wrong? CPKulkarni On Sun, Jun 26, 2011 at 1:04 PM, Andreas Jung li...@zopyx.com wrote: Working for me as it should using Zope 2.13.6. -aj ___ Zope maillist - Zope@zope.org https://mail.zope.org/mailman/listinfo/zope ** No cross posts or HTML encoding! ** (Related lists - https://mail.zope.org/mailman/listinfo/zope-announce https://mail.zope.org/mailman/listinfo/zope-dev )
Re: [Zope] Zsql and oracle CLOB column
harshad behere wrote:
Dear list,
While inserting data into one of the oracle table's CLOB column using
zsql via python script, we observed that zope process (running in debug
mode) is getting terminated/killed if length of text data = 4094 with
message on command prompt
Input string too long, limit 4096
Above one more looks like issue/bug with oracle odbc driver rather than
zsql/zope issue.
http://164.100.150.68/downloads/oracle/Oracle_InstantClient/Oracle/instantclient10_1/ODBCRelnotesUS.htm
http://www.dbatools.net/doc/bug10203.html
http://mail.python.org/pipermail/python-list/2007-March/603644.html
Whereas for text data with length =4001 and =4093 - It throws
following error
OperationalError on Products.mxODBCZopeDA.ZopeDA.DatabaseConnection
DSN=test_clob_oracle thread 3672/3672 at 0x3ac3530: ('HY000', 1704,
'[Oracle][ODBC][Ora]ORA-01704: string literal too long\n', 6113)
python script details:
print context.clob_zsql_test(i_description='A'*4095).
return printed
zsql details -
INSERT INTO CLOB_TEST(description) VALUES(dtml-sqlvar i_description
type=string)
Table/column details :
id - number
description - clob
Where as if we use mxODBCZopeDA execute API with following insert query
syntax
string_value = 'A'*5000
query = INSERT INTO CLOB_TEST(document) VALUES(?)
result = test_connection.execute(query, string_value)
it works fine even with text data having length 1
After googling on the same found that execute method uses/support
parameters binding (is it similar to oracle bind varibles ? thats may be
reason why it works well). Do we have similar support for zsql or is
there other workaround ?
Configuration/other details are as follows :
RDBMS : Oracle Database 10g Release 10.2.0.1.0
ODBC used : Oracle ODBC which shipped with Oracle-Client-10g
Python adapter : mxODBC Python ODBC Interface - 2.1
Related Zope Product : mxODBCZopeDA-1.0.10
Zope : Zope 2.11.1-final
Python : python 2.4.4
Operating system : win32
ZEO setup : Yes
No of ZEO nodes : one
Threads per ZEO node : 10
Any kind of pointers/suggestions would be of great help.
Thanking you,
Harshad.
___
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, call for early beta testers
Maciej Wisniowski wrote: It works something like this:: (...) If in the folder 'sql/Userstuff' you have a file called 'select_users.sql' you will the be able to just run: # select users: print SQLUser.select_users() # same but first printed the final SQL used print SQLUser.select_users(debug__=True) # save time for the next call print SQLUser.select_users(memcache__=True) Few questions :) sql/Userstuff is a filesystem folder or a folder in ZODB? On the filesystem. No ZODB. How does select_users.sql look? is this dtml with parameters like in ZSQLMethods? Can I just pass parameters like: SQLUser.select_users(param1='p1', param2='p2')? Exactly like DTML ZSQLMethods. Yes. Have you compared zsql + memcache performace with eg. CCZSQLMethods from Dieter Maurer? Wasn't aware of this project. Will check it out. Is anybody interested in a being early beta testers before I release it as Open Source? It works quite well here for me but I fear I'm getitng a bit home blind based on just my needs. I'm pretty sure it works but some early feedback would be appreciated. I'm interested, although I can't guarantee quick response :) -- 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, call for early beta testers
It works something like this:: (...) If in the folder 'sql/Userstuff' you have a file called 'select_users.sql' you will the be able to just run: # select users: print SQLUser.select_users() # same but first printed the final SQL used print SQLUser.select_users(debug__=True) # save time for the next call print SQLUser.select_users(memcache__=True) Few questions :) sql/Userstuff is a filesystem folder or a folder in ZODB? How does select_users.sql look? is this dtml with parameters like in ZSQLMethods? Can I just pass parameters like: SQLUser.select_users(param1='p1', param2='p2')? Have you compared zsql + memcache performace with eg. CCZSQLMethods from Dieter Maurer? Is anybody interested in a being early beta testers before I release it as Open Source? It works quite well here for me but I fear I'm getitng a bit home blind based on just my needs. I'm pretty sure it works but some early feedback would be appreciated. I'm interested, although I can't guarantee quick response :) -- Maciej Wisniowski ___ 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 Methods and transaction control
robert rottermann schrieb:
Hi there,
I am implementing a tool to handle userdata that is stored in a MySQL db.
The underlaying logic of the stored procedures used to maintain the db
content
dictate that I have to control the transactions myself.
Can you elaborate on that? I dont see a reason why you would
commit around zopes transaction handling (which would actually
be dangerous anyway - think of the retry mechanism in case of
database conflicts)
I understand that there is a way to handle transaction controll in the
ZMySQL
methods. However I find no documentation at all on how to use it.
Well most databases accept COMMIT as sql command.
Without using ZMySQL methods I would be using the following piece of code:
db = mysql.connect(host=localhost ...)
cursor = db.cursor()
query = CALL insertUser('%s', '$xxx$', @id, @error);select @id, @error
% 'JohnTheUser'
cursor.execute(query)
# get the next resultset, it has the result of the select
cursor.nextset()
result = cursor.fetchall()[0]
error = int(result[1])
if error:
print error %s when trying to add user %s % (ERRRORS[error],
'JohnTheUser')
db.rollback()
continue #we are in a loop actually
what does the loop?
..
db.commit()
How can I mimic that using ZSQL Methods
Are you adding users in a series? What exactly
are you doing? If its kind of migation, consider
running it as a script with zopectl run migationscript.py
once. There you could just write python code as you like
w/o interfering w/ Zopes session handling.
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 Methods and transaction control
Are you sure it doesn't do it out of the box?
I know it does for ZPsycopgDA which is the Postgresq Zope database adapter.
With that one I don't have to come near handling the SQL transactions.
What Zope commits and rollsback is done for SQL too automatically.
robert rottermann wrote:
Hi there,
I am implementing a tool to handle userdata that is stored in a MySQL db.
The underlaying logic of the stored procedures used to maintain the db
content
dictate that I have to control the transactions myself.
I understand that there is a way to handle transaction controll in the
ZMySQL
methods. However I find no documentation at all on how to use it.
Without using ZMySQL methods I would be using the following piece of code:
db = mysql.connect(host=localhost ...)
cursor = db.cursor()
query = CALL insertUser('%s', '$xxx$', @id, @error);select @id, @error
% 'JohnTheUser'
cursor.execute(query)
# get the next resultset, it has the result of the select
cursor.nextset()
result = cursor.fetchall()[0]
error = int(result[1])
if error:
print error %s when trying to add user %s % (ERRRORS[error],
'JohnTheUser')
db.rollback()
continue #we are in a loop actually
..
db.commit()
How can I mimic that using ZSQL Methods
thanks for any pointers
Robert
___
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 Methods and transaction control
robert rottermann wrote at 2007-1-10 09:00 +0100: ... I am implementing a tool to handle userdata that is stored in a MySQL db. The underlaying logic of the stored procedures used to maintain the db content dictate that I have to control the transactions myself. If you know what you do, then you can probably do something along the following lines. Almost surely, MySQL will provide some SQL command to control transaction. Maybe, they are called commit and rollback (as is the case for Postgres). In this case, you ZSQL method (or your stored procedure) can use it. In the ZSQL method, it could look like: --- some SQL --- dtml-var sql_delimiter commit -- Dieter ___ 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's Precision
In a PostgreSQL DB I have a Numeric data type with a precision of two decimal places. If I have 1.33 in as the result from my query, it's everything is good. However, if I have 1.30 it reduces the precision to one decimal place and I get 1.3. Not so good when dealing with currency ( I don't want to use the money data dype ). Any ideas on how I can get my ZSQL method to keep two decimal places? As others said it's a matter of displaying. You say that you don't want to use money datatype but keep in mind that postgre adapter is able to use Python's Decimal datatype for money values (in python2.3 you need additional module decimal.py from Python 2.4 to enable it) which may give you necessary precision for financial operations and is able to store really big numbers. -- Maciej Wisniowski ___ 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's Precision
Maciej Wisniowski schrieb: In a PostgreSQL DB I have a Numeric data type with a precision of two decimal places. If I have 1.33 in as the result from my query, it's everything is good. However, if I have 1.30 it reduces the precision to one decimal place and I get 1.3. Not so good when dealing with currency ( I don't want to use the money data dype ). Any ideas on how I can get my ZSQL method to keep two decimal places? As others said it's a matter of displaying. You say that you don't want to use money datatype but keep in mind that postgre adapter is able to use Python's Decimal datatype for money values (in python2.3 you need additional module decimal.py from Python 2.4 to enable it) which may give you necessary precision for financial operations and is able to store really big numbers. The adaptor should use it for numeric data as well (hey, hence the name ;) Use of the deprecated money datatype in postgres isnt recommended. (And its superceeded by numeric anyway) Regards Tino Wildenhain ___ 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's Precision
At Tuesday 24/10/2006 01:05, Jason C. Leach wrote: In a PostgreSQL DB I have a Numeric data type with a precision of two decimal places. If I have 1.33 in as the result from my query, it's everything is good. However, if I have 1.30 it reduces the precision to one decimal place and I get 1.3. Not so good when dealing with currency ( I don't want to use the money data dype ). Any ideas on how I can get my ZSQL method to keep two decimal places? I assume you get a float type from the DB. 1.30 *prints* as 1.3 but has around 12 decimal digits of precision. -- Gabriel Genellina Softlab SRL __ Correo Yahoo! Espacio para todos tus mensajes, antivirus y antispam ¡gratis! ¡Abrí tu cuenta ya! - http://correo.yahoo.com.ar ___ 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's Precision
Jason C. Leach wrote: Hi: In a PostgreSQL DB I have a Numeric data type with a precision of two decimal places. If I have 1.33 in as the result from my query, it's everything is good. However, if I have 1.30 it reduces the precision to one decimal place and I get 1.3. Not so good when dealing with currency ( I don't want to use the money data dype ). Any ideas on how I can get my ZSQL method to keep two decimal places? Thanks, Jason. 1.3 and 1.30 are different only in terms display and printing. One approach can be wrapping your calls to the zsql in a python script that applies formatting before returning the relavent results. res = yourZSQL(params or request is all set up) return $%.2f % res[0].yourAmountThatYouNeedToFORMAT ___ 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,
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 with different user
Alle 18:42, lunedì 19 dicembre 2005, hai scritto: Pier Luigi Fiorini wrote: Hello, I'm developing a Zope application that uses a PostgreSQL connection and several ZSQL objects. People should log in using a Postgres user and ZSQL object should be executed by the user that's logged in. Multiple people can be logged at the same time. Unfortunately it is not possible because the same Postgres connection is used by all the ZSQL object. Is there a way to change the user executing a ZSQL query? An alternative would be to log into the event.log some information (like the username, that's stored in the session) and the query source. Your alternative is dead easy. zLOG (or, better, the Python logging module, for which zLOG is now a facade) is quite easy to use. You can even make your own log file to contain only such events. I know _how_ to use zLOG. I just don't know how to log the query source. Is there some documentation about these things. I can find only documentation about making things like a simple form which is not so useful because you can learn it in some days. -- YACME S.r.l. Via del Mobiliere, 9 40138 Bologna Tel: +39 051 538709 Fax: +39 051 532399 [EMAIL PROTECTED] www.yacme.com pgp3kODOcvUzE.pgp Description: PGP signature ___ 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 with different user
Pier Luigi Fiorini wrote: Alle 18:42, lunedì 19 dicembre 2005, hai scritto: Pier Luigi Fiorini wrote: Hello, I'm developing a Zope application that uses a PostgreSQL connection and several ZSQL objects. People should log in using a Postgres user and ZSQL object should be executed by the user that's logged in. Multiple people can be logged at the same time. Unfortunately it is not possible because the same Postgres connection is used by all the ZSQL object. Is there a way to change the user executing a ZSQL query? An alternative would be to log into the event.log some information (like the username, that's stored in the session) and the query source. Your alternative is dead easy. zLOG (or, better, the Python logging module, for which zLOG is now a facade) is quite easy to use. You can even make your own log file to contain only such events. I know _how_ to use zLOG. I just don't know how to log the query source. Is there some documentation about these things. I can find only documentation about making things like a simple form which is not so useful because you can learn it in some days. That depends. What do you mean by query source? --jcc -- Building Websites with Plone http://plonebook.packtpub.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 with different user
Pier Luigi Fiorini schrieb: Hello, I'm developing a Zope application that uses a PostgreSQL connection and several ZSQL objects. People should log in using a Postgres user and ZSQL object should be executed by the user that's logged in. Multiple people can be logged at the same time. Unfortunately it is not possible because the same Postgres connection is used by all the ZSQL object. Is there a way to change the user executing a ZSQL query? Well good news and bad news... Good news first: yes it can be done Bad news: you would have to code it ;) You could base on the database adapter you currently have but organize your connection pool on a per user basis - so you need a connection with a user-folder as well (Interesting if you could subclass Userfolder and PsycopgDA...) so when a user logs in you look in the connection pool and/or authorize the user and use an idle/new connection for this user when ZSQL methods are called. Interesting project but not so easy. Also in worst case you could end up having zope-threads X users connection to your database. And connection setup is somewhat expensive, so if your pool is too small performance will suffer. 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 with different user
On Mon, Dec 19, 2005 at 03:39:51PM +0100, Tino Wildenhain wrote: Pier Luigi Fiorini schrieb: Hello, I'm developing a Zope application that uses a PostgreSQL connection and several ZSQL objects. People should log in using a Postgres user and ZSQL object should be executed by the user that's logged in. Multiple people can be logged at the same time. Unfortunately it is not possible because the same Postgres connection is used by all the ZSQL object. Is there a way to change the user executing a ZSQL query? Well good news and bad news... Good news first: yes it can be done Bad news: you would have to code it ;) You could base on the database adapter you currently have but organize your connection pool on a per user basis - so you need a connection with a user-folder as well (Interesting if you could subclass Userfolder and PsycopgDA...) so when a user logs in you look in the connection pool and/or authorize the user and use an idle/new connection for this user when ZSQL methods are called. Interesting project but not so easy. Also in worst case you could end up having zope-threads X users connection to your database. And connection setup is somewhat expensive, so if your pool is too small performance will suffer. The postgresql commandline tool psql has a command /user to change the details of the current connection. Wouldn't it be easier to use a command like that at the beginning of each database request? (I think that would still need some hacking in Userfolder and/or PsycopgDA, or even in Psyco itself...) -- __ Nothing is as subjective as reality Reinoud van Leeuwen[EMAIL PROTECTED] http://www.xs4all.nl/~reinoud __ ___ 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 with different user
--On 19. Dezember 2005 15:32:46 +0100 Pier Luigi Fiorini [EMAIL PROTECTED] wrote: Hello, I'm developing a Zope application that uses a PostgreSQL connection and several ZSQL objects. People should log in using a Postgres user and ZSQL object should be executed by the user that's logged in. Multiple people can be logged at the same time. Unfortunately it is not possible because the same Postgres connection is used by all the ZSQL object. Is there a way to change the user executing a ZSQL query? The short version: forget it. DA connections are tied to a particular user. Connections are persistent and shared across threads and requests. You really don't want to connect/re-connect for every request and user. You would have to implement your own connection management including connection pooling *somehow*. -aj pgpQhnV4SPuHW.pgp Description: PGP signature ___ 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 with different user
Am Montag, den 19.12.2005, 15:43 +0100 schrieb Reinoud van Leeuwen: The postgresql commandline tool psql has a command /user to change the details of the current connection. Wouldn't it be easier to use a command like that at the beginning of each database request? Well this \connect command really reconnecting. There is SET SESSION AUTHORIZATION but I'm not sure if it really helps. Regards Tino Wildenhain ___ 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 batching with dtml-in
Ed Colmar wrote: Will the database be queried for all results, and only have 20 displayed, or will the database just return the 20 that are needed? The database will get hammered every time ;-) There was a thread between myself and Charlie from eGenix on the zope-db list earlier this week... ...and stop using dtml! *grinz* Chris -- Simplistix - Content Management, Zope Python Consulting - http://www.simplistix.co.uk ___ 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 batching with dtml-in
Ed Colmar wrote: Do ZSQL methods communicate with the dtml-in call and understand the size attribute? for example, in this dtml-in statement: dtml-in SQL_get_large_list size=20 start=query_start /dtml-in Will the database be queried for all results, and only have 20 displayed, or will the database just return the 20 that are needed? Thanks for the insight! -Ed ___ Ed, Just add a Z Search Interface to a folder that can acquire your zSQL method(s). I think it will create a sample of what you want to do in either ZPT or DTML. Then you can experiment. You can use the advanced tab to tell zSqL methods how many data records to download per get and how many to cache. David ___ 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 batching with dtml-in
Am Donnerstag, den 08.12.2005, 13:46 -0800 schrieb Ed Colmar: Do ZSQL methods communicate with the dtml-in call and understand the size attribute? Not really. for example, in this dtml-in statement: dtml-in SQL_get_large_list size=20 start=query_start /dtml-in Will the database be queried for all results, and only have 20 displayed, or will the database just return the 20 that are needed? Well, this depends (in theory at least :) on the database adaptor. It could just fetch the results up to 20 but usually it will just fetch all and cut the batch out of it. This isnt so bad as it seems because you can have the ZSQL Method cache the result so the database isnt asked any time. You dont need DTML for batching. There is a batch module you can use in python scripts as well (and with ZPT) HTH 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: postgres, SELECT INTO
Has anybody selectively used SELECT INTO with zope/postgres? Make that succesfully. Zope version: Zope 2.1.6 (binary release, python 1.5.2, linux2-x86) Python version: 1.5.2 (#10, Dec 6 1999, 12:16:27) [GCC 2.7.2.3] System Platform: linux2 ZPyGreSQLDA-0-0-3 I'm trying something like: SELECT DISTINCT song, ipadress, date INTO TEMP d_requests FROM requests WHERE date dtml-sqlvar startdate type="string" dtml-var sql_delimiter SELECT COUNT(*) AS num_requests, song FROM d_requests GROUP BY song - works fine under psql zope raises an exceptions.ValueError and the db connection dies. traceback follows: !-- Traceback (innermost last): File /path/to/zope/lib/python/ZPublisher/Publish.py, line 214, in publish_module File /path/to/zope/lib/python/ZPublisher/Publish.py, line 179, in publish File /path/to/zope/lib/python/Zope/__init__.py, line 180, in zpublisher_exception_hook (Object: copy_of_getTopRequests) File /path/to/zope/lib/python/ZODB/Transaction.py, line 180, in begin File /path/to/zope/lib/python/ZODB/Transaction.py, line 155, in abort File /path/to/zope/lib/python/Shared/DC/ZRDB/THUNK.py, line 115, in abort File /path/to/zope/lib/python/Products/ZPyGreSQLDA/db.py, line 117, in _abort ValueError: PQsendQuery() -- There is no connection to the backend. -- ___ 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: postgres, SELECT INTO
+---[ Steve Drees ]-- | Has anybody selectively used SELECT INTO with zope/postgres? It's probably dying because there can only be ONE SELECT clause in a ZSQL Method. The SELECT ... INTO will be triggering a false positive. -- Totally Holistic Enterprises Internet| P:+61 7 3870 0066 | Andrew Milton The Internet (Aust) Pty Ltd | F:+61 7 3870 4477 | ACN: 082 081 472 ABN: 83 082 081 472 | M:+61 416 022 411 | Carpe Daemon PO Box 837 Indooroopilly QLD 4068|[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 )
[Zope] Calling ZSQL from PythonMethods (was RE: [Zope] ZSQL, Python methods and XMLRPC)ods and XMLRPC)
Hmm - with regard calling ZSQL method from PythonMethod, this works: def pythonmethod(self): a = [] for v in self.SQL_list_something(): a.append(v.field) return a dtml-var pythonmethod *But* of course I can't pass "self" over XMLRPC. Why does self.ZSQL_method() work, but: def pythonmethod(meth): a = [] for v in meth(): a.append(v.field) return a dtml-var "pythonmethod(ZSQL_method)" Give "__call__ not defined"? Regards, Phil +--+ | Phil Mayers, Network Support | | Centre for Computing Services| | Imperial College | +--+ -Original Message- From: Mayers, Philip J [mailto:[EMAIL PROTECTED]] Sent: 18 January 2001 22:37 To: '[EMAIL PROTECTED]' Subject: [Zope] ZSQL, Python methods and XMLRPC I've three questions (but I'm making progress!): 1) In 2.2.5, how can I call a ZSQL method from a Python Method - I'm passing the method in as a parameter, and the furthest I get says "__call__ not defined". Do PythonScripts obivate the need for this? I assume I can just do this: for record in context.folder.subfolder.SQL_method(val1='a',val2='b'): # do stuff Does that work? 2) When calling a DTML method over XMLRPC, dtml-in "SQL_method(username=AUTHENTICATED_USER)" doesn't work, but dtml-in "SQL_method(username=REQUEST.AUTHENTICATED_USER)" does. Why? 3) Why aren't record values (the return of an SQL method) marshallable over XMLRPC. I get this: cannot marshal extension class ExtensionClass at 70356ff4 objects Regards, Phil ___ 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 is killing me...
Yes it should if the one in the root is the "closest" or first connection it finds. Im using ZODBC and it works for me. -- Andy McKay, Developer. ActiveState. - Original Message - From: "Hugo Ramos" [EMAIL PROTECTED] To: "ZOPE" [EMAIL PROTECTED] Sent: Wednesday, November 08, 2000 4:00 AM Subject: [Zope] ZSQL is killing me... Yellow ppl, I think everybody already had this problem... Suppose you have a DB connection in /root/subfolder/DB_conn then you build lots of zsql objects from that point down... then you move the DB connection object up to the root. result: all ZSQL's have to be changed to assume the DB_conn new position. shouldn't the zsql's assume right away that the upper DB_conn in the root folder is the right one?? regards = Hugo Ramos - [EMAIL PROTECTED] ZopersORG - http://www.zopers.org = Do not meddle in the affairs of programmers, for they are easy to annoy, and have all the source code!!! ___ 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 Strange Error
Cesar A. K. Grossmann wrote: Hi! I'm trying to emulate an outter join in a Z SQL, but Zope crashes every time I try the query. ... What is wrong? What I can do to get the job done (I need to fill an multiselect with 'codigo', 'nome' from 'cad_exportacao', and if exists any 'codigo' for 'username' in 'user_codigo', the option must be selected - I tryed the "Filling MULTIPLE SELECT ... HOWTO', but it doesn't works for me, and I don't know why)? I'm not sure why this should *crash* zope, but you can do it with a much simpler query and a little DTML coding. The following should work (UNTESTED): Z SQL Method (named sql_query): select codigo, nome, tipo from cad_exportacao; DTML Method: select dtml-in sql_query option value="dtml-codigo;" dtml-if "tipo=='E'"Selected/dtml-if dtml-var nome/option /dtml-in /select hth, Casey D. ___ 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 Strange Error
I'm not sure why this should *crash* zope, but you can do it with a much simpler query and a little DTML coding. The following should work (UNTESTED): It cannot help me. I have two tables. One called cad_exportacao, and the other is called user_codigo. The table user_codigo maps every username with one or more rows at cad_exportacao. I wanna fill a SELECT input with all rows in cad_exportacao, but wanna all the records that exists for 'username' in user_codigo to be selected. There was the howto that explains how to do this at http://www.zope.org/Members/Roug/select_with_multiple, but it not works for me... []s -- César A. K. Grossmann http://members.xoom.com/ckant/ ___ 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 Strange Error
Duh, maybe I should learn to read. Maybe try this sql: select codigo, nome, case when codigo in select codigo from user_codigo where username = dtml-sqlvar username type=string and tipo = 'E' ) then 'selected' else null end as select_attr from cad_exportacao; Then your dtml method: select dtml-in sql_method option value="dtml-codigo" dtml-var select_attr missingdtml-var nome/option /dtml-in /select Let me know if this is any better, Casey D. -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] Sent: Thursday, November 02, 2000 12:08 PM To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Subject: Re: [Zope] ZSql Strange Error I'm not sure why this should *crash* zope, but you can do it with a much simpler query and a little DTML coding. The following should work (UNTESTED): It cannot help me. I have two tables. One called cad_exportacao, and the other is called user_codigo. The table user_codigo maps every username with one or more rows at cad_exportacao. I wanna fill a SELECT input with all rows in cad_exportacao, but wanna all the records that exists for 'username' in user_codigo to be selected. There was the howto that explains how to do this at http://www.zope.org/Members/Roug/select_with_multiple, but it not works for me... []s -- César A. K. Grossmann http://members.xoom.com/ckant/ ___ 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 Strange Error
Casey Duncan wrote: Duh, maybe I should learn to read. :-) Let me know if this is any better, It worked! Thanks. []s -- César A. K. Grossmann http://members.xoom.com/ckant/ ___ 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'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 question
I have a table with lots of data in my Gadfly database. I would like to add a couple of new columns to that table. Is it at all possible to add columns to existing table or do I have to create a new table all together? Nope, ALTER TABLE TABLE_NAME ADD COLUMN_NAME FORMAT_TYPE i think should do it... Let me check ALTER TABLE memberlist ADD name VARCHAR Yep... that does it. Although I had tested this in PGSQL, Im sure it works for GadFly also. Since you are also inserting a new column you may want to initialize all current reocrds with a value, but I cant remember what it was... If you really need it, let me know, I'll dig in for you. Cheers, -- Paz Oratrix Development BV http://www.oratrix.com GRiNS SMIL Editor - ___ 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
Tom Deprez writes:
... nested Z SQL method call ...
I do not understand fully, what you want to do.
But the following FAQ may also be your problem:
Z SQL methods do *NOT* use the DTML namespace
for parameters.
You must either pass the parameters explicitly:
dtml-in "sql_method(param1=val1, ..., paramn=valn)"
or put the parameters in the REQUEST object:
dtml-call "REQUEST.set('param1', val1)"
dtml-call "REQUEST.set('paramn', valn)"
dtml-in sql_method
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
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-Methods: last inserted id
You can get a ZSQLMethod that's used for inserting to return the last inserted ID by doing the following: INSERT ...whatever... dtml-var sql_delimiter SELECT LAST_INSERT_ID() AS newid You get the results as you would with any ZSQLMethod: dtml-in my_zsql_method Inserted ID: dtml-var newid /dtml-in ___ Ron Bickers Logic Etc, Inc. [EMAIL PROTECTED] -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of Andy Sent: Wednesday, May 17, 2000 7:45 PM To: [EMAIL PROTECTED] Subject: [Zope] ZSQL-Methods: last inserted id I wonder how I can find out about the last inserted id of a row in my mysql-database, which I inserted through a ZSQL-Method... I tried mysql's built-in function LAST_INSERT_ID(), but that did not solve my problem... ___ 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??/
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 )
