SQL IF EXISTS Command Error

[ashleyg]

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?

[ttielebein]

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

[ashleyg]

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

[PaiChung]

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

[ttielebein]

This is a good idea from best practice perspective, also, so far as I know.

Ashley, I am going to look into this quickly and see if I can reproduce it. I'll reply back shortly

[ttielebein]

So I just tested this on a Database thing for MySQL, and the syntax is a bit different, but it does work. Here is the syntax in MySQL: select exists(select * from table_name WHERE field='field_value');

I found this syntax on stack overflow: Usage of MySQL's "IF EXISTS" - Stack Overflow

As far as I can tell, if exists... is valid syntax for MSSQL, so I am not sure what the problem is. You are right in saying that error message is largely unhelpful. I am going to find out if I can get a copy of MSSQL and test that as well.

[AdamR]

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

[ashleyg]

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

[ashleyg]

Also that code you provided doesn't seem to work either, starting to think it's my end and not the syntax that is at fault...