B4J Library jNxtReportsB4J: Library to view reports generated by Next-Reports

************** UPDATE - 11 MARCH 2021 ******************************
The source files which I used for this library have been uploaded in this thread.
Since I have stopped working with this library and my Java knowledge is minimal, I hope that someone may use these files to find a fix for the problem listed below.
Note that the libs folder is empty - due to size limitations.
This folder contained the following files - all are available online (although I suspect some might be redundant):
commons-jexl-2.1.1
commons-logging-1.1.1
itext-2.1.7
itextpdf-5.0.6
itext-rtf-2.1.7
jcalendar-1.3.2
jtds-1.3.1
nextreports-engine-X.X
poi-3.7
xstream-1.3.1
xstream-1.4.7

*************************************************************************



*******************************************************************

IMPORTANT NOTE - 11 MAY 2020
*******************************************************************
This library works fine with Java 8.


Unfortunately, there is an important issue with OpenJDK 11.
Although non-UI apps still run fine (tested with ABMaterial app), UI apps can only run fine from the IDE.
When UI apps are packed with B4JPackager11, when the program tries to generate a report, this error appears:


As there is currently no fix for this problem, if you need to have reports on an OpenJDK11 UI-app, you need to consider other reporting libraries (e.g Jasper reports, ABMReport in ABMateriral).

*****************************************************************

jNxtReportsB4J Library allows B4J programs to handle report files generated by NextReports.

It contains just one function, which receives all its parameters in a list.

LIMITATIONS:
Library was compiled with Java 8. So, I assume, it will require Java 8 to run.

Only table reports are supported.
Charts are NOT supported.

Report files can be exported CSV, TXT, XLS, XML, PDF, HTML or RTF format.

If a report contains images, then images are automatically displayed only in the HTML format export case. In order to display images in other formats, you must edit the MANIFEST.MF file (see instructions in Step 6 below).

Output files are always exported in your application folder (i.e. where your application’s Jar file is located).


TESTING:
The demo and library supplied here have been tested on Windows 7 and Windows 8.1, running Java 8, with reports generated by Next Reports Version 8.1., against a Microsoft SQL Server database.

HOW TO USE:
Step 1
Copy your report file (i.e. a file with “report” extension, generated by Next Reports), including any images necessary for your report,into a folder of your choice.
(Next Reports by default saves report files at C:\Users\YourUserName\.nextreports-8.1\output\YourConnectionName\Reports.)

Step 2
Extract the jNxtReportsB4J.zip (it contains 3 files: jNxtReportsB4J.jar, jNxtReportsB4J.xml, nextreports-engine-8.1.jar) to the Additional Libraries folder of B4J.

Step 3
Download and save to the Libraries folder of B4J the applicable Jar files listed in the “Additional Jar” section of the demo program. All files are available on the web.

Step 4
Copy the appropriate JDBC Jar file (necessary for the database type you are using), into the Libraries folder of B4J.

Step 5
Edit, as necessary, the code of NxtReportsDemo. This demo operates on a Microsoft SQL SERVER database, outputs the report in the format specified and then opens it for viewing.
You must, at least, specify your own report name and report location (the folder where you saved your report in Step 1) plus the url and driverclass parameters applicable to your database.

Step 6 (Images)
If your report contains images, these are automatically displayed ONLY in html format. In order to display them in other formats, you must add a Class-Path parameter in the MANIFEST.MF file of the Jar file of your program. In order to do this, you must have 7-Zip and Notepad++ installed (or other programs with similar functionality). Follow these steps:
  1. Locate your Jar file (in the Objects folder of your application).
  2. Right click on you Jar file and open it with 7-Zip
  3. Enter in the META-INF folder
  4. Open the MANIFEST.MF program with Notepad++
  5. Add a line similar to this: Class-Path: .\reports\
The Class-Path should point to the folder where you have stored the images of your report – see Step 1. In my example, this is a folder called “reports” which is located in my application folder (which is also the current folder). You can specify an absolute path or a relative path, as I did above.
Now the MANIFEST.MF file will look like this:

Manifest-Version: 1.0
JavaFX-Version: 2.2
JavaFX-Application-Class: b4j.example.main
Created-By: JavaFX Packager
Main-Class: anywheresoftware.b4a.keywords.Common
Class-Path: .\reports\


Next, save changes and close Notepad++.
7-Zip will ask you if MANIFEST.MF file should be updated.
Press Yes.
Close 7-Zip.
You can now run your Jar file and the images will be displayed regardless of format output.
 

