Devart Forums

You can read about some limitations we've found in my blog:
http://blogs.microsoft.co.il/blogs/idof ... racle.aspx

We've implemented devart's solution for declaring SPs for CUD operations (adding a dummy column that will handle the cursor return value of SPs), and we've encountered a problem using except keywords.

When we have an entity with the dummy field, the generated SQL contains a null blob value. When we use an esql statement that uses except, such as :
select value X from entity as X
except
select value T from entity as T where ...

The generated sql is constructed of 2 sql statements with a "Minus" operator, and the sql throws the following error:
"inconsistent datatypes: expected - got BLOB".

We know by now that the problem is having the Blob columns, the question is whether a solution exists for this problem?

Thank in advance,
Ido Flatow.

When you create an eager load with .Include() on more than one entity, the generated query creates a sql part to select null values, and because it created TO_BLOB(NULL) parts instead of HEXTORAW(0) part and this causes exceptions because the union statement tries to union different types of columns.

I tried changing the conceptual type to GUID because I read somewhere that GUID is translated into RAW, but then I got an error message because the conceptual field of the GUID cannot be mapped to a function parameter of type REF CURSOR.

Any workaround for this problem?

Ido.

I tried to write a simple stored procedure to see if I can use it instead of the command text.

I've created the stored procedure with a "ref cursor" out parameter, and checked it with simple OracleCommand to see if it works - and it does.

I then tried to update the edmx file without success:
1. If I declare the ref cursor parameter in the ssdl function, I get an error in the msl because I don't have anything to map that parameter to (no scalar is of ref cursor type)
2. If I don't declare the ref cursor in the parameters, the savechanges fails because the number of parameters in the command and in the SP does not match.

So ... How can you call a stored procedure that needs to set result bindings for an insert/udpate/delete function mappings?

I hope someone will have the answer because otherwise we'll have to get back to using sql server ...

Thanks in advance,
Ido Flatow.

AndreyR wrote:We have sent you a workaround.

Can you please publish an article that describe how to do it?

Thanks,
Ido.

Hi,

I have a model that has a defining query and function mappings for all CRUD operations.

Select works great, but something isn't right in the update function.

My function's command text is quite complex but I've found there to be a problem even when using simple update statements.

Reproduction:
1. Construct a model
2. Replace Entity Set table's name with a defining query of "select * from table"
3. Create a function for the update statement and use a command text element to enter a simple "update table set column = :param1 where id = :id"
4. Apply appropriate modification function mappings

When trying to update I receive an error message : "Store update, insert, or delete statement affected an unexpected number of rows(0)..."

I'm quite sure the update should have worked, and just to make sure, I used some code found on the internet that allows reproducing command that EF sends to the DB. When I intercept that dbcommand and execute it myself using ExecuteNonQuery, it works just find and updates the row.

I've also tried to use RowsAffectedParameter and it didn't work either, and from what i've seen in the generated dbcommand, the RowsAffected parameter has a direction of input instead of output for some reason.

Can you please confirm there is a bug in using modification functions with commandText (rather than with stored procedures)?

I've tried this both on version 5.0.1 and 5.2.


Thanks in advance,
Ido Flatow.

I've tried without success to write function mappings for insert/update commands.

When working with sql server we had a function mapping for the update with a commandtext section that contained a sql statement that does the update/insert and afterwards another select statement for the resultbinding, for example:

-- insert row to history table
insert into ... select ...
-- update the row
update table set ...
-- get the results for the updated row
select current_version, updated_date, ... from table where id = :id


(the current_version, updated_date are resultbinding to update some of the entity's properties).

Can someone give me an example of how I can do this when working with oracle?

I need the command text to be a sql command and not a stored procedure call because I have dynamic issues which SPs will be a hard time to implement.

Thanks in advance,
Ido.

When I have an entity set which is a table, the generated sql is something like:
select "extent1".column1, "extent1".column2 from table "extent1"

But if I use a defining query instead of a table name, the generated sql is something like:
select "extent1"."column1", "extent1"."column2" from (...) "extent1"
and my inner sql statement is something like :
select column1, column2 from table

Why in normal mode there are no double-quotes on the columns and in defnining query there are? This requires me to change all my defining queries to the double-quote format (using aliases).

Is there some way to tell the provider to not use double-quotes?

Thanks in advance,
Ido.