cancel
Showing results for 
Search instead for 
Did you mean: 

The SQL query is not executing

Former Member
0 Kudos

Hi

I have the following situation: In a project we designed our reports calling a stored procedure the exits in a MS SQL Server 200 database. The Stored Procedures works fine and when they are used in the report everything works perfectly.

The reports are being made with CR DEsigner 11, when the designer ends them, ha pass them to me and we put them in our java web application. I open them and even preview them since the Crystal Reprots Perspective of Eclipse and I can see the data, so everything to this point is OK.

The problem comes when I change of connection, I'm trying to connect every report to the same host and database, and when the reprot is displayed in the browser there is no data. I profile the SQL commands that are executed when the report is requested and I found that the reprot is not executing the stored procedure.

I guess because i'm connectring the report to the same database and host that was used when the report is created and i'm also passing exactly the same parameters of the stored procedure, then report thinks that it doesn't have executing again becuase it will be the same information.

SO, i wonder if there is a way to request to the report to execute the sql query every time i have to display it.

thanks for any help.

Accepted Solutions (0)

Answers (1)

Answers (1)

Former Member
0 Kudos

Hi,

Please check/uncheck the Verify Stored Procedures on First Refresh option in the designer.

(File->Report Options) and try to execute again.

Are you using Crystal Report For Eclipse ?

Crystal Reports for Eclipse v2.0 includes several SDKs and provides support SDKs for following previous versions:

- Crystal Reports Java SDK (12.0)

- Viewers Java SDK (12.0)

- Crystal Reports Designer Extension Points SDK (12.0)

- Java Reporting Component SDK (11.8)

- Java Reporting Component SDK (11.5)

Can I have a look at the change Connection code sample you are trying to execute ?.

Please let me know the results.

Thanks,

Neeraj

Former Member
0 Kudos

Here is the code:

DatabaseController databaseController = reportClientDoc.getDatabaseController();

ConnectionInfos connectionInfos = (ConnectionInfos)databaseController.getConnectionInfos(null);

IConnectionInfo oldConnectionInfo = connectionInfos.getConnectionInfo(0);

IConnectionInfo newConnectionInfo = new ConnectionInfo();

newConnectionInfo.setKind(ConnectionInfoKind.SQL);

PropertyBag newProperties = new PropertyBag();

newProperties.putStringValue(PropertyBagHelper.CONNINFO_JDBC_CONNECTION_URL, data.getConnectionString());

newProperties.putStringValue(PropertyBagHelper.CONNINFO_JDBC_DATABASECLASSNAME, data.getDriverClassName());

newProperties.putStringValue(PropertyBagHelper.CONNINFO_DATABASE_NAME, data.getDatabaseName());

newProperties.putStringValue(PropertyBagHelper.CONNINFO_SERVER_TYPE, "JDBC (JNDI)");

newProperties.putStringValue(PropertyBagHelper.CONNINFO_DATABASE_DLL, "crdb_jdbc.dll");

newConnectionInfo.setAttributes(newProperties);

newConnectionInfo.setUserName(data.getUsername());

newConnectionInfo.setPassword(data.getPassword());

databaseController.replaceConnection(oldConnectionInfo, newConnectionInfo, null, DBOptions._useDefault);

-


In the previous code there is an instance called data, data is a java bean with the necessary information to connect to the database(like connection string, user, password)

-


+Please check/uncheck the Verify Stored Procedures on First Refresh option in the designer.

(File->Report Options) and try to execute again.+

It doesn't work.

-


Yes, i'm using Crystal Reports for Eclipse 2.0 SP1, but the reports are designed with Crystal Reports v11

Edited by: Othon Reyes on May 25, 2009 6:11 PM

Former Member
0 Kudos

Hi,

Instead of :

databaseController.replaceConnection(oldConnectionInfo, newConnectionInfo, null, DBOptions._useDefault);

Try with follwing code line and let me know the results:

int replaceParams = DBOptions._ignoreCurrentTableQualifiers + DBOptions._doNotVerifyDB;

databaseController.replaceConnection(oldConnectionInfo, newConnectionInfo, null,replaceParams);

Thanks,

Neeraj

