cancel
Showing results for 
Search instead for 
Did you mean: 

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

Former Member
0 Kudos
192

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

Accepted Solutions (1)

Accepted Solutions (1)

ted_ueda
Advisor
Advisor
0 Kudos

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

Answers (4)

Answers (4)

Former Member
0 Kudos

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
0 Kudos

Hi Vishal

to be honest, I don't really understand what you're asking me. What is "sy-subrc"? Why do you want to connect with native SQL and not with the aid of crysraldecisions?

cheers

-t

Former Member
0 Kudos

Hey Tom,

Thanks for replying back!!!

To be more precise my requirement is to connect to MySQL database (used by third party system) and access the data in SAP by writing code in native SQL , ABAP. Sy-subrc is a system variable which gets initialized to 0 whenever there is successful execution of a statement. So, when I am writing a statement connect to :dbs where dbs is the connection name given in DBCON table, sy-subrc is not equal to 0 and hence connection to MySQL database is not established.

But I created an ODBC connection and tested it It's working fine.

Hope I am able to reach you and communicate you my issue.

Cheers

VJ

Former Member
0 Kudos

Hi Vishal,

unfortunately I am not familiar with SAP and ABAP. I understand that your connection fails. This can have various reasons:

- credentials of the user you are trying to connect with (should work as ODBC connection is fine)

- database name (in your code you write dbs = 'TST_TIME' but your text says MSSQL_DSN=SFA, is that a collision?)

- connection strings (what does DBCON table return when you select your connection)

- driver issues

- IP/Port issues

is there a way to read/debug the value of sy-subrc and get more information with that value?

cheers

-t

Edited by: tomspengler on Jan 5, 2012 3:20 PM

Former Member
0 Kudos

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
0 Kudos

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 Member
0 Kudos

@all

is there anyone who can help me with this topic?

Former Member
0 Kudos

nobody?

Former Member
0 Kudos

has anybody ever changed databeses tied to a report at runtime?

former_member217070
Active Participant
0 Kudos

Hi, Tom,

[Here is a sample|https://www.sdn.sap.com/irj/scn/go/portal/prtroot/docs/library/uuid/506be76a-3c11-2c10-1986-8b816c501b47] that demonstrates how to change the database and server of a report that uses a stored procedure using the CRJ.

It comes from this [wiki page of CRJ samples|http://wiki.sdn.sap.com/wiki/display/BOBJ/CrystalReportsJava+SDKSamples], in the database section.

I know these wikis can be hard to find, so no worries if you didn't know about it.

Regards,

Bryan

Former Member
0 Kudos

Hello Bryan,

thanks for your answer. Unfortunately it didn't solve my Problem, because I want to change the Type of Database, too. Please find the above posts that explain my problem in detail (names of parameters in stored proc and thus in report differ).

SP in MSSQL params named '@param1'

SP in MySQL params named 'param1'

Params are used in functions, too.

Do you have any further suggestions?

Best regards,

Tom

Former Member
0 Kudos

after all I have managed to set param Values, now I'm stuck because the parms seem to have dissappeared in the formula fields. E.g. I receive this Exception:

<br><br>

com.crystaldecisions.sdk.occa.report.lib.ReportSDKException: Ausnahme in Formel ""{@DurationMin}"" bei ""{dataProc.Duration}"":

Dieser Feldname ist unbekannt.---- Error code:-2147467259 Error code name:failed

at com.businessobjects.reports.sdk.JRCCommunicationAdapter.a(SourceFile:2285)

at com.businessobjects.reports.sdk.JRCCommunicationAdapter.if(SourceFile:733)

at com.businessobjects.reports.sdk.JRCCommunicationAdapter.a(SourceFile:167)

at com.businessobjects.reports.sdk.JRCCommunicationAdapter$2.a(SourceFile:529)

at com.businessobjects.reports.sdk.JRCCommunicationAdapter$2.call(SourceFile:527)

at com.crystaldecisions.reports.common.ThreadGuard.syncExecute(SourceFile:102)

at com.businessobjects.reports.sdk.JRCCommunicationAdapter.for(SourceFile:525)

at com.businessobjects.reports.sdk.JRCCommunicationAdapter.int(SourceFile:424)

at com.businessobjects.reports.sdk.JRCCommunicationAdapter.request(SourceFile:352)

at com.businessobjects.sdk.erom.jrc.a.a(SourceFile:54)

at com.businessobjects.sdk.erom.jrc.a.execute(SourceFile:67)

at com.crystaldecisions.proxy.remoteagent.RemoteAgent$a.execute(SourceFile:716)

at com.crystaldecisions.proxy.remoteagent.CommunicationChannel.a(SourceFile:125)

at com.crystaldecisions.proxy.remoteagent.RemoteAgent.a(SourceFile:537)

at com.crystaldecisions.sdk.occa.report.application.RowsetController.getSQLStatement(SourceFile:1485)

at org.apache.jsp.report_jsp._jspService(report_jsp.java:360)

at org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:70)

at javax.servlet.http.HttpServlet.service(HttpServlet.java:717)

at org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:386)

