2

I running a sql server procedure in Jaspersoft Studio (JSS), but the Query Dialog only show one field.

How it looks in JSS:

Procedure on Jasper Studio - show only one field.

In the SQL SERVER Management Studio, it works correctly. The procedure returns more than ten fields...

Procedure on SQL SERVER Management Studio

Has anyone seen a similar case? What might be happening?

I wrote a java code to check, and by jdbc java application, show all fields normally:

 Connection conn = VAGASConnectionFactory.getNewConnectionSQLDRIVER(VAGASConnectionFactory.AMBIENTE.DESENV);
    CallableStatement cs = conn.prepareCall("{call pr_cria_tabela_relatorio_parametrizado(90918)}");
    ResultSet rs = cs.executeQuery();
    while(rs.next()) {
        System.out.println(rs.getString(1) + "," + rs.getString(2) + "," + rs.getString(3) + "," + rs.getString(4));
    }

The result of this code runs correctly:

Jéssica Alba,28/04/1981,35,Condado

Jim Carrey,17/01/1962,54,Goiana

But in Jasper Studio, only shows one field.... :-/

The Jasper Studio log when a click to "Read fields":

Start ClassPath Mapping
Mapping: Archive: file:/home/diego.queres/.eclipse/1695504528/configuration/org.eclipse.osgi/bundles/55/1/.cp/lib/jasperreports-6.1.1.jar
Mapping: Archive: file:/home/diego.queres/.eclipse/1695504528/configuration/org.eclipse.osgi/bundles/55/1/.cp/lib/jasperreports-chart-themes-6.1.1.jar
Mapping: Archive: file:/home/diego.queres/.eclipse/1695504528/configuration/org.eclipse.osgi/bundles/55/1/.cp/lib/jasperreports-fonts-6.1.1.jar
Mapping: Archive: file:/home/diego.queres/.eclipse/1695504528/configuration/org.eclipse.osgi/bundles/55/1/.cp/lib/jasperreports-functions-6.1.1.jar
Mapping: Archive: file:/home/diego.queres/.eclipse/1695504528/configuration/org.eclipse.osgi/bundles/40/1/.cp/lib/js-common.jar
Mapping: Archive: file:/home/diego.queres/.eclipse/1695504528/configuration/org.eclipse.osgi/bundles/40/1/.cp/lib/js-extra-classes.jar
End ClassPath Mapping

I'm using Jasper Studio 6.1.1. I tried use Jasper Studio 6.3.0, without success too.

tobi6
  • 8,033
  • 6
  • 26
  • 41
diegoqueres
  • 149
  • 1
  • 13
  • 2
    Possible duplicate of [How do I call a stored procedure from Jasper Report?](http://stackoverflow.com/questions/1871642/how-do-i-call-a-stored-procedure-from-jasper-report) – Alex K Aug 10 '16 at 15:12
  • @AlexK I've tried to use syntax similar to this: `Call procedure_name ($ P {parm1}, $ P {parm2} )`, and had no effect. I use another stored procedures that work normally. This only happened to this procedure. This procedure fill a dynamic VARCHAR @SQL (MAX). Which runs at the end with an EXEC command. I've already tried: 1) Change @sql to varchar (max) - without success 2) Include SET ANSI_WARNINGS, and SET NOCOUNT to OFF - without success – diegoqueres Aug 10 '16 at 20:31
  • @AlexK, the problem is not about how to call stored procedure from Jasper Report, because Jasper Studio runs normally with another procedures. – diegoqueres Aug 10 '16 at 20:33
  • I tried replace the JDBC driver for another version, but without success - JDBC driver is https://www.microsoft.com/pt-br/download/details.aspx?id=11774 – diegoqueres Aug 10 '16 at 20:52

1 Answers1

0

After many attempts, what I discovered is the Query Editor is limited to read fields if a have a complex stored procedure (like mine). As my procedure uses cursors and dynamically makes a sql query in a string, reading the procedure scheme is hampered by the Query Editor.

I decided do the following: print the query SQL to the procedure output (print @sql). Then I cut and paste the query that runs (on the procedure output) in the Query Editor window (instead of the procedure call, "exec procedure ...."). Thus the Query Editor can read the fields of the query. After query editor read fields, I delete the query SQL and insert the procedure call again, "exec procedure ....".

Now it worked :-)

diegoqueres
  • 149
  • 1
  • 13