Take the 2-minute tour ×
Mathematica Stack Exchange is a question and answer site for users of Mathematica. It's 100% free, no registration required.

I have a database table with 900,000 rows and 80 columns per row which I'd like to work with in mathematica. My computations are isolated to each row, so I'd like to stream only a single row (or small number of rows) at a time.

SQLSelect naturally loads the entire results into memory causing a java heap error in my JDBC driver.

My initial thought was to use result sets and cursor through them. However, after trying a number of approaches, the JDBC driver (PostgreSQL) and mathematica still try to load the entire results into memory, causing the JDBC driver to run out of memory each attempt.

I can use SQLExecute to setup a cursor manually, but this loses composability.

How can I force Mathematica's SQLResultSets to use a cursor and work in a memory efficient way? From reading the documentation I've setup my Resultset correctly, the only thing I can think of that might be missing is forcing Autocommit off, which I can't figure out how to do.

Here's my code.

Needs["DatabaseLink`"];
dbconn = OpenSQLConnection[JDBC["PostgreSQL", "localhost/mydatabase"]]

(* Don't know how to turn off Auto-commit, this may be what is missing here *)
rs = SQLResultSetOpen[SQLSelect[dbconn, "mytable"], 
  "FetchDirection" -> "Forward", "Mode" -> "ForwardOnly", 
  "FetchSize" -> 100]

SQLResultSetRead[rs]

SQLResultSetClose[rs]
share|improve this question
    
stackoverflow.com/questions/827110/… About a page down there is a mention that autocommit must be off. Not sure how to do that. You may be able to dig the raw java driver instance out of dbconn and set it perhaps. –  Ymareth Jul 18 at 23:22

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.