Stored Procedure group problem

I have a stored procedure I’m trying to get working in FactorySQL and I keep getting the same error. “…Procedure ‘SP_LRunNumbers’ expects parameter ‘@inputRunNumber’, which is not supplied.” My stored procedure is set to return one string.

I can call the procedure from Access using a pass-through query on this same machine, using the same DSN connection. So it shouldn’t be a permissions issue.

I’ve got two OPC items in the group; one acting as the input for the stored procedure named CurrentRunNumber, the second should be the return value of the SP, named WriteBackNumber.

If I look at the item configuration for CurrentRunNumber, I checked “Map to in param: InputRunNumber”. InputRunNumber is the input parameter in my stored procedure. I even changed the name of the input parameter on the SQL server and when I clicked the down arrow here, the name of the “in param” changed to the new name. So I know it SEES my stored procedure. I checked “Don’t write back to OPC”. As far as I can tell, by reading through the help file, this SHOULD send the current value of this tag to the stored procedure, but FSQL is acting as though I haven’t even set this up.

On the item configuration for WriteBackNumber, I checked “Map to return value”. But I haven’t gotten far enough to see if the SP will return its value to this tag.

Might stored procedure input variable names be case-sensitive? inputRunNumber vs InputRunNumber

If I inferred correctly, your database connection is set up to use a DSN, correct? It appears as though the stored procedure group currently has some problems with DSN based connections. Try creating a native connection to your SQL Server database and see if that solves your problem.

Regards,

Interesting, a problem with DSN? I guess it’ll have to wait until Monday now since I’m at home now. If this actually works I’ll be very happy.

Carl, from what I can see, the variable input is not case sensitive. But either way, when I check “Map to input param”, my input variable is listed in the drop down list, so I didn’t have to type it. FactorySQL examined my SP and knew what the input variable was.

Yeah, it was a shot in the dark :slight_smile:

Don’t think I don’t appreciate it!!! :prayer:

When Rockwell had a forum, I used to wait a year for an answer to questions. Then instead of making their forums better, they simply removed them from their site. You guys are awesome! You’ve got program developers moderating the forums, and I’ve gotten three replies within a couple hours!! Awesome.

Sonic - From you PLCTalk post:

  1. The operator scans a barcode to enter a batch number
  2. batchNum changes value in the PLC, triggering FSQL to check the database for existence.
  3. If the batch doesn’t exist FSQL writes the batch number to the confirmation tag. If it already exists FSQL writes a 0.
  4. RSView prompts for supervisor verification and deals with duplicate batches however you see fit.
  5. You may index batch numbers in your db table without enforcing unique values
  6. Stored procedures are not necessary to accomplish this task

Do this:

  1. Create a “Standard Group”. Add batchNum and confirmation, two integer tags (OPC items). Double click both items to set to read only.
  2. Create an Action Item, batchExists. SQL query type. batchID is your column name, {batchNum} the plugged in OPC value (right click “items…”).
SELECT batchID FROM table WHERE batchID={batchNum}
  1. Create another Action Item, writeBack. This one will be an expression. Check “store result to DB field or OPC item” and write back to your confirmation tag.
If(IsNull({batchExists}), {batchNum}, 0)
  1. On the group trigger tab, select “Execute group on a trigger”. Select batchNum. Select “Active on value change”.

Other action items can easily write different values to other locations if you need them for RSView. You may want the PLC to do something if batchNum changes but confirmation doesn’t after a certain timeout (meaning that FSQL is not responding).

A few more points:

  1. You could consolidate #2 & 3
  2. This will work fine with multiple entries with the same batch number

Nathan, thank you SO much. I actually can’t wait until Monday to try this out! What’s wrong with me? It’s Saturday and I can’t wait until Monday.

What you wrote makes total sense and I can definitely see how it should work for me.

Thanks for replying here about my post from plctalk.net.

One more question. Can FactorySQL query SQL views? I’m not sure if I plan on doing it in this instance, but I use views to send data to an Access front end for other purposes. It would just be nice to know if FSQL has that capability.

Also, are you the guy who made the videos? Were those birds chirping in the background of one of them? Funny stuff. Great vids though.

Sure, FactorySQL can work with views. The ins and outs of it will obviously depend on what it is exactly that you’re trying to do, but overally they just look like tables to FSQL.

And yes, Nathan made the videos, and some boisterous love birds make an appearance from time to time. I myself also have a couple of lovebirds, so I can say from experience that they’re pretty fond of making as much noise as possible when it’s least desirable…

