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: 465
  • crystalreportstest.zip
    2.3 KB · Views: 489
  • CRConfig.xml
    5.3 KB · Views: 582
  • jCrystalReports-1.01.zip
    30 KB · Views: 530
Last edited:

Juan Marrero

Active Member
Licensed User
Longtime User
This is what i got from the forum:
UCanAccess has dependencies - HSQLDB and Jackcess, with additional Jackcess dependencies - so the UCanAccess driver is more than just the "ucanaccess jar" file itself. See this answer for details on the other jar files you will need to make available to Crystal Reports if you want to use UCanAccess.
I added all jars to the classpath in CRConfig.xml file, still Driver Not Found error appeared. There is no parameter to add in the connection url to specify dependencies. Sadly all other drivers are paid drivers. I will still search info on this to see if I manage to get it working. For know I can recommend you to create a small sqlite database to dump the records you want to display on the report and use sqlite jdbc driver to configure the report.

The answer ("this answer" link) they gave me in the forum is the same info as in the ucanaccess website.
 

kelvo01

Member
Licensed User
Longtime User
I use:
ucanaccess-4.0.4.jar
hsqldb.jar
commons-lang-2.6.jar
commons-logging-1.1.3.jar

maybe jackcess-2.1.11.jar

try this
 

Juan Marrero

Active Member
Licensed User
Longtime User
I use:
ucanaccess-4.0.4.jar
hsqldb.jar
commons-lang-2.6.jar
commons-logging-1.1.3.jar

maybe jackcess-2.1.11.jar

try this
I added all of those jar files to the CRConfig.xml and added to the C:\Program Files (x86)\Business Objects\Common\3.5\java\lib folder and nothing. I think the problem lies in specifying the dependencies when creating the jdbc connection inside crystal reports, I haven't found a way to doing it (if there is any). Maybe If the ucanaccess jar file didn't need any dependencies it would've worked. I did used the bat file and I did connected to a access database and managed to do some queries, but from crystal or jasper reports it doesn't seem o work. Maybe or possibly I'm doing something wrong. I found on another database (DB2 i think) that needed a secondary jar file for it to work and it has to be added to the classpath and it may work in java coding and possibly it will work in B4J if I find a way to use UCanAccess with Crystal Reports. My issue right know is Crystal Reports (And Jasper Reports), not B4J. That's what I want to solve.
 

kelvo01

Member
Licensed User
Longtime User
In B4J ucanaccess work i just add
#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.jar
to read and write mdb files

In B4J your crystal report library library work.

In crystal report designer i modify C:\Program Files (x86)\Business Objects\Common\3.5\java\lib CRConfig as you write
i modify java path but whend i add jodbc source sqlite i write url and driver but i receive not found ...
 

Juan Marrero

Active Member
Licensed User
Longtime User
In B4J ucanaccess work i just add
#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.jar
to read and write mdb files

In B4J your crystal report library library work.

In crystal report designer i modify C:\Program Files (x86)\Business Objects\Common\3.5\java\lib CRConfig as you write
i modify java path but whend i add jodbc source sqlite i write url and driver but i receive not found ...
Exactly. That's what I'm trying to solve: Driver not found.
 

kelvo01

Member
Licensed User
Longtime User
But in crystal report designer with Sqllite data source you create a report?

I modify C:\Program Files (x86)\Business Objects\Common\3.5\java\lib CRConfig as you wrote
I modify java path but whend i add jdbc source sqlite i write url and driver but i receive not found ...
There il a log file in crystal report ?
 

Juan Marrero

Active Member
Licensed User
Longtime User
But in crystal report designer with Sqllite data source you create a report?

I modify C:\Program Files (x86)\Business Objects\Common\3.5\java\lib CRConfig as you wrote
I modify java path but whend i add jdbc source sqlite i write url and driver but i receive not found ...
There il a log file in crystal report ?
In the CRConfig did you use back slash and forward slash in the path? \ and /?
Did you copied the jar files into C:\Program Files (x86)\Business Objects\Common\3.5\java\lib?
If yes to both I forgot 1 step.
This is my fault completely. I ommited an important step. In the CRConfig file find <JDBC> section. Then look for the examples in the CRConfig file I uploaded in first post. You have to modify <JDBCURL> (mandatory), <JDBCClassName> (mandatory) and <JDBCUserName> (if needed). I was getting an error when using sqlite and I went to the file and saw the examples. Mine was configured for MS SQL. I changed it to SQLite and connected to my sqlite database. Please try this.
Having this in mind I will try again with UCanAccess. Will keep you informed.
 

kelvo01

