Way to do an overlay opt out on a SQL Binding in Vision 7.9.9?

Ignition
1

Using Ignition 7.9.9 and SQL Server.

I have a project that has many schemas, with many tables that are identical in name and shape, but each also has tables unique to them, and some tables are slightly different in between them.

We have a screen that is meant to work with all schemas. I have a custom property on a radio button that is a SQL Query that will expectedly will fail for some schemas, but in those cases the entire component is still red. I set a fallback value but I think that only works in the event that 0 rows are returned, not in the event the SQL fails.

I am using SQL Server and saw in my coworker resolve this by doing something like the following, except it is not working for me -

IF EXISTS(
	SELECT myColumn
	FROM {[Client]Schema}.someTableThatMayOrMayNotExists
	WHERE ID=1)
	Begin
		SELECT myColumn
		FROM {[Client]Schema}.someTableThatMayOrMayNotExists
		WHERE ID=1
	End
else
	Select 'None'

But this is not working as well. Any thoughts on how I can resolve the red overlay from a SQL binding that I expect to work in some occasions?

2

Found the right way to check if a table exists

IF (EXISTS (SELECT * 
                 FROM INFORMATION_SCHEMA.TABLES 
                 WHERE TABLE_SCHEMA = 'TheSchema' 
                 AND  TABLE_NAME = 'TheTable'))
BEGIN
    --Do Stuff
END

Ugly as all hell but it is what it is.

3

IMO, It’s cleaner than the first one. At least, as clean as Microsoft ever makes things…

1 Like
4

Yea both are ugly, at least the second one isn’t redundant and is a little clearer semantically. This project has been in production for very long, and a quick search revealed the second way is used all over the place, so thems the breaks for me.

The big ball of mud just got bigger.

1 Like
5

Information_Schema is part of the SQL standard. Microsoft effectively had to implement it.

1 Like