title: Change Databaseconnection with Java for several tables in Crystal Reports
body: <p>We are using Crystal Reports XI and I generated a simple report with one table. During creating the report I connected to our development database with ODBC.
  Now I wanted to change the datasource during runtime in Java in order to connect to another database of ours.</p>
  
  <p>So I created a method to change the datasource. Because we are using different database systems (infomix, oracle and mysql).</p>
  
  <pre><code>private ReportClientDocument replaceDatabaseConnection(ReportClientDocument doc,  Database database, String ip, String port, String dbname, String dbservername, String username, String passwort, String userOrSchemaName) {
      StopWatch sw = new StopWatch();
      try {       
  
          for (int i = 0; i &lt; doc.getDatabaseController().getDatabase().getTables().size(); i++) {
              ITable table = doc.getDatabaseController().getDatabase().getTables().get(i);
              sw.start(table.getName());
  
  
              System.out.println("processing table : " + table.getName());
  
              // dbane.owner.tablename
              String original_qualifiedName = table.getQualifiedName();
              String new_qualifierName = original_qualifiedName;
  
              switch (database) {
              case ORACLE:
                  // SCHEMA.TABLENAME
                  String tableName = StringUtils.substringAfterLast(original_qualifiedName, ".").toUpperCase();
                  new_qualifierName = userOrSchemaName.toUpperCase()+"."+tableName;
                  break;
              case INFORMIX:
                  // DATABASE.OWNER.TABLENAME
                  new_qualifierName = dbname + ":" + userOrSchemaName + "." + StringUtils.substringAfterLast(original_qualifiedName, ".");                    
  
                  break;
              case MYSQL:
                  // DATABASE.OWNER.TABLENAME
                  new_qualifierName = dbname + "." + userOrSchemaName + "." + StringUtils.substringAfterLast(original_qualifiedName, ".");                    
                  break;
              default:
                  break;
              }
  
              String jdbcConnectionString = null;
              String preQEServerName = null;
              String driver = null;
  
              switch (database) {
              case INFORMIX:
                  // !com.informix.jdbc.IfxDriver!jdbc:informix-sqli://localhost:40421/export:INFORMIXSERVER=cargool1!user={userid}!password={password}
                  jdbcConnectionString = "!com.informix.jdbc.IfxDriver!jdbc:informix-sqli://"+ip+":"+port+"/"+dbname+":INFORMIXSERVER="+dbservername+"!user={userid}!password={password}";
                  // jdbc:informix-sqli://localhost:40421/export:INFORMIXSERVER=cargool1
                  preQEServerName      = "jdbc:informix-sqli://"+ip+":"+port+"/"+dbname+":INFORMIXSERVER="+dbservername;
                  // com.informix.jdbc.IfxDriver
                  driver = "com.informix.jdbc.IfxDriver";
                  break;
              case MYSQL:
                  // !com.mysql.jdbc.Driver!jdbc:mysql://172.20.9.170:3306/hego
                  jdbcConnectionString = "!com.mysql.jdbc.Driver!jdbc:mysql://"+ip+":"+port+"/"+dbname;
                  // jdbc:mysql://172.20.9.170:3306/customer
                  preQEServerName      = "jdbc:mysql://"+ip+":"+port+"/"+dbname;
                  // com.mysql.jdbc.Driver
                  driver = "com.mysql.jdbc.Driver";
                  break;
              case ORACLE:
                  // !oracle.jdbc.driver.OracleDriver!jdbc:oracle:thin:@//localhost:50000/tmsddb2
                  jdbcConnectionString = "!oracle.jdbc.driver.OracleDriver!jdbc:oracle:thin:@//"+ip+":"+port+"/"+dbname;
                  // jdbc:oracle:thin:@//localhost:50000/tmsddb2
                  preQEServerName      = "jdbc:oracle:thin:@//"+ip+":"+port+"/"+dbname;
                  // "oracle.jdbc.driver.OracleDriver"
                  driver = "oracle.jdbc.driver.OracleDriver";
                  break;
              default:
                  break;
              }
  
  
  
      System.out.println("Qualfied Name : "+ table.getQualifiedName());
      table.setQualifiedName(new_qualifierName);
  
      ConnectionInfo newConnectionInfo = new ConnectionInfo();
  
      PropertyBag boPropertyBag1 = new PropertyBag();
  
      boPropertyBag1.put("JDBC Connection String", jdbcConnectionString);
      boPropertyBag1.put("PreQEServerName",   preQEServerName);
      boPropertyBag1.put("Server Type", "JDBC (JNDI)");
      boPropertyBag1.put("Database DLL", "crdb_jdbc.dll");
      boPropertyBag1.put("Database", dbname);
      boPropertyBag1.put("Database Class Name", driver);
      boPropertyBag1.put("Use JDBC", "true");
      boPropertyBag1.put("Database Name", dbname);
      boPropertyBag1.put("Server Name", preQEServerName);
      boPropertyBag1.put("Connection URL", preQEServerName);
      boPropertyBag1.put("Server", null);
  
      newConnectionInfo.setAttributes(boPropertyBag1);
      newConnectionInfo.setUserName(username);
      newConnectionInfo.setPassword(passwort);
  
      table.setConnectionInfo(newConnectionInfo);
  
              doc.getDatabaseController().setTableLocation(table, doc.getDatabaseController().getDatabase().getTables().get(i));
              System.out.println("***************************DONE********************************");
              sw.stop();
          }
          return doc;
  
      } catch (Exception ex) {
          System.out.println(ExceptionUtils.getFullStackTrace(ex));
      }
  
  
      System.out.println(sw.prettyPrint());
  
      return doc;
  
  }
  </code></pre>
  
  <p>Now the stange behavior is if I am using a report with only one table it is working fine. I can connect to each database without any problems and it is working really fast.</p>
  
  <p>But if the report has more than one table, I am getting strange database connection errors that the username / password are incorrect. The username from the exception is completly wrong (it is not the username I passed to the method).</p>
  
  <blockquote>
    <p>com.crystaldecisions.sdk.occa.report.lib.ReportSDKLogonException: Fehler bei der Anmeldung: Incorrect password or user com.informix.asf.IfxASFRemoteException: username@employee-nb1 is not known on the database server.---- Error code:-2147217393 Error code name:dbLogonFailed</p>
  </blockquote>
  
  <p>First thing, I didn't want to connect to a informix database. I passed to the method a mysql parameter. The exception comes from informix which is the database I connected to during creating the report (over ODBC).</p>
  
  <p>The next strange thing is that <strong>username@employee-nb1</strong> Username is the username I logged in to the database during creating the report and the most irritating thing is emplyee-nb1. That is the name of one notebook in our company but it has nothing to do with me. The report was never open/created on the machine, and the java process isn't running on that machine either. </p>
  
  <p>Like I said, if I am using a report (created the same way) with only one table, everything works fine. With more then one table, I am getting this error. And I am not using any subreports yet.</p>
  
  <p>Has anyone an idea?</p>
  
  <p>This is how I call the method</p>
  
  <pre><code>    private void createReport8() {
      ReportClientDocument doc = new ReportClientDocument();
  
      try {
          doc.setReportAppServer(ReportClientDocument.inprocConnectionString);
          doc.open("C:\\dev\\reports\\003_jdbc_informix_3_tabellen.rpt",OpenReportOptions._openAsReadOnly);
  
          changeDataSource(doc, null, null, "username", "******", "!com.mysql.jdbc.Driver!jdbc:mysql://172.20.9.170:3306/customerdb", "com.mysql.jdbc.Driver", null);
  
          PDFExportFormatOptions outputOptions = new PDFExportFormatOptions();
          ExportOptions exportOptions = new ExportOptions();
          exportOptions.setExportFormatType(ReportExportFormat.PDF);
          exportOptions.setFormatOptions(outputOptions);
  
          InputStream stream = doc.getPrintOutputController().export(
                  exportOptions);
          byte[] bytes = StreamUtils.getBytes(stream);
  
          FileUtils.writeByteArrayToFile(new File("C:\\dev\\reports\\pdf\\Infomix_MultipleTables001.pdf"), bytes);
  
      }catch (Exception ex) {
          System.out.println(ex);
      }
  }
  </code></pre>