Solved: Changing databases from MS SQL Express to MySQL in...

Former Member · ‎10-05-2011

Hi all,

I have an issue in changing databases from MS SQL Express to MYSQL or vice versa in a Report at runtime.

Here is what I do:

I create a very simple report with the Crystal Report Designer 2008 V 12.3.0.601.

This report uses a stored procedure to retrieve it's data. I connect to the Database with JDBC.

I have two Databases with similar schema and procedures: MS SQL Express an MySQL.

When I create the report with MSSQL all parameters have the '@' character, as they are named. ok.

I deploy this report in a JSP application with the crystaldecisions SDK an show it in html viewer format.

The connection to the MSSQL DB gets established by my JSP and the CRJavaHelper.

I pass the parameters through my URL to the JSP and finally to the CRJavaHelper (addDiscreteParameterValue)

and everything works fine.

The challenge starts, when I try to change the Datasource of this report to MySQL at runtime (which is a requirement in my project).

I successfully can establish the DBConnection, but my stored procedure just doesn't get called!

The report just displays totally empty, no Exceptions etc. are thrown. In the DB logfile I see the effort to find my procedure

and that's all. The Statements called by the SDK finds the correct Procedure, but there's no call to the procedure.

Of course I know, that MS SQL names it's procedures slightly different (';1' at the end), but even when I rename the table Alias to the correct name in CRHelperJava, I get no call to the procedure in MySQL. I also renamed the parameter-fields in changeDataSource(..) by casting the table to IProcedure and working on its methods. Still no success.

What am I missing to do or what do I do wrong?

Any suggestions or even solutions?

Best Regards,

Tom

ted_ueda · ‎10-05-2011

Did you set the new parameters when changing to the parametrized stored proc?

Note that the original parameters as represented in the report would be deleted, since they're tied to the stored proc.

Usually, the way to troubleshoot this would be to first design a report directly against the target datasource, and determine what's different between it and the report that you've converted (i.e., make the changeDataSource call, then saveAs the rpt file, and open in designer and compare to the successful report).

Sincerely,

Ted Ueda

Former Member · ‎01-04-2012

Hi Tom,

I have a requirement in which I am trying to connect to MySQL database. For that I created an ODBC connection with DSN name 'SFA' and tested it. It's working fine. I made an entry in DBCON table with MSSQL_DSN=SFA in the connection info. Now i wrote a code in native sql to connect to MySQL database. But sy-subrc is not equal to 0. Can you please help me out.

dbs = 'TST_TIME'.

    EXEC SQL.
      connect to :dbs
    ENDEXEC.

Cheers

VJ

Edited by: Vishal Jindal on Jan 4, 2012 10:43 AM

Former Member · ‎12-20-2011

ok, after hours of debugging, rearranging code, trial and error the only thing that helped me out was to add a trick in the mysql DB. after all i found, that it's possible to name parameters with the @prefix by setting them inside `` characters e.g. `@myParm`. Then only rename the original tablename and alias and finally change DB by using <br>clientDoc.getDatabaseController().replaceConnection(origTable.getConnectionInfo(), newConnectionInfo, newParameterFields, DBOptions._doNotVerifyDB);

at last it was very helpful to save the report after each step and open it in designer to see what happened

reportClientDocument.saveAs("report_name.rpt", "C:", ReportSaveAsOptions._overwriteExisting)

Former Member · ‎11-11-2011

Hello Ted,

thanx for your answer (and patience). I have worked on other items and come back to this topic now. I have compared the two versions of my report: one created with MySQL, the other with MSSQL. The only difference, as far as I can see, is the naming of the Parameters and the Connection String. MSSQL uses the prefix '@' on parameter names, thus the parameters in the report are named the same e.g.' @myParam1'. In the MySQL report they are named 'myParam1'.

My idea is to take the MSSQL based report and simply rename all parameters at runtime after connecting to MySQL and before setting the parameter value. Therefore I wrote a method in CRJavaHelper:

public static void renameParameter(ReportClientDocument clientDoc, String reportName, String parameterName, String newParameterName) throws ReportSDKException{

DataDefController dataDefController = null;

if(reportName.equals(""))

dataDefController = clientDoc.getDataDefController();

else

dataDefController = clientDoc.getSubreportController().getSubreport(reportName).getDataDefController();

ParameterField paramField = (ParameterField)dataDefController.getDataDefinition().getParameterFields().findField(parameterName, FieldDisplayNameType.fieldName, Locale.getDefault());

paramField.setName(newParameterName);

paramField.setId(newParameterName);

}

Still it doesn't work. The name of the Parameter sits somewhere in the ParameterField and I get the exception

com.crystaldecisions.sdk.occa.report.lib.ReportSDKException: Parameterfeld ist nicht vorhanden.---- Error code:-2147467259 Error code name:failed

when

clientDoc.getDataDefController().getParameterFieldController().setCurrentValue(reportName, parameterName , newValue);

is called in CRJavaHelper.addDiscreteParameterValue(...)

How can I correctly rename my Parameters at runtime with the SDK Java Classes?

Thanks in advance

Tom

Edited by: tomspengler on Nov 11, 2011 4:35 PM

former_member11696 · ‎10-05-2011

Moved to Java SDK forum.

Changing databases from MS SQL Express to MySQL in a Report at runtime

Accepted Solutions (1)

Accepted Solutions (1)

Answers (4)

Answers (4)

Re: Unable to override standard methods using Adap...

Re: Can SAP's OData Framework be used as API endpo...

How to configure Custom SAPUI5 app to Fiori launch...

How to add dynamically formcell or button table in...

Re: Extended Analysis for Process Flow