CODEWITHSUNDEEP
c#asp.netsqloracle10goracle11g
mikespiteri
mikespiteri

Reputation: 901

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

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')

Upvotes: 6

Views: 1118

Answers (6)

Ryan S
Ryan S

Reputation: 3268

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

Upvotes: 2

Neil Moss
Neil Moss

Reputation: 6818

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);

Upvotes: 1

James L.
James L.

Reputation: 9453

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

Upvotes: 1

Jon Heller
Jon Heller

Reputation: 36807

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.

Upvotes: 1

Carth
Carth

Reputation: 2343

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.

Upvotes: 1

Icarus
Icarus

Reputation: 63956

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.

Upvotes: 5

Related Questions