On Wed, Jul 16, 2008 at 11:48:58AM -0400, James Leigh wrote:
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 I don't think correct 'transaction isolation' is a problem. Its a feature any dbms should have. Just revert the 2 create statements and it should work as expected. (ps72) throws a "Query did not produce a result set" in MonetDB?
No I don't. Could you try to run the queries with out java, ie simply using mclient -lsql? Niels
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(); }
-- Niels Nes, Centre for Mathematics and Computer Science (CWI) Kruislaan 413, 1098 SJ Amsterdam, The Netherlands room C0.02, phone ++31 20 592-4098, fax ++31 20 592-4312 url: http://www.cwi.nl/~niels e-mail: Niels.Nes@cwi.nl