[web2py] Re: informix standard engine

[Massimo Di Pierro]

This is a similar problem as mssql which does not support pagination.
I just committed to trunk a new adpater:

DAL('informix-se://.')

please give it a try and see if does what you need. You should be able to 
fix the problem just by tweaking the InformixSEAdapter and not the normal 
InformixAdapter. Let me know if this work for you.

Massimo

On Tuesday, 4 December 2012 05:08:37 UTC-6, Wouter Pronk wrote:

 I'm trying to connect web2py to Informix SE (standard engine) with some 
 succes. I've edited dal.py and removed the following
 dbms_version = int(self.connection.dbms_version.split('.')[0])
 if lmin and (dbms_version = 10):
 # Requires Informix 10.0+
 sql_s += ' SKIP %d' % (lmin, )
 if fetch_amt and (dbms_version = 9):
 # Requires Informix 9.0+
 sql_s += ' FIRST %d' % (fetch_amt, )
 because Informix SE doesn't have a limitby function. After the 
 modification I can connect to small tables, but it doesn't function with 
 large tables. How can I use the fetchmany() function from informixdb? I 
 would like to limit to 20 results and than with another function get the 
 next 20 results.  



-- 

Re: [web2py] Re: informix standard engine

[Wouter Pronk]

Hello Massimo,

I wil try. Thank you very much!

Op 04-12-12 15:49, Massimo Di Pierro schreef:

This is a similar problem as mssql which does not support pagination.
I just committed to trunk a new adpater:

DAL('informix-se://.')

please give it a try and see if does what you need. You should be able 
to fix the problem just by tweaking the InformixSEAdapter and not the 
normal InformixAdapter. Let me know if this work for you.


Massimo

On Tuesday, 4 December 2012 05:08:37 UTC-6, Wouter Pronk wrote:

I'm trying to connect web2py to Informix SE (standard engine) with
some succes. I've edited dal.py and removed the following
|
dbms_version =int(self.connection.dbms_version.split('.')[0])
iflmin and(dbms_version =10):
# Requires Informix 10.0+
sql_s +=' SKIP %d'%(lmin,)
iffetch_amt and(dbms_version =9):
# Requires Informix 9.0+
sql_s +=' FIRST %d'%(fetch_amt,)
|
because Informix SE doesn't have a limitby function. After the
modification I can connect to small tables, but it doesn't
function with large tables. How can I use the fetchmany() function
from informixdb? I would like to limit to 20 results and than with
another function get the next 20 results.

--





--

[web2py] Re: informix standard engine

[Derek]

mssql 2005 and above has row_number which would allow pagination. 

On Tuesday, December 4, 2012 7:49:21 AM UTC-7, Massimo Di Pierro wrote:

 This is a similar problem as mssql which does not support pagination.
 I just committed to trunk a new adpater:

 DAL('informix-se://.')

 please give it a try and see if does what you need. You should be able to 
 fix the problem just by tweaking the InformixSEAdapter and not the normal 
 InformixAdapter. Let me know if this work for you.

 Massimo

 On Tuesday, 4 December 2012 05:08:37 UTC-6, Wouter Pronk wrote:

 I'm trying to connect web2py to Informix SE (standard engine) with some 
 succes. I've edited dal.py and removed the following
 dbms_version = int(self.connection.dbms_version.split('.')[0])
 if lmin and (dbms_version = 10):
 # Requires Informix 10.0+
 sql_s += ' SKIP %d' % (lmin, )
 if fetch_amt and (dbms_version = 9):
 # Requires Informix 9.0+
 sql_s += ' FIRST %d' % (fetch_amt, )
 because Informix SE doesn't have a limitby function. After the 
 modification I can connect to small tables, but it doesn't function with 
 large tables. How can I use the fetchmany() function from informixdb? I 
 would like to limit to 20 results and than with another function get the 
 next 20 results.  



-- 

[web2py] Re: informix standard engine

[Massimo Di Pierro]

Can you provide an example of usage?

On Tuesday, 4 December 2012 10:23:14 UTC-6, Derek wrote:

 mssql 2005 and above has row_number which would allow pagination. 

 On Tuesday, December 4, 2012 7:49:21 AM UTC-7, Massimo Di Pierro wrote:

 This is a similar problem as mssql which does not support pagination.
 I just committed to trunk a new adpater:

 DAL('informix-se://.')

 please give it a try and see if does what you need. You should be able to 
 fix the problem just by tweaking the InformixSEAdapter and not the normal 
 InformixAdapter. Let me know if this work for you.

 Massimo

 On Tuesday, 4 December 2012 05:08:37 UTC-6, Wouter Pronk wrote:

 I'm trying to connect web2py to Informix SE (standard engine) with some 
 succes. I've edited dal.py and removed the following
 dbms_version = int(self.connection.dbms_version.split('.')[0])
 if lmin and (dbms_version = 10):
 # Requires Informix 10.0+
 sql_s += ' SKIP %d' % (lmin, )
 if fetch_amt and (dbms_version = 9):
 # Requires Informix 9.0+
 sql_s += ' FIRST %d' % (fetch_amt, )
 because Informix SE doesn't have a limitby function. After the 
 modification I can connect to small tables, but it doesn't function with 
 large tables. How can I use the fetchmany() function from informixdb? I 
 would like to limit to 20 results and than with another function get the 
 next 20 results.  