Attachments

  • jNxtReportsB4J.zip
    486.9 KB · Views: 765
  • NxtReportsDemo.zip
    1.6 KB · Views: 594
  • Source jNxtReportsB4J.zip
    11.5 KB · Views: 86
Last edited:

Duncan H Williamson

Member
Licensed User
jNxtReportsB4J Library allows B4J programs to handle report files generated by NextReports.

It contains just one function, which receives all its parameters in a list.

LIMITATIONS:
Library was compiled with Java 8. So, I assume, it will require Java 8 to run.

Only table reports are supported.
Charts are NOT supported.

Report files can be exported CSV, TXT, XLS, XML, PDF, HTML or RTF format.

If a report contains images, then images are automatically displayed only in the HTML format export case. In order to display images in other formats, you must edit the MANIFEST.MF file (see instructions in Step 6 below).

Output files are always exported in your application folder (i.e. where your application’s Jar file is located).


TESTING:
The demo and library supplied here have been tested on Windows 7 and Windows 8.1, running Java 8, with reports generated by Next Reports Version 8.1., against a Microsoft SQL Server database.

HOW TO USE:
Step 1
Copy your report file (i.e. a file with “report” extension, generated by Next Reports), including any images necessary for your report,into a folder of your choice.
(Next Reports by default saves report files at C:\Users\YourUserName\.nextreports-8.1\output\YourConnectionName\Reports.)

Step 2
Extract the jNxtReportsB4J.zip (it contains 3 files: jNxtReportsB4J.jar, jNxtReportsB4J.xml, nextreports-engine-8.1.jar) to the Additional Libraries folder of B4J.

Step 3
Download and save to the Libraries folder of B4J the applicable Jar files listed in the “Additional Jar” section of the demo program. All files are available on the web.

Step 4
Copy the appropriate JDBC Jar file (necessary for the database type you are using), into the Libraries folder of B4J.

Step 5
Edit, as necessary, the code of NxtReportsDemo. This demo operates on a Microsoft SQL SERVER database, outputs the report in the format specified and then opens it for viewing.
You must, at least, specify your own report name and report location (the folder where you saved your report in Step 1) plus the url and driverclass parameters applicable to your database.

Step 6 (Images)
If your report contains images, these are automatically displayed ONLY in html format. In order to display them in other formats, you must add a Class-Path parameter in the MANIFEST.MF file of the Jar file of your program. In order to do this, you must have 7-Zip and Notepad++ installed (or other programs with similar functionality). Follow these steps:
  1. Locate your Jar file (in the Objects folder of your application).
  2. Right click on you Jar file and open it with 7-Zip
  3. Enter in the META-INF folder
  4. Open the MANIFEST.MF program with Notepad++
  5. Add a line similar to this: Class-Path: .\reports\
The Class-Path should point to the folder where you have stored the images of your report – see Step 1. In my example, this is a folder called “reports” which is located in my application folder (which is also the current folder). You can specify an absolute path or a relative path, as I did above.
Now the MANIFEST.MF file will look like this:

Manifest-Version: 1.0
JavaFX-Version: 2.2
JavaFX-Application-Class: b4j.example.main
Created-By: JavaFX Packager
Main-Class: anywheresoftware.b4a.keywords.Common
Class-Path: .\reports\


Next, save changes and close Notepad++.
7-Zip will ask you if MANIFEST.MF file should be updated.
Press Yes.
Close 7-Zip.
You can now run your Jar file and the images will be displayed regardless of format output.


Hi
I have downloaded next reports ver 8.2 (latest and only version available ) however when compiled and run the following error occurs:

unsupported classversion error unsupported major.minor ver 52.0

Im assuming this is due to the version of next reports..... any help would be greatly apreciated
 

Duncan H Williamson

Member
Licensed User
Thats great , :) I have not had the opportunity to get my report running due to time constraints.... I pointed to java 8 and lost all the error messages but created new ones due to database access issues :) so when I have time I will have another go
 

atiaust

Active Member
Licensed User
I have this library working with Next Reports Designer and Engine v9.1.

Does anyone know how to pass a 'date' variable to to the engine. Strings & numbers are fine but I get an error with dates. I have a report with 'start_date' and 'end_date'. The report works in the designer.

"ro.nextreports.engine.ReportRunnerException: ro.nextreports.engine.ReportRunnerException: java.lang.ClassCastException: javafx.scene.control.DatePicker cannot be cast to java.util.Date"

Thanks
 

atiaust

Active Member
Licensed User
Hi All,

