aperez_6

I need to read a file from a path or a blob field in the database and I want to complete some areas with information from the database (by filling in fields / tags to combine). Maintaining the format that user puts.

Hi, Yesterday, I just asked a similar question. I need to read a file from a path or a blob field in the database and I want to complete some areas with information from the database (by filling in fields / tags to combine). Maintaining the format that user puts. Is there a tool or way to do it? Thank you,

I would like to know if it is possible to interact with a text document through the Jasper report (through Scriptlet). And that this, contains "tags" to replace them with values obtained by consulting DataSets. My goal is to get the user to create their own documents with a series of rules (I say by how to locate the "tags") and dynamically these can be replaced by the corresponding values of the queries. I would appreciate obtaining an example or someone who had applied it, introduce us to this option. And I hope I expressed myself well! Regards,

Now, that I have time to give my solution that I discovered; between your answers and what was searched in other forums. Say that through the REPORT_CONNECTION parameter and passing it through the Scriptlet through java functions; I have managed to get the report connection directly. And that's it! The thread of the question closes, for my part

Hi hozawa, I was on vacation these days and I just saw it. Well with your answer, does it mean that it is impossible to pass the username and password of the Jasper connection? How do you work in a class to execute queries from the Jasper connection (using the Scriptlet)? Regards,

Good afternoon, I am looking for information on the following: - From Jasper I'm using a Scriptlet, I have several data adapters (different connections). Well, from my Java class, I want to try to establish the connection without directly using the username and password (it's much better without showing them). But yes, of the URL connection. My idea is to pass the username / password connection from the same jasper to the Scriptlet method / function. It will be the best way, won't? If someone knows/works with this, I would appreciate an example of how to interact between jasper and the java class (scriptlet). I am sure that someone knows the answer and it would be a detail to explain to all the people in the same situation tha's me. Thanks in advance. Aranzazu P. PD: My attemps in the Java Class package gestor.documental;import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; import javax.naming.NamingException; import net.sf.jasperreports.engine.JRDefaultScriptlet; import net.sf.jasperreports.engine.JRParameter; import net.sf.jasperreports.engine.JRScriptletException; import net.sf.jasperreports.engine.fill.JRFillParameter; public class ConnectionManager extends JRDefaultScriptlet{ private static String url = "jdbc:oracle:thin:@host:port:SERVICE_NAME"; private static String driverName = "oracle.jdbc.driver.OracleDriver"; private static String username = "username"; private static String password = "pswd"; private static Connection con; private Connection conn; private Object server; // EXAMPLE 1: I try to use but it doesn't work and I don't know WHY? I don't understand why the people who exposed him in a forum and it works. public Connection getConnection_pruebas() throws JRScriptletException { if (getParameterValue(JRFillParameter.REPORT_CONNECTION) != null) { conn= ((Connection) getParameterValue(JRParameter.REPORT_CONNECTION) ); } else { throw new RuntimeException("No db-connection configured in the report!"); } return conn; } // EXAMPLE 2: I try to use but it doesn't work public static Connection getConnection() throws JRScriptletException, ClassNotFoundException { try { Class.forName(driverName).newInstance(); try { con= DriverManager.getConnection(url, username, password); } catch (SQLException ex) { // log an exception. fro example: //System.out.println("Failed to create the database connection."); } } catch (ClassNotFoundException ex) { System.out.println("ClassNotFound: " + ex.getCause() ); } finally { if (con != null) { try{ con.close(); } catch(Exception e){ e.printStackTrace(); } } } return con; } public static void main(String[] args) throws JRScriptletException, SQLException { Connection con = getConnection(); } }

Hi, How could I make a generic code to connect to the database according to the datasource in a Java class that I use in a scriptlet? With this code works when I execute an statement. But it is not correctly to me because is not private the information: public class ConnectionManager { private static String url = "jdbc:oracle:thin:@xxxx:1111:name"; private static String driverName = "oracle.jdbc.driver.OracleDriver"; private static String username = "user"; private static String password = "pswd"; private static Connection con; public static Connection getConnection() { try { Class.forName(driverName); try { con = DriverManager.getConnection(url, username, password); } catch (SQLException ex) { // log an exception. fro example: System.out.println("Failed to create the database connection."); } } catch (ClassNotFoundException ex) { // log an exception. for example: System.out.println("Driver not found."); } return con; } } With this code doesn't work: import java.sql.Connection; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; import java.util.ArrayList; import java.util.Arrays; import java.util.List; import javax.faces.model.SelectItem; import javax.naming.NamingException; import net.sf.jasperreports.engine.JRDefaultScriptlet; import net.sf.jasperreports.engine.JRParameter; import net.sf.jasperreports.engine.JRScriptletException; //import javax.faces.model.SelectItem; //import net.sf.jasperreports.engine.JRParameter; //Clase que contendrá las Utilidades Comunes para cualquier Report de Jasper public class Utilidades_Principal extends JRDefaultScriptlet { public List get_list_values(String sql) throws SQLException, NamingException, JRScriptletException { String result = ""; Statement stmt = null; Connection conf = (Connection)this.getParameterValue(JRParameter.REPORT_CONNECTION); try { stmt = conf.createStatement(); ResultSet rs = stmt.executeQuery(sql); while (rs.next()) { result = result + rs.getString(1) + ","; } } catch (SQLException e) { e.printStackTrace(); } finally { if (stmt != null) { stmt.close(); } } return Arrays.asList(result.split(",")); } I would like to try to get the connection in a generic way. If someone has succeeded, I would appreciate it. Thanks in advance,

I have to resume it after the holidays and see the situation in which I leave it. When I try it, I will comment on the result. Thanks for the reply.

Good afternoon, I have a problem when execute a jasper, at the moment that a one parameter is null. The query in the main Dataset is: SELECT DISTINCT DECODE( NVL($P{p_idreg_docs_prest},'0'), '0', NULL, CASE WHEN T2.TRA_TRADUCCION IS NOT NULL OR D.EJERCICIO IS NOT NULL THEN (T.TRA_TRADUCCION||' -> '||T2.TRA_TRADUCCION||' '||D.EJERCICIO) ELSE T.TRA_TRADUCCION END ) DOCUMENTS FROM COMUN.COM_DOM_VAL_TRADUCCIONES_VW T, COMUN.COM_DOM_VAL_TRADUCCIONES_VW T2, PREST.PRE_EJECS_OPERS_DOCUMENTOS D WHERE ( D.IDREG IN ( $P!{p_idreg_docs_prest}) and $P{p_idreg_docs_prest} is not null or $P{p_idreg_docs_prest} is null ) AND T.VAL_IDREG=D.IDDO_MODELO_INFORMACION AND T2.VAL_IDREG(+) = D.IDDO_PERIODO AND T.TRA_IDDO_IDIOMA = $P{p_idioma} AND T2.TRA_IDDO_IDIOMA(+) = $P{p_idioma} When the parameter p_idreg_docs_prest IS NOT NULL, works wonderfully. But if the parameter is null, goes out the following message: net.sf.jasperreports.engine.JRException: net.sf.jasperreports.engine.JRException: Error executing SQL statement for: Reclamacion_documentos_simple. at com.jaspersoft.studio.editor.preview.view.control.ReportControler.fillReport(ReportControler.java:548) at com.jaspersoft.studio.editor.preview.view.control.ReportControler.access$20(ReportControler.java:523) at com.jaspersoft.studio.editor.preview.view.control.ReportControler$5.run(ReportControler.java:404) at org.eclipse.core.internal.jobs.Worker.run(Worker.java:55) Caused by: net.sf.jasperreports.engine.JRException: Error executing SQL statement for: Reclamacion_documentos_simple. at net.sf.jasperreports.engine.query.JRJdbcQueryExecuter.createDatasource(JRJdbcQueryExecuter.java:344) at net.sf.jasperreports.engine.fill.JRFillDataset.createQueryDatasource(JRFillDataset.java:1245) at net.sf.jasperreports.engine.fill.JRFillDataset.initDatasource(JRFillDataset.java:723) at net.sf.jasperreports.engine.fill.BaseReportFiller.setParameters(BaseReportFiller.java:438) at net.sf.jasperreports.engine.fill.JRBaseFiller.fill(JRBaseFiller.java:550) at net.sf.jasperreports.engine.fill.BaseFillHandle$ReportFill.run(BaseFillHandle.java:123) at java.lang.Thread.run(Thread.java:745) Caused by: java.sql.SQLSyntaxErrorException: ORA-00936: missing an expression at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:450) at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:399) at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:1059) at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:522) at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:257) at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:587) at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:225) at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:53) at oracle.jdbc.driver.T4CPreparedStatement.executeForDescribe(T4CPreparedStatement.java:774) at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:925) at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1111) at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:4798) at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:4845) at oracle.jdbc.driver.OraclePreparedStatementWrapper.executeQuery(OraclePreparedStatementWrapper.java:1501) at net.sf.jasperreports.engine.query.JRJdbcQueryExecuter.createDatasource(JRJdbcQueryExecuter.java:310) ... 6 more With the statement $X{ IN, FIELD, PARAMETER }, doesn't works too. Maybe I have to change a property in the TextField ( DOCUMENTS ) or make an Print When Expression? If someone has had this problem, I would appreciate reporting the solution. Thank you,

