Empty DataReader when SQL statement contains a join — oracle-tech

    Forum Stats

  • 3,716,000 Users
  • 2,242,928 Discussions
  • 7,845,734 Comments

Discussions

Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

Empty DataReader when SQL statement contains a join

65350fd4-571b-4896-b003-707aa958290d
edited May 2019 in ODP.NET

I am using Oracle.ManagedDataAccess.Core v2.18.6 and I am experiencing an issue when executing a command that includes joins to other tables. I've tried different join syntaxes, etc., and have confirmed joining any table causes the datareader to be empty (.HasRows = false)
I even took the exact same statements and ran them in Oracle SQL Developer and rows come back. What am I missing?

string oradb = "connection string info here";
OracleConnection conn = new OracleConnection(oradb); // C#
conn.Open();
OracleCommand cmd = new OracleCommand();
cmd.Connection = conn;

//if I select from one table the data reader contains data

cmd.CommandText = "select * from patients where personal_num=1";

//if I select from parent and child table data reader does not contain data
cmd.CommandText = "select a.* from patients a inner join patient_history b on a.personal_num = b.personal_num where personal_num=1"

cmd.CommandType = CommandType.Text;
OracleDataReader dr = cmd.ExecuteReader();
dr.Read();
label1.Text = dr.GetString(0);
conn.Dispose();

65350fd4-571b-4896-b003-707aa958290d

Best Answer

  • 65350fd4-571b-4896-b003-707aa958290d
    edited May 2019 Accepted Answer

    Interestingly... the reason results were showing in SQL developer is I had just ran an insert into the history table but did not commit. The subsequent select must have been reading the uncommitted records. The .NET client was getting 0 rows back.

    UGH! I am a SQL Server guy used to implicit transactions.

    My apologies, thanks for the response!

Answers

  • AndrewSayer
    AndrewSayer Member Posts: 12,998 Gold Crown
    edited May 2019
    65350fd4-571b-4896-b003-707aa958290d wrote:I am using Oracle.ManagedDataAccess.Core v2.18.6 and I am experiencing an issue when executing a command that includes joins to other tables. I've tried different join syntaxes, etc., and have confirmed joining any table causes the datareader to be empty (.HasRows = false) 
    I even took the exact same statements and ran them in Oracle SQL Developer and rows come back. What am I missing?string oradb = "connection string info here";
    OracleConnection conn = new OracleConnection(oradb); // C#
    conn.Open();
    OracleCommand cmd = new OracleCommand();
    cmd.Connection = conn;

    //if I select from one table the data reader contains datacmd.CommandText = "select * from patients where personal_num=1";
    //if I select from parent and child table data reader does not contain data
    cmd.CommandText = "select a.* from patients a inner join patient_history b on a.personal_num = b.personal_num where personal_num=1"
    cmd.CommandType = CommandType.Text;
    OracleDataReader dr = cmd.ExecuteReader();
    dr.Read();
    label1.Text = dr.GetString(0);
    conn.Dispose();

    Personal_num in your query could refer to either patients or patient_history tables so I would expect this query would fail to parse and you should get an exception. Are you sure you’ve run the exact same query in sql Developer?

  • 65350fd4-571b-4896-b003-707aa958290d
    edited May 2019

    Yes, I fully qualified it but neglected to reflect that in my post. No exception is being thrown. The datareader I get back is empty. All other properties are fine... The only difference it the SQL is a terminating semi-colon. I do not use it from the oracle data client.

  • 65350fd4-571b-4896-b003-707aa958290d
    edited May 2019 Accepted Answer

    Interestingly... the reason results were showing in SQL developer is I had just ran an insert into the history table but did not commit. The subsequent select must have been reading the uncommitted records. The .NET client was getting 0 rows back.

    UGH! I am a SQL Server guy used to implicit transactions.

    My apologies, thanks for the response!

  • AndrewSayer
    AndrewSayer Member Posts: 12,998 Gold Crown
    edited May 2019
    65350fd4-571b-4896-b003-707aa958290d wrote:Interestingly... the reason results were showing in SQL developer is I had just ran an insert into the history table but did not commit. The subsequent select must have been reading the uncommitted records. The .NET client was getting 0 rows back.UGH! I am a SQL Server guy used to implicit transactions.My apologies, thanks for the response!

    Implicit transactions aren’t so necessary when writers can’t block readers and readers cant block writers

    65350fd4-571b-4896-b003-707aa958290d
Sign In or Register to comment.