I want to create a where clause for my sql query and use it to dynamically pull in data - how can this be done?

[djones]

I'm using Spotfire Analyst Cloud. The workflow I'm wanting is to bring in a shapefile - xmin, xmax, ymin, ymax are all created automatically when the shapefile is brought in. I want to use this data to bring in data from another sql connection. Something like:

select * from table where ?param

and have param be created using a data function. The value being: (longitude between xmin and xmax and Latitude between ymin and ymax)
 or (longitude between xmin1 and xmax1 and Latitude between ymin1 and ymax1)

I currently have a data function that generates this and stores as a document property that I pass to the param. The problem is the sql query seems to need something to evaluate, so I updated my query to be:

select * from table where longitude >= ?param

and my document property value is: xmin and longitude <= xmax and Latitude between ymin and ymax
 or (longitude between xmin1 and xmax1 and Latitude between ymin1 and ymax1)

My thought being that this would evaluate as:
select * from table where longitude >= xmin and longitude <= xmax and latitude between ymin and ymax  or (longitude between xmin1 and xmax1 and Latitude between ymin1 and ymax1)

This query works just fine in SQL Server, but not in Spotfire. Can anyone help me think through a better way to achieve what I'm trying to do? Bonus points if this connection can be internal instead of an external query...

Thanks!

[djones]

Okay it seems like I had an issue where Longitude is type float, but by creating the were clause, the param is a string. If I cast longitude to be type varchar, the query runs, but the where clause is not evaluated, so everything is brought in. If I cast Longitude in SQL Server, it evaluates correctly and the data brought in is limited. Any ideas for help on this?

[Olivier Keugue Tadaa]

Hi djones, can you see the SQL generated by Spotfire?
I fear that what you are trying to achieve (SQL injection) would not work for some obvious security reason. But please first check the query in the log files.

--- to enable logging in to the Analyst, go to the menu Help -> Support and Diagnostics and Logging -> Logging

Edited September 18 by Olivier Keugue Tadaa

[Olivier Keugue Tadaa]

I just tried and as expected, the generated SQL is: 

select * from table where longitude >= 'xmin and longitude <= xmax and Latitude between ymin and ymax 
 or (longitude between xmin1 and xmax1 and Latitude between ymin1 and ymax1)'

Note that your condition has been provided as a string (without any "evaluation")

This is how most systems protect themselves from potential SQL injection intrusions.

Let us see if there is another approach to sending dynamic filter expressions to your database.

 

[djones]

@Olivier 
Thanks for looking into this. I get the concern about sql injection. If there's some other way to achieve what I'm trying to do that would be awesome. The dataset I'm pulling from has a lot of data and this seems like the best approach to pull in only relevant data. It's a challenge because the data needed is based on the spatial information, not necessarily a unique identifier. I'm currently pulling in a large amount of data and then using marking in a map to pass unique identifiers back to the data connection to pull in what's needed, but if I'm able to skip the first part that would be great.

 

The other issue is I'm using Spotfire Analyst Cloud, so I can't use information links which may have been a viable solution? I'm also using a 3rd party sql connection, so I cannot use stored procedures to pass parameters either.

[Olivier Keugue Tadaa]

This could be possible with Information Services. However, since it is also protected from SQL Injections, one would need to write some extension based on the API, that implements the following interface

https://docs.tibco.com/pub/spotfire_server/14.4.0/doc/api/TIB_sfire_server_Information_Services_API_Reference/com/spotfire/ws/im/ds/sql/SQLRuntime.html

[djones]

Hm this is a bit outside of my knowledge to be honest. I'm not familiar with Information Services. I can research some to see if I can figure it out. Thanks for the helping! If Spotfire could make something like this possible natively that would be awesome,

[Olivier Keugue Tadaa]

Please submit your idea here https://ideas.spotfire.com/ideas

Meanwhile, I'd encourage you to work with your Spotfire contact to see how we can help you develop that specific custom extension.