public static void DbFunctions_NVL2()
{
DbFunctionTools tools = new DbFunctionTools();
{
Console.WriteLine("Testing DbFunctions.NVL2(DateTime)...");
List<DbValue> args = new List<DbValue>();
DateTime dt = DateTime.Parse("12/1/2000 10:00:00 AM");
DateTime ifnull = DateTime.Parse("12/11/2000 10:00:00 AM");
DateTime notnull = DateTime.Parse("12/14/2000 10:00:00 AM");
args.Add(tools.AllocValue(dt));
args.Add(tools.AllocValue(notnull));
args.Add(tools.AllocValue(ifnull));
DbFunctionArguments fargs = new DbFunctionArguments(args);
DbValue valOutput = DbFunctions.NVL2(tools, fargs);
ByteSlice bs = valOutput.Eval();
DateTime output = tools.GetDateTime(bs);
DateTime expected = notnull;
if (expected != output)
{
throw new Exception("DbFunctions.NVL2(DateTime) has failed. Expected result: " + expected.ToString() + ", but received: " + output.ToString());
}
else
{
Console.WriteLine("Expected results received.");
}
}
{
Console.WriteLine("Testing DbFunctions.NVL2(DateTime)...");
List<DbValue> args = new List<DbValue>();
byte[] buf = new byte[9];
buf[0] = 1; //is null
DateTime ifnull = DateTime.Parse("12/11/2000 10:00:00 AM");
DateTime notnull = DateTime.Parse("12/14/2000 10:00:00 AM");
args.Add(tools.AllocValue(ByteSlice.Prepare(buf), DbType.Prepare("DateTime", 9)));
args.Add(tools.AllocValue(notnull));
args.Add(tools.AllocValue(ifnull));
DbFunctionArguments fargs = new DbFunctionArguments(args);
DbValue valOutput = DbFunctions.NVL2(tools, fargs);
ByteSlice bs = valOutput.Eval();
DateTime output = tools.GetDateTime(bs);
DateTime expected = ifnull;
if (expected != output)
{
throw new Exception("DbFunctions.NVL2(DateTime) has failed. Expected result: " + expected.ToString() + ", but received: " + output.ToString());
}
else
{
Console.WriteLine("Expected results received.");
}
}
}
public static DbValue MONTHS_BETWEEN(DbFunctionTools tools, DbFunctionArguments args)
{
string FunctionName = "MONTHS_BETWEEN";
args.EnsureCount(FunctionName, 2);
DbType arg0type;
ByteSlice arg0 = args[0].Eval(out arg0type);
if (Types.IsNullValue(arg0))
{
return(tools.AllocNullValue()); // Give a null, take a null.
}
if (arg0type.ID != DbTypeID.DATETIME)
{
args.Expected(FunctionName, 0, "input DATETIME, got " + arg0type.Name.ToUpper());
}
DbType arg1type;
ByteSlice arg1 = args[1].Eval(out arg1type);
if (Types.IsNullValue(arg1))
{
return(tools.AllocNullValue()); // Give a null, take a null.
}
if (arg1type.ID != DbTypeID.DATETIME)
{
args.Expected(FunctionName, 0, "input DATETIME, got " + arg1type.Name.ToUpper());
}
DateTime dt0 = tools.GetDateTime(arg0);
DateTime dt1 = tools.GetDateTime(arg1);
int daysInMonth0 = DateTime.DaysInMonth(dt0.Year, dt0.Month);
int daysInMonth1 = DateTime.DaysInMonth(dt1.Year, dt1.Month);
double btw = 0;
if (dt0.Year == dt1.Year && dt0.Month == dt1.Month) //same month and same year
{
btw = (double)(dt0.Day - dt1.Day) / (double)daysInMonth0;
}
else if (dt0.Day == daysInMonth0 && dt1.Day == daysInMonth1) //both fall on the last day of their months
{
btw = 12 * (dt0.Year - dt1.Year) + dt0.Month - dt1.Month;
}
else
{
TimeSpan sp = dt0 - dt1;
btw = sp.TotalDays / 31d;
}
return(tools.AllocValue(btw));
}
public static DbValue MONTHS_BETWEEN(DbFunctionTools tools, DbFunctionArguments args)
{
string FunctionName = "MONTHS_BETWEEN";
args.EnsureCount(FunctionName, 2);
DbType arg0type;
ByteSlice arg0 = args[0].Eval(out arg0type);
if (Types.IsNullValue(arg0))
{
return tools.AllocNullValue(); // Give a null, take a null.
}
if (arg0type.ID != DbTypeID.DATETIME)
{
args.Expected(FunctionName, 0, "input DATETIME, got " + arg0type.Name.ToUpper());
}
DbType arg1type;
ByteSlice arg1 = args[1].Eval(out arg1type);
if (Types.IsNullValue(arg1))
{
return tools.AllocNullValue(); // Give a null, take a null.
}
if (arg1type.ID != DbTypeID.DATETIME)
{
args.Expected(FunctionName, 0, "input DATETIME, got " + arg1type.Name.ToUpper());
}
DateTime dt0 = tools.GetDateTime(arg0);
DateTime dt1 = tools.GetDateTime(arg1);
int daysInMonth0 = DateTime.DaysInMonth(dt0.Year, dt0.Month);
int daysInMonth1 = DateTime.DaysInMonth(dt1.Year, dt1.Month);
double btw = 0;
if (dt0.Year == dt1.Year && dt0.Month == dt1.Month) //same month and same year
{
btw = (double)(dt0.Day - dt1.Day) / (double)daysInMonth0;
}
else if (dt0.Day == daysInMonth0 && dt1.Day == daysInMonth1) //both fall on the last day of their months
{
btw = 12 * (dt0.Year - dt1.Year) + dt0.Month - dt1.Month;
}
else
{
TimeSpan sp = dt0 - dt1;
btw = sp.TotalDays / 31d;
}
return tools.AllocValue(btw);
}
public static DbValue ADD_MONTHS(DbFunctionTools tools, DbFunctionArguments args)
{
string FunctionName = "ADD_MONTHS";
args.EnsureCount(FunctionName, 2);
DbType arg0type;
ByteSlice arg0 = args[0].Eval(out arg0type);
if (Types.IsNullValue(arg0))
{
return(tools.AllocNullValue()); // Give a null, take a null.
}
if (arg0type.ID != DbTypeID.DATETIME)
{
args.Expected(FunctionName, 0, "input DATETIME, got " + arg0type.Name.ToUpper());
}
DbType arg1type;
ByteSlice arg1 = args[1].Eval(out arg1type);
if (Types.IsNullValue(arg1))
{
return(tools.AllocNullValue()); // Give a null, take a null.
}
if (arg1type.ID != DbTypeID.INT)
{
args.Expected(FunctionName, 0, "input INT, got " + arg1type.Name.ToUpper());
}
DateTime dt = tools.GetDateTime(arg0);
int months = tools.GetInt(arg1);
dt = dt.AddMonths(months);
return(tools.AllocValue(dt));
}
public static DbValue ADD_MONTHS(DbFunctionTools tools, DbFunctionArguments args)
{
string FunctionName = "ADD_MONTHS";
args.EnsureCount(FunctionName, 2);
DbType arg0type;
ByteSlice arg0 = args[0].Eval(out arg0type);
if (Types.IsNullValue(arg0))
{
return tools.AllocNullValue(); // Give a null, take a null.
}
if (arg0type.ID != DbTypeID.DATETIME)
{
args.Expected(FunctionName, 0, "input DATETIME, got " + arg0type.Name.ToUpper());
}
DbType arg1type;
ByteSlice arg1 = args[1].Eval(out arg1type);
if (Types.IsNullValue(arg1))
{
return tools.AllocNullValue(); // Give a null, take a null.
}
if (arg1type.ID != DbTypeID.INT)
{
args.Expected(FunctionName, 0, "input INT, got " + arg1type.Name.ToUpper());
}
DateTime dt = tools.GetDateTime(arg0);
int months = tools.GetInt(arg1);
dt = dt.AddMonths(months);
return tools.AllocValue(dt);
}
public static void DbFunctions_ADD_MONTHS()
{
DbFunctionTools tools = new DbFunctionTools();
//String,Int32.
{
Console.WriteLine("Testing DbFunctions.ADD_MONTHS...");
List<DbValue> args = new List<DbValue>();
DateTime dt = DateTime.Now;
int months = 9;
args.Add(tools.AllocValue(dt));
args.Add(tools.AllocValue(months));
DbFunctionArguments fargs = new DbFunctionArguments(args);
DbValue valOutput = DbFunctions.ADD_MONTHS(tools, fargs);
ByteSlice bs = valOutput.Eval();
DateTime output = tools.GetDateTime(bs);
DateTime expected = dt.AddMonths(months);
if (expected != output)
{
throw new Exception("DbFunctions.ADD_MONTHS has failed. Expected result: " + expected.ToString() + ", but received: " + output.ToString());
}
else
{
Console.WriteLine("Expected results received.");
}
}
}
public static void DbFunctions_SYSDATE()
{
DbFunctionTools tools = new DbFunctionTools();
//String,Int32.
{
Console.WriteLine("Testing DbFunctions.SYSDATE...");
DbValue valOutput = DbFunctions.SYSDATE(tools, new DbFunctionArguments());
ByteSlice bs = valOutput.Eval();
DateTime output = tools.GetDateTime(bs);
DateTime expected = DateTime.Now;
TimeSpan sp = output - expected;
if (sp.TotalMinutes > 5)
{
throw new Exception("DbFunctions.SYSDATE has failed. Expected result: " + expected.ToString() + ", but received: " + output.ToString());
}
else
{
Console.WriteLine("Expected results received.");
}
}
}
public static DbValue FORMAT(DbFunctionTools tools, DbFunctionArguments args)
{
string FunctionName = "FORMAT";
args.EnsureCount(FunctionName, 2);
DbType arg0type;
ByteSlice arg0 = args[0].Eval(out arg0type);
DbType arg1type;
ByteSlice arg1 = args[1].Eval(out arg1type);
if (Types.IsNullValue(arg0))
{
return(tools.AllocNullValue()); // Give a null, take a null.
}
if (arg0type.ID != DbTypeID.CHARS)
{
args.Expected(FunctionName, 0, "input CHAR(n), got " + arg0type.Name.ToUpper());
}
if (Types.IsNullValue(arg1))
{
return(tools.AllocNullValue()); // Give a null, take a null.
}
if (arg1type.ID != DbTypeID.DATETIME)
{
args.Expected(FunctionName, 0, "input DATETIME, got " + arg1type.Name.ToUpper());
}
string formatstr = tools.GetString(arg0).ToString();
DateTime dt = tools.GetDateTime(arg1);
mstring result = mstring.Prepare(dt.ToString(formatstr));
while (result.Length < 80)
{
result.MAppend('\0');
}
return(tools.AllocValue(result));
}
public static DbValue LAST_DAY(DbFunctionTools tools, DbFunctionArguments args)
{
string FunctionName = "LAST_DAY";
args.EnsureCount(FunctionName, 1);
DbType arg0type;
ByteSlice arg0 = args[0].Eval(out arg0type);
if (Types.IsNullValue(arg0))
{
return tools.AllocNullValue(); // Give a null, take a null.
}
if (arg0type.ID != DbTypeID.DATETIME)
{
args.Expected(FunctionName, 0, "input DATETIME, got " + arg0type.Name.ToUpper());
}
DateTime dt = tools.GetDateTime(arg0);
dt = dt.AddMonths(1);
dt = dt.AddDays(-1);
return tools.AllocValue(dt);
}
public static DbValue NEXT_DAY(DbFunctionTools tools, DbFunctionArguments args)
{
string FunctionName = "NEXT_DAY";
args.EnsureCount(FunctionName, 1);
DbType arg0type;
ByteSlice arg0 = args[0].Eval(out arg0type);
if (Types.IsNullValue(arg0))
{
return(tools.AllocNullValue()); // Give a null, take a null.
}
if (arg0type.ID != DbTypeID.DATETIME)
{
args.Expected(FunctionName, 0, "input DATETIME, got " + arg0type.Name.ToUpper());
}
DateTime dt = tools.GetDateTime(arg0);
dt = dt.AddDays(1);
return(tools.AllocValue(dt));
}
public static DbValue FORMAT(DbFunctionTools tools, DbFunctionArguments args)
{
string FunctionName = "FORMAT";
args.EnsureCount(FunctionName, 2);
DbType arg0type;
ByteSlice arg0 = args[0].Eval(out arg0type);
DbType arg1type;
ByteSlice arg1 = args[1].Eval(out arg1type);
if (Types.IsNullValue(arg0))
{
return tools.AllocNullValue(); // Give a null, take a null.
}
if (arg0type.ID != DbTypeID.CHARS)
{
args.Expected(FunctionName, 0, "input CHAR(n), got " + arg0type.Name.ToUpper());
}
if (Types.IsNullValue(arg1))
{
return tools.AllocNullValue(); // Give a null, take a null.
}
if (arg1type.ID != DbTypeID.DATETIME)
{
args.Expected(FunctionName, 0, "input DATETIME, got " + arg1type.Name.ToUpper());
}
string formatstr = tools.GetString(arg0).ToString();
DateTime dt = tools.GetDateTime(arg1);
mstring result = mstring.Prepare(dt.ToString(formatstr));
while (result.Length < 80)
{
result.MAppend('\0');
}
return tools.AllocValue(result);
}
public static DbValue DATEPART_YEAR(DbFunctionTools tools, DbFunctionArguments args)
{
string FunctionName = "DATEPART_YEAR";
args.EnsureCount(FunctionName, 1);
DbType arg0type;
ByteSlice arg0 = args[0].Eval(out arg0type);
if (Types.IsNullValue(arg0))
{
return tools.AllocNullValue(); // Give a null, take a null.
}
if (arg0type.ID != DbTypeID.DATETIME)
{
args.Expected(FunctionName, 0, "input DateTime, got " + arg0type.Name.ToUpper());
return null; // Doesn't reach here.
}
DateTime dt = tools.GetDateTime(arg0);
return tools.AllocValue(dt.Year);
}
public static DbValue DATEPART_HOUR(DbFunctionTools tools, DbFunctionArguments args)
{
string FunctionName = "DATEPART_HOUR";
args.EnsureCount(FunctionName, 1);
DbType arg0type;
ByteSlice arg0 = args[0].Eval(out arg0type);
if (Types.IsNullValue(arg0))
{
return(tools.AllocNullValue()); // Give a null, take a null.
}
if (arg0type.ID != DbTypeID.DATETIME)
{
args.Expected(FunctionName, 0, "input DateTime, got " + arg0type.Name.ToUpper());
return(null); // Doesn't reach here.
}
DateTime dt = tools.GetDateTime(arg0);
return(tools.AllocValue(dt.Hour));
}
public static DbValue CAST(DbFunctionTools tools, DbFunctionArguments args)
{
#if DEBUG
//System.Diagnostics.Debugger.Launch();
#endif
string FunctionName = "CAST";
args.EnsureCount(FunctionName, 2);
DbType type;
ByteSlice bs = args[0].Eval(out type);
mstring xas = tools.GetString(args[1]);
if (!xas.StartsWith("AS "))
{
#if DEBUG
throw new Exception("Expected AS <type> in CAST, not: " + xas);
#endif
throw new Exception("Expected AS <type> in CAST");
}
mstring msastype = xas.SubstringM(3);
string sastype = msastype.ToString(); // Alloc.
sastype = DbType.NormalizeName(sastype); // Alloc if char(n).
DbType astype = DbType.Prepare(sastype); // Alloc if char(n).
if (DbTypeID.NULL == astype.ID)
{
throw new Exception("Unknown AS type in CAST: " + sastype);
}
switch (astype.ID)
{
case DbTypeID.INT:
switch (type.ID)
{
case DbTypeID.INT: // as INT
if (astype.Size > type.Size)
{
throw new Exception("CAST: source value buffer too small");
}
return tools.AllocValue(ByteSlice.Prepare(bs, 0, astype.Size), astype);
case DbTypeID.LONG: // as INT
return tools.AllocValue((int)tools.GetLong(bs));
case DbTypeID.DOUBLE: // as INT
return tools.AllocValue((int)tools.GetDouble(bs));
case DbTypeID.DATETIME: // as INT
return tools.AllocValue((int)tools.GetDateTime(bs).Ticks);
case DbTypeID.CHARS: // as INT
{
int to = tools.GetString(bs).NextItemToInt32(' ');
return tools.AllocValue(to);
}
default:
throw new Exception("Cannot handle CAST value of type " + type.Name + " AS " + astype.Name);
}
break;
case DbTypeID.LONG:
switch (type.ID)
{
case DbTypeID.INT: // as LONG
return tools.AllocValue((long)tools.GetInt(bs));
case DbTypeID.LONG: // as LONG
if (astype.Size > type.Size)
{
throw new Exception("CAST: source value buffer too small");
}
return tools.AllocValue(ByteSlice.Prepare(bs, 0, astype.Size), astype);
case DbTypeID.DOUBLE: // as LONG
return tools.AllocValue((long)tools.GetDouble(bs));
case DbTypeID.DATETIME: // as LONG
return tools.AllocValue((long)tools.GetDateTime(bs).Ticks);
case DbTypeID.CHARS: // as LONG
{
long to = tools.GetString(bs).NextItemToInt64(' ');
return tools.AllocValue(to);
}
default:
throw new Exception("Cannot handle CAST value of type " + type.Name + " AS " + astype.Name);
}
break;
case DbTypeID.DOUBLE:
switch (type.ID)
{
case DbTypeID.INT: // as DOUBLE
return tools.AllocValue((double)tools.GetInt(bs));
case DbTypeID.LONG: // as DOUBLE
return tools.AllocValue((double)tools.GetLong(bs));
case DbTypeID.DOUBLE: // as DOUBLE
if (astype.Size > type.Size)
{
throw new Exception("CAST: source value buffer too small");
}
return tools.AllocValue(ByteSlice.Prepare(bs, 0, astype.Size), astype);
case DbTypeID.DATETIME: // as DOUBLE
return tools.AllocValue((double)tools.GetDateTime(bs).Ticks);
case DbTypeID.CHARS: // as DOUBLE
{
double to = tools.GetString(bs).NextItemToDouble(' ');
return tools.AllocValue(to);
}
default:
throw new Exception("Cannot handle CAST value of type " + type.Name + " AS " + astype.Name);
}
break;
case DbTypeID.DATETIME:
switch (type.ID)
{
case DbTypeID.INT: // as DATETIME
return tools.AllocValue(new DateTime((long)tools.GetInt(bs)));
case DbTypeID.LONG: // as DATETIME
return tools.AllocValue(new DateTime((long)tools.GetLong(bs)));
case DbTypeID.DOUBLE: // as DATETIME
return tools.AllocValue(new DateTime((long)tools.GetDouble(bs)));
case DbTypeID.DATETIME: // as DATETIME
if (astype.Size > type.Size)
{
throw new Exception("CAST: source value buffer too small");
}
return tools.AllocValue(ByteSlice.Prepare(bs, 0, astype.Size), astype);
case DbTypeID.CHARS: // as DATETIME
{
mstring to = tools.GetString(bs);
return tools.AllocValue(DateTime.Parse(to.ToString()));
}
default:
throw new Exception("Cannot handle CAST value of type " + type.Name + " AS " + astype.Name);
}
break;
case DbTypeID.CHARS:
switch (type.ID)
{
case DbTypeID.INT: // as CHAR(n)
{
mstring ms = mstring.Prepare(tools.GetInt(bs));
return tools.AllocValue(ms, astype.Size);
}
case DbTypeID.LONG: // as CHAR(n)
{
mstring ms = mstring.Prepare(tools.GetLong(bs));
return tools.AllocValue(ms, astype.Size);
}
case DbTypeID.DOUBLE: // as CHAR(n)
{
mstring ms = mstring.Prepare(tools.GetDouble(bs));
return tools.AllocValue(ms, astype.Size);
}
case DbTypeID.DATETIME: // as CHAR(n)
{
mstring ms = mstring.Prepare(tools.GetDateTime(bs).ToString());
return tools.AllocValue(ms, astype.Size);
}
case DbTypeID.CHARS: // as CHAR(n)
if (astype.Size > type.Size)
{
//throw new Exception("CAST: source value buffer too small");
return tools.AllocValue(tools.GetString(bs), astype.Size);
}
return tools.AllocValue(ByteSlice.Prepare(bs, 0, astype.Size), astype);
default:
throw new Exception("Cannot handle CAST value of type " + type.Name + " AS " + astype.Name);
}
break;
default:
throw new Exception("Cannot handle CAST value of type " + type.Name + " AS " + astype.Name);
}
}
public static DbValue DATEDIFF(DbFunctionTools tools, DbFunctionArguments args)
{
string FunctionName = "DATEDIFF";
args.EnsureCount(FunctionName, 3);
DbType arg0type;
ByteSlice arg0 = args[0].Eval(out arg0type);
DbType arg1type;
ByteSlice arg1 = args[1].Eval(out arg1type);
DbType arg2type;
ByteSlice arg2 = args[2].Eval(out arg2type);
if (Types.IsNullValue(arg0))
{
return(tools.AllocNullValue()); // Give a null, take a null.
}
if (arg0type.ID != DbTypeID.CHARS)
{
args.Expected(FunctionName, 0, "input CHAR(n), got " + arg0type.Name.ToUpper());
}
if (Types.IsNullValue(arg1))
{
return(tools.AllocNullValue()); // Give a null, take a null.
}
if (arg1type.ID != DbTypeID.DATETIME)
{
args.Expected(FunctionName, 0, "input DATETIME, got " + arg1type.Name.ToUpper());
}
if (Types.IsNullValue(arg2))
{
return(tools.AllocNullValue()); // Give a null, take a null.
}
if (arg2type.ID != DbTypeID.DATETIME)
{
args.Expected(FunctionName, 0, "input DATETIME, got " + arg2type.Name.ToUpper());
}
string datepart = tools.GetString(arg0).ToUpperM().ToString();
DateTime startdate = tools.GetDateTime(arg1);
DateTime enddate = tools.GetDateTime(arg2);
double partdiff = 0;
switch (datepart)
{
case "YEAR":
case "YY":
case "YYYY":
partdiff = enddate.Year - startdate.Year;
break;
case "QUARTER":
case "QQ":
case "Q":
partdiff = GetMonthDiff(startdate.Year, startdate.Month, enddate.Year, enddate.Month) / 3;
break;
case "MONTH":
case "MM":
case "M":
partdiff = GetMonthDiff(startdate.Year, startdate.Month, enddate.Year, enddate.Month);
break;
case "DAY":
case "DD":
case "D":
{
DateTime sdate = new DateTime(startdate.Year, startdate.Month, startdate.Day);
DateTime edate = new DateTime(enddate.Year, enddate.Month, enddate.Day);
TimeSpan sp = edate - sdate;
partdiff = sp.TotalDays;
}
break;
case "WEEK":
case "WK":
case "WW":
{
DateTime sdate = new DateTime(startdate.Year, startdate.Month, startdate.Day);
DateTime edate = new DateTime(enddate.Year, enddate.Month, enddate.Day);
TimeSpan sp = edate - sdate;
partdiff = (int)sp.TotalDays / 7;
}
break;
case "HOUR":
case "HH":
{
DateTime sdate = new DateTime(startdate.Year, startdate.Month, startdate.Day, startdate.Hour, 0, 0);
DateTime edate = new DateTime(enddate.Year, enddate.Month, enddate.Day, enddate.Hour, 0, 0);
TimeSpan sp = edate - sdate;
partdiff = sp.TotalHours;
}
break;
case "MINUTE":
case "MI":
case "N":
{
DateTime sdate = new DateTime(startdate.Year, startdate.Month, startdate.Day, startdate.Hour, startdate.Minute, 0);
DateTime edate = new DateTime(enddate.Year, enddate.Month, enddate.Day, enddate.Hour, enddate.Minute, 0);
TimeSpan sp = edate - sdate;
partdiff = sp.TotalMinutes;
}
break;
case "SECOND":
case "SS":
case "S":
{
DateTime sdate = new DateTime(startdate.Year, startdate.Month, startdate.Day, startdate.Hour, startdate.Minute, startdate.Second);
DateTime edate = new DateTime(enddate.Year, enddate.Month, enddate.Day, enddate.Hour, enddate.Minute, enddate.Second);
TimeSpan sp = edate - sdate;
partdiff = sp.TotalSeconds;
}
break;
case "MILLISECOND":
case "MS":
{
TimeSpan sp = enddate - startdate;
partdiff = sp.TotalMilliseconds;
}
break;
default:
args.Expected(FunctionName, 0, "input datepart invalid");
return(null);
}
return(tools.AllocValue(partdiff));
}
public static DbValue CAST(DbFunctionTools tools, DbFunctionArguments args)
{
#if DEBUG
//System.Diagnostics.Debugger.Launch();
#endif
string FunctionName = "CAST";
args.EnsureCount(FunctionName, 2);
DbType type;
ByteSlice bs = args[0].Eval(out type);
mstring xas = tools.GetString(args[1]);
if (!xas.StartsWith("AS "))
{
#if DEBUG
throw new Exception("Expected AS <type> in CAST, not: " + xas);
#endif
throw new Exception("Expected AS <type> in CAST");
}
mstring msastype = xas.SubstringM(3);
string sastype = msastype.ToString(); // Alloc.
sastype = DbType.NormalizeName(sastype); // Alloc if char(n).
DbType astype = DbType.Prepare(sastype); // Alloc if char(n).
if (DbTypeID.NULL == astype.ID)
{
throw new Exception("Unknown AS type in CAST: " + sastype);
}
switch (astype.ID)
{
case DbTypeID.INT:
switch (type.ID)
{
case DbTypeID.INT: // as INT
if (astype.Size > type.Size)
{
throw new Exception("CAST: source value buffer too small");
}
return(tools.AllocValue(ByteSlice.Prepare(bs, 0, astype.Size), astype));
case DbTypeID.LONG: // as INT
return(tools.AllocValue((int)tools.GetLong(bs)));
case DbTypeID.DOUBLE: // as INT
return(tools.AllocValue((int)tools.GetDouble(bs)));
case DbTypeID.DATETIME: // as INT
return(tools.AllocValue((int)tools.GetDateTime(bs).Ticks));
case DbTypeID.CHARS: // as INT
{
int to = tools.GetString(bs).NextItemToInt32(' ');
return(tools.AllocValue(to));
}
default:
throw new Exception("Cannot handle CAST value of type " + type.Name + " AS " + astype.Name);
}
break;
case DbTypeID.LONG:
switch (type.ID)
{
case DbTypeID.INT: // as LONG
return(tools.AllocValue((long)tools.GetInt(bs)));
case DbTypeID.LONG: // as LONG
if (astype.Size > type.Size)
{
throw new Exception("CAST: source value buffer too small");
}
return(tools.AllocValue(ByteSlice.Prepare(bs, 0, astype.Size), astype));
case DbTypeID.DOUBLE: // as LONG
return(tools.AllocValue((long)tools.GetDouble(bs)));
case DbTypeID.DATETIME: // as LONG
return(tools.AllocValue((long)tools.GetDateTime(bs).Ticks));
case DbTypeID.CHARS: // as LONG
{
long to = tools.GetString(bs).NextItemToInt64(' ');
return(tools.AllocValue(to));
}
default:
throw new Exception("Cannot handle CAST value of type " + type.Name + " AS " + astype.Name);
}
break;
case DbTypeID.DOUBLE:
switch (type.ID)
{
case DbTypeID.INT: // as DOUBLE
return(tools.AllocValue((double)tools.GetInt(bs)));
case DbTypeID.LONG: // as DOUBLE
return(tools.AllocValue((double)tools.GetLong(bs)));
case DbTypeID.DOUBLE: // as DOUBLE
if (astype.Size > type.Size)
{
throw new Exception("CAST: source value buffer too small");
}
return(tools.AllocValue(ByteSlice.Prepare(bs, 0, astype.Size), astype));
case DbTypeID.DATETIME: // as DOUBLE
return(tools.AllocValue((double)tools.GetDateTime(bs).Ticks));
case DbTypeID.CHARS: // as DOUBLE
{
double to = tools.GetString(bs).NextItemToDouble(' ');
return(tools.AllocValue(to));
}
default:
throw new Exception("Cannot handle CAST value of type " + type.Name + " AS " + astype.Name);
}
break;
case DbTypeID.DATETIME:
switch (type.ID)
{
case DbTypeID.INT: // as DATETIME
return(tools.AllocValue(new DateTime((long)tools.GetInt(bs))));
case DbTypeID.LONG: // as DATETIME
return(tools.AllocValue(new DateTime((long)tools.GetLong(bs))));
case DbTypeID.DOUBLE: // as DATETIME
return(tools.AllocValue(new DateTime((long)tools.GetDouble(bs))));
case DbTypeID.DATETIME: // as DATETIME
if (astype.Size > type.Size)
{
throw new Exception("CAST: source value buffer too small");
}
return(tools.AllocValue(ByteSlice.Prepare(bs, 0, astype.Size), astype));
case DbTypeID.CHARS: // as DATETIME
{
mstring to = tools.GetString(bs);
return(tools.AllocValue(DateTime.Parse(to.ToString())));
}
default:
throw new Exception("Cannot handle CAST value of type " + type.Name + " AS " + astype.Name);
}
break;
case DbTypeID.CHARS:
switch (type.ID)
{
case DbTypeID.INT: // as CHAR(n)
{
mstring ms = mstring.Prepare(tools.GetInt(bs));
return(tools.AllocValue(ms, astype.Size));
}
case DbTypeID.LONG: // as CHAR(n)
{
mstring ms = mstring.Prepare(tools.GetLong(bs));
return(tools.AllocValue(ms, astype.Size));
}
case DbTypeID.DOUBLE: // as CHAR(n)
{
mstring ms = mstring.Prepare(tools.GetDouble(bs));
return(tools.AllocValue(ms, astype.Size));
}
case DbTypeID.DATETIME: // as CHAR(n)
{
mstring ms = mstring.Prepare(tools.GetDateTime(bs).ToString());
return(tools.AllocValue(ms, astype.Size));
}
case DbTypeID.CHARS: // as CHAR(n)
if (astype.Size > type.Size)
{
//throw new Exception("CAST: source value buffer too small");
return(tools.AllocValue(tools.GetString(bs), astype.Size));
}
return(tools.AllocValue(ByteSlice.Prepare(bs, 0, astype.Size), astype));
default:
throw new Exception("Cannot handle CAST value of type " + type.Name + " AS " + astype.Name);
}
break;
default:
throw new Exception("Cannot handle CAST value of type " + type.Name + " AS " + astype.Name);
}
}
public static void DbFunctions_DATEADD()
{
DbFunctionTools tools = new DbFunctionTools();
{
Console.WriteLine("Testing DbFunctions.DATEADD(year)...");
List<DbValue> args = new List<DbValue>();
mstring datepart = mstring.Prepare("year");
args.Add(tools.AllocValue(datepart));
int number = -10;
args.Add(tools.AllocValue(number));
DateTime dt = new DateTime(2000, 9, 14, 12, 0, 0);
args.Add(tools.AllocValue(dt));
DbFunctionArguments fargs = new DbFunctionArguments(args);
DbValue valOutput = DbFunctions.DATEADD(tools, fargs);
ByteSlice bs = valOutput.Eval();
DateTime output = tools.GetDateTime(bs);
DateTime expected = dt.AddYears(number);
if (expected != output)
{
throw new Exception("DbFunctions.DATEADD(year) has failed. Expected result: " + expected.ToString() + ", but received: " + output.ToString());
}
else
{
Console.WriteLine("Expected results received.");
}
}
{
Console.WriteLine("Testing DbFunctions.DATEADD(quarter)...");
List<DbValue> args = new List<DbValue>();
mstring datepart = mstring.Prepare("qq");
args.Add(tools.AllocValue(datepart));
int number = 5;
args.Add(tools.AllocValue(number));
DateTime dt = new DateTime(2000, 9, 14, 12, 0, 0);
args.Add(tools.AllocValue(dt));
DbFunctionArguments fargs = new DbFunctionArguments(args);
DbValue valOutput = DbFunctions.DATEADD(tools, fargs);
ByteSlice bs = valOutput.Eval();
DateTime output = tools.GetDateTime(bs);
DateTime expected = dt.AddMonths(number * 3);
if (expected != output)
{
throw new Exception("DbFunctions.DATEADD(quarter) has failed. Expected result: " + expected.ToString() + ", but received: " + output.ToString());
}
else
{
Console.WriteLine("Expected results received.");
}
}
{
Console.WriteLine("Testing DbFunctions.DATEADD(month)...");
List<DbValue> args = new List<DbValue>();
mstring datepart = mstring.Prepare("m");
args.Add(tools.AllocValue(datepart));
int number = 10;
args.Add(tools.AllocValue(number));
DateTime dt = new DateTime(2000, 9, 14, 12, 0, 0);
args.Add(tools.AllocValue(dt));
DbFunctionArguments fargs = new DbFunctionArguments(args);
DbValue valOutput = DbFunctions.DATEADD(tools, fargs);
ByteSlice bs = valOutput.Eval();
DateTime output = tools.GetDateTime(bs);
DateTime expected = dt.AddMonths(number);
if (expected != output)
{
throw new Exception("DbFunctions.DATEADD(month) has failed. Expected result: " + expected.ToString() + ", but received: " + output.ToString());
}
else
{
Console.WriteLine("Expected results received.");
}
}
{
Console.WriteLine("Testing DbFunctions.DATEADD(day)...");
List<DbValue> args = new List<DbValue>();
mstring datepart = mstring.Prepare("day");
args.Add(tools.AllocValue(datepart));
int number = -9;
args.Add(tools.AllocValue(number));
DateTime dt = new DateTime(2000, 9, 14, 12, 0, 0);
args.Add(tools.AllocValue(dt));
DbFunctionArguments fargs = new DbFunctionArguments(args);
DbValue valOutput = DbFunctions.DATEADD(tools, fargs);
ByteSlice bs = valOutput.Eval();
DateTime output = tools.GetDateTime(bs);
DateTime expected = dt.AddDays(number);
if (expected != output)
{
throw new Exception("DbFunctions.DATEADD(day) has failed. Expected result: " + expected.ToString() + ", but received: " + output.ToString());
}
else
{
Console.WriteLine("Expected results received.");
}
}
{
Console.WriteLine("Testing DbFunctions.DATEADD(week)...");
List<DbValue> args = new List<DbValue>();
mstring datepart = mstring.Prepare("wk");
args.Add(tools.AllocValue(datepart));
int number = 22;
args.Add(tools.AllocValue(number));
DateTime dt = new DateTime(2000, 9, 14, 12, 0, 0);
args.Add(tools.AllocValue(dt));
DbFunctionArguments fargs = new DbFunctionArguments(args);
DbValue valOutput = DbFunctions.DATEADD(tools, fargs);
ByteSlice bs = valOutput.Eval();
DateTime output = tools.GetDateTime(bs);
DateTime expected = dt.AddDays(number * 7);
if (expected != output)
{
throw new Exception("DbFunctions.DATEADD(week) has failed. Expected result: " + expected.ToString() + ", but received: " + output.ToString());
}
else
{
Console.WriteLine("Expected results received.");
}
}
{
Console.WriteLine("Testing DbFunctions.DATEADD(hour)...");
List<DbValue> args = new List<DbValue>();
mstring datepart = mstring.Prepare("hour");
args.Add(tools.AllocValue(datepart));
int number = -99;
args.Add(tools.AllocValue(number));
DateTime dt = new DateTime(2000, 9, 14, 12, 0, 0);
args.Add(tools.AllocValue(dt));
DbFunctionArguments fargs = new DbFunctionArguments(args);
DbValue valOutput = DbFunctions.DATEADD(tools, fargs);
ByteSlice bs = valOutput.Eval();
DateTime output = tools.GetDateTime(bs);
DateTime expected = dt.AddHours(number);
if (expected != output)
{
throw new Exception("DbFunctions.DATEADD(hour) has failed. Expected result: " + expected.ToString() + ", but received: " + output.ToString());
}
else
{
Console.WriteLine("Expected results received.");
}
}
{
Console.WriteLine("Testing DbFunctions.DATEADD(minute)...");
List<DbValue> args = new List<DbValue>();
mstring datepart = mstring.Prepare("mi");
args.Add(tools.AllocValue(datepart));
int number = 80;
args.Add(tools.AllocValue(number));
DateTime dt = new DateTime(2000, 9, 14, 12, 0, 0);
args.Add(tools.AllocValue(dt));
DbFunctionArguments fargs = new DbFunctionArguments(args);
DbValue valOutput = DbFunctions.DATEADD(tools, fargs);
ByteSlice bs = valOutput.Eval();
DateTime output = tools.GetDateTime(bs);
DateTime expected = dt.AddMinutes(number);
if (expected != output)
{
throw new Exception("DbFunctions.DATEADD(minute) has failed. Expected result: " + expected.ToString() + ", but received: " + output.ToString());
}
else
{
Console.WriteLine("Expected results received.");
}
}
{
Console.WriteLine("Testing DbFunctions.DATEADD(second)...");
List<DbValue> args = new List<DbValue>();
mstring datepart = mstring.Prepare("s");
args.Add(tools.AllocValue(datepart));
int number = 900;
args.Add(tools.AllocValue(number));
DateTime dt = new DateTime(2000, 9, 14, 12, 0, 0);
args.Add(tools.AllocValue(dt));
DbFunctionArguments fargs = new DbFunctionArguments(args);
DbValue valOutput = DbFunctions.DATEADD(tools, fargs);
ByteSlice bs = valOutput.Eval();
DateTime output = tools.GetDateTime(bs);
DateTime expected = dt.AddSeconds(number);
if (expected != output)
{
throw new Exception("DbFunctions.DATEADD(second) has failed. Expected result: " + expected.ToString() + ", but received: " + output.ToString());
}
else
{
Console.WriteLine("Expected results received.");
}
}
{
Console.WriteLine("Testing DbFunctions.DATEADD(millisecond)...");
List<DbValue> args = new List<DbValue>();
mstring datepart = mstring.Prepare("millisecond");
args.Add(tools.AllocValue(datepart));
int number = 900;
args.Add(tools.AllocValue(number));
DateTime dt = new DateTime(2000, 9, 14, 12, 0, 0);
args.Add(tools.AllocValue(dt));
DbFunctionArguments fargs = new DbFunctionArguments(args);
DbValue valOutput = DbFunctions.DATEADD(tools, fargs);
ByteSlice bs = valOutput.Eval();
DateTime output = tools.GetDateTime(bs);
DateTime expected = dt.AddMilliseconds(number);
if (expected != output)
{
throw new Exception("DbFunctions.DATEADD(millisecond) has failed. Expected result: " + expected.ToString() + ", but received: " + output.ToString());
}
else
{
Console.WriteLine("Expected results received.");
}
}
}
public static void DbFunctions_CAST()
{
DbFunctionTools tools = new DbFunctionTools();
DateTime dt = DateTime.Now;
DbValue[] conv = new DbValue[]
{
tools.AllocValue((int)-372), tools.AllocValue((int)-372),
tools.AllocValue((int)-372), tools.AllocValue((long)-372),
tools.AllocValue((int)-372), tools.AllocValue((double)-372),
//tools.AllocValue((int)dt.Ticks), tools.AllocValue(new DateTime((int)dt.Ticks)),
tools.AllocValue((int)-372), tools.AllocValue(mstring.Prepare("-372")),
tools.AllocValue((int)-372), tools.AllocValue(mstring.Prepare("-372"), 51),
tools.AllocValue((long)-372), tools.AllocValue((int)-372),
tools.AllocValue((long)-372), tools.AllocValue((long)-372),
tools.AllocValue((long)-372), tools.AllocValue((double)-372),
tools.AllocValue((long)dt.Ticks), tools.AllocValue(new DateTime((long)dt.Ticks)),
tools.AllocValue((long)-372), tools.AllocValue(mstring.Prepare("-372")),
tools.AllocValue((long)-372), tools.AllocValue(mstring.Prepare("-372"), 51),
tools.AllocValue((double)-372), tools.AllocValue((int)-372),
tools.AllocValue((double)-372), tools.AllocValue((long)-372),
tools.AllocValue((double)-372), tools.AllocValue((double)-372),
tools.AllocValue((double)dt.Ticks), tools.AllocValue(new DateTime((long)(double)dt.Ticks)),
tools.AllocValue((double)-372), tools.AllocValue(mstring.Prepare("-372")),
tools.AllocValue((double)-372), tools.AllocValue(mstring.Prepare("-372"), 51),
// Extra double ones:
tools.AllocValue((double)101.1), tools.AllocValue((int)101),
tools.AllocValue((double)101.1), tools.AllocValue((long)101),
tools.AllocValue((double)101.1), tools.AllocValue(mstring.Prepare((double)101.1)),
tools.AllocValue((double)(22.0/7.0)), tools.AllocValue(mstring.Prepare((double)(22.0/7.0))),
tools.AllocValue(dt), tools.AllocValue((int)dt.Ticks),
tools.AllocValue(dt), tools.AllocValue((long)dt.Ticks),
tools.AllocValue(dt), tools.AllocValue((double)dt.Ticks),
tools.AllocValue(dt), tools.AllocValue(dt),
tools.AllocValue(dt), tools.AllocValue(mstring.Prepare(dt.ToString())),
tools.AllocValue(dt), tools.AllocValue(mstring.Prepare(dt.ToString()), 51),
tools.AllocValue(mstring.Prepare("-372")), tools.AllocValue((int)-372),
tools.AllocValue(mstring.Prepare("-372")), tools.AllocValue((long)-372),
tools.AllocValue(mstring.Prepare("-372")), tools.AllocValue((double)-372),
tools.AllocValue(mstring.Prepare(dt.ToString())), tools.AllocValue(dt),
tools.AllocValue(mstring.Prepare("-372")), tools.AllocValue(mstring.Prepare("-372")),
tools.AllocValue(mstring.Prepare("-372")), tools.AllocValue(mstring.Prepare("-372"), 51),
// Extra string ones:
tools.AllocValue(mstring.Prepare("-372"), 51), tools.AllocValue(mstring.Prepare("-372"), 101),
tools.AllocValue(mstring.Prepare("-372"), 101), tools.AllocValue(mstring.Prepare("-372"), 51),
null
};
for (int ic = 0; ic + 2 <= conv.Length; ic += 2)
{
DbValue a = conv[ic + 0];
DbType atype;
ByteSlice abytes = a.Eval(out atype);
DbValue b = conv[ic + 1];
DbType btype;
ByteSlice bbytes = b.Eval(out btype);
Console.WriteLine("Testing DbFunctions.CAST({0} AS {1})...", atype.Name, btype.Name);
List<DbValue> args = new List<DbValue>();
args.Add(a);
args.Add(tools.AllocValue(mstring.Prepare("AS " + btype.Name)));
DbFunctionArguments fargs = new DbFunctionArguments(args);
DbValue r = DbFunctions.CAST(tools, fargs);
DbType rtype;
ByteSlice rbytes = r.Eval(out rtype);
if (rtype.ID != btype.ID)
{
throw new Exception(string.Format("DbFunctions.CAST({0} AS {1}) resulted in type {2}: result has unexpected ID of {3}",
atype.Name, btype.Name, btype.Name, rtype.ID));
}
if (rtype.Size != btype.Size)
{
throw new Exception(string.Format("DbFunctions.CAST({0} AS {1}) resulted in type {2}: result has unexpected size of {3}",
atype.Name, btype.Name, btype.Name, rtype.Size));
}
if (rtype.ID == DbTypeID.DATETIME)
{
if (tools.GetDateTime(rbytes).ToString() != tools.GetDateTime(bbytes).ToString())
{
throw new Exception(string.Format("DbFunctions.CAST({0} AS {1}) resulted in type {2}: result has unexpected value",
atype.Name, btype.Name, btype.Name));
}
}
else
{
for (int ix = 0; ix < rtype.Size; ix++)
{
if (rbytes[ix] != bbytes[ix])
{
throw new Exception(string.Format("DbFunctions.CAST({0} AS {1}) resulted in type {2}: result has unexpected value",
atype.Name, btype.Name, btype.Name));
}
}
}
}
}
public static DbValue DATEDIFF(DbFunctionTools tools, DbFunctionArguments args)
{
string FunctionName = "DATEDIFF";
args.EnsureCount(FunctionName, 3);
DbType arg0type;
ByteSlice arg0 = args[0].Eval(out arg0type);
DbType arg1type;
ByteSlice arg1 = args[1].Eval(out arg1type);
DbType arg2type;
ByteSlice arg2 = args[2].Eval(out arg2type);
if (Types.IsNullValue(arg0))
{
return tools.AllocNullValue(); // Give a null, take a null.
}
if (arg0type.ID != DbTypeID.CHARS)
{
args.Expected(FunctionName, 0, "input CHAR(n), got " + arg0type.Name.ToUpper());
}
if (Types.IsNullValue(arg1))
{
return tools.AllocNullValue(); // Give a null, take a null.
}
if (arg1type.ID != DbTypeID.DATETIME)
{
args.Expected(FunctionName, 0, "input DATETIME, got " + arg1type.Name.ToUpper());
}
if (Types.IsNullValue(arg2))
{
return tools.AllocNullValue(); // Give a null, take a null.
}
if (arg2type.ID != DbTypeID.DATETIME)
{
args.Expected(FunctionName, 0, "input DATETIME, got " + arg2type.Name.ToUpper());
}
string datepart = tools.GetString(arg0).ToUpperM().ToString();
DateTime startdate = tools.GetDateTime(arg1);
DateTime enddate = tools.GetDateTime(arg2);
double partdiff = 0;
switch (datepart)
{
case "YEAR":
case "YY":
case "YYYY":
partdiff = enddate.Year - startdate.Year;
break;
case "QUARTER":
case "QQ":
case "Q":
partdiff = GetMonthDiff(startdate.Year, startdate.Month, enddate.Year, enddate.Month) / 3;
break;
case "MONTH":
case "MM":
case "M":
partdiff = GetMonthDiff(startdate.Year, startdate.Month, enddate.Year, enddate.Month);
break;
case "DAY":
case "DD":
case "D":
{
DateTime sdate = new DateTime(startdate.Year, startdate.Month, startdate.Day);
DateTime edate = new DateTime(enddate.Year, enddate.Month, enddate.Day);
TimeSpan sp = edate - sdate;
partdiff = sp.TotalDays;
}
break;
case "WEEK":
case "WK":
case "WW":
{
DateTime sdate = new DateTime(startdate.Year, startdate.Month, startdate.Day);
DateTime edate = new DateTime(enddate.Year, enddate.Month, enddate.Day);
TimeSpan sp = edate - sdate;
partdiff = (int)sp.TotalDays / 7;
}
break;
case "HOUR":
case "HH":
{
DateTime sdate = new DateTime(startdate.Year, startdate.Month, startdate.Day, startdate.Hour, 0, 0);
DateTime edate = new DateTime(enddate.Year, enddate.Month, enddate.Day, enddate.Hour, 0, 0);
TimeSpan sp = edate - sdate;
partdiff = sp.TotalHours;
}
break;
case "MINUTE":
case "MI":
case "N":
{
DateTime sdate = new DateTime(startdate.Year, startdate.Month, startdate.Day, startdate.Hour, startdate.Minute, 0);
DateTime edate = new DateTime(enddate.Year, enddate.Month, enddate.Day, enddate.Hour, enddate.Minute, 0);
TimeSpan sp = edate - sdate;
partdiff = sp.TotalMinutes;
}
break;
case "SECOND":
case "SS":
case "S":
{
DateTime sdate = new DateTime(startdate.Year, startdate.Month, startdate.Day, startdate.Hour, startdate.Minute, startdate.Second);
DateTime edate = new DateTime(enddate.Year, enddate.Month, enddate.Day, enddate.Hour, enddate.Minute, enddate.Second);
TimeSpan sp = edate - sdate;
partdiff = sp.TotalSeconds;
}
break;
case "MILLISECOND":
case "MS":
{
TimeSpan sp = enddate - startdate;
partdiff = sp.TotalMilliseconds;
}
break;
default:
args.Expected(FunctionName, 0, "input datepart invalid");
return null;
}
return tools.AllocValue(partdiff);
}
public static DbValue DATEADD(DbFunctionTools tools, DbFunctionArguments args)
{
string FunctionName = "DATEADD";
args.EnsureCount(FunctionName, 3);
DbType arg0type;
ByteSlice arg0 = args[0].Eval(out arg0type);
DbType arg1type;
ByteSlice arg1 = args[1].Eval(out arg1type);
DbType arg2type;
ByteSlice arg2 = args[2].Eval(out arg2type);
if (Types.IsNullValue(arg0))
{
return(tools.AllocNullValue()); // Give a null, take a null.
}
if (arg0type.ID != DbTypeID.CHARS)
{
args.Expected(FunctionName, 0, "input CHAR(n), got " + arg0type.Name.ToUpper());
}
if (Types.IsNullValue(arg1))
{
return(tools.AllocNullValue()); // Give a null, take a null.
}
if (arg1type.ID != DbTypeID.INT)
{
args.Expected(FunctionName, 0, "input INT, got " + arg1type.Name.ToUpper());
}
if (Types.IsNullValue(arg2))
{
return(tools.AllocNullValue()); // Give a null, take a null.
}
if (arg2type.ID != DbTypeID.DATETIME)
{
args.Expected(FunctionName, 0, "input DATETIME, got " + arg2type.Name.ToUpper());
}
string datepart = tools.GetString(arg0).ToUpperM().ToString();
int num = tools.GetInt(arg1);
DateTime dt = tools.GetDateTime(arg2);
DateTime newdt = dt;
switch (datepart)
{
case "YEAR":
case "YY":
case "YYYY":
newdt = dt.AddYears(num);
break;
case "QUARTER":
case "QQ":
case "Q":
newdt = dt.AddMonths(num * 3);
break;
case "MONTH":
case "MM":
case "M":
newdt = dt.AddMonths(num);
break;
case "DAY":
case "DD":
case "D":
newdt = dt.AddDays(num);
break;
case "WEEK":
case "WK":
case "WW":
newdt = dt.AddDays(7 * num);
break;
case "HOUR":
case "HH":
newdt = dt.AddHours(num);
break;
case "MINUTE":
case "MI":
case "N":
newdt = dt.AddMinutes(num);
break;
case "SECOND":
case "SS":
case "S":
newdt = dt.AddSeconds(num);
break;
case "MILLISECOND":
case "MS":
newdt = dt.AddMilliseconds(num);
break;
default:
args.Expected(FunctionName, 0, "input datepart invalid");
return(null);
}
return(tools.AllocValue(newdt));
}
public static DbValue DATEADD(DbFunctionTools tools, DbFunctionArguments args)
{
string FunctionName = "DATEADD";
args.EnsureCount(FunctionName, 3);
DbType arg0type;
ByteSlice arg0 = args[0].Eval(out arg0type);
DbType arg1type;
ByteSlice arg1 = args[1].Eval(out arg1type);
DbType arg2type;
ByteSlice arg2 = args[2].Eval(out arg2type);
if (Types.IsNullValue(arg0))
{
return tools.AllocNullValue(); // Give a null, take a null.
}
if (arg0type.ID != DbTypeID.CHARS)
{
args.Expected(FunctionName, 0, "input CHAR(n), got " + arg0type.Name.ToUpper());
}
if (Types.IsNullValue(arg1))
{
return tools.AllocNullValue(); // Give a null, take a null.
}
if (arg1type.ID != DbTypeID.INT)
{
args.Expected(FunctionName, 0, "input INT, got " + arg1type.Name.ToUpper());
}
if (Types.IsNullValue(arg2))
{
return tools.AllocNullValue(); // Give a null, take a null.
}
if (arg2type.ID != DbTypeID.DATETIME)
{
args.Expected(FunctionName, 0, "input DATETIME, got " + arg2type.Name.ToUpper());
}
string datepart = tools.GetString(arg0).ToUpperM().ToString();
int num = tools.GetInt(arg1);
DateTime dt = tools.GetDateTime(arg2);
DateTime newdt = dt;
switch (datepart)
{
case "YEAR":
case "YY":
case "YYYY":
newdt = dt.AddYears(num);
break;
case "QUARTER":
case "QQ":
case "Q":
newdt = dt.AddMonths(num * 3);
break;
case "MONTH":
case "MM":
case "M":
newdt = dt.AddMonths(num);
break;
case "DAY":
case "DD":
case "D":
newdt = dt.AddDays(num);
break;
case "WEEK":
case "WK":
case "WW":
newdt = dt.AddDays(7 * num);
break;
case "HOUR":
case "HH":
newdt = dt.AddHours(num);
break;
case "MINUTE":
case "MI":
case "N":
newdt = dt.AddMinutes(num);
break;
case "SECOND":
case "SS":
case "S":
newdt = dt.AddSeconds(num);
break;
case "MILLISECOND":
case "MS":
newdt = dt.AddMilliseconds(num);
break;
default:
args.Expected(FunctionName, 0, "input datepart invalid");
return null;
}
return tools.AllocValue(newdt);
}
public static void DbFunctions_NULLIF()
{
DbFunctionTools tools = new DbFunctionTools();
{
Console.WriteLine("Testing DbFunctions.NULLIF(DateTime, DateTime)...");
List<DbValue> args = new List<DbValue>();
args.Add(tools.AllocValue(DateTime.Parse("12/1/2000 10:00:00 AM")));
args.Add(tools.AllocValue(DateTime.Parse("12/1/2000 10:00:00 AM")));
DbFunctionArguments fargs = new DbFunctionArguments(args);
DbValue valOutput = DbFunctions.NULLIF(tools, fargs);
ByteSlice bs = valOutput.Eval();
bool output = Types.IsNullValue(bs);
bool expected = true;
if (expected != output)
{
throw new Exception("DbFunctions.NULLIF(DateTime, DateTime) has failed. Expected result: " + expected.ToString() + ", but received: " + output.ToString());
}
else
{
Console.WriteLine("Expected results received.");
}
}
{
Console.WriteLine("Testing DbFunctions.NULLIF(DateTime, DateTime)...");
List<DbValue> args = new List<DbValue>();
DateTime dt = DateTime.Parse("12/1/2000 10:00:00 AM");
args.Add(tools.AllocValue(dt));
args.Add(tools.AllocValue(DateTime.Parse("12/2/2000 10:00:00 AM")));
DbFunctionArguments fargs = new DbFunctionArguments(args);
DbValue valOutput = DbFunctions.NULLIF(tools, fargs);
ByteSlice bs = valOutput.Eval();
DateTime output = tools.GetDateTime(bs);
DateTime expected = dt;
if (expected != output)
{
throw new Exception("DbFunctions.NULLIF(DateTime, DateTime) has failed. Expected result: " + expected.ToString() + ", but received: " + output.ToString());
}
else
{
Console.WriteLine("Expected results received.");
}
}
{
Console.WriteLine("Testing DbFunctions.NULLIF(Int32, Int32)...");
List<DbValue> args = new List<DbValue>();
args.Add(tools.AllocValue(10));
args.Add(tools.AllocValue(10));
DbFunctionArguments fargs = new DbFunctionArguments(args);
DbValue valOutput = DbFunctions.NULLIF(tools, fargs);
ByteSlice bs = valOutput.Eval();
bool output = Types.IsNullValue(bs);
bool expected = true;
if (expected != output)
{
throw new Exception("DbFunctions.NULLIF(Int32, Int32) has failed. Expected result: " + expected.ToString() + ", but received: " + output.ToString());
}
else
{
Console.WriteLine("Expected results received.");
}
}
{
Console.WriteLine("Testing DbFunctions.NULLIF(Int32, Int32)...");
List<DbValue> args = new List<DbValue>();
int x = 10;
args.Add(tools.AllocValue(x));
args.Add(tools.AllocValue(11));
DbFunctionArguments fargs = new DbFunctionArguments(args);
DbValue valOutput = DbFunctions.NULLIF(tools, fargs);
ByteSlice bs = valOutput.Eval();
int output = tools.GetInt(bs);
int expected = x;
if (expected != output)
{
throw new Exception("DbFunctions.NULLIF(Int32, Int32) has failed. Expected result: " + expected.ToString() + ", but received: " + output.ToString());
}
else
{
Console.WriteLine("Expected results received.");
}
}
{
Console.WriteLine("Testing DbFunctions.NULLIF(Int32, long)...");
List<DbValue> args = new List<DbValue>();
int x = 10;
long y = 10;
args.Add(tools.AllocValue(x));
args.Add(tools.AllocValue(y));
DbFunctionArguments fargs = new DbFunctionArguments(args);
DbValue valOutput = DbFunctions.NULLIF(tools, fargs);
ByteSlice bs = valOutput.Eval();
bool output = Types.IsNullValue(bs);
bool expected = true;
if (expected != output)
{
throw new Exception("DbFunctions.NULLIF(Int32, long) has failed. Expected result: " + expected.ToString() + ", but received: " + output.ToString());
}
else
{
Console.WriteLine("Expected results received.");
}
}
{
Console.WriteLine("Testing DbFunctions.NULLIF(Int32, long)...");
List<DbValue> args = new List<DbValue>();
int x = 10;
long y = 11;
args.Add(tools.AllocValue(x));
args.Add(tools.AllocValue(y));
DbFunctionArguments fargs = new DbFunctionArguments(args);
DbValue valOutput = DbFunctions.NULLIF(tools, fargs);
ByteSlice bs = valOutput.Eval();
int output = tools.GetInt(bs);
int expected = x;
if (expected != output)
{
throw new Exception("DbFunctions.NULLIF(Int32, long) has failed. Expected result: " + expected.ToString() + ", but received: " + output.ToString());
}
else
{
Console.WriteLine("Expected results received.");
}
}
}
public static DbValue COMPARE(DbFunctionTools tools, DbFunctionArguments args)
{
string FunctionName = "COMPARE";
args.EnsureCount(FunctionName, 2);
int result;
DbType arg0type;
ByteSlice arg0 = args[0].Eval(out arg0type);
DbType arg1type;
ByteSlice arg1 = args[1].Eval(out arg1type);
if (Types.IsNullValue(arg0))
{
result = int.MaxValue;
return(tools.AllocValue(result));
}
if (Types.IsNullValue(arg1))
{
result = int.MinValue;
return(tools.AllocValue(result));
}
int i0 = 0, i1 = 0;
long l0 = 0, l1 = 0;
double d0 = 0, d1 = 0;
switch (arg0type.ID)
{
case DbTypeID.INT:
i0 = tools.GetInt(arg0);
break;
case DbTypeID.LONG:
l0 = tools.GetLong(arg0);
break;
case DbTypeID.DOUBLE:
d0 = tools.GetDouble(arg0);
break;
case DbTypeID.CHARS:
{
if (arg1type.ID != DbTypeID.CHARS && arg1type.ID != DbTypeID.DATETIME)
{
args.Expected(FunctionName, 0, "input CHAR(n) or DATETIME, got " + arg1type.Name.ToUpper());
return(null); // Doesn't reach here.
}
if (arg1type.ID == DbTypeID.CHARS)
{
for (int i = 1; ; i += 2)
{
bool b1end = (i + 2 > arg0.Length) || (arg0[i] == 0 && arg0[i + 1] == 0);
bool b2end = (i + 2 > arg1.Length) || (arg1[i] == 0 && arg1[i + 1] == 0);
if (b1end)
{
if (b2end)
{
result = 0;
break;
}
result = -1;
break;
}
else if (b2end)
{
result = 1;
break;
}
int diff = Types.UTF16BytesToLowerChar(arg0[i], arg0[i + 1])
- Types.UTF16BytesToLowerChar(arg1[i], arg1[i + 1]);
if (0 != diff)
{
char ch0 = Types.UTF16BytesToChar(arg0[i], arg0[i + 1]);
char ch1 = Types.UTF16BytesToChar(arg1[i], arg1[i + 1]);
if (!Char.IsLetter(ch0) || !Char.IsLetter(ch1))
{
result = ch0 - ch1;
}
result = diff;
break;
}
}
}
else
{
string sdt = tools.GetString(arg0).ToString();
DateTime dt0 = DateTime.Parse(sdt);
DateTime dt1 = tools.GetDateTime(arg1);
result = dt0.CompareTo(dt1);
}
return(tools.AllocValue(result));
}
break;
case DbTypeID.DATETIME:
{
if (arg1type.ID != DbTypeID.CHARS && arg1type.ID != DbTypeID.DATETIME)
{
args.Expected(FunctionName, 0, "input CHAR(n) or DATETIME, got " + arg1type.Name.ToUpper());
return(null); // Doesn't reach here.
}
if (arg1type.ID == DbTypeID.DATETIME)
{
DateTime dt0 = tools.GetDateTime(arg0);
DateTime dt1 = tools.GetDateTime(arg1);
result = dt0.CompareTo(dt1);
}
else
{
DateTime dt0 = tools.GetDateTime(arg0);
string sdt = tools.GetString(arg1).ToString();
DateTime dt1 = DateTime.Parse(sdt);
result = dt0.CompareTo(dt1);
}
return(tools.AllocValue(result));
}
break;
default:
args.Expected(FunctionName, 0, "input INT, LONG or DOUBLE, got1 " + arg0type.Name.ToUpper());
return(null); // Doesn't reach here.
}
switch (arg1type.ID)
{
case DbTypeID.INT:
i1 = tools.GetInt(arg1);
break;
case DbTypeID.LONG:
l1 = tools.GetLong(arg1);
break;
case DbTypeID.DOUBLE:
d1 = tools.GetDouble(arg1);
break;
default:
args.Expected(FunctionName, 0, "input INT, LONG or DOUBLE, got2 " + arg1type.Name.ToUpper());
return(null); // Doesn't reach here.
}
switch (arg0type.ID)
{
case DbTypeID.INT:
switch (arg1type.ID)
{
case DbTypeID.INT:
result = i0.CompareTo(i1);
break;
case DbTypeID.LONG:
result = ((long)i0).CompareTo(l1);
break;
case DbTypeID.DOUBLE:
result = ((double)i0).CompareTo(d1);
break;
default:
return(null); // Should never happen.
}
break;
case DbTypeID.LONG:
switch (arg1type.ID)
{
case DbTypeID.INT:
result = l0.CompareTo((long)i1);
break;
case DbTypeID.LONG:
result = l0.CompareTo(l1);
break;
case DbTypeID.DOUBLE:
result = ((double)l0).CompareTo(d1);
break;
default:
return(null); // Should never happen.
}
break;
case DbTypeID.DOUBLE:
switch (arg1type.ID)
{
case DbTypeID.INT:
result = d0.CompareTo((double)i1);
break;
case DbTypeID.LONG:
result = d0.CompareTo((double)l1);
break;
case DbTypeID.DOUBLE:
result = d0.CompareTo(d1);
break;
default:
return(null); // Should never happen.
}
break;
default:
return(null); // Should never happen.
}
return(tools.AllocValue(result));
}
public static DbValue COMPARE(DbFunctionTools tools, DbFunctionArguments args)
{
string FunctionName = "COMPARE";
args.EnsureCount(FunctionName, 2);
int result;
DbType arg0type;
ByteSlice arg0 = args[0].Eval(out arg0type);
DbType arg1type;
ByteSlice arg1 = args[1].Eval(out arg1type);
if (Types.IsNullValue(arg0))
{
result = int.MaxValue;
return tools.AllocValue(result);
}
if (Types.IsNullValue(arg1))
{
result = int.MinValue;
return tools.AllocValue(result);
}
int i0 = 0, i1 = 0;
long l0 = 0, l1 = 0;
double d0 = 0, d1 = 0;
switch (arg0type.ID)
{
case DbTypeID.INT:
i0 = tools.GetInt(arg0);
break;
case DbTypeID.LONG:
l0 = tools.GetLong(arg0);
break;
case DbTypeID.DOUBLE:
d0 = tools.GetDouble(arg0);
break;
case DbTypeID.CHARS:
{
if (arg1type.ID != DbTypeID.CHARS && arg1type.ID != DbTypeID.DATETIME)
{
args.Expected(FunctionName, 0, "input CHAR(n) or DATETIME, got " + arg1type.Name.ToUpper());
return null; // Doesn't reach here.
}
if (arg1type.ID == DbTypeID.CHARS)
{
for (int i = 1; ; i += 2)
{
bool b1end = (i + 2 > arg0.Length) || (arg0[i] == 0 && arg0[i + 1] == 0);
bool b2end = (i + 2 > arg1.Length) || (arg1[i] == 0 && arg1[i + 1] == 0);
if (b1end)
{
if (b2end)
{
result = 0;
break;
}
result = -1;
break;
}
else if (b2end)
{
result = 1;
break;
}
int diff = Types.UTF16BytesToLowerChar(arg0[i], arg0[i + 1])
- Types.UTF16BytesToLowerChar(arg1[i], arg1[i + 1]);
if (0 != diff)
{
char ch0 = Types.UTF16BytesToChar(arg0[i], arg0[i + 1]);
char ch1 = Types.UTF16BytesToChar(arg1[i], arg1[i + 1]);
if (!Char.IsLetter(ch0) || !Char.IsLetter(ch1))
{
result = ch0 - ch1;
}
result = diff;
break;
}
}
}
else
{
string sdt = tools.GetString(arg0).ToString();
DateTime dt0 = DateTime.Parse(sdt);
DateTime dt1 = tools.GetDateTime(arg1);
result = dt0.CompareTo(dt1);
}
return tools.AllocValue(result);
}
break;
case DbTypeID.DATETIME:
{
if (arg1type.ID != DbTypeID.CHARS && arg1type.ID != DbTypeID.DATETIME)
{
args.Expected(FunctionName, 0, "input CHAR(n) or DATETIME, got " + arg1type.Name.ToUpper());
return null; // Doesn't reach here.
}
if (arg1type.ID == DbTypeID.DATETIME)
{
DateTime dt0 = tools.GetDateTime(arg0);
DateTime dt1 = tools.GetDateTime(arg1);
result = dt0.CompareTo(dt1);
}
else
{
DateTime dt0 = tools.GetDateTime(arg0);
string sdt = tools.GetString(arg1).ToString();
DateTime dt1 = DateTime.Parse(sdt);
result = dt0.CompareTo(dt1);
}
return tools.AllocValue(result);
}
break;
default:
args.Expected(FunctionName, 0, "input INT, LONG or DOUBLE, got1 " + arg0type.Name.ToUpper());
return null; // Doesn't reach here.
}
switch (arg1type.ID)
{
case DbTypeID.INT:
i1 = tools.GetInt(arg1);
break;
case DbTypeID.LONG:
l1 = tools.GetLong(arg1);
break;
case DbTypeID.DOUBLE:
d1 = tools.GetDouble(arg1);
break;
default:
args.Expected(FunctionName, 0, "input INT, LONG or DOUBLE, got2 " + arg1type.Name.ToUpper());
return null; // Doesn't reach here.
}
switch (arg0type.ID)
{
case DbTypeID.INT:
switch (arg1type.ID)
{
case DbTypeID.INT:
result = i0.CompareTo(i1);
break;
case DbTypeID.LONG:
result = ((long)i0).CompareTo(l1);
break;
case DbTypeID.DOUBLE:
result = ((double)i0).CompareTo(d1);
break;
default:
return null; // Should never happen.
}
break;
case DbTypeID.LONG:
switch (arg1type.ID)
{
case DbTypeID.INT:
result = l0.CompareTo((long)i1);
break;
case DbTypeID.LONG:
result = l0.CompareTo(l1);
break;
case DbTypeID.DOUBLE:
result = ((double)l0).CompareTo(d1);
break;
default:
return null; // Should never happen.
}
break;
case DbTypeID.DOUBLE:
switch (arg1type.ID)
{
case DbTypeID.INT:
result = d0.CompareTo((double)i1);
break;
case DbTypeID.LONG:
result = d0.CompareTo((double)l1);
break;
case DbTypeID.DOUBLE:
result = d0.CompareTo(d1);
break;
default:
return null; // Should never happen.
}
break;
default:
return null; // Should never happen.
}
return tools.AllocValue(result);
}