at org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:313)

at org.apache.jasper.servlet.JspServlet.service(JspServlet.java:260)

at javax.servlet.http.HttpServlet.service(HttpServlet.java:717)

at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:290)

at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)

at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:233)

at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:191)

at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:127)

at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:102)

at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:109)

at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:298)

at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:859)

at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.process(Http11Protocol.java:588)

at org.apache.tomcat.util.net.JIoEndpoint$Worker.run(JIoEndpoint.java:489)

at java.lang.Thread.run(Unknown Source)

Caused by: com.crystaldecisions.reports.dataengine.DataEngineException: Ausnahme in Formel ""{@DurationMin}"" bei ""{dataProc.Duration}"":

Dieser Feldname ist unbekannt.

at com.crystaldecisions.reports.dataengine.DataSourceManager.do(SourceFile:692)

at com.crystaldecisions.reports.dataengine.DataSourceManager.new(SourceFile:607)

at com.crystaldecisions.reports.dataengine.DataSourceManagerCoordinator.a(SourceFile:505)

at com.crystaldecisions.reports.dataengine.DataSourceManager.a(SourceFile:1060)

at com.crystaldecisions.reports.dataengine.DataProcessor2.a(SourceFile:729)

at com.crystaldecisions.reports.dataengine.DataProcessor2.a(SourceFile:699)

at com.businessobjects.reports.sdk.requesthandler.DatabaseRequestHandler.i(SourceFile:1308)

at com.businessobjects.reports.sdk.requesthandler.DatabaseRequestHandler.k(SourceFile:1297)

at com.businessobjects.reports.sdk.JRCCommunicationAdapter.do(SourceFile:1310)

at com.businessobjects.reports.sdk.JRCCommunicationAdapter.if(SourceFile:661)

... 32 more

Caused by: com.crystaldecisions.reports.formulas.FormulaException: Ausnahme in Formel ""{@DurationMin}"" bei ""{dataProc.Duration}"":

Dieser Feldname ist unbekannt.

at com.crystaldecisions.reports.formulas.o.a(SourceFile:1041)

at com.crystaldecisions.reports.formulas.o.do(SourceFile:1013)

at com.crystaldecisions.reports.formulas.o.new(SourceFile:688)

at com.crystaldecisions.reports.formulas.o.else(SourceFile:459)

at com.crystaldecisions.reports.formulas.f.void(SourceFile:65)

at com.crystaldecisions.reports.formulas.FormulaParser.a(SourceFile:345)

at com.crystaldecisions.reports.formulas.r.a(SourceFile:89)

at com.crystaldecisions.reports.formulas.FormulaInfo.a(SourceFile:570)

at com.crystaldecisions.reports.formulas.FormulaService.compile(SourceFile:347)

at com.crystaldecisions.reports.reportdefinition.FormulaCompiler.a(SourceFile:139)

at com.crystaldecisions.reports.reportdefinition.FormulaFieldDefinitionBase.compile(SourceFile:928)

at com.crystaldecisions.reports.reportdefinition.o.a(SourceFile:1360)

at com.crystaldecisions.reports.reportdefinition.o.a(SourceFile:1348)

at com.crystaldecisions.reports.reportdefinition.o.a(SourceFile:4225)

at com.crystaldecisions.reports.dataengine.DataSourceManager.do(SourceFile:687)

... 41 more

<br><br>

where dataProc is my stored procedure and @DurationMin is a function field.

do I have to call verifyDatabase() when I have changed DBs and set the param Values?

Is there a defined sequence to execute these steps?

Edited by: tomspengler on Dec 8, 2011 1:55 PM

0 Kudos

Moved to Java SDK forum.