Thingworx 9.0 backups are not suits in Thingworx 9.1

[DT]

Hi Everyone,

I have used Thingworx 8.5 then I have shifted all my backups in thingworx 9.0 it's fine and there is no issue with any entity but now when I have shifted all the Thingworx 9.0 backups into Thingworx 9.1.

It totally strange, some widgets that I have used in thingworx 9.0 mashups are gone.

I have used PostgreSQL queries in Thingworx 9.0 but after shifting backups in Thingworx 9.1 it's not running I haven't modified any query after shifting as it is I have shifted.

a select query is running in the database thing but not running JOINs query in the database things

Spoiler

SELECT workout_type_id,workout_type FROM "ref_workout_type" ;

Like above query is running but not running below types query i.e JOINs

Spoiler

SELECT
mem.member_id,concat(us.first_name,' ',us.Last_name) as fullname,ci.city_name,us.is_active,mem.checkbox
FROM "tbl_members" AS mem
JOIN "tbl_users" AS us
ON mem.user_id = us.user_id
JOIN "tbl_user_info" AS ui
ON us.user_id = ui.user_id
JOIN "ref_cities" AS ci
ON ui.city_id = ci.city_id

error is below, I have used this same querry in PostgreSQL it is running but thingworx its NOT

Spoiler

Unable to Invoke Service getMemberForProgram on PostgreSQL_DB : Execute Query failed: org.postgresql.util.PSQLException: ERROR: syntax error at or near "AS" Position: 130

Is it thingworx version problem?

I don't know what happened and what I have to do next please suggest the way.

[suverma]

Hi @DT 

The error appears to be a syntax error only. Could you attach Thingworx logs here after replicating the issue again, to get more information.

You have also mentioned about some of the widget disappeared in your Composer, can you let me know more about those widget, if they were OOTB or custom widgets. 

Are you unable to search those widgets or it is possible that you have Clicked Yes on the pop-up message about replacement of Legacy Widget.

Regards,

Sudeepta

[DT]

No, I haven't accepted the popup about the replacement of Legacy Widget. I just took Thingworx9.0 backups into Thingworx 9.1 but SQL JOINS  queries not running on especially.

About syntax error, if a syntax error is there then how can the same query execute in PostgreSQL even in Thingworx 9.0

I have attached the log file pls check.

[c_lowy]

Hi @DT,

Regarding the failing SQL Query, I believe this is related to the following known issue : Line breaks in SQL Service (Database Thing) are not properly handled in ThingWorx 9.1.0.

Can you confirm if the query runs successfully when using one of the syntaxes mentioned in the resolution section is used ? (no line breaks)

Note that this bug has been corrected in upcoming ThingWorx 9.1.1.

[suverma]

Hi @DT 

Should you get the error resolved after following the suggestion provided by @c_lowy can you please mark the appropriate response as the Accepted Solution for the benefit of others with the same question.