Crystal Reports: Use JDBC to connect to Oracle data source

1. Download the ojdbc7.jar from Oracle website and place it in the C:\Program Files (x86)\SAP BusinessObjects\SAP BusinessObjects Enterprise XI 4.0\java\lib directory, or the directory accordingly in your system.

2. Open the CRConfig.xml located in the C:\Program Files (x86)\SAP BusinessObjects\SAP BusinessObjects Enterprise XI 4.0\java directory.

3. Add the ojdbc7.jar to the Classpath.

4. Modify the JDBC section.
        <JDBCURL>jdbc:oracle:thin:@hostIP:1521:databaseName</JDBCURL>
<JDBCClassName>oracle.jdbc.OracleDriver</JDBCClassName>
<JDBCUserName>userID</JDBCUserName>

5. Save the changes and restart your Crystal Report designer.

6. Open your report. Click at the Database in the top menu, select Database Expert. Under Create New Connection, select JDBC(JNDI) to connect to your database.

---------------------------------------------------------------------------------------------------------------

                        
If you have ever asked yourself these questions, this is the book for you. What is the meaning of life? Why do people suffer? What is in control of my life? Why is life the way it is? How can I stop suffering and be happy? How can I have a successful life? How can I have a life I like to have? How can I be the person I like to be? How can I be wiser and smarter? How can I have good and harmonious relations with others? Why do people meditate to achieve enlightenment? What is the true meaning of spiritual practice? Why all beings are one? Read the book free here.

Crystal Reports: How to wrap column text?

Open the report in the designer.

1. Right click on the field and select Format Field.
2. In the popped up Format Editor window, select the Common tab. Then, check the Can Grow check box and enter the maximum number of lines allowed.

---------------------------------------------------------------------------------------------------------------

                        
If you have ever asked yourself these questions, this is the book for you. What is the meaning of life? Why do people suffer? What is in control of my life? Why is life the way it is? How can I stop suffering and be happy? How can I have a successful life? How can I have a life I like to have? How can I be the person I like to be? How can I be wiser and smarter? How can I have good and harmonious relations with others? Why do people meditate to achieve enlightenment? What is the true meaning of spiritual practice? Why all beings are one? Read the book free here.

Crystal Report: How to set alternate row colors in the detail section?

Open the report in the Crystal Report designer

1. Right click on the Details and select the Section Expert.
2. In the popped up Section Expert window, select the Color tab.

3. Click on the X-2 square button.
4. In the popped up Formula Workshop window, set the colors in the bottom right pane.
            if (RecordNumber mod 2 = 0) then
                  Color(213, 213, 150) //or crYellow
            else
                  crWhite

---------------------------------------------------------------------------------------------------------------

                        
If you have ever asked yourself these questions, this is the book for you. What is the meaning of life? Why do people suffer? What is in control of my life? Why is life the way it is? How can I stop suffering and be happy? How can I have a successful life? How can I have a life I like to have? How can I be the person I like to be? How can I be wiser and smarter? How can I have good and harmonious relations with others? Why do people meditate to achieve enlightenment? What is the true meaning of spiritual practice? Why all beings are one? Read the book free here.

How to rename a crystal report?

In the crystal report designer, click File in the top menu and select Summary Info. In the pop up window, change the Title field. This will change your report name to whatever you entered in the title field.

---------------------------------------------------------------------------------------------------------------

                        
If you have ever asked yourself these questions, this is the book for you. What is the meaning of life? Why do people suffer? What is in control of my life? Why is life the way it is? How can I stop suffering and be happy? How can I have a successful life? How can I have a life I like to have? How can I be the person I like to be? How can I be wiser and smarter? How can I have good and harmonious relations with others? Why do people meditate to achieve enlightenment? What is the true meaning of spiritual practice? Why all beings are one? Read the book free here.

crystalreport ERROR JRCCommunicationAdapter - detected an exception: Failed to bind columns