Thanks Hozawa, I had tried this specificacion but this didn't work too. I think that the problem is with the type definition of the parameter and I don't find manner to use correctly. Because the parameter is a String and it contains X values that I have to use in the query such as collection of numeric values. If I use the $X{IN, field1, p_param} the error in the dataset is: Caused by: java.sql.SQLException: Token SQL92 not supported in position: 556 Also I've tried: $X{IN, TO_CHAR(field1), p_param} But it was a big error hehe. If someone has encountered the problem, I would appreciate getting the solution! Thanks,

Hi, I have a problem with a parameter String ( I usually pass values such as: "1234,8756,4212" ), and I use this parameter in the main dataset query: select * from table where field1 in ( $P!{p_param} ) The before statement works when I pass the p_param with values. But when the p_param hasn't values doesn't work the jasper report. How could I put the solution? Thanks in advanced.

Hi all, I need someone to show me an example to see how to interact with PIVOT queries regarding the pivoted field in the main query. Because this field happens to become X fields, according to its value. EXAMPLE: PARAMETERS: PARAM1 java.lang.Integer => 2PARAM2 java.lang.String => "2,3,4,5,6"[/code]MAIN QUERY: SELECT A.*, B.TOTAL_IMPORTFROM( select * from ( SELECT sd.NAME, sd.CODE, fi.GROUP, fi.PERCENT, fi.IDREG_CONCEPT FROM TABLE1 fi, TABLE2 sd, WHERE fi.ID = $P{ PARAM1l} AND fi.ID = sd.ID ) pivot ( count( IDREG_CONCEPT ) for IDREG_CONCEPT in ( $P!{ PARAM2} ) )) A,(SELECT sd.ID, sd.NAME, sd.CODE, fi.GROUP, fi.PERCENT, fi.IDREG_CONCEPT, SUM( IMPORT ) TOTAL_IMPORT FROM TABLE1 fi, TABLE2 sd, WHERE fi.ID = $P{ PARAM1l} AND fi.ID = sd.IDgroup by sd.ID, sd.NAME, sd.CODE, fi.GROUP, fi.PERCENT, fi.IDREG_CONCEPTorder by sd.ID, sd.CODE, sd.GROUP, sd.PERCENT) BWHERE A.ID = B.ID AND A.NAME = B.NAME AND A.CODE = B.CODE AND A.GROUP = B.GROUP AND A.PERCENT = B.PERCENT AND A.CONCEPT = B.CONCEPT;[/code]RESULT: ID NAME CODE GROUP PERCENT CONCEPT 2 3 4 5 6 So, I would like to assign different descriptions for the field label . Thank you,

Hi, Today I saw your question and I think you can do the following in the TextField Expression Editor that represents the title: ( $V{PAGE_NUMBER}.intValue() == 1 ? "Original Bill of Lading" : ($V{PAGE_NUMBER}.intValue() == 2 ? "Shipping Order" : ($V{PAGE_NUMBER}.intValue() == 3 ? "Memorandum" : " Packing List")))[/code]I did not provide it! Test by youself.

Thank you for the answer. I solved it by seeing in a forum an indication, which said to add the library ojdbc6.jar in the Build Path of the Jasper Studio. This is how I managed to execute the test method.

I have continued to try other options and finally I have seen what I was missing! It was to add the JDBC library to the Build Path. And the code that worked for me is the following: public String get_document_PE05_BIS( ) throws JRScriptletException, SQLException{ ResultSet rs = null; PreparedStatement pstmt = null; int dbsituacio=0; int dbidparte=0; Connection conn; try { Class.forName("oracle.jdbc.driver.OracleDriver"); conn = DriverManager.getConnection("jdbc:oracle:thin:@xxx.xxx:xxxx:YYYY", "xxx", "xxx"); if ( conn == null ) { return "Conexión nula"; } if (conn.isReadOnly() == true) //make sure update can proceed { conn.setReadOnly(false); } String query = "select clase_situacio, id_parte from partes where sini_prov = ? and sini_of = ? and sini_any = ? and sini_numero = ? and sini_reca = ?"; pstmt = conn.prepareStatement(query); // create a statement pstmt.setInt(1, 43); // set input parameter pstmt.setInt(2, 1); // set input parameter pstmt.setInt(3, 2017); // set input parameter pstmt.setInt(4, 23622); // set input parameter pstmt.setInt(5, 0); // set input parameter rs = pstmt.executeQuery(); // extract data from the ResultSet while (rs.next()) { dbsituacio = rs.getInt(1); dbidparte = rs.getInt(2); //System.out.println(dbsituacio + "t" + dbidparte); } return dbsituacio + "t" + dbidparte; } catch (Exception e) { return e.getMessage() + " " + e.getCause(); } } I hope it will help someone in my situation. What I saw when you change the Java code and redo the .JAR. You must follow the following steps: 1. Close the Jasper Studio and reopen it 2. Refresh the Project 3. Compile the Report 4. Execute the result Regards,