DAL not returning results on temp table query

[Yarin]

I'm trying to execute some SQL from the DAL that relies on a temp table:

CREATE TEMPORARY TABLE tmp LIKE people;

 INSERT INTO tmp SELECT * FROM people;
 INSERT INTO tmp SELECT * FROM people;
 SELECT * FROM tmp;

This code works fine in a SQL panel, but fails when used in the DAL:

def test():


 db_test = DAL('mysql://root:root@localhost/test')
 
 sql = """CREATE TEMPORARY TABLE tmp LIKE people;
 INSERT INTO tmp SELECT * FROM people;
 INSERT INTO tmp SELECT * FROM people;
 SELECT * FROM tmp;"""
 
 results = db_test.executesql(sql)

Results always returns None. No idea why or how to proceed.

[Niphlod]

I think the DBAPI doesn't support several results set in a single statement.

If you cared to do those one line at a time you'd noticed that:
create temporary table tmp like people does not return results.
insert into tmp select * from people returns the just inserted values
select * from tmp returns your lines.

I think that as long as you have only one statement (and it's the last ) in your executesql() wrapped string you'll be fine. If some of your lines returns results, then the driver doesn't know what set to actually return.
Anyway splitting those lines in multiple db.executesql()s is working great.

[Yarin]

Thanks Simone- yeah I just figured this out. Though I actually went a different route and just stuffed it all in a stored procedure..

[Massimo Di Pierro]

Exately. On top of this until yesterday executesql had a "try fetchall() except return None." which is now gone. If you get the latest web2py from trunk it should give you an error.

[Yarin Kessler]

Massimo- good, that's gonna help- thanks

--
 
 
 

[Yarin]

Massimo- Just tried testing with latest trunk DAL- it still fails silently and returns None results when trying to execute multiple statements with a single call

[Massimo Di Pierro]

Please check agin. there is no more try-except in there.