Since I am creating a new report similar to an existing report, I made a copy of the existing rpt file and renamed it to the new report. Even though I ran the Verify Database function and it works fine in the designer. When I ran the report with my java code, I got the error shown at the end of the blog.

This error was fixed by

1. Assigning an alias to all aggregate columns in the select clause of the query.

        max(columnA) as columnA, count(*) as totalNum

2. Making sure no parameters are included in the select clause of the query. If there is really no place to put a parameter, add it to the where clause like {?parameter1}={?parameter1}

2. adding the following line to my code after the data source and all the parameters are set.

      reportClientDocument.verifyDatabase();


The exception:
ERROR JRCCommunicationAdapter -  detected an exception: Failed to bind columns.
at com.crystaldecisions.reports.datafoundation.DFQuery.for(SourceFile:632)
at com.crystaldecisions.reports.datalayer.a.do(SourceFile:1621)
at com.crystaldecisions.reports.datalayer.a.a(SourceFile:1404)
at com.crystaldecisions.reports.dataengine.m.b(SourceFile:334)
at com.crystaldecisions.reports.dataengine.j.b(SourceFile:515)
at com.crystaldecisions.reports.dataengine.m.o(SourceFile:408)
at com.crystaldecisions.reports.dataengine.m.a(SourceFile:173)
at com.crystaldecisions.reports.dataengine.ContextNode.a(SourceFile:114)
at com.crystaldecisions.reports.dataengine.ContextNode.a(SourceFile:95)
at com.crystaldecisions.reports.dataengine.j.case(SourceFile:1080)
at com.crystaldecisions.reports.dataengine.h.<init>(SourceFile:108)
at com.crystaldecisions.reports.dataengine.DataContext.a(SourceFile:254)
at com.crystaldecisions.reports.dataengine.DataProcessor2.a(SourceFile:4660)
at com.crystaldecisions.reports.dataengine.DataProcessor2.a(SourceFile:4574)
at com.crystaldecisions.reports.dataengine.DataProcessor2.new(SourceFile:2652)
at com.crystaldecisions.reports.dataengine.DataProcessor2.byte(SourceFile:2610)
at com.crystaldecisions.reports.dataengine.DataProcessor2.try(SourceFile:2282)
at com.crystaldecisions.reports.dataengine.DataProcessor2.int(SourceFile:2442)
at com.crystaldecisions.reports.dataengine.DataProcessor2.I(SourceFile:1013)
at com.crystaldecisions.reports.formatter.formatter.objectformatter.ObjectFormatter.fo(SourceFile:526)
at com.crystaldecisions.reports.formatter.formatter.paginator.PageFormatter.if(SourceFile:613)
at com.crystaldecisions.reports.formatter.formatter.paginator.PageFormatter.a(SourceFile:578)
at com.crystaldecisions.reports.formatter.formatter.paginator.PageFormatter.X(SourceFile:387)
at com.crystaldecisions.reports.formatter.formatter.paginator.PageFormatter.moveToPageN(SourceFile:339)
at com.businessobjects.reports.sdk.requesthandler.ReportViewingRequestHandler.a(SourceFile:1090)
at com.businessobjects.reports.sdk.requesthandler.ReportViewingRequestHandler.byte(SourceFile:218)
at com.businessobjects.reports.sdk.JRCCommunicationAdapter.do(SourceFile:1909)
at com.businessobjects.reports.sdk.JRCCommunicationAdapter.if(SourceFile:661)
at com.businessobjects.reports.sdk.JRCCommunicationAdapter.a(SourceFile:167)
at com.businessobjects.reports.sdk.JRCCommunicationAdapter$2.a(SourceFile:529)
at com.businessobjects.reports.sdk.JRCCommunicationAdapter$2.call(SourceFile:527)
at com.crystaldecisions.reports.common.ThreadGuard.syncExecute(SourceFile:102)
at com.businessobjects.reports.sdk.JRCCommunicationAdapter.for(SourceFile:525)
at com.businessobjects.reports.sdk.JRCCommunicationAdapter.int(SourceFile:424)
at com.businessobjects.reports.sdk.JRCCommunicationAdapter.request(SourceFile:352)
at com.businessobjects.sdk.erom.jrc.a.a(SourceFile:54)
at com.businessobjects.sdk.erom.jrc.a.execute(SourceFile:67)
at com.crystaldecisions.proxy.remoteagent.RemoteAgent$a.execute(SourceFile:716)
at com.crystaldecisions.proxy.remoteagent.CommunicationChannel.a(SourceFile:125)
at com.crystaldecisions.proxy.remoteagent.RemoteAgent.a(SourceFile:537)
at com.crystaldecisions.sdk.occa.report.application.ds.a(SourceFile:186)
at com.crystaldecisions.sdk.occa.report.application.ReportSource.a(SourceFile:1558)
at com.crystaldecisions.sdk.occa.report.application.ReportSource.getPage(SourceFile:767)
at com.crystaldecisions.sdk.occa.report.application.AdvancedReportSource.getPage(SourceFile:324)
at com.crystaldecisions.reports.reportengineinterface.JPEReportSource.getPage(SourceFile:149)
at com.businessobjects.crystalreports.viewer.core.rs.RSRecordSource.openInputStream(Unknown Source)
at com.businessobjects.crystalreports.viewer.core.TSLVReader.a(Unknown Source)
at com.businessobjects.crystalreports.viewer.core.TSLVReader.run(Unknown Source)
at java.lang.Thread.run(Thread.java:745)

