A published resource in TIBCO Data Virtualization can not be named as GROUP.

A published resource in TIBCO Data Virtualization can not be named as GROUP.

book

Article ID: KB0072442

calendar_today

Updated On:

Products Versions
TIBCO Data Virtualization All supported versions

Description

Below steps can be followed to simulate the behavior:

1. Publish a resource under the database service with the name 'Group'. E.g. publish ViewOrder (can be found under /shared/examples) with the name 'Group'.
2. Open CMD in windows and locate to <TDV_INSTALL_DIR>/apps/jdbc
3. Hit the command as below:
------------
JdbcSample.bat Test localhost 9251 admin <password> composite "SELECT * from Group"
------------
Observation: This runs into error and does not return any data.

ERROR:
------------
java.sql.SQLException: An exception occurred when executing the following query: "SELECT CompanyName from Group". Cause: Unable to parse query text: Incorrect syntax near "from", found "Group". On line 1, column 25. [parser-2904201] [Log ID: 266cff0f-ef41-4550-873a-49464310527c] [jdbc-1906510]
at cs.jdbc.driver.StatementImpl.execute(StatementImpl.java:458)
at JdbcSample.main(JdbcSample.java:58)
Caused by: java.sql.SQLException: Unable to parse query text: Incorrect syntax near "from", found "Group". On line 1, column 25. [parser-2904201] [Log ID: 266cff0f-ef41-4550-873a-49464310527c]
at cs.jdbc.driver.StatementImpl.execute(StatementImpl.java:454)
... 1 more

------------

Environment

All supported environment

Resolution

The error message "Incorrect syntax near "from", found "Group" is prompted because "Group" is a reserved keyword, and therefore it cannot be used as a table name. Also, Postgres does not accept a CREATE TABLE statement if the table name in the statement is "Group":

========================
C:\apps\tdv83\repository\bin\psql -h localhost -p 9528 -U tutorial -d orders
orders=> CREATE TABLE Group (pname INTEGER);

ERROR: syntax error at or near "Group"
LINE 1: CREATE TALBE Group (pname INTEGER); 
If the name is changed to "Group1", then the CREATE TABLE is accepted (since "Group1" is not a reserved keyword):

orders=> CREATE TABLE Group1 (pname INTEGER);
CREATE TABLE

orders=> create table Group1 (pname INTEGER);
CREATE TABLE orders=> select * from Group1;
 pname
-------
(0 rows)
========================

Since "Group" is a reserved keyword in Postgres, avoid using such convention while naming a resource.

Issue/Introduction

This article emphasizes the fact that a published resource in TIBCO Data Virtualization can not be named as GROUP. This article also gives reason as well.

Additional Information

https://www.eversql.com/sql-syntax-check-validator/
https://developer.mimer.com/sql-2016-validator/