Problem with the query - rows are returned when the query is executed in sql navigator, but not in my C # program

Question

Update:

This is a request from the debugger that was obtained from the line 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 braces and place them in the Navigator, it will work.

Original:

I have a problem starting my program. The query in sql navigator returns 192 rows, but when I run the query in 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'm using in the 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') 

Answer 1

Try to drop the view and create it again. Make sure you have the correct aliases too.

Answer 2

I bet the dates from your C # program are different because your sql statement is identical. Place a breakpoint and make sure the dates match. Also make sure that date1 and date2 are transmitted in the proper order.

Answer 3

When solving this problem, it is usually best not to make too many assumptions as to where the error comes from. If you have access to the mailbox, I will run the trace and make sure that the statements being executed are really identical. If they are identical, then you know that you have a software error on the receiving or processing side of your application.

You can modify your statement to insert the results in a temporary table and make sure that the 192 rows you expect are there. If you execute the same statement and the temp table shows that you are getting results, expect that you have narrowed down the problem even further and you can start looking for application errors.

Answer 4

Assuming your C # code is sending the correct request to the database, there are several ways in which Oracle can execute the same request in different ways depending on the session. You may need to get a DBA to work to figure this out (for example, by looking at the actual executable statement in v $ sql) or at least eliminate these strange cases.

NLS_DATE_FORMAT

If the DATE column is stored as a string, an implicit conversion to date will occur. If SQL Navigator uses a different NLS_DATE_FORMAT than C #, the conversion may produce different dates. Although, if they were different, you would have a good chance of getting an error, and not just 0 lines.

Virtual private database

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

Advanced Request Forwarding

It is intended for materialized presentations. But some people use it for performance fixes, sort of like saved outlines. And some evil people use it to rewrite your request into a completely different request. Various session settings, such as QUERY_REWRITE_INTEGRITY and CURSOR_SHARING, may explain why the request works in one session, but not in another. Speaking of CURSOR_SHARING, this can lead to some rare problems if it is installed in SIMILAR or FORCE.

Answer 5

I often came across the same situation with other development tools and SQL Server. In my case, I found that in the query tool, I will have two outputs, entries in the data file and the message “affected lines” in the result area. However, in my development IDE, I did not see any data (unless I checked additional data sets). For some reason, it will return rows affected as the first result set. When I turned off the rowcount parameter in the request itself, the data appeared in both places.

You can also use a protocol analyzer (such as Ethereal ) and capture TCP / IP traffic to make sure the requests are identical over the wire. It also helped me.

Good luck.

Jl

Answer 6

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

Your call to string.Format does not indicate the format of your date1 and date2 values ​​in the SQL string itself. Therefore, it uses the default DateTime.ToString () value of date1 and date2, which can be something like "09/16/2011 12:23:34 PM" and therefore does not match the format specified in the to_date statement.

Try the following:

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