[MonetDB-users] Problems With CREATE TEMPORARY TABLE

15 Jul 2008

      Hi all,

I am having trouble using MonetDB, with dynamic temporary table
creation. I have included a simple unit test to demonstrate the problem.
These tests work fine on other JDBC SQL servers, but fail with the below
exceptions when using MonetDB.

Any ideas on how to make new tables available to other connections or
keep new tables from interrupting other jdbc statements?

Thanks,
James
---%<---
java.sql.SQLException: INSERT INTO: no such table 'paints_2'
	at nl.cwi.monetdb.jdbc.MonetConnection$ResponseList.executeQuery(MonetConnection.java:2058)
	at nl.cwi.monetdb.jdbc.MonetConnection$ResponseList.processQuery(MonetConnection.java:1808)
	at nl.cwi.monetdb.jdbc.MonetStatement.internalExecute(MonetStatement.java:371)
	at nl.cwi.monetdb.jdbc.MonetStatement.execute(MonetStatement.java:340)
	at nl.cwi.monetdb.jdbc.MonetPreparedStatement.<init>(MonetPreparedStatement.java:102)
	at nl.cwi.monetdb.jdbc.MonetConnection.prepareStatement(MonetConnection.java:625)
	at nl.cwi.monetdb.jdbc.MonetConnection.prepareStatement(MonetConnection.java:586)
	at org.apache.commons.dbcp.DelegatingConnection.prepareStatement(DelegatingConnection.java:248)
	at org.apache.commons.dbcp.PoolingDataSource$PoolGuardConnectionWrapper.prepareStatement(PoolingDataSource.java:302)
	at MonetDBJdbcTest.testDualCreateTable(MonetDBJdbcTest.java:28)

---%<---
java.sql.SQLException: Query did not produce a result set
	at nl.cwi.monetdb.jdbc.MonetPreparedStatement.executeQuery(MonetPreparedStatement.java:228)
	at org.apache.commons.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:93)
	at MonetDBJdbcTest.testSelectWhileCreateTable(MonetDBJdbcTest.java:77)
---%<---
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import junit.framework.TestCase;

import org.apache.commons.dbcp.BasicDataSource;

public class MonetDBJdbcTest extends TestCase {

	private BasicDataSource ds1;

	public void testDualCreateTable() throws Exception {
		Connection c4 = ds1.getConnection();
		c4.setAutoCommit(true);
		Connection c76 = ds1.getConnection();
		c76.setAutoCommit(false);
		Statement s93 = c76.createStatement();
		s93.execute("CREATE TEMPORARY TABLE TRANSACTION_STATEMENTS (\n  ctx INTEGER NOT NULL,\n  subj INTEGER NOT NULL,\n  pred INTEGER NOT NULL,\n  obj INTEGER NOT NULL,\n  expl BOOL NOT NULL\n)");
		s93.close();
		Statement s96 = c4.createStatement();
		s96.execute("CREATE TABLE paints_2 (\n  ctx INTEGER NOT NULL,\n  subj INTEGER NOT NULL,\n  obj INTEGER NOT NULL,\n  expl BOOL NOT NULL\n)");
		s96.close();
		PreparedStatement ps102 = c76.prepareStatement("INSERT INTO paints_2\nSELECT DISTINCT ctx, subj, obj, expl FROM TRANSACTION_STATEMENTS tr\nWHERE NOT EXISTS (SELECT * FROM paints_2 st\nWHERE st.ctx = tr.ctx AND st.subj = tr.subj AND st.obj = tr.obj AND st.expl = tr.expl)");
		ps102.close();
		c76.close();
		c4.close();
	}

	public void testSelectWhileCreateTable() throws Exception {
		Connection c2 = ds1.getConnection();
		c2.setAutoCommit(true);
		Statement s7 = c2.createStatement();
		s7.execute("CREATE TABLE HASH_VALUES (\n  id INTEGER NOT NULL,\n  value BIGINT NOT NULL\n)");
		s7.close();
		PreparedStatement ps72 = c2.prepareStatement("SELECT id, value\nFROM HASH_VALUES\nWHERE value IN (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)");
		ps72.setLong(1, 157585763729791056l);
		ps72.setLong(2, 1105705122070497121l);
		ps72.setLong(3, 940939309403574879l);
		ps72.setLong(4, 44738479624713631l);
		ps72.setLong(5, 93693228172326007l);
		ps72.setLong(6, 909856949769965718l);
		ps72.setLong(7, 388219017765868455l);
		ps72.setLong(8, 498326321060763331l);
		ps72.setLong(9, 917168269028472831l);
		ps72.setLong(10, 560146884310966732l);
		ps72.setLong(11, 178986943741094305l);
		ps72.setNull(12, -5);
		ps72.setNull(13, -5);
		ps72.setNull(14, -5);
		ps72.setNull(15, -5);
		ResultSet rs73 = ps72.executeQuery();
		rs73.next();
		rs73.close();
		Statement s76 = c2.createStatement();
		s76.execute("CREATE TABLE TRIPLES (\n  ctx INTEGER NOT NULL,\n  subj INTEGER NOT NULL,\n  pred INTEGER NOT NULL,\n  obj INTEGER NOT NULL,\n  expl BOOL NOT NULL\n)");
		s76.close();
		ps72.setLong(1, 157585763729791056l);
		ps72.setLong(2, 93693228172326007l);
		ps72.setLong(3, 44738479624713631l);
		ps72.setLong(4, 909856949769965718l);
		ps72.setLong(5, 388219017765868455l);
		ps72.setLong(6, 917168269028472831l);
		ps72.setNull(7, -5);
		ps72.setNull(8, -5);
		ps72.setNull(9, -5);
		ps72.setNull(10, -5);
		ps72.setNull(11, -5);
		ps72.setNull(12, -5);
		ps72.setNull(13, -5);
		ps72.setNull(14, -5);
		ps72.setNull(15, -5);
		ResultSet rs88 = ps72.executeQuery();
		rs88.close();
		ps72.close();
		c2.close();
	}

	@Override
	protected void setUp() throws Exception {
		Class.forName("nl.cwi.monetdb.jdbc.MonetDriver");
		ds1 = new BasicDataSource();
		ds1.setUrl("jdbc:monetdb://localhost/jdbc_test");
		ds1.setUsername("monetdb");
		ds1.setPassword("monetdb");
	}

	@Override
	protected void tearDown() throws Exception {
		Connection c106 = ds1.getConnection();
		Statement s107 = c106.createStatement();
		try {
			s107.execute("DROP TABLE HASH_VALUES");
			s107.execute("DROP TABLE TRIPLES");
		} catch (SQLException exc) {
			// ignore
		}
		try {
			s107.execute("DROP TABLE TRANSACTION_STATEMENTS");
			s107.execute("DROP TABLE paints_2");
		} catch (SQLException exc) {
			// ignore
		}
		s107.close();
		c106.close();
		ds1.close();
	}

}

[MonetDB-users] Problems With CREATE TEMPORARY TABLE

James Leigh