Do you have birds in the office? I mean, you have to think from my point of view. I’m imagining that you a) have birds in the office, b) don’t have an office and you all work out of someone’s house, or c) Nathan is so obsessed with work that he made the videos at home, where he has a couple birds that he can’t keep quiet.

Funny stuff :laughing:

Glad to hear - I get excited about this stuff too :smiley:

Those are Korean lovebirds that you're hearing. I work out of the Seoul branch. Seriously though, I'm not employed by IA - I do consulting work from home.

[quote="SonicClang"]Do you have birds in the office? I mean, you have to think from my point of view. I'm imagining that you a) have birds in the office, b) don't have an office and you all work out of someone's house, or c) Nathan is so obsessed with work that he made the videos at home, where he has a couple birds that he can't keep quiet.

Funny stuff :laughing:[/quote]

You don’t work for IA? Really? That’s surprising.

Guess I was splitting hairs - I do work for IA on a consulting basis - quite enjoy being involved. During the day I repay Uncle Sam for my education.

So I have it working.

I made the confirmation tag a bit instead of integer. Our run number is a string, not integer. I don’t want to write back the run number to another string if it exists in the database, I’d rather just set a bit high or low.

So what I did now was, I made the the expression action item:

if(isnull({RNExists}), 0, 1)

So if the run number exists, it writes a 1 to a bit in the PLC. If it doesn’t exist, the bit goes to 0.

This software is UH-MAZING. Could I do this in RsSQL? Probably. But this easily? No. I’d have to do half the equation on the SQL server and the second half in RsSQL. With FSQL I can do it all in one place, and in a way that I fully understand.

I’ve got a new question on this same subject.

As the operator shuts down each run, they will be asked why the machine was shut off. “Was the run complete?” And they’ll answer Yes or No. FactorySQL will then record the run number along with whether the run was complete or not (a done bit = 1 or 0).

We’ll then use the “done” bit to automate the process of starting the machine. If an existing run number is scanned and that run was marked as complete (done bit = 1), then we’ll require supervisory override to start the machine with that run number. If the existing run number is marked as not complete (done bit = 0), we will not require supervisory override.

My question now is, how would I have FactorySQL set the done bit from 0 to 1 in the database on a previous run number without adding a new record? I just want to update the record.

I think I might have an idea, but writing it out here helped me gather my thoughts.

Hi-

You have a few options here…

The standard group can be used to update rows as well as insert them. Furthermore, it can update rows using a dynamic where clause- that is, the where clause can reference the values of items in the group in order to change dynamically. So, you could have a group that has the done bit, another item with the run id (either from the plc or an action item which looks up the last run id from the database). The group would be set to update, with a custom where clause (the italic items are settings on the standard group’s action tab). In the where clause, you could say “row_id={row_id_item}” (without quotes, and with the correct item reference- which you can get by right-clicking or hitting CTRL-Space).

Set up the group with the correct trigger, and then when it runs it will update that row.

Another option would be to stick with stored procedures (people coming from RSSQL tend to be fond of them :slight_smile: )… and just make an “updateRun” procedure that takes in the run id and the new done bit value.

Finally, you could just create a standard group that “writes to the first row of a ‘dummy’ table” (basically, it doesn’t write to the DB itself), that has read-only OPC items and a series of Action items that do what you need. Action items can be queries or expressions, can refer to the values of other items, and execute in sequence. Basically, you would end up crafting something similar to option #1 above, but you’d just be writing the update query yourself instead of letting the group do it.

Hope that gives you some ideas…

Regards,

I went with a standard group, set to update, with the where clause set to RunNumber=’{RunNumber}’ and that works perfectly.

Now I’ve got two groups that write to the table. The first one, which will be used 9 times out of 10, writes new values to the table. The second updates the done bit of existing run numbers. The triggering for each will be done through action items.

To write new run numbers, the expression will be: RecordRun = 1 & ExistingRun = 0

To update the done bit of existing runs: RecordRun = 1 & ExistingRun = 1

Cheese and rice! I just keep running into stupid little issues.

For my custom where clause for my update group I’m getting an error because the run number is a string. It’s trying to convert it to an integer. No matter what I do I can’t get it to accept a string for the where query.

What error are you getting? Is it from FactorySQL or from the database during execution?

What is the value of the WHERE clause? Post it in here using the [quote][/quote] markup in order to get the text precisely.

Regards,