Insertion from other processes

RLOMASKY

Hi,

I'm using AwareIM to create an admin module for the backend of our website. However, there will also be records inserted to the same tables through the website. I'm concerned because the tables lack identity fields and foreign keys that users will be able to insert bad data. For example, if someone doesn't update the BAS_IDGEN table correctly, the records will be bad. Or if someone enters an incorrect foreign key, I won't be able to detect it.

Is there any way to get around this?

aware_support

You need to make sure that BAS_IDGEN is updated appropriately, otherwise your system is likely to be unusable.

So you need to prevent people from inserting directly or write some database script that would do it properly and get users to use the script rather than inserting directly

johntalbott

Along the lines of Vlad's suggestion ...

How about locking down the tables and creating SP's for the other systems to use?

Another option would be to have the website integrate via web service calls rather than directly touching the database at all.

BLOMASKY

since I am doing a lot of SQL and SP for creating rows in tables, let me tell you some of the techniques I have used
and remember:
does NOT matter what the ID # in a table is, as long as it is unique
does not matter if 2 diff. BOs have the same ID field.

Technique 1
Start a transaction
get ID from BAS_IDGEN
after done adding rows, get count of them
Update ID with # of rows added
Update BAS_IDGEN
now, while adding the rows, I use the ROW_NUMBER() function in MSSQL so my insert looks like
INSERT INTO XXX (ID ....)
SELECT @ID + Row_Number() (order by xxxx) .....
this will give me a sequential #

Technique 2, for when others are adding and I want to let them do their "thing"
Add a numeric field to my BO that is not used for anything in Aware
Change it to an identity field in SQL with the SEED set to a large #, above what the ID will ever get to
Create an AFTER INSERT trigger that checks if ID is NULL and if so, assigns identity field to ID
this way, dont have to worry about BAS_ID

Technique 3, when I am inserting records from other tables, they might have a unique key, and if I know its not going to meet the ID fields in Aware, will just use that. Many times my Aware BOs are at the end of the food chain, after other table have been created.

and, when all else fails, you can always reset the ID # if you really screw up (which I have done more than once.), however, I do find I have to restart aware when I do that...

Bruce

ACDC

Another option would be to have the website integrate via web service calls rather than directly touching the database at all.

I agree
One of my golden rules is to never touch the database, always go through an interface and let Awareim do the posting. Alternatively write to an external database and then fetch the data from within AwareIM using persistance setting to external database . Also this way the rules engine is always at play

BLOMASKY

Rachel just did a test that works! Changed the ID field to a Sequence, set the initial seed to 1000000 and all is good. When Aware adds records, it uses the ID that Aware generates. When inserting records from an external source, it will use the sequence.

Too bad I can't do this with MSSQL. oh well.

Bruce