-- 

[web2py] Re: informix standard engine

[Niphlod]

*select id, first_name, last_name, email, password, registration_key, 
reset_password_key, registration_id from (
select ROW_NUMBER() over (order by id) AS total_ordering,
id, first_name, last_name, email, password, registration_key, 
reset_password_key, registration_id
from auth_user
) a
where total_ordering
between 1001 and 2000
*
is *more or less* a limit 1000 offset 1000 (emphasis on more or less).

1st beware: row_number() needs a unique column (needs something that 
changes at every row) to calculate correctly the total_ordering as a 
sequential integer. If id is not sequential is not a problem, it just has 
to change for every row. For a table like

*name email
massimo mass...@com
massimo mass...@org
simonesim...@com
simonesim...@org*

a query like

*select row_number() over (order by name) as total_ordering, name, email
from table*

would return

*1 massimo mass...@example.com
1 massimo mass...@example.org
2 simone sim...@example.com
2 simone sim...@example.org*

obviously screwing up everything ^_^ (using over(order by name, email) 
would fix the particular case)

Limiting from, e.g., a *group by* set would be a real problem using 
row_number() for pagination (should include every column to get a distinct 
logical primary key)

Moreover, for large datasets (or several columns in the order by 
partition) is nowhere near speedy (internally it scans the entire 
columns, put those in order, calculates the row number)

The order of the returned set is depending on the over(order by ) clause 
(both the presence order and the asc/desc directives).
*
select * from (
select row_number() over (order by id) total_ordering,
* from auth_user
) a
where total_ordering between 1001 and 2000
order by id desc*

returns the same set as
*
select * from (
select row_number() over (order by id) total_ordering,
* from auth_user
) a
where total_ordering between 1001 and 2000
order by id asc*

it's just reverse ordered.

the correct translation of select * from auth_user order by id desc limit 
50 offset 50 is
*
select * from (
select row_number() over (order by id desc) total_ordering,
* from auth_user
) a
where total_ordering between 51 and 100
*
This is a coincidence:  a translation of select * from auth_user order 
by first_name limit 50 offset 50 is not possible (without resorting to 
select top 100 * ... order by first_name and discard the first 50)

BTW, MSSQL 2012 adopted the nicer*
select id, first_name, last_name, email, password, registration_key, 
reset_password_key, registration_id
from auth_user
offset 1000 rows
fetch next 1000 rows only*

It resolves the syntax problem, but its slow as hell and memory hungry 
with large datasets

-- 

[web2py] Re: informix standard engine

[Massimo Di Pierro]

OK. This is similar to how Oracle handles it too. How about this?

class MSSQL3Adapter(MSSQLAdapter):
def select_limitby(self, sql_s, sql_f, sql_t, sql_w, sql_o, limitby):
if limitby:
(lmin, lmax) = limitby
return 'SELECT %s FROM (SELECT %s ROW_NUMBER() over (order by 
id) AS w_row, %s FROM %s%s%s) TMP WHERE w_row BETWEEN %i AND %s;' % 
(sql_f,sql_s,sql_f,sql_t,sql_w,sql_o,lmin,lmax)
return 'SELECT %s %s FROM %s%s%s;' % (sql_s,sql_f,sql_t,sql_w,sql_o)
def rowslice(self,rows,minimum=0,maximum=None):
return rows

Can you help test it?

