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