cancel
Showing results for 
Search instead for 
Did you mean: 
cancel
Showing results for 
Search instead for 
Did you mean: 

Community Tip - Need to share some code when posting a question or reply? Make sure to use the "Insert code sample" menu option. Learn more! X

SQL Server SELECT IF Exists Not working

mnaeem
14-Alexandrite

SQL Server SELECT IF Exists Not working

Hi,

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

mnaeem_0-1581071471271.png

Thank You!

Naeem

8 REPLIES 8
raluca_edu
17-Peridot
(To:mnaeem)

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

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

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
14-Alexandrite
(To:Constantine)

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.

mnaeem_0-1581411860226.png

 

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

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
14-Alexandrite
(To:Constantine)

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.

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
14-Alexandrite
(To:Constantine)

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!

Top Tags