Calling a Stored Procedure

General Discussion
,
1

Good afternoon Team,

I am back once again seeking knowledge. Take heart, I am close to being done and I am down to some final buttons.

My question: I created a button to call a named query. When I execute this button I get an error posted below.

image
image971×576 52.1 KB

Here is the code in the button, using an {onActionPerformed>Script}

lane = self.getSibling("Table_Accumulation_Lanes").props.selection.data[0].lane
	pallet = self.getSibling("Table_Palletizer_Lanes").props.selection.data[0].lane 
	
	category = "HMI"
	source = "ASCADA Client"
	usr = self.session.props.auth.user.id
	message = str(usr) + " Released Accum Lane " + str(lane) + " to palletizer " + str(pallet)
	data9 = usr 
			
		# Update Obsolete Cases
	system.db.runNamedQuery("Tables/call_pa_release_lane",{
		"lane": lane,
		"pallet": pallet
		})
			
		#Update Syslog		
	system.db.runNamedQuery("Tables/syslog_update",{
		"category": category,
		"source": source,
		"message": message,
		"data9": data9
		})

STORE PROCEDURE

CREATE DEFINER=`atronix`@`%` PROCEDURE `PA_release_lane`(IN lane INT, IN palletizer INT)
BEGIN
	INSERT INTO syslog(message, data1) VALUES (CONCAT("Manually releasing lane ", lane), lane);
	UPDATE accumulation_lanes2 JOIN manual_releases ON accumulation_lanes2.lane = manual_releases.lane
    SET manual_releases.status = 'PENDING', manual_releases.palletizer = palletizer, accumulation_lanes2.state = 'SUSPENDED'
    where accumulation_lanes2.lane = lane;
END
2

What is the type of the named query?

1 Like
3

It is in Query type.

The weird thing is that I believe it actually working but it throws that error.

4

He means here in the named query - is it an update query or a regular query or a scalar query?

image

5

It is not in update, just regular query against the db.

6

Can you show the content of your named query of the problematic line? It would clear things up.

I think what @PGriffith is driving at is that if you have it as a regular query but it says "UPDATE someTable SET x=y WHERE id=:id" or similar (which is what your python comment implies it is, an update query) but you have it set as a regular query, you’re going to get an error.

Ignition can’t parse your SQL so this is the only way you can inform Ignition of the nature of the query and if it should be returning a result set or if its just going to be getting back a number of affected rows etc.

7

The only place it shows update is within the stored procedure, which is separate from ignition.

image
image1295×500 20.8 KB

8

If you change the Query Type to Update Query and try again do you still get the error?

If this is SQL Server, I think there’s something at the top to get it to play nice with Ignition, I think SET NOCOUNT;

But right now yes I would expect you to get an error because your Query Type being Query means Ignition expects a result set. You’re Stored Procedure is not providing a result set. Hence the issue.

2 Likes
9

Yep, that's exactly it. More specifically, this is actually a JDBC requirement; the Statement class can either execute a query or an update:

image
image1942×76 21.4 KB

The error in this case is coming from the JDBC driver indicating that the wrong 'type' was attempted for the procedure call.

1 Like