Community Tip - Did you know you can set a signature that will be added to all your posts? Set it here! X
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?
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
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
Not an expert, but if you could perhaps create this as a procedure in the Database, execution probably will be easier.
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
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
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
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...