B4J Library jCrystalReports Library

Juan Marrero

Active Member
Licensed User
Hi all.
I would like to share this jCrystalReports Library. (For now it supports MS SQL JTDS, MS SQL SQLJDBC, MySQL, Oracle and SQLite) I think I'm wrong, the java code in this library doesn't restrict to any jdbc connection and you can configure any jdbc in crystal reports as long is in the classpath (step 3). Please test everything.
Steps:
1. Copy all jdbc libraries to "C:\Program Files (x86)\Business Objects\Common\3.5\java\lib" (optional) or you just can specify jars' path explained in step 3.
2. Copy all crystal report jars to your B4j additional libraries folder. Link
3. In the CRConfig.xml you need to add the jars locations to <Classpath> under <DataDriverCommon> section using same format as all other entries (use \ and / in the path).
4. Also you need to change <JavaDir> from the dafault location to your Java JRE or JDK bin folder (it has to be the 32bit one). For example mine is "C:\Program Files (x86)\Java\jre1.8.0_181\bin". I found on the internet that the default java directory from crystal reports is not working.
5. In the CRConfig.xml configure <JDBC> section, modifying the following fields
<JDBCURL></JDBCURL>
<JDBCClassName></JDBCClassName>
<JDBCUserName></JDBCUserName>
with the required strings.
Example:
<JDBCURL>jdbc:sqlite:C:/YourPath/mydatabase.db</JDBCURL>
<JDBCClassName>org.sqlite.JDBC</JDBCClassName>
<JDBCUserName></JDBCUserName> This field is optional if no username is needed.
6. Create a Crystal Report using JDBC as your data connection. (If you can't find JDBC (JNDI) in your connections use your Crystal Reports installation program to add that feature).
7. Create a B4J project and add the following in the #Region Project Attributes:
#AdditionalJar: CrystalCommon2
#AdditionalJar: DatabaseConnectors
8. Open the example to fill all variables and launch the report.

I attached the library files, an example and a copy of my CRConfig.xml located in "C:\Program Files (x86)\Business Objects\Common\3.5\java" (could be "\4.0\java") as an example of my configuration.

v1.00 - Release.
v1.01 - Added methods PrintReport and ExportReport.
 

Attachments

Last edited:

Juan Marrero

Active Member
Licensed User
v1.01 - Added methods to print directly to a printer (PrintReport) and to export the report to PDF, XML, Excel, Word, etc..

PrintReport:
B4X:
Dim cr As CrystalReports
   
   cr.REPORT_NAME = "path to report"
   cr.USERNAME = "username"
   cr.PASSWORD = "password"
   cr.TRUSTED_CONN = "false" 'or "true"
   cr.CONN_URL = "jdbc:jtds:sqlserver://<servername>:<port>"
   cr.DB_NAME = "database name"
   cr.DB_CLASS_NAME = "net.sourceforge.jtds.jdbc.Driver"
   cr.SERVER_NAME = "server name"
   cr.SELECTION_FORMULA = ""
   
   Dim service As CR_PrintService
   
   cr.InitializePrintServices
   
   Dim printerList() As String = cr.GetPrintServices
   Dim printers As List
   Dim selPrinter As String
   
   printers.Initialize
   
   For i = 0 To printerList.Length - 1
       printers.Add(printerList(i))   
   Next
   
   Dim sel As Int = fx.InputList(MainForm, printers, "Select Printer:", "Printers", -1)
   
   If sel <> fx.DialogResponse.CANCEL Then
       selPrinter = printers.Get(sel)
   End If
   
   If selPrinter <> "" Then
       service = cr.SelectPrintService(cr.PrintServices, selPrinter)
       cr.PrintReport(service, cr.PAPER_LETTER, 1)
   Else
       cr.LaunchReport
   End If
ExportReport:
B4X:
Dim cr As CrystalReports
   
   cr.REPORT_NAME = "path to report"
   cr.USERNAME = "username"
   cr.PASSWORD = "password"
   cr.TRUSTED_CONN = "false" 'or "true"
   cr.CONN_URL = "jdbc:jtds:sqlserver://<servername>:<port>"
   cr.DB_NAME = "database name"
   cr.DB_CLASS_NAME = "net.sourceforge.jtds.jdbc.Driver"
   cr.SERVER_NAME = "server name"
   cr.SELECTION_FORMULA = "{Table.ID} = 10"
   
   Dim ans As Int = fx.Msgbox2(MainForm, "Export or View?", "Report", "Export", "Cancel", "View", fx.MSGBOX_CONFIRMATION)
   
   If ans = fx.DialogResponse.POSITIVE Then
       cr.ExportReport(cr.format_PDF, "C:\exportedreport.pdf")
   Else if fx.DialogResponse.NEGATIVE Then
       cr.LaunchReport
   End If
 

Juan Marrero

Active Member
Licensed User
should I install the crystal report first, if so, what version?
Will get an answer for you soon. For apps created in Visual Studio you have to install crystal reports engine 2008 (CRRedist2008_x86 or CRRedist2008_x64). Since this is using a Java SDK I'm not sure if you have to install an engine. Crystal Reports is not needed, just the engine.
 

roaddee

Member
i'm sorry, but i confused for step 5 (Create a Crystal Report using JDBC as your data connection. (If you can't find JDBC (JNDI) in your connections use your Crystal Reports installation program to add that feature). how can i do ? because i can find JDBC (JNDI) in my connections also in my cr xi setup/installation program
 

Juan Marrero

Active Member
Licensed User
i'm sorry, but i confused for step 5 (Create a Crystal Report using JDBC as your data connection. (If you can't find JDBC (JNDI) in your connections use your Crystal Reports installation program to add that feature). how can i do ? because i can find JDBC (JNDI) in my connections also in my cr xi setup/installation program
Oh. You need to run Crystal Reports installer (do a customized installation not a typical installation) and look for Data Access. Mark all database connections you need including JDBC. If Crystal Reports is installed, run the installer and click on Add Features. Hope it helps.
 

Juan Marrero

Active Member
Licensed User
i'm sorry. but mine like this (mybe my crystal report version dit not support jdbc)
View attachment 77180
I use the same version as you. Please review all the steps. I found the steps on the web when I wrote the library. I had to reinstall Windows on my PC so the link to the webpage was lost. Do a google search for "Adding JDBC to Crystal Reports" or something similar. I did the steps on my work PC and my personal laptop and both worked.
 

kelvo01

Member
Licensed User
I receive this error when I compile:


Error
Impossibile trovare: C:\Program Files (x86)\Anywhere Software\B4J\libraries\crystalreportsruntime.jar
(not found)

Any Idea where crystalreportsruntime.jar is?
 

kelvo01

Member
Licensed User
RESOLVED: I add the contenent of crjava-runtime_12.2.220.zip and itextpdf-5.5.0.jar jtds-1.3.1.jar mssql-jdbc-7.0.0.jre8.jar mysql-connector-java-5.1.40-bin.jar ojdbc7.jar sqlite-jdbc-3.23.1.jar
in libraries
 

kelvo01

Member
Licensed User
REQUEST: for v1.03


- add support for MDB thru
gSQL.Initialize("net.ucanaccess.jdbc.UcanaccessDriver", "jdbc:ucanaccess://" & gstrdbfile & ";memory=true")
'
 

Juan Marrero

Active Member
Licensed User
REQUEST: for v1.03


- add support for MDB thru
gSQL.Initialize("net.ucanaccess.jdbc.UcanaccessDriver", "jdbc:ucanaccess://" & gstrdbfile & ";memory=true")
'
Do you manage to create a Crystal Reports database connection with this driver?
 

kelvo01

Member
Licensed User
good question
in Crystal Reports how to configure/add a new database connection with this driver?
 

Juan Marrero

Active Member
Licensed User
good question
in Crystal Reports how to configure/add a new database connection with this driver?
Following the steps from first post (Steps 1-5). That's what I did with all the other jdbc drivers but with ucanaccess I'm getting a "Driver not found" error trying to create the jdbc connection in Crystal reports. I asked for help in a forum.
 
Top