Question about checking data in a database

This is my first post on this site and I wanted to say that this forum is amazing. I'm kicking myself for not finding it earlier. I've been struggling to learn things on my own for years. This is such a great resource.

I've got a situation that has me a bit perplexed and I'm hoping the experts here can help me out. We're currently using RsSQL to log data to a SQL database. Each run of material has its own unique run number, and that number is what we query to retrieve the process information for our records. I've been asked to design it into the start up of each new run that the computer will check against all the previous runs and verify that the run number the operator has entered hasn't been run before. Actually, the operator is scanning a barcode on a batch master for each run, but potentially they could scan the barcode from the previous run. This would be bad for obvious reasons, and unfortunately when we examine the database, we've found that this has happened.

The way I see it we have two options. 1) Somehow retrieve the run numbers that we already have stored in the database and compare them to the one the operator is currently scanning, or 2) Store each run number in a string file in the PLC and compare against all those values (it needs to be string because there are mixed character types).

The reason I'm not excited about option 2 is because if the PLC loses its memory and the numbers disappear, this feature wouldn't work for the remainder of the campaign. Campaigns sometimes run for up to a couple months, so I need to be able to reliably store the run numbers, which is why I'm thinking that iption 1 would be better. The only problem is, I can't fiture out how to do this.

I mentioned earlier that we're using RsSQL to record data, but thanks to this forum I've found FactorySQL and we're going to go with that for sending data from database to PLC's and keep RsSQL simply logging data. So I'd be doing this in FactorySQL.

In the SQL table, the run numbers would be stored in one column named "RunNumber". How would I have FactorySQL send all the values in that table to different addresses in a PLC?

ACH!!!
DO NOT DO THAT!!!
Fix the Database up if you have to, or add even a duplicate column. Make the "Run_Number" field unique and indexed. Doesn't have to be a primary key, but flag the column as unique. Now, if you try to insert a duplicate record, the DBMS will return an error immediately.

----------Edit
Blindly retrieving all rows of a table, even for one field is almost always a bad idea. Network bandwidth is consumed, the DBMS has to do a lot of work. Better to let the DBMS itself just do a fast insert. Use a stored procedure. On a valid (non-duplicate) insert, you can return the @@identity field (assuming you have a column tagged as such). On a duplicate insert, you will receive an error.

Yes, the DB wouldn't accept the new number, but from my controls side, how would I make it so RsView wouldn't allow them to run if that error occurred? Without that interlock, they could just keep on running, only now the data wouldn't be recorded.

Doesn't RSSQL allow return values? Or FactorySQL?
Post the attempt as a triggerred stored procedure, and check the return value.

Ok, interesting. Your skills are obviously way beyond mine. I'll have to look into that. I just started using FactorySQL so I don't know if it has that (my guess would be that it does).

Check out the Block Group function in FactorySQL. I haven't used it yet but it looks like it will do everything that you are asking about.

Most SQL's have a way of doing a query.

Select * from [database] WHERE BarCode ID = Record.BarcodeID.

You are basically using the new barcode and comparing it to your database history.
If it finds a match, this would indicate a duplicate.

Ok, thanks Mark, I'll look into that.

I realized a fatal flaw in indexing the run number. Sometimes we actually do re-run a batch of material without assigning a new run number. If I simply say that a run number can't be entered into a table more than once, that would prevent us from running the way the company has run for 35 years.

What I want to do is check if the run number is in the database, and if it is, alert the operator, but not really much more. We might make it so RsView will only allow a supervisor to allow the run to be started in this situation. So, using something like what Mark suggested, how would I make this happen? I understand how to run queries, but what's new to me is running a query and returning a value to a PLC.

Still no takers? I may be asking my question badly, I don't know.

When each run ends, I want FactorySQL to record the run number in a table, along with whether the run was complete or if it was shut down for an emergency. What I'd like to have happen is, if the run was complete, record the number with "complete" set to "yes". If it was shut down for an emergency, record the number with "emergency" set to "yes". If it's a run that was previously shut down for an emergency, but it's now finishing, I don't want to add a number, I want to update the record to show that it is now complete.

Am I trying to do something FactorySQL is not capable of doing? Would this require a stored procedure? I don't know how to use stored procedures, but if I need to, I'll learn.

1. Index the run number in your table. Don't require the value to be unique.
2. A simple query like Mark suggested will indicate whether the run exists. If so, prompt for supervisor approval.

This is simple to do in FactorySQL - no stored procedures required. I'll explain the steps in response to your post on the IA forum. You would go through similar motions to accomplish the task with RSSQL (but probably with stored procedures).

I agree with the intent of RDs 1st reply -
1. A primary key will guarantee a unique, indexed value for each row (actual production run)
2. An indexed "batch/run" column makes users operate on unique values, but allows for duplicates with supervisor approval
3. No need to query lots of data then search the big resultset for a match when the DB supports querying directly for that match.

It will be up to you to determine how to merge/view production data when re-running a batch.

I do disagree on forcing column uniqueness in the table as a means of input validation. This can be accomplished more gracefully elsewhere - in the group in FactorySQL or your Stored Procedure in RSSQL.

Norml - FSQL Block transfer group does array reads/writes for efficient transfers of large amounts of data.