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.
-
Required File(s): pvjdbc2.jar - The JDBC driver is bundled with the Pervasive installation in files named
pvjdbc2.jar,jpscs.jar, andpvjdbc2x.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. -
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)
-
Download and install ZappySys ODBC PowerPack: Download ZappySys ODBC PowerPack and proceed to install it on your system.
-
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.
-
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
DEMODATAdatabase. -
Driver class: Set the driver class as follows.
com.pervasive.jdbc.v2.Driver
-
JDBC driver file(s): Specify the file path
pvjdbc2.jarfile. (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.
-
-
Go to the Preview Tab, expand the Table Dropdown, and select the desired table from which you want to read the data.
-
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
UBIGINTtoBIGINT.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)
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 ZappySys ODBC Drivers 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. If you encounter any challenges or have specific use cases, please contact our support team via chat or ticket.