Support Article

Database-TableWithNoColumns on Postgres views

SA-36378

Summary



After instaling Project Management Framework (PMF) on a Postgres database; while using the application users were observing a number of the tabs were showing no records although records did exist.

At the same time the below error was being seen in the logs. The issue is common to all database views.


Error Messages



2017-04-10 11:35:25,363 [ myco-exec-3] [TABTHREAD0] [ ] [ mycoPMF:01.01.01] (sKeysExposed._baseclass.Action) ERROR custo-pmf-was.myco.| testuser - Error occured while chekcing if Property Exposed for property: , object class:Data-Assignment-AllAssignmentsDatabase-TableWithNoColumns data.PC_ALLASSIGNMENTS PegaDATA
com.pega.pegarules.pub.database.NonexistentTableException: Database-TableWithNoColumns data.PC_ALLASSIGNMENTS PegaDATA
From: (REQUESTOR)
at com.pega.pegarules.data.internal.access.TableInformationMapImpl.lookupColumnInfo(TableInformationMapImpl.java:638)
at com.pega.pegarules.data.internal.access.TableInformationMapImpl.lookupTableInfo(TableInformationMapImpl.java:476)
at com.pega.pegarules.data.internal.access.TableInformationMapImpl.get(TableInformationMapImpl.java:360)
at com.pega.pegarules.data.internal.access.TableInformationMapImpl.get(TableInformationMapImpl.java:322)
at com.pega.pegarules.data.internal.dictionary.ClassDefinitionImpl.isPropertyExposed(ClassDefinitionImpl.java:1178)
at com.pegarules.generated.activity.ra_action_pxcheckclasskeysexposed_63dd2545aea76632a557315fde5e2c03.step5_1_circum0(ra_action_pxcheckclasskeysexposed_63dd2545aea76632a557315fde5e2c03.java:594)
at com.pegarules.generated.activity.ra_action_pxcheckclasskeysexposed_63dd2545aea76632a557315fde5e2c03.step5_0_circum0_Embed_ClassKeys(ra_action_pxcheckclasskeysexposed_63dd2545aea76632a557315fde5e2c03.java:534)
at com.pegarules.generated.activity.ra_action_pxcheckclasskeysexposed_63dd2545aea76632a557315fde5e2c03.perform(ra_action_pxcheckclasskeysexposed_63dd2545aea76632a557315fde5e2c03.java:175)
at com.pega.pegarules.session.internal.mgmt.Executable.doActivity(Executable.java:3553)
at com.pega.pegarules.exec.internal.declare.infengine.pages.DeclarativePageDirectoryImpl.runRule(DeclarativePageDirectoryImpl.java:1053)
at com.pega.pegarules.exec.internal.declare.infengine.pages.DeclarativePageDirectoryImpl.runLoadActivity(DeclarativePageDirectoryImpl.java:446)
at com.pega.pegarules.session.internal.mgmt.base.handler.ReadOnlyDataPageHandler.loadDataPage(ReadOnlyDataPageHandler.java:241)
at com.pega.pegarules.session.internal.mgmt.base.handler.ReadOnlyDataPageHandler.findDataPage(ReadOnlyDataPageHandler.java:405)
at com.pega.pegarules.session.internal.mgmt.base.AbstractPageDirectory.findDataPageInCurrentDir(AbstractPageDirectory.java:939)
at com.pega.pegarules.session.internal.mgmt.base.AbstractPageDirectory.getDirectPage(AbstractPageDirectory.java:723)
at com.pega.pegarules.session.internal.mgmt.base.ThreadPageDir.getDirectPage(ThreadPageDir.java:471)
at com.pega.pegarules.session.internal.mgmt.base.ThreadPageDir.getPage(ThreadPageDir.java:429)


Steps to Reproduce



The Database-TableWithNoColumns can happen when the JDBC driver is unable to identify the columns associated with a table or view.

To prove whether this was the case a standalone class was built to query the view columns outside of Pega, with:

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Calendar;

public class GetPGColumns {

/**
* @param args
*/
public static void main(String[] args) {
GetPGColumns me = new GetPGColumns();
me.run();
}


public void run() {
Connection con = null;

try {
// Load the JDBC driver
logit("Begin");

// Create a connection to the database
String url = "jdbc:postgresql://DBSERVERNAME:5432/dbname";
String username = "username";
String password = "password";
String databaseName = "postgres";
String dataSchema = "pega722data";
String tableName = "pc_allassignments";

System.out.println("Connecting via JDBC");
con = DriverManager.getConnection(url, username, password);

// try to replicate the getColumns call
logit("Getting columns");
ResultSet cols;
cols = con.getMetaData().getColumns(databaseName, dataSchema , tableName, "%");
logit("Columns retrieved");



// Loop through the results and display the column name
// NOTE: If more columns are needed please refer to

System.out.println("COLUMNS FOR " + tableName + " : -");
while (cols.next()) {
System.out.println(cols.getString("column_name"));
}


System.out.println("=====================");

} catch (SQLException e) {
e.printStackTrace();
} catch (Exception e) {
e.printStackTrace();
}

}

public void logit(String logText) {
Calendar calendar = Calendar.getInstance();

String h = String.valueOf(calendar.get(Calendar.HOUR_OF_DAY));
String m = String.valueOf(calendar.get(Calendar.MINUTE));
String s = String.valueOf(calendar.get(Calendar.SECOND));
String mi = String.valueOf(calendar.get(Calendar.MILLISECOND));

/*
Date now = calendar.getTime();
System.out.println(System.currentTimeMillis());
*/

System.out.println(h+":"+m+":"+s+","+mi + " : " + logText);
}

}


The java source was first modified to include the correct credentials/details for the system.
Then this was compiled/run: -

@echo off
set JAVA_HOME="C:\Program Files\Java\jdk1.8.0_91"
set JDBC_DRIVER=C:\JDBC\postgresql-9.4-1204.jdbc42.jar

%JAVA_HOME%\bin\javac -cp .;%JDBC_DRIVER% GetPGColumns.java

%JAVA_HOME%\bin\java -cp .;%JDBC_DRIVER% GetPGColumns



Which produced the results:

C:\workspace\POJO\src>GetPGColumns_compileandrun.cmd
16:29:9,134 : Begin
Connecting via JDBC
16:29:9,516 : Getting columns
16:29:9,589 : Columns retrieved
COLUMNS FOR pc_allassignments : -
=====================

C:\workspace\POJO\src>




Root Cause



The standalone test showed that the call to getColumn was failing to return any columns for the view.

This was due to the use of the
postgresql-9.4-1204.jdbc42.jar database driver.

Resolution



After updating the Postgres database driver to postgresql-9.4-1212.jdbc42.jar the standalone test was successfully returns columns, and information was being displayed in the relevant tabs in PMF.

Published April 12, 2017 - Updated April 27, 2017

Have a question? Get answers now.

Visit the Collaboration Center to ask questions, engage in discussions, share ideas, and help others.