B4J Library jCrystalReports Library

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

  • jCrystalReports1.00.zip
    11.9 KB · Views: 722
  • crystalreportstest.zip
    2.3 KB · Views: 740
  • CRConfig.xml
    5.3 KB · Views: 841
  • jCrystalReports-1.01.zip
    30 KB · Views: 802
Last edited:

kelvo01

Member
Licensed User
Longtime User
I'm using Squirrel SQL to test the driver
I receive : mdb: net.ucanaccess.jdbc.FeatureNotSupportedException: Feature not supported.
I think is the combinatione of the jars not the good version

#AdditionalJar: ucanaccess-4.0.4.jar
#AdditionalJar: jackcess-2.1.11.jar
#AdditionalJar: hsqldb.jar
#AdditionalJar: commons-logging-1.1.3.jar
#AdditionalJar: commons-lang-2.6.ja
 

kelvo01

Member
Licensed User
Longtime User
now no error with this combination;
Immagine01.jpg
 

Juan Marrero

Active Member
Licensed User
Longtime User
The problem is between Crystal Reports and UcanAccess. Other ways to connect to the database work.
 

kelvo01

Member
Licensed User
Longtime User
Ucanaccess Works from crystal report designer and tested also now from B4j

in Crystal you must add all the jar to the xml , in B4J

#AdditionalJar: ucanaccess-4.0.4.jar
#AdditionalJar: jackcess-2.1.11.jar
#AdditionalJar: hsqldb-2.3.1.jar
#AdditionalJar: commons-logging-1.1.3.jar
#AdditionalJar: commons-lang-2.6.jar
add also
#MergeLibraries: False (load & compile more faster ...)


In crystal report you receive Feature not supported. and the fields are not showned (i belive the driver is not well designed ...) but works!
So you must add a "command" from visual database designer in my case "select contatto from clienti" i think for every field used
 

Juan Marrero

Active Member
Licensed User
Longtime User
Ucanaccess Works from crystal report designer and tested also now from B4j

in Crystal you must add all the jar to the xml , in B4J

#AdditionalJar: ucanaccess-4.0.4.jar
#AdditionalJar: jackcess-2.1.11.jar
#AdditionalJar: hsqldb-2.3.1.jar
#AdditionalJar: commons-logging-1.1.3.jar
#AdditionalJar: commons-lang-2.6.jar
add also
#MergeLibraries: False (load & compile more faster ...)


In crystal report you receive Feature not supported. and the fields are not showned (i belive the driver is not well designed ...) but works!
So you must add a "command" from visual database designer in my case "select contatto from clienti" i think for every field used

You solved the enigma!! I tried it with all your suggestions and it's working!! I only used the "command" once in my life and was for printing labels with a formula skipping positions and printing on the label position desired. Never thought of that in this case. Thanks kelvo01.
 

roberto64

Active Member
Licensed User
Longtime User
Hi, which version of the crystal report? where to find it? I have not installed visual studio in my pc.
regards
 

Juan Marrero

Active Member
Licensed User
Longtime User
Hi, which version of the crystal report? where to find it? I have not installed visual studio in my pc.
regards
The version I'm using is Crystal Reports XI R2 SP6, but I think any SP version from XI is usable. Also for the client PCs you need the latest Redistributable version of Crystal Reports. You can find it in SAP website.
 

roberto64

Active Member
Licensed User
Longtime User
Hello, the version I have istallatasap crystal reports 2013 support pack 6 ver. 14.1.6.1702, I did everything as described but not a function does not open the db Sqlite gives me an error, or put yours with CRconfig but nothing
regards
 

Juan Marrero

Active Member
Licensed User
Longtime User
Hello, the version I have istallatasap crystal reports 2013 support pack 6 ver. 14.1.6.1702, I did everything as described but not a function does not open the db Sqlite gives me an error, or put yours with CRconfig but nothing
regards
What error? Driver not found? Can you send me the CRConfig.xml? I think that in my CRConfig file the sqlite path is missing the .jar at the end. Also I haven't tested crystal reports 2013.
 

