Problems With CREATE TEMPORARY TABLE
|
View:
New views
7 Messages
—
Rating Filter:
Alert me
|
 |
|
|
|
Re: Problems With CREATE TEMPORARY TABLE
by Niels Nes
::
Rate this Message:
On Tue, Jul 15, 2008 at 03:23:15PM -0400, James Leigh wrote:
> 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(); 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 > 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(); > } > > } > > > > ------------------------------------------------------------------------- > This SF.Net email is sponsored by the Moblin Your Move Developer's challenge > Build the coolest Linux based applications with Moblin SDK & win great prizes > Grand prize is a trip for two to an Open Source event anywhere in the world > http://moblin-contest.org/redirect.php?banner_id=100&url=/ > _______________________________________________ > MonetDB-users mailing list > MonetDB-users@... > https://lists.sourceforge.net/lists/listinfo/monetdb-users -- 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@... ------------------------------------------------------------------------- This SF.Net email is sponsored by the Moblin Your Move Developer's challenge Build the coolest Linux based applications with Moblin SDK & win great prizes Grand prize is a trip for two to an Open Source event anywhere in the world http://moblin-contest.org/redirect.php?banner_id=100&url=/ _______________________________________________ MonetDB-users mailing list MonetDB-users@... https://lists.sourceforge.net/lists/listinfo/monetdb-users |
|
|
Re: Problems With CREATE TEMPORARY TABLE
by James Leigh
::
Rate this Message:
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(); > > } ------------------------------------------------------------------------- This SF.Net email is sponsored by the Moblin Your Move Developer's challenge Build the coolest Linux based applications with Moblin SDK & win great prizes Grand prize is a trip for two to an Open Source event anywhere in the world http://moblin-contest.org/redirect.php?banner_id=100&url=/ _______________________________________________ MonetDB-users mailing list MonetDB-users@... https://lists.sourceforge.net/lists/listinfo/monetdb-users |
|
|
Re: Problems With CREATE TEMPORARY TABLE
by Niels Nes
::
Rate this Message:
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 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@... ------------------------------------------------------------------------- This SF.Net email is sponsored by the Moblin Your Move Developer's challenge Build the coolest Linux based applications with Moblin SDK & win great prizes Grand prize is a trip for two to an Open Source event anywhere in the world http://moblin-contest.org/redirect.php?banner_id=100&url=/ _______________________________________________ MonetDB-users mailing list MonetDB-users@... https://lists.sourceforge.net/lists/listinfo/monetdb-users |
|
|
Re: Problems With CREATE TEMPORARY TABLE
by James Leigh
::
Rate this Message:
On Wed, 2008-07-16 at 18:04 +0200, Niels Nes wrote:
> On Wed, Jul 16, 2008 at 11:48:58AM -0400, James Leigh wrote: > > > > 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. It is a problem if the schema is not fixed and there are multiple connections that all need to share data in a new table. However, as I said it is manageable. > > (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 The sql is fine, it runs the first time - no problems. However, when executed again (after a new table is created) it fails. James ------------------------------------------------------------------------- This SF.Net email is sponsored by the Moblin Your Move Developer's challenge Build the coolest Linux based applications with Moblin SDK & win great prizes Grand prize is a trip for two to an Open Source event anywhere in the world http://moblin-contest.org/redirect.php?banner_id=100&url=/ _______________________________________________ MonetDB-users mailing list MonetDB-users@... https://lists.sourceforge.net/lists/listinfo/monetdb-users |
|
|
Re: Problems With CREATE TEMPORARY TABLE
by James Leigh
::
Rate this Message:
On Wed, 2008-07-16 at 18:04 +0200, Niels Nes wrote:
> > (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 Here you can see that the "exec 0();" command works before a table is created, but is lost once a table is created. sql>prepare select 0; sql>exec 0(); +--------------+ | single_value | +==============+ | 0 | +--------------+ sql>create table blah(blah integer); sql>exec 0(); sql>prepare select 1; sql>exec 0(); +--------------+ | single_value | +==============+ | 1 | +--------------+ ------------------------------------------------------------------------- This SF.Net email is sponsored by the Moblin Your Move Developer's challenge Build the coolest Linux based applications with Moblin SDK & win great prizes Grand prize is a trip for two to an Open Source event anywhere in the world http://moblin-contest.org/redirect.php?banner_id=100&url=/ _______________________________________________ MonetDB-users mailing list MonetDB-users@... https://lists.sourceforge.net/lists/listinfo/monetdb-users |
|
|
Re: Problems With CREATE TEMPORARY TABLE
by Niels Nes
::
Rate this Message:
On Mon, Jul 21, 2008 at 10:07:04AM -0400, James Leigh wrote:
> On Wed, 2008-07-16 at 18:04 +0200, Niels Nes wrote: > > > (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 > > > Here you can see that the "exec 0();" command works before a table is > created, but is lost once a table is created. > > sql>prepare select 0; > sql>exec 0(); > +--------------+ > | single_value | > +==============+ > | 0 | > +--------------+ > sql>create table blah(blah integer); > sql>exec 0(); > sql>prepare select 1; > sql>exec 0(); > +--------------+ > | single_value | > +==============+ > | 1 | > +--------------+ > > Good catch. The problem is indeed that the query and prepared statement cache is cleaned after an 'schema' statement. A simple reorder of the code should help. A better error message should afcourse be given. A more flexible prepare/execute scheme could be a nice feature request. Niels -- 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@... ------------------------------------------------------------------------- This SF.Net email is sponsored by the Moblin Your Move Developer's challenge Build the coolest Linux based applications with Moblin SDK & win great prizes Grand prize is a trip for two to an Open Source event anywhere in the world http://moblin-contest.org/redirect.php?banner_id=100&url=/ _______________________________________________ MonetDB-users mailing list MonetDB-users@... https://lists.sourceforge.net/lists/listinfo/monetdb-users |
| Free Forum Powered by Nabble | Forum Help |