I have added an updated tutorial that uses NextReports 9.1 and Mysql as the data source.
All the additional jar files can be obtained from the NextReports web site. http://www.next-reports.com/download.html

The NextReports 9.1 Designer, Engine and Server are available "free" and works quite well for my application which is mostly simple reports with date ranges and totals.

I have a menu in my app to select the required report and input parameters, then present the report in a PDF reader for viewing or printing.
Create the reports using the designer and copy them from the "c:/users/user_name/.nextreports-9.1/output/database_name/reports/report_name.report" to your
app/objects/reports folder or where ever you want to place them.

B4X:
#Region Project Attributes
    #MainFormWidth: 600
    #MainFormHeight: 400
'  
    'This jar file must be present in the B4J Additional Libraries folder
    '#AdditionalJar:nextreports-engine-8.1
    #AdditionalJar:nextreports-engine-9.1
'  
    'Don't forget to add jNxtReportsB4J in the B4J IDE Libraries tab
'  
'    Download these jar files to the B4J Libraries folder
'    All these jar files are available from http://www.next-reports.com/download.html
    #AdditionalJar:commons-jexl-2.1.1
    #AdditionalJar:commons-logging-1.1.1
    #AdditionalJar:itext-2.1.7
    #AdditionalJar:itext-rtf-2.1.7
    #AdditionalJar:itextpdf-5.0.6
    #AdditionalJar:xstream-1.3.1
    #AdditionalJar:xstream-1.4.7
    #AdditionalJar:poi-3.7
'  
    'replace this by your applicable database jar file
    #AdditionalJar:mysql-connector-java-5.1.39
    '#AdditionalJar:mysql-connector-java-5.1.27.jar
'      
#End Region

#Region Notes
'    Updated 20-10-2016 - atiaust
'
'    This demo was adapted from one posted by Philip Chatzigeorgiodis with his library jNxtReportsB4J
'    This works with NextReports designer and Engine Version 9.1 - see Philips Tutorial for install instructions
'    This is a UI demo using Datepickers for DateFrom and DateTo selection criteria
'
'    The report was generated using the NextReports Report Designer and adding "Between ${start_date} And ${end_date}" as the
'    selection criteria in the query
'
#End Region

Sub Process_Globals
    Private fx As JFX
    Private MainForm As Form
    Private repexec As jNxtReportsB4J        ' jNxtReportsB4J library from B4x.com
'    form for testing
    Private btnOK As Button
    Private dpDateFrom As DatePicker
    Private dpDateTo As DatePicker
'
End Sub

Sub AppStart (Form1 As Form, Args() As String)
    MainForm = Form1
    MainForm.RootPane.LoadLayout("frmMain") 'Load the layout file.
    MainForm.Show
'
End Sub

Sub btnOK_MouseClicked (EventData As MouseEvent)
        'File separator character is set, according to the OS detected.
    Dim os As String = GetSystemProperty("os.name", "").ToLowerCase
    Dim filesep As String
    If os.Contains("win") Then  
          filesep="\"
    Else If os.Contains("mac") Then
          filesep="/"
    End If  
'
    Dim inputlist As List
    Dim inlocation,repin, repout, reptype, driverclass, url As String
'  
    'Location of the *.report file and any necessary images
    inlocation=File.DirApp & filesep & "reports" 'For this demo the report is stored in the Objects/reports directory. You can specify any other directory.
    'name of report created by NextReports.
    repin="Test" ' Report specified: Report_name.report - No need for the .report extension
'  
    'Output file specification
    repout="MYOUTPUT" 'name of output report
    reptype="PDF"  ' Available options: CSV, TXT, XLS, XML, PDF, HTML or RTF. File type must be in CAPITALS.
    'Output file will be MYOUTPUT.RTF. Output file (and any necessary images) will be stored in your application directory

'    logs the location of the report file
    LogDebug("Location = "&inlocation)
    'replace this by your database driver class name
    driverclass="com.mysql.jdbc.Driver"            '    driver class for Mysql
'  
    'replace this by applicable url for your database
    'I use IP instead of server name. For some reason, in my tests with SQL SERVER, when I used server name, it would not not work on MACs
    'jdbc:mysql://localhost:3306/HerongDB?user=Herong&password=TopSecret
    url="jdbc:mysql://192.168.0.1:3306/database_name?user=db_user&password=user_pw"
    'Initialize input list and add necessary arguments in the order shown below
    inputlist.initialize
    inputlist.Add(inlocation)
    inputlist.add(repin)
    inputlist.add(repout)
    inputlist.add(reptype)
    inputlist.add(driverclass)
    inputlist.add(url)
  
