cancel
Showing results for 
Search instead for 
Did you mean: 

iSeries JDBC Changing DataSource in the Runtime

kanishka1975
Member
0 Kudos

I have a bunch of Crystal reports that are being used in multiple environments. It has been working just fine with the Java API and recently the Series got updated and now when I try to set the connection information to the report in the runtime, It doesn't really pick up the tables from the Schema in the URL. Crystal picks up the first table that it finds. Not the one specified in the URL. How can I force the Crystal to use the specific Schema? Below is the code that I use. Any help would be appreciated.

I tried putting various property names that I can think of like "Schema Name". "Database Name" "Owner" (as it shows in the Crystal Report IDE) without much luck.

JDBC URL: jdbc:as400://<HOST>/<SCHEMANAME>;naming=sql;errors=full;date format=iso;extended dynamic=false

private static void changeDataSource(ReportClientDocument clientDoc,
			String reportName, String tableName, String username,
			String password, String connectionURL, String driverName,
			String jndiName) throws ReportSDKException {

		PropertyBag propertyBag = null;
		IConnectionInfo connectionInfo = null;
		ITable origTable = null;
		ITable newTable = null;

		// Declare variables to hold ConnectionInfo values.
		// Below is the list of values required to switch to use a JDBC/JNDI
		// connection
		String TRUSTED_CONNECTION = "false";
		String SERVER_TYPE = "JDBC (JNDI)";
		String USE_JDBC = "true";
		String DATABASE_DLL = "crdb_jdbc.dll";
		String JNDI_OPTIONAL_NAME = jndiName;
		String CONNECTION_URL = connectionURL;
		String DATABASE_CLASS_NAME = driverName;

		// Declare variables to hold database User Name and Password values
		String DB_USER_NAME = username;
		String DB_PASSWORD = password;
		System.out.println("Trusted_Connection:" + TRUSTED_CONNECTION);
		System.out.println("Server Type:" + SERVER_TYPE);
		System.out.println("Use JDBC:" + USE_JDBC);
		System.out.println("Database DLL:" + DATABASE_DLL);
		System.out.println("JNDIOptionalName:" + JNDI_OPTIONAL_NAME);
		System.out.println("Connection URL:" + CONNECTION_URL);
		System.out.println("Database Class Name:" + DATABASE_CLASS_NAME);
		System.out.println("DB_USER_NAME:" + DB_USER_NAME);
		System.out.println("DB_PASSWORD:" + DB_PASSWORD);
		// Obtain collection of tables from this database controller
		if (reportName == null || reportName.equals("")) {
			Tables tables = clientDoc.getDatabaseController().getDatabase()
					.getTables();
			for (int i = 0; i < tables.size(); i++) {
				origTable = tables.getTable(i);
				if (tableName == null || origTable.getName().equals(tableName)) {
					newTable = (ITable) origTable;
					newTable.setQualifiedName(origTable.getAlias());
					connectionInfo = newTable.getConnectionInfo();


					// Set new table connection property attributes
					propertyBag = new PropertyBag();
					// Overwrite any existing properties with updated values
					propertyBag.put("Trusted_Connection", TRUSTED_CONNECTION);
					propertyBag.put("Server Type", SERVER_TYPE);
					propertyBag.put("Use JDBC", USE_JDBC);
					propertyBag.put("Database DLL", DATABASE_DLL);
					propertyBag.put("JNDIOptionalName", JNDI_OPTIONAL_NAME);
					propertyBag.put("Connection URL", CONNECTION_URL);
					propertyBag.put("Database Name", "COURTS");
					propertyBag.put("Database Class Name", DATABASE_CLASS_NAME);
					propertyBag.put("Schema Name", "COURTS");
					connectionInfo.setAttributes(propertyBag);
					connectionInfo.setUserName(DB_USER_NAME);
					connectionInfo.setPassword(DB_PASSWORD);
					// Update the table information
					clientDoc.getDatabaseController().setTableLocation(
							origTable, newTable);
				}
			}
		}
		// Next loop through all the subreports and pass in the same
		// information. You may consider
		// creating a separate method which accepts
		if (reportName == null || !(reportName.equals(""))) {
			IStrings subNames = clientDoc.getSubreportController()
					.getSubreportNames();
			for (int subNum = 0; subNum < subNames.size(); subNum++) {
				Tables tables = clientDoc.getSubreportController()
						.getSubreport(subNames.getString(subNum))
						.getDatabaseController().getDatabase().getTables();
				for (int i = 0; i < tables.size(); i++) {
					origTable = tables.getTable(i);
					if (tableName == null
							|| origTable.getName().equals(tableName)) {
						newTable = (ITable) origTable;
						newTable.setQualifiedName(origTable.getAlias());
						// Change connection information properties
						connectionInfo = newTable.getConnectionInfo();
						// Set new table connection property attributes
						propertyBag = new PropertyBag();
						// Overwrite any existing properties with updated values
						propertyBag.put("Trusted_Connection",
								TRUSTED_CONNECTION);
						propertyBag.put("Server Type", SERVER_TYPE);
						propertyBag.put("Use JDBC", USE_JDBC);
						propertyBag.put("Database DLL", DATABASE_DLL);
						propertyBag.put("JNDIOptionalName", JNDI_OPTIONAL_NAME);
						propertyBag.put("Connection URL", CONNECTION_URL);
						propertyBag.put("Database Class Name",
								DATABASE_CLASS_NAME);
						propertyBag.put("Schema Name", "COURTS");
						
						connectionInfo.setAttributes(propertyBag);
						connectionInfo.setUserName(DB_USER_NAME);
						connectionInfo.setPassword(DB_PASSWORD);


						// Update the table information
						clientDoc.getSubreportController()
								.getSubreport(subNames.getString(subNum))
								.getDatabaseController()
								.setTableLocation(origTable, newTable);
					}
				}
			}
		}
	}
