Multi Database in Tomcat 5.5.20

I can access Mysql, but I can't access MSSQL

Page 1 of 1

3 Replies - 2177 Views - Last Post: 23 September 2010 - 03:14 AM

#1 pjamrisk  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 38
  • Joined: 02-March 09

Multi Database in Tomcat 5.5.20

Posted 12 July 2010 - 08:58 PM

Hello Gurus,
I been working on an applet for Jasper Reports to generate dynamic reports via a Java servlet in Tomcat.
I been using Google all over the web including the jasper forge website due to a problem I have with the ODBC driver jar for MSSQL.
I have copied the jar where many websites suggest to have it at (here is an example, not necessarily on straight tomcat but with a similar intent: http://www.chemaxon....n/tomcat.html). This particular version of tomcat is also running JVM JDK 1.5 as jasper for some reason will not start if I use version 1.6.

I am using a Ubuntu Linux 9.04 machine for production environment for my application as a reference.
Anyways, the problem happens as follows. Jasper makes use of its own type of XML called JRXML to compile via their own generated Java classes. SO I generated the servlet that would take care of this, and modified the JRXML file to comply with a subdataset connection to another database.

When I execute the report locally on my machine, via iReport, it works like a champ. However, once I upload the compiled war compilation to Tomcat I keep getting the error "No suitable Driver found".
I have copied the darn jar driver all over the place in the server, and restarted it countless times now, and I still get the same exact error (the jar file is the newest version from Microsoft, and it is working locally on my machine, so I know is not corrupted).

So my question is, can someone help me find a solution for this, or point me on the right path to try to solve this problem? This is the first time I am trying to access multiple Databases at the same time via Tomcat, and so far it is not doing it.

Any help will be greatly appreciated on this.

Thanks,
Patrick Jamriska

PD: On my servlet I created a try/catch block to record any errors the program might throw. Here is what I get from the driver:

Jasper encountered a problem when attemptingto populate the report. 

Error Message ==> Error evaluating expression : Source text : java.sql.DriverManager.getConnection("jdbc:sqlserver://VMCITSMSQL:1433;DatabaseName=AVQUERY", "AppQuery", "BiteMe") 
Cause of Error ==> java.sql.SQLException: No suitable driver


This post has been edited by pjamrisk: 12 July 2010 - 09:53 PM


Is This A Good Question/Topic? 0
  • +

Replies To: Multi Database in Tomcat 5.5.20

#2 pjamrisk  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 38
  • Joined: 02-March 09

Re: Multi Database in Tomcat 5.5.20

Posted 13 July 2010 - 02:32 AM

Ok, I been working on this for a week already, and is getting me nowhere. I am posting here my small project code to see if someone has a different set of eyes, or a suggestion on how to have 2 database connections made through the servlet I have here since nothing seems to work with this app.
I tried to create a JNDI connection to mssql and that did not work (I don't know why). The Jasper server installation is from Jasperforge, so there is no funky configurations on tomcat and/or jasper server in that sense.
All comments and suggestions are gladly welcome, I just don't want to see this thing anymore right now because I feel that reading is just not helping at this point anymore..

Patrick.
PD: just a quick note, if the previous post uname and oass are different is because I changed for posting this that's all, so don't look at that as a problem please.

the test.java file
//List of Includes
import java.io.IOException;
import java.io.PrintWriter;
//Servlet imports
import javax.servlet.ServletOutputStream;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
// Hash map and Database Imports.
import java.util.HashMap; // we will use this to create the maps for variables.
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

//Jasper Reports Import classes. This is based currently on Jasper server 3.5.0; Updated to 3.7.2!!
import net.sf.jasperreports.engine.JRException; // This will be used to catch any exceptions during compilation
import net.sf.jasperreports.engine.JasperCompileManager; // this is to compile the report in a jasper file
import net.sf.jasperreports.engine.JasperExportManager;
import net.sf.jasperreports.engine.JasperFillManager;
import net.sf.jasperreports.engine.JasperPrint;

public class Test extends HttpServlet {

    /**
     * Processes requests for both HTTP <code>GET</code> and <code>POST</code> methods.
     * @param request servlet request
     * @param response servlet response
     * @throws ServletException if a servlet-specific error occurs
     * @throws IOException if an I/O error occurs
     */
    public void processRequest(HttpServletRequest request, HttpServletResponse response)
    throws ServletException, IOException {
        /**
         * Declare the printwriter (which we'll use if an error occurs), but
         * don't instantiate it yet because instantiating it will prevent us
         * from streaming the PDF back to the client if everything else works.
         * @connection This variable is the connection for Mysql
         */
        PrintWriter out = null;
        Connection connection = null;
        ServletOutputStream outResponse = null;
        try{
            //Prepare the Database connection, we first connect to mysql, and we let the jrxml connect to 
            //mssql
            Class.forName("com.mysql.jdbc.Driver").newInstance();
            connection = DriverManager.getConnection("jdbc:mysql://CEDEV/dbapp?user=admin&password=password");
            // Generate the Hasmap of variables to be passed to the report.
            HashMap map = new HashMap(); // new HashMap to add to the elements.
            map.put("control_number", request.getParameter("control_number"));
            //map.put("Quarter", request.getParameter("quarter"));
            ///////Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver").newInstance();//does not   
            //work if called again. gives null pointer exception.
            // Start The report Compilation
            //Must figure out why the relative path does not work.
            //Compile the report First
            JasperCompileManager.compileReportToFile(
                    "/usr/local/jasperserver/apache-tomcat/webapps/ClinicalEngineeringReports/WEB-INF/classes/Test/main.jrxml",
                    "/usr/local/jasperserver/apache-tomcat/webapps/ClinicalEngineeringReports/WEB-INF/classes/Test/main.jasper");
            //Fill the report with the data
            JasperPrint jasperPrint = JasperFillManager.fillReport("/usr/local/jasperserver/apache-tomcat/webapps/ClinicalEngineeringReports/WEB-INF/classes/Test/main.jasper",
                    map, connection);
            // export report to pdf and stream back to browser
            //Export the report to PDF File.
            byte[] pdfAsBytes = JasperExportManager.exportReportToPdf(jasperPrint); // this will generate the stream
            outResponse = response.getOutputStream();
            // Header disposition: This can be changed ot other formats if we have to do the distribution.
            response.setContentType("application/pdf");
            response.setContentLength(pdfAsBytes.length); // to avoid bottlenecks
            //This basically created the given output filename. We can modify this
            //to match specific parts of a report, such as dates.
            response.setHeader("Content-disposition","attachment; filename=test.pdf");
            outResponse.write(pdfAsBytes); // send the report back to the user.
            //close the buffer at the end
            outResponse.flush();
            outResponse.close();
            }
            // Exception Control
            catch (JRException jre){
             // Get the writer from the response so we can output markup
             out = response.getWriter();

             // Jasper had an internal error when filling the report. Give the
             // user the lowdown.
             out.println("<html>");
             out.println("\t<body>");
             out.println("\t\t<br /><br />");
             out.println("\t\tJasper encountered a problem when attempting"
                     + "to populate the report.");
             out.println("\t\t<br /><br />");
             out.println("\t\tError Message ==> " + jre.getLocalizedMessage());
             out.println("\t\t<br />");
             out.println("\t\tCause of Error ==> " + jre.getCause());
             out.println("\t</body>");
             out.println("</html>");
            }

            catch (ClassNotFoundException e){
            e.printStackTrace();
            }

            catch (SQLException e){
                // Get the writer from the response so we can output markup
                 out = response.getWriter();

                 // Start the html, but don't fill in the juicy bits yet
                 out.println("<html>");
                 out.println("\t<body>");

                 // SQLExceptions can be chained. We have at least one exception, so
                 // set up a loop to make sure we let the user know about all of them
                 // if there happens to be more than one.
                 SQLException tempException = e;
                 while (null != tempException) {
                     // work
                     // Write out the useful info on this exception to the response
                     out.println("\t\t<br /><br />");
                     out.println("\t\tThe following database error occurred:");
                     out.println("\t\t<br /><br />");
                     out.println("\t\tError Message ==> "
                             + e.getLocalizedMessage());
                     out.println("\t\t<br />");
                     out.println("\t\tCause of Error ==> " + e.getCause());
                     out.println("\t\t<br />");
                     out.println("\t\tSQL State ==> " + e.getSQLState());
                     out.println("\t\t<br />");
                     out.println("\t\tVendor Error Code ==> " + e.getErrorCode());

                     // loop to the next exception
                     tempException = tempException.getNextException();
                 }
                 // Write out the closing tags to the response
                 out.println("\t</body>");
                 out.println("</html>");
            }

             catch (IOException ioe) {
                 // Get the writer from the response so we can output markup
                 out = response.getWriter();

                 // This was an IO error, so it quite possibly resulted from an error
                 // creating the print writer, but we're still gonna give it the old
                 // college try and attempt to send back useful info to the user
                 out.println("<html>");
                 out.println("\t<body>");
                 out.println("\t\t<br /><br />");
                 out.println("\t\tDue to a naming problem with this servlet's"
                         + "initial context, the system is unable to display"
                         + "the report at this time.");
                 out.println("\t\t<br /><br />");
                 out.println("\t\tError Message ==> " + ioe.getLocalizedMessage());
                 out.println("\t\t<br />");
                 out.println("\t\tCause of Error ==> " + ioe.getCause());
                 out.println("\t</body>");
                 out.println("</html>");
             }

             catch (Exception e) {
                 // The above code leads to Internal Server error 500. This is mapped
                 // to 500error.html in web.xml. When this Servlet is requested,
                 // customized page is displayed.
                 throw new ServletException("Servlet caught a ServletException: "
                                           + e.getMessage());
              }
        //Prepare to close the database connection. IMportant to reduce overhead.
        finally {
            try {
                // We're done here, so clean up the connection
                connection.close();
            }
            catch (SQLException sqle) {
                     // We don't actually care, we were just trying to clean up an
                     // expensive DB connection
            }
        }
    }

    // <editor-fold defaultstate="collapsed" desc="HttpServlet methods. Click on the + sign on the left to edit the code.">
    /**
     * Handles the HTTP <code>GET</code> method.
     * @param request servlet request
     * @param response servlet response
     * @throws ServletException if a servlet-specific error occurs
     * @throws IOException if an I/O error occurs
     */
    protected void doGet(HttpServletRequest request, HttpServletResponse response)
    throws ServletException, IOException {
        processRequest(request, response);
    }

    /**
     * Handles the HTTP <code>POST</code> method.
     * @param request servlet request
     * @param response servlet response
     * @throws ServletException if a servlet-specific error occurs
     * @throws IOException if an I/O error occurs
     */
    protected void doPost(HttpServletRequest request, HttpServletResponse response)
    throws ServletException, IOException {
        processRequest(request, response);
    }

    /**
     * Returns a short description of the servlet.
     * @return a String containing servlet description
     */
    public String getServletInfo() {
        return "Short description";
    }// </editor-fold>

}



This is my main.jrxml File. I highlighted the connection part of the subreport where I get the error.
<?xml version="1.0" encoding="UTF-8"?>
<jasperReport xmlns="http://jasperreports.sourceforge.net/jasperreports" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://jasperreports.sourceforge.net/jasperreports http://jasperreports.sourceforge.net/xsd/jasperreport.xsd" name="main" language="groovy" pageWidth="842" pageHeight="595" orientation="Landscape" columnWidth="802" leftMargin="20" rightMargin="20" topMargin="20" bottomMargin="20">
	<property name="ireport.zoom" value="1.0"/>
	<property name="ireport.x" value="0"/>
	<property name="ireport.y" value="0"/>
	<style name="Title" isDefault="false" forecolor="#FFFFFF" fontName="DejaVu Sans" fontSize="50" isBold="false" pdfFontName="Helvetica"/>
	<style name="SubTitle" isDefault="false" forecolor="#666666" fontName="DejaVu Sans" fontSize="18" isBold="false" pdfFontName="Helvetica"/>
	<style name="Column header" isDefault="false" forecolor="#666666" fontName="DejaVu Sans" fontSize="14" isBold="true" pdfFontName="Helvetica"/>
	<style name="Detail" isDefault="false" mode="Transparent" fontName="DejaVu Sans" pdfFontName="Helvetica"/>
	<style name="Row" isDefault="false" mode="Transparent" fontName="DejaVu Sans" pdfFontName="Helvetica">
		<conditionalStyle>
			<conditionExpression><![CDATA[$V{REPORT_COUNT}%2 == 0]]></conditionExpression>
			<style isDefault="false" style="Row" mode="Opaque" backcolor="#F0EFEF"/>
		</conditionalStyle>
	</style>
	<subDataset name="PieChartDataset"/>
	<parameter name="control_number" class="java.lang.String"/>
	<parameter name="SUBREPORT_DIR" class="java.lang.String" isForPrompting="false">
		<defaultValueExpression><![CDATA["./"]]></defaultValueExpression>
	</parameter>
	<queryString>
		<![CDATA[SELECT
  Inventory.ControlNumber,
  Inventory.Insp_PMPerformed,
  Inventory.Insp_ItemName,
  Inventory.Insp_SuppName,
  Inventory.Insp_ModelName,
  Inventory.Insp_SerialNumber,
  Inventory.Insp_DeptName,
  CONCAT(Inventory.Insp_Building, ' - ', Inventory.Insp_Floor, ' - ', Inventory.EquipStatus) AS location,
  EquipmentStatus.`Status`,
  Inventory.Insp_Room,
  PMProcedure.Description,
  CONCAT(Inventory.Insp_PMPerformed, ' - ',PMProcedure.Description) AS pmperformed
FROM
  EquipmentStatus
  LEFT OUTER JOIN Inventory ON (EquipmentStatus.EqipStatusID = Inventory.EquipStatus)
  INNER JOIN PMProcedure ON (Inventory.Insp_PMPerformed = PMProcedure.PMNumber)
WHERE
  Inventory.ControlNumber LIKE $P{control_number}]]>
	</queryString>
	<field name="ControlNumber" class="java.lang.String">
		<fieldDescription><![CDATA[]]></fieldDescription>
	</field>
	<field name="Insp_PMPerformed" class="java.lang.Integer">
		<fieldDescription><![CDATA[]]></fieldDescription>
	</field>
	<field name="Insp_ItemName" class="java.lang.String">
		<fieldDescription><![CDATA[]]></fieldDescription>
	</field>
	<field name="Insp_SuppName" class="java.lang.String">
		<fieldDescription><![CDATA[]]></fieldDescription>
	</field>
	<field name="Insp_ModelName" class="java.lang.String">
		<fieldDescription><![CDATA[]]></fieldDescription>
	</field>
	<field name="Insp_SerialNumber" class="java.lang.String">
		<fieldDescription><![CDATA[]]></fieldDescription>
	</field>
	<field name="Insp_DeptName" class="java.lang.String">
		<fieldDescription><![CDATA[]]></fieldDescription>
	</field>
	<field name="location" class="java.lang.String">
		<fieldDescription><![CDATA[]]></fieldDescription>
	</field>
	<field name="Status" class="java.lang.String">
		<fieldDescription><![CDATA[]]></fieldDescription>
	</field>
	<field name="Insp_Room" class="java.lang.String">
		<fieldDescription><![CDATA[]]></fieldDescription>
	</field>
	<field name="Description" class="java.lang.String">
		<fieldDescription><![CDATA[]]></fieldDescription>
	</field>
	<field name="pmperformed" class="java.lang.String">
		<fieldDescription><![CDATA[]]></fieldDescription>
	</field>
	<background>
		<band height="555" splitType="Stretch"/>
	</background>
	<title>
		<band height="94" splitType="Stretch">
			<staticText>
				<reportElement style="SubTitle" x="336" y="65" width="449" height="29"/>
				<textElement textAlignment="Right">
					<font size="22" isBold="false"/>
				</textElement>
				<text><![CDATA[Asset Information]]></text>
			</staticText>
			<frame>
				<reportElement mode="Opaque" x="-20" y="0" width="822" height="65" forecolor="#006699" backcolor="#006699"/>
				<staticText>
					<reportElement style="Title" x="154" y="0" width="354" height="65"/>
					<textElement textAlignment="Right">
						<font fontName="DejaVu Sans" size="36" isBold="false"/>
					</textElement>
					<text><![CDATA[Work Order History for ]]></text>
				</staticText>
				<textField>
					<reportElement x="507" y="0" width="307" height="65"/>
					<textElement>
						<font fontName="DejaVu Sans" size="36" pdfFontName="Helvetica"/>
					</textElement>
					<textFieldExpression class="java.lang.String"><![CDATA[$P{control_number}]]></textFieldExpression>
				</textField>
			</frame>
		</band>
	</title>
	<pageHeader>
		<band height="39" splitType="Stretch"/>
	</pageHeader>
	<columnHeader>
		<band height="11" splitType="Stretch">
			<textField isStretchWithOverflow="true" isBlankWhenNull="true">
				<reportElement style="Detail" positionType="Float" x="0" y="-20" width="143" height="14"/>
				<textElement>
					<font fontName="DejaVu Sans" size="8" isBold="false" pdfFontName="Helvetica"/>
				</textElement>
				<textFieldExpression class="java.lang.String"><![CDATA[$F{location}]]></textFieldExpression>
			</textField>
			<textField isStretchWithOverflow="true" isBlankWhenNull="true">
				<reportElement style="Detail" positionType="Float" x="147" y="-20" width="110" height="14"/>
				<textElement>
					<font fontName="DejaVu Sans" size="8" isBold="false" pdfFontName="Helvetica"/>
				</textElement>
				<textFieldExpression class="java.lang.String"><![CDATA[$F{Insp_ModelName}]]></textFieldExpression>
			</textField>
			<textField isStretchWithOverflow="true" isBlankWhenNull="true">
				<reportElement style="Detail" positionType="Float" x="262" y="-20" width="110" height="14"/>
				<textElement>
					<font fontName="DejaVu Sans" size="8" isBold="false" pdfFontName="Helvetica"/>
				</textElement>
				<textFieldExpression class="java.lang.String"><![CDATA[$F{Insp_ItemName}]]></textFieldExpression>
			</textField>
			<textField isStretchWithOverflow="true" isBlankWhenNull="true">
				<reportElement style="Detail" positionType="Float" x="391" y="-21" width="43" height="14"/>
				<textElement>
					<font fontName="DejaVu Sans" size="8" isBold="false" pdfFontName="Helvetica"/>
				</textElement>
				<textFieldExpression class="java.lang.String"><![CDATA[$F{Insp_SerialNumber}]]></textFieldExpression>
			</textField>
			<textField isStretchWithOverflow="true" isBlankWhenNull="true">
				<reportElement style="Detail" positionType="Float" x="437" y="-20" width="92" height="14"/>
				<textElement>
					<font fontName="DejaVu Sans" size="8" isBold="false" pdfFontName="Helvetica"/>
				</textElement>
				<textFieldExpression class="java.lang.String"><![CDATA[$F{Insp_SuppName}]]></textFieldExpression>
			</textField>
			<textField isStretchWithOverflow="true" isBlankWhenNull="true">
				<reportElement style="Detail" positionType="Float" x="531" y="-20" width="78" height="14"/>
				<textElement>
					<font fontName="DejaVu Sans" size="8" isBold="false" pdfFontName="Helvetica"/>
				</textElement>
				<textFieldExpression class="java.lang.String"><![CDATA[$F{Insp_DeptName}]]></textFieldExpression>
			</textField>
			<textField isStretchWithOverflow="true" isBlankWhenNull="true">
				<reportElement style="Detail" positionType="Float" x="609" y="-20" width="115" height="14"/>
				<textElement>
					<font fontName="DejaVu Sans" size="8" isBold="false" pdfFontName="Helvetica"/>
				</textElement>
				<textFieldExpression class="java.lang.String"><![CDATA[$F{pmperformed}]]></textFieldExpression>
			</textField>
			<textField isStretchWithOverflow="true" isBlankWhenNull="true">
				<reportElement style="Detail" positionType="Float" x="724" y="-20" width="63" height="14"/>
				<textElement>
					<font fontName="DejaVu Sans" size="8" isBold="false" pdfFontName="Helvetica"/>
				</textElement>
				<textFieldExpression class="java.lang.String"><![CDATA[$F{Status}]]></textFieldExpression>
			</textField>
			<frame>
				<reportElement x="227" y="-33" width="575" height="14"/>
				<staticText>
					<reportElement style="Column header" positionType="Float" x="-220" y="0" width="143" height="14" forecolor="#000000"/>
					<textElement>
						<font fontName="DejaVu Sans" size="12" isBold="true" pdfFontName="Helvetica"/>
					</textElement>
					<text><![CDATA[Last Known Location]]></text>
				</staticText>
				<staticText>
					<reportElement style="Column header" positionType="Float" x="-73" y="-2" width="110" height="14" forecolor="#000000"/>
					<textElement>
						<font fontName="DejaVu Sans" size="12" isBold="true" pdfFontName="Helvetica"/>
					</textElement>
					<text><![CDATA[Model]]></text>
				</staticText>
				<staticText>
					<reportElement style="Column header" positionType="Float" x="42" y="-2" width="110" height="14" forecolor="#000000"/>
					<textElement>
						<font fontName="DejaVu Sans" size="12" isBold="true" pdfFontName="Helvetica"/>
					</textElement>
					<text><![CDATA[Item]]></text>
				</staticText>
				<staticText>
					<reportElement style="Column header" positionType="Float" x="171" y="-2" width="43" height="14" forecolor="#000000"/>
					<textElement>
						<font fontName="DejaVu Sans" size="12" isBold="true" pdfFontName="Helvetica"/>
					</textElement>
					<text><![CDATA[SN]]></text>
				</staticText>
				<staticText>
					<reportElement style="Column header" positionType="Float" x="217" y="-2" width="92" height="14" forecolor="#000000"/>
					<textElement>
						<font fontName="DejaVu Sans" size="12" isBold="true" pdfFontName="Helvetica"/>
					</textElement>
					<text><![CDATA[Manufacturer]]></text>
				</staticText>
				<staticText>
					<reportElement style="Column header" positionType="Float" x="311" y="-2" width="78" height="14" forecolor="#000000"/>
					<textElement>
						<font fontName="DejaVu Sans" size="12" isBold="true" pdfFontName="Helvetica"/>
					</textElement>
					<text><![CDATA[Dept]]></text>
				</staticText>
				<staticText>
					<reportElement style="Column header" positionType="Float" x="389" y="-2" width="115" height="14" forecolor="#000000"/>
					<textElement>
						<font fontName="DejaVu Sans" size="12" isBold="true" pdfFontName="Helvetica"/>
					</textElement>
					<text><![CDATA[PM]]></text>
				</staticText>
				<staticText>
					<reportElement style="Column header" positionType="Float" x="504" y="0" width="63" height="14" forecolor="#000000"/>
					<textElement>
						<font fontName="DejaVu Sans" size="12" isBold="true" pdfFontName="Helvetica"/>
					</textElement>
					<text><![CDATA[Status]]></text>
				</staticText>
			</frame>
		</band>
	</columnHeader>
	<detail>
		<band height="103" splitType="Stretch">
			<line>
				<reportElement positionType="FixRelativeToBottom" x="7" y="93" width="795" height="1"/>
			</line>
			<frame>
				<reportElement x="227" y="-33" width="575" height="14"/>
			</frame>
			<subreport>
				<reportElement x="27" y="6" width="543" height="81"/>
				<subreportParameter name="MetaDataConnection"/>
				<subreportParameter name="control_number">
					<subreportParameterExpression><![CDATA[$P{control_number}]]></subreportParameterExpression>
				</subreportParameter>
				<connectionExpression><![CDATA[java.sql.DriverManager.getConnection("jdbc:sqlserver://VMCITSM:1433;DatabaseName=HDQUERY", "App", "BiteMe")]]></connectionExpression>
				<subreportExpression class="java.lang.String"><![CDATA[$P{SUBREPORT_DIR} + "itsm.jasper"]]></subreportExpression>
			</subreport>
		</band>
	</detail>
	<columnFooter>
		<band height="7" splitType="Stretch">
			<line>
				<reportElement positionType="FixRelativeToBottom" x="0" y="3" width="555" height="1"/>
				<graphicElement>
					<pen lineWidth="0.5" lineColor="#999999"/>
				</graphicElement>
			</line>
		</band>
	</columnFooter>
	<pageFooter>
		<band height="16" splitType="Stretch">
			<frame>
				<reportElement mode="Opaque" x="2" y="0" width="800" height="16" forecolor="#D0B48E" backcolor="#006699"/>
				<textField evaluationTime="Report">
					<reportElement style="Column header" x="753" y="0" width="40" height="16" forecolor="#FFFFFF"/>
					<textElement verticalAlignment="Middle">
						<font fontName="DejaVu Sans" size="12" isBold="false" pdfFontName="Helvetica"/>
					</textElement>
					<textFieldExpression class="java.lang.String"><![CDATA[" " + $V{PAGE_NUMBER}]]></textFieldExpression>
				</textField>
				<textField>
					<reportElement style="Column header" x="673" y="0" width="80" height="16" forecolor="#FFFFFF"/>
					<textElement textAlignment="Right" verticalAlignment="Middle">
						<font fontName="DejaVu Sans" size="12" isBold="false" pdfFontName="Helvetica"/>
					</textElement>
					<textFieldExpression class="java.lang.String"><![CDATA["Page "+$V{PAGE_NUMBER}+" of"]]></textFieldExpression>
				</textField>
				<textField pattern="EEEEE dd MMMMM yyyy">
					<reportElement style="Column header" x="2" y="0" width="197" height="16" forecolor="#FFFFFF"/>
					<textElement verticalAlignment="Middle">
						<font fontName="DejaVu Sans" size="12" isBold="false" pdfFontName="Helvetica"/>
					</textElement>
					<textFieldExpression class="java.util.Date"><![CDATA[new java.util.Date()]]></textFieldExpression>
				</textField>
			</frame>
		</band>
	</pageFooter>
	<summary>
		<band splitType="Stretch"/>
	</summary>
</jasperReport>


now the imports have all the requiered jars from Jasper reports, because I can run single reports from my mysql server and or mssql. When I want to do a multiple report like this one, then I get the error that I posted above.
I will include as well the jrxml I am calling from main for viewing pleasure.
<?xml version="1.0" encoding="UTF-8"?>
<jasperReport xmlns="http://jasperreports.sourceforge.net/jasperreports" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://jasperreports.sourceforge.net/jasperreports http://jasperreports.sourceforge.net/xsd/jasperreport.xsd" name="itsm" language="groovy" pageWidth="802" pageHeight="555" orientation="Landscape" columnWidth="802" leftMargin="0" rightMargin="0" topMargin="0" bottomMargin="0">
	<parameter name="control_number" class="java.lang.String" isForPrompting="false"/>
	<parameter name="MetaDataConnection" class="java.sql.Connection" isForPrompting="false">
		<defaultValueExpression><![CDATA[java.sql.DriverManager.getConnection("jdbc:sqlserver://VMCITSM:1433;DatabaseName=HDQUERY", "App", "BiteMe")]]></defaultValueExpression>
[/color]	</parameter>
	<queryString>
		<![CDATA[SELECT
  dbo.it_inquiry_to_ce_int_cc.actioncode,
  dbo.it_inquiry_to_ce_int_cc.actioncode1,
  dbo.it_inquiry_to_ce_int_cc.actioncode2,
  dbo.it_inquiry_to_ce_int_cc.assetclosecode,
  dbo.it_inquiry_to_ce_int_cc.assigned_technician,
  dbo.it_inquiry_to_ce_int_cc.assigned_technician_rep,
  dbo.it_inquiry_to_ce_int_cc.control_number,
  dbo.it_inquiry_to_ce_int_cc.diary_entered_by,
  dbo.it_inquiry_to_ce_int_cc.diary_entered_dt,
  sv_diary.diary_id,
  it_inquiry_to_ce_int_cc.diary_note,
  sv_diary.entry_dt,
  it_inquiry_to_ce_int_cc.failurecode,
  it_inquiry_to_ce_int_cc.failurecode1,
  it_inquiry_to_ce_int_cc.failurecode2,
  sv_diary.notes,
  it_inquiry_to_ce_int_cc.priority,
  it_inquiry_to_ce_int_cc.problem_comments,
  sv_diary.rep,
  it_inquiry_to_ce_int_cc.reported_by,
  it_inquiry_to_ce_int_cc.reported_by_name,
  it_inquiry_to_ce_int_cc.reported_by_phone,
  CONVERT(varchar, it_inquiry_to_ce_int_cc.reported_dt, 101) AS reported_dt,
  CONVERT(varchar, it_inquiry_to_ce_int_cc.resolved_dt, 101) AS resolved_dt,
  it_inquiry_to_ce_int_cc.room,
  it_inquiry_to_ce_int_cc.solution_txt,
  it_inquiry_to_ce_int_cc.[status],
  sv_diary.topic,
  it_inquiry_to_ce_int_cc.work_order_reference,
  it_inquiry_to_ce_int_cc.to_ce_id,
  sv_solve_txt.change_uid
FROM
  dbo.it_inquiry_to_ce_int_cc
  FULL OUTER JOIN dbo.sv_diary ON (dbo.it_inquiry_to_ce_int_cc.work_order_reference = dbo.sv_diary.inquiry_id)
  FULL OUTER JOIN dbo.sv_solve_txt ON (dbo.it_inquiry_to_ce_int_cc.work_order_reference = dbo.sv_solve_txt.inquiry_id)
WHERE
  dbo.it_inquiry_to_ce_int_cc.control_number = $P{control_number}
ORDER BY
  dbo.it_inquiry_to_ce_int_cc.work_order_reference DESC,
  dbo.sv_diary.change_dt,
  dbo.it_inquiry_to_ce_int_cc.integration_timestamp DESC]]>
	</queryString>
	<field name="actioncode" class="java.lang.Integer">
		<fieldDescription><![CDATA[]]></fieldDescription>
	</field>
	<field name="actioncode1" class="java.lang.Integer">
		<fieldDescription><![CDATA[]]></fieldDescription>
	</field>
	<field name="actioncode2" class="java.lang.Integer">
		<fieldDescription><![CDATA[]]></fieldDescription>
	</field>
	<field name="assetclosecode" class="java.lang.Integer">
		<fieldDescription><![CDATA[]]></fieldDescription>
	</field>
	<field name="assigned_technician" class="java.lang.String">
		<fieldDescription><![CDATA[]]></fieldDescription>
	</field>
	<field name="assigned_technician_rep" class="java.lang.String">
		<fieldDescription><![CDATA[]]></fieldDescription>
	</field>
	<field name="control_number" class="java.lang.String">
		<fieldDescription><![CDATA[]]></fieldDescription>
	</field>
	<field name="diary_entered_by" class="java.lang.String">
		<fieldDescription><![CDATA[]]></fieldDescription>
	</field>
	<field name="diary_entered_dt" class="java.sql.Timestamp">
		<fieldDescription><![CDATA[]]></fieldDescription>
	</field>
	<field name="diary_id" class="java.lang.Integer">
		<fieldDescription><![CDATA[]]></fieldDescription>
	</field>
	<field name="diary_note" class="java.lang.String">
		<fieldDescription><![CDATA[]]></fieldDescription>
	</field>
	<field name="entry_dt" class="java.sql.Timestamp">
		<fieldDescription><![CDATA[]]></fieldDescription>
	</field>
	<field name="failurecode" class="java.lang.Integer">
		<fieldDescription><![CDATA[]]></fieldDescription>
	</field>
	<field name="failurecode1" class="java.lang.Integer">
		<fieldDescription><![CDATA[]]></fieldDescription>
	</field>
	<field name="failurecode2" class="java.lang.Integer">
		<fieldDescription><![CDATA[]]></fieldDescription>
	</field>
	<field name="notes" class="java.lang.String">
		<fieldDescription><![CDATA[]]></fieldDescription>
	</field>
	<field name="priority" class="java.lang.String">
		<fieldDescription><![CDATA[]]></fieldDescription>
	</field>
	<field name="problem_comments" class="java.lang.String">
		<fieldDescription><![CDATA[]]></fieldDescription>
	</field>
	<field name="rep" class="java.lang.String">
		<fieldDescription><![CDATA[]]></fieldDescription>
	</field>
	<field name="reported_by" class="java.lang.String">
		<fieldDescription><![CDATA[]]></fieldDescription>
	</field>
	<field name="reported_by_name" class="java.lang.String">
		<fieldDescription><![CDATA[]]></fieldDescription>
	</field>
	<field name="reported_by_phone" class="java.lang.String">
		<fieldDescription><![CDATA[]]></fieldDescription>
	</field>
	<field name="reported_dt" class="java.lang.String">
		<fieldDescription><![CDATA[]]></fieldDescription>
	</field>
	<field name="resolved_dt" class="java.lang.String">
		<fieldDescription><![CDATA[]]></fieldDescription>
	</field>
	<field name="room" class="java.lang.String">
		<fieldDescription><![CDATA[]]></fieldDescription>
	</field>
	<field name="solution_txt" class="java.lang.String">
		<fieldDescription><![CDATA[]]></fieldDescription>
	</field>
	<field name="status" class="java.lang.String">
		<fieldDescription><![CDATA[]]></fieldDescription>
	</field>
	<field name="topic" class="java.lang.String">
		<fieldDescription><![CDATA[]]></fieldDescription>
	</field>
	<field name="work_order_reference" class="java.lang.Integer">
		<fieldDescription><![CDATA[]]></fieldDescription>
	</field>
	<field name="to_ce_id" class="java.lang.Integer">
		<fieldDescription><![CDATA[]]></fieldDescription>
	</field>
	<field name="change_uid" class="java.lang.String">
		<fieldDescription><![CDATA[]]></fieldDescription>
	</field>
	<background>
		<band splitType="Stretch"/>
	</background>
	<title>
		<band height="5" splitType="Stretch"/>
	</title>
	<pageHeader>
		<band height="5" splitType="Stretch"/>
	</pageHeader>
	<columnHeader>
		<band height="5" splitType="Stretch"/>
	</columnHeader>
	<detail>
		<band height="143" splitType="Stretch">
			<textField>
				<reportElement x="333" y="9" width="100" height="20"/>
				<textElement>
					<font fontName="DejaVu Sans" size="8"/>
				</textElement>
				<textFieldExpression class="java.lang.String"><![CDATA[$F{control_number}]]></textFieldExpression>
			</textField>
			<textField>
				<reportElement x="448" y="9" width="157" height="20"/>
				<textElement>
					<font fontName="DejaVu Sans" size="8"/>
				</textElement>
				<textFieldExpression class="java.lang.String"><![CDATA[$F{status}]]></textFieldExpression>
			</textField>
			<textField>
				<reportElement x="112" y="9" width="100" height="20"/>
				<textElement>
					<font fontName="DejaVu Sans" size="8"/>
				</textElement>
				<textFieldExpression class="java.lang.String"><![CDATA[$F{reported_dt}]]></textFieldExpression>
			</textField>
			<textField>
				<reportElement x="5" y="9" width="100" height="20"/>
				<textElement>
					<font fontName="DejaVu Sans" size="8"/>
				</textElement>
				<textFieldExpression class="java.lang.Integer"><![CDATA[$F{work_order_reference}]]></textFieldExpression>
			</textField>
			<textField>
				<reportElement x="113" y="32" width="100" height="20"/>
				<textElement>
					<font fontName="DejaVu Sans" size="8"/>
				</textElement>
				<textFieldExpression class="java.lang.String"><![CDATA[$F{assigned_technician}]]></textFieldExpression>
			</textField>
			<textField>
				<reportElement x="223" y="32" width="100" height="20"/>
				<textElement>
					<font fontName="DejaVu Sans" size="8"/>
				</textElement>
				<textFieldExpression class="java.lang.Integer"><![CDATA[$F{diary_id}]]></textFieldExpression>
			</textField>
			<textField isBlankWhenNull="true">
				<reportElement x="333" y="32" width="453" height="20"/>
				<textElement>
					<font fontName="DejaVu Sans" size="8"/>
				</textElement>
				<textFieldExpression class="java.lang.String"><![CDATA[$F{diary_note}]]></textFieldExpression>
			</textField>
			<textField>
				<reportElement x="222" y="9" width="100" height="20"/>
				<textElement>
					<font fontName="DejaVu Sans" size="8"/>
				</textElement>
				<textFieldExpression class="java.lang.String"><![CDATA[$F{reported_by_name}]]></textFieldExpression>
			</textField>
			<textField isBlankWhenNull="true">
				<reportElement x="112" y="77" width="674" height="20"/>
				<textElement>
					<font fontName="DejaVu Sans" size="8"/>
				</textElement>
				<textFieldExpression class="java.lang.String"><![CDATA[$F{notes}]]></textFieldExpression>
			</textField>
			<textField isBlankWhenNull="true">
				<reportElement x="112" y="54" width="674" height="20"/>
				<textElement>
					<font fontName="DejaVu Sans" size="8"/>
				</textElement>
				<textFieldExpression class="java.lang.String"><![CDATA[$F{problem_comments}]]></textFieldExpression>
			</textField>
			<textField isBlankWhenNull="true">
				<reportElement x="113" y="99" width="674" height="20"/>
				<textElement>
					<font fontName="DejaVu Sans" size="8"/>
				</textElement>
				<textFieldExpression class="java.lang.String"><![CDATA[$F{solution_txt}]]></textFieldExpression>
			</textField>
		</band>
	</detail>
	<columnFooter>
		<band height="5" splitType="Stretch"/>
	</columnFooter>
	<pageFooter>
		<band height="5" splitType="Stretch"/>
	</pageFooter>
	<summary>
		<band height="5" splitType="Stretch"/>
	</summary>
</jasperReport>


This is my web.xml and context.xml files on the project.

<?xml version="1.0" encoding="UTF-8"?>
<web-app version="2.4" xmlns="http://java.sun.com/xml/ns/j2ee" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://java.sun.com/xml/ns/j2ee http://java.sun.com/xml/ns/j2ee/web-app_2_4.xsd">
    <servlet>
        <servlet-name>PMCompletionReport</servlet-name>
        <servlet-class>PMCompletionReport</servlet-class>
    </servlet>
    <servlet>
        <servlet-name>PMCompletionReportKY</servlet-name>
        <servlet-class>PMCompletionReportKY</servlet-class>
    </servlet>
    <servlet>
        <servlet-name>EquipmentReport</servlet-name>
        <servlet-class>EquipmentReport</servlet-class>
    </servlet>
    <servlet>
        <servlet-name>EquipmentReportKF</servlet-name>
        <servlet-class>EquipmentReportKF</servlet-class>
    </servlet>
    <servlet>
        <servlet-name>TimeSheetReport</servlet-name>
        <servlet-class>TimeSheetReport</servlet-class>
    </servlet>
    <servlet>
        <servlet-name>TimeSheetSummary</servlet-name>
        <servlet-class>TimeSheetSummary</servlet-class>
    </servlet>
    <servlet>
        <servlet-name>PMCompletionReport_Past</servlet-name>
        <servlet-class>PMCompletionReport_Past</servlet-class>
    </servlet>
    <servlet>
        <servlet-name>Test</servlet-name>
        <servlet-class>Test</servlet-class>
    </servlet>
    <servlet-mapping>
        <servlet-name>PMCompletionReport</servlet-name>
        <url-pattern>/PMCompletionReport</url-pattern>
    </servlet-mapping>
    <servlet-mapping>
        <servlet-name>PMCompletionReportKY</servlet-name>
        <url-pattern>/PMCompletionReportKY</url-pattern>
    </servlet-mapping>
    <servlet-mapping>
        <servlet-name>EquipmentReport</servlet-name>
        <url-pattern>/EquipmentReport</url-pattern>
    </servlet-mapping>
    <servlet-mapping>
        <servlet-name>EquipmentReportKF</servlet-name>
        <url-pattern>/EquipmentReportKF</url-pattern>
    </servlet-mapping>
    <servlet-mapping>
        <servlet-name>TimeSheetReport</servlet-name>
        <url-pattern>/TimeSheetReport</url-pattern>
    </servlet-mapping>
    <servlet-mapping>
        <servlet-name>TimeSheetSummary</servlet-name>
        <url-pattern>/TimeSheetSummary</url-pattern>
    </servlet-mapping>
    <servlet-mapping>
        <servlet-name>PMCompletionReport_Past</servlet-name>
        <url-pattern>/PMCompletionReport_Past</url-pattern>
    </servlet-mapping>
    <servlet-mapping>
        <servlet-name>Test</servlet-name>
        <url-pattern>/Test</url-pattern>
    </servlet-mapping>
    <session-config>
        <session-timeout>
            30
        </session-timeout>
    </session-config>
    <welcome-file-list>
        <welcome-file>index.jsp</welcome-file>
    </welcome-file-list>
</web-app>



<?xml version="1.0" encoding="UTF-8"?>
<Context antiJARLocking="true" path="/ClinicalEngineeringReports">
<!-- Commented out because it does not work. 
<Resource
      auth="Container"
      name="jdbc/ITSM"
      type="javax.sql.DataSource"
      driverClassName="com.microsoft.sqlserver.jdbc.SQLServerDriver"
      password="SPU73UMe"
      maxIdle="2"
      maxWait="5000"
      username="AppQueryCE"
      url="jdbc:sqlserver://VMCITSMSQL2A:1433;databaseName=HDQUERY"
      maxActive="4"/> -->
</Context>


This post has been edited by pjamrisk: 13 July 2010 - 02:34 AM

Was This Post Helpful? 0
  • +
  • -

#3 pjamrisk  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 38
  • Joined: 02-March 09

Re: Multi Database in Tomcat 5.5.20

Posted 14 July 2010 - 01:59 AM

Ok, so far I made it work as it is. The solutuion so far was to update to jasper server 3.7 and have java 1.6 with it running. I installed the sqljdbc4.jar on the /lib folder of tomcat, and viola.
Now, this is at my sandbox server. I tried the same procedure on our production server, and the damn thing does not work, because I can't get Jasper server to work on it.
The only difference is the Ubuntu version. Sandbox is on 10.04lts, and production is on 9.04.
We did an update about 2 weeks ago before we tried this, and guess what, now production does not want to work with the solution in our sandbox.
I'll let you guys know what I get going, maybe I will have the production server update to 10.04, I don't know yet.

If anyone has a step by step tutorial on ubuntu 9.04 for jasper, let me know please.
Thanks,
Patrick
Was This Post Helpful? 0
  • +
  • -

#4 audriusa  Icon User is offline

  • New D.I.C Head

Reputation: 5
  • View blog
  • Posts: 28
  • Joined: 22-September 10

Re: Multi Database in Tomcat 5.5.20

Posted 23 September 2010 - 03:14 AM

Do you actually have the JDBC driver .jar in your WAR file or somewhere directly on the server? The driver .jar should be in the lib folder. See diagram of the WAR internal structure.

This post has been edited by audriusa: 23 September 2010 - 03:15 AM

Was This Post Helpful? 0
  • +
  • -

Page 1 of 1