Aggregated Intelligence: Oracle

Friday, April 01, 2011

Monday, January 12, 2009

Crystal Reports for Visual Studio: 2 useful C# functions

When I first started working with Crystal Reports, what I found troubling was that when you connect to a database and select a table to use in your report (or for that matter any stored procedure), you provide Crystal Reports with the connection information which is then stored in some hidden place within the RPT files. The reason that this was troubling to me was that there did not seem to be any way to point to have different databases for development and production.

Sure, I tried the "SetDatabaseLogon" that is available on ReportDocument within the CrystalReportSource object - but that never seemed to work. After a lot of frustration, what I ended up doing was creating data-sets and then creating reports using the data-sets. Then during run-time, I had a method that would return the data-set from the appropriate database (dev or production), which was then set into the report using "SetDataSource" that is also available on on the ReportDocument object in the CrystalReportSource object.

By setting the DataSource for the CrystalReport, I was able to get a solution where depending on what mode I was running the application - I was able to get the data from the development, test or production databases.

This solution worked fine and dandy for a while, until I came across a complex report that had a sub-report embedded inside it. The sub-report had to be run once for every record in the main report. This is something that cannot be done using the DataSource method, because, even though I could give CR the main report's data-source, I would not be able to provide a data-source for each sub-report that needed to be run.

This time while looking for a solution, I found out 2 things:

1. The reason SetDatabaseLogon does not work is that it only allows you to change the user-id and password. The four parameter version will not change the database and server names: It will change the user-id and password for all those objects that share the database and server name passed in as part of the arguments. (This information is unfortunately buried in the remarks part of the documentation).
2. To change the database that is being used one needs to use the "ApplyLogOnInfo" on each table (CrystalDecisions.CrystalReports.Engine.Table) that is in the CrystalReport object.

So here is my first useful C# function for working with CrystalReports. It is used to change the database used by CrystalReports to get its data. (use it when you need to use separate development, test and production servers as your data-source for CrystalReports)

public static void SetCRLogOnInfo(ReportDocument mainInRD, string dataSource, string userId, string pwd)
    {
        //do the main reports database
        TableLogOnInfo logonInfo = null;
        foreach (CrystalDecisions.CrystalReports.Engine.Table table in mainInRD.Database.Tables)
        {
            logonInfo = table.LogOnInfo;
            logonInfo.ConnectionInfo.ServerName = dataSource;
            logonInfo.ConnectionInfo.DatabaseName = "";
            logonInfo.ConnectionInfo.UserID = userId;
            logonInfo.ConnectionInfo.Password = pwd;
            table.ApplyLogOnInfo(logonInfo);
        }
        try
        {
            //now update logon info for all sub-reports
            if (!mainInRD.IsSubreport && mainInRD.Subreports != null && mainInRD.Subreports.Count > 0)
            {
                foreach (ReportDocument rd in mainInRD.Subreports)
                {
                    SetCRLogOnInfo(rd, dataSource, userId, pwd);
                }
            }
        }
        catch
        {
        }
    }

The second method is used to export the CrystalReport as a PDF file. The reason it takes a HTTPResponse, is that this snippet is used in a ASP.Net application and it outputs the PDF to the browser.

public static void ExportToPDF(CrystalReportSource crs, HttpResponse response)
{
    try
    {
        response.Buffer = false;
        response.ClearContent();
        response.ClearHeaders();
        response.ContentType = "application/pdf";
        
        Stream pdfStreamFromCR = crs.ReportDocument.ExportToStream(ExportFormatType.PortableDocFormat);
        byte[] outputBuffer = new byte[(int)pdfStreamFromCR.Length];
        pdfStreamFromCR.Read(outputBuffer, 0, (int)pdfStreamFromCR.Length);
        response.BinaryWrite(outputBuffer);
        response.Flush();
        response.End();
    }
    catch(ThreadAbortException)
    {
        //expected do nothing
    }
    catch (Exception ex)
    {
        //Log4Net
    }
}

(updated on 1/12/2008)

Oracle: Calling PL/SQL procedures that return a cursor

How does one go about calling a PL/SQL procedure that returns a cursor as an OUT parameter from a SQL script?
For example how do you call the following procedure?

PROCEDURE GETCURSORS (
    DEPARTMENTID IN NUMBER, 
    EMPLOYEESCUR OUT SYS_REFCURSOR)

Well, you use a bind variable. Here is how to do it:

variable retCur refcursor; --declare the bind variable
exec GetCursor(119,:retCur); --call the procedure and have the bind variable filled out
print :retCur; -- print the results to output - this is handy for debugging

This is truly handy for debugging. And here is how one can use bind variables to increase application performance.

Bind variables - The key to application performance.

Monday, January 05, 2009

Oracle - How To: Get the source for your package

Here are a couple of scripts that will let you get your hands on the source for your package as stored by the database.

Select  TEXT 
from SYS.USER_SOURCE 
where NAME='Package Name' 
and TYPE='PACKAGE' 
order by LINE;

Select  TEXT 
from SYS.USER_SOURCE 
where NAME='Package Name'
and TYPE='PACKAGE BODY'
order by LINE;

What was surprising to me, was that the code is stored as a separate record for each line in the package.