Re: [MonetDB-users] Problems With CREATE TEMPORARY TABLE

16 Jul 2008


      On Wed, 2008-07-16 at 09:59 +0200, Niels Nes wrote:
...
plain text document attachment (Re: [MonetDB-users] Problems With
CREATE TEMPORARY TABLE.eml)
On Tue, Jul 15, 2008 at 03:23:15PM -0400, James Leigh wrote:
...
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)");
Here Your 'c76' transaction is started.
...
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();
This transaction is auto-commited but afcourse (correctly) not visiable
by your c76 transaction.
...
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)");
So this statement will give an error as it doesn't know about paints_2.
A simple change of order of you java statements should fix this problem.
Niels
Thanks for your quick response. It is unfortunate that MonetDB does not
support this, but it is manageable. Any ideas why this other statement
(ps72) throws a "Query did not produce a result set" in MonetDB?

...
...
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();
  }