'    Now add your parameters - if any
'    First, the parameter name - as it was defined in NextReports ie.
'    add compare field in Report Designer ${surname}  
'    Then add your parameter value - see examples below...
'
'    inputlist.add("surname")
'    inputlist.add("xxxxx")
'
'    for dates use dates from DatePicker inputs on the B4j UI
'  
    Dim t As Long
'
    t = dpDateFrom.DateTicks
    inputlist.add("start_date")
    inputlist.add(TicksToDate(t))
'  
    t = dpDateTo.DateTicks
    inputlist.add("end_date")
    inputlist.add(TicksToDate(t))
'  
'    you can add as many parameters as necessary
'
Try
    'Run the report and export the output report file
    repexec.reportPrepAndRun(inputlist)
Catch
    LogDebug("Error: Loading Reports : " & LastException.Message)
End Try  
    'Show the output report file
    fx.ShowExternalDocument(File.GetUri(File.DIRAPP, repout & "." & reptype))
'  
End Sub

Sub TicksToDate (ticks As Long) As Object
   Dim jo As JavaObject
   jo.InitializeNewInstance("java.util.Date", Array(ticks))
   Return jo
End Sub

Thanks to Philip for the library and tutorial, rwblinn for his assistance and Erel for B4j

Hope this helps someone.
 
Last edited:

Philip Chatzigeorgiadis

Active Member
Licensed User
Hi All,

I have added an updated tutorial that uses NextReports 9.1 and Mysql as the data source.
All the additional jar files can be obtained from the NextReports web site. http://www.next-reports.com/download.html

The NextReports 9.1 Designer, Engine and Server are available "free" and works quite well for my application which is mostly simple reports with date ranges and totals.

I have a menu in my app to select the required report and input parameters, then present the report in a PDF reader for viewing or printing.
Create the reports using the designer and copy them from the "c:/users/user_name/.nextreports-9.1/output/database_name/reports/report_name.report" to your
app/objects/reports folder or where ever you want to place them.

B4X:
#Region Project Attributes
    #MainFormWidth: 600
    #MainFormHeight: 400
' 
    'This jar file must be present in the B4J Additional Libraries folder
    '#AdditionalJar:nextreports-engine-8.1
    #AdditionalJar:nextreports-engine-9.1
' 
    'Don't forget to add jNxtReportsB4J in the B4J IDE Libraries tab
' 
'    Download these jar files to the B4J Libraries folder
'    All these jar files are available from http://www.next-reports.com/download.html
    #AdditionalJar:commons-jexl-2.1.1
    #AdditionalJar:commons-logging-1.1.1
    #AdditionalJar:itext-2.1.7
    #AdditionalJar:itext-rtf-2.1.7
    #AdditionalJar:itextpdf-5.0.6
    #AdditionalJar:xstream-1.3.1
    #AdditionalJar:xstream-1.4.7
    #AdditionalJar:poi-3.7
' 
    'replace this by your applicable database jar file
    #AdditionalJar:mysql-connector-java-5.1.39
    '#AdditionalJar:mysql-connector-java-5.1.27.jar
'     
#End Region

#Region Notes
'    Updated 20-10-2016 - atiaust
'
'    This demo was adapted from one posted by Philip Chatzigeorgiodis with his library jNxtReportsB4J
'    This works with NextReports designer and Engine Version 9.1 - see Philips Tutorial for install instructions
'    This is a UI demo using Datepickers for DateFrom and DateTo selection criteria
'
'    The report was generated using the NextReports Report Designer and adding "Between ${start_date} And ${end_date}" as the
'    selection criteria in the query
'
#End Region

Sub Process_Globals
    Private fx As JFX
    Private MainForm As Form
    Private repexec As jNxtReportsB4J        ' jNxtReportsB4J library from B4x.com
'    form for testing
    Private btnOK As Button
    Private dpDateFrom As DatePicker
    Private dpDateTo As DatePicker
'
End Sub

Sub AppStart (Form1 As Form, Args() As String)
    MainForm = Form1
    MainForm.RootPane.LoadLayout("frmMain") 'Load the layout file.
    MainForm.Show
'
End Sub

Sub btnOK_MouseClicked (EventData As MouseEvent)
        'File separator character is set, according to the OS detected.
    Dim os As String = GetSystemProperty("os.name", "").ToLowerCase
    Dim filesep As String
    If os.Contains("win") Then 
          filesep="\"
    Else If os.Contains("mac") Then
          filesep="/"
    End If 
