[web2py] Re: Problem with oracle query
Thanks for posting your solution. I ran into the same problem and probably wouldn't have figured it out without your post. - Tom On Monday, April 15, 2019 at 5:20:36 AM UTC-6, gliporace wrote: > > I think I found the problem: > the query is sent with the table name double-quoted: > > select "ana_paz"."COGNOME", "ana_paz"."NOME" from "ana_paz" where > "ana_paz"."COGNOME"='ROSSI' <--- doesn't not work ("table or view does > not exists") > > select ana_paz."COGNOME", ana_paz."NOME" from "ana_paz" where > ana_paz."COGNOME"='ROSSI' < works > > Setting > entity_quoting = False > > in the database connection solved the problem. > > > > Il giorno venerdì 12 aprile 2019 17:00:14 UTC+2, gliporace ha scritto: >> >> Hi, >> I just updated my web2py installation from 2.11 to 2.18.5, but the apps >> who make use of cx_Oracle for connecting to a Oracle database show this >> error >> when ther try to query a table/view: >> >> Exception ORA-00942: table or view does not exist >> >> The old web2py installation run without problems on a Ubuntu server 14.04 >> and cx_Oracle 5.3, the new installation is on Ubuntu server 16.04 with the >> same version of cx_Oracle. >> >> Is there any means to check what is the query before is sent to the >> server? >> > -- Resources: - http://web2py.com - http://web2py.com/book (Documentation) - http://github.com/web2py/web2py (Source code) - https://code.google.com/p/web2py/issues/list (Report Issues) --- You received this message because you are subscribed to the Google Groups "web2py-users" group. To unsubscribe from this group and stop receiving emails from it, send an email to web2py+unsubscr...@googlegroups.com. To view this discussion on the web visit https://groups.google.com/d/msgid/web2py/20c1d130-f770-4361-9403-9e4bcd30e3aa%40googlegroups.com.
[web2py] Re: Web2Py and ldap integration
I have used ldap_auth successfully by implementing the following in models/db.py: from gluon.contrib.login_methods.ldap_auth import ldap_auth ... db = DAL('mysql://mydb:mydb@localhost/password') ... # --- end of web2py scaffolding --- ... auth.settings.login_methods.append(ldap_auth(mode='ad', server='dc1.mydomain.com',base_dn='ou=users,dc=mydomain,dc=com')) auth.settings.login_methods.append(ldap_auth(mode='ad', server='dc1.mydomain.com',base_dn='ou=operators,dc=mydomain,dc=com')) ... db2 = DAL('oracle://myextradb/account@password', migrate=False) I found that I had to make extra database definitions after the append statements or I received a segmentation fault. I suspect that you could add another append statement to add an additional domain controller but I haven't tested this. Hopefully this helps you - Tom On Friday, July 19, 2019 at 11:30:00 PM UTC-6, Davidiam wrote: > > Hello, > > I have read and implemented the recipe for Windows active directory ldap > integration with Web2Py ( > http://www.web2py.com/books/default/chapter/29/09/access-control) and for > the most part the documentation is clear except for the last bit where it > refers to : > > from gluon.contrib.login_methods.ldap_auth import ldap_auth > auth.settings.login_methods.append(ldap_auth(mode='ad', >server='my.domain.controller', >base_dn='ou=Users,dc=domain,dc=com')) > > For me there are 2 things that aren't clear here : > 1) Where should this code be inserted ? In the default.py controller or > in db.py or somewhere else ? > 2) We have multiple domain controllers in our domain, do we need to supply > a specific server for the server parameter ? > > An example of implementing this with the welcome application would be > appreciated, specifying the file which was modified. > -- Resources: - http://web2py.com - http://web2py.com/book (Documentation) - http://github.com/web2py/web2py (Source code) - https://code.google.com/p/web2py/issues/list (Report Issues) --- You received this message because you are subscribed to the Google Groups "web2py-users" group. To unsubscribe from this group and stop receiving emails from it, send an email to web2py+unsubscr...@googlegroups.com. To view this discussion on the web visit https://groups.google.com/d/msgid/web2py/62b48bac-e85d-4ce7-94c4-637dfcf41aa3%40googlegroups.com.
[web2py] Re: please help testing web3py
I installed py4web on centos 7 in a virtual python environment and it works fine. http://127.0.0.1:8000/_dashboard works as advertised, however http://localhost:8000/_dashboard doesn't. When using localhost, the dashboard page comes up, but it won't display any of the installed applications or any tickets. I followed the documentation to add a session counter. The code works when using http://127.0.0.1/myapp but not when using http://localhost/myapp. (localhost is defined as 127.0.0.1 on my system) Most of my tests work with http://localhost/myapp/* but some don't. I'm not sure if this is a problem or not. - Tom On Saturday, July 6, 2019 at 9:57:26 PM UTC-6, Massimo Di Pierro wrote: > > Let's test the dashboard today: > > # download web2py from pypi > > python3 -m pip install web3py > > # start it: > > web3py-start myapps > > (pick a one time password and answer yes when it wants to create > something, it will create myapps folder and a temp .web3py-service folder) > > # open browser > > http://127.0.0.1:8000/_dashboard > > Now create a new app by cloning scaffold > > Does it work? > How does it flow? > What explanations should be necessary? > Can you write a brief documentation of your experience for other users? > > > -- Resources: - http://web2py.com - http://web2py.com/book (Documentation) - http://github.com/web2py/web2py (Source code) - https://code.google.com/p/web2py/issues/list (Report Issues) --- You received this message because you are subscribed to the Google Groups "web2py-users" group. To unsubscribe from this group and stop receiving emails from it, send an email to web2py+unsubscr...@googlegroups.com. To view this discussion on the web visit https://groups.google.com/d/msgid/web2py/0a90e92e-24b5-4caa-84a9-d842ff888290%40googlegroups.com.
[web2py] RSA Securid integration with web2py
Hi, Has anyone integrated web2py authentication with RSA Securid? If you have, I'd appreciate it if you could share the steps you took. - Thanks in advance, - Tom -- Resources: - http://web2py.com - http://web2py.com/book (Documentation) - http://github.com/web2py/web2py (Source code) - https://code.google.com/p/web2py/issues/list (Report Issues) --- You received this message because you are subscribed to the Google Groups "web2py-users" group. To unsubscribe from this group and stop receiving emails from it, send an email to web2py+unsubscr...@googlegroups.com. For more options, visit https://groups.google.com/d/optout.
[web2py] Re: How can I access functions from multiple files in the controllers directory?
Hi, Thanks for your reply. I tried using modules as you suggested. It works great for simple python functions, but when I try to use DAL it dies. It appears that functions in modules aren't aware of the model definitions. Chapter 4 of the manual indicates that I could probably do this by importing exec_environment but I think I will just put my DAL code back in controllers/default.py. - Tom On Tuesday, November 1, 2016 at 10:03:30 PM UTC-6, 黄祥 wrote: > > just an idea why not put it on modules? > e.g. > *controllers/default.py* > import file1 > > a = file1.function1(x, y) > b = file1.function2(y, z) > > *modules/file1.py* > def function1(value1, value2): > code > return > > def function2(value1, value2): > code > return > > best regards, > stifan > -- Resources: - http://web2py.com - http://web2py.com/book (Documentation) - http://github.com/web2py/web2py (Source code) - https://code.google.com/p/web2py/issues/list (Report Issues) --- You received this message because you are subscribed to the Google Groups "web2py-users" group. To unsubscribe from this group and stop receiving emails from it, send an email to web2py+unsubscr...@googlegroups.com. For more options, visit https://groups.google.com/d/optout.
[web2py] How can I access functions from multiple files in the controllers directory?
Hi, In the controllers directory I wanted to define some functions in another file. I had hoped to import the functions to make them available to default.py but I haven't been able to get this to work. I'm hoping that someone can point out my error, and possibly a solution. In the controllers directory there is default.py and file1.py --- default.py --- ... from file1 import * ... a = function1(x,y) ... b = function2(y,z) ... --- file1.py --- ... def function1(value1,value2): code return def function2(value1,value2): code return ImportError: No module named file1 (I defined __init__.py in controllers, but this didn't help) Any pointers would be appreciated - Tom -- Resources: - http://web2py.com - http://web2py.com/book (Documentation) - http://github.com/web2py/web2py (Source code) - https://code.google.com/p/web2py/issues/list (Report Issues) --- You received this message because you are subscribed to the Google Groups "web2py-users" group. To unsubscribe from this group and stop receiving emails from it, send an email to web2py+unsubscr...@googlegroups.com. For more options, visit https://groups.google.com/d/optout.
[web2py] Re: Code change in oracle.py to allow joins in pagination.
I downloaded the latest release, 2.14.6, and downloaded my patch file from this post and applied it against oracle.py: cd gluon/packages/dal/pydal/adapters patch < oracle.patch It created the oracle.py I expected and it runs correctly. Are you referring to a different version of web2py? - Tom On Monday, October 3, 2016 at 1:29:59 PM UTC-6, Massimo Di Pierro wrote: > > Sorry the patch does not work with the latest pydal/adapters/oracle.py. > Can I ask you to resubmit it? > > On Sunday, 25 September 2016 20:24:48 UTC-5, tomt wrote: >> >> Hi, I've attached the oracle.patch file for gluon/packages/dal/pydal/ >> adapters.oracle.py >> >> - Tom >> >> On Saturday, September 24, 2016 at 9:25:06 PM UTC-6, Massimo Di Pierro >> wrote: >>> >>> I can take care of it. Can you email me your patch as an attachment? >>> >>> On Friday, 23 September 2016 19:46:34 UTC-5, tomt wrote: I'd be happy to give it a try, but I'm not sure what steps to take. > When I go to github and click on create new pull request it appears I > have > to select a branch to compare to. I'm uncertain if I am supposed to > select > admin, experimental, or enter a value of my own. Does web2py have any > suggested steps, or a guideline for using github? > - Tom >>> -- Resources: - http://web2py.com - http://web2py.com/book (Documentation) - http://github.com/web2py/web2py (Source code) - https://code.google.com/p/web2py/issues/list (Report Issues) --- You received this message because you are subscribed to the Google Groups "web2py-users" group. To unsubscribe from this group and stop receiving emails from it, send an email to web2py+unsubscr...@googlegroups.com. For more options, visit https://groups.google.com/d/optout.
[web2py] Re: Code change in oracle.py to allow joins in pagination.
Hi, I've attached the oracle.patch file for gluon/packages/dal/pydal/adapters.oracle.py - Tom On Saturday, September 24, 2016 at 9:25:06 PM UTC-6, Massimo Di Pierro wrote: > > I can take care of it. Can you email me your patch as an attachment? > > On Friday, 23 September 2016 19:46:34 UTC-5, tomt wrote: >> >> I'd be happy to give it a try, but I'm not sure what steps to take. When >>> I go to github and click on create new pull request it appears I have to >>> select a branch to compare to. I'm uncertain if I am supposed to select >>> admin, experimental, or enter a value of my own. Does web2py have any >>> suggested steps, or a guideline for using github? >>> >> >> - Tom >> > -- Resources: - http://web2py.com - http://web2py.com/book (Documentation) - http://github.com/web2py/web2py (Source code) - https://code.google.com/p/web2py/issues/list (Report Issues) --- You received this message because you are subscribed to the Google Groups "web2py-users" group. To unsubscribe from this group and stop receiving emails from it, send an email to web2py+unsubscr...@googlegroups.com. For more options, visit https://groups.google.com/d/optout. --- oracle.py 2016-05-09 18:21:47.0 -0600 +++ oracle.py.new 2016-09-25 19:01:04.0 -0600 @@ -67,7 +67,30 @@ sql_w_row = sql_w + ' AND w_row > %i' % lmin else: sql_w_row = 'WHERE w_row > %i' % lmin -return 'SELECT %s %s FROM (SELECT w_tmp.*, ROWNUM w_row FROM (SELECT %s FROM %s%s%s) w_tmp WHERE ROWNUM<=%i) %s %s %s;' % (sql_s, sql_f, sql_f, sql_t, sql_w, sql_o, lmax, sql_t, sql_w_row, sql_o) + +# remove blanks from sql_f +mysql_f = sql_f.replace(" ","") +# split into list +myfields = mysql_f.split(",") +select1 = "SELECT /*+ FIRST_ROWS(10) */" +select1 = "SELECT" +select2 = "SELECT" +select3 = "SELECT" +for i in range(len(myfields)): +#select1 += ' c%s "%s",' % (i,myfields[i]) #Full field names are not required +select1 += ' c%s,' % (i) +select2 += ' w_tmp.c%s,' % (i) +select3 += ' %s c%s,' % (myfields[i],i) +# remove trailing , +select1 = select1.rstrip(",") +select3 = select3.rstrip(",") +mysql = "%s\nFROM (\n %s ROWNUM rn\n FROM (\n%s" % (select1,select2,select3) +mysql += "\nFROM %s\n%s\n%s" % (sql_t,sql_w,sql_o) +mysql += "\n ) w_tmp\n WHERE ROWNUM <= %s\n)WHERE rn > %s\n" % (limitby[1],limitby[0]) +#print "mysql:%s" % (mysql) +return mysql +#return 'SELECT %s %s FROM (SELECT w_tmp.*, ROWNUM w_row FROM (SELECT %s FROM %s%s%s) w_tmp WHERE ROWNUM<=%i) %s %s %s;' % (sql_s, sql_f, sql_f, sql_t, sql_w, sql_o, lmax, sql_t, sql_w_row, sql_o) + return 'SELECT %s %s FROM %s%s%s;' % (sql_s, sql_f, sql_t, sql_w, sql_o) def constraint_name(self, tablename, fieldname):
[web2py] Re: Code change in oracle.py to allow joins in pagination.
> > I'd be happy to give it a try, but I'm not sure what steps to take. When > I go to github and click on create new pull request it appears I have to > select a branch to compare to. I'm uncertain if I am supposed to select > admin, experimental, or enter a value of my own. Does web2py have any > suggested steps, or a guideline for using github? > - Tom -- Resources: - http://web2py.com - http://web2py.com/book (Documentation) - http://github.com/web2py/web2py (Source code) - https://code.google.com/p/web2py/issues/list (Report Issues) --- You received this message because you are subscribed to the Google Groups "web2py-users" group. To unsubscribe from this group and stop receiving emails from it, send an email to web2py+unsubscr...@googlegroups.com. For more options, visit https://groups.google.com/d/optout.
[web2py] Code change in oracle.py to allow joins in pagination.
Hello, I have patched the select_limitby routine in oracle.py to allow for successful pagination when joins are used. This is an example of the sql it generates: SELECT c0 "STATUSPOINT.POINTNUMBER", c1 "STATUSPOINT.POINTNAME", c2 "AOR.REFERENCENAME", c3 "AOR.AOR" FROM ( SELECT w_tmp.c0, w_tmp.c1, w_tmp.c2, w_tmp.c3, ROWNUM rn FROM ( SELECT STATUSPOINT.POINTNUMBER c0, STATUSPOINT.POINTNAME c1, AOR.REFERENCENAME c2, AOR.AOR c3 FROM AOR, STATUSPOINT WHERE (STATUSPOINT.POINTACCESSAREA = AOR.AOR) ORDER BY STATUSPOINT.POINTNUMBER ) w_tmp WHERE ROWNUM <= 20 )WHERE rn > 10 The sql I used is based on a suggestion from https://blog.jooq.org/2014/06/09/stop-trying-to-emulate-sql-offset-pagination-with-your-in-house-db-framework/ It works successfully in my initial tests, but I realize that it's possible that this change may cause some problems that I haven't tested for. I'm hopeful that this change may be considered for implementation into the official web2py code. Please let me know if there is anything that I can do to assist in this process. ... gluon/packages/dal/pydal/adapters/oracle.py ... def select_limitby(self, sql_s, sql_f, sql_t, sql_w, sql_o, limitby): if limitby: (lmin, lmax) = limitby if len(sql_w) > 1: sql_w_row = sql_w + ' AND w_row > %i' % lmin else: sql_w_row = 'WHERE w_row > %i' % lmin # start of my code changes # remove blanks from sql_f mysql_f = sql_f.replace(" ","") # split into lists myfields = mysql_f.split(",") select1 = "SELECT" select2 = "SELECT" select3 = "SELECT" for i in range(len(myfields)): select1 += ' c%s "%s",' % (i,myfields[i]) select2 += ' w_tmp.c%s,' % (i) select3 += ' %s c%s,' % (myfields[i],i) # remove trailing ',' select1 = select1.rstrip(",") select3 = select3.rstrip(",") mysql = "%s\nFROM (\n %s ROWNUM rn\n FROM (\n%s" % (select1,select2,select3) mysql += "\nFROM %s\n%s\n%s" % (sql_t,sql_w,sql_o) mysql += "\n ) w_tmp\n WHERE ROWNUM <= %s\n)WHERE rn > %s\n" % (limitby[1],limitby[0]) return mysql #return 'SELECT %s %s FROM (SELECT w_tmp.*, ROWNUM w_row FROM (SELECT %s FROM %s%s%s) w_tmp WHERE ROWNUM<=%i) %s %s %s;' % (sql_s, sql_f, sql_f, sql_t, sql_w, sql_o, lmax, sql_t, sql_w_row, sql_o) # end of my code changes return 'SELECT %s %s FROM %s%s%s;' % (sql_s, sql_f, sql_t, sql_w, sql_o) . -- Resources: - http://web2py.com - http://web2py.com/book (Documentation) - http://github.com/web2py/web2py (Source code) - https://code.google.com/p/web2py/issues/list (Report Issues) --- You received this message because you are subscribed to the Google Groups "web2py-users" group. To unsubscribe from this group and stop receiving emails from it, send an email to web2py+unsubscr...@googlegroups.com. For more options, visit https://groups.google.com/d/optout.
[web2py] Re: SQLFORM.grid problems with Oracle database tables
Nico, Thanks for your response. The tables were already joined in the query definition inside the controller. query = ((db2.STATUSPOINT.POINTNUMBER < 100)&\ (db2.STATUSPOINT.POINTACCESSAREA == db2.AOR.AOR)) I feel quite certain that the problem is associated with the extra SQL required to do pagination on an Oracle database. I have tested this theory to the extent that I successfully implemented the same controller, model, and data on a mysql database, and everything worked perfectly. I've included the models as you suggested: db2 = DAL('oracle://**/@*', migrate=False) db2.define_table('AOR', Field('AOR','integer',writable=False), Field('REFERENCENAME','string',writable=False), Field('ACCESSAREAASSIGNMENT','integer',writable=False), Field('ACCESSZONEASSIGNMENT','integer,writable=False'), primarykey=['AOR'], migrate=False ) db2.define_table('STATUSPOINT', Field('POINTNUMBER', 'integer',writable=False), Field('POINTNAME', 'string',writable=False), Field('LOGPARTITION', 'integer',writable=False), Field('POINTACCESSAREA', 'integer',writable=False), Field('ALTERNATEMENUINFORMATIONAREA', 'integer',writable=False), Field('LOGICALDISPLAYREFERENCE', 'string',writable=False), Field('STATION', 'integer',writable=False), Field('GROUPINHERITANCE', 'integer',writable=False), Field('TRIGGERS', 'integer',writable=False), Field('INITIALVALUE', 'integer',writable=False), Field('PROPAGATEINITIALVALUE', 'string',writable=False), Field('SETNONUPDATE', 'string',writable=False), Field('MANUALOVERRIDE', 'string',writable=False), Field('MANUALENTRY', 'string',writable=False), Field('EVENTS', 'string',writable=False), Field('MAPBOARDGROUPNUMBER', 'integer',writable=False), Field('DATADIVISION', 'integer',writable=False), Field('ANALOGDEMANDSCAN', 'string',writable=False), Field('STATEFEATURES', 'integer',writable=False), Field('STATECALCULATOR', 'integer',writable=False), Field('CONTROL', 'integer',writable=False), Field('INDICATION', 'integer',writable=False), Field('ALTDATASOURCELIST', 'integer',writable=False), Field('ALTDATASOURCEUSAGELIST', 'integer',writable=False), Field('ALTDATASOURCEPROC', 'integer',writable=False), Field('ASSOCDEVICESETNUMBER', 'integer',writable=False), Field('ASSETID', 'string',writable=False), Field('PICOLLECTION', 'string',writable=False), Field('PIVALUETAG', 'string',writable=False), Field('PIQUALITYSTORAGE', 'string',writable=False), Field('PIQUALITYTAG', 'string',writable=False), Field('COLLECTTOHISTORICAL', 'integer',writable=False), Field('POINTURLS', 'integer',writable=False), Field('ACTIONDESCRIPTIONSET', 'integer',writable=False), Field('CIRCUITLABELNUMBER', 'integer',writable=False), Field('SUBSTATIONNUMBER', 'integer',writable=False), Field('DEVICETYPENUMBER', 'integer',writable=False), Field('LONGNAMENUMBER', 'integer',writable=False), Field('TOGGLERELEASETHRESHOLD', 'integer',writable=False), Field('TOGGLEINHIBITTHRESHOLD', 'integer',writable=False), Field('CONTROLWARNING', 'integer',writable=False), Field('EDNACOLLECTION', 'string',writable=False), Field('EDNAEXTIDENTIFIER', 'string',writable=False), Field('EDNAQUALITYSTORAGE', 'string',writable=False), Field('EDNASECURITYGROUP', 'integer',writable=False), Field('DOG1REF', 'integer',writable=False),
[web2py] SQLFORM.grid problems with Oracle database tables
Hi, I have been using SQLFORM.grid to display some legacy Oracle tables. While it works fine when the query is for a single table, as soon as the query involves a join between two tables the grid returns multiple duplicate entries. (I'm using web2py 2.13.4-) The result looks like this: PointnumberPointnameReferencename 1RTU 1 Status & ControlHarris Test 1RTU 1 Status & ControlHarris Test 1RTU 1 Status & ControlHarris Test 1RTU 1 Status & ControlHarris Test 1RTU 1 Status & ControlHarris Test 2RTU 2 Status & ControlHarris Test 2RTU 2 Status & ControlHarris Test 2RTU 2 Status & ControlHarris Test 2RTU 2 Status & ControlHarris Test 2RTU 2 Status & ControlHarris Test 3RTU 3 Status & ControlHarris Test 3RTU 3 Status & ControlHarris Test 3RTU 3 Status & ControlHarris Test 3RTU 3 Status & ControlHarris Test 3RTU 3 Status & ControlHarris Test ... The following is the controller that generated the previous results. .. def search1(): fields = [db2.STATUSPOINT.POINTNUMBER,db2.STATUSPOINT.POINTNAME,db2.AOR.REFERENCENAME] maxtextlengths = { 'STATUSPOINT.POINTNAME': 30, 'AOR.REFERENCENAME': 30, } query = ((db2.STATUSPOINT.POINTNUMBER < 100)&\ (db2.STATUSPOINT.POINTACCESSAREA == db2.AOR.AOR)) orderby = [db2.STATUSPOINT.POINTNUMBER] grid=SQLFORM.grid( query=query, deletable=False,editable=False,details=False, searchable=True,fields=fields, paginate=5,csv=False,maxtextlengths=maxtextlengths, orderby=orderby, ) print "search1 grid db2._timings %s" % db2._timings print type(grid) print len(grid) return dict(grid=grid) .. - the number of duplicates(5) is the same as the value for paginate. - paging forward shows the same values. - the sql reported by db2._timings is [("ALTER SESSION SET NLS_DATE_FORMAT = '-MM-DD HH24:MI:SS'", 0.00066494941711425781), ("ALTER SESSION SET NLS_TIMESTAMP_FORMAT = '-MM-DD HH24:MI:SS'", 0.0005130767822265625), ('SELECT count(*) FROM AOR,STATUSPOINT WHERE ((STATUSPOINT.POINTNUMBER < 100) AND (STATUSPOINT.POINTACCESSAREA = AOR.AOR))', 0.0013380050659179688), ('SELECT STATUSPOINT.POINTNUMBER, STATUSPOINT.POINTNAME, AOR.REFERENCENAME, AOR.AOR FROM (SELECT w_tmp.*, ROWNUM w_row FROM (SELECT STATUSPOINT.POINTNUMBER, STATUSPOINT.POINTNAME, AOR.REFERENCENAME, AOR.AOR FROM AOR, STATUSPOINT WHERE ((STATUSPOINT.POINTNUMBER < 100) AND (STATUSPOINT.POINTACCESSAREA = AOR.AOR)) ORDER BY STATUSPOINT.POINTNUMBER) w_tmp WHERE ROWNUM<=25) AOR, STATUSPOINT WHERE ((STATUSPOINT.POINTNUMBER < 100) AND (STATUSPOINT.POINTACCESSAREA = AOR.AOR)) AND w_row > 20 ORDER BY STATUSPOINT.POINTNUMBER', 0.014330863952636719)] - when I execute this sql from the command line using sqlplus, I see the same results with all the duplicates - I believe there is flaw with the sql statements generated by web2py - I wrote some sql that return more appropriate results but I'm not familiar enough with the inner workings of web2py to be able to implement it. - If anyone has suggestions about where to start, what modules to change and best coding practices, I'd appreciate the pointers. SELECT c1 "STATUSPOINT.POINTNUMBER", c2 "STATUSPOINT.POINTNAME", c3 "AOR.REFERENCENAME" FROM ( SELECT w_tmp.c1, w_tmp.c2, w_tmp.c3, ROWNUM rn FROM ( SELECT STATUSPOINT.POINTNUMBER c1, STATUSPOINT.POINTNAME c2, AOR.REFERENCENAME c3 FROM AOR, STATUSPOINT WHERE STATUSPOINT.POINTACCESSAREA = AOR.AOR ORDER BY STATUSPOINT.POINTNUMBER ) w_tmp WHERE ROWNUM <= 30 ) WHERE rn > 20; - Tom -- Resources: - http://web2py.com - http://web2py.com/book (Documentation) - http://github.com/web2py/web2py (Source code) - https://code.google.com/p/web2py/issues/list (Report Issues) --- You received this message because you are subscribed to the Google Groups "web2py-users" group. To unsubscribe from this group and stop receiving emails from it, send an email to web2py+unsubscr...@googlegroups.com. For more options, visit https://groups.google.com/d/optout.
[web2py] Re: Oracle perfomance issues with version 2.12.3
Thanks for the suggestion. I started a web2py session from the command line, and ran a simple DAL select from there. python web2py.py -M -S myapplication result = db2(db2.soe_tdb.pointnumber>0).select(db2.soe_tdb.ALL,limitby=(0,10)) print db2._timings [("ALTER SESSION SET NLS_DATE_FORMAT = '-MM-DD HH24:MI:SS'", 0.001035928726196289), ("ALTER SESSION SET NLS_TIMESTAMP_FORMAT = '-MM-DD HH24:MI:SS'", 0.0008900165557861328), ('SELECT soe_tdb.utctime, soe_tdb.miliseconds, soe_tdb.pointnumber, soe_tdb.pointname, soe_tdb.stationname, soe_tdb.statenumber, soe_tdb.statename, soe_tdb.groupname, soe_tdb.tv_sec, soe_tdb.tv_usec, soe_tdb.confirmed FROM (SELECT w_tmp.*, ROWNUM w_row FROM (SELECT soe_tdb.utctime, soe_tdb.miliseconds, soe_tdb.pointnumber, soe_tdb.pointname, soe_tdb.stationname, soe_tdb.statenumber, soe_tdb.statename, soe_tdb.groupname, soe_tdb.tv_sec, soe_tdb.tv_usec, soe_tdb.confirmed FROM soe_tdb WHERE (soe_tdb.pointnumber > 0) ORDER BY soe_tdb.utctime) w_tmp WHERE ROWNUM<=10) soe_tdb WHERE (soe_tdb.pointnumber > 0) AND w_row > 0 ORDER BY soe_tdb.utctime', 59.642492055892944)] The select took about a minute whether I used web2py version 2.9.5, 2.11.2 or 2.12.3. This does suggest that something changed to SQLFORM.grid between 2.11 and 2.12. I'll dig a little deeper by trying some variations on my use of SQLFORM.grid. Thanks, - Tom On Tuesday, November 10, 2015 at 12:30:55 AM UTC-6, Paolo Valleri wrote: > Hi, > there should be something wrong going on. > As a first step, I'd write a simple query (i.e., select(db.table.ALL, > limit=(0,10)) using pydal (the database abstraction layer used in web2py) > and try to find out in which version has been introduced this performance > issue. If you don't find a performance issue on pydal, then there should be > something in the SQLFORM.grid part. > > Paolo > > On Sunday, November 8, 2015 at 6:16:31 AM UTC+1, tomt wrote: >> >> Hi, >> >> I encountered really slow responses when I was using my web2py app to >> access an oracle database. I tried to use dbstats in response.toolbar to >> measure this, but it wouldn't show the sql or any timing information. I >> decided to try downgrading to an older web2py version, 2.11.2, and noticed >> a dramatic change. >> >> web2py 2.11.2 query took 4 seconds >> web2py 2.12.3 query took > 2 minutes >> >> I've tried this several times, with the same result. Were there any >> changes in web2py that could account for this? >> >> The query I am running was passed to sqlform.grid >> >> soequery = ( (db2.soe_tdb.utctime > fromdate)&\ >> (db2.soe_tdb.utctime < todate)&\ >> (db2.soe_tdb.stationname.belongs(stationlist))&\ >> (db2.soe_tdb.pointnumber == db2.statuspoint.pointnumber)&\ >> (db2.statuspoint.pointaccessarea == >> db2.accessareaassignment.setnumber)&\ >> (db2.accessareaassignment.referencename == 'SOE')&\ >> ~(db2.statuspoint.pointname.like('%@%')) ) >> >> orderby = [db2.soe_tdb.utctime] >> >> grid=SQLFORM.grid( >> query=soequery, >> deletable=False,editable=False,details=False, >> searchable=True,fields=fields,headers=headers, >> paginate=10,csv=False,maxtextlengths=maxtextlengths, >> orderby=orderby, >> ) >> >> Please let me know if there is any other information that I could provide >> to try to resolve this. >> >> Thanks in advance, - Tom >> > -- Resources: - http://web2py.com - http://web2py.com/book (Documentation) - http://github.com/web2py/web2py (Source code) - https://code.google.com/p/web2py/issues/list (Report Issues) --- You received this message because you are subscribed to the Google Groups "web2py-users" group. To unsubscribe from this group and stop receiving emails from it, send an email to web2py+unsubscr...@googlegroups.com. For more options, visit https://groups.google.com/d/optout.
[web2py] Oracle perfomance issues with version 2.12.3
Hi, I encountered really slow responses when I was using my web2py app to access an oracle database. I tried to use dbstats in response.toolbar to measure this, but it wouldn't show the sql or any timing information. I decided to try downgrading to an older web2py version, 2.11.2, and noticed a dramatic change. web2py 2.11.2 query took 4 seconds web2py 2.12.3 query took > 2 minutes I've tried this several times, with the same result. Were there any changes in web2py that could account for this? The query I am running was passed to sqlform.grid soequery = ( (db2.soe_tdb.utctime > fromdate)&\ (db2.soe_tdb.utctime < todate)&\ (db2.soe_tdb.stationname.belongs(stationlist))&\ (db2.soe_tdb.pointnumber == db2.statuspoint.pointnumber)&\ (db2.statuspoint.pointaccessarea == db2.accessareaassignment.setnumber)&\ (db2.accessareaassignment.referencename == 'SOE')&\ ~(db2.statuspoint.pointname.like('%@%')) ) orderby = [db2.soe_tdb.utctime] grid=SQLFORM.grid( query=soequery, deletable=False,editable=False,details=False, searchable=True,fields=fields,headers=headers, paginate=10,csv=False,maxtextlengths=maxtextlengths, orderby=orderby, ) Please let me know if there is any other information that I could provide to try to resolve this. Thanks in advance, - Tom -- Resources: - http://web2py.com - http://web2py.com/book (Documentation) - http://github.com/web2py/web2py (Source code) - https://code.google.com/p/web2py/issues/list (Report Issues) --- You received this message because you are subscribed to the Google Groups "web2py-users" group. To unsubscribe from this group and stop receiving emails from it, send an email to web2py+unsubscr...@googlegroups.com. For more options, visit https://groups.google.com/d/optout.
[web2py] dbstats with oracle doesn't show sql selects or timing
Hi, I'm using web2py 2.12.3 connecting to oracle 10.2 I tried to use the dbstat function of {{=response.toolbar()}} to examine database performance, but the sql select and its timing isn't printed. This feature works as expected when I'm connecting to a mysql database. Is this a bug, or just a limitation of the oracle driver? Thanks in advance, - Tom -- Resources: - http://web2py.com - http://web2py.com/book (Documentation) - http://github.com/web2py/web2py (Source code) - https://code.google.com/p/web2py/issues/list (Report Issues) --- You received this message because you are subscribed to the Google Groups "web2py-users" group. To unsubscribe from this group and stop receiving emails from it, send an email to web2py+unsubscr...@googlegroups.com. For more options, visit https://groups.google.com/d/optout.
[web2py] Re: what are the12 core objects
- thanks for the response -- Resources: - http://web2py.com - http://web2py.com/book (Documentation) - http://github.com/web2py/web2py (Source code) - https://code.google.com/p/web2py/issues/list (Report Issues) --- You received this message because you are subscribed to the Google Groups "web2py-users" group. To unsubscribe from this group and stop receiving emails from it, send an email to web2py+unsubscr...@googlegroups.com. For more options, visit https://groups.google.com/d/optout.
[web2py] what are the12 core objects
The preface says that "the API includes just 12 core objects" What are they? - Thanks in advance -- Resources: - http://web2py.com - http://web2py.com/book (Documentation) - http://github.com/web2py/web2py (Source code) - https://code.google.com/p/web2py/issues/list (Report Issues) --- You received this message because you are subscribed to the Google Groups "web2py-users" group. To unsubscribe from this group and stop receiving emails from it, send an email to web2py+unsubscr...@googlegroups.com. For more options, visit https://groups.google.com/d/optout.