Mailing-List: contact derby-user-help@db.apache.org; run by ezmlm
Precedence: bulk
Reply-To: "Derby Discussion" <derby-user@db.apache.org>
Received-SPF: pass (athena.apache.org: domain of rick.hillegas@oracle.com
 designates 148.87.113.117 as permitted sender)
Message-ID: <4FBBE9FC.8020306@oracle.com>
Date: Tue, 22 May 2012 12:33:16 -0700
From: Rick Hillegas <rick.hillegas@oracle.com>
User-Agent: Mozilla/5.0 (Macintosh; U; Intel Mac OS X 10.7; en-US;
 rv:1.9.2.18) Gecko/20110616 Thunderbird/3.1.11
MIME-Version: 1.0
To: derby-user@db.apache.org
Subject: Re: best way to check for the exsitence of a table
References: 
 <619F13B2042F204E8E8E93D73870255809CFA57E@EXJSQUSDAG04.ad.jefco.com>
In-Reply-To: 
 <619F13B2042F204E8E8E93D73870255809CFA57E@EXJSQUSDAG04.ad.jefco.com>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit

Hi Pavel,

Querying the metadata as you suggest is a good approach. You will want 
to adjust the arguments to DatabaseMetaData.getTables() however. The 
arguments in your example may find false matches in other schemas and 
with tables created with double-quoted names. The following example may 
help you adjust the arguments to getTable():

public class z
{
     public  static  void    main( String... args ) throws Exception
     {
         Connection  conn = DriverManager.getConnection( 
"jdbc:derby:memory:db;create=true" );

         conn.prepareStatement( "create table myTable( a int )" ).execute();

         DatabaseMetaData    dbmd = conn.getMetaData();
         ResultSet   rs;

         rs = dbmd.getTables( null, "APP", "MYTABLE", new String[] { 
"TABLE" } );
         System.out.println( "Table 'MYTABLE' exists = " + rs.next() );

         rs = dbmd.getTables( null, "APP", "mytable", new String[] { 
"TABLE" } );
         System.out.println( "Table 'mytable' exists = " + rs.next() );
     }

}

Hope this helps,
-Rick

On 5/22/12 11:33 AM, Pavel Bortnovskiy wrote:
>
> Hello:
>
> When reading derby docs, I noticed that the functionWwdUtils.wwdChk4Tablein Derby demos is implemented by executing an update on the table and then relying on the exception mechanism to determine whether or not the table exists.
>   
> My approach was to execute DatabaseMetaData.getTables method (pls see implementation below) and iterate through the resultset. I also wanted to assure case insensitivity.
>   
> Which method is more preferable from the point of efficiency and database operations (locks, etc).
> This code might be executed frequently and by multiple threads.
>   
> Thank you,
> Pavel.
>
>
>
>     public static boolean tableExists(final Connection connection, 
> final String tableName) throws SQLException {
>
>         try {
>
>             final DatabaseMetaData databaseMetaData = 
> connection.getMetaData();
>
>             final ResultSet resultSet = 
> databaseMetaData.getTables(null, null, null, new String[]{"TABLE"});
>
>             try {
>
>                 while (resultSet.next()) {
>
>                     if 
> (resultSet.getString("TABLE_NAME").equalsIgnoreCase(tableName)) {
>
>                         return true;
>
>                     }
>
>                 }
>
>             } finally {
>
>                 connection.commit();
>
>                 resultSet.close();
>
>             }
>
>             return false;
>
>         } catch (SQLException e) {
>
>             Statement statement = null;
>
>             try {
>
>                 final Connection c = 
> Components.getMemoryDb().getConnection(true);
>
>                 statement = c.createStatement();
>
>                 final String select = "SELECT * FROM 
> SYSCS_DIAG.LOCK_TABLE";
>
>                 final ResultSet resultSet = 
> statement.executeQuery(select);
>
>                 final int cnt = resultSet.getMetaData().getColumnCount();
>
>                 final StringBuilder builder = new 
> StringBuilder("Results of \"" + select + "\":");
>
>                 while (resultSet.next()) {
>
>                     builder.append("\n\t");
>
>                     for (int i = 1; i <= cnt; i++) {
>
>                         if (i > 1) {
>
>                             builder.append(",");
>
>                         }
>
>                         builder.append(resultSet.getObject(i));
>
>                     }
>
>                 }
>
>                 logger.info(builder.toString());
>
>             } finally {
>
>                 if (statement != null) {
>
>                     statement.close();
>
>                 }
>
>             }
>
>             throw e;
>
>         }
>
>     }
>
>
>             Jefferies archives and monitors outgoing and incoming
>             e-mail. The contents of this email, including any
>             attachments, are confidential to the ordinary user of the
>             email address to which it was addressed. If you are not
>             the addressee of this email you may not copy, forward,
>             disclose or otherwise use it or any part of it in any form
>             whatsoever. This email may be produced at the request of
>             regulators or in connection with civil litigation.
>             Jefferies accepts no liability for any errors or omissions
>             arising as a result of transmission. Use by other than
>             intended recipients is prohibited. In the United Kingdom,
>             Jefferies operates as Jefferies International Limited;
>             registered in England: no. 1978621; registered office:
>             Vintners Place, 68 Upper Thames Street, London EC4V 3BJ.
>             Jefferies International Limited is authorised and
>             regulated by the Financial Services Authority.
>