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