Hi Guys,
I do a ton or insert and select statements, i have been getting the keys back (getKey=1) and saving them into tags for future use when i need to reference the inserted row later in the app. Is this the recommended way to pass keys around?
Some colleagues of mine were scared that if a tag didn’t get set somehow it could screw everything up and its a valid concern. I am curious to see what you guys do and if you have any suggestions? Thanks
no suggestions?
I’m not sure why you’re saving the key in a tag. Generally getKey is used when doing multiple inserts into different tables, i.e. I create a new user, I use getKey to get the new user’s id and then use that value to insert additional information about the user into another table. These two statements would generally be executed within the same script so there isn’t a reason to keep the key’s value once the script is done.
Maybe some information as to why your keeping the keys would help.
Yea, like Pat, I use a key in the same script that generated the key.
If I wanted data from a database I would just query for it using a different column of a database table.
Why would you store the keys? They are already stored in the database.
Hi Guys, the only reason I’m saving the key is because i have to reference the inserted row later. The process I’m working on can last hours or days, I do the first insert once its started, save the key and then reference the key in my update query after the process is finished.
That was the best way I could think of to have a reference to the key, the inserted row does have date columns, instead of saving the key in a tag, i could run a second query where i check for a row from that day but with no end time. There could be an issue if the process runs into the next day, just looking for some suggestions. Thanks
I see, whatever works best for your situation. If storing the IDs in the tags is working okay then that sounds fine.
Another idea is creating a new column in the same database table that your key exists in. When a process completes you must have some kind of identifying information to locate the key associated with the process: put this data in your new column. When a process completes run an update query using the identifying data.
If you can’t create a new column in the database table for some reason then you could create a new database table that contains a column to hold a key and another column to hold identifying data. Use this table to look up keys with the identifying data.
You’re table should be designed in such a way that you don’t need to save the key. You should be able to run some query to get the key or determine what row(s) in the table you should update. If you can’t then I would look at your table’s design and improve it.
A key is generally used to link data across multiple tables. I might have a user table which has the columns userID and userName, and another userAddress table which has the columns userID and userAddress. Here my userID key is used to link the userAddress to the userName. There’s no need for me to store the userID anywhere since I can query the user table and get it.
A table’s key doesn’t have to be only one column either, a key can consist of multiple columns. Let’s say I have a table called myLevels with the columns myLevelsID, dateTime, tagID, tagValue. There’s the primary key ‘myLevelsID’, but there’s also a key using the dateTime and tagID columns. So I could query my table asking for the tagValue where dateTime = ‘2013-12-06 00:00:00’ and tagID = ‘myTagname’.
In your case it sounds like you have a process, when your process starts your putting an entry into your table, when your process is complete you are updating that entry in your table. I would design the table so that I could run an update without using the key. You could have a table called myProcess with the columns myProcessID, processName, processStartTime, processEndTime along with your process data. At the start of your process you’ll insert your process data but not a value for the processEndTime, it will be NULL. When your process is complete you’ll do an update using a WHERE clause like “WHERE processName = ‘Process1’ AND processEndTime is NULL”. Don’t forget during the update to assign a value to the processEndTime.
Thanks Pat,
You’re right on, it is process related, the problem is that we have about 6 processes insetting rows into the table, so its not a 1:1 relationship. There are 6 processes and each process can have six operations going at one time. I am currently doing it as you have listed below except for the fact that I am using the get key = 1 to return the key so i can update it later when the operation is finished.
I’ll rethink the design and see if i can use you’re option and think of something similar. Thanks!