Created
April 25, 2016 20:19
-
-
Save jsbattig/98acae6aef483f15bc13fb4fe7f23e45 to your computer and use it in GitHub Desktop.
Issue trying to pull deltas in Oracle using date field
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
using System; | |
using System.Data; | |
using Oracle.DataAccess.Client; | |
namespace oradatetest | |
{ | |
class Program | |
{ | |
static void Main(string[] args) | |
{ | |
using (var conn1 = new OracleConnection("Data Source=P11D;User Id=ceenv64;Password=ce1099;")) | |
{ | |
conn1.Open(); | |
/* First let's clean up out table if we run this test before. | |
CREATE TABLE CNVLOG | |
( "USRNAME" VARCHAR2(50 BYTE) NOT NULL ENABLE, | |
"LOGCODE" NUMBER(10,0) NOT NULL ENABLE, | |
"LOGDATE" DATE NOT NULL ENABLE, | |
... other fields here... | |
) */ | |
using (var qr1 = new OracleCommand() | |
{ | |
Connection = conn1, | |
CommandText = "delete from CNVLOG where LOGCODE=9999 and extract(day from LOGDATE) = extract(day from :CUR_DATE)", | |
CommandType = CommandType.Text | |
}) | |
{ | |
qr1.Parameters.Add("CUR_DATE", DateTime.Now); | |
qr1.ExecuteNonQuery(); | |
} | |
/* Now let's add one record using current date and time from .NET */ | |
using (var qr1 = new OracleCommand() | |
{ | |
Connection = conn1, | |
CommandText = "insert into CNVLOG (USRNAME, LOGCODE, LOGDATE) values ('JSBATTIG', 9999, :LOGDATE)", | |
CommandType = CommandType.Text | |
}) | |
{ | |
qr1.Parameters.Add("LOGDATE", DateTime.Now); | |
qr1.ExecuteNonQuery(); | |
} | |
/* Let's create a query, and select ONLY records matching the current day. This is key, you could do the same test here selecting | |
data with time resolution to a millisecond if you want */ | |
using (var qr2 = new OracleCommand() | |
{ | |
Connection = conn1, | |
CommandText = "select USRNAME from CNVLOG where LOGCODE=9999 and extract(day from LOGDATE) = extract(day from :CUR_DATE)", | |
CommandType = CommandType.Text | |
}) | |
{ | |
qr2.Parameters.Add("CUR_DATE", DateTime.Now); | |
/* Let's effectively open the query */ | |
using (var reader = qr2.ExecuteReader()) | |
{ | |
while (reader.Read()) | |
{ | |
/* We will print here the first column of the result set for the ONE record we fetched */ | |
Console.WriteLine(reader.GetString(0)); | |
/* Now, within the loop, we insert one more record. DAY will be the same day we opened the query, but this record | |
will not be seen by the reader when we loop thorough. As I said before, this will be equally valid even if our | |
resolution is milliseconds. | |
In essence, this record won't be fetched if we start fetching data from resolution + 1 ( tomorrow in our case ) */ | |
using (var qr3 = new OracleCommand() | |
{ | |
Connection = conn1, | |
CommandText = "insert into CNVLOG (USRNAME, LOGCODE, LOGDATE) values ('JSBATTIG-2', 9999, :LOGDATE)", | |
CommandType = CommandType.Text | |
}) | |
{ | |
qr3.Parameters.Add("LOGDATE", DateTime.Now); | |
qr3.ExecuteNonQuery(); | |
} | |
} | |
} | |
} | |
} | |
} | |
} | |
} |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment