Background, what I'm trying to do is feed the ovsd db from a form created via coldfusion, what I need to happen is when this form is submitted a new person record with corresponding fields from CF app fill in automatically.
I've determined with Ruth Porters help that ITSM_PERSONS is the table in which the data will be stored, but now my problem is this:
PER_OID is a decimal field (i believe the object identifier for each new person record???) WHERE DOES THE VALUE IN THIS FIELD COME FROM? I cannot find how it is related and when the CF application tries to create the new person record it needs the info for this field to give the new record a unique ID...
To determine a value for a new PER_OID, SD uses an algorithm which can best be compared with the usual UUID algorithms. Only, it's algorithm is slightly more complicated in order to generate unique id's (within one SD instance) with a maximum length of 64 bits (instead of the 128 bits for real UUIDs). The algorithm is such that a new PER_OID will always be a higher number than the currently highest PER_OID.
So, in your case, determine the minimum value of PER_OID; it's probably something with 10 digits. Any number you come up with below that number is fine; SD will never generate a number equal to that. So in the simplest case, a new PER_OID value would be:
SELECT MAX(PER_OID) + 1 FROM ITSM_PERSONS WHERE PER_OID < 999999999
If you're using Oracle, it's probably safest (with regards to transaction mechanisms) to use an Oracle Sequence with a starting value of 1.
Can you call an external URL from ColdFusion? It would be much easier to accomplish this as a web-api application in a JSP page and then just call the JSP from your ColdFusion app. I'm not familar with ColdFusion, but most scripting languages seem to have a way to create an HTTP request.
Honestly, I would pursue an option along these lines LONG before I would start writing data into the database. It's very dangerous since you could easily start getting duplicate OID's once SD and your app start both trying to create person records. Using the web-api assures that all the proper mechanisms are being used.