JDBC ResultSet no entries for queries where iquery returnes rows


#1

Hello everybody,

I’m trying to use SciDB’s JDBC connection, but some queries return no rows, but those queries return rows if I use the iquery command line tool.

I’m using Ubuntu 14.04 VM and SciDB 18.1.

The array I’m queriying is created like:

CREATE ARRAY rundata
<id:int64, signal:string, timekey:double, value:double>[dim0];

I used the load command to get data into the array.

load(rundata,'/home/ninti/scidb/rundata.csv',-2,'csv:l');

The array got approximately 160 million rows.
There are about 1200 timekeys(1-1200) per signal, and there are about 255 different signals.
My id is starting at 123.

My base program:

import java.io.IOException;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.logging.Level;
import java.util.logging.Logger;

import org.scidb.jdbc.Connection;
import org.scidb.jdbc.IResultSetWrapper;

class SciDBWithJDBC {

	private static Logger logger = Logger
			.getLogger(SciDBWithJDBC.class.getName());

	public static void main(String[] args) throws IOException {
		try {
			Class.forName("org.scidb.jdbc.Driver");
		} catch (ClassNotFoundException e) {
			logger.log(Level.SEVERE, "Driver is not in the CLASSPATH -> ", e);
		}

		String iqueryHost = "localhost";
		String iqueryPort = "1239";
		try {

			// Connection conn = DriverManager.getConnection(connString);
			Connection conn = new Connection(iqueryHost,
					Integer.valueOf(iqueryPort));

			conn.getSciDBConnection().setAfl(true);
			ResultSet rs = conn.createStatement().executeQuery("filter(rundata, id=123)");
			System.out.println(rs.next());
			while (rs.next()) {
				System.out.println("id: " + rs.getLong("id") + "\tsignal: "
						+ rs.getString("signal") + "\ttimekey: "
						+ rs.getDouble("timekey") + "\tvalue: "
						+ rs.getDouble("value"));
			}
			conn.close();
		} catch (SQLException e) {
			logger.log(Level.SEVERE, e.getMessage(), e);
		}

		System.exit(0);
	}
}

The query is working just fine:

filter(rundata, id=123)

The output is something like this:

|id: 123|signal: YFRGEH|timekey: 698.0|value: -251.551|
|id: 123|signal: YFRGEH|timekey: 699.0|value: 353.319|
|id: 123|signal: YFRGEH|timekey: 700.0|value: 675.065|
|id: 123|signal: YFRGEH|timekey: 701.0|value: -515.232|
|id: 123|signal: YFRGEH|timekey: 702.0|value: -792.94|
|id: 123|signal: YFRGEH|timekey: 703.0|value: 258.987|
|id: 123|signal: YFRGEH|timekey: 704.0|value: -829.655|
|id: 123|signal: YFRGEH|timekey: 705.0|value: -836.3|
|id: 123|signal: YFRGEH|timekey: 706.0|value: 742.551|

But if I change the query to:

filter(rundata, id>123)

System.out.println(rs.next()) reports false. There are no problems with that query when using the iquery commandline tool!

IMPORTANT NOTE
My first id is 123, there are about 360.000 entries per id.

Same goes for the signal strings, I can retrieve the first ~80 signals by name, but everything afterwards returns an empty result set. The signals can be retrieved via iquery commandline tool without any problem.

So am I doing something wrong? Is there maybe a parameter which I need to tune? Or is this a bug?

Greetings
Ninti


#2

Hi @Nintinugga, sorry about the trouble.

At the moment, the JDBC connector should be treated as experimental. Most of our deployments use the shim connector together with SciDB-R and SciDB-Py. Those connectors are much more performant and stable - I would recommend using those if possible.

Our immediate priority right now is to redesign the low-level C++ API after which we will revamp the connectors.

This particular issue looks suspicious and we will take a look when we get a chance.


#3

Hi @apoliakov,

Thanks for the fast reply. I will look into the SciDB-Py package!

Greetings
Ninti