'
    Dim inputlist As List
    Dim inlocation,repin, repout, reptype, driverclass, url As String
' 
    'Location of the *.report file and any necessary images
    inlocation=File.DirApp & filesep & "reports" 'For this demo the report is stored in the Objects/reports directory. You can specify any other directory.
    'name of report created by NextReports.
    repin="Test" ' Report specified: Report_name.report - No need for the .report extension
' 
    'Output file specification
    repout="MYOUTPUT" 'name of output report
    reptype="PDF"  ' Available options: CSV, TXT, XLS, XML, PDF, HTML or RTF. File type must be in CAPITALS.
    'Output file will be MYOUTPUT.RTF. Output file (and any necessary images) will be stored in your application directory

'    logs the location of the report file
    LogDebug("Location = "&inlocation)
    'replace this by your database driver class name
    driverclass="com.mysql.jdbc.Driver"            '    driver class for Mysql
' 
    'replace this by applicable url for your database
    'I use IP instead of server name. For some reason, in my tests with SQL SERVER, when I used server name, it would not not work on MACs
    'jdbc:mysql://localhost:3306/HerongDB?user=Herong&password=TopSecret
    url="jdbc:mysql://192.168.0.1:3306/database_name?user=db_user&password=user_pw"
    'Initialize input list and add necessary arguments in the order shown below
    inputlist.initialize
    inputlist.Add(inlocation)
    inputlist.add(repin)
    inputlist.add(repout)
    inputlist.add(reptype)
    inputlist.add(driverclass)
    inputlist.add(url)
 
'    Now add your parameters - if any
'    First, the parameter name - as it was defined in NextReports ie.
'    add compare field in Report Designer ${surname} 
'    Then add your parameter value - see examples below...
'
'    inputlist.add("surname")
'    inputlist.add("xxxxx")
'
'    for dates use dates from DatePicker inputs on the B4j UI
' 
    Dim t As Long
'
    t = dpDateFrom.DateTicks
    inputlist.add("start_date")
    inputlist.add(TicksToDate(t))
' 
    t = dpDateTo.DateTicks
    inputlist.add("end_date")
    inputlist.add(TicksToDate(t))
' 
'    you can add as many parameters as necessary
'
Try
    'Run the report and export the output report file
    repexec.reportPrepAndRun(inputlist)
Catch
    LogDebug("Error: Loading Reports : " & LastException.Message)
End Try 
    'Show the output report file
    fx.ShowExternalDocument(File.GetUri(File.DIRAPP, repout & "." & reptype))
' 
End Sub

Thanks to Philip for the library and tutorial, rwblinn for his assistance and Erel for B4j

Hope this helps someone.

I had not tried the library with Date parameters - so thanks for your clarification.
It's also good to see that everything works with NextReports 9.1 and MySql!
Please verify that the TicksToDate function can be found here:
https://www.b4x.com/android/forum/threads/java-util-date-error.72261/#content
 
Last edited:

atiaust

Active Member
Licensed User
Hi Philip,

That is correct. For some reason the tickstodate sub didn't copy.
I will correct this and add to my post.

Thanks
 

Juan Marrero

Active Member
Licensed User
Hi All,

I have added an updated tutorial that uses NextReports 9.1 and Mysql as the data source.
All the additional jar files can be obtained from the NextReports web site. http://www.next-reports.com/download.html

The NextReports 9.1 Designer, Engine and Server are available "free" and works quite well for my application which is mostly simple reports with date ranges and totals.

I have a menu in my app to select the required report and input parameters, then present the report in a PDF reader for viewing or printing.
Create the reports using the designer and copy them from the "c:/users/user_name/.nextreports-9.1/output/database_name/reports/report_name.report" to your
app/objects/reports folder or where ever you want to place them.

Did you encountered an error of Permission Denied? I get this when using your code applied to my database: java.sql.SQLException: Access denied for user 'root'@'localhost' (using password: YES)
NOTE: I have tried with 3 different users, all with full GRANT OPTIONS.
 

atiaust

Active Member
Licensed User
Hi Juan,

The answer to your question s 'YES' I have had that error.

You may find that the "bind.address" statement in the my.cnf file is only allowing logins on the 'localhost' and not allowing the Mysql connector to log in remote.

On my server I have 'bind.address = 0.0.0.0'

Hope this helps.
 

Juan Marrero

