Oracle Identity Columns
Posted 18 May 2006 - 09:55 PM
I can't see a way to identify such a data type when defining the table within PRO IV.
And if it is possible, how do you get the value back to use in the function.
Is it as simple as making the generated number a parameter in the package and passiing it back?
It's my understanding that PRO IV treats ORACLE as a native data repository, so why isn't this a no brainer?
As usual, PRO IV documentation is sadly lacking on this subject.
Posted 19 May 2006 - 05:52 AM
It works exactly like Pro-Isam.
AK Key1 10
AK Key2 10
AK SEQ_NO 10
When adding within a paging screen, you setup the first 2 keys and let ProIV maintain the seq no.
In an update use GETLPSEQ or your own # counter.
You can also use Oracle Sequences, but there are disadvantages to using them.
One disadvantage with Auto Sequenced files, is SQL select statements. I don't know if its been fixed in 5.5, but in 4.6 a select statement on an autosequenced file within a paging screen, returned 1 row regardless of how many rows are actually supposed to be retrieved.
Posted 19 May 2006 - 08:02 AM
I would be very careful using GETLPSEQ with Oracle.
Auto Seq files have lots of problems with oracle and embedded SQL.
And GETLPSEQ gives incorrect Seq numbers because it does not take any other transactions into account.
I would use Oracle Seqences for this, then you wont get any problems.
Posted 19 May 2006 - 09:01 AM
It depends on how you use it.
If one user has a transaction that includes a GETLPSEQ and then another user at the same time uses GETLPSEQ on the same file and main key, then the 2nd GETLPSEQ does not get the correct seq number because it cant see the other transaction. (Which is actually the same for other file types, just with oracle normally transactions take a little longer, esp if you have no commits in there).
Normally a seq number does not need to be 100% seqential, its just needs to be in order and unique, therefor Oracle Seqences are ok...
Its just something that caused us problems on an Oracle system I was working on a while ago.
Using Embedded SQL on Autoseq files dosnt work well and gives some very strange results some times... maybe older p4 versions were ok, but the version of 5.5 I was using was not.
Posted 19 May 2006 - 12:16 PM
PRO IV touts Oracle as a native relational database, but it doen't allow one of the most basic presepts of relational database design, namely Identity columns.
Just want to make sure I'm interpreting your answers correctly.
Posted 20 May 2006 - 09:22 AM
So far I am told I am the only one requesting this feature. Good to see I am not alone.
The reason we want the database to be in control of issuing keys is that we need to connect to the database from multiple environments (.Net and pro-iv)
We found the following workaround: It involves a couple of alternate file definitions and an extra field.
In SQL, add a field PROIVKEY to the table definition, and define an index on it.
If you create a pro-iv file definition that has PROIVKEY as key defined, and the real (autonumerated) key not present in the pro-iv file definition, you can add a record to the table.
You will need a second file definition, again with PROIV KEY as key defined, but now with the real key as a (numeric) field present in the proiv file definition. Afther you wrote the record using the first alternate, you can now read the record using the second alternate. You can now read the real key field, which you can use to store in other files, or if this was a parent record, to create the child record based on the real key.
The last file definition you need, will have the (numeric) real key defined as a key field and can be used for regular maintenance of the table.
I am about to set up some example functions and file definitions to show pro-iv how we can work with these autonumerated keys.
I can post them here if you are interested once they are done.
Posted 22 May 2006 - 02:36 PM
We are running 5.6 and I still have problems with a SQL statement only returning 1 row on an Oracle table with a alpha sequence. It is pretty madening. I would shy away from alpha-sequences as keys if you are using oracle. We also have used Oracle sequences and find them pretty nice.
Posted 13 June 2006 - 06:09 AM
The Zip file contains a word document which describes the problem and a VIP export with the demo file and function definitions.
It's for SQL Server, but would probably work in a similar way for Oracle
Posted 13 June 2006 - 03:18 PM
Regarding your problem with oracle returning one column, pro-iv has an idtiotsyncracy where if you do a sel-partial on a oracle table which is marked as auto-sequenced, it will return ONLY all records where all key fields except the sequence number match.
For example, if your file is CUSTNO, SEQNO, NOTE and you do this:
CUSTNO = '' SEL-PARTIAL(CUSTNO)
you will get all records for the first customer in the file, and then pro-iv will abandon processing.
This happens in updates as well.
We get around this by creating an alternate filedef, with just key fields and NOT auto-sequenced.
You should be able to test this by removing the auto-sequence flag from the filedef, regenning your function and running it.
Posted 10 July 2006 - 08:40 PM
I have a question. In your example, does the PROIVKEY need to be unique for that table? If so, then the SQL generated identity column EVENT_KEY is not really doing much for you. Is that correct? Thanks. Mark Dexter
Posted 11 July 2006 - 06:09 AM
Yes, the PROIVKEY field should be unique, otherwise it would not be possible to retrieve the EVENT_KEY field for the record just inserted.
What this is doing for us, is that both PRO-IV and Microsoft .Net code can add to this file, and the database will issue the keys.
I haven't tried it yet, but we could use @TERM for the PROIVKEY field, and clear it after reading the record
Posted 12 July 2006 - 11:56 PM
1. Create a SQL table with an Identity column and an alpha key of the same length (e.g., alpha key of 12 to hold identity up to 999,999,999,999).
2. Create a PRO-IV file def that omits the Identity column but includes the alpha key.
3. (The tricky bit!) Create an INSERT trigger that sets the alpha key to the alpha equivalent of the identity value whenever a new row is inserted.
4. When adding rows in PRO-IV, just make sure the alpha key is either left blank or set to '0'. It will automatically be set to the next identity value when the INSERT trigger fires.
5. To optimize performance with PRO-IV table reads, you might want to add an index on the alpha key value. (We haven't gotten this far.)
See the attached document for an example with screen shots. So far, we've only prototyped this and have not used in production. Any suggestions or feedback would be appreciated. Thanks. Mark Dexter
Reply to this topic
0 user(s) are reading this topic
0 members, 0 guests, 0 anonymous users