mynynachau
Community Advocate
Community Advocate
0 Kudos

Thank you for visiting SAP Community to get answers to your questions. Since you're new in asking questions here, I recommend that you familiarize yourself with https://community.sap.com/resources/questions-and-answers (if you haven't already), as it provides tips for preparing questions that draw responses from our members.

Should you wish, you can revise your question by selecting Actions, then Edit (although once someone answers your question, you'll lose the ability to edit the question -- but if that happens, you can leave more details in a comment).

Finally, if you're hoping to connect with readers, please consider adding a picture to your profile. Here's how you do it: https://www.youtube.com/watch?v=F5JdUbyjfMA&list=PLpQebylHrdh5s3gwy-h6RtymfDpoz3vDS . By personalizing your profile with a photo of you, you encourage readers to respond.

Best regards

Mynyna

SAP Community moderator

Accepted Solutions (0)

Answers (1)

Answers (1)

dorothea_stein
Participant
0 Kudos

Dear Kanishka,

what you describe below looks good to me. You are using SQL naming, so the default schema is either the user name (if no schema is specified), or the schema name attached to the URL, just like you describe it. If the schema resides in a so-called independent ASP, you need to specify this iASP name using property "database name". (The full list of JDBC properties is e.g. available here: http://jt400.sourceforge.net/doc/com/ibm/as400/access/doc-files/JDBCProperties.html.)

Whether setting the default schema is sufficient, however, depends on the implementation of the following call:

Tables tables = clientDoc.getDatabaseController().getDatabase().getTables(); 

If it gets mapped to a direct query to IBM system catalogs (SYSTABLES), the default schema should do the job. But in Java this is not very likely. It is more likely, that it gets mapped to a call to the JDBC DatabaseMetadata API. In this case, it depends if database controller code is able to extract the schema name from property "Connection URL" to stuff it into the metadata API call. (Setting the default schema alone won't limit the result.)

ResultSet rs = connection.getMetaData().getTables(null, 'schema', null, null); 


If the code used to work before, there is another thought:

You mentioned that you have updated the IBM i server. If you did not do that yet, I would recommend to update the JDBC driver (JTOpen) itself as well, to avoid client/server protocol problems. You can download the latest level of JTOpen from https://sourceforge.net/projects/jt400/.

Another possible root cause for sudden reduction of result sets is that the database user lost its rights to access the tables in the given schema.

Hope this helps.

Best regards,
Dorothea

0 Kudos

Thank you Dorothea for the prompt response.

I found a solution where I set a configuration parameter SCHEMA representing the environment and when we create a new ITable to match the runtime environment, I set the qualifiedName as below. (I'm sharing the code for whatever it's worth)

  private static void changeDataSource(ReportClientDocument clientDoc,
   String reportName, String tableName, String username,
   String password, String connectionURL, String driverName,
   String jndiName, String schema) throws ReportSDKException {


  PropertyBag propertyBag = null;
  IConnectionInfo connectionInfo = null;
  ITable origTable = null;
  ITable newTable = null;


  // Declare variables to hold ConnectionInfo values.
  // Below is the list of values required to switch to use a JDBC/JNDI
  // connection
  String TRUSTED_CONNECTION = "false";
  String SERVER_TYPE = "JDBC (JNDI)";
  String USE_JDBC = "true";
  String DATABASE_DLL = "crdb_jdbc.dll";
  String JNDI_OPTIONAL_NAME = jndiName;
  String CONNECTION_URL = connectionURL;
  String DATABASE_CLASS_NAME = driverName;


  // Declare variables to hold database User Name and Password values
  String DB_USER_NAME = username;
  String DB_PASSWORD = password;
  System.out.println("Trusted_Connection:" + TRUSTED_CONNECTION);
  System.out.println("Server Type:" + SERVER_TYPE);
  System.out.println("Use JDBC:" + USE_JDBC);
  System.out.println("Database DLL:" + DATABASE_DLL);
  System.out.println("JNDIOptionalName:" + JNDI_OPTIONAL_NAME);
  System.out.println("Connection URL:" + CONNECTION_URL);
  System.out.println("Database Class Name:" + DATABASE_CLASS_NAME);
  System.out.println("DB_USER_NAME:" + DB_USER_NAME);
  System.out.println("DB_PASSWORD:" + DB_PASSWORD);
  System.out.println("DB_SCHEMA" + schema);
  // Obtain collection of tables from this database controller
  if (reportName == null || reportName.equals("")) {
   Tables tables = clientDoc.getDatabaseController().getDatabase()
     .getTables();
   for (int i = 0; i < tables.size(); i++) {
    origTable = tables.getTable(i);
    if (tableName == null || origTable.getName().equals(tableName)) {
     newTable = (ITable) origTable;
 
    //********** SET THE QUALIFIED NAME TO REPRESENT THE RUNTIME SCHEMA **************      
     newTable.setQualifiedName(schema + "." + origTable.getAlias());

     connectionInfo = newTable.getConnectionInfo();


     // Set new table connection property attributes
     propertyBag = new PropertyBag();
     // Overwrite any existing properties with updated values
     propertyBag.put("Trusted_Connection", TRUSTED_CONNECTION);
     propertyBag.put("Server Type", SERVER_TYPE);
     propertyBag.put("Use JDBC", USE_JDBC);
     propertyBag.put("Database DLL", DATABASE_DLL);
     propertyBag.put("JNDIOptionalName", JNDI_OPTIONAL_NAME);
     propertyBag.put("Connection URL", CONNECTION_URL);
     propertyBag.put("Database Class Name", DATABASE_CLASS_NAME);
     connectionInfo.setAttributes(propertyBag);
     connectionInfo.setUserName(DB_USER_NAME);
     connectionInfo.setPassword(DB_PASSWORD);


     // Update the table information
     clientDoc.getDatabaseController().setTableLocation(
       origTable, newTable);
    }
   }
  }
  // Next loop through all the subreports and pass in the same
  // information. You may consider
  // creating a separate method which accepts
  if (reportName == null || !(reportName.equals(""))) {
   IStrings subNames = clientDoc.getSubreportController()
     .getSubreportNames();
   for (int subNum = 0; subNum < subNames.size(); subNum++) {
    Tables tables = clientDoc.getSubreportController()
      .getSubreport(subNames.getString(subNum))
      .getDatabaseController().getDatabase().getTables();
    for (int i = 0; i < tables.size(); i++) {
     origTable = tables.getTable(i);
     if (tableName == null
       || origTable.getName().equals(tableName)) {
      newTable = (ITable) origTable;

      //********** SET THE QUALIFIED NAME TO REPRESENT THE RUNTIME SCHEMA **************
      newTable.setQualifiedName(schema + "." + origTable.getAlias());
      // Change connection information properties

      connectionInfo = newTable.getConnectionInfo();
      // Set new table connection property attributes
      propertyBag = new PropertyBag();


      // Overwrite any existing properties with updated values
      propertyBag.put("Trusted_Connection",
        TRUSTED_CONNECTION);
      propertyBag.put("Server Type", SERVER_TYPE);
      propertyBag.put("Use JDBC", USE_JDBC);
      propertyBag.put("Database DLL", DATABASE_DLL);
      propertyBag.put("JNDIOptionalName", JNDI_OPTIONAL_NAME);
      propertyBag.put("Connection URL", CONNECTION_URL);
      propertyBag.put("Database Class Name",
        DATABASE_CLASS_NAME);
      connectionInfo.setAttributes(propertyBag);
      connectionInfo.setUserName(DB_USER_NAME);
      connectionInfo.setPassword(DB_PASSWORD);


      // Update the table information
      clientDoc.getSubreportController()
        .getSubreport(subNames.getString(subNum))
        .getDatabaseController()
        .setTableLocation(origTable, newTable);
     }
    }
   }
  }
 }