Active Member
Licensed User
Strange. I already have 'bind-address = 0.0.0.0' at the end of my.ini (in my case) file because I didn't have connection from any other PC in the same network to the server and that bind-address setting solved it. I guess I'm missing another security setting. I'm used to Microsoft SQL Server, I'm practically starting to understand MySQL. From Next Reports Designer connects perfectly and display the data perfectly.
 
Last edited:

Juan Marrero

Active Member
Licensed User
this is what i get when using this library, mssql and java 8.
B4X:
Waiting for debugger to connect...
Program started.
Aug 15, 2017 1:37:32 PM ro.nextreports.engine.querybuilder.sql.dialect.DialectFactory addDialect
INFO: Dialect added: Oracle = ro.nextreports.engine.querybuilder.sql.dialect.OracleDialect
Aug 15, 2017 1:37:32 PM ro.nextreports.engine.querybuilder.sql.dialect.DialectFactory addDialect
INFO: Dialect added: Adaptive Server Anywhere = ro.nextreports.engine.querybuilder.sql.dialect.MSSQLDialect
Aug 15, 2017 1:37:32 PM ro.nextreports.engine.querybuilder.sql.dialect.DialectFactory addDialect
INFO: Dialect added: Microsoft SQL Server = ro.nextreports.engine.querybuilder.sql.dialect.MSSQLDialect
Aug 15, 2017 1:37:32 PM ro.nextreports.engine.querybuilder.sql.dialect.DialectFactory addDialect
INFO: Dialect added: MySQL = ro.nextreports.engine.querybuilder.sql.dialect.MySQLDialect
Aug 15, 2017 1:37:32 PM ro.nextreports.engine.querybuilder.sql.dialect.DialectFactory addDialect
INFO: Dialect added: Apache Derby = ro.nextreports.engine.querybuilder.sql.dialect.DerbyDialect
Aug 15, 2017 1:37:32 PM ro.nextreports.engine.querybuilder.sql.dialect.DialectFactory addDialect
INFO: Dialect added: PostgreSQL = ro.nextreports.engine.querybuilder.sql.dialect.PostrgreSQLDialect
Aug 15, 2017 1:37:32 PM ro.nextreports.engine.querybuilder.sql.dialect.DialectFactory addDialect
INFO: Dialect added: SQLite = ro.nextreports.engine.querybuilder.sql.dialect.SQLiteDialect
Aug 15, 2017 1:37:32 PM ro.nextreports.engine.querybuilder.sql.dialect.DialectFactory addDialect
INFO: Dialect added: CsvJdbc = ro.nextreports.engine.querybuilder.sql.dialect.CSVDialect
Aug 15, 2017 1:37:32 PM ro.nextreports.engine.querybuilder.sql.dialect.DialectFactory addDialect
INFO: Dialect added: Vertica Database = ro.nextreports.engine.querybuilder.sql.dialect.VerticaDialect
Aug 15, 2017 1:37:32 PM ro.nextreports.engine.querybuilder.sql.dialect.DialectFactory addDialect
INFO: Dialect added: Pervasive.SQL = ro.nextreports.engine.querybuilder.sql.dialect.PervasiveDialect
Aug 15, 2017 1:37:32 PM ro.nextreports.engine.querybuilder.sql.dialect.DialectFactory addDialect
INFO: Dialect added: Teradata = ro.nextreports.engine.querybuilder.sql.dialect.TeradataDialect
Aug 15, 2017 1:37:32 PM ro.nextreports.engine.querybuilder.sql.dialect.DialectFactory addDialect
INFO: Dialect added: Ucanaccess = ro.nextreports.engine.querybuilder.sql.dialect.MSAccessDialect
Aug 15, 2017 1:37:32 PM ro.nextreports.engine.querybuilder.sql.dialect.AbstractDialect <init>
INFO: Using dialect: ro.nextreports.engine.querybuilder.sql.dialect.MSSQLDialect@7c031263
java.io.FileNotFoundException:
   at java.io.FileOutputStream.open0(Native Method)
   at java.io.FileOutputStream.open(FileOutputStream.java:270)
   at java.io.FileOutputStream.<init>(FileOutputStream.java:213)
   at java.io.FileOutputStream.<init>(FileOutputStream.java:101)
   at ro.nextreports.integration.ReportExec.runReport(ReportExec.java:61)
   at ro.nextreports.integration.ReportExec.reportPrepAndRun(ReportExec.java:90)
   at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
   at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
   at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
   at java.lang.reflect.Method.invoke(Method.java:498)
   at anywheresoftware.b4a.shell.Shell.runVoidMethod(Shell.java:657)
   at anywheresoftware.b4a.shell.Shell.raiseEventImpl(Shell.java:234)
   at anywheresoftware.b4a.shell.Shell.raiseEvent(Shell.java:159)
   at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
   at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
   at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
   at java.lang.reflect.Method.invoke(Method.java:498)
   at anywheresoftware.b4a.BA.raiseEvent2(BA.java:90)
   at anywheresoftware.b4a.ShellBA.raiseEvent2(ShellBA.java:93)
   at anywheresoftware.b4a.BA$1.run(BA.java:215)
   at com.sun.javafx.application.PlatformImpl.lambda$null$173(PlatformImpl.java:295)
   at java.security.AccessController.doPrivileged(Native Method)
   at com.sun.javafx.application.PlatformImpl.lambda$runLater$174(PlatformImpl.java:294)
   at com.sun.glass.ui.InvokeLaterDispatcher$Future.run(InvokeLaterDispatcher.java:95)
   at com.sun.glass.ui.win.WinApplication._runLoop(Native Method)
   at com.sun.glass.ui.win.WinApplication.lambda$null$148(WinApplication.java:191)
   at java.lang.Thread.run(Thread.java:745)
