Querying the Data

The RSSBus JDBC Driver for MSCRM follows the JDBC convention: first load the MSCRM driver class, then make a connection, and finally execute the query to retrieve the data from the ResultSet.

Load the Driver

Note: This step is optional per the JDBC 4.0 specification.

Class.forName("rssbus.jdbc.mscrm.MSCRMDriver");

Establish a Connection

To establish a connection you must prepare a connection string. The connection string must start with "jdbc:mscrm:", and may include any connection property. A typical connection string looks like "jdbc:mscrm:user=myuseraccount;password=mypassword;URL=http://myserver/myOrgRoot;". The connection string can be used with the DriverManager.getConnection() method to obtain a connection object.

Connection conn = DriverManager.getConnection("jdbc:mscrm:user=myuseraccount;password=mypassword;URL=http://myserver/myOrgRoot;");

Alternatively, you may prepare the connection options using the Properties object and use it with the DriverManager.

Properties prop = new Properties();
prop.put("User","XXX");
prop.put("Password","YYY");
prop.put("Location","ZZZ");
Connection conn = DriverManager.getConnection("jdbc:mscrm:",prop);

Create a Statement

Statement stat = conn.createStatement();

Execute the Query

The example below selects the Id and FullName columns of the Lead table.

boolean ret = stat.execute("SELECT Id, FullName FROM Lead");
ResultSet rs=stat.getResultSet();
while(rs.next()){
  for(int i=1;i<=rs.getMetaData().getColumnCount();i++)
  {
    System.out.println(rs.getMetaData().getColumnName(i) +"="+rs.getString(i));
  }
}

We can also use the Statement object to execute an INSERT, UPDATE or DELETE statement. The example below updates the Id and FullName columns of the Lead table.

stat.execute("UPDATE Lead SET Id = 'XXX' , FullName = 'YYY'");
int count=stat.getUpdateCount();
System.out.println(count+" rows are affected");

Supported SQL Syntax

The RSSBus JDBC Driver for MSCRM supports a restricted subset of the SQL Syntax. See SELECT Statements for details.