-----------------------------------------------------------------------------------------------------------------

Watch the blessing and loving online channel: SupremeMasterTV live

If you have ever asked yourself these questions, this is the book for you. What is the meaning of life? Why do people suffer? What is in control of my life? Why is life the way it is? How can I stop suffering and be happy? How can I have a successful life? How can I have a life I like to have? How can I be the person I like to be? How can I be wiser and smarter? How can I have good and harmonious relations with others? Why do people meditate to achieve enlightenment? What is the true meaning of spiritual practice? Why all beings are one? Read the book for free here.

How to tell if value in database is 0 or null when the value in ResultSet is 0?

For example, you have a count column in your sales table. When you query the table,

      String sql = "select itemId, count from sales where employeeId = 950780";
      PreparedStatement ps = conn.prepareStatement(sql);
      ResultSet rs = ps.executeQuery();
      while (rs.next()) {
            System.out.println("Item: " + rs.getString("itemId") + ", Count: "+rs.getInt("count"));
      }

When the count is null in the database, it returns 0 by rs.getInt("count"). How could you tell if the count is 0 or null? The ResultSet wasNull() method will give you the answer.

     int count = rs.getInt("count"); //After this immediately call the wasNull method.
     boolean isNull = rs.wasNull();

---------------------------------------------------------------------------------------------------------------

                        
If you have ever asked yourself these questions, this is the book for you. What is the meaning of life? Why do people suffer? What is in control of my life? Why is life the way it is? How can I stop suffering and be happy? How can I have a successful life? How can I have a life I like to have? How can I be the person I like to be? How can I be wiser and smarter? How can I have good and harmonious relations with others? Why do people meditate to achieve enlightenment? What is the true meaning of spiritual practice? Why all beings are one? Read the book free here.

[solved] Various ways to calculate days between two dates

There are several ways to calculate the days between two dates.