Aug 15, 2017 1:40:22 PM ro.nextreports.engine.querybuilder.sql.dialect.AbstractDialect <init>
INFO: Using dialect: ro.nextreports.engine.querybuilder.sql.dialect.MSSQLDialect@12473b1c
java.io.FileNotFoundException:
   at java.io.FileOutputStream.open0(Native Method)
   at java.io.FileOutputStream.open(FileOutputStream.java:270)
   at java.io.FileOutputStream.<init>(FileOutputStream.java:213)
   at java.io.FileOutputStream.<init>(FileOutputStream.java:101)
   at ro.nextreports.integration.ReportExec.runReport(ReportExec.java:61)
   at ro.nextreports.integration.ReportExec.reportPrepAndRun(ReportExec.java:90)
   at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
   at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
   at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
   at java.lang.reflect.Method.invoke(Method.java:498)
   at anywheresoftware.b4a.shell.Shell.runVoidMethod(Shell.java:657)
   at anywheresoftware.b4a.shell.Shell.raiseEventImpl(Shell.java:234)
   at anywheresoftware.b4a.shell.Shell.raiseEvent(Shell.java:159)
   at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
   at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
   at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
   at java.lang.reflect.Method.invoke(Method.java:498)
   at anywheresoftware.b4a.BA.raiseEvent2(BA.java:90)
   at anywheresoftware.b4a.ShellBA.raiseEvent2(ShellBA.java:93)
   at anywheresoftware.b4a.BA$1.run(BA.java:215)
   at com.sun.javafx.application.PlatformImpl.lambda$null$173(PlatformImpl.java:295)
   at java.security.AccessController.doPrivileged(Native Method)
   at com.sun.javafx.application.PlatformImpl.lambda$runLater$174(PlatformImpl.java:294)
   at com.sun.glass.ui.InvokeLaterDispatcher$Future.run(InvokeLaterDispatcher.java:95)
   at com.sun.glass.ui.win.WinApplication._runLoop(Native Method)
   at com.sun.glass.ui.win.WinApplication.lambda$null$148(WinApplication.java:191)
   at java.lang.Thread.run(Thread.java:745)
I know the report is in the correct folder.
This is my code:
B4X:
Sub btnOK_Action
   Try
     Dim os As String = GetSystemProperty("os.name", "").ToLowerCase
     Dim filesep As String
     If os.Contains("win") Then
       filesep="\"
     Else
       filesep="/"
     End If
    
     If Not(File.Exists(File.DirApp & filesep & "reports", "")) Then
       File.MakeDir(File.DirApp, "reports")
     End If
    
     Dim inputlist As List
     Dim inlocation, repin, repout, reptype, driverclass, url As String
     Dim repexec As jNxtReportsB4J
    
     inlocation = File.DirApp & filesep & "reports"
     repin = "UsersByName"
     'repin = "Sales"
     'repin = "testCust"
     repout = "UsersByName"
     'repout = "Sales"
     'repout = "testCust"
     reptype = "HTML"
     driverclass = "net.sourceforge.jtds.jdbc.Driver"
     'driverclass="com.mysql.jdbc.Driver"
     url="jdbc:jtds:sqlserver://itprogrammer-pc:1433;databaseName=Membership;user=sa;password=xxxxxx"
     'url="jdbc:mysql://192.168.0.221:3306/unicentaopos?user=unicentaReports&password=mvp687en?autoReconnect=true&useSSL=false"
    
     inputlist.initialize
     inputlist.Add(inlocation)
     inputlist.add(repin)
     inputlist.add(repout)
     inputlist.add(reptype)
     inputlist.add(driverclass)
     inputlist.add(url)
     'inputlist.add("custid")
     'inputlist.add(5)
     repexec.reportPrepAndRun(inputlist)
    
     fx.ShowExternalDocument(File.GetUri(File.DirApp, repout & "." & reptype))
   Catch
     Log(LastException.Message)
   End Try
 
