I've been getting java heap size errors when reading a lot of rows
returned from a query via sqlalchemy with a jython script.
I isolated the problem to the way sqlalchemy creates a cursor from
zxjdbc. The particular function in question is the
Oracle_zxjdbcExecutionContext.create_cursor() in sqlalchemy/dialects/
oracle/zxjdbc.py:
def create_cursor(self):
cursor = self._connection.connection.cursor()
cursor.datahandler =
self.dialect.DataHandler(cursor.datahandler)
return cursor
The call to cursor() will return a static cursor rather than a dynamic
cursor. The difference is described here:
http://www.jython.org/archive/21/docs/zxjdbc.html
The static cursor will iterate over the result set, return the data in
the cursor and close the resulset. The dynamic cursor instead iterates
over the result set as required. This would be the preferred behavior
in my case as I'm returning many rows.
If I call zxjdbc directly with a dynamic cursor the memory usage stays
constant and I avoid the Java heap errors.
Here is a test case I was using:
# use sqlalchemy
db = sqlalchemy.create_engine('oracle+zxjdbc://user:passw...@host:1521/
sid')
query = "select level, dbms_random.string('X',4000) s1,
dbms_random.string('X',4000) s2 from dual connect by level <= 500000"
results = db.execute(query)
for r in results:
pass
# use zxjdbc directly but use dynamic cursor by calling cursor(1)
instead of cursor()
from com.ziclix.python.sql import zxJDBC
db = zxJDBC.connect('jdbc:oracle:thin:@host:1521:sid', 'user',
'password', 'oracle.jdbc.driver.OracleDriver')
query = "select level, dbms_random.string('X',4000) s1,
dbms_random.string('X',4000) s2 from dual connect by level <= 500000"
cursor = db.cursor(1)
results = cursor.execute(query)
for r in results:
pass
Not sure where to report this so I'm posting here on the google group.
Tahir
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalch...@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
