public async Task PostgresAnonymousParametersA()
{
var db = new SPTestsDatabase();
// Only PostgreSQL supports anonymous parameters (AFAIK) - we treat object[] in the context of params differently from
// how it is treated when it appears in args in the standard Massive API, to provide support for this. (Note, object[]
// makes no sense in the context of named parameters otherwise, and will throw an exception on the other DBs.)
dynamic fnResultAnon = await db.ExecuteProcedureAsync("find_max", inParams : new object[] { 12, 7 }, returnParams : new { returnValue = 0 });
Assert.AreEqual(12, fnResultAnon.returnValue);
}
public async Task NonQueryFromMixedCursorOutput()
{
var db = new SPTestsDatabase();
// Following the Oracle pattern this will not dereference: we get a variable value and a cursor ref.
var itemCursorMix = await db.ExecuteProcedureAsync("cursor_mix", outParams : new { anyname = new Cursor(), othername = 0 });
Assert.AreEqual(42, itemCursorMix.othername);
Assert.AreEqual(typeof(Cursor), itemCursorMix.anyname.GetType());
Assert.AreEqual(typeof(string), ((Cursor)itemCursorMix.anyname).CursorRef.GetType()); // NB PostgreSql ref cursors return as string
}
public async Task ReadOutputParamsUsingQuery()
{
var db = new SPTestsDatabase();
// Again this is Postgres specific: output params are really part of data row and can be read that way
var record = await db.SingleFromProcedureAsync("test_vars", new { w = 2 });
Assert.AreEqual(3, record.v);
Assert.AreEqual(4, record.w);
Assert.AreEqual(5, record.x);
}
public async Task ProvideValueToInputOutputParam()
{
var db = new SPTestsDatabase();
// w := w + 2; v := w - 1; x := w + 1
dynamic testResult = await db.ExecuteProcedureAsync("test_vars", ioParams : new { w = 2 }, outParams : new { v = 0, x = 0 });
Assert.AreEqual(3, testResult.v);
Assert.AreEqual(4, testResult.w);
Assert.AreEqual(5, testResult.x);
}
public void InitialNullInputOutputParam()
{
var db = new SPTestsDatabase();
dynamic xParam = new ExpandoObject();
xParam.x = null;
dynamic squareResult = db.ExecuteProcedure("square_num", ioParams: xParam);
Assert.AreEqual(null, squareResult.x);
}
public void QueryMultipleWithBreaks()
{
var db = new SPTestsDatabase();
var resultCTestFull = db.QueryMultipleFromProcedure("cbreaktest", outParams: new { c1 = new Cursor(), c2 = new Cursor() });
CheckMultiResultSetStructure(resultCTestFull, 10, 11);
var resultCTestToBreak = db.QueryMultipleFromProcedure("cbreaktest", ioParams: new { c1 = new Cursor(), c2 = new Cursor() });
CheckMultiResultSetStructure(resultCTestToBreak, breakTest: true);
}
public async Task NonQueryWithTwoOutputCursors()
{
var db = new SPTestsDatabase(ProviderName);
var twoSetDirect = await db.ExecuteProcedureAsync("tworesults", outParams : new { prc1 = new Cursor(), prc2 = new Cursor() });
Assert.AreEqual(typeof(Cursor), twoSetDirect.prc1.GetType());
Assert.AreEqual("OracleRefCursor", ((Cursor)twoSetDirect.prc1).CursorRef.GetType().Name);
Assert.AreEqual(typeof(Cursor), twoSetDirect.prc2.GetType());
Assert.AreEqual("OracleRefCursor", ((Cursor)twoSetDirect.prc2).CursorRef.GetType().Name);
}
public async Task QuerySetOfRecordsFromFunction()
{
var db = new SPTestsDatabase();
var setOfRecords = await db.QueryFromProcedureAsync("sum_n_product_with_tab", new { x = 10 });
int count = 0;
await setOfRecords.ForEachAsync(innerRecord => {
Console.WriteLine(innerRecord.sum + "\t|\t" + innerRecord.product);
count++;
});
Assert.AreEqual(4, count);
}
public async Task InitialNullDateReturnParamMethod2()
{
var db = new SPTestsDatabase();
// NB This is PostgreSql specific; Npgsql completely ignores the output parameter type and sets it (sensibly) from the return type.
dynamic dParam = new ExpandoObject();
dParam.d = null;
dynamic dateResult = await db.ExecuteProcedureAsync("get_date", returnParams : dParam);
Assert.AreEqual(typeof(DateTime), dateResult.d.GetType());
}
public async Task DefaultValueFromNullInputOutputParam_CrossDb()
{
// This is the cross-DB compatible way to do it.
var db = new SPTestsDatabase();
// w := w + 2; v := w - 1; x := w + 1
dynamic testResult = await db.ExecuteProcedureAsync("test_vars", ioParams : new { w = (int?)null }, outParams : new { v = 0, x = 0 });
Assert.AreEqual(1, testResult.v);
Assert.AreEqual(2, testResult.w);
Assert.AreEqual(3, testResult.x);
}
public async Task NonQueryFromMixedCursorOutput()
{
var db = new SPTestsDatabase(ProviderName);
var mixedDirect = await db.ExecuteProcedureAsync("mixedresults", outParams : new { prc1 = new Cursor(), prc2 = new Cursor(), num1 = 0, num2 = 0 });
Assert.AreEqual(typeof(Cursor), mixedDirect.prc1.GetType());
Assert.AreEqual("OracleRefCursor", ((Cursor)mixedDirect.prc1).CursorRef.GetType().Name);
Assert.AreEqual(typeof(Cursor), mixedDirect.prc2.GetType());
Assert.AreEqual("OracleRefCursor", ((Cursor)mixedDirect.prc2).CursorRef.GetType().Name);
Assert.AreEqual(1, mixedDirect.num1);
Assert.AreEqual(2, mixedDirect.num2);
}
public async Task InitialNullDateReturnParamMethod3()
{
var db = new SPTestsDatabase();
// Look - it REALLY ignores the parameter type. This would not work on other ADO.NET providers.
// (Look at per-DB method: `private static bool IgnoresOutputTypes(this DbParameter p);`)
dynamic dParam = new ExpandoObject();
dParam.d = false;
dynamic dateResult = await db.ExecuteProcedureAsync("get_date", returnParams : dParam);
Assert.AreEqual(typeof(DateTime), dateResult.d.GetType());
}
public async Task InitialNullIntegerOutputParam()
{
var db = new SPTestsDatabase();
// NB This is PostgreSql specific; Npgsql completely ignores the output parameter type and sets it (sensibly) from the return type.
dynamic z = new ExpandoObject();
z.z = null;
dynamic procResult = await db.ExecuteProcedureAsync("find_min", inParams : new { x = 5, y = 3 }, outParams : z);
Assert.AreEqual(typeof(int), procResult.z.GetType());
Assert.AreEqual(3, procResult.z);
}
public async Task DereferenceCursorOutputParameter()
{
var db = new SPTestsDatabase();
// Unlike the Oracle data access layer, Npgsql v3 does not dereference cursor parameters.
// We have added back the support for this which was previously in Npgsql v2.
var employees = await db.QueryFromProcedureAsync("cursor_employees", outParams: new { refcursor = new Cursor() });
int count = 0;
await employees.ForEachAsync(employee => {
Console.WriteLine(employee.firstname + " " + employee.lastname);
count++;
});
Assert.AreEqual(9, count);
}
public void QueryFromStoredProcedure()
{
var db = new SPTestsDatabase();
var people = db.QueryFromProcedure("uspGetEmployeeManagers", new { BusinessEntityID = 35 });
int count = 0;
foreach (var person in people)
{
Console.WriteLine(person.FirstName + " " + person.LastName);
count++;
}
Assert.AreEqual(3, count);
}
public void QuerySetOfRecordsFromFunction()
{
var db = new SPTestsDatabase();
var setOfRecords = db.QueryFromProcedure("sum_n_product_with_tab", new { x = 10 });
int count = 0;
foreach (var innerRecord in setOfRecords)
{
Console.WriteLine(innerRecord.sum + "\t|\t" + innerRecord.product);
count++;
}
Assert.AreEqual(4, count);
}
public async Task QueryFromStoredProcedure()
{
var db = new SPTestsDatabase();
var people = await db.QueryFromProcedureAsync("uspGetEmployeeManagers", new { BusinessEntityID = 35 });
int count = 0;
await people.ForEachAsync(person => {
Console.WriteLine(person.FirstName + " " + person.LastName);
count++;
});
Assert.AreEqual(3, count);
}
public void DereferenceCursorValuedFunction()
{
var db = new SPTestsDatabase(ProviderName);
// Oracle function one cursor return value
var employees = db.QueryFromProcedure("get_dept_emps", inParams: new { p_DeptNo = 10 }, returnParams: new { v_rc = new Cursor() });
int count = 0;
foreach (var employee in employees)
{
Console.WriteLine(employee.EMPNO + " " + employee.ENAME);
count++;
}
Assert.AreEqual(3, count);
}
public void DereferenceCursorOutputParameter()
{
var db = new SPTestsDatabase(ProviderName);
// Oracle procedure one cursor output variables
var moreEmployees = db.QueryFromProcedure("myproc", outParams: new { prc = new Cursor() });
int count = 0;
foreach (var employee in moreEmployees)
{
Console.WriteLine(employee.EMPNO + " " + employee.ENAME);
count++;
}
Assert.AreEqual(14, count);
}
public void DereferenceFromQuery_AutoWrapping()
{
var db = new SPTestsDatabase();
// use dummy cursor to trigger wrapping transaction support in Massive
var employees = db.QueryWithParams("SELECT * FROM cursor_employees()", outParams: new { abc = new Cursor() });
int count = 0;
foreach (var employee in employees)
{
Console.WriteLine(employee.firstname + " " + employee.lastname);
count++;
}
Assert.AreEqual(9, count);
}
public async Task DereferenceFromQuery_AutoWrapping()
{
var db = new SPTestsDatabase();
// use dummy cursor to trigger wrapping transaction support in Massive
// TO DO: document this (the idea is that this requires a wrapping transaction, but Mighty will do this for us if we do this;
// still to remind exactly why and document)
var employees = await db.QueryWithParamsAsync("SELECT * FROM cursor_employees()", outParams: new { abc = new Cursor() });
int count = 0;
await employees.ForEachAsync(employee => {
Console.WriteLine(employee.firstname + " " + employee.lastname);
count++;
});
Assert.AreEqual(9, count);
}
public async Task DereferenceCursorOutputParameter()
{
var db = new SPTestsDatabase(ProviderName);
// Oracle procedure one cursor output variables
var moreEmployees = await db.QueryFromProcedureAsync("myproc", outParams : new { prc = new Cursor() });
int count = 0;
await moreEmployees.ForEachAsync(employee => {
Console.WriteLine(employee.EMPNO + " " + employee.ENAME);
count++;
});
Assert.AreEqual(14, count);
}
public async Task DereferenceCursorValuedFunction()
{
var db = new SPTestsDatabase(ProviderName);
// Oracle function one cursor return value
var employees = await db.QueryFromProcedureAsync("get_dept_emps", inParams : new { p_DeptNo = 10 }, returnParams : new { v_rc = new Cursor() });
int count = 0;
await employees.ForEachAsync(employee => {
Console.WriteLine(employee.EMPNO + " " + employee.ENAME);
count++;
});
Assert.AreEqual(3, count);
}
public async Task InputCursors_1XN()
{
var db = new SPTestsDatabase();
db.NpgsqlAutoDereferenceCursors = false; // for this instance only
// cursors in PostgreSQL must share a transaction (not just a connection, as in Oracle)
using (var conn = await db.OpenConnectionAsync())
{
using (var trans = conn.BeginTransaction())
{
// Including a cursor param is optional and makes no difference, because Npgsql/PostgreSQL is lax about such things
// and we don't need to hint to Massive to do anything special
var cursors = await db.QueryFromProcedureAsync("cursorOneByN", connection : conn); //, outParams: new { abcdef = new Cursor() });
string[] cursor = new string[2];
int i = 0;
await cursors.ForEachAsync(item =>
{
cursor[i++] = item.cursoronebyn;
});
Assert.AreEqual(2, i);
var cursor1 = await db.QueryFromProcedureAsync("fetch_next_ints_from_cursor", new { mycursor = new Cursor(cursor[0]) }, connection : conn);
int count1 = 0;
await cursor1.ForEachAsync(item =>
{
Assert.AreEqual(1, item.myint1);
Assert.AreEqual(2, item.myint2);
count1++;
});
Assert.AreEqual(1, count1);
var cursor2 = await db.QueryFromProcedureAsync("fetch_next_ints_from_cursor", new { mycursor = new Cursor(cursor[1]) }, connection : conn);
int count2 = 0;
await cursor2.ForEachAsync(item =>
{
Assert.AreEqual(3, item.myint1);
Assert.AreEqual(4, item.myint2);
count2++;
});
Assert.AreEqual(1, count2);
trans.Commit();
}
}
}
public async Task DefaultValueFromNullInputOutputParam_Npgsql()
{
var db = new SPTestsDatabase();
// the two lines create a null w param with a no type; on most DB providers this only works
// for input params, where a null is a null is a null, but not on output params, where
// we need to know what type the output var should be; but some providers plain ignore
// the output type - in which case we do not insist that the user provide one
dynamic wArgs = new ExpandoObject();
wArgs.w = null;
// w := w + 2; v := w - 1; x := w + 1
dynamic testResult = await db.ExecuteProcedureAsync("test_vars", ioParams: wArgs, outParams: new { v = 0, x = 0 });
Assert.AreEqual(1, testResult.v);
Assert.AreEqual(2, testResult.w);
Assert.AreEqual(3, testResult.x);
}
public async Task LargeCursor_AutomaticDereferencing()
{
var db = new SPTestsDatabase();
// Either of these will show big server-side buffers in PostrgeSQL logs (but will still pass)
//db.AutoDereferenceFetchSize = -1; // FETCH ALL
//db.AutoDereferenceFetchSize = 400000;
var fetchTest = await db.QueryFromProcedureAsync("lump", returnParams: new { cursor = new Cursor() });
int count = 0;
await fetchTest.ForEachAsync(item => {
count++;
// there is no ORDER BY (it would not be sensible on such a huge data set) - this only sometimes works...
//Assert.AreEqual(count, item.id);
});
Assert.AreEqual(LargeCursorSize, count);
}
public void In_Out_Params_SQL()
{
var _providerName = ProviderName;
if (ProviderName == "MySql.Data.MySqlClient")
{
// this must be added to access user variables on the Oracle/MySQL driver
_providerName += ";AllowUserVariables=true";
}
var db = new SPTestsDatabase(_providerName);
// old skool SQL
// this approach only works on the Oracle/MySQL driver if "AllowUserVariables=true" is included in the connection string
var result = db.Scalar("CALL testproc_in_out(10, @param2); SELECT @param2");
Assert.AreEqual((long)20, result);
}
public void HugeCursorTest()
{
var db = new SPTestsDatabase();
//// Huge cursor tests....
var config = db.SingleFromQuery("SELECT current_setting('work_mem') work_mem, current_setting('log_temp_files') log_temp_files");
//// huge data from SELECT *
//var resultLargeSelectTest = await db.QueryWithParamsAsync("SELECT * FROM large");
//foreach(var item in resultLargeSelectTest)
//{
// int a = 1;
//}
//// huge data from (implicit) FETCH ALL
//// AUTO-DEREFERENCE TWO HUGE, ONLY FETCH FROM ONE
//var resultLargeProcTest = await db.QueryFromProcedureAsync("lump2", returnParams: new { abc = new Cursor() });
//foreach (var item in resultLargeProcTest)
//{
// Console.WriteLine(item.id);
// break;
//}
var results = await db.QueryMultipleFromProcedureAsync("lump2", returnParams : new { abc = new Cursor() });
db.NpgsqlAutoDereferenceFetchSize = 4000000;
await CheckMultiResultSetStructureAsync(results, 10000000, 10000000, true, true);
// one item from cursor
//using (var conn = db.OpenConnection())
//{
// using (var trans = conn.BeginTransaction())
// {
// var result = db.ExecuteAsProcedure("lump2", returnParams: new { abc = new Cursor(), def = new Cursor() }, connection: conn);
// var singleItemTest = await db.QueryWithParamsAsync($@"FETCH 5000000 FROM ""{result.abc}"";", connection: conn);
// foreach (var item in singleItemTest)
// {
// Console.WriteLine(item.id);
// break;
// }
// NB plain Execute() did NOT take a connection, and changing this MIGHT be an API breaking change??? TEST...!
// (This is the first, and so far only, really unwanted side effect of trying to stay 100% non-breaking.)
// db.Execute($@"CLOSE ""{result.abc}"";", conn);
// trans.Commit();
// }
//}
}
public async Task InputCursors_TransactionScope(bool explicitConnection)
{
var db = new SPTestsDatabase(explicitConnection);
if (explicitConnection)
{
MightyTests.ConnectionStringUtils.CheckConnectionStringRequiredForOpenConnectionAsync(db);
}
// cursors in PostgreSQL must share a transaction (not just a connection, as in Oracle)
using (var conn = await db.OpenConnectionAsync(
explicitConnection ?
MightyTests.ConnectionStringUtils.GetConnectionString(TestConstants.ReadWriteTestConnection, TestConstants.ProviderName) :
null
))
{
using (var trans = conn.BeginTransaction())
{
var cursors = await db.ExecuteProcedureAsync("cursorNByOne", outParams : new { c1 = new Cursor(), c2 = new Cursor() }, connection : conn);
var cursor1 = await db.QueryFromProcedureAsync("fetch_next_ints_from_cursor", new { mycursor = cursors.c1 }, connection : conn);
int count1 = 0;
await cursor1.ForEachAsync(item =>
{
Assert.AreEqual(11, item.myint1);
Assert.AreEqual(22, item.myint2);
count1++;
});
Assert.AreEqual(1, count1);
var cursor2 = await db.QueryFromProcedureAsync("fetch_next_ints_from_cursor", new { mycursor = cursors.c2 }, connection : conn);
int count2 = 0;
await cursor2.ForEachAsync(item =>
{
Assert.AreEqual(33, item.myint1);
Assert.AreEqual(44, item.myint2);
count2++;
});
Assert.AreEqual(1, count2);
trans.Commit();
}
}
}
public void Function_Call_Byte()
{
var db = new SPTestsDatabase(ProviderName);
var result = db.ExecuteProcedure("inventory_in_stock",
inParams: new { p_inventory_id = 5 },
returnParams: new { retval = (byte)1 });
if (ProviderName == "Devart.Data.MySql")
{
Assert.AreEqual(typeof(short), result.retval.GetType());
}
else
{
Assert.AreEqual(typeof(byte), result.retval.GetType());
}
Assert.AreEqual(1, result.retval);
}