Last edited:

Juan Marrero

Active Member
Licensed User
Ok for whatever reason if I put "stops" in debug mode the code errors, but if I remove the "stops" the code works. Odd. Still got permission problems with MySQL.
 

atiaust

Active Member
Licensed User
Hi Juan,

I am using Java 8 and Mysql without any issues. Your code looks similar to mine and should work.

I was going to suggest using 'LogDebug("File Location = "&InLocation) to check the report location is correct.

My preference is to use 'LogDebug' or 'Log' instead of using 'stops'.
 

Juan Marrero

Active Member
Licensed User
Hi Juan,

I am using Java 8 and Mysql without any issues. Your code looks similar to mine and should work.

I was going to suggest using 'LogDebug("File Location = "&InLocation) to check the report location is correct.

My preference is to use 'LogDebug' or 'Log' instead of using 'stops'.

I use stops for knowing exactly at which line the code crashes. But for this library LogDebug is a most. Can you tell me how is your my.ini file configured for not giving you permission denied? Already have bind-address=0.0.0.0. I'm gonna try again without the stops to see what brings me.
 

Harris

Expert
Licensed User
Nice!

I have it working with ABMaterial as well. This example uses the start and end date params... NextReport 9.1...
Including all jars turned my 5 meg app jar into 12 meg. Nice to have the (small) engine included (as opposed to separate server - yet is available).
Beats the 900 meg of JasperSoft...
Now, to try this on my Ubuntu remote server...

pdfFolder = File.DirApp &"/www/"&AppName&"/rptout_"&Main.comp_id
repout=pdfFolder&"/"&fn 'name of output report

The output report folder was created during login for the site user.
It prepends the directory to the output report name.... Seems to work, however an Output Directory property would be nice, as well as output report name.

For complex reports that involves much data processing, I find it easier to create the report if you normalize the data first. Output processed data to a temp table for the report to use, where each column has the complex computed values. Then all you have to do is render column text.

This report's query in Next Reports... It get's funcky due to long type DateTime values sent by the android app to MySQL.
B4X:
SELECT
    e1.First_name,
    e1.Last_name,
    s1.start,
    s1.end,
    s1.worktype,
    FROM_UNIXTIME(s1.start / 1000) as SD,
    FROM_UNIXTIME(s1.end / 1000)   as ED,
    SUBTIME(    FROM_UNIXTIME(s1.end / 1000) , ( FROM_UNIXTIME(s1.start / 1000))  ) as ST
FROM
    emp e1,
    shifthist s1
WHERE
   e1.PK = s1.empid AND DATE(FROM_UNIXTIME(s1.start / 1000)) >= ${sdate} AND DATE(FROM_UNIXTIME(s1.start / 1000)) <= ${edate}
   ORDER BY e1.Last_name, e1.First_name


As a simple test example - On RUN REPORT, the pdf is loaded in ABMPDFViewer. From viwer, you can download or print.
pdf1.SetDocument("../rptout_"&Main.comp_id&"/"&name)

pdfview.jpg
 

Harris

Expert
Licensed User
Been screwing with this for some time...

Dim tlist as list
tlist.intilaize
tlist.addall('1054', '1056', '1057')

I need to add a list ('1054', '1056', '1057')
to a NextReport param called trkid
inputlist.add("trkid")
tlist.Add(tlist) ' this should be a objects of a list - tlist

This list will contain a value of -1 (meaning all ALL trkid values in the table)
or ONLY the values in the list (or one value).

example:

Next Reports SQL..

case when ${trkid} = -1 then eventmast.trkid > -1
else eventmast.trkid in (${trkid}) end

Maybe someone else has confronted this issue and has some light to shed.

Essentially,
I need to to select one or all items form a list (db table).
If param = -1 , then ALL items are used in query.
If param > 0, then ONLY this item is used.

Any suggestions will help..

Thanks
 
Top