Re: [Zope-DB]found reason: sql query works in database connector but not in ZSQL method
Maciej Wisniowski wrote: >> what I now do as an interim solution, I call the "read only" procedures >> directly from python. >> procedures that alter data i call from a zope connector. >> >> > What do you mean by 'directly from python' and 'from a zope connector'? > directly from python: I mean I read the database using a MySQLdb call a zope connector: I mean a ZSQL object (I am currently testing mxODBC Zope DA) >> I would be glad to here of any reason not to do this. >> > First I need to understand what and how you're doing. Possible > issues may be with transactional integrity. > > I do only read data to display and do not store it. do you still think that could affect integrity. robert begin:vcard fn:robert rottermann n:rottermann;robert email;internet:[EMAIL PROTECTED] tel;work:031 333 10 20 tel;fax:031 333 10 23 tel;home:031 333 36 03 x-mozilla-html:FALSE version:2.1 end:vcard ___ Zope-DB mailing list Zope-DB@zope.org http://mail.zope.org/mailman/listinfo/zope-db
Re: [Zope-DB]found reason: sql query works in database connector but not in ZSQL method
Am 18.01.2007, 17:58 Uhr, schrieb robert rottermann <[EMAIL PROTECTED]>: I think I found the reason, for my problems with stored procedures. it seems that on can not call a stored MySQL 5.0x procedure from zope that returns a data set. If you use the mxODBC Zope DA callproc() method then you can read the last result set returned by a procedure. when I call the body of the stored procedure everything works fine. what I now do as an interim solution, I call the "read only" procedures directly from python. procedures that alter data i call from a zope connector. I would be glad to here of any reason not to do this. Yes, if you really do something outside of Zope then you are compromising your transactional integrity. Charlie ___ Zope-DB mailing list Zope-DB@zope.org http://mail.zope.org/mailman/listinfo/zope-db
Re: [Zope-DB]found reason: sql query works in database connector but not in ZSQL method
> what I now do as an interim solution, I call the "read only" procedures > directly from python. > procedures that alter data i call from a zope connector. > What do you mean by 'directly from python' and 'from a zope connector'? > I would be glad to here of any reason not to do this. First I need to understand what and how you're doing. Possible issues may be with transactional integrity. -- Maciej Wisniowski ___ Zope-DB mailing list Zope-DB@zope.org http://mail.zope.org/mailman/listinfo/zope-db
Re: [Zope-DB]found reason: sql query works in database connector but not in ZSQL method
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 hi there, I think I found the reason, for my problems with stored procedures. it seems that on can not call a stored MySQL 5.0x procedure from zope that returns a data set. when I call the body of the stored procedure everything works fine. what I now do as an interim solution, I call the "read only" procedures directly from python. procedures that alter data i call from a zope connector. I would be glad to here of any reason not to do this. thanks for your help. robert -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.5 (GNU/Linux) Comment: Using GnuPG with SUSE - http://enigmail.mozdev.org iD8DBQFFr6c9GaryJ0T9kUYRAlkiAJ9TZ3rKw3iiTHuZ1fiQuJE6Sdoa0wCfZr63 uNOpVr8pc+rYV6jwbZ9l6sI= =p3jD -END PGP SIGNATURE- ___ Zope-DB mailing list Zope-DB@zope.org http://mail.zope.org/mailman/listinfo/zope-db
Re: [Zope-DB] How lunch SQL request from python script ?
Am 18.01.2007, 11:52 Uhr, schrieb CERETTO Thierry <[EMAIL PROTECTED]>: select distinct c.id_risque, c.libelle_risque, c.id_fiche, c.id_portee, c.url_info from (mots a join amotrisque b on a.id_mot = b.id_mot ) join risques c on b.id_risque = c.id_risque where a.mot_MAJ like '%@@CRITERE1@@%' union select distinct c.id_risque, c.libelle_risque, c.id_fiche, c.id_portee, c.url_info from (mots a join amotrisque b on a.id_mot = b.id_mot ) join risques c on b.id_risque = c.id_risque where a.mot_MAJ like '%@@CRITERE2@@%' union *** where "%@@CRITERE1@@'" , %@@CRITERE2@@, ..., are words filled in textfield (separate by a space). If you are simply adding results of the same query but different results then do not use UNION!!! Run the same query three times with different parameters and simply add the results together in a script. Your ZSQL should look something like this select distinct c.id_risque, c.libelle_risque, c.id_fiche, c.id_portee, c.url_info from (mots a join amotrisque b on a.id_mot = b.id_mot ) join risques c on b.id_risque = c.id_risque where a.mot_MAJ like # or use if you prefer call this from a PythonScript criteria = ['n'import quoi', 'depardieu', 'segolene', 'sarko'] results = [] for c in criteria: results = context.ZSQLMethod(CRITERE=c) This will work for fairly simple SQL but if you want to create your SQL dynamically (and I don't think this is such a good idea) then you should use a different template than ZSQL. With our mxODBC Zope DA you can pass your SQL directly into the execute method of a DA connection object. Charlie ___ Zope-DB mailing list Zope-DB@zope.org http://mail.zope.org/mailman/listinfo/zope-db
Re: [Zope-DB] How lunch SQL request from python script ?
> 2) I think to do that, I must to use a python script, I have a piece
> of code (above) but I don't know to connect MySql database and I don't
> know how to lunch my SQL query in python L (and I suppose which I can
> generate a HTML results in this same python script OR it's better to
> call a Page template to display results?):
>
1. You may read some about dtml (especially loops) and try to write your
ZSQLMethod that gets one parameter (with value of textfield.split(' ')
for example) and loops by it to generate proper SQL.
2. Create ZSQLMethod 'myzsqlmethod' that takes one sql parameter like
'myquery' and it's content is:
.
Then call it like:
context.myzsqlmethod(myquery=sqlquery)
where sqlquery is string generated by code below:
> for value in textfield.split(' '):
>
> sqlquery.append("select distinct c.id_risque, c.libelle_risque,
> c.id_fiche, c.id_portee, c.url_info \
>
> from (mots a join amotrisque b on a.id_mot =
> b.id_mot) \
>
> join risques c on b.id_risque = c.id_risque \
>
> where a.mot_MAJ like '%%%s%%' " % value)
>
> sqlquery = " union ".join(sqlquery)
>
In this case you must be very carefull because:
1. this ZSQLMethod is a high security risk, and should be secured with
proper permissions and called by script with proper proxy roles
2. there may be possiblity for SQLInjection in your script above (say
somebody will write something harmful to textfield)
>
> I read many things on web, I try, but without success, example, in
> ZMI, in python script object, I write this:
>
> # "my_base" is a "Z MySQL database connection"
>
> bd = context.my_base()
>
> curs = bd.connection.cursor()
>
> but i get a login box
>
Recently, there were few questions about managing transactions from
ZSQLMethods etc. Search
for these posts. There were examples how to call your sql directly. In
general, you should use External
method.
--
Maciej Wisniowski
___
Zope-DB mailing list
Zope-DB@zope.org
http://mail.zope.org/mailman/listinfo/zope-db
Re: [Zope-DB] How lunch SQL request from python script ?
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
Thierry,
I think all problems you describe can be solved with ZSQL easily.
the best thing for yo is to grap the documentation on how to do that.
you find it here
http://www.plope.com/Books/2_7Edition/RelationalDatabases.stx
look into it, and if you find some of your questions not answered I'll
gladly help (if I can)
robert
CERETTO Thierry schrieb:
> Hi and all my wishes for you for 2007,
>
>
>
>
>
> Zope 2.8.6-final, python 2.3.5, win32, ZMySQLDA, MySQL for python.
>
>
>
> In ZMI (I know, it's not very well but I'm a newbie…), I use "Z MySQL
> database connection", "Z SQL method", "Page Template", etc, that's works
> fine but in "Z SQL method" I use only a simple SQL query, like this :
>
>
>
> ***
>
> select distinct c.id_risque, c.libelle_risque, c.id_fiche, c.id_portee,
> c.url_info
>
> from (mots a join amotrisque b on a.id_mot = b.id_mot )
>
> join risques c on b.id_risque = c.id_risque
>
> where a.
>
>
>
> ***
>
>
>
> But now, I need to submit a dynamic SQL query something like this:
>
>
>
>
>
> ***
>
> select distinct c.id_risque, c.libelle_risque, c.id_fiche, c.id_portee,
> c.url_info
>
> from (mots a join amotrisque b on a.id_mot = b.id_mot )
>
> join risques c on b.id_risque = c.id_risque
>
> where a.mot_MAJ like '%@@CRITERE1@@%'
>
> union
>
> select distinct c.id_risque, c.libelle_risque, c.id_fiche, c.id_portee,
> c.url_info
>
> from (mots a join amotrisque b on a.id_mot = b.id_mot )
>
> join risques c on b.id_risque = c.id_risque
>
> where a.mot_MAJ like '%@@CRITERE2@@%'
>
> union
>
>
>
>
>
> ***
>
>
>
> where "%@@CRITERE1@@'" , %@@CRITERE2@@, …, are words filled in
> textfield (separate by a space).
>
>
>
>
>
> 1) From ONE argument, it's possible to generate SQL dynamic request in
> "Z SQL method, if yes, how? (I think that the answer is NO but I'm not sure)
>
>
>
> 2) I think to do that, I must to use a python script, I have a piece of
> code (above) but I don't know to connect MySql database and I don't know
> how to lunch my SQL query in python L (and I suppose which I can
> generate a HTML results in this same python script OR it's better to
> call a Page template to display results?):
>
>
>
> for value in textfield.split(' '):
>
> sqlquery.append("select distinct c.id_risque, c.libelle_risque,
> c.id_fiche, c.id_portee, c.url_info \
>
> from (mots a join amotrisque b on a.id_mot = b.id_mot) \
>
> join risques c on b.id_risque = c.id_risque \
>
> where a.mot_MAJ like '%%%s%%' " % value)
>
> sqlquery = " union ".join(sqlquery)
>
>
>
> I read many things on web, I try, but without success, example, in ZMI,
> in python script object, I write this:
>
> # "my_base" is a "Z MySQL database connection"
>
> bd = context.my_base()
>
> curs = bd.connection.cursor()
>
>
>
> but i get a login box
>
>
>
> How to connect a Mysql base and run my dynamic SQL request?
>
>
>
> Have you an example so that I can start and understand how this works.
>
>
>
> Thank you very much!
>
>
>
> A desperate French guy L L
>
>
>
> Thierry
>
>
>
>
>
>
>
>
>
>
>
>
>
>
> ___
> Zope-DB mailing list
> Zope-DB@zope.org
> http://mail.zope.org/mailman/listinfo/zope-db
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.5 (GNU/Linux)
Comment: Using GnuPG with SUSE - http://enigmail.mozdev.org
iD8DBQFFr2ETGaryJ0T9kUYRAgeeAKCLS5QxERJYw3+Zl+pUkERhrYlTFQCdGs+j
6SaqgT3khOrNeld3jUxgLNo=
=f2MP
-END PGP SIGNATURE-
___
Zope-DB mailing list
Zope-DB@zope.org
http://mail.zope.org/mailman/listinfo/zope-db
[Zope-DB] How lunch SQL request from python script ?
Hi and all my wishes for you for 2007,
Zope 2.8.6-final, python 2.3.5, win32, ZMySQLDA, MySQL for python.
In ZMI (I know, it's not very well but I'm a newbie...), I use "Z MySQL
database connection", "Z SQL method", "Page Template", etc, that's works
fine but in "Z SQL method" I use only a simple SQL query, like this :
***
select distinct c.id_risque, c.libelle_risque, c.id_fiche, c.id_portee,
c.url_info
from (mots a join amotrisque b on a.id_mot = b.id_mot )
join risques c on b.id_risque = c.id_risque
where a.
***
But now, I need to submit a dynamic SQL query something like this:
***
select distinct c.id_risque, c.libelle_risque, c.id_fiche, c.id_portee,
c.url_info
from (mots a join amotrisque b on a.id_mot = b.id_mot )
join risques c on b.id_risque = c.id_risque
where a.mot_MAJ like '%@@CRITERE1@@%'
union
select distinct c.id_risque, c.libelle_risque, c.id_fiche, c.id_portee,
c.url_info
from (mots a join amotrisque b on a.id_mot = b.id_mot )
join risques c on b.id_risque = c.id_risque
where a.mot_MAJ like '%@@CRITERE2@@%'
union
***
where "%@@CRITERE1@@'" , %@@CRITERE2@@, ..., are words filled in
textfield (separate by a space).
1) From ONE argument, it's possible to generate SQL dynamic request in
"Z SQL method, if yes, how? (I think that the answer is NO but I'm not
sure)
2) I think to do that, I must to use a python script, I have a piece of
code (above) but I don't know to connect MySql database and I don't know
how to lunch my SQL query in python :-( (and I suppose which I can
generate a HTML results in this same python script OR it's better to
call a Page template to display results?):
for value in textfield.split(' '):
sqlquery.append("select distinct c.id_risque, c.libelle_risque,
c.id_fiche, c.id_portee, c.url_info \
from (mots a join amotrisque b on a.id_mot =
b.id_mot) \
join risques c on b.id_risque = c.id_risque \
where a.mot_MAJ like '%%%s%%' " % value)
sqlquery = " union ".join(sqlquery)
I read many things on web, I try, but without success, example, in ZMI,
in python script object, I write this:
# "my_base" is a "Z MySQL database connection"
bd = context.my_base()
curs = bd.connection.cursor()
but i get a login box
How to connect a Mysql base and run my dynamic SQL request?
Have you an example so that I can start and understand how this works.
Thank you very much!
A desperate French guy :-( :-(
Thierry
___
Zope-DB mailing list
Zope-DB@zope.org
http://mail.zope.org/mailman/listinfo/zope-db
