Problem with View - Urgent

Problem with View - Urgent

Postby tcurtis » Thu 14 Jan 2010 17:04

Hi All

Pulling my hair out today. I need to retrieve a sequence from Oracle via Entity Framework. Giving up with stored procs/functions as it seems almost impossible to do this remarkably simple thing.

I'm now trying to do this with a view. I have a view which calls a function which returns the nextval from a sequence.

The view shows up when I do 'Update from database' under Views. I select the view and click finish - and absolutely nothing happens. No errors, nothing, the view simply isn't there in the model.

Can anybody help please? This is getting pretty urgent as I've spent an entire day simply trying to get a sequence nextval. Crazy or what man!

Cheers dudes.
tcurtis
 
Posts: 13
Joined: Thu 14 Jan 2010 16:52

PS Here is the view

Postby tcurtis » Thu 14 Jan 2010 17:05

CREATE OR REPLACE VIEW get_next_tapetp_seq
AS
SELECT
CAST(get_sequence_nextval('tapetp_seq') AS NUMBER(9)) AS nextval
FROM
dual
tcurtis
 
Posts: 13
Joined: Thu 14 Jan 2010 16:52

Postby AndreyR » Fri 15 Jan 2010 08:29

Entity Framework has a limitation: every entity should have Primary Key defined.
The view you have created does not have Primary Key and can't have it, so there is no possibility to add it to the model.
If you have to use stored procedures, please take a look at this blog post:
http://www.devart.com/blogs/dotconnect/?p=5
But if you simply want to retreive the value of the inserted primary key which is generated from the sequence,
it will be enough to set the StoreGeneratedPattern attribute in the Store part of the Entity Framework model
to "Identity" for this column.
AndreyR
Devart Team
 
Posts: 2919
Joined: Mon 07 Jul 2008 13:16

Postby tcurtis » Fri 15 Jan 2010 09:25

Hi AndreyR

Thanks for your reply, much appreciated. Unfortunately, I'm still not much clearer. I can change the view for a stored proc or a function but I'm still not clear how to actually map it. The stored proc would take one input parameter, the name of the sequence and return the next value (int64).

I'm not clear what you mean when you refer to inserted primary key etc. I don't have a primary key, I just want to get a sequence which has nothing to do with any table. Is this possible?

Thanks for your help.
tcurtis
 
Posts: 13
Joined: Thu 14 Jan 2010 16:52

Sorted

Postby tcurtis » Fri 15 Jan 2010 11:37

Managed to sort it out by bypassing EF. Just not worth the bother of trying to get EF to do such simple things, I'll wait until the next release and then try again.

I'm opening a seperate connection to get my sequence number and wrapping this connection and my EF connection in a transactionscope so they both participate in the same transaction.

Have to say, EF is pretty sucky and MS need to get their act together fast or people will drop it.
tcurtis
 
Posts: 13
Joined: Thu 14 Jan 2010 16:52

Postby AndreyR » Fri 15 Jan 2010 13:03

There is no way to handle the stored procedure returning value that is not an Entity using the Microsoft Entity Framework v1.
I recommend you to create a stored procedure with out parameter like in the following example:
Code: Select all
CREATE OR REPLACE procedure SCOTT.SELECT_AUTOINC_SEQ (t out number)
AS
BEGIN
  SELECT autoinc_seq.NEXTVAL INTO t FROM DUAL;
  END;

Then add it to your Devart Entity Model in Entity Developer and simply call it from code like in the following code snippet:
Code: Select all
decimal? d = null;
db.SelectAutoincSeq(ref d);
AndreyR
Devart Team
 
Posts: 2919
Joined: Mon 07 Jul 2008 13:16


Return to dotConnect for Oracle