public DateTest {

      //METHOD 1
      private int getDaysBetweetDates(Calendar day1, Calendar day2) {
            int days;
            Calendar dayOne = (Calendar) day1.clone(),
                   dayTwo = (Calendar) day2.clone();

           if (dayOne.get(Calendar.YEAR) == dayTwo.get(Calendar.YEAR)) {
               days = (dayTwo.get(Calendar.DAY_OF_YEAR) - dayOne.get(Calendar.DAY_OF_YEAR));
           } else if (dayTwo.get(Calendar.YEAR) > dayOne.get(Calendar.YEAR)) {
                  int extraDays = 0;

                  int dayTwoOriginalYearDays = dayTwo.get(Calendar.DAY_OF_YEAR);

                  while (dayTwo.get(Calendar.YEAR) > dayOne.get(Calendar.YEAR)) {
                         dayTwo.add(Calendar.YEAR, -1);
                        // getActualMaximum() important for leap years
                        extraDays += dayTwo.getActualMaximum(Calendar.DAY_OF_YEAR);
                  }
                  days = extraDays + dayTwoOriginalYearDays - dayOne.get(Calendar.DAY_OF_YEAR);
           } else {
                  days = 0;
           }
           return days;
      }

      //METHOD 2
      private int getDaysBetweenDates2(Calendar day1, Calendar day2){
             Calendar dayOne = (Calendar) day1.clone(),
                    dayTwo = (Calendar) day2.clone();
   
             long diff = dayTwo.getTimeInMillis() - dayOne.getTimeInMillis();
             return (diff /(24*60*60*1000));
      }

      //METHOD 3
       private float getDaysBetweenDates3(Calendar day1, Calendar day2){
           Calendar dayOne = (Calendar) day1.clone(),
                   dayTwo = (Calendar) day2.clone();
           long diff = dayTwo.getTimeInMillis() - dayOne.getTimeInMillis();
           return TimeUnit.DAYS.convert(diff, TimeUnit.MILLISECONDS);
      }

      public static void main(String[] args){
           DateTest test = new DateTest();

           long t1 = 1371773105000L;
           long t2 = 1372203030000L;

           Calendar d1 = Calendar.getInstance();
           d1.setTimeInMillis(t1);
        System.out.println("Date1: " + d1.get(Calendar.YEAR) + "/" + (d1.get(Calendar.MONTH)+1) + "/" + d1.get(Calendar.DAY_OF_MONTH));

           Calendar d2 = Calendar.getInstance();
           d2.setTimeInMillis(t2);
        System.out.println("Date2: " + d2.get(Calendar.YEAR) + "/" + (d2.get(Calendar.MONTH)+1) + "/" + d2.get(Calendar.DAY_OF_MONTH));

        System.out.println("Method1: "+ test.getDaysBetweenDates1(d1,d2));
        System.out.println("Method2: "+ test.getDaysBetweenDates2(d1, d2));
        System.out.println("Method3: "+ test.getDaysBetweenDates3(d1, d2));
}

The output:
Date1: 2013/6/20
Date2: 2013/6/25
Method1: 5
Method2: 4
Method3: 4


However, if you add the following lines to the beginning of Method 2 and Method 3. For example, the Method 2 will look like this.

      private int getDaysBetweenDates2(Calendar day1, Calendar day2){
            day1.set(Calendar.HOUR_OF_DAY, 1);
            day2.set(Calendar.HOUR_OF_DAY, 1);
            day1.set(Calendar.MINUTE, 1);
            day2.set(Calendar.MINUTE, 1);

             Calendar dayOne = (Calendar) day1.clone(),
                    dayTwo = (Calendar) day2.clone();
   
             long diff = dayTwo.getTimeInMillis() - dayOne.getTimeInMillis();
             return (diff /(24*60*60*1000));
      }

The output will become:
Date1: 2013/6/20
Date2: 2013/6/25
Method1: 5
Method2: 5
Method3: 5

---------------------------------------------------------------------------------------------------------------

                        
If you have ever asked yourself these questions, this is the book for you. What is the meaning of life? Why do people suffer? What is in control of my life? Why is life the way it is? How can I stop suffering and be happy? How can I have a successful life? How can I have a life I like to have? How can I be the person I like to be? How can I be wiser and smarter? How can I have good and harmonious relations with others? Why do people meditate to achieve enlightenment? What is the true meaning of spiritual practice? Why all beings are one? Read the book free here.