Problem with empty result from storeprocedure

Problem with empty result from storeprocedure

Postby Trixz » Wed 02 Jun 2010 11:15

Hi

I have a WinForm application with a OracleDataTable (called dt from now on). The dt is configured to call a storeprocedure and when a button is pressed i do the followin


Code: Select all
dt.Active = false;
dt.SelectCommand.Parameters["InParam"].Value = editBox.Text;
dt.Select();
dt.FetchAll = true;
dt.Active = true;

myGrid.DataSource = dt;


This works fine, as long as the store procedure returns any rows. When the sp returns a cursor with only columnheaders (no rows) the dt still have the same row as before the command.

I've tried dt.Clear() and dt.Rows.Clear()
dt.Clear() resultet in a errormessage saying that the datareader was closed when doing the 3rd call to datatable.
dt.Rows.Clear() cleared the rows, but when the active = true was set the old result was loaded back into the dt.

It seems like the problem lies in that the dt has a datasource behind and when the storeprocedure returns en empty result, the datasource is not cleared and refilled.

What I'm I doing wrong?
Trixz
 
Posts: 31
Joined: Thu 30 Apr 2009 07:59

Postby Shalex » Wed 02 Jun 2010 15:12

Setting the Active property to false does not clear the data, but frees the resources allocated on the server, if any. Please try the following code:
Code: Select all
dt.Clear();
dt.SelectCommand.Parameters["p1"].Value = editBox.Text;         
dt.Active = true;
myGrid.DataSource = dt;


If the problem persists, please specify your current version (x.xx.xxx) of dotConnect for Oracle (the Tools>Oracle>About menu of VS) and the exact Grid control you are using (is it System.Windows.Forms.DataGridView?).
Shalex
Devart Team
 
Posts: 7392
Joined: Thu 14 Aug 2008 12:44

Postby Trixz » Wed 02 Jun 2010 15:41

I made an example project....
DevArt version 5.0.40.0

