public void SqlPropertyNoStoreNonIdentifierGrouping([DataSources] string context)
{
using (new FirebirdQuoteMode(FirebirdIdentifierQuoteMode.Auto))
using (var db = GetDataContext(context))
using (db.CreateLocalTable(new []
{
new DynamicTablePrototype {
NotIdentifier = 77, Value = 5
},
new DynamicTablePrototype {
NotIdentifier = 77, Value = 5
}
}))
{
var query =
from d in db.GetTable <DynamicTable>()
group d by new { NI = Sql.Property <int>(d, TestProvNameDb2i.IsiSeries(context) ? "NotIdentifier" : "Not Identifier") }
into g
select new
{
g.Key.NI,
Count = g.Count(),
Sum = g.Sum(i => Sql.Property <int>(i, TestProvNameDb2i.IsiSeries(context) ? "SomeValue" : "Some Value"))
};
var result = query.ToArray();
Assert.AreEqual(77, result[0].NI);
Assert.AreEqual(2, result[0].Count);
Assert.AreEqual(10, result[0].Sum);
}
}
private static void AssertString(string?expected, string?actual, string provider)
{
if (TestProvNameDb2i.IsiSeriesOleDb(provider))
{
expected = expected?.TrimEnd(' ');
}
Assert.AreEqual(expected, actual);
}
private static void AssertChar(char?expected, char?actual, string provider)
{
if (!TestProvNameDb2i.IsiSeriesDB2Connect(provider) && expected == ' ')
{
expected = '\0';
}
Assert.AreEqual(expected, actual);
}
public void BulkCopyProviderSpecific([IncludeDataSources(TestProvNameDb2i.All_AccessClient, TestProvNameDb2i.All_DB2Connect)] string context)
{
if (TestProvNameDb2i.IsiSeriesAccessClient(context))
{
BulkCopyTest_DB2Connect(context, BulkCopyType.ProviderSpecific, 50000, 100001);
}
if (TestProvNameDb2i.IsiSeriesDB2Connect(context))
{
BulkCopyTest_AccessClient(context, BulkCopyType.ProviderSpecific, 50000, 100001);
}
}
public void TestNumerics([IncludeDataSources(TestProvNameDb2i.All)] string context)
{
var skipDecFloat = TestProvNameDb2i.IsiSeriesOleDb(context) ? " decfloat" : "";
using (var conn = new DataConnection(context))
{
TestSimple <sbyte>(conn, 1, DataType.SByte);
TestSimple <short>(conn, 1, DataType.Int16);
TestSimple <int>(conn, 1, DataType.Int32);
TestSimple <long>(conn, 1L, DataType.Int64);
TestSimple <byte>(conn, 1, DataType.Byte);
TestSimple <ushort>(conn, 1, DataType.UInt16);
TestSimple <uint>(conn, 1u, DataType.UInt32);
TestSimple <ulong>(conn, 1ul, DataType.UInt64);
TestSimple <float>(conn, 1, DataType.Single);
TestSimple <double>(conn, 1d, DataType.Double);
TestSimple <decimal>(conn, 1m, DataType.Decimal);
TestSimple <decimal>(conn, 1m, DataType.VarNumeric);
TestSimple <decimal>(conn, 1m, DataType.Money);
TestSimple <decimal>(conn, 1m, DataType.SmallMoney);
TestNumeric(conn, sbyte.MinValue, DataType.SByte);
TestNumeric(conn, sbyte.MaxValue, DataType.SByte);
TestNumeric(conn, short.MinValue, DataType.Int16);
TestNumeric(conn, short.MaxValue, DataType.Int16);
TestNumeric(conn, int.MinValue, DataType.Int32, "smallint");
TestNumeric(conn, int.MaxValue, DataType.Int32, "smallint real");
TestNumeric(conn, long.MinValue, DataType.Int64, "smallint int double" + skipDecFloat);
TestNumeric(conn, long.MaxValue, DataType.Int64, "smallint int double real" + skipDecFloat);
TestNumeric(conn, byte.MaxValue, DataType.Byte);
TestNumeric(conn, ushort.MaxValue, DataType.UInt16, "smallint");
TestNumeric(conn, uint.MaxValue, DataType.UInt32, "smallint int real");
TestNumeric(conn, ulong.MaxValue, DataType.UInt64, "smallint int real bigint double" + skipDecFloat);
TestNumeric(conn, -3.40282306E+38f, DataType.Single, "bigint int smallint decimal(31) decfloat");
TestNumeric(conn, 3.40282306E+38f, DataType.Single, "bigint int smallint decimal(31) decfloat");
TestNumeric(conn, -1.79E+308d, DataType.Double, "bigint int smallint decimal(31) decfloat real");
TestNumeric(conn, 1.79E+308d, DataType.Double, "bigint int smallint decimal(31) decfloat real");
TestNumeric(conn, decimal.MinValue, DataType.Decimal, "bigint int smallint double real" + skipDecFloat);
TestNumeric(conn, decimal.MaxValue, DataType.Decimal, "bigint int smallint double real" + skipDecFloat);
TestNumeric(conn, decimal.MinValue, DataType.VarNumeric, "bigint int smallint double real" + skipDecFloat);
TestNumeric(conn, decimal.MaxValue, DataType.VarNumeric, "bigint int smallint double real" + skipDecFloat);
TestNumeric(conn, -922337203685477m, DataType.Money, "int smallint real");
TestNumeric(conn, +922337203685477m, DataType.Money, "int smallint real");
TestNumeric(conn, -214748m, DataType.SmallMoney, "smallint");
TestNumeric(conn, +214748m, DataType.SmallMoney, "smallint");
}
}
public void StringTrimming([DataSources] string context)
{
using (var db = GetDataContext(context))
{
var lastId = db.GetTable <StringTestTable>().Select(_ => _.Id).Max();
try
{
//Strip null chars - not supported in DB2i
var testData = StringTestData.Where(_ => !(_.NString ?? string.Empty).Contains("\0")).ToList();
//foreach (var record in testData)
testData.ForEach(record =>
{
var query = db.GetTable <StringTestTable>().Value(_ => _.NString, record.NString);
query = query.Value(_ => _.String, record.String);
query.Insert();
});
var records = db.GetTable <StringTestTable>().Where(_ => _.Id > lastId).OrderBy(_ => _.Id).ToList();
Assert.AreEqual(testData.Count, records.Count);
testData.Zip(records, (expected, actual) => (expected, actual))
.ToList().ForEach(x =>
{
var(expected, actual) = x;
Assert.AreEqual(expected.String?.TrimEnd(' '), actual.String);
if (TestProvNameDb2i.IsiSeriesOleDb(context) && expected.NString is { } && expected.NString.StartsWith("test20")) //OleDb strips \u3000
{
Assert.AreEqual(expected.NString?.TrimEnd(), actual.NString);
}
else
{
Assert.AreEqual(expected.NString?.TrimEnd(' '), actual.NString);
}
});
public void SqlPropertyNoStoreNonIdentifier([DataSources] string context)
{
using (new FirebirdQuoteMode(FirebirdIdentifierQuoteMode.Auto))
using (var db = GetDataContext(context))
using (db.CreateLocalTable(new []
{
new DynamicTablePrototype {
NotIdentifier = 77
}
}))
{
var query =
from d in db.GetTable <DynamicTable>()
select new
{
NI = Sql.Property <int>(d, TestProvNameDb2i.IsiSeries(context) ? "NotIdentifier" : "Not Identifier")
};
var result = query.ToArray();
Assert.AreEqual(77, result[0].NI);
}
}
public void SchemaProvider([IncludeDataSources(TestProvNameDb2i.All_73)] string context)
{
using (var conn = new DataConnection(context))
{
var sp = conn.DataProvider.GetSchemaProvider();
var dbSchema = sp.GetSchema(conn);
var table = dbSchema.Tables.Single(t => t.IsDefaultSchema && t.TableName == "ALLTYPES");
if (TestProvNameDb2i.IsiSeries(context))
{
var binaryType = TestProvNameDb2i.IsiSeriesAccessClient(context) ? "BINARY(20)" : "BINARY";
Assert.That(table.Columns.Single(c => c.ColumnName == "BINARYDATATYPE").ColumnType, Is.EqualTo(binaryType));
Assert.That(table.Columns.Single(c => c.ColumnName == "VARBINARYDATATYPE").ColumnType, Is.EqualTo("VARBIN"));
}
else
{
Assert.That(table.Columns.Single(c => c.ColumnName == "BINARYDATATYPE").ColumnType, Is.EqualTo("CHAR (5) FOR BIT DATA"));
Assert.That(table.Columns.Single(c => c.ColumnName == "VARBINARYDATATYPE").ColumnType, Is.EqualTo("VARCHAR (5) FOR BIT DATA"));
}
}
}
public void TestDate([IncludeDataSources(TestProvNameDb2i.All)] string context)
{
using (var conn = new DataConnection(context))
{
var dateTime = new DateTime(2012, 12, 12);
Assert.That(
conn.ExecuteScalar <DateTime>("'2012-12-12'", "date"), Is.EqualTo(dateTime));
Assert.That(
conn.ExecuteScalar <DateTime?>("'2012-12-12'", "date"), Is.EqualTo(dateTime));
Assert.That(
conn.ExecuteScalarParameter <DateTime>(DataParameter.Date("p", dateTime), "date"), Is.EqualTo(dateTime));
Assert.That(
conn.ExecuteScalarParameter <DateTime>("p", "date", dateTime, DataType.Date), Is.EqualTo(dateTime));
//iSeries native provider cannot assign datetime parameter to date
if (!TestProvNameDb2i.IsiSeriesAccessClient(context))
{
Assert.That(
conn.ExecuteScalarParameter <DateTime>("p", "date", dateTime), Is.EqualTo(dateTime));
}
}
}
