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.

 

[kelvo01]

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

 

[Juan Marrero]

UcanAccess is definitely not build for crystal reports.

 

[kelvo01]

now no error with this combination;

 

[Juan Marrero]

The problem is between Crystal Reports and UcanAccess. Other ways to connect to the database work.

 

[kelvo01]

Ok Work in a strange way ...

 

[kelvo01]

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]

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.

 

[kelvo01]

Well done Juan! continue to implement the library

 

[roberto64]

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

 

[Juan Marrero]

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]

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]

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]

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

 

[Juan Marrero]

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]

Thanks I'll try tomorrow and let you know

 

[roberto64]

Hello, ok it works, now I do some tests, can you use the Crystal parameters?
thank you

 

[roberto64]

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'. ",

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]

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'. ",

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]

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]

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