Jump to content


Photo
- - - - -

Oracle Identity Columns


19 replies to this topic

#1 sebago62

sebago62

    Member

  • Members
  • PipPip
  • 25 posts
  • Gender:Male

Posted 18 May 2006 - 09:55 PM

Is it possible to add records to an ORACLE table with an auto sequenced number as the primary key from within PRO IV?

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.

Thanks,
Tom

#2 Guest_Guest_*

Guest_Guest_*
  • Guests

Posted 19 May 2006 - 05:52 AM

ProIV handles the auto-sequencing not Oracle.

It works exactly like Pro-Isam.

File A
--------
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.

#3 Rob Donovan

Rob Donovan

    rob@proivrc.com

  • Admin
  • 1,640 posts
  • Gender:Male
  • Location:Spain

Posted 19 May 2006 - 08:02 AM

Hi,

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.

Rob.

#4 Neil Hunter

Neil Hunter

    ProIV Guru

  • Members
  • PipPipPipPipPip
  • 414 posts
  • Gender:Male
  • Location:Johannesburg, South Africa

Posted 19 May 2006 - 08:25 AM

Sorry that was me posting as Guest.

Haven't had issues like that with GETLPSEQ and Oracle. Maybe 5.5 is different.

Don't forget, with Oracle Sequences, that you get one bite at the cherry. Once that number is incremented, its lost

#5 Rob Donovan

Rob Donovan

    rob@proivrc.com

  • Admin
  • 1,640 posts
  • Gender:Male
  • Location:Spain

Posted 19 May 2006 - 09:01 AM

Neil,

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.

Rob.

#6 sebago62

sebago62

    Member

  • Members
  • PipPip
  • 25 posts
  • Gender:Male

Posted 19 May 2006 - 12:16 PM

So... It isn't possible to get PRO IV to recognize an Identity column in Oracle then, is it?

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.

Thanks Again,
Tom

#7 Wim Soutendijk

Wim Soutendijk

    ProIV Guru

  • Members
  • PipPipPipPipPip
  • 211 posts
  • Gender:Male
  • Location:Netherlands

Posted 20 May 2006 - 09:22 AM

I have asked pro-iv over a year ago for support of autonumerated keys, maintained by SQL Server.
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.

#8 sebago62

sebago62

    Member

  • Members
  • PipPip
  • 25 posts
  • Gender:Male

Posted 22 May 2006 - 12:58 PM

Wim... please do post the results, thank you.

We are also contacting PRO IV, but in the past, when doing things like this, we've found they are not much help.

Thanks,
Tom

#9 NeilIV

NeilIV

    Member

  • Members
  • PipPip
  • 28 posts
  • Gender:Male

Posted 22 May 2006 - 02:36 PM

As a side note,

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.


NeilIV

#10 Wim Soutendijk

Wim Soutendijk

    ProIV Guru

  • Members
  • PipPipPipPipPip
  • 211 posts
  • Gender:Male
  • Location:Netherlands

Posted 13 June 2006 - 06:09 AM

Here is an example I created to show ProIV what work around is possible to use autonumerated files.
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

Attached Files



#11 sebago62

sebago62

    Member

  • Members
  • PipPip
  • 25 posts
  • Gender:Male

Posted 13 June 2006 - 01:10 PM

Wim,

Thanks for the info and examples. It is much appreciated and will be very helpful.

Thanks Again,
Tom

#12 Mike Schoen

Mike Schoen

    Expert

  • Members
  • PipPipPipPip
  • 198 posts
  • Gender:Male
  • Location:Guelph, Canada

Posted 13 June 2006 - 03:18 PM

NeilIV,

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.

#13 mdexter

mdexter

    Advanced

  • Members
  • PipPipPip
  • 70 posts
  • Gender:Male

Posted 10 July 2006 - 08:40 PM

Wim, thanks for the example. I agree with your enhancement request for PRO-IV. It would also be nice to allow SQL statements to store values into PRO-IV variables. (There is a posting that indicates that this works in Oracle, but I have not gotten it to work in SQL Server.)

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

#14 Wim Soutendijk

Wim Soutendijk

    ProIV Guru

  • Members
  • PipPipPipPipPip
  • 211 posts
  • Gender:Male
  • Location:Netherlands

Posted 11 July 2006 - 06:09 AM

Mark,

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

#15 mdexter

mdexter

    Advanced

  • Members
  • PipPipPip
  • 70 posts
  • Gender:Male

Posted 12 July 2006 - 11:56 PM

One of our clever programmers came up with the following method for using Identity columns in SQL Server. I think it would work the same in Oracle, but we don't use Oracle. (It would be great if someone could try it and confirm it.) It only needs one PRO-IV file definition and doesn't require any key generation in PRO-IV. Here is a quick overview. I'm attaching a Word document with more details.

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

Attached Files





Reply to this topic



  


0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users