cancel
Showing results for 
Search instead for 
Did you mean: 
cancel
Showing results for 
Search instead for 
Did you mean: 

facing an error while executing postgresql services in thingworx 9.1

122
Amethyst
Amethyst

facing an error while executing postgresql services in thingworx 9.1

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

3 REPLIES 3

Re: facing an error while executing postgresql services in thingworx 9.1

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.

Re: facing an error while executing postgresql services in thingworx 9.1

Hello @122 

 

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

Tags (1)

Re: facing an error while executing postgresql services in thingworx 9.1

Hi @122.

 

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

Announcements

Check out the upcoming Expert Session: Understanding ThingWorx Navigate Licensing in Community "Customer Events" section.