How to catch errors from named queries?

Ignition
1

I am building a recipe manager, and I am not sure this is the best way to do it, as far as data goes.

I've got a named query that takes a string argument, and query calls a stored procedure in SQL Server that returns a dataset based on the passed string.

The sproc takes the first four letters of the string, because the first four are standardized.
Here is an example list of recipes:
list = ['A111-100','A111-150','B211-100','B211-100','B211X-150']

The last one has an extra designator, and the three following digits represent the speed of the machine. So as we are able to increase the speed, the recipe must change to get the same specs on the product.

What I have created so far are a bunch of SQL views, that are named with everything but the speed, so if I were to look at the dataset for A111, I would get both the 100 and 150 speed recipes to look at. I didn't want to create a view for every possible speed we might run at, which could double or triple the number of SQL views.

This is my sproc, which selects which view to grab the data from based on the string passed from the named query:

CREATE PROCEDURE [dbo].[usp_SelectRecipeView]
	-- Add the parameters for the stored procedure here
	@recipe VARCHAR(20)

AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

	DECLARE @View nvarchar(30)
	DECLARE @Sql nvarchar(4000)
	DECLARE @recipe_Short nvarchar(7)

	--SET @Sql = N'SELECT * FROM Recipe.'
	SET @View = 'dbo.'
	SET  @recipe_Short = (SELECT LEFT(@recipe, CHARINDEX('-', @recipe) - 1))

    -- Insert statements for procedure here
	SET @View = @View + (SELECT v.name 
						FROM sys.views AS v	
						WHERE v.name LIKE '%' + @recipe_Short + '%')

	--SELECT @View

	SET @Sql = N'SELECT * FROM ' + @View
	
	--SELECT @Sql

	--EXECUTE sp_executesql @Sql, N'@View nvarchar(30)'
	EXEC (@Sql)

END
GO

I have not determined how to prevent more than one dataset being returned at the moment. For instance, if I pass B211, I would get B211 and B211X datasets.
For this issue, my current thought is creating an SQL view for each recipe...OR, perhaps I could check for an additional character (like X) just before the dash - ...

My questions are:

  1. Should I create an SQL view for each individual recipe?
  2. How can I catch an empty dataset so the user does not get the error dialog box? Some recipes do not have data yet...

Thanks.

2

How are you using/calling your named queries ?

3

Oh, custom prop binding on the root container in Vision.

prop1 gets the recipe, prop2 is bound to the query and takes prop1 as it's argument to pass to the query.

4

Why did you choose to use views?
Do you have many joins that make up the view?
Is the speed a column value in your recipe table?
I don’t understand why you could not just query the recipe table(s) WHERE recipes.name = ‘A111-150’ barring you have many relationships between recipes table and other tables.

On the other question, exceptions that are thrown by system.db.* calls are not Jython exceptions, but rather a Java Exception(Throwable). You would need to handle these separately in a separate except block in your try code.

from java.lang import Throwable
logger = system.util.getLogger('myLogger')

# corrected
try
    result = system.db.runQuery()
except Throwable as t:
    logger.warn('Exception', t)
5

That particular construct is terrible. It loses any nested causes and loses the backtrace. You should always hand throwables to a logger so the entire content is retained.

1 Like
6

My mistake Phil, I have corrected the code.

7

I am using views to create custom columns based on the recipe. For example, sometimes the first three columns are combined into one, sometimes the first six columns. There is one join.

The speed is a factor in the recipe setup.

The reason I do not query WHERE recipes.names = 'A111-150' is because the base recipe, in this case is A111, the speed will continue to change until we reach a speed ceiling, limited by equipment, technology, etc. So, I wanted to create a query (via the sproc) that would get the recipes for A111 with every speed included, in case the operators needed to search back for some setting.

I may be changing this setup, as I mentioned in the initial post, to create a view for each different recipe and speed. But, this also means there will be a lot of views, and by a lot I mean currently we would have about 30, and could end up with over 100. I suppose by then, the slower speed recipes could just be trimmed because they would probably have no relevance.

On exceptions: I will work with that and see what I can do.

Thanks.

8

Except that I am using a query binding, so I cannot use the script you provided. Nor does it look like I can use that in an Expression binding.

Edit: Unless, of course, I use scripting on the drop down box...

Edit 2: And Throwable is not defined error on the exception line.

Here's my code on the drop down component:

import java.lang.Throwable
logger = system.util.getLogger('myLogger')

value = event.source.selectedStringValue
query = 'Machine/Testing/GetRecipeView'
params = {'p_Recipe':value}
try:
	event.source.parent.initial_tbl_data = system.db.runNamedQuery(query, params)
except Throwable as t:
	logger.warn('Exception', t)

Edit 3: By changing the except line to this: except java.lang.Throwable as t:, I no longer get the error regarding Throwable, but I still get a dialog box with an error running query,

The statement did not return a result set.
	caused by Exception: com.microsoft.sqlserver.jdbc.SQLServerException: The statement did not return a result set.

So, is there a way to handle this and do something simple for the user that says, "Sorry, no data for this Recipe."?

9

If I may suggest a different method. Instead of storing the speed values with the recipe in the same table, store them separately in another table with a reference to the base recipe.
Your base recipe table should have a primary key column that uniquely identifies each recipe. The new table will also have a primary key column that uniquely identifies each speed parameter and its related recipe. Also include a ‘retired’ column that you set true/false when a speed is not used anymore or is considered ‘old’.

Now query the recipe speeds
select rp.speed from recipe_params rp where rp.recipe_id = (select r.recipe_id from recipe r where r.name = ‘A111’) and rp.retired = 0 —false
If you would like to return all of the retired speeds, simply remove the retired condition. If you always want to default to the last used or newest one, you can include a modified column with your speed params and change the query to do order by rp.modified desc.

The Throwable error you are getting is my fault, it should be from java.lang import Throwable. I corrected my earlier post.

As for notifying the user, it depends on your preference or what the users are accustomed to. Some show errors with a popup or an indicator/label.