How to Connect Pervasive SQL Database using ODBC-JDBC Driver

Pervasive PSQL databases are widely used in various industries for their reliability and performance. To interact with these databases programmatically, utilizing an ODBC (Open Database Connectivity) JDBC (Java Database Connectivity) driver is a common approach. In this article, we’ll delve into the steps required to establish a connection to a PSQL using the ZappySys JDBC Bridge Driver, enabling seamless data access and manipulation.

Step-by-Step Guide

To connect to the Pervasive PSQL databases with ZappySys JDBC Bridge Driver

System Requirements:

Before we begin, it’s important to have Java Runtime Environment (JRE) or Java Development Kit (JDK) installed on your system. We suggest installing JRE8, but other versions may also suffice. There are multiple ways to obtain the JRE8 installer, but we recommend using one of the following methods. If you already have Java Runtime installed, you can skip the steps below.

  • Ensure you have Java 8 (or a newer version) installed on your system, either as Java Runtime Environment (JRE) or Java Development Kit (JDK). You can download the latest OpenJDK JRE 8 installer for Windows x64 from here.

  • You can obtain Java from various sources. For instance, you can download it from Oracle JAVA, use the OpenJDK distribution provided by Amazon, or get it from the OpenJDK distribution by Adoptium. Choose a source that suits your preferences and requirements.

  1. Required File(s): pvjdbc2.jar - The JDBC driver is bundled with the Pervasive installation in files named pvjdbc2.jar, jpscs.jar, and pvjdbc2x.jar. Depending on your Pervasive configuration, only the pvjdbc2.jar file may be required. You can locate these files in the directory where you installed the Pervasive PSQL database on the server.

    Platform File Types Default Installation Location
    Windows (64-bit) PSQL Application Data drive:\ProgramData\Actian\PSQL\
    Program Files (64-bit) drive:\Program Files\Actian\PSQL\
    Program Files (32-bit) drive:\Program Files (x86)\Actian\PSQL\
  2. Pervasive PSQL database Details. Gather database details such as the host, port, username, password, and database name. (username and password are optional if security is configured)

  3. Download and install ZappySys ODBC PowerPack: Download ZappySys ODBC PowerPack and proceed to install it on your system.

  4. Create ZappySys JDBC Bridge Driver: After installing JRE and ZappySys ODBC Driver, you can access the driver in various applications using the following methods:

    • Using ODBC DSN: Utilize either a User-level or System-level Data Source Name (DSN) to configure your connection.
    • Using Direct ODBC Connection String: Construct an ODBC connection string directly with the required parameters.
    • Using Data Gateway: Utilize the ZappySys Data Gateway to connect client apps or servers from Windows, Linux, or macOS to the ZappySys Data Gateway using Microsoft SQL Server Driver (e.g., JDBC, ODBC, OLEDB). Regardless of the method chosen for your app, the following steps will guide you in configuring your connectivity correctly.
  5. Configure the JDBC Bridge Driver: To set up a JDBC Bridge Driver for Pervasive, you need to go through these steps.

    • Connection string: Configure the connection string as follows:

      jdbc:pervasive://{machinename}:{portnumber}/{datasource}

      {machinename} is the host name or IP address of the machine that runs the PSQL DB Server.
      {portnumber} is the port on which the PSQL dB Server is listening. By default it is 1583.
      {datasource} is the name of the ODBC DSN on the PSQL database server that the application intends to use.

      e.g.

      jdbc:pervasive://localhost:1583/DEMODATA

      Here, we are using the DEMODATA database.

    • Driver class: Set the driver class as follows.

      com.pervasive.jdbc.v2.Driver

    • JDBC driver file(s): Specify the file path pvjdbc2.jar file. (jpscs.jar and pvjdbc2x.jar may also be needed)

      C:\Program Files (x86)\Actian\Zen\bin\pvjdbc2.jar

    • User Name & Password: Set the credentials for the Pervasive Database. By default, the master username is Master
      e.g.

      User name: Master
      Password: pa$$wrdEXAMPLEKE

    Click on “Test Connection”, and if the configuration is correct, you will receive a “Test Connection Successful” message.

  6. Go to the Preview Tab, expand the Table Dropdown, and select the desired table from which you want to read the data.

  7. That’s it! Click on the “Preview Data” button, and it will execute the select query call to load the data. If you prefer to use your own Pervasive DB query, you can input it directly into the query window. For example, you may need to cast some columns, as their data types are UBIGINT to BIGINT.

    SELECT 
      CAST(Student_ID as BIGINT) as Student_ID,
      Transaction_Number,
      Log,
      Amount_Owed,
      Amount_Paid,
      CAST(Registrar_ID as BIGINT) as Registrar_ID,
      Comments
    FROM Billing
    