On Tuesday, 4 December 2012 15:42:16 UTC-6, Niphlod wrote:

 *select id, first_name, last_name, email, password, registration_key, 
 reset_password_key, registration_id from (
 select ROW_NUMBER() over (order by id) AS total_ordering,
 id, first_name, last_name, email, password, registration_key, 
 reset_password_key, registration_id
 from auth_user
 ) a
 where total_ordering
 between 1001 and 2000
 *
 is *more or less* a limit 1000 offset 1000 (emphasis on more or less).

 1st beware: row_number() needs a unique column (needs something that 
 changes at every row) to calculate correctly the total_ordering as a 
 sequential integer. If id is not sequential is not a problem, it just has 
 to change for every row. For a table like

 *name email
 massimo mass...@com
 massimo mass...@org
 simonesim...@com
 simonesim...@org*

 a query like

 *select row_number() over (order by name) as total_ordering, name, email
 from table*

 would return

 *1 massimo mas...@example.com javascript:
 1 massimo mas...@example.org javascript:
 2 simone sim...@example.com javascript:
 2 simone sim...@example.org javascript:*

 obviously screwing up everything ^_^ (using over(order by name, email) 
 would fix the particular case)

 Limiting from, e.g., a *group by* set would be a real problem using 
 row_number() for pagination (should include every column to get a distinct 
 logical primary key)

 Moreover, for large datasets (or several columns in the order by 
 partition) is nowhere near speedy (internally it scans the entire 
 columns, put those in order, calculates the row number)

 The order of the returned set is depending on the over(order by ) clause 
 (both the presence order and the asc/desc directives).
 *
 select * from (
 select row_number() over (order by id) total_ordering,
 * from auth_user
 ) a
 where total_ordering between 1001 and 2000
 order by id desc*

 returns the same set as
 *
 select * from (
 select row_number() over (order by id) total_ordering,
 * from auth_user
 ) a
 where total_ordering between 1001 and 2000
 order by id asc*

 it's just reverse ordered.

 the correct translation of select * from auth_user order by id desc limit 
 50 offset 50 is
 *
 select * from (
 select row_number() over (order by id desc) total_ordering,
 * from auth_user
 ) a
 where total_ordering between 51 and 100
 *
 This is a coincidence:  a translation of select * from auth_user order 
 by first_name limit 50 offset 50 is not possible (without resorting to 
 select top 100 * ... order by first_name and discard the first 50)

 BTW, MSSQL 2012 adopted the nicer*
 select id, first_name, last_name, email, password, registration_key, 
 reset_password_key, registration_id
 from auth_user
 offset 1000 rows
 fetch next 1000 rows only*

 It resolves the syntax problem, but its slow as hell and memory hungry 
 with large datasets



-- 

[web2py] Re: informix standard engine

[Niphlod]

uhm, aside from calculating the offset with lmin+1, I understand the effort 
on supporting this but . I explained why row_number is not a viable 
option:
- orderby and limitby together will not play nice (ever :-P) 
and with this implementation:
- you can't have an ORDER BY clause inside the subselect (select * from 
(select * from auth_user order by first_name desc) TMP  will raise an error)
- any table without the id field will go into exception (primary_key=[] 
and Field('otherid', 'id') support ?)
- groupby and distinct won't work with limitby

Sure we want to go that way ?

On Wednesday, December 5, 2012 1:30:05 AM UTC+1, Massimo Di Pierro wrote:

 OK. This is similar to how Oracle handles it too. How about this?

 class MSSQL3Adapter(MSSQLAdapter):
 def select_limitby(self, sql_s, sql_f, sql_t, sql_w, sql_o, limitby):
 if limitby:
 (lmin, lmax) = limitby
 return 'SELECT %s FROM (SELECT %s ROW_NUMBER() over (order by 
 id) AS w_row, %s FROM %s%s%s) TMP WHERE w_row BETWEEN %i AND %s;' % 
 (sql_f,sql_s,sql_f,sql_t,sql_w,sql_o,lmin,lmax)
 return 'SELECT %s %s FROM %s%s%s;' % 
 (sql_s,sql_f,sql_t,sql_w,sql_o)
 def rowslice(self,rows,minimum=0,maximum=None):
 return rows

 Can you help test it?


-- 

[web2py] Re: informix standard engine

[Alan Etkin]

 On Monday, November 26, 2012 4:27:19 AM UTC-3, Wouter Pronk wrote:Hello 
All,

 I'm trying to connect to an external Informix Standard Enigine database. 
This works when I use Informixdb (python-module), but in 
 web2py I get the following error when go to the database administration: 

The adapter is failling to get a version number from a string. You could 
try patching dal.py bypassing the version check (maybe setting it's value 
to 0) and see if it's just a bug or there are other compatibility issues.

-- 

[web2py] Re: informix standard engine

[Wouter Pronk]

Alan,

if you were here I would give you a big hug. It works! I already tried this 
but I left one of the sql_s+ assignments. Now I cleared them all and I have 
the required result. Thank you very much.


Op dinsdag 27 november 2012 12:25:38 UTC+1 schreef Alan Etkin het volgende:

  On Monday, November 26, 2012 4:27:19 AM UTC-3, Wouter Pronk wrote:Hello 
 All,

  I'm trying to connect to an external Informix Standard Enigine database. 
 This works when I use Informixdb (python-module), but in 
  web2py I get the following error when go to the database administration: 

 The adapter is failling to get a version number from a string. You could 
 try patching dal.py bypassing the version check (maybe setting it's value 
 to 0) and see if it's just a bug or there are other compatibility issues.


--