Stored Procedure Question

Good afternoon Team,

I am working on a stored procedure to "count" the time between a scan on my scanners.

When I run the SP it calls as it should showing each line individually.

I have created a query tag as well as a named query in ignition perspective. Both of these only show the count, which is 28 rows rather than all the data.

What am I doing wrong?


There's no way to know without seeing your binding on the data property...

HEre is my tag binding on the table.

Here is my query tag.

Is this MySQL you're using? I'm not familiar with that GUI in your screenshot... I'm trying to figure out if it's syntax related or maybe a DB driver but it's difficult to do without knowing the vendor.

OK, so I found what I think is the issue... You need to add your DB's version of SET NOCOUNT ON (that's SQL Server syntax) in your procedure because if you don't , you'll return two datasets - the number of rows affected and then the data you're expecting. I found that here and remembered that I myself have done this before...

1 Like

Great, thanks for the reply.

I am using mySQL and it does not appear to have a NOCOUNT setting. I am going to dig deeper.

An update for anyone that cares.

When I created a named query for the called procedure I noticed this error.

java.sql.SQLException: Bad format for Timestamp '00:12:11.128504' in column 4.

I then went into the stored procedure and formated the date on the column to HH:MM:SS and it started working and pulled the dataset instead of the count.

Thanks for the hlep!

1 Like