[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] 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] 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] 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.