I run the project and clear does not work.
The first time I press the button and set the selectparameter, I get the result.
Second, also a result (it work even if it's empty, thats a progress)
But the third time I always get this exception.

Code: Select all
An unhandled exception of type 'System.InvalidOperationException' occurred in Devart.Data.dll

Additional information: Invalid attempt to GetSchemaTable when reader is closed.


I use the oracledatatable control from toolbar to the form and configure it.
Here is some code generated
Code: Select all
            //
            // oracleDataTable1
            //
            this.oracleDataTable1.Columns.AddRange(new System.Data.DataColumn[] {
            this.oracleDataTable1_LANDKOD,
            this.oracleDataTable1_LAND_BENAMNING,
            this.oracleDataTable1_ORDERNR,
            this.oracleDataTable1_ORDERFAKTURASTATUS_ID,
            this.oracleDataTable1_ORDERFAKTURASTATUS_BENAMNING,
            this.oracleDataTable1_ORDERLEVERANSSTATUS_ID,
            this.oracleDataTable1_ORDERLEVERANSSTATUS_BENAMNING,
            this.oracleDataTable1_VARREFERENS,
            this.oracleDataTable1_BETALNINGSVILLKOR_ID,
            this.oracleDataTable1_BETALNINGSVILLKOR_VILLKOR,
            this.oracleDataTable1_LAGERSTALLE_ID,
            this.oracleDataTable1_LAGERSTALLE_BENAMNING,
            this.oracleDataTable1_FRAKT,
            this.oracleDataTable1_ANTAL_SLUTKUNDER,
            this.oracleDataTable1_TOTAL_VOLYM,
            this.oracleDataTable1_TOTAL_VIKT,
            this.oracleDataTable1_CHECKPREMIEVAL,
            this.oracleDataTable1_ORDERVARDEFP,
            this.oracleDataTable1_PROVISION,
            this.oracleDataTable1_ORDERVARDE,
            this.oracleDataTable1_BIDRAG,
            this.oracleDataTable1_BUNTNR,
            this.oracleDataTable1_BUNTNRSMART,
            this.oracleDataTable1_PRIORITET_ID,
            this.oracleDataTable1_PRIORITET_BENAMNING,
            this.oracleDataTable1_ORDERTYP_ID,
            this.oracleDataTable1_ORDERTYP_BENAMNING,
            this.oracleDataTable1_ORDERDATUM,
            this.oracleDataTable1_ORDER_PLATS});
            this.oracleDataTable1.Connection = this.oracleConnection1;
            this.oracleDataTable1.Name = "oracleDataTable1";
            this.oracleDataTable1.SelectCommand = this.oracleSelectCommand1;
            this.oracleDataTable1.TableName = "oracleDataTable1";
            this.oracleDataTable1.Owner = this;
            this.oracleDataTable1.TableMapping.DataSetTable = "oracleDataTable1";
            this.oracleDataTable1.TableMapping.SourceTable = "oracleDataTable1";


            //
            // oracleSelectCommand1
            //
            this.oracleSelectCommand1.CommandType = System.Data.CommandType.StoredProcedure;
            this.oracleSelectCommand1.CommandText = "jfp.jfp_forms.orderinfo_getorders";
            this.oracleSelectCommand1.Connection = this.oracleConnection1;
            this.oracleSelectCommand1.Name = "oracleSelectCommand1";
            this.oracleSelectCommand1.Parameters.Add(new Devart.Data.Oracle.OracleParameter("IN_LANDKOD", Devart.Data.Oracle.OracleDbType.VarChar, 2, System.Data.ParameterDirection.Input, false, ((byte)(0)), ((byte)(0)), "", System.Data.DataRowVersion.Current, "SE"));
            this.oracleSelectCommand1.Parameters.Add(new Devart.Data.Oracle.OracleParameter("IN_KUNDNR", Devart.Data.Oracle.OracleDbType.Number, 0, System.Data.ParameterDirection.Input, false, ((byte)(0)), ((byte)(0)), "", System.Data.DataRowVersion.Current, null));
            this.oracleSelectCommand1.Parameters.Add(new Devart.Data.Oracle.OracleParameter("OUT_C", Devart.Data.Oracle.OracleDbType.Cursor, 0, System.Data.ParameterDirection.Output, false, ((byte)(0)), ((byte)(0)), "", System.Data.DataRowVersion.Current, null));
            this.oracleSelectCommand1.TableValuedResultType = null;
            this.oracleSelectCommand1.Owner = this;



Code: Select all
            //
            // oracleDataTable1_LANDKOD
            //
            this.oracleDataTable1_LANDKOD.AllowDBNull = false;
            this.oracleDataTable1_LANDKOD.ColumnName = "LANDKOD";
            //
            // oracleDataTable1_LAND_BENAMNING
            //
            this.oracleDataTable1_LAND_BENAMNING.ColumnName = "LAND_BENAMNING";
            //
            // oracleDataTable1_ORDERNR
            //
            this.oracleDataTable1_ORDERNR.AllowDBNull = false;
            this.oracleDataTable1_ORDERNR.ColumnName = "ORDERNR";
            this.oracleDataTable1_ORDERNR.DataType = typeof(double);
            //
            // oracleDataTable1_ORDERFAKTURASTATUS_ID
            //
            this.oracleDataTable1_ORDERFAKTURASTATUS_ID.ColumnName = "ORDERFAKTURASTATUS_ID";
            this.oracleDataTable1_ORDERFAKTURASTATUS_ID.DataType = typeof(double);
            //
            // oracleDataTable1_ORDERFAKTURASTATUS_BENAMNING
            //
            this.oracleDataTable1_ORDERFAKTURASTATUS_BENAMNING.ColumnName = "ORDERFAKTURASTATUS_BENAMNING";

...
Trixz
 
Posts: 31
Joined: Thu 30 Apr 2009 07:59

Postby Trixz » Wed 02 Jun 2010 15:43

Version update: 5.35.79.0

I used the devexpress control originaly, but sample made with standard System.Window.Forms.DataGridView
Trixz
 
Posts: 31
Joined: Thu 30 Apr 2009 07:59

Postby Shalex » Thu 03 Jun 2010 11:15

We have reproduced the "Invalid attempt to GetSchemaTable when reader is closed." error with the latest (5.70.140) build of dotConnect for Oracle. It is fixed. Look forward to the next build. I will post here when it is available for download. As a temporary workaround, please use the Close() method. This method disconnects DbDataTable from the data source and frees server resources allocated for the query.
Code: Select all
private void button1_Click(object sender, EventArgs e) {
    //dt.Clear();
    dt.Close();
    //the next line overcomes the issue when DataGridView tracks the table state NOT correctly
    myGrid.DataSource = null;
    dt.SelectCommand.Parameters["p1"].Value = editBox.Text;
    dt.Active = true;
    myGrid.DataSource = dt;
}
Shalex
Devart Team
 
Posts: 7392
Joined: Thu 14 Aug 2008 12:44

Postby Trixz » Fri 18 Jun 2010 14:21

This fixed the schema problem... but there is another problem that may be related, so I post in in same thread.

I have a storeprocedure. When I call it with parameter "A" set, (B and C is empty) it returns a resultset with 1 row.
I change parameter "A" to a new value, another row is returned.

I clear parameter "A" and set "B". Tests of the storeprocedure returns 20 rows, but the datatable holds the result from "A" still.



Did the other way around. Started with "B", 20 rows returned. Changed "B", new 15 rows displayed. Tested to clear the "B" parameter, and set the "A", the single row was displayed. Tried to change back, datatable only has the "A".


As you probably can figure out, A is a primary key and always returns 1 row, B is for example a name.
I figured that this may have something to do with the earlier problem and the generated schema.

Between all searches I do a

Code: Select all
dtSearchAgent.Close();
gridAgentList.DataSource = null;
dtSearchAgent.SelectCommand.Parameters["IN_KUNDNR"].Value = txtSearchAgent.Text;
dtSearchAgent.SelectCommand.Parameters["In_SaljareFornamn"].Value = txtSearchFirstname.Text;
dtSearchAgent.Active = true;
gcAgentList.DataSource = dtSearchAgent;


When doing test against the storeprocedure from pl/sql it works fine.
the storeprocedure sends a ref_cursor out.
Trixz
 
Posts: 31
Joined: Thu 30 Apr 2009 07:59

Postby Shalex » Tue 22 Jun 2010 11:59

Thank you for your report. We have reproduced the problem.
Shalex
Devart Team
 
Posts: 7392
Joined: Thu 14 Aug 2008 12:44

Postby Shalex » Wed 23 Jun 2010 09:59

The problem is fixed. Look forward to the next build. As a temporary workaround, please use the following code:
Code: Select all
dtSearchAgent.Close();
gridAgentList.DataSource = null;
//assuming that KUNDNR is your primary key
dtSearchAgent.SelectCommand.Parameters["IN_KUNDNR"].Value = (txtSearchAgent.Text == "") ? null : txtSearchAgent.Text;
dtSearchAgent.SelectCommand.Parameters["In_SaljareFornamn"].Value = txtSearchFirstname.Text;
dtSearchAgent.Active = true;
gcAgentList.DataSource = dtSearchAgent;
Shalex
Devart Team
 
Posts: 7392
Joined: Thu 14 Aug 2008 12:44

Postby Shalex » Wed 07 Jul 2010 17:53

New build of dotConnect for Oracle 5.70.146 is available for download now!
It can be downloaded from http://www.devart.com/dotconnect/oracle/download.html (trial version) or from Registered Users' Area (for users with valid subscription only).
For more information, please refer to http://www.devart.com/forums/viewtopic.php?t=18424 .
Shalex
Devart Team
 
Posts: 7392
Joined: Thu 14 Aug 2008 12:44


Return to dotConnect for Oracle