Former Member
0 Kudos

It doesn't work. Do you think i have to redesign the report. Is there a real difference if a design the report from Eclipse than Crystal Reports (Designer)?.

What is really surprising is that the report worked a week ago and nobody has changed it since then. Now it doesn´t work.

Is there a way to explore the data recovered by the report?.

In your personal experience, what do you suggest to work around this problem?

Former Member
0 Kudos

Hi,

Crystal Designer has more capabilities/features than the designer provided in the Eclipse IDE,however it does not make much difference whether you are using Eclipse Designer or Crystal Report Designer.

What happens when you try to view the report using a simple viewer.jsp?without changing the connection?

Also what happens if you dont use the data bean and give everything there only?

Restart application server and let me know the results.

Thanks,

Neeraj

ted_ueda
Employee
Employee
0 Kudos

What is really surprising is that the report worked a week ago and nobody has changed it since then. Now it doesn´t work.

Did anything change on the RDBMS side? Additional packages, changes to the schema, ownership, etc?

It's informative that the stored proc isn't being invoked.

If you configure Log4J logging to DEBUG, you should see the database invokes from the Crystal Java engine, specifically the queries sent and the number of rowsets returned. Do you notice any errors?

Sincerely,

Ted Ueda

Former Member
0 Kudos

What happens when you try to view the report using a simple viewer.jsp?without changing the connection?

2009-05-25 14:06:09,250 ERROR - detected an exception: Error de conexión: [SQLServer 2000 Driver for JDBC]Error establishing socket.

But the database server is ok, so i think the rpt needs more information to be connect to the database.

Also what happens if you dont use the data bean and give everything there only?

The same, the rpt is not executing the stored procedure.

Did anything change on the RDBMS side? Additional packages, changes to the schema, ownership, etc?

No, the server is ok and the database is ok.

If you configure Log4J logging to DEBUG, you should see the database invokes from the Crystal Java engine, specifically the queries sent and the number of rowsets returned. Do you notice any errors?

I have a problem here with log4j. In my project i'm using spring and with the help of spring i configure log4j, basically with spring is easier to configure log4j.

I'm telling you this because i have log4j working but when my application reaches the code to change the connection to the rerport, log4j dies and stop sending any message to the stdout or to a log file. I haven't since this behavior in any other app o library. So i want to guess i have something wrong with my log4j or maybe with log4j+spring.

So, i remove all the code of spring referring to log4j, but the problem with log4j wasn't solved.

What i will try now is to remove spring from the project.

This is my log4j.properties:

log4j.rootLogger=DEBUG, stdout

log4j.appender.stdout=org.apache.log4j.ConsoleAppender
log4j.appender.stdout.layout=org.apache.log4j.PatternLayout
log4j.appender.stdout.layout.ConversionPattern=%d %p [%c] - %m%n

Former Member
0 Kudos

As a workaround i do the following:

I change the datasource location for a POJO in Eclipse.

This give me a new problem, Eclipse couldn't translate the saved data.

I open Crystal Reports(Designer) and uncheck the Saved Data from File>Report Options

After that, in my code i execute the stored procedure with iBatis and passed a list to the report.

The test was succesful and the report is being displayed correctly in the browser.

This is the code:


Map<String, Integer> param = new HashMap<String, Integer>();
param.put("pintOrigen", new Integer(request.getParameter("pintOrigen")) );
param.put("pintOpcion", new Integer(request.getParameter("pintOpcion")) );

List<Anexo14Vo> datos = dao.getAnexo14(param);

String report = reportes.getProperty("reporteAnexo14");
log.warn("reporte a abrir: "+ report);
ReportClientDocument rcd = new ReportClientDocument();
rcd.open(report, 0);
DatabaseController dbc = rcd.getDatabaseController();

String reportTable = "Anexo14Vo"; //The table name in your report.

dbc.setDataSource(datos, Anexo14Vo.class, reportTable, reportTable);
log.warn("Se termina busqueda");
return rcd.getReportSource();

In my pc the code works, but a similary code doesn´t work so i have to review that too to be sure it will not fail.

Even i have a possible workaround and want to know why the stored procedure is not executed from the report.

Thanks for your help.