SQL Server SELECT IF Exists Not working

[mnaeem]

Hi,

Any idea about why SELECT IF EXISTS command is not working.

Thank You!

Naeem

[raluca_edu]

Hi,

Syntax is not correct. Please check this link: https://stackoverflow.com/questions/7887011/how-to-drop-a-table-if-it-exists

Hope it helps,

Raluca Edu

[Constantine]

Hello Naeem,

You can't use [[abc]] as placeholders for table names, only for parameter values. It's a rather frequent question, here's another example: https://community.ptc.com/t5/ThingWorx-Developers/Problems-with-the-input-parameter-in-the-SQL-query-service/m-p/618832

Regards,
Constantine

[Constantine]

Just to be precise -- it is technically possible to use <<abc>>, which simply replaces strings in your SQL statement, so you can use it anywhere in your service (unlike [[abc]], which works as PreparedStatement parameters). However, this makes your service vulnerable to SQL Injection exploits (e.g. https://portswigger.net/web-security/sql-injection), so you should be very (VERY) careful with that. Make sure you only expose this service to System user and wrap it in another one, which thoroughly validates its parameters. If you don't do it -- somebody might be able to steal your credentials, drop the entire database, etc. etc.

/ Constantine

[mnaeem]

Thank You @Constantine and @raluca_edu ,

Still not able to drop the temporary table according to the link that @raluca_edu mentioned in her comment.

Moreover, according to @Constantine I'd like to avoid using query as a string.

Basically I've a query in which I'm using both DDL and DML functionality. Is there a way of implementing it.

I'd like to use the following single query which works fine, not in thingworx.

IF OBJECT_ID('tempdb..#RESULT_TABLE') IS NOT NULL DROP TABLE #RESULT_TABLE;

WITH T1 As
(
SELECT ............
WHERE ...............
AND ................
AND ...................
)
SELECT DISTINCT ..........
INTO #RESULT_TABLE
FROM .................
WHERE ..............
AND ....................
AND ..................;

WITH TO_DELETE AS
(
SELECT ...................
)
DELETE T1
FROM #RESULT_TABLE T1
INNER JOIN TO_DELETE T2 ON T1.ID  = T2.ID;

WITH valid AS
(
SELECT DISTINCT .............. FROM #RESULT_TABLE
)
SELECT SUM(Duration) tot_duration FROM valid;

DROP TABLE #RESULT_TABLE

Thank You!

Naeem

[Constantine]

Hello Naeem,

Keep in mind that those SQL services are executed through a JDBC driver, which has its own settings and probably behaves differently from whatever tool you're using to test your statements outside of ThingWorx. For instance, in your example you have multiple statements separated by semicolon. Some JDBC drivers don't allow this by default.

I would suggest splitting this service into a couple of services -- one which checks if the table exists, and another one that drops it (yes it's not transactional, but DDL is not transactional either). Also depending on your specific DB provider you might have some more convenient syntax for doing it. For example, SQL Server has "DROP TABLE IF EXISTS", which doesn't require a SELECT.

/ Constantine

[mnaeem]

Thank You @Constantine ,

Yes exactly what I was trying to do to split the query, but I'm not able to drop a temporary table from Thingworx.

I'm using SQL server 2014.

[Constantine]

I'm sorry, my knowledge of SQL Server expired many years ago, but here are few suggestions:

1. IF (OBJECT_ID('tempdb.dbo.#RESULT_TABLE') IS NOT NULL DROP TABLE tempdb.dbo.#RESULT_TABLE
2. IF (OBJECT_ID('tempdb.dbo.#RESULT_TABLE', 'U') IS NOT NULL DROP TABLE #RESULT_TABLE
3. IF (OBJECT_ID('tempdb..#RESULT_TABLE') IS NOT NULL DROP TABLE #RESULT_TABLE

(and their combinations, most probably (2) + (3)).

[mnaeem]

None of it worked.

Anyways thank you, Used another approach starting from the simple tables selected from database and doing the calculations on Thingworx.

Thank You!