Member
Licensed User
Longtime User
my file for Sqlite: C:\Program Files (x86)\Business Objects\Common\3.5\java\CRConfig.xml

B4X:
<?xml version="1.0" encoding="utf-8"?><CrystalReportEngine-configuration>
    <reportlocation>../..</reportlocation>
    <timeout>10</timeout>
    
    <ExternalFunctionLibraryClassNames>
        <classname> </classname>
        <classname> </classname>
    </ExternalFunctionLibraryClassNames>
    
<keycode>B6W60-01CS200-000G970-0YA1</keycode>
<Javaserver-configuration>
<DataDriverCommon>
    <!-- <JavaDir>C:\Program Files (x86)\Business Objects\j2sdk1.4.2_08\bin</JavaDir> -->
    <JavaDir>C:\Program Files (x86)\Java\jre1.8.0_161\bin</JavaDir>
    <Classpath>C:\Program Files (x86)\Business Objects\Common\3.5\java/lib/crlovmanifest.jar;C:\Program Files (x86)\Business Objects\Common\3.5\java/lib/CRLOVExternal.jar;C:\Program Files (x86)\Business Objects\Common\3.5\java/lib/CRDBJavaServerCommon.jar;C:\Program Files (x86)\Business Objects\Common\3.5\java/lib/CRDBJavaServer.jar;C:\Program Files (x86)\Business Objects\Common\3.5\java/lib/CRDBJDBCServer.jar;C:\Program Files (x86)\Business Objects\Common\3.5\java/lib/CRDBXMLServer.jar;C:\Program Files (x86)\Business Objects\Common\3.5\java/lib/CRDBJavaBeansServer.jar;C:\Program Files (x86)\Business Objects\Common\3.5\java/lib/external/CRDBXMLExternal.jar;C:\Program Files (x86)\Business Objects\Common\3.5\java/lib/external/log4j.jar;C:\Program Files (x86)\Business Objects\Common\3.5\java/lib/cecore.jar;C:\Program Files (x86)\Business Objects\Common\3.5\java/lib/celib.jar;C:\Program Files (x86)\Business Objects\Common\3.5\java/lib/ebus405.jar;C:\Program Files (x86)\Business Objects\Common\3.5\java/lib/corbaidl.jar;C:\Program Files (x86)\Business Objects\Common\3.5\java/lib/external/freessl201.jar;C:\Program Files (x86)\Business Objects\Common\3.5\java/lib/external/asn1.jar;C:\Program Files (x86)\Business Objects\Common\3.5\java/lib/external/certj.jar;C:\Program Files (x86)\Business Objects\Common\3.5\java/lib/external/jsafe.jar;C:\Program Files (x86)\Business Objects\Common\3.5\java/lib/external/sslj.jar;"C:\Program Files (x86)\Business Objects\Common\3.5\java/lib/mysql-connector-java-5.1.40-bin.jar";"C:\Program Files (x86)\Business Objects\Common\3.5\java/lib/jtds-1.3.1.jar";"C:\Program Files (x86)\Business Objects\Common\3.5\java/lib/ojdbc7.jar";"C:\Program Files (x86)\Business Objects\Common\3.5\java/lib/mssql-jdbc-7.0.0.jre8.jar";C:\Program Files (x86)\Business Objects\Common\3.5\java/lib/sqlite-jdbc-3.23.1;${CLASSPATH}</Classpath>
    <IORFileLocation>${TEMP}</IORFileLocation>
    <JavaServerTimeout>1800</JavaServerTimeout>
    <JavaServerStartupTimeout>30</JavaServerStartupTimeout>
    <JVMMaxHeap>64000000</JVMMaxHeap>
    <JVMMinHeap>32000000</JVMMinHeap>
    <NumberOfThreads>100</NumberOfThreads>
