Monday, April 13, 2009

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
Reblog this post [with Zemanta]

3 comments:

Philipp Kursawe said...

If DTP would use OSGi more efficiently, there would be no need to specify a class-path to drivers anymore. Just name the driver and let OSGi do the rest.

Erik said...

Is this available only in the 1.7 release, I don't see that API within 1.6.

aliyaa said...

People must consider icu nurse resume for better opportunities in job and admission. A well written resume is bets in all.