8 Replies Latest reply on Aug 8, 2016 5:42 AM 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

        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

          • 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

             

            Ashley

          • 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

            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