8 Replies Latest reply on Jan 3, 2018 2:11 PM by ashleyg RSS
    ashleyg Apprentice

    SQL IF EXISTS Command Error

    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?

      • Re: SQL IF EXISTS Command Error
        ttielebein Collaborator



        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)?



          • Re: SQL IF EXISTS Command Error
            ashleyg Apprentice

            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



          • Re: SQL IF EXISTS Command Error
            adamr Creator

            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


                 DECLARE @Hold AS STRING;


                 SET @Hold = '0';

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


                 IF (@Hold = '1')


                      UPDATE CEN_ThingWorx

                      SET StringColumn ='newString'

                      WHERE Thing='TestThing'