With statement

[jbm417]

Hi,

I am creating a c# application which allows the user to enter sql. I have a problem when executing a with statement; e.g.

WITH x
AS (
SELECT level id
FROM dual
connect BY level <= 10
)
SELECT * FROM x

There are 0 rows returned for some strange reason.

I am using the ODBC driver & here is the code snippet.

myConnection = new OdbcConnection(ConStr);
myCommand = new OdbcCommand(SQL, myConnection);
myCommand.Connection.Open();

myReader = myCommand.ExecuteReader(CommandBehavior.CloseConnection);

for (int i = 0; i < myReader.FieldCount; i++)
{
listData.Columns.Add(myReader.GetName(i), 100, HorizontalAlignment.Center);
}

while (myReader.Read()) // reads rows
{
//if (!myReader.IsDBNull(0))
listData.Items.Add(myReader.GetValue(0).ToString());

for (int i = 1; i < myReader.FieldCount; i++) // reads columns
{
listData.Items[dispInd].SubItems.Add(myReader.GetValue(i).ToString());
}

Any comments/thought/help will be much appreciated. TIA, Joe

 

[SantaMufasa]

JBM,

Your code should work, as it does for me here:

WITH x 
AS (
 SELECT level id
 FROM  dual
connect BY level <= 10
)
 SELECT *  FROM x
/

        ID
----------
         1
         2
         3
         4
         5
         6
         7
         8
         9
        10

But if someone goofed up the contents of the "dual" table (such as deleting the one row in the dual table), then it will cause the effect you mention:

SQL> create table test.dual as select * from sys.dual;

Table created.

SQL> delete from dual;

1 row deleted.

SQL> select * from dual;

no rows selected

SQL> WITH x 
  2  AS (
  3   SELECT level id
  4   FROM  dual
  5  connect BY level <= 10
  6  )
  7   SELECT *  FROM x;

no rows selected.

So, to determine if you are experiencing the problem I illustrate, above, please issue this command:

SELECT * FROM DUAL;
(Result A -- Proper response):
D
-
X

1 row selected.
(Result B -- Error condition):
no rows selected

Let us know your findings.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”

 

[jbm417]

I did the select * from dual & got the correct result.

I also ran

SELECT level id
FROM dual
connect BY level <= 10

and get the expected 10 rows. It is just when I add the
WITH part that I get no rows returned by my app.

Just FYI, the entire WITH sql runs fine if I use Oracle's
user interface aka SQL Commands.

I know it is something related to my app, but just don't know what.

Thanks, Joe

 

[SantaMufasa]

Then, yes, if everything works fine from Oracle's user interface (e.g., SQL*Plus), then you have at least isolated the problem to your non-Oracle app.

Must you use the "WITH" construct in your app? Won't it allow you to use the in-line view that works properly?

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”

 

[oradba101]

Greetings,

Are you using the Oracle10g driver from Oracle. The MS one may not support this.

Regards,



William Chadbourne
Programmer/Analyst

 

[jbm417]

Thanks to both of you for the replies.

Yes, the app must support the user entering any valid SQL so I don't have the option to only allow an inline view.

BTW, the inline view flavor of the above problem script does work fine.

The ODBC driver is the MS flavor, so this seems the most likely culprit.

Guess the next step is to build a small test case with a different driver.

Thx again.

 

[jbm417]

OK, I created a simple test case and the results are:

Only OracleDataReader supports the WITH statement.

Neither ODBC nor OLE returned any results.