Considerations

Error: Can’t serialize class java.math.BigInteger

If you encounter the following error:

Query execution failure: Row read error: JBR error: java.lang.IllegalArgumentException: Can't serialize class java.math.BigInteger
	at org.bson.BasicBSONEncoder.putNumber(BasicBSONEncoder.java:298)
	at org.bson.BasicBSONEncoder._putObjectField(BasicBSONEncoder.java:158)
	at org.bson.BasicBSONEncoder.putArray(BasicBSONEncoder.java:446)
	at org.bson.BasicBSONEncoder._putObjectField(BasicBSONEncoder.java:182)
	at org.bson.BasicBSONEncoder.putArray(BasicBSONEncoder.java:446)
	at org.bson.BasicBSONEncoder._putObjectField(BasicBSONEncoder.java:182)
	at org.bson.BasicBSONEncoder.putObject(BasicBSONEncoder.java:110)
	at org.bson.BasicBSONEncoder.encode(BasicBSONEncoder.java:53)
	at ClientHandler.dispatch(ClientHandler.java:193)
	at ClientHandler.lambda$0(ClientHandler.java:83)
	at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
	at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
	at java.lang.Thread.run(Thread.java:750)

In that case, you need to cast the UBIGINT to BIGINT using CAST, as shown below:

SELECT 
     CAST(Student_ID as BIGINT) as Student_ID,
FROM Billing

Error: JBR error: java.lang.IllegalArgumentException at java.sql.Time.getYear(Time.java:161)

If you encounter the following error:

Query execution failure: Row read error: JBR error: java.lang.IllegalArgumentException
	at java.sql.Time.getYear(Time.java:161)
	at DateObj.DateToTicks(DateObj.java:28)
	at DateObj.<init>(DateObj.java:13)
	at ClientHandler.getRows(ClientHandler.java:779)
	at ClientHandler.dispatch(ClientHandler.java:157)
	at ClientHandler.lambda$0(ClientHandler.java:83)
	at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
	at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
	at java.lang.Thread.run(Thread.java:750)

Error for Time DataType

In that case, you need to cast the TIME to VARCHAR(10) using CAST, as shown below:

SELECT 
  Start_Date,
  Cast(Start_Time as VARCHAR(10)) as Start_Time 
FROM Class

Apps Integration

You can use this JDBC Bridge connector to integrate JDBC-ODBC Bridge data inside apps like SSIS, SQL Server or popular ETL Platforms / BI Tools/ Reporting Apps / Programming languages (i.e. Informatica, Power BI, SSRS, Excel, C#, JAVA, Python)
Click Here for JDBC-ODBC Bridge Integration.

Conclusion

Establishing a connection to a Pervasive PSQL database using the ODBC JDBC driver is a straightforward process. By following the steps outlined in this guide and utilizing the provided code snippet, developers can seamlessly integrate Pervasive PSQL databases into their Java applications, enabling efficient data access and management.

References

Contact Us

If you have further questions, don’t hesitate to reach out to us. You can contact us through live chat or email us at support@zappysys.com. For additional support, visit our support page.