Base.find(String query, Object... params) is fetching the whole resultset instead of streaming it

[alniks]

the code is passing 0 to the method which sets fetch size:

find(RowProcessor.ResultSetType.FORWARD_ONLY, RowProcessor.ResultSetConcur.READ_ONLY, 0, query, params)

according to docs 0 is ignored:

https://docs.oracle.com/javase/7/docs/api/java/sql/Statement.html#setFetchSize(int)

which leads to fetching whole resultset into memory.

I would set the default value to at least 10 to save on network calls

[ipolevoy]

@alniks this is not a bug. This is a way to get an infinite number of records from a database by processing the results using RowProcessor one record at the time. If you are somehow accumulating these records and are running out of heap space, this API is not for this case. The whole idea here is not to have a limit, but rather to use a "cursor" to be able to process infinite number of records.

[alniks]

@ipolevoy you are wrong: "0" is ignored by the driver and means - fetch the whole result set into memory. If you need "one record at the time" you have to pass 1. Here is the stack strace:

at java.lang.OutOfMemoryError.()V (OutOfMemoryError.java:48)
at org.mariadb.jdbc.internal.io.input.StandardPacketInputStream.getPacketArray(Z)[B (StandardPacketInputStream.java:266)
at org.mariadb.jdbc.internal.com.read.resultset.SelectResultSet.readNextValue()Z (SelectResultSet.java:376)
at org.mariadb.jdbc.internal.com.read.resultset.SelectResultSet.fetchAllResults()V (SelectResultSet.java:292) <= here
at org.mariadb.jdbc.internal.com.read.resultset.SelectResultSet.([Lorg/mariadb/jdbc/internal/com

Line 4 from the top.

Basically Base.find(RowProcessor.ResultSetType.FORWARD_ONLY, RowProcessor.ResultSetConcur.READ_ONLY, 0, query, params) is equal to Base.findAll

Limit means "how many records you are going to fetch at one time when moving cursor". 0, as I said, is ignored and means "fetch all records at one time", 1 means "fetch one record, then another one" meaning driver will make a network call to fetch each record and return it to RowProcessor. So it's better to use something like 100 to save network round trips.

[ipolevoy]

@alniks you are correct, here is a better explanation: https://mariadb.com/kb/en/about-mariadb-connector-j/#streaming-result-sets

[ipolevoy]

@alniks the last commit should take care of the issue. I will be included in the release for Java 16

[ipolevoy]

I still would not say this is a bug, since the other method allows you a full control over a fetch size:

javalite/activejdbc/src/main/java/org/javalite/activejdbc/Base.java

Line 282 in 9524dfb

public static RowProcessor find(RowProcessor.ResultSetType type, RowProcessor.ResultSetConcur concur, int fetchSize, String query, Object ... params) {

I'd rather call this an enhancement :)