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?
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)?
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.
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
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.
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')
SET StringColumn ='newString'
I am using an OleDB Driver this is the connection string if it helps...
"ConnectionString": "Provider=SQLNCLI11; Server=xxxx;Database=ThingWorx;User ID=xxxx;Pwd=xxxx; Trusted_Connection=false;",