I can't seem to figure out how to do the mapping for this simple scenario because of the way resultsets are returned differently between Oracle and Sql Server, which lead to different number of arguments between 2 versions of the same stored procedure.

Please give me details on on you would do this yourself.

Many thanks!

Here is the SP on Sql Server:

CREATE PROCEDURE dbo.GetOrders (@customerId integer) AS select * from Orders where CustomerId=@customerId; go

Here is the Oracle one:

create or replace package db.types as type cursorType is ref cursor; end; /

create or replace procedure GetOrders(customerId in integer, p_cursor in out types.cursorType) as begin open p_cursor for select * from Orders where CustomerId=customerId;; end; /

• What is the generated signature method in both cases?

• What is your LLBLGen version and Runtime Library version? (http://www.llblgen.com/TinyForum/Messages.aspx?ThreadID=7725 )

daelmo, What I tried to say is that I am not sure what is the good, right way to do given the example I described earlier with regarding to calling a stored procedure that returns a set of tuples (resultset) because Sql Server and Oracle return them differently (e.g., we have to use cursor for Oracle, but not Sql Server).

If I use the signature in example 1) below I cannot map it for Oracle since Oracle requires cursor to return the resultset

If I use the signature in example 2) below I cannot map it for Sql Server since Sql Server does not use cursor for returning resultsets

Note that both 1) and 2) do the same thing; I am looking for a generic way to call a procedure that will work for both sql Server and Oracle. Does this make sense?

Thank you.

I can't seem to figure out how to do the mapping for this simple scenario because of the way resultsets are returned differently between Oracle and Sql Server, which lead to different number of arguments between 2 versions of the same stored procedure.

Please give me details on on you would do this yourself.

Many thanks!

1) Here is the SP on Sql Server:

CREATE PROCEDURE dbo.GetOrders (@customerId integer) AS select * from Orders where CustomerId=@customerId; go

2)Here is the Oracle one:

create or replace package db.types as type cursorType is ref cursor; end; /

create or replace procedure GetOrders(customerId in integer, p_cursor in out types.cursorType) as begin open p_cursor for select * from Orders where CustomerId=customerId;; end; /

Are you going to use the same generated code on these 2 databases at the same time. i.e. DBGeneric Entities targeting 2 DBSpecific projects (one for each DB Type), that's why you need to make sure the same SP on both Dbs are going to be mapped to exactly the same FunctionCall?

AFAIK, both will map to the same .NET calling code.

Are you going to use the same generated code on these 2 databases at the same time. a> yes, that is exactly what I am trying to do

i.e. DBGeneric Entities targeting 2 DBSpecific projects (one for each DB Type), that's why you need to make sure the same SP on both Dbs are going to be mapped to exactly the same FunctionCall? a> right, exactly

AFAIK, both will map to the same .NET calling code. a> not sure I know how, please give me an example for how to to this (maybe a simple .llblgenpro file). I look for a general way to map a SP to both Sql Server and Oracle, let say it take one input int argument (e.g., Id) and return a set of tuples/result set.

Many thanks.

Please look in the forum for StoredProcedureCall with Oracle.

You need to take the first step and create a PoC, and if it fails, we'll be happy to help you out.

But it's too simple, both the SQL SP and the OracleSP you have posted will be yield the same SPCall signature, the cursor parameter isn't going to appear in the signature, if that's what you are afraid of.

Thanks, I was confused by the GUI and did not look carefully. Here are the steps:

1) run Refresh Relational Model Data From a Database to import my sql server and Oracle schema both (each has the SP I want to call)

2) select the Sql Server SP in Catalog Explorer and run Create Stored Procedure Call Definition to create call for Sql Server (this worked perfectly)

3) bring up the editor on the SP call (for Sql Server) created in step 2, switch "Database type" to Oracle from the drop down, click on "Create mapping" button

In step 3 above, after clicking on "Create mapping" button the editor showed the mapping for an input argument with "Target element name" empty (with the red cross). When I selected the input argument in the editor, the "Target element details" panel was empty/grayed out, clicked on the empty cell under "Target element name" showed nothing. NOTHING I COULD DO.... NOTHING until I selected the Oracle target from from the Target dropdown (I was assuming that a new target would be created for Oracle and the "Target" drop down would be switched to it automatically since I just selected Oracle for "Database type").

The confusion was probably caused by the designer showing the input mappings without a target selected. I thought you might like to correct this issue.

Once I figured this out, everything worked perfectly

Good you figured it out. And it's not a bug, the thing is that you can create your own mappings, map to an existing table, a new one, etc. You can go to Project->'Auto-map unmapped entities' to do this autmatically.

Sorry, false alarm, the mappings did not work did not work, so I could not generate any code at all ( 2 days on this already, please).

Here is my sql server SP, having 1 input arg and return 1 resultset:

CREATE PROCEDURE dbo.GetChildrenInHierarchy1 (@parentId integer) AS DECLARE @path VARCHAR(1024) DECLARE @id int DECLARE @parent_path VARCHAR(1024) DECLARE @level int

with pathlist as ( select parent.Path, parent.Id, parent.ParentPath, 1 as level from Entity as parent where parent.Id = @parentId union all select child.Path, child.Id, child.ParentPath, el.level + 1 from Entity as child inner join pathlist as el on child.ParentPath = el.Path where child.ParentPath is not null ) select * from pathlist;

go

Here is my Oracle SP, the parameter list has 1 input arg and returning 1 resultset:

create or replace procedure "Test"."GetChildrenInHierarchy1"(parentId in integer, p_cursor out SYS_REFCURSOR) as begin

open p_cursor for SELECT "Path", "Id", "ParentPath", level FROM "EntityHeader" START WITH "ParentPath" IS NULL CONNECT BY PRIOR "Path" = "ParentPath";

end; /

The problem is that when I created the SP call by using the Sql Server SP (has 1 input arg) then trying to map it to the Oracle target (has 2 args in the argument list) LLBL failed to validate, complaining I only mapped 1 out of 2 required args. Please see attachment.

The reverse, creating the SP call by using the Oracle SP also failed.

Thanks

The way SPs are mapped on your model cannot be changed. However the generated code is the same.

Let's start over on the final result you want. I think that what you want is to have one DBGeneric project (that with entities, collections, etc) and two DBSpecific project (the project with the persistence info that connects to DB): one for Oracle and one for SQLServer.

To achieve that you have to: 1. Create a LLBLGen project, initially from your SQLServer DB, then generated code. Grab your DBGeneric and DBSpecific project somewhere. 2. Go back to your LLBLGen project, delete the SQLServer catalog and and your Oracle one. Re-map all the entities to the Oracle targets. 3. For SPs you will have to delete them and re-add them again. This way your LLBLGen project will be validated. 4. Generate code and grab the generated projects somewhere. Now you have two DBGeneric projects that are exactly the same and two DBSpecific projects, one for SQLServer and one for Oracle. 5. Use one DBGeneric project and the two DBSpecific and now you have an application that can connect to both Oracle and SQLServer with the same Model (DBGeneric). The signatures of SPs calls should be equal as well, so for your Application, it's transparently.

Similar thread: http://www.llblgen.com/TinyForum/Messages.aspx?ThreadID=20481&StartAtMessage=0&#115410