KB46848: How to Create a Database Connection to the MicroStrategy Statistics Table Hosted on Microsoft SQL Server 2012 using the MicroStrategy Web SDK

Developers may want to connect to the Strategy Statistics table using the Strategy Web SDK.  The following example assumes that the Strategy Statistics table is hosted on Microsoft's SQL Server 2012.
 
CONFIGURING PROJECT IN ECLIPSE

1. Read the following documentation to determine which jar files are needed for a database connection created in java - http://technet.microsoft.com/en-us/library/ms378422.aspx
2. The documentation states that sqljdbc.jar or sqljdbc4.jar can be used for Microsoft SQL Server 2012.  The download page for both of these jar files can be found here - http://www.microsoft.com/en-us/download/details.aspx?displaylang=en&id=11774
3. Once installed, configure the Strategy project in Eclipse
   
1. Right click on Strategy project Package Explorer tab in Eclipse
2. Go to Build Path > Configure Build Path
3. On the Java Build Path section, click on the Libraries tab
4. Click the Add External JARs
5. Navigate to the location sqljdbc4.jar is installed.  Add sqljdbc4.jar.  If this throws an error, you may need to use the other jar file instead.  Only add one, not both.
6. Click Ok

 
SAMPLE CODE TO RETRIEVE INFORMATION FROM STATS TABLE
 The following is sample code to connect to the MicroStratgy Statistics table.

import java.sql.DriverManager ;
import java.sql.Statement ;
import java.sql.ResultSet ;
import java.sql.SQLException ;
import java.sql.Connection ;
     public class ReadFromDB {
         Connection connection = null;
         Statement statement = null;
         ResultSet resultSet = null;

         String driver = "com.microsoft.sqlserver.jdbc.SQLServerDriver";
         String urlDriver = "jdbc:sqlserver";
         String SQLserverName = "10.27.10.44";
         String port = "1433";
         String databaseName = "RLA4APR10U1";
         String url = urlDriver + "://" + SQLserverName + ":" + port + ";databaseName=" + databaseName;
         String dbUser = "RLA4APR10U1";
         String dbPass = "RLA4APR10U1";

        public ReadFromDB() {
          // Load driver
             try {
                 Class.forName(driver);
             } catch (ClassNotFoundException ex) {
                 ex.printStackTrace();
             }
         }

        public void doWork() {
             String query = "";
             try {
              // Create connection object
              connection = DriverManager.getConnection(url, dbUser, dbPass);
              // Create Statement object
              // In this example, retrieving all tables from IS_REPORT_STATS
              statement = connection.createStatement();
              query = "SELECT * FROM dbo.IS_REPORT_STATS";

              // Execute the query
              resultSet = statement.executeQuery(query);
 
              // Loops through ALL rows and retrieves Report ID and JOB ID information
                while (resultSet.next()) {
                 System.out.print("Report ID: " + resultSet.getObject("REPORTID").toString());

                System.out.print(" correlates with JOB ID: " + resultSet.getObject("JOBID").toString());
                System.out.print("\n"); //new line
                 }
             } catch (SQLException ex) {
                 ex.printStackTrace();
                 System.out.println(query);
             } 
             finally {
                 try {
                     if (resultSet != null) {
                         resultSet.close();
                     }
                     if (statement != null) {
                         statement.close();
                     }
                     if (connection != null) {
                         connection.close();
                     }
                 } catch (SQLException ex) {
                     ex.printStackTrace();
                 }
             }
         }
 
        public static void main(String[] args) {
             ReadFromDB dbTest = new ReadFromDB();
             dbTest.doWork();
         }
     }

 
 
The example provided in this document is provided “as-is” to the user and assumes that the user:

• Can program, compile (e.g. javac, jikes, etc.), and execute Java programs
• Can configure environment variables (e.g. JAR files, property files, etc.)
• Have all the necessary tools to create Java applications (JDK, IDE, etc.)
• Has access to the Strategy SDK documentation.
• Has read the customization warning at the bottom of this document

PREREQUISITES:
 
For the sample to work, the Strategy Web JAR files must be accessible by the Java Runtime
Environment. The Strategy Web JAR files can be found under:

• Strategy Web Universal (JSP): {web_root}/WEB-INF/lib directory.
• Strategy Web (.NET): Program Files\Common Files\Strategy directory.

Note:
 
More elaborate programs will require additional library files and are out of the scope of this document.
 
 
 
 
CUSTOMIZATION WARNING:
 
This customization is provided as a convenience to Strategy users and is only directly applicable to the version stated. While this code may apply to other releases directly, Strategy Technical Support makes no guarantees that the code provided will apply to any future or previous builds. In the event of a code change in future builds, Strategy Technical Support makes no guarantee that an updated version of this particular customization will be provided. In the event of a code change in future builds, Strategy may not be able to provide additional code on this matter even though this customization is provided at this time for this specific build. For enhancements to this customization or to incorporate similar functionality into other versions, contact your Account Executive to inquire about Strategy Consulting assistance.