</DataDriverCommon>
<JDBC>
    <CacheRowSetSize>100</CacheRowSetSize>
    <JDBCURL>jdbc:sqlite:C:\sqlite\db\sqlite.db</JDBCURL>
    <JDBCClassName>org.sqlite.JDBC</JDBCClassName>
    <JDBCUserName></JDBCUserName>
    

    <!-- ORACLE
    <JDBCURL>jdbc:oracle:thin:@<server-name>:[port]:<service-name></JDBCURL>
    <JDBCClassName>oracle.jdbc.driver.OracleDriver</JDBCClassName>
    <JDBCUserName>sa</JDBCUserName> -->
    <!-- MYSQL
    <JDBCURL>jdbc:mysql://<server_name>:[port]/<database_name></JDBCURL>
    <JDBCClassName>com.mysql.jdbc.Driver</JDBCClassName>
    <JDBCUserName>sa</JDBCUserName> -->
    <!-- MS SQL
    <JDBCURL>jdbc:sqlserver://localhost:1433;databaseName=<database_name></JDBCURL>
    <JDBCClassName>com.microsoft.sqlserver.jdbc.SQLServerDriver</JDBCClassName>
    <JDBCUserName>sa</JDBCUserName> -->
    <!-- MS SQL WITH INSTANCE
    <JDBCURL>jdbc:sqlserver://[serverName[\instanceName][:portNumber];databaseName=<database_name></JDBCURL>
    <JDBCClassName>com.microsoft.sqlserver.jdbc.SQLServerDriver</JDBCClassName>
    <JDBCUserName>sa</JDBCUserName> -->
    <!-- JTDS
    <JDBCURL>jdbc:jtds:sqlserver://localhost:1433/<database_name></JDBCURL>
    <JDBCClassName>net.sourceforge.jtds.jdbc.Driver</JDBCClassName>
    <JDBCUserName>sa</JDBCUserName> -->
    <!-- JTDS WITH INSTANCE
    <JDBCURL>jdbc:jtds:sqlserver://localhost:1433/<database_name>;instance=<INSTANCE></JDBCURL>
    <JDBCClassName>net.sourceforge.jtds.jdbc.Driver</JDBCClassName>
    <JDBCUserName>sa</JDBCUserName> -->
    <!-- SQLITE
    <JDBCURL>jdbc:sqlite:C:/sqlite/db/sqlite.db</JDBCURL>
    <JDBCClassName>org.sqlite.JDBC</JDBCClassName>
    <JDBCUserName>sa</JDBCUserName> -->
    <JNDIURL></JNDIURL>
    <JNDIConnectionFactory></JNDIConnectionFactory>
    <JNDIInitContext>/</JNDIInitContext>
    <JNDIUserName>weblogic</JNDIUserName>
    <GenericJDBCDriver>
        <Default>
            <ServerType>UNKNOWN</ServerType>
            <QuoteIdentifierOnOff>ON</QuoteIdentifierOnOff>
            <StoredProcType>Standard</StoredProcType>
            <LogonStyle>Standard</LogonStyle>
        </Default>
        <Sybase>
            <ServerType>SYBASE</ServerType>
            <QuoteIdentifierOnOff>OFF</QuoteIdentifierOnOff>
            <DriverClassName>com.sybase.jdbc2.jdbc.SybDriver</DriverClassName>
            <StoredProcType>Standard</StoredProcType>
            <LogonStyle>MySQL</LogonStyle>
        </Sybase>
    </GenericJDBCDriver>
</JDBC>
<XML>
    <CacheRowSetSize>100</CacheRowSetSize>
    <PreReadNBytes>4096</PreReadNBytes>
        <MaxCacheXMLSize>153600</MaxCacheXMLSize>
    <XMLLocalURL></XMLLocalURL>
    <SchemaLocalURL></SchemaLocalURL>
    <XMLHttpURL></XMLHttpURL>
    <SchemaHttpURL></SchemaHttpURL>
</XML>
<JavaBeans>
    <CacheRowSetSize>100</CacheRowSetSize>
    <JavaBeansClassPath></JavaBeansClassPath>
</JavaBeans>
</Javaserver-configuration>
</CrystalReportEngine-configuration>
 

kelvo01

Member
Licensed User
Longtime User
Thanks Juoan!
UPDATE:
- With Crystal Report Designer now Sqlite driver work, I create a report
- From B4J i compile and a demo that i write export the report to a pdf Wowk

problem was I don't add .jar extension in XML file ... :)
 

Juan Marrero

Active Member
Licensed User
Longtime User
Thanks Juoan!
UPDATE:
- With Crystal Report Designer now Sqlite driver work, I create a report
- From B4J i compile and a demo that i write export the report to a pdf Wowk

problem was I don't add .jar extension in XML file ... :)
GREAT!!!!
I will post here If I can make UCanAccess work.
 

kelvo01

Member
Licensed User
Longtime User
By the Way ... you post the code to print and export ... and to have a video preview?
 

Juan Marrero

Active Member
Licensed User
Longtime User
For Print use the PrintReport method. For Export use the ExportReport method. For Preview use the LaunchReport method.
 

Juan Marrero

Active Member
Licensed User
Longtime User
I solved the UCanAccess Driver Not Found error but know I'm stuck at Feature Not Supported error. Tried creating access database with .mdb and .accdb, setting username and password, running de security level wizard and always get the Feature Not Supported error. :(
 
Top