Skip to main content
A
ashleyg1-Visitor
1-Visitor
August 5, 2016
Question

SQL IF EXISTS Command Error

  • August 5, 2016
  • 2 replies
  • 4391 views

Hi Guys

After doing much research I still cannot get this IF EXISTS sql command to work, the Thing that this SQL Command service runs from is connected to an SQL Database called ThingWorx and a table called CEN_ThingWorx:

Line 1:      Use ThingWorx;

Line 2:      IF EXISTS (SELECT * FROM CEN_ThingWorx WHERE Thing='TestThing')

Line 3:      BEGIN

Line 4:      UPDATE CEN_ThingWorx SET (StringColumn='newString') WHERE Thing='TestThing'

Line 5:      END

There is a column called StringColumn and there is a Thing column with a value 'TestThing'.

However I keep getting errors and any error checker I put this into online returns Line 2 being at fault but it could be other things I'm not sure can anyone help please?

2 replies

T
ttielebein5-Regular Member
5-Regular Member
August 5, 2016

Ashley,

What are the errors that you get? What is the template of the Thing in ThingWorx which has this code? If the database is external, to which type of database are you connecting (e.g. MySQL, MSSQL, etc)?

Tori

A
ashleyg1-VisitorAuthor
1-Visitor
August 5, 2016

Hi Tori,

From my understanding ThingWorx gives you barely any error reporting at all for SQL Commands? Nothing appears in the Script Log and the only error that appears when I execute the service is:

Unable to Invoke Service updateLastRunningValuesToDatabase on TimesheetDB : null

The ThingTemplate of the Thing that connects to the Database with this service on is a 'RemoteDatabase' ThingTemplate.

It is connected to a MSSQL Database.

Many Thanks

Ashley

    P
    PaiChung22-Sapphire I
    22-Sapphire I
    August 5, 2016

    Not an expert, but if you could perhaps create this as a procedure in the Database, execution probably will be easier.

      A
      AdamR14-Alexandrite
      14-Alexandrite
      August 5, 2016

      Not 100% sure why the IF EXISTS is not working.  Are you using an ADO or ODBC driver to connect?  If so, which one?  Occasionally some have funky quirks with syntax.  Alternatively you could try the following...

      SET NOCOUNT ON

      BEGIN

           DECLARE @Hold AS STRING;

           SET @Hold = '0';

           SELECT @Hold = '1' FROM CEN_ThingWorx WHERE Thing='TestThing'

           IF (@Hold = '1')

           BEGIN

                UPDATE CEN_ThingWorx

                SET StringColumn ='newString'

                WHERE Thing='TestThing'

           END

      END

      A
      ashleyg1-VisitorAuthor
      1-Visitor
      August 8, 2016

      Hi Adam

      I am using an OleDB Driver this is the connection string if it helps...

      "ConnectionType": "OleDb",

      "ConnectionString": "Provider=SQLNCLI11; Server=xxxx;Database=ThingWorx;User ID=xxxx;Pwd=xxxx; Trusted_Connection=false;",

      "AlwaysConnected": false,

      "QueryEnabled": true,

      "CommandEnabled": true,

      "CommandTimeout": 60

      Terms & ConditionsAccessibility statement