JDBC and WHERE IN clauses
Hi, Does anybody know of a way to use a PreparedStatement with a WHERE IN (?) clause e.g. SELECT * FROM Names WHERE ID IN (?) where the parameter is replaced by an array of e.g. int's ? regards Tony Seebregts
Re: derby performance and 'order by'
Rick Hillegas wrote: Thanks for the pointer to this presentation, Oyvind. It's a pretty startling observation though I'm not sure how to use it. I'd be interested in hearing your thoughts about this some time. The question raised in this presentation is whether the optimizers in the databases examined (Oracle, DB2 and SQL Server) are actually too clever, evaluating (in some cases) 100+ query plans for a single query when simply changing the selectivity on two relations. The assumption is that they could evaluate far less plans and still perform well. Now, in Derby, I think we're still on the opposite side - we should probably investigate more optimization techniques and execution plans, as this thread has shown. -- Oyvind Bakksjo Sun Microsystems, Database Technology Group Trondheim, Norway http://weblogs.java.net/blog/bakksjo/
Re: Locks not released on error
LC == Lars Clausen [EMAIL PROTECTED] writes: LC Hi! LC We're using Derby version 10.1 (Bundle-Version: 10.1.100.208786) LC embeddedly in our system. Even though I've put extensive rollback and LC statement closing handling in the code, we still occasionally see cases LC where a table gets permanently locked after an error during update LC (detail below). Is there something other than closing open statements LC and rolling back existing savepoints that could be required for LC releasing locks? Am I doing the closing/rolling back wrongly? Any LC other ideas on how to cure this would be welcome. Lars, Note that rolling back to savepoints does not roll back the entire transaction. This means that locks set by the transaction are not released. In order to release locks you need to do call Connection.rollback() (without any parameters). -- Øystein
Re: Locks not released on error
On fre, 2005-09-23 at 09:43, Øystein Grøvlen wrote: LC == Lars Clausen [EMAIL PROTECTED] writes: LC Hi! LC We're using Derby version 10.1 (Bundle-Version: 10.1.100.208786) LC embeddedly in our system. Even though I've put extensive rollback and LC statement closing handling in the code, we still occasionally see cases LC where a table gets permanently locked after an error during update LC (detail below). Is there something other than closing open statements LC and rolling back existing savepoints that could be required for LC releasing locks? Am I doing the closing/rolling back wrongly? Any LC other ideas on how to cure this would be welcome. Lars, Note that rolling back to savepoints does not roll back the entire transaction. This means that locks set by the transaction are not released. In order to release locks you need to do call Connection.rollback() (without any parameters). I see. Sun's javadoc is not entirely clear on that, but I now found it deep in the Derby docs. So essentially, I should avoid using explicit savepoints without a very good reason, but just do Connection.rollback in the finally block (since it would normally come after the Connection.commit call, it shouldn't matter when things go well, unless there's a performance hit on trying a rollback right after a commit?) Thanks for the help! -Lars
Re: JDBC and WHERE IN clauses
Don't think you can do this in Derby. However, you can do: SELECT * FROM Names WHERE ID IN (?, ?, ?) and bind individual elements of the array to each of the parameters. This, of course, forces you to know the number of elements of the array.. Satheesh Tony Seebregts wrote: Hi, Does anybody know of a way to use a PreparedStatement with a WHERE IN (?) clause e.g. SELECT * FROM Names WHERE ID IN (?) where the parameter is replaced by an array of e.g. int's ? regards Tony Seebregts
Re: JDBC and WHERE IN clauses
Hi Tony, You could try using a temporary table to hold your parameter values. By changing the contents of the temporary table you might achieve what you want. Something like the following: declare global temporary table session.ztemp ( param int ) not logged; select * from names where id in ( select param from session.ztemp ); Cheers, -Rick Tony Seebregts wrote: Hi, Does anybody know of a way to use a PreparedStatement with a WHERE IN (?) clause e.g. SELECT * FROM Names WHERE ID IN (?) where the parameter is replaced by an array of e.g. int's ? regards Tony Seebregts
Re: Too narrow column display width using ij.
If you add a cast to 'columndatatype', you can achieve the 'minimumDisplayWidth' like functionality. ij select columnname, cast(columndatatype as char(20)) from sys.syscolumns where referenceid in (select tableid from sys.systables a, sys.sysschemas b where a.schemaid=b.schemaid and b.schemaname='APP' and a.tablename='MYTABLE'); COLUMNNAME |2 - MY_COUNTER |INTEGER NOT NULL 1 row selected Satheesh [EMAIL PROTECTED] wrote: Hi gurus, I displayed the description of a table (MYTABLE) using the following sql with ij, but the second column's display width was too narrow to see... SQL== ij select columnname, columndatatype from sys.syscolumns where referenceid in (select tableid from sys.systables a, sys.sysschemas b where a.schemaid=b.schemaid and b.schemaname='APP' and a.tablename='MYTABLE'); === Result=== COLUMNNAME |COLUMNDATATYPE MY_COUNTER |INTEGER NOT NU === (I made the MYTABLE talbe using "create table MYTABLE (my_counter int generated always as identity (start with 1));".) How can I expand the display width of the second column ? I thought ij have a minimumdisplaywidth option since it has the maximumdisplaywidth, but actually it doesn't have the minimumdisplaywidth option. hm... Regards, wolfgang __ For All Sports Fans! http://pr.mail.yahoo.co.jp/yells/
Re: Too narrow column display width using ij.
Hi Satheesh, Thank you for your quick response. It works pretty good. :) wolfgang If you add a cast to 'columndatatype', you can achieve the'minimumDisplayWidth' like functionality. ij select columnname, cast(columndatatype as char(20))from sys.syscolumns where referenceid in (select tableid from sys.systables a, sys.sysschemas b where a.schemaid=b.schemaid and b.schemaname='APP' and a.tablename='MYTABLE'); COLUMNNAME |2 - MY_COUNTER |INTEGER NOT NULL 1 row selected Satheesh __ For All Sports Fans! http://pr.mail.yahoo.co.jp/yells/
