Re: How can I access BIRT classic model database using ij scripting tool.
Hi Sunitha, Thanks for your help. Now i'm able to connect the classic model DB via ij, using the statement, ij connect 'jdbc:derby:jar:(/var/EclipseBirt/eclipse/plugins/org.eclipse.birt.report.data.oda.sampledb_2.1.1.v20070205-1728/db/BirtSample.jar)BirtSample'; but couldn't able to access the tables in the database. Can you tell me how to do it. here is what i did: ij connect 'jdbc:derby:jar:(/var/EclipseBirt/eclipse/plugins/org.eclipse.birt.report.data.oda.sampledb_2.1.1.v20070205-1728/db/BirtSample.jar)BirtSample'; ij select * from customers; this statement shows the error: ERROR 42X05: Table/View 'CUSTOMERS' does not exist. Note: i'm able to execute the following statement. ij show tables; TABLE_SCHEM |TABLE_NAME|REMARKS SYS |SYSALIASES| SYS |SYSCHECKS | SYS |SYSCOLUMNS| SYS |SYSCONGLOMERATES | SYS |SYSCONSTRAINTS| SYS |SYSDEPENDS| SYS |SYSFILES | SYS |SYSFOREIGNKEYS| SYS |SYSKEYS | SYS |SYSSCHEMAS| SYS |SYSSTATEMENTS | SYS |SYSSTATISTICS | SYS |SYSTABLES | SYS |SYSTRIGGERS | SYS |SYSVIEWS | SYSIBM |SYSDUMMY1 | CLASSICMODELS |CUSTOMERS | CLASSICMODELS |EMPLOYEES | CLASSICMODELS |OFFICES | CLASSICMODELS |ORDERDETAILS | CLASSICMODELS |ORDERS| CLASSICMODELS |PAYMENTS | CLASSICMODELS |PRODUCTLINES | CLASSICMODELS |PRODUCTS | 24 rows selected ij Thanks, Senthil. Sunitha Kambhampati wrote: Senthil Gugan wrote: Hi, I am working on LINUX SuSe10.2 OS with the following version of softwares, jdk1.5.0_11, eclipse3.2(BIRT2.1.2). I have installed the Derby plugin in the Eclipse plugin Directory. Here my problem is I couldn't able to access the BIRT classic model database using the ij scripting tool. Can anybody tell me is it possible to access the BIRT classic model database using the ij scripting tool?. The BIRT Classic model database is in a jar file. I have the birt-database-2.2M2 downloaded on my machine and the database is inside BirtSample.jar under birt-database-2.2M2\ClassicModels\derby\bin More info in docs about how to access database in a jar is here: http://db.apache.org/derby/docs/dev/devguide/cdevdvlp24155.html To access this via ij , set classpath to include the jar. $ export CLASSPATH=BirtSample.jar;$CLASSPATH $ java org.apache.derby.tools.ij ij version 10.2 ij connect 'jdbc:derby:jar:(C:\work\scripts\BirtSample.jar)BirtSample'; ij show tables; TABLE_SCHEM |TABLE_NAME|REMARKS SYS |SYSALIASES| SYS |SYSCHECKS | SYS |SYSCOLUMNS| SYS |SYSCONGLOMERATES | SYS |SYSCONSTRAINTS| SYS |SYSDEPENDS| SYS |SYSFILES | SYS |SYSFOREIGNKEYS| SYS |SYSKEYS | SYS |SYSSCHEMAS| SYS |SYSSTATEMENTS | SYS |SYSSTATISTICS | SYS |SYSTABLES | SYS |SYSTRIGGERS | SYS |SYSVIEWS | SYSIBM |SYSDUMMY1 | CLASSICMODELS |CUSTOMERS | CLASSICMODELS |EMPLOYEES | CLASSICMODELS |OFFICES | CLASSICMODELS |ORDERDETAILS | CLASSICMODELS |ORDERS|
Problem in release connection
Hi, I am using Cloudscape(IBM Cloudscape Version 10.1) and derbyclient.jar provided by derby. Following are the code I am using for clean the database connection and also PreparedStatement. public void cleanup(Connection aoConnection, PreparedStatement aoPreparedStatement) { try{ if(!(aoConnection ==null || aoConnection.isClosed())) aoConnection.close();//Release Connection if(aoPreparedStatement != null) aoPreparedStatement.close(); }catch(SQLException aoSQLException){ aoSQLException.printStackTrace(); } } But some time this method call get hang. Can any one tell me why it is happening? I have one more problem with this database. I am updating a table using following code. //Table create script CREATE TABLE TABLE_MEMORY ( MEMORY_ID BIGINT NOT NULL GENERATED ALWAYS AS IDENTITY, MEMORY SMALLINT, PRIMARY KEY (MEMORY_ID) ); //JAVA Code Connection aoConnection = getConnection(); PreparedStatement psUpdate = aoConnection.prepareStatement(UPDATE TABLE_MEMORY SET MEMORY=? WHERE MEMORY_ID=?); psUpdate.setInt(counter++, 38650); psUpdate.setInt(counter++, 15254); int updated = psUpdate.executeUpdate(); If I run above code, executeUpdate method does not throw any exception and always returns value 1. But, due to size of value I am setting (38650) is more than size of smallint, so it does not update database. Can any one tell me why value return by executeUpdate method is 1 even database update is failed? For any reply thanks in advance. Thanks Regards Shambhu Kumar Sinha
Re: Problem in release connection
Hi, Maybe it would help to close the prepared statement befor you close the connection, which also would be the natural way of cleaning up. Bernt Shambhu wrote (2007-03-21 18:55:29): Hi, I am using Cloudscape(IBM Cloudscape Version 10.1) and derbyclient.jar provided by derby. Following are the code I am using for clean the database connection and also PreparedStatement. public void cleanup(Connection aoConnection, PreparedStatement aoPreparedStatement) { try{ if(!(aoConnection ==null || aoConnection.isClosed())) aoConnection.close();//Release Connection if(aoPreparedStatement != null) aoPreparedStatement.close(); }catch(SQLException aoSQLException){ aoSQLException.printStackTrace(); } } But some time this method call get hang. Can any one tell me why it is happening? I have one more problem with this database. I am updating a table using following code. //Table create script CREATE TABLE TABLE_MEMORY ( MEMORY_ID BIGINT NOT NULL GENERATED ALWAYS AS IDENTITY, MEMORY SMALLINT, PRIMARY KEY (MEMORY_ID) ); //JAVA Code Connection aoConnection = getConnection(); PreparedStatement psUpdate = aoConnection.prepareStatement(UPDATE TABLE_MEMORY SET MEMORY=? WHERE MEMORY_ID=?); psUpdate.setInt(counter++, 38650); psUpdate.setInt(counter++, 15254); int updated = psUpdate.executeUpdate(); If I run above code, executeUpdate method does not throw any exception and always returns value 1. But, due to size of value I am setting (38650) is more than size of smallint, so it does not update database. Can any one tell me why value return by executeUpdate method is 1 even database update is failed? For any reply thanks in advance. Thanks Regards Shambhu Kumar Sinha -- Bernt Marius Johnsen, Database Technology Group, Staff Engineer, Technical Lead Derby/Java DB Sun Microsystems, Trondheim, Norway signature.asc Description: Digital signature
Aggregates
I need to use several aggregate functions, including most of the built-in aggregates (count, min, max, avg, etc.) as well as some custom aggregates (standard deviation and various percentiles). Is there any documentation or example on how to create custom aggregate functions? I have found one reference [1] which only mentions that custom aggregates can be created, but contains no other details. (Also, are there any plans to include standard deviation or percentiles as built-in aggregates in derby?) [1] http://db.apache.org/derby/docs/10.2/ref/rrefsqlj33923.html#rrefsqlj33923__sqlj92398
Re: Aggregates
Hi Shelley, There is an enhancement request logged for this feature: http://issues.apache.org/jira/browse/DERBY-672. No-one has picked up this enhancement request yet--you are welcome to! Most of the code needed for this feature is actually in the Derby codeline. I think the blocking issue is that the community must agree on syntax for declaring user defined aggregates. Right now you can fake a user defined aggregate. An example of how to do this can be found in the scores demo package in java/demo/scores. For more details, please grep that subtree for getMedianTestScore. Hope this helps, -Rick Shelley L wrote: I need to use several aggregate functions, including most of the built-in aggregates (count, min, max, avg, etc.) as well as some custom aggregates (standard deviation and various percentiles). Is there any documentation or example on how to create custom aggregate functions? I have found one reference [1] which only mentions that custom aggregates can be created, but contains no other details. (Also, are there any plans to include standard deviation or percentiles as built-in aggregates in derby?) [1] http://db.apache.org/derby/docs/10.2/ref/rrefsqlj33923.html#rrefsqlj33923__sqlj92398 http://db.apache.org/derby/docs/10.2/ref/rrefsqlj33923.html#rrefsqlj33923__sqlj92398
Re: Problem in release connection
provided by derby. Following are the code I am using for clean the database connection and also PreparedStatement. if(!(aoConnection ==null || aoConnection.isClosed())) aoConnection.close();//Release Connection if(aoPreparedStatement != null) aoPreparedStatement.close(); I think it would be better to close the statement before you close the connection. I also tend not to bother with calling isClosed methods. Instead I set the variable to null after I close the connection. I think that might also help the garbage collector find more garbage to collect. So I'd write something more like: if (aoPreparedStatement != null) { aoPreparedStatement.close(); aoPreparedStatement = null; } if (aoConnection != null) { aoConnection.close(); aoConnection = null; } thanks, bryan
Re: How can I access BIRT classic model database using ij scripting tool.
You need to specify the schema name or set your default schema to CLASSICMODELS - select * from claisicmodels.customers; should work Senthil Gugan wrote: Hi Sunitha, Thanks for your help. Now i'm able to connect the classic model DB via ij, using the statement, ij connect 'jdbc:derby:jar:(/var/EclipseBirt/eclipse/plugins/org.eclipse.birt.report.data.oda.sampledb_2.1.1.v20070205-1728/db/BirtSample.jar)BirtSample'; but couldn't able to access the tables in the database. Can you tell me how to do it. here is what i did: ij connect 'jdbc:derby:jar:(/var/EclipseBirt/eclipse/plugins/org.eclipse.birt.report.data.oda.sampledb_2.1.1.v20070205-1728/db/BirtSample.jar)BirtSample'; ij select * from customers; this statement shows the error: ERROR 42X05: Table/View 'CUSTOMERS' does not exist. Note: i'm able to execute the following statement. ij show tables; TABLE_SCHEM |TABLE_NAME|REMARKS SYS |SYSALIASES| SYS |SYSCHECKS | SYS |SYSCOLUMNS| SYS |SYSCONGLOMERATES | SYS |SYSCONSTRAINTS| SYS |SYSDEPENDS| SYS |SYSFILES | SYS |SYSFOREIGNKEYS| SYS |SYSKEYS | SYS |SYSSCHEMAS| SYS |SYSSTATEMENTS | SYS |SYSSTATISTICS | SYS |SYSTABLES | SYS |SYSTRIGGERS | SYS |SYSVIEWS | SYSIBM |SYSDUMMY1 | CLASSICMODELS |CUSTOMERS | CLASSICMODELS |EMPLOYEES | CLASSICMODELS |OFFICES | CLASSICMODELS |ORDERDETAILS | CLASSICMODELS |ORDERS| CLASSICMODELS |PAYMENTS | CLASSICMODELS |PRODUCTLINES | CLASSICMODELS |PRODUCTS | 24 rows selected ij Thanks, Senthil. Sunitha Kambhampati wrote: Senthil Gugan wrote: Hi, I am working on LINUX SuSe10.2 OS with the following version of softwares, jdk1.5.0_11, eclipse3.2(BIRT2.1.2). I have installed the Derby plugin in the Eclipse plugin Directory. Here my problem is I couldn't able to access the BIRT classic model database using the ij scripting tool. Can anybody tell me is it possible to access the BIRT classic model database using the ij scripting tool?. The BIRT Classic model database is in a jar file. I have the birt-database-2.2M2 downloaded on my machine and the database is inside BirtSample.jar under birt-database-2.2M2\ClassicModels\derby\bin More info in docs about how to access database in a jar is here: http://db.apache.org/derby/docs/dev/devguide/cdevdvlp24155.html To access this via ij , set classpath to include the jar. $ export CLASSPATH=BirtSample.jar;$CLASSPATH $ java org.apache.derby.tools.ij ij version 10.2 ij connect 'jdbc:derby:jar:(C:\work\scripts\BirtSample.jar)BirtSample'; ij show tables; TABLE_SCHEM |TABLE_NAME|REMARKS SYS |SYSALIASES| SYS |SYSCHECKS | SYS |SYSCOLUMNS| SYS |SYSCONGLOMERATES | SYS |SYSCONSTRAINTS| SYS |SYSDEPENDS| SYS |SYSFILES | SYS |SYSFOREIGNKEYS| SYS |SYSKEYS | SYS |SYSSCHEMAS| SYS |SYSSTATEMENTS | SYS |SYSSTATISTICS | SYS |SYSTABLES | SYS |SYSTRIGGERS | SYS |SYSVIEWS | SYSIBM |SYSDUMMY1 | CLASSICMODELS |CUSTOMERS | CLASSICMODELS |EMPLOYEES | CLASSICMODELS |OFFICES
Re: Connection reset
sometimes i get a Connection reset (i'm also investigating why this happens but i think i've found a problem with derby) when calling a remote derby db. the connection is closed but the transaction is still in progress (see last table at the bottom of the mail). I think that a Connection Reset can mean that the client was terminated abruptly without closing all its Statement and Connection objects. That is, it just hard-closed the TCP/IP connection without closing all the JDBC resources first. When that happens, I think that the server will eventually figure out that the connection has been closed, and will abort the in-progress transaction. I am not sure how long it will take for the server to figure this out. Perhaps it depends on how the network is configured. Does the behavior that you are seeing differ from this? thanks, bryan
Re: invalid checksum
we installed the app serveral times and i think the following lines are per installation (so per installation it happend on the same page): org.hibernate.util.JDBCExceptionReporter - Invalid checksum on Page Page(28,Container(0, 1248)), expected=3'455'715'557, on-disk version=3'357'396'866, page dump follows: Hex dump: org.hibernate.util.JDBCExceptionReporter - Invalid checksum on Page Page(461,Container(0, 1248)), expected=3'908'279'257, on-disk version=137'782'528, page dump follows: Hex dump: org.hibernate.util.JDBCExceptionReporter - Invalid checksum on Page Page(482,Container(0, 1248)), expected=4'113'528'744, on-disk version=44'301'386, page dump follows: Hex dump: org.hibernate.util.JDBCExceptionReporter - Invalid checksum on Page Page(702,Container(0, 944)), expected=2'598'292'545, on-disk version=3'174'535'138, page dump follows: Hex dump: org.hibernate.util.JDBCExceptionReporter - Invalid checksum on Page Page(2767,Container(0, 944)), expected=2'067'623'629, on-disk version=2'180'532'620, page dump follows: Hex dump: org.hibernate.util.JDBCExceptionReporter - Invalid checksum on Page Page(99,Container(0, 1248)), expected=242'030'294, on-disk version=3'389'026'885, page dump follows: Hex dump: That looks to me like it is happening in several different containers (1248 and also 944), and on several different pages (28, 461, 482, 702, 2767, 99). Perhaps that particular computer has a hard disk that is failing. Do you use NTFS on that computer? Can you check the computer's event log and see if the operating system is reporting hardware errors? thanks, bryan
Re: Connection reset
hi bryan thanks for your reply. i'm pretty shure that it was a hard close. it might be that it would have unlocked the table but i was not patient enough to testing it. do you know where to set such timeouts on linux/derby? fabian - Original Message - From: Bryan Pendleton [EMAIL PROTECTED] To: Derby Discussion derby-user@db.apache.org Sent: Wednesday, March 21, 2007 5:57 PM Subject: Re: Connection reset sometimes i get a Connection reset (i'm also investigating why this happens but i think i've found a problem with derby) when calling a remote derby db. the connection is closed but the transaction is still in progress (see last table at the bottom of the mail). I think that a Connection Reset can mean that the client was terminated abruptly without closing all its Statement and Connection objects. That is, it just hard-closed the TCP/IP connection without closing all the JDBC resources first. When that happens, I think that the server will eventually figure out that the connection has been closed, and will abort the in-progress transaction. I am not sure how long it will take for the server to figure this out. Perhaps it depends on how the network is configured. Does the behavior that you are seeing differ from this? thanks, bryan
Re: Single Transaction Using Multiple Method Calls That Open New Database Connections
Hi David, I kept a copy of your message because I was intested in the DatabaseManager program that you attached. I am just studying the code and I am curious to know if the two tables REQUEST and EVENTS are for the purpose of testing the program, or if they have another purpose that I am missing? Excuse my ignorance, but I'm just learning this topic. Thanks for your help, Bill - Original Message - From: David Van Couvering [EMAIL PROTECTED] To: Derby Discussion derby-user@db.apache.org; [EMAIL PROTECTED] Sent: Thursday, February 15, 2007 6:48 PM Subject: Re: Single Transaction Using Multiple Method Calls That Open New Database Connections Sheesh, I forgot about this. I've actually done that before. This is part of my local calendar demo; see how I use thread local storage to keep a connection. This is in the Derby codeline... David Craig L Russell wrote: Hi Sisilla, You might look at using a java.lang.ThreadLocal to store the Connection or some object that stores more state including the Connection. Since you don't have to pass the Connection through multiple layers, it can make your code simpler and less messy. Craig On Feb 15, 2007, at 9:24 AM, Sisilla wrote: Thank you very much, David. I appreciate your time and consideration. I chose to use one Connection Object and pass it to all my methods so no new connections were opened. It makes my code messier, but I don't know anything about JTA. Again, thanks for the suggestion. ~Sisilla David Van Couvering-2 wrote: If you're using multiple connections, then you need to use JTA to have it work in a single transaction, and your database driver needs to support XA. If you're running in a J2EE environment with declarative transaction management enabled, you're good to go. If you're running in a web container, you may be able to get a reference to a UserTransaction object through JNDI (look up UserTransaction in Google). Some web containers provide JTA support, others don't and you'll need to get it from a third party. Others may provide details and/or corrections to what I say here, but that's the general idea. David Sisilla wrote: Hello All, I would like to update several tables in one transaction. The updates happen via several method calls, and each of these methods open new database connections. The methods also contain calls to other methods that open new database connections. Is it at all possible that these updates could be handled as a single transaction without eliminating these method calls? I am using conn.setAutoCommit(false); before the updates and conn.commit(); after the updates, but it isn't working as is. I am using Derby 10.2.1.6 and JDK 1.6.0 on Windows XP Professional. I appreciate any help. Thanks, Sisilla --View this message in context: http://www.nabble.com/Single-Transaction-Using-Multiple-Method-Calls-That-Open-New-Database-Connections-tf3228984.html#a8989769 Sent from the Apache Derby Users mailing list archive at Nabble.com. Craig Russell DB PMC [EMAIL PROTECTED] http://db.apache.org/jdo /* Licensed to the Apache Software Foundation (ASF) under one or more contributor license agreements. See the NOTICE file distributed with this work for additional information regarding copyright ownership. The ASF licenses this file to You under the Apache License, Version 2.0 (the License); you may not use this file except in compliance with the License. You may obtain a copy of the License at http://www.apache.org/licenses/LICENSE-2.0 Unless required by applicable law or agreed to in writing, software distributed under the License is distributed on an AS IS BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. See the License for the specific language governing permissions and limitations under the License. */ import javax.sql.*; import java.sql.*; import org.apache.derby.jdbc.EmbeddedDataSource; /** * A container for the singleton data source, so we don't have to * create a separate one for each class that wants to do JDBC */ public class DatabaseManager { private static EmbeddedDataSource ds; public static String REQUESTS_TABLE = APP.REQUESTS; public static String EVENTS_TABLE = APP.EVENTS; // We want to keep the same connection for a given thread // as long as we're in the same transaction private static ThreadLocalConnection tranConnection = new ThreadLocal(); private static void initDataSource(String dbname, String user, String password) { ds = new EmbeddedDataSource(); ds.setDatabaseName(dbname); ds.setUser(user); ds.setPassword(password); ds.setCreateDatabase(create); } public static void logSql() throws Exception { executeUpdate(CALL SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY( +
Re: Single Transaction Using Multiple Method Calls That Open New Database Connections
Hi, Bill The two tables REQUEST and EVENTS are specific to the application where I wrote the DatabaseManager. They are used to store local calendar events and to store pending requests to Google Calendar. In terms of the DatabaseManager, if you want this to be a generic utility class, you should probably get rid of references to these tables (I think they're used only in clearTables if I remember right). David Bill Slack wrote: Hi David, I kept a copy of your message because I was intested in the DatabaseManager program that you attached. I am just studying the code and I am curious to know if the two tables REQUEST and EVENTS are for the purpose of testing the program, or if they have another purpose that I am missing? Excuse my ignorance, but I'm just learning this topic. Thanks for your help, Bill - Original Message - From: David Van Couvering [EMAIL PROTECTED] To: Derby Discussion derby-user@db.apache.org; [EMAIL PROTECTED] Sent: Thursday, February 15, 2007 6:48 PM Subject: Re: Single Transaction Using Multiple Method Calls That Open New Database Connections Sheesh, I forgot about this. I've actually done that before. This is part of my local calendar demo; see how I use thread local storage to keep a connection. This is in the Derby codeline... David Craig L Russell wrote: Hi Sisilla, You might look at using a java.lang.ThreadLocal to store the Connection or some object that stores more state including the Connection. Since you don't have to pass the Connection through multiple layers, it can make your code simpler and less messy. Craig On Feb 15, 2007, at 9:24 AM, Sisilla wrote: Thank you very much, David. I appreciate your time and consideration. I chose to use one Connection Object and pass it to all my methods so no new connections were opened. It makes my code messier, but I don't know anything about JTA. Again, thanks for the suggestion. ~Sisilla David Van Couvering-2 wrote: If you're using multiple connections, then you need to use JTA to have it work in a single transaction, and your database driver needs to support XA. If you're running in a J2EE environment with declarative transaction management enabled, you're good to go. If you're running in a web container, you may be able to get a reference to a UserTransaction object through JNDI (look up UserTransaction in Google). Some web containers provide JTA support, others don't and you'll need to get it from a third party. Others may provide details and/or corrections to what I say here, but that's the general idea. David Sisilla wrote: Hello All, I would like to update several tables in one transaction. The updates happen via several method calls, and each of these methods open new database connections. The methods also contain calls to other methods that open new database connections. Is it at all possible that these updates could be handled as a single transaction without eliminating these method calls? I am using conn.setAutoCommit(false); before the updates and conn.commit(); after the updates, but it isn't working as is. I am using Derby 10.2.1.6 and JDK 1.6.0 on Windows XP Professional. I appreciate any help. Thanks, Sisilla --View this message in context: http://www.nabble.com/Single-Transaction-Using-Multiple-Method-Calls-That-Open-New-Database-Connections-tf3228984.html#a8989769 Sent from the Apache Derby Users mailing list archive at Nabble.com. Craig Russell DB PMC [EMAIL PROTECTED] http://db.apache.org/jdo /* Licensed to the Apache Software Foundation (ASF) under one or more contributor license agreements. See the NOTICE file distributed with this work for additional information regarding copyright ownership. The ASF licenses this file to You under the Apache License, Version 2.0 (the License); you may not use this file except in compliance with the License. You may obtain a copy of the License at http://www.apache.org/licenses/LICENSE-2.0 Unless required by applicable law or agreed to in writing, software distributed under the License is distributed on an AS IS BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. See the License for the specific language governing permissions and limitations under the License. */ import javax.sql.*; import java.sql.*; import org.apache.derby.jdbc.EmbeddedDataSource; /** * A container for the singleton data source, so we don't have to * create a separate one for each class that wants to do JDBC */ public class DatabaseManager { private static EmbeddedDataSource ds; public static String REQUESTS_TABLE = APP.REQUESTS; public static String EVENTS_TABLE = APP.EVENTS; // We want to keep the same connection for a given thread // as long as we're in the same transaction private static
Re: Single Transaction Using Multiple Method Calls That Open New Database Connections
Thanks for clarifying David. Bill - Original Message - From: David Van Couvering [EMAIL PROTECTED] To: Derby Discussion derby-user@db.apache.org Sent: Wednesday, March 21, 2007 3:21 PM Subject: Re: Single Transaction Using Multiple Method Calls That Open New Database Connections Hi, Bill The two tables REQUEST and EVENTS are specific to the application where I wrote the DatabaseManager. They are used to store local calendar events and to store pending requests to Google Calendar. In terms of the DatabaseManager, if you want this to be a generic utility class, you should probably get rid of references to these tables (I think they're used only in clearTables if I remember right). David Bill Slack wrote: Hi David, I kept a copy of your message because I was intested in the DatabaseManager program that you attached. I am just studying the code and I am curious to know if the two tables REQUEST and EVENTS are for the purpose of testing the program, or if they have another purpose that I am missing? Excuse my ignorance, but I'm just learning this topic. Thanks for your help, Bill - Original Message - From: David Van Couvering [EMAIL PROTECTED] To: Derby Discussion derby-user@db.apache.org; [EMAIL PROTECTED] Sent: Thursday, February 15, 2007 6:48 PM Subject: Re: Single Transaction Using Multiple Method Calls That Open New Database Connections Sheesh, I forgot about this. I've actually done that before. This is part of my local calendar demo; see how I use thread local storage to keep a connection. This is in the Derby codeline... David Craig L Russell wrote: Hi Sisilla, You might look at using a java.lang.ThreadLocal to store the Connection or some object that stores more state including the Connection. Since you don't have to pass the Connection through multiple layers, it can make your code simpler and less messy. Craig On Feb 15, 2007, at 9:24 AM, Sisilla wrote: Thank you very much, David. I appreciate your time and consideration. I chose to use one Connection Object and pass it to all my methods so no new connections were opened. It makes my code messier, but I don't know anything about JTA. Again, thanks for the suggestion. ~Sisilla David Van Couvering-2 wrote: If you're using multiple connections, then you need to use JTA to have it work in a single transaction, and your database driver needs to support XA. If you're running in a J2EE environment with declarative transaction management enabled, you're good to go. If you're running in a web container, you may be able to get a reference to a UserTransaction object through JNDI (look up UserTransaction in Google). Some web containers provide JTA support, others don't and you'll need to get it from a third party. Others may provide details and/or corrections to what I say here, but that's the general idea. David Sisilla wrote: Hello All, I would like to update several tables in one transaction. The updates happen via several method calls, and each of these methods open new database connections. The methods also contain calls to other methods that open new database connections. Is it at all possible that these updates could be handled as a single transaction without eliminating these method calls? I am using conn.setAutoCommit(false); before the updates and conn.commit(); after the updates, but it isn't working as is. I am using Derby 10.2.1.6 and JDK 1.6.0 on Windows XP Professional. I appreciate any help. Thanks, Sisilla --View this message in context: http://www.nabble.com/Single-Transaction-Using-Multiple-Method-Calls-That-Open-New-Database-Connections-tf3228984.html#a8989769 Sent from the Apache Derby Users mailing list archive at Nabble.com. Craig Russell DB PMC [EMAIL PROTECTED] http://db.apache.org/jdo /* Licensed to the Apache Software Foundation (ASF) under one or more contributor license agreements. See the NOTICE file distributed with this work for additional information regarding copyright ownership. The ASF licenses this file to You under the Apache License, Version 2.0 (the License); you may not use this file except in compliance with the License. You may obtain a copy of the License at http://www.apache.org/licenses/LICENSE-2.0 Unless required by applicable law or agreed to in writing, software distributed under the License is distributed on an AS IS BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. See the License for the specific language governing permissions and limitations under the License. */ import javax.sql.*; import java.sql.*; import org.apache.derby.jdbc.EmbeddedDataSource; /** * A container for the singleton data source, so we don't have to * create a separate one for each class that wants to do JDBC */ public class