Jump to content


Photo
- - - - -

Oracle Identity Columns


19 replies to this topic

#16 Wim Soutendijk

Wim Soutendijk

    ProIV Guru

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

Posted 13 July 2006 - 06:05 AM

Mark,

I like the idea, and will do some testing on it. How would get the actual key back in to proiv?
What if I have a parent-child relation, and have to inserted a couple of records in to a detail file, for which I would need the key being generated for the header record?

Wim

#17 mdexter

mdexter

    Advanced

  • Members
  • PipPipPip
  • 70 posts
  • Gender:Male

Posted 13 July 2006 - 05:45 PM

Here is one way you could get the value of the identity column in PRO-IV, at least in SQL Server. In SQL Server, there is a function IDENT_CURRENT() that gives the value of the most recently used identity value for a given table. After the row was added, you could do something like the following, where TABLE_NAME is the name of the data table with the identity column.

SQL DYNAMIC
UPDATE temp_table
SET ALPHA_VALUE = IDENT_CURRENT(‘TABLE_NAME’)
ENDSQL

In SQL Server, you cannot directly read a SQL variable from PRO-IV logic, so you have to store it in a SQL table and then read a row from that table in PRO-IV. So at this point, you would need to read temp_table and get the value of ALPHA_VALUE. That would be the identity column for the current row.

I don't know if there is an equivalent function to IDENT_CURRENT in Oracle (I would guess that there would). If so, I think you can do an SELECT INTO a PRO-IV scratch variable, so you might be able to do something like the following (except that you would need the Oracle equivalent of IDENT_CURRENT):

SQL
BEGIN
SELECT IDENT_CURRENT('TABLE_NAME') INTO :$VAR
END;
ENDSQL

Hope this helps. Mark Dexter

#18 mdexter

mdexter

    Advanced

  • Members
  • PipPipPip
  • 70 posts
  • Gender:Male

Posted 20 July 2006 - 09:59 PM

Multi-user problem in SQL Server:

A note of caution about my previous post using the SQL Server Identity column and an INSERT trigger. We tested this in a multi-user simulation and it appears to only allow one user at a time to INSERT rows. We aren't sure whether this is because of the trigger or just a consequence of using the IDENTITY column in SQL Server. So we are going to use a different method (the NEWID() function in SQL Server). Mark Dexter

#19 Matthews Estrice

Matthews Estrice

    ProIV Guru

  • Members
  • PipPipPipPipPip
  • 241 posts
  • Gender:Male
  • Location:Henley-on-klip

Posted 28 July 2006 - 01:33 PM

In PostgreSQL I use the ff. sql :-
=======================
CREATE SEQUENCE testseq
INCREMENT 1
MINVALUE 1
MAXVALUE 100
START 10
CACHE 1;
ALTER TABLE testseq OWNER TO edb;

CREATE TABLE tblseq
(
seq_id int4 default nextval('testseq'),
col1 numeric
)
It works fine and I can select seq_id from tblsq where seq_id = ?.

I am busy testing on Oracle DB using the ff. sql
=================================
CREATE SEQUENCE testseq
INCREMENT BY 1
START WITH 10
MINVALUE 1
MAXVALUE 100
CACHE 2;

with a trigger inserting values into seq_id.

#20 Guest_Matthew Estrice_*

Guest_Matthew Estrice_*
  • Guests

Posted 14 August 2006 - 09:53 AM

CREATE SEQUENCE dbname.MATTHEWS_SEQ INCREMENT BY 1 START WITH 1
MAXVALUE 999999999 MINVALUE 1 NOCYCLE NOCACHE NOORDER;
/
CREATE TABLE dbname.MATTHEWS_TAB
(
COL_A NUMBER(10) NOT NULL,
COL_B VARCHAR2(10) NOT NULL,
COL_C VARCHAR2(10)
)
TABLESPACE USERS;
CREATE OR REPLACE TRIGGER dbname.MATTHEWS_TRG BEFORE
INSERT ON MATTHEWS_TAB FOR EACH ROW DECLARE
generator NUMBER(10);
BEGIN
SELECT MATTHEWS_SEQ.NEXTVAL INTO generator FROM DUAL;
:NEW.col_a:=generator;

END;
/

(insert into matthews_tab values 'me','you');



Reply to this topic



  


0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users