[Mono-bugs] [Bug 573188] New: OracleDataAdapter fills only the first table of the resulting dataSet when invoking a stored procedure with more than one cursor ref out parameters.
bugzilla_noreply at novell.com
bugzilla_noreply at novell.com
Fri Jan 22 14:30:43 EST 2010
http://bugzilla.novell.com/show_bug.cgi?id=573188
http://bugzilla.novell.com/show_bug.cgi?id=573188#c0
Summary: OracleDataAdapter fills only the first table of the
resulting dataSet when invoking a stored procedure
with more than one cursor ref out parameters.
Classification: Mono
Product: Mono: Class Libraries
Version: 2.6.x
Platform: x86-64
OS/Version: Ubuntu
Status: NEW
Severity: Major
Priority: P5 - None
Component: Sys.Data
AssignedTo: mono-bugs at lists.ximian.com
ReportedBy: kuritsu at gmail.com
QAContact: mono-bugs at lists.ximian.com
Found By: ---
Blocker: ---
Created an attachment (id=338403)
--> (http://bugzilla.novell.com/attachment.cgi?id=338403)
Here's a MonoDevelop C# Project with the failing test (modify the connection
string for correct use).
User-Agent: Mozilla/5.0 (X11; U; Linux x86_64; es-MX; rv:1.9.1.5)
Gecko/20091109 Ubuntu/9.10 (karmic) Firefox/3.5.5
I have an Oracle stored procedure (GET_ADMIN_TABLES) with two OUT SYS_REFCURSOR
parameters (CROLES and CUSERS).
After I create an OracleDataAdapter using an OracleCommand with properties
CommandText, CommandType and parameters correctly set, I invoke the Fill method
with an empty created DataSet:
OracleConnection connection = new OracleConnection("Data Source=MYDB;User
ID=user;Password=password");
OracleCommand command = new OracleCommand("GET_ADMIN_TABLES", connection);
command.CommandType = CommandType.StoredProcedure;
OracleParameter p1 = new OracleParameter("CROLES", OracleType.Cursor);
p1.Direction = ParameterDirection.Output;
OracleParameter p2 = new OracleParameter("CUSERS", OracleType.Cursor);
p2.Direction = ParameterDirection.Output;
command.Parameters.AddRange(new OracleParameter[]{p1, p2});
OracleDataAdapter adapter = new OracleDataAdapter(command);
DataSet ds = new DataSet();
adapter.Fill(ds);
After the call, the first table of the resulting dataset (ds) is well formed
and filled, but the other table has the schema of the first table (this is
maybe a clue of what's happening) and it is totally empty.
Reproducible: Always
Steps to Reproduce:
1. In an Oracle database schema, create two tables and fill them like described
by the following script:
CREATE TABLE USERS (USER_ID INTEGER, USER_LOGIN VARCHAR2(50));
CREATE TABLE ROLES (ROLE_ID INTEGER, ROLE_NAME VARCHAR2(50));
INSERT INTO USERS (USER_ID, USER_LOGIN) VALUES (1, 'Jeff');
INSERT INTO USERS (USER_ID, USER_LOGIN) VALUES (2, 'David');
INSERT INTO USERS (USER_ID, USER_LOGIN) VALUES (3, 'Eva');
INSERT INTO ROLES (ROLE_ID, ROLE_NAME) VALUES (1, 'Admin');
INSERT INTO ROLES (ROLE_ID, ROLE_NAME) VALUES (2, 'Tester');
INSERT INTO ROLES (ROLE_ID, ROLE_NAME) VALUES (3, 'Programmer');
2. Create an stored procedure as described below:
CREATE PROCEDURE GET_ADMIN_TABLES(CROLES OUT SYS_REFCURSOR, CUSERS OUT
SYS_REFCURSOR) AS BEGIN OPEN CROLES FOR SELECT * FROM ROLES; OPEN CUSERS FOR
SELECT * FROM USERS; END;
3. In MonoDevelop, create a new C# console application.
4. Add the assembly references System.Data and System.Data.OracleClient.
5. Create a new OracleConnection with a valid connection string, a new
OracleCommand and set it's CommandType and Parameters like the following code:
OracleConnection connection = new OracleConnection("Data Source=MYDB;User
ID=user;Password=password");
OracleCommand command = new OracleCommand("GET_ADMIN_TABLES", connection);
command.CommandType = CommandType.StoredProcedure;
OracleParameter p1 = new OracleParameter("CROLES", OracleType.Cursor);
p1.Direction = ParameterDirection.Output;
OracleParameter p2 = new OracleParameter("CUSERS", OracleType.Cursor);
p2.Direction = ParameterDirection.Output;
command.Parameters.AddRange(new OracleParameter[]{p1, p2});
6. Create a new OracleDataAdapter a fill a new DataSet:
OracleDataAdapter adapter = new OracleDataAdapter(command);
DataSet ds = new DataSet();
adapter.Fill(ds);
7. Save the dataset including the schema to an XML file:
ds.WriteXml("tables.xml", XmlWriteMode.WriteSchema);
Actual Results:
This is the resulting tables.xml:
<?xml version="1.0" standalone="yes"?>
<NewDataSet>
<xs:schema id="NewDataSet" xmlns=""
xmlns:xs="http://www.w3.org/2001/XMLSchema"
xmlns:msdata="urn:schemas-microsoft-com:xml-msdata">
<xs:element name="NewDataSet" msdata:IsDataSet="true"
msdata:UseCurrentLocale="true">
<xs:complexType>
<xs:choice minOccurs="0" maxOccurs="unbounded">
<xs:element name="Table">
<xs:complexType>
<xs:sequence>
<xs:element name="ROLE_ID" type="xs:decimal" minOccurs="0" />
<xs:element name="ROLE_NAME" type="xs:string" minOccurs="0" />
</xs:sequence>
</xs:complexType>
</xs:element>
<xs:element name="Table1">
<xs:complexType>
<xs:sequence>
<xs:element name="ROLE_ID" type="xs:decimal" minOccurs="0" />
<xs:element name="ROLE_NAME" type="xs:string" minOccurs="0" />
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:choice>
</xs:complexType>
</xs:element>
</xs:schema>
<Table>
<ROLE_ID>1</ROLE_ID>
<ROLE_NAME>Admin</ROLE_NAME>
</Table>
<Table>
<ROLE_ID>2</ROLE_ID>
<ROLE_NAME>Tester</ROLE_NAME>
</Table>
<Table>
<ROLE_ID>3</ROLE_ID>
<ROLE_NAME>Programmer</ROLE_NAME>
</Table>
</NewDataSet>
As you can see, this XML doesn't include the right columns and rows from the
USERS table (Table1).
Expected Results:
This is the tables.xml file that the program should output:
<?xml version="1.0" standalone="yes"?>
<NewDataSet>
<xs:schema id="NewDataSet" xmlns=""
xmlns:xs="http://www.w3.org/2001/XMLSchema"
xmlns:msdata="urn:schemas-microsoft-com:xml-msdata">
<xs:element name="NewDataSet" msdata:IsDataSet="true"
msdata:UseCurrentLocale="true">
<xs:complexType>
<xs:choice minOccurs="0" maxOccurs="unbounded">
<xs:element name="Table">
<xs:complexType>
<xs:sequence>
<xs:element name="ROLE_ID" type="xs:decimal" minOccurs="0" />
<xs:element name="ROLE_NAME" type="xs:string" minOccurs="0" />
</xs:sequence>
</xs:complexType>
</xs:element>
<xs:element name="Table1">
<xs:complexType>
<xs:sequence>
<xs:element name="USER_ID" type="xs:decimal" minOccurs="0" />
<xs:element name="USER_LOGIN" type="xs:string" minOccurs="0" />
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:choice>
</xs:complexType>
</xs:element>
</xs:schema>
<Table>
<ROLE_ID>1</ROLE_ID>
<ROLE_NAME>Admin</ROLE_NAME>
</Table>
<Table>
<ROLE_ID>2</ROLE_ID>
<ROLE_NAME>Tester</ROLE_NAME>
</Table>
<Table>
<ROLE_ID>3</ROLE_ID>
<ROLE_NAME>Programmer</ROLE_NAME>
</Table>
<Table1>
<USER_ID>1</USER_ID>
<USER_LOGIN>Jeff</USER_LOGIN>
</Table1>
<Table1>
<USER_ID>2</USER_ID>
<USER_LOGIN>David</USER_LOGIN>
</Table1>
<Table1>
<USER_ID>3</USER_ID>
<USER_LOGIN>Eva</USER_LOGIN>
</Table1>
</NewDataSet>
As you can see, this XML includes the right columns and rows from the USERS
table (Table1).
The C# MonoDevelop project attached is an example of failing test. The
connection string of the OracleConnection object must be changed to a proper
one to run the program successfully.
--
Configure bugmail: http://bugzilla.novell.com/userprefs.cgi?tab=email
------- You are receiving this mail because: -------
You are the QA contact for the bug.
You are the assignee for the bug.
More information about the mono-bugs
mailing list