Jump to: navigation, search

Difference between revisions of "DTP FAQ"

 
(3 intermediate revisions by the same user not shown)
Line 37: Line 37:
 
       }
 
       }
 
       return null;
 
       return null;
 +
  }
 +
 +
'''Q: What can I do with the JDBC connection once I get it from DTP?'''
 +
 +
A: Simple answer is that you can do anything you can with any JDBC connection instance. For example, you could execute some DDL or a SQL statement:
 +
 +
  java.sql.Connection conn = getJavaConnectionForProfile(MyProfileInstance);
 +
  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();
 +
      }
 +
  }
 +
 +
'''Q: How do I create a Transient connection profile vs. a Persisted profile?'''
 +
 +
A: Under the covers the only difference between a Transient and a Persisted connection profile is that when the workbench is closed, the Transient profile is not saved between sessions. So once you create it, you can do whatever you'd like with it - and it will be closed and not persisted when Eclipse is shut down.
 +
 +
To create the Transient profile, you have to know a bit more information than you do for a normal profile created via the UI. 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
 +
 +
For example, to create a Transient Derby connection profile...
 +
 +
  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
 +
     
 
   }
 
   }
  
Line 58: Line 117:
 
       return null;
 
       return null;
 
   }
 
   }
 +
 +
== SQL Development Tools ==
 +
 +
'''Q: How do I execute DDL via API with a connection profile instance so my results appear in the Results View?'''
 +
 +
A: <TBA>
  
 
[[Category:Data_Tools_Platform]]
 
[[Category:Data_Tools_Platform]]

Latest revision as of 18:29, 2 October 2009

Back to DTP Main Page


This page should be a repository for all those "Frequently Asked Questions" or FAQs posted on the DTP newsgroups, mailing lists, and whatnot.

Connectivity

Q: How do I programmatically access an existing connection profile?

A: Access to existing connection profiles (persisted or transient) is done using the ProfileManager class (org.eclipse.datatools.connectivity.ProfileManager). So this is pretty simple:

  IConnectionProfile profile = ProfileManager.getInstance().getProfileByName("myprofile");

Q: How do I connect to an existing connection profile programmatically?

A: Again, pretty simple:

  IConnectionProfile profile = ProfileManager.getInstance().getProfileByName("myprofile");
  IStatus status = profile.connect();
  if (status.getCode() == IStatus.OK)) {
     // success
  } else {
     // failure :(
     if (status.getException() != null) {
        status.getException().printStackTrace();
     }
  } 

Q: How do I get the raw JDBC Connection from my connected connection profile?

A: The ProfileConnectionManager class provides a method for retrieving a particular type of connection class from a connected profile. A "java.sql.Connection" is one of the two different types (the other being the ConnectionInfo class) you can retrieve for connected database connection profiles.

  public java.sql.Connection getJavaConnectionForProfile (IConnectionProfile profile) {
     IManagedConnection managedConnection = ((IConnectionProfile)profile).
        getManagedConnection ("java.sql.Connection");
     if (managedConnection != null) {
        return (java.sql.Connection) managedConnection.getConnection().getRawConnection();
     }
     return null;
  }

Q: What can I do with the JDBC connection once I get it from DTP?

A: Simple answer is that you can do anything you can with any JDBC connection instance. For example, you could execute some DDL or a SQL statement:

  java.sql.Connection conn = getJavaConnectionForProfile(MyProfileInstance);
  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();
     }
  }

Q: How do I create a Transient connection profile vs. a Persisted profile?

A: Under the covers the only difference between a Transient and a Persisted connection profile is that when the workbench is closed, the Transient profile is not saved between sessions. So once you create it, you can do whatever you'd like with it - and it will be closed and not persisted when Eclipse is shut down.

To create the Transient profile, you have to know a bit more information than you do for a normal profile created via the UI. 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

For example, to create a Transient Derby connection profile...

  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
     
  }

Q: How do I get at the database model from my connected connection profile?

A: This is a little more difficult, but along the same lines of getting a JDBC connection. We just have a different connection type under the covers that lumps a reference to the Database model object with some other bits and pieces about the connection called "ConnectionInfo". Once you get the Database object you can use it like any other EMF model object - looking at its properties, child objects, and so on. Generally it's modeled the same way as JDBC is. From the Database object you can get a list of catalogs or schemas. From a schema you can get a list of tables, procedures, and so on.

  public org.eclipse.datatools.modelbase.sql.schema.Database (IConnectionProfile profile) {
     IManagedConnection managedConnection = ((IConnectionProfile)profile).getManagedConnection("org.eclipse.datatools.connectivity.sqm.core.connection.ConnectionInfo");
     if (managedConnection != null) {
        try {
           ConnectionInfo connectionInfo = (ConnectionInfo) managedConnection.getConnection().getRawConnection();
           if (connectionInfo != null) {
              Database database = connectionInfo.getSharedDatabase();
              return database;
           }
        } catch (Exception e) {
           e.printStackTrace();
        }
     }
     return null;
  }

SQL Development Tools

Q: How do I execute DDL via API with a connection profile instance so my results appear in the Results View?

A: <TBA>