on 2011 Oct 05 9:36 AM
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
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
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
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)
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
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
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
Moved to Java SDK forum.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
75 | |
9 | |
9 | |
8 | |
8 | |
7 | |
7 | |
6 | |
4 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.