Query problem - rows are returned when query is run in sql navigator , but not in my c# program

Question

enter image description here

Update:

This is the query from the debugger, which was retrieved from a string builder:

{SELECT * FROM FCR.V_REPORT WHERE DATE BETWEEN to_date('14/09/2001' , 'dd/mm/yyyy') AND to_date('30/09/2011' , 'dd/mm/yyyy')}

If you remove the curly brackets and post it in Navigator, it works.

Original:

I have a problem when running my program. The query in sql navigator returns 192 rows but when I run the query on c#(visual studio 2010) the query returns 0 rows. Below is my c# code:

public static DataTable GetReport(string date1, string date2)
{
  DatabaseAdapter dba = DatabaseAdapter.GetInstance();
  string SqlQuery =
    string.Format(@"SELECT * 
                  FROM FCR.V_REPORT 
                  WHERE DATE BETWEEN to_date('{0}' , 'dd/mm/yyyy')
                    AND to_date('{1}' , 'dd/mm/yyyy')", date1, date2);
  OracleDataReader reader = dba.QueryDatabase(SqlQuery);
  DataTable dt = new DataTable();
  dt.Load(reader);
  int temp = dt.Rows.Count;
  return dt;
}

This is the query I am using in sql navigator(which returns 192 rows):

SELECT * 
FROM FCR.V_REPORT
WHERE DATE BETWEEN to_date('01/01/2001' , 'dd/mm/yyyy')
AND to_date('30/09/2011' , 'dd/mm/yyyy')

can you paste the actual value of SqlQuery (I mean, can you past the result of the string.Format call)? — Steve B
– Steve B, Commented Sep 9, 2011 at 9:06
SqlQuery is null... get it after the call of string.Format, and please, post the value of the variable, not a screenshot of the debugger. — Steve B
– Steve B, Commented Sep 9, 2011 at 9:18
@mikespiteri how should we know what the query is if your returning the locals tab before sqlQuery being created? that dumb my friend... — cgval
– cgval, Commented Sep 12, 2011 at 11:26
Probably a stupid question, but are you using the same oracle login details in your C# vs. navigator? — mrcrowl
– mrcrowl, Commented Sep 15, 2011 at 23:00

Icarus · Accepted Answer · 2011-09-09 09:04:58Z

5

I bet you that the dates passed in from your c# program are different because your sql statement is identical. Put a break point and verify that the dates are exactly the same. Also verify that date1 and date2 are passed in in the appropriate order.

answered Sep 9, 2011 at 9:04

Icarus

64k14 gold badges102 silver badges116 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

mikespiteri Over a year ago

I have checked and re-checked the dates. Thats why I have posted here because it is quite strange. I did a breakpoint in my program to check if the row count increases but it remains 0

Ryan S · Accepted Answer · 2011-09-16 09:48:41Z

2

+50

Try dropping the view and create again. Make sure you got the aliases correct too.

answered Sep 16, 2011 at 9:48

Ryan S

3,27817 gold badges50 silver badges79 bronze badges

Comments

Carth · Accepted Answer · 2011-09-15 21:10:16Z

1

When addressing this kind of problem it's usually best to not make too many assumptions in regard to where the error is originating from. If you have access to the box I would run a trace and make sure that the statements being run are indeed identical. If they are identical then you know you have a programmatic error on the receiving or processing side of your application.

You can modify your statement to insert the results into a temporary table and verify that the 192 rows you expect are there. If you are executing the exact same statement and the temp table shows that you are getting the results you expect you've further narrowed down the problem and can begin looking for application errors.

answered Sep 15, 2011 at 21:10

Carth

2,3431 gold badge17 silver badges27 bronze badges

Comments

Jon Heller · Accepted Answer · 2011-09-16 07:43:25Z

Assuming that your C# code is sending the correct query to the database, there are several ways for Oracle to run the same query differently depending on the session. You may need to get a DBA involved to figure this out (e.g. by looking at the actual executed statement in v$sql), or at least to rule out these weird cases.

NLS_DATE_FORMAT

If the DATE column is stored as a string, there would be an implicit conversion to a date. If SQL Navigator uses a different NLS_DATE_FORMAT than C# the conversion could create different dates. Although if they were different there's a good chance you'd get an error, not just 0 rows.

Virtual Private Database

VPD can add a predicate to every query, possibly using different session information. For example if the program that created the session is like '%Navigator%' it could add 'where 1 = 0' to every query. (I know this sounds crazy, but I've seen something very similar.)

Advanced Query Rewrite

This is meant for materialized views. But some people use it for performance fixes, sort of like stored outlines. And some evil people use it to rewrite your query into a completely different query. Different session settings, such as QUERY_REWRITE_INTEGRITY and CURSOR_SHARING, could explain why the query works on one session but not another. Speaking of CURSOR_SHARING, that may lead to some rare problems if it is set to SIMILAR or FORCE.

James L. · Accepted Answer · 2011-09-16 08:00:20Z

1

I have run into the same situation quite often with other development tools and SQL Server. In my case I found that in the query tool I would have two outputs, the records in a datagrid and the 'rows affected' message in a results pane. However, in my development IDE I would see no data (unless I checked for additional datasets). For some reason it would return the rows affected as the 1st result set. When I turned off the rowcount option in the query itself, then the data showed up in both places.

You could also use a protocol analyzer (e.g., Ethereal) and capture the TCP/IP traffic to verify that the requests are identical over wire. That has helped me in a pinch too.

Best of luck.

jl

answered Sep 16, 2011 at 8:00

James L.

9,5115 gold badges42 silver badges80 bronze badges

Comments

Community · Accepted Answer · 2017-05-23 11:48:22Z

1

I'm wondering is this is the same problem as your question here.

Your string.Format call is not specifying the format of your date1 and date2 values in the SQL string itself. Hence it is using the default DateTime.ToString() of date1 and date2, which could be something like '16/09/2011 12:23:34' and so which does not match the format specified in your to_date statement.

Try this:

   string SqlQuery = string.Format(@"SELECT * FROM V_REPORT WHERE DATE BETWEEN 
                                   to_date('{0:dd/MM/yyyy}' , 'dd/mm/yyyy')
                                   AND 
                                   to_date('{1:dd/MM/yyyy}' , 'dd/mm/yyyy')", 
                                   date1, 
                                   date2);

edited May 23, 2017 at 11:48

CommunityBot

11 silver badge

answered Sep 16, 2011 at 8:40

retired

6,8902 gold badges32 silver badges47 bronze badges

1 Comment

mikespiteri Over a year ago

thanks everyone for your help. I solved the problem. I am not sure what was wrong but I dropped the view table and created it again and it worked. Still I dont know what was happening cause everything seemed fine. Thanks again

Collectives™ on Stack Overflow

Query problem - rows are returned when query is run in sql navigator , but not in my c# program

6 Answers 6

1 Comment

Comments

Comments

Comments

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

6 Answers 6

1 Comment

Comments

Comments

Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related