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

Community Tip - Need to share some code when posting a question or reply? Make sure to use the "Insert code sample" menu option. Learn more! X

facing an error while executing postgresql services in thingworx 9.1

Ru
14-Alexandrite
14-Alexandrite

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

1 ACCEPTED SOLUTION

Accepted Solutions
tdixit
13-Aquamarine
(To:PaiChung)

Hello @Ru 

 

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

View solution in original post

4 REPLIES 4
PaiChung
22-Sapphire I
(To:Ru)

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.

tdixit
13-Aquamarine
(To:PaiChung)

Hello @Ru 

 

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

slangley
23-Emerald II
(To:Ru)

Hi @Ru.

 

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

slangley
23-Emerald II
(To:slangley)

Hi @Ru.

 

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

Top Tags