Skip to main content
R
Ru_0114-Alexandrite
14-Alexandrite
February 15, 2021
Solved

facing an error while executing postgresql services in thingworx 9.1

  • February 15, 2021
  • 2 replies
  • 2265 views

hii

i have one database(POSTGRESQL) configure with thingworx 9.1.

i have created postgresql thing which contain services(postgresql queries) for eg:-

i have one postgresql query which gives me allmembers data but it shows an error 

"select ch.tcr_id,concat(us.first_name,' ',us.last_name) as member_name,
ch.user_id,ch.checkin_time,ch.checkout_time,ch.band_id,ch.checkbox
from "tbl_checkin_records" as ch
join "tbl_members" as mem
on ch.user_id = mem.user_id
join "tbl_users" as us
on mem.user_id = us.user_id order by ch.checkout_time desc;"        this is my query which gives me an following error:- Unable to Invoke Service getAllCheckinRecordsForMember on PostgreSQL_DB : Execute Query failed: org.postgresql.util.PSQLException: ERROR: syntax error at or near "as" Position: 165

But when i was using 

i have attached snapshots below for your better understanding

Best answer by tdixit

Hello @Ru_01 

 

Thank you for contacting PTC. As stated by @PaiChung  this is a known bug in ThingWorx 9.1 and will be fixed in ThingWorx 9.1.1

Recommendation is to upgrade to ThingWorx 9.1.1 patch version. You can follow this article CS182505  to apply patch.

But , ThingWorx 9.1.1 is not released yet ,workaround is to remove line breaks or add space before follow-up line

  • For example following query will return an error:

         

SELECT name 
FROM thing_model
  • Following syntaxes can be used instead:
SELECT name FROM thing_model

SELECT name 
 FROM thing_model

 

Please feel free to reach out to us in case of any question/concern

 

Thanks & Regards,

Toolika Dixit

2 replies

P
PaiChung22-Sapphire I
22-Sapphire I
February 15, 2021

You may be running into a known bug.

Please try adding an actual space between every statement vs. just using CR/LF

easy way to make sure you have done is to make it look like this

"select ch.tcr_id,concat(us.first_name,' ',us.last_name) as member_name,
 ch.user_id,ch.checkin_time,ch.checkout_time,ch.band_id,ch.checkbox
 from "tbl_checkin_records" as ch
 join "tbl_members" as mem
 on ch.user_id = mem.user_id
 join "tbl_users" as us
 on mem.user_id = us.user_id order by ch.checkout_time desc;"  

 

Not sure if you can tell but on each next line after your first I added a space in front.

T
tdixit5-Regular MemberAnswer
5-Regular Member
February 19, 2021

Hello @Ru_01 

 

Thank you for contacting PTC. As stated by @PaiChung  this is a known bug in ThingWorx 9.1 and will be fixed in ThingWorx 9.1.1

Recommendation is to upgrade to ThingWorx 9.1.1 patch version. You can follow this article CS182505  to apply patch.

But , ThingWorx 9.1.1 is not released yet ,workaround is to remove line breaks or add space before follow-up line

  • For example following query will return an error:

         

SELECT name 
FROM thing_model
  • Following syntaxes can be used instead:
SELECT name FROM thing_model

SELECT name 
 FROM thing_model

 

Please feel free to reach out to us in case of any question/concern

 

Thanks & Regards,

Toolika Dixit

S
slangleyCommunity Manager
Community Manager
February 26, 2021

Hi @Ru_01.

 

If you feel your question has been answered, please mark it as the Accepted Solution for the benefit of others with the same question.

 

Regards.

 

--Sharon

S
slangleyCommunity Manager
Community Manager
April 9, 2021

Hi @Ru_01.

 

ThingWorx 9.1.1 is now available for download on the support site.

 

If you feel your issue has been addressed, please mark the appropriate response as the Accepted Solution, for the benefit of others in the community.

 

Regards.

 

--Sharon

Terms & ConditionsCookie settingsAccessibility statement