Record IDs

rgates@ttcdas.com

I have a process that is outside of AwareIM that needs to create a row of data in a BO in AwareIM. We were able to successfully create the row, but aren't 100% sure the row ID column. We were planning on using the max(ID)+1, but wasn't sure if that is a problem. We did a quick test after we created a row by going back into AwareIM and creating a row in the same BO table. The ID for the new object is not 1 greater then the row we created outside of AwareIM.

tford

What is the Persistence type of the BO?

rgates@ttcdas.com

Database: Aware IM automatic

george.greiner

I have the exact same question funny enough. I recreated ALL of my tables to use Persistence Database: Aware IM Automatic and not sure how to deal with that number as well when I import data. I will need to upload records into multiple tables as our business is compiling data, cleaning it and then repackaging it to our clients.

tford

rgates - I think you are treading on thin ice trying to add instances to this BO from outside of AwareIM.

george.greiner

Also what is BASVersion?

rgates@ttcdas.com

It is a necessary for us to add data to tables in AwareIM via other applications.

I'm hoping that there is a solution or workaround for this type of processing. One other approach would be to have a Database_ Existing External that holds the data. Once I write something into this table a rule within the External Object would execute a copy over to the "native" AwareIM object. I'm just not 100% sure if Rules would handle this type of "trigger" on a External Object or whether I have to schedule something.

tford

One other approach would be to have a Database_ Existing External that holds the data.

You then define and manage the BO's primary key.

technopak

Hi,

I'm very interested in this discussion.

If I'm using a MySQL database for my Aware application, is there any reason why I can't insert data into the MySQL database directly ? I don't need Aware to perform any processing or data validation for me, so I wonder is there any good rreason why I should not do this ?

Anyone know how is the ID field generated ? The BASVERSION is another curiosity to me too.

Any suggestions ?

Peter

aware_support

Yes, you are treading on a thin ice here but using a little help from your friends (aka aware_support 🙂 ) may get you across the line.

BAS_VERSION is an internal column used by Aware IM to resolve version conflicts. If you are creating a new row, set it to 1

The ID should be generated as follows (this is extremely IMPORTANT - if you don't do it right you may stuff up your database).

a) There is a table called BAS_IDGEN. The table has a single row and a single column. It stores the maximum ID generated so far.
b) You should read the value in this column, increment it by 1 and set it as ID of the row
c) Then you need to update the BAS_IDGEN table with the incremented value
d) You have to synchronize access to this table, so that no other user/application etc modifies this value in parallel to you

Once again, if you stuff it up, you may stuff up the entire d/b, so be careful.

RocketRod

Just some comments about my own site.

We run JDEdwards ERP application and I extract lots of data into AwareIM BSV's constantly via scheduled SQL jobs that run constantly during the day. My process is to take the extracted data first into a work table (one defined for each equivalent AwareIM table) in my SQL database. The work table maintains the ID as Max ID + 1. From there I can change the data if required before I add it via another scheduled SQL job into the AwareIM table. So the ID is copied across to the AwareIM table from my work table. However I NEVER ALLOW INSTANCES TO BE ADDED for these AwareIM automatic tables defined within AWAREIM. I for example have a CRM application that takes customer information and sales orders from our JDEdwards system into AWAREIM. The SQL job is scheduled to run several times a day and our new customers and customer detail changes are all automatically taken care. I have had no issues after 9 months. I also developed a freight BSV that uses despatch information from my JDE system, a Financial reporting BSV that uses JDE GL information and a Supplier Management system that uses JDE Purchasing information. The key is I believe to NEVER mix the two. You ALWAYS only add instances from within AwareIM or externally but NEVER the two or you are asking for trouble. Although Support have stated how it can be done I would never touch the internals of a package such as Awareim by changing the value in the Bas_idgen table.

This process has given me the advantages of using existing AwareIM tables over otherwise using existing external tables.

Cheers Rod

pixerp

rgates,

in your 1st post you don't say how you create the row in BASDB.Table.

If you are using an external SQL statement, I agree with Aware_Support & RocketRod remarks: it's quite dangerous.

You should stick with AwareIM methods either "pulling" from within your BSV or "pushing" from an outside app using http-xml or a mix of both.

Just my 2c.

technopak

Thanks Support for the 'How to'.

Using MySQL, can you tell me what happens if an external application tries to update the BAS_IDGEN at the same moment as my Aware application does ?

Can I lock the BAS_IDGEN table momentarily to reserve the current position while creating a record externally and then release it back to Aware ? Will Aware grumble about that arrangement or does it wait a moment to get the unlocked max BAS_IDGEN and then proceed ?

Creating records externally is a massive benefit to us - if Aware copes well !!

Peter

rgates@ttcdas.com

I'm going to continue down the same path as what Technopak is asking. We are using SQL. How can I gurantee that the BAS_IDGEN doesn't get "stomped on" via an external process versus AwareIM updating it. What type of locking methodology is AwareIM using?

As for RocketRods approach --- my current requirements need for the data to be available immediately via an external application posting data to SQL and then getting the data into AwareIM. Scheduled routines don't work if I'm going to do it every 5 minutes. The data needs to be available RealTime.

My bsv is a CRM application. We have a need for people to attach documents to BOs within CRM without going directly into the CRM BSV. Also, people need to be able to retrieve attachments easily. We have created an application that is aware of BOs that could have attachments. We expose this interface via .net by using a drag and drop approach. This works best with trying to attach/select many attachments for use within Email or anything else that requires the attachments.

aware_support

Aware IM obviously locks the BAS_IDGEN table. This table is shared across all Aware IM concurrent users so locking is imperative. All you need to do is make sure that this table is locked as well when you make the request outside of Aware IM, but only for the time that you generate the ID - you must unlock it as soon as you can.

ab042

Let me offer a suggestion, send me a commission check if it works.... Just joking 🙂
I have not tested this but can't think of a reason it wouldn't work, then again maybe someone else has input.

#1) Set the BAS_IDGEN table record to ONE MILLION or some number you are NEVER going to go above. By doing this you are pre-blocking an ID range below it that AwareIM will NEVER USE (It always does an ID+1)

#2) Keep track of your own record ID below that number in your other application outside Aware. Keeping in mind if you ever get to the ID number in #1 your going to crash something.

#3) Insert you data directly into the database outside of AwareIM and set the ID field to whatever you want. Of course never go above the ID in #1

Everyone should be happy and you don't have do any locks.

Good luck, please post back any suggestions.

technopak

Thanks ab042,

An interesting suggestion indeed. I wonder is there any significance in the sequence of ID numbers - eg do they indicate the age of a record or anything else internally in Aware ?

Thanks support too, that explains what happens when Aware locks the table. Can you indicate what happens with Aware should an outside application lock the table at exactly the moment when Aware is trying to use it ? Will Aware 'wait' for the locked table to be unlocked by the outside application ? Or perhaps MySQL handles the management of the table availability internally ?

I prefer to maintain the sequence of IDs consistent with Aware's own internal operation, but I'm just worried about Aware not being able to process the max ID etc when it needs to if an external application had locked the table.

Thanks for the help on this,

Peter.

aware_support

Nothing dramatic will happen if you lock the table properly - one database can be shared between several applications and the database takes care of locking properly. By the way, you cannot use Derby if you want to access the database both by Aware IM and externally at the same time - Derby only allows access by one application.