DTP APis and How to Use a Transient Connection Profile

Hi there...

On the DTP newsgroup we had a question about using DTP APIs to create a new transient connection profile and then use that to execute some DDL...

It's pretty easy actually... The trick for the transient profile is knowing all the bits and pieces you have to have ahead of time, like the:

• provider ID, which is the connection profile type ID
  
• vendor and version, which relate to the vendor/version of the database you're connecting to
• and then the driver path. Note that you can also use a pre-defined driver and get the DriverInstance from the DriverManager, then retrieve various properties like the vendor, version, class name, and driver path from there
  

So you end up with something like this:

    private static String providerID = "org.eclipse.datatools.connectivity.db.derby.embedded.connectionProfile"; //$NON-NLS-1$
   private static String vendor = "Derby"; //$NON-NLS-1$
   private static String version = "10.1"; //$NON-NLS-1$

   private static String jarList = "C:\\Derby10.1.3.1\\db-derby-10.1.3.1-bin\\lib\\derby.jar"; //$NON-NLS-1$
   private static String dbPath = "c:\\DerbyDatabases\\MyDB"; //$NON-NLS-1$
   private static String userName = ""; //$NON-NLS-1$
   private static String password = ""; //$NON-NLS-1$

   private static String driverClass = "org.apache.derby.jdbc.EmbeddedDriver"; //$NON-NLS-1$
   private static String driverURL = "jdbc:derby:" + dbPath + ";create=true"; //$NON-NLS-1$ //$NON-NLS-2$

   public static Properties generateTransientDerbyProperties() {
       Properties baseProperties = new Properties();
       baseProperties.setProperty( IDriverMgmtConstants.PROP_DEFN_JARLIST, jarList );
       baseProperties.setProperty(IJDBCConnectionProfileConstants.DRIVER_CLASS_PROP_ID, driverClass);
       baseProperties.setProperty(IJDBCConnectionProfileConstants.URL_PROP_ID, driverURL);
       baseProperties.setProperty(IJDBCConnectionProfileConstants.USERNAME_PROP_ID, userName);
       baseProperties.setProperty(IJDBCConnectionProfileConstants.PASSWORD_PROP_ID, password);
       baseProperties.setProperty(IJDBCConnectionProfileConstants.DATABASE_VENDOR_PROP_ID, vendor);
       baseProperties.setProperty(IJDBCConnectionProfileConstants.DATABASE_VERSION_PROP_ID, version);
       baseProperties.setProperty( IJDBCConnectionProfileConstants.SAVE_PASSWORD_PROP_ID, String.valueOf( true ) );
       return baseProperties;
   }

   public void createTransientDerbyProfile() throws Exception {
       ProfileManager pm = ProfileManager.getInstance();
      
       IConnectionProfile transientDerby = pm.createTransientProfile(providerID, generateTransientDerbyProperties());
       // do something with the profile
      
   }

And then once you have your transient profile, connect, get the Java connection object, and execute your DDL...

        IStatus status = transientDerby.connect();
       if (status.equals(IStatus.OK)) {
           // success
           java.sql.Connection conn = getJavaConnectionForProfile(transientDerby);
           if (conn != null) {
               try {
                   java.sql.Statement stmt = conn.createStatement();
                   java.sql.ResultSet results = stmt.executeQuery("<INSERT QUERY/DDL HERE>");
               } catch (java.sql.SQLException sqle) {
                   sqle.printStackTrace();
               }

           }
          
       } else {
           // failure :(
           if (status.getException() != null) {
               status.getException().printStackTrace();
           }
       }

So not too bad. Great question though! Hope this helps!

--Fitz

Cool DTP Talks at EclipseCon 2009

Hey all!

I'm always amazed by the depth and breadth of talks at EclipseCon and this year is no different. In the Data Tooling category, we have a diverse set of talks (you can see the list here) on everything from some new tooling we've been working on, updates to the Graphical SQL Query Builder, how Ingres is rolling DTP components in new and unique ways, to using YouTube in DTP and how a commercial vendor (IBM) is using and extending DTP APIs for their PureQuery product.

I have to admit I'm sort of partial to the YouTube presentation I'm doing :), but I'm very curious to hear Ingres and IBM talk about their tooling and products and how DTP is playing a role in those.

For those of you just getting started with DTP, we have a tutorial scheduled for the Monday of the conference that's going to walk through a ton of topics from adding a new JDBC driver to the mix, to supporting a new database, and customizing SQL syntax and so on...

DTP is much more than just a great set of tools for data access... It's a great community. And EclipseCon is when that community comes together.

Come join our community!

--Fitz

Related articles by Zemanta

• Smoothly Blending Java and SQL with pureQuery
• New Open Source Database Offerings from Ingres and Sun
• Data Tools at EclipseCon 2009