roberto64

Active Member
Licensed User
Longtime User
Good evening, I send you the CRConfig file, it gives me this error "Unable to open the JDBC drivers details connection not found", sorry that I could not answer you before because I had a commitment.
Greetings
 

Attachments

  • CRConfig.xml
    6.1 KB · Views: 318

Juan Marrero

Active Member
Licensed User
Longtime User
Good evening, I send you the CRConfig file, it gives me this error "Unable to open the JDBC drivers details connection not found", sorry that I could not answer you before because I had a commitment.
Greetings
It's OK. I've been busy this days too. Moved to a new house.
In this line: C:\Program Files (x86)\SAP BusinessObjects\SAP BusinessObjects Enterprise XI 4.0\java/lib/sqlite-jdbc-3.23.1;${CLASSPATH}</Classpath> the jdbc is missing the .jar at the end. That was my mistake when I uploaded the CRConfig.xml file the first time. Add the .jar extentions to sqlite-jdbc-3.23.1. Also write the path with forward slashes "/" in the jdbcurl line: (<JDBCURL>jdbc:sqlite:C:/sqlite/db/sqlite.db</JDBCURL>). Try this and let me know.
 

roberto64

Active Member
Licensed User
Longtime User
Hello, ok it works, now I do some tests, can you use the Crystal parameters?
thank you
 

roberto64

Active Member
Licensed User
Longtime User
Hi, give me this error 354553 [JavaFX Application Thread] ERROR com.businessobjects.reports.sdk.JRCCommunicationAdapter - detected an exception: Error loading JDBC driver. Could not find the class 'org.sqlite.JDB'. ",
B4X:
cr.REPORT_NAME = "C:/Users/apple/Desktop/roberto64-3/Files/Stprima.rpt"
    cr.USERNAME = "sa"
    cr.PASSWORD = ""
    cr.TRUSTED_CONN = "false" 'or "true"
    cr.CONN_URL = "dbc:sqlite:C:/Users/apple/Desktop/RubricaTel/Database/DbRubTel.db"
    cr.DB_NAME = "DbRubTel.db"
    cr.DB_CLASS_NAME = "org.sqlite.JDB"
    cr.SERVER_NAME = "org.sqlite.JDB"
    cr.SELECTION_FORMULA = ""
And create this connection?
regards
 

Juan Marrero

Active Member
Licensed User
Longtime User
Hi, give me this error 354553 [JavaFX Application Thread] ERROR com.businessobjects.reports.sdk.JRCCommunicationAdapter - detected an exception: Error loading JDBC driver. Could not find the class 'org.sqlite.JDB'. ",
B4X:
cr.REPORT_NAME = "C:/Users/apple/Desktop/roberto64-3/Files/Stprima.rpt"
    cr.USERNAME = "sa"
    cr.PASSWORD = ""
    cr.TRUSTED_CONN = "false" 'or "true"
    cr.CONN_URL = "dbc:sqlite:C:/Users/apple/Desktop/RubricaTel/Database/DbRubTel.db"
    cr.DB_NAME = "DbRubTel.db"
    cr.DB_CLASS_NAME = "org.sqlite.JDB"
    cr.SERVER_NAME = "org.sqlite.JDB"
    cr.SELECTION_FORMULA = ""
And create this connection?
regards
Classname is "org.sqlite.JDBC", you're missing a C after JDB.
 

roberto64

Active Member
Licensed User
Longtime User
Hello, now I get this error 95802 [JavaFX Application Thread] ERROR com.businessobjects.reports.sdk.JRCCommunicationAdapter - detected an exception: Unexpected error of the database connector "
regards
 

roberto64

Active Member
Licensed User
Longtime User
solved the first msg, now gives me another error, opens the press and then gives me an error "the size of the field can be a good thing -294.967, .294"
regards
 
Top