Page 1 of 1
Best practice for multiple result sets?
Posted: Mon 19 Feb 2007 20:07
by tag2453
We need to support both SQL Server and Oracle with our application.
In SQL Server we have stored procedures that return multiple resultsets used to return 2 datatables such as order information and the order line items records.
In Oracle we have a package with 2 REF Cursors to return the same 2 resultsets. Schemas and data are the same in both system.
Can you provide some example code or information on how to best code this scenario using unidirect to retrieve the data from either database without having to change any of the application code?
Posted: Tue 20 Feb 2007 06:46
by Alexey
Please provide us with your databases' objects definitions.
Posted: Tue 20 Feb 2007 22:42
by tag2453
Okay here's a rough example...
CREATE TABLE [Store_Order](
[orderID] [int] IDENTITY(1,1) NOT NULL,
[orderNumber] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[orderDate] [smalldatetime] NOT NULL CONSTRAINT [DF_CMRC_Orders_OrderDate_1] DEFAULT (getdate()),
[orderStatusID] [int] NOT NULL CONSTRAINT [DF_CMRC_Orders_OrderStatusID] DEFAULT ((0)),
[userName] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[email] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[firstName] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[lastName] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[shippingMethod] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[subTotalAmount] [money] NOT NULL CONSTRAINT [DF_CMRC_Orders_OrderSubTotal] DEFAULT ((0)),
[shippingAmount] [money] NOT NULL CONSTRAINT [DF_CMRC_Orders_Shipping_1] DEFAULT ((0)),
[handlingAmount] [money] NOT NULL CONSTRAINT [DF_CMRC_Orders_HandlingAmount] DEFAULT ((0)),
[taxAmount] [money] NOT NULL CONSTRAINT [DF_CMRC_Orders_Tax_1] DEFAULT ((0)),
[taxRate] [numeric](18, 0) NOT NULL CONSTRAINT [DF_CMRC_Orders_taxRate] DEFAULT ((0)),
[couponCodes] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[discountAmount] [money] NOT NULL CONSTRAINT [DF_CMRC_Orders_discountAmount] DEFAULT ((0)),
[shipToAddress] [nvarchar](500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[billToAddress] [nvarchar](500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
CONSTRAINT [PK_Store_Order] PRIMARY KEY CLUSTERED
(
[orderID] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
CREATE TABLE [Store_OrderItem](
[orderItemID] [int] IDENTITY(1,1) NOT NULL,
[orderID] [int] NOT NULL,
[productID] [int] NOT NULL,
[sku] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[productName] [nvarchar](250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[quantity] [int] NOT NULL,
[originalPrice] [money] NOT NULL,
[pricePaid] [money] NOT NULL,
[isShipped] [bit] NOT NULL CONSTRAINT [DF_CSK_Store_OrderItem_isShipped] DEFAULT ((0)),
[shipDate] [datetime] NULL,
CONSTRAINT [PK_CMRC_OrderItems] PRIMARY KEY CLUSTERED
(
[orderItemID] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [Store_OrderItem] WITH CHECK ADD CONSTRAINT [FK_Store_OrderItem_Store_Order] FOREIGN KEY([orderID])
REFERENCES [Store_Order] ([orderID])
Create Procedure GetOrderInfo(@OrderID int)
as
Select * from Store_Order where orderID = @OrderID
Select * from Store_OrderItem where orderID = @OrderID
Oracle:
CREATE OR REPLACE PROCEDURE GETORDERINFO(
OrderID IN Number,
RCORDER IN OUT sys_RefCursor,
RCDETAILS IN OUT sys_RefCursor)
AS
BEGIN
Open RCORDER for
Select * from SA.CSK_Store_Order where CSK_Store_Order.orderID = OrderID;
Open RCDetails for
Select * from SA.CSK_Store_OrderItem where CSK_Store_OrderItem.orderID = OrderID;
END GETORDERINFO;
Now I need to know how to write one function in my data access layer that retruns a dataset with these 2 tables from either database depending on which db I specify to use in config file.
Thanks...
Posted: Wed 21 Feb 2007 10:42
by Alexey
Take a look at the following code snippet:
Code: Select all
DataSet RetrieveResults(UniConnection uniConnection, string procedureName, int id) {
if (uniConnection.State != ConnectionState.Open)
uniConnection.Open();
UniCommand uniCommand = uniConnection.CreateCommand();
uniCommand.CommandType = CommandType.StoredProcedure;
uniCommand.CommandText = procedureName;
UniCommandBuilder.DeriveParameters(uniCommand);
uniCommand.Parameters["OrderID"].Value = id;
UniDataReader reader = uniCommand.ExecuteReader();
DataSet results = new DataSet();
DataTable table1 = new DataTable("Result1");
table1.Load(reader); // reader.NextResult is called implicitly
results.Tables.Add(table1);
DataTable table2 = new DataTable("Result2");
table2.Load(reader);
results.Tables.Add(table2);
return results;
}
private void TestButton_Click(object sender, EventArgs e) {
using (UniConnection connection = new UniConnection(OracleConnStr)) {
dataGrid1.DataSource = RetrieveResults(connection, "scott..GetOrderInfo", 34);
connection.ConnectionString = SqlConnStr;
dataGrid2.DataSource = RetrieveResults(connection, "GetOrderInfo", 20);
}
}
The connection strings are:
Code: Select all
const string OracleConnStr = "Provider=Oracle;data source=ora; user id=scott; pwd=tiger";
const string SqlConnStr = "Provider=Sql Server;data source=server; user id=sa; database=test";
Posted: Wed 21 Feb 2007 18:41
by tag2453
That looks great and exactly what I was hoping for...
So if I understand this correctly then the DeriveParameters method is automatically figuring out that when using Oracle, the 2 output parameters are ref cursors, and automatically creating datareaders from them so NextResult can be used? Can you explain this a little further...
Posted: Fri 23 Feb 2007 07:49
by Alexey
DeriveParameters method populates the specified uniCommand's Parameters collection with parameter information for the stored procedure specified in the CommandText. Then datareader is created using ExecuteReader method of the uniCommand object. NextResult method is called by firing DataTable.Load(IDataReader) method.
Posted: Tue 31 Mar 2009 12:12
by rcmetta
Hi,
I need to return multiple result sets from an oracle stored procedure. the number of result sets is dynamic. I sent a list of customer names to the procedure and for each customer, I send a list of fields to be retrieved. the procedure should execute one query for each customer and return the data. my front end is in VB6. Can you please let me know what is the process for this?
rcm
Posted: Wed 01 Apr 2009 13:06
by Shalex
1. The next result set can be accessed with the help of the NextResult() method of the UniDataReader object.
2. In Oracle the amount of the returned result sets, received like this, equals to the amount of the ref cursor parameters that are declared only once when creating the procedure. Therefore you can not change the amount of the result sets this way.
3. Take a look at the following implementations of your task:
a) stored procedure receives the only customer name and returns the fixed amount of the result sets for it. If you want to obtain the whole set of necessary result sets, call this procedure in a loop changing its parameters.
b) return the only result set for the whole list of customers and work with it using the Read() method of the UniDataReader object.
dbxpress best practice for multiple result sets
Posted: Thu 14 May 2009 05:49
by BenW
Hi I have a MSSQL stored proc that returns multiple result sets. What is the best practice using dbexpsda40.dll?
Posted: Thu 14 May 2009 08:49
by Plash
dbExpress has some bugs in support of multiple result sets. So it is recommended to create a separate stored procedure for each result set.
But you can try the following code:
Code: Select all
SQLStoredProc1.Open;
while not SQLStoredProc1.Eof do begin
a := SQLStoredProc1.Fields[0].Value;
SQLStoredProc1.Next;
end;
SQLStoredProc1.NextRecordSet;
while not SQLStoredProc1.Eof do begin
a := SQLStoredProc1.Fields[0].Value;
SQLStoredProc1.Next;
end;
SQLStoredProc1.Close;
Posted: Thu 14 May 2009 22:22
by BenW
Thanks for the prompt reply. Do you have any idea which version of dbExpress will support multiple result sets?
Posted: Fri 15 May 2009 08:01
by Plash
This code works in Delphi 6 and Delphi 7.
But new versions of Dephi - 2007 and 2009 - have a bug with the NextRecordSet method. Please contact CodeGear support to fix this bug.