Calling a Stored Procedure from OBDC

This topic came up and I found this Oracle documents about using ODBC. Here is an excerpt:

XIV. Calling a Stored Procedure Via ODBC

[See also the Stored Procedures from Microsoft  Visual Basic section of this paper.]

The following is an example of the Visual Basic syntax for calling a stored procedure via ODBC:

db.ExecuteSQL(“{CALL procedurename(param1,param2,param3)}”)

NOTE: This assumes input parameters only and that you have assembled this such that each of the parameters is embedded into the string as a literal. Also note that this syntax DOES NOT work with packaged procedures, for those you must use the alternative begin …end; syntax

In the above example dB is assumed to be a valid database object. If you are using a tool such as MSQuery just use the {CALL …} (ODBC Procedure Call Escape) syntax without the double quotes. You must include the () even when you don’t have any parameters. Out parameters are supported at the ODBC Level 2 conformance (Oracle7 ODBC Version 2.x). The Oracle Level 1 drivers (Version 1.x) will not support this, you must be using a Level 2 or better driver. The 7.3 Oracle driver does not support returning dynasets. This functionality is first implemented in the 8.0.5.x version of the driver. An alternative to the call syntax is shown below:

db.ExecuteSQL(“BEGIN procedurename(param1,param2,param3); END;”, SQLPASSTHROUGH)

This alternative does require the use of the SQLPASSTHROUGH parameter, but will also allow for calling packaged procedures (i.e. packagename.procedurename()).

To return a result set with a stored procedure, refer to the following Microsoft knowledge base articles:

  • Q147938 (RDO)
  • Q126992 (DAO)

The Microsoft provided Oracle ODBC supports this functionality through the use of PL/SQL table types. The Oracle provided drivers do not support this functionality prior to version 8.0.5.x (where it is implemented in PL/SQL by returning a REF CURSOR).

For simple output parameters from a stored procedure you could use the following SQL:

{call procname(?,?)} The above would be passed to SQLExecute() and then have called SQLBindCol() or SQLBindParameter() for the output bind variables (the variables referred to by the ‘?’) you defined in your program. [Note: the Begin; … End; syntax would also work just as well here.] If you are using the Oracle 8.0.x ODBC driver and receiving an ORA-6502 and/or ORA-6512 errors, you must upgrade the driver to version 8.0.3.0.1 or later and update your MDAC to the latest version.

Have someone else make you password for you

For me I’m always trying to think of a good password without mashing my keyboard. Here are a couple of sites I use:

  • [Strong] Secure Password Generator – Favorite – I put ‘strong’ in brackets because if you search for it Google shows it as ‘Strong Secure Password Generator ‘
  • Norton Password Generate – The antivirus/antimalware company has their own generator. It pretty good and configurable. It’s not my favorite because most of the is cover for advertisement for their other production.
  • LastPass Password Generator – Tons of configuration and really ‘pretty’
  • DashLine – Another pretty with nifty slider. This site was made more for mobile use. So the toll elements may look huge on a conventional monitor.

Find All Columns and Tables in Oracle

Source:
https://www.thepolyglotdeveloper.com/2015/01/find-tables-oracle-database-column-name/

Depending on your access you will use one of the following queries:

-- Standard Users
select table_name from all_tab_columns where column_name = 'PICK_COLUMN';
-- DBA Users
select table_name from dba_tab_columns where column_name = 'PICK_COLUMN';
-- Just want to see what table looks like
describe namespace.tableName

If you want some database tool here are ones I like. I usually go for the ‘kitchen sink’ toolsets:

Updated 5/15 – Add looking for column in a known table

SELECT * 
FROM all_tab_columns 
WHERE table_name = 'MyTableName'
ORDER BY COLUMN_ID;

Month Names (Stupidly Useful)

Often times I want the stupidest things in an array or table. Today’s item is month’s name.

Month Names in a Table to Copy and Paste to a Spreadsheet

Month Name 3 Letter Name Zero Based Index One Based Index
January Jan 0 1
February Feb 1 2
March Mar 2 3
April Apr 3 4
May May 4 5
June Jun 5 6
July Jul 6 7
August Aug 7 8
September Sep 8 9
October Oct 9 10
November Nov 10 11
December Dec 11 12

String in Various Arrays

// Javascript - Zero based array
var monthNames = ["January", "February", "March", "April", "May", "June", "July", "August", "September", "October", "November", "December"];

// Javascript - One based array
var monthNames = ["Shift 1", "January", "February", "March", "April", "May", "June", "July", "August", "September", "October", "November", "December"];

// C# - Zero based array
string [] monthNames = new string [] {"January", "February", "March", "April", "May", "June", "July", "August", "September", "October", "November", "December"};

// C# - One based array
string [] monthNames = new string [] {
"Shift 1", "January", "February", "March", "April", "May", "June", "July", "August", "September", "October", "November", "December"};

Why have a one based array?
Most people will enter months starting at ‘1’ not zero. Most date object return January as a 1. Use which work best for you.

Oracle TRUNCATE TABLE

Source: https://docs.oracle.com/cd/B28359_01/server.111/b28286/statements_10007.htm#SQLRF01707

Caution:You cannot roll back a TRUNCATE TABLE statement, nor can you use a FLASHBACKTABLE statement to retrieve the contents of a table that has been truncated.

Use the TRUNCATE TABLE statement to remove all rows from a table. By default, Oracle Database also performs the following tasks:

  • Deallocates all space used by the removed rows except that specified by the MINEXTENTS storage parameter
  • Sets the NEXT storage parameter to the size of the last extent removed from the segment by the truncation process

Removing rows with the TRUNCATE TABLE statement can be more efficient than dropping and re-creating a table. Dropping and re-creating a table invalidates dependent objects of the table, requires you to regrant object privileges on the table, and requires you to re-create the indexes, integrity constraints, and triggers on the table and respecify its storage parameters. Truncating has none of these effects.

Removing rows with the TRUNCATE TABLE statement can be faster than removing all rows with the DELETE statement, especially if the table has numerous triggers, indexes, and other dependencies.

See Also:

Lambda expressions (C# Programming Guide)

Source: Microsoft – https://docs.microsoft.com/en-us/dotnet/csharp/programming-guide/statements-expressions-operators/lambda-expressions

A lambda expression is a block of code (an expression or a statement block) that is treated as an object. It can be passed as an argument to methods, and it can also be returned by method calls. Lambda expressions are used extensively for:

Lambda expressions are code that can be represented either as a delegate, or as an expression tree that compiles to a delegate. The specific delegate type of a lambda expression depends on its parameters and return value. Lambda expressions that don’t return a value correspond to a specific Action delegate, depending on its number of parameters. Lambda expressions that return a value correspond to a specific Func delegate, depending on its number of parameters. For example, a lambda expression that has two parameters but returns no value corresponds to an Action<T1,T2> delegate. A lambda expression that has one parameter and returns a value corresponds to Func<T,TResult> delegate.

A lambda expression uses =>, the lambda declaration operator, to separate the lambda’s parameter list from its executable code. To create a lambda expression, you specify input parameters (if any) on the left side of the lambda operator, and you put the expression or statement block on the other side. For example, the single-line lambda expression x => x * x specifies a parameter that’s named x and returns the value of x squared. You can assign this expression to a delegate type, as the following example shows:

Func<int, int> square = x => x * x;
Console.WriteLine(square(5));
// Output:
// 25

You also can assign a lambda expression to an expression tree type:

System.Linq.Expressions.Expression<Func<int, int>> e = x => x * x;
Console.WriteLine(e);
// Output:
// x => (x * x)

Or you can pass it directly as a method argument:

int[] numbers = { 2, 3, 4, 5 };
var squaredNumbers = numbers.Select(x => x * x);
Console.WriteLine(string.Join(" ", squaredNumbers));
// Output:
// 4 9 16 25

When you use method-based syntax to call the Enumerable.Select method in the System.Linq.Enumerable class (as you do in LINQ to Objects and LINQ to XML) the parameter is a delegate type System.Func<T,TResult>. A lambda expression is the most convenient way to create that delegate. When you call the Queryable.Select method in the System.Linq.Queryable class (as you do in LINQ to SQL) the parameter type is an expression tree type Expression<Func<TSource,TResult>>. Again, a lambda expression is just a very concise way to construct that expression tree. The lambdas allow the Select calls to look similar although in fact the type of object created from the lambda is different.

All restrictions that apply to anonymous methods also apply to lambda expressions.

Expression lambdas

A lambda expression with an expression on the right side of the => operator is called an expression lambda. Expression lambdas are used extensively in the construction of expression trees. An expression lambda returns the result of the expression and takes the following basic form:

(input-parameters) => expression

The parentheses are optional only if the lambda has one input parameter; otherwise they are required.

Specify zero input parameters with empty parentheses:

Action line = () => Console.WriteLine();

Two or more input parameters are separated by commas enclosed in parentheses:

Func<int, int, bool> testForEquality = (x, y) => x == y;

Sometimes it’s impossible for the compiler to infer the input types. You can specify the types explicitly as shown in the following example:

Func<int, string, bool> isTooLong = (int x, string s) => s.Length > x;

Input parameter types must be all explicit or all implicit; otherwise, a CS0748 compiler error occurs.

The body of an expression lambda can consist of a method call. However, if you are creating expression trees that are evaluated outside the context of the .NET common language runtime, such as in SQL Server, you should not use method calls in lambda expressions. The methods will have no meaning outside the context of the .NET common language runtime.

Statement lambdas

A statement lambda resembles an expression lambda except that the statement(s) is enclosed in braces:

(input-parameters) => { statement; }

The body of a statement lambda can consist of any number of statements; however, in practice there are typically no more than two or three.

Action<string> greet = name => 
{ 
    string greeting = $"Hello {name}!";
    Console.WriteLine(greeting);
};
greet("World");
// Output:
// Hello World!

Statement lambdas, like anonymous methods, cannot be used to create expression trees.

Async lambdas

You can easily create lambda expressions and statements that incorporate asynchronous processing by using the async and await keywords. For example, the following Windows Forms example contains an event handler that calls and awaits an async method, ExampleMethodAsync.

public partial class Form1 : Form
{
    public Form1()
    {
        InitializeComponent();
        button1.Click += button1_Click;
    }

    private async void button1_Click(object sender, EventArgs e)
    {
        await ExampleMethodAsync();
        textBox1.Text += "\r\nControl returned to Click event handler.\n";
    }

    private async Task ExampleMethodAsync()
    {
        // The following line simulates a task-returning asynchronous process.
        await Task.Delay(1000);
    }
}

You can add the same event handler by using an async lambda. To add this handler, add an async modifier before the lambda parameter list, as the following example shows:

public partial class Form1 : Form
{
    public Form1()
    {
        InitializeComponent();
        button1.Click += async (sender, e) =>
        {
            await ExampleMethodAsync();
            textBox1.Text += "\r\nControl returned to Click event handler.\n";
        };
    }

    private async Task ExampleMethodAsync()
    {
        // The following line simulates a task-returning asynchronous process.
        await Task.Delay(1000);
    }
}

For more information about how to create and use async methods, see Asynchronous Programming with async and await.

Lambda expressions and tuples

Starting with C# 7.0, the C# language provides built-in support for tuples. You can provide a tuple as an argument to a lambda expression, and your lambda expression can also return a tuple. In some cases, the C# compiler uses type inference to determine the types of tuple components.

You define a tuple by enclosing a comma-delimited list of its components in parentheses. The following example uses tuple with three components to pass a sequence of numbers to a lambda expression, which doubles each value and returns a tuple with three components that contains the result of the multiplications.

Func<(int, int, int), (int, int, int)> doubleThem = ns => (2 * ns.Item1, 2 * ns.Item2, 2 * ns.Item3);
var numbers = (2, 3, 4);
var doubledNumbers = doubleThem(numbers);
Console.WriteLine($"The set {numbers} doubled: {doubledNumbers}");
// Output:
// The set (2, 3, 4) doubled: (4, 6, 8)

Ordinarily, the fields of a tuple are named Item1Item2, etc. You can, however, define a tuple with named components, as the following example does.

Func<(int n1, int n2, int n3), (int, int, int)> doubleThem = ns => (2 * ns.n1, 2 * ns.n2, 2 * ns.n3);
var numbers = (2, 3, 4);
var doubledNumbers = doubleThem(numbers);
Console.WriteLine($"The set {numbers} doubled: {doubledNumbers}");

For more information about C# tuples, see C# tuple types.

Lambdas with the standard query operators

LINQ to Objects, among other implementations, have an input parameter whose type is one of the Func<TResult> family of generic delegates. These delegates use type parameters to define the number and type of input parameters, and the return type of the delegate. Func delegates are very useful for encapsulating user-defined expressions that are applied to each element in a set of source data. For example, consider the Func<T,TResult> delegate type:

public delegate TResult Func<in T, out TResult>(T arg)

The delegate can be instantiated as a Func<int, bool> instance where int is an input parameter and bool is the return value. The return value is always specified in the last type parameter. For example, Func<int, string, bool> defines a delegate with two input parameters, int and string, and a return type of bool. The following Func delegate, when it’s invoked, returns Boolean value that indicates whether the input parameter is equal to five:

Func<int, bool> equalsFive = x => x == 5;
bool result = equalsFive(4);
Console.WriteLine(result);   // False

You can also supply a lambda expression when the argument type is an Expression<TDelegate>, for example in the standard query operators that are defined in the Queryable type. When you specify an Expression<TDelegate> argument, the lambda is compiled to an expression tree.

The following example uses the Count standard query operator:

int[] numbers = { 5, 4, 1, 3, 9, 8, 6, 7, 2, 0 };
int oddNumbers = numbers.Count(n => n % 2 == 1);
Console.WriteLine($"There are {oddNumbers} odd numbers in {string.Join(" ", numbers)}");

The compiler can infer the type of the input parameter, or you can also specify it explicitly. This particular lambda expression counts those integers (n) which when divided by two have a remainder of 1.

The following example produces a sequence that contains all elements in the numbers array that precede the 9, because that’s the first number in the sequence that doesn’t meet the condition:

int[] numbers = { 5, 4, 1, 3, 9, 8, 6, 7, 2, 0 };
var firstNumbersLessThanSix = numbers.TakeWhile(n => n < 6);
Console.WriteLine(string.Join(" ", firstNumbersLessThanSix));
// Output:
// 5 4 1 3

The following example specifies multiple input parameters by enclosing them in parentheses. The method returns all the elements in the numbers array until it encounters a number whose value is less than its ordinal position in the array:

int[] numbers = { 5, 4, 1, 3, 9, 8, 6, 7, 2, 0 };
var firstSmallNumbers = numbers.TakeWhile((n, index) => n >= index);
Console.WriteLine(string.Join(" ", firstSmallNumbers));
// Output:
// 5 4

Type inference in lambda expressions

When writing lambdas, you often don’t have to specify a type for the input parameters because the compiler can infer the type based on the lambda body, the parameter types, and other factors as described in the C# language specification. For most of the standard query operators, the first input is the type of the elements in the source sequence. If you are querying an IEnumerable<Customer>, then the input variable is inferred to be a Customer object, which means you have access to its methods and properties:

customers.Where(c => c.City == "London");

The general rules for type inference for lambdas are as follows:

  • The lambda must contain the same number of parameters as the delegate type.
  • Each input parameter in the lambda must be implicitly convertible to its corresponding delegate parameter.
  • The return value of the lambda (if any) must be implicitly convertible to the delegate’s return type.

Note that lambda expressions in themselves don’t have a type because the common type system has no intrinsic concept of “lambda expression.” However, it’s sometimes convenient to speak informally of the “type” of a lambda expression. In these cases the type refers to the delegate type or Expression type to which the lambda expression is converted.

Variable scope in lambda expressions

Lambdas can refer to outer variables (see Anonymous methods) that are in scope in the method that defines the lambda expression, or in scope in the type that contains the lambda expression. Variables that are captured in this manner are stored for use in the lambda expression even if the variables would otherwise go out of scope and be garbage collected. An outer variable must be definitely assigned before it can be consumed in a lambda expression. The following example demonstrates these rules:

public static class VariableScopeWithLambdas
{
    public class VariableCaptureGame
    {
        internal Action<int> updateCapturedLocalVariable;
        internal Func<int, bool> isEqualToCapturedLocalVariable;

        public void Run(int input)
        {
            int j = 0;

            updateCapturedLocalVariable = x =>
            {
                j = x;
                bool result = j > input;
                Console.WriteLine($"{j} is greater than {input}: {result}");
            };

            isEqualToCapturedLocalVariable = x => x == j;

            Console.WriteLine($"Local variable before lambda invocation: {j}");
            updateCapturedLocalVariable(10);
            Console.WriteLine($"Local variable after lambda invocation: {j}");
        }
    }

    public static void Main()
    {  
        var game = new VariableCaptureGame();
        
        int gameInput = 5;
        game.Run(gameInput);

        int jTry = 10;
        bool result = game.isEqualToCapturedLocalVariable(jTry);
        Console.WriteLine($"Captured local variable is equal to {jTry}: {result}");

        int anotherJ = 3;
        game.updateCapturedLocalVariable(anotherJ);

        bool equalToAnother = game.isEqualToCapturedLocalVariable(anotherJ);
        Console.WriteLine($"Another lambda observes a new value of captured variable: {equalToAnother}");
    }
    // Output:
    // Local variable before lambda invocation: 0
    // 10 is greater than 5: True
    // Local variable after lambda invocation: 10
    // Captured local variable is equal to 10: True
    // 3 is greater than 5: False
    // Another lambda observes a new value of captured variable: True
}

The following rules apply to variable scope in lambda expressions:

  • A variable that is captured will not be garbage-collected until the delegate that references it becomes eligible for garbage collection.
  • Variables introduced within a lambda expression are not visible in the enclosing method.
  • A lambda expression cannot directly capture an inref, or out parameter from the enclosing method.
  • return statement in a lambda expression doesn’t cause the enclosing method to return.
  • A lambda expression cannot contain a gotobreak, or continue statement if the target of that jump statement is outside the lambda expression block. It’s also an error to have a jump statement outside the lambda expression block if the target is inside the block.

ASCII and Emoji Code Websites

I prefer the websites below for looking up ASCII code’s when I’m programming. Sometime I need to send a character code in a stream. Emoji’s are great for making your communications friendlier and to the point.

ASCII Codes: https://theasciicode.com.ar/
Emoji’s: https://emojipedia.org/

Bonus: Some websites I use for graphical icons when both fall short:

LINQ Tips and Tricks

Search Results: https://www.ecosia.org/search?q=linq+tricks&addon=opensearch

LINQ Tips and Tricks:https://markheath.net/post/linq-tips-and-tricks

Here is a quick overview:

  1. Use LINQPad
  2. Think in Patterns
  3. Use Pipelines
  4. Be Lazy
  5. Extend LINQ
  6. Optimize Performance
  7. Understand the Underlying SQL

Learn to Love LINQ:
https://gist.github.com/jehugaleahsa/9d92be18e642bc65d7a167c621d91e90

  • Query vs Method syntax
  • Use multiples wheres to break up long conditionals
  • Use where anywhere
  • Uniqueness on multiple keys
  • Using into and let
  • DefaultIfEmpty and left-joins
  • Joins and alternatives
  • Include always at the end
  • Avoiding null tests
  • Any() && !Skip(1).Any()
  • UniqueOrDefault
  • Enumerable.Repeat(x, 1) and Enumerable.Empty().DefaultIfEmpty(x)
  • Building up an IEnumerable

50 LINQ Examples, Tips and How To’s
https://www.dotnetcurry.com/linq/727/linq-examples-tips-tricks

  1. List.ConvertAll>>() with Lambda Expression
  2. Calculate the Size of a Folder/Directory using .NET 4.0
  3. LINQ: Generate Odd Numbers using Parallel Execution
  4. LINQ: Compare two Sequences
  5. LINQ: Calculate Average File Size in C#
  6. LINQ: Generate a Cartesian Product
  7. LINQ: List Classes implementing the IEnumerable Interface
  8. Find Uppercase words in a String using C#
  9. LINQ: Query Comma Separated Value (CSV) files
  10. Generate Odd Numbers within a Range using LINQ
  11. Generate Sequence of Float Numbers within a Range using LINQ
  12. Query a Sequence using LINQ
  13. Divide Sequence into Groups and Query using LINQ
  14. LINQ – Left Join Example in C#
  15. Inner Join Example in LINQ and C#
  16. Using from-let-where Clause in LINQ
  17. List all .NET Attributes in the Loaded Assemblies
  18. Rewrite Nested ForEach Loop in LINQ
  19. Count File Extensions and Group it using LINQ
  20. Swap Words inside a String using LINQ
  21. Filter a Type in .NET inside the For-Each Loop
  22. Split a String Collection into Groups using LINQ
  23. Highest Value in each Group using LINQ
  24. Loop through Master-Detail Records using LINQ
  25. Concatenate Unique Elements of two List<String> and Sort using LINQ
  26. List all Files in .NET 4.0 Based on the Creation Date
  27. Skip and Select Elements in a String Array using LINQ
  28. Enumerate Hidden Directories in .NET 4.0
  29. Ordering Elements of a List by Length and Content
  30. Distinct OrderBy in LINQ
  31. Using LINQ to select Only Strings from an ArrayList
  32. Combine Multiple Sequences in LINQ using the Zip Operator
  33. Sort a String Array containing Numbers using LINQ
  34. Join Two String Arrays with Distinct values using LINQ
  35. Using TrueForAll with Generic Lists
  36. Determine all Types that Implement an Interface
  37. Using LINQ to Find Top 5 Processes that are Consuming Memory
  38. Find Distinct Text Using LINQ
  39. Using LINQ to Search and Delete Old Files
  40. Using LINQ to Find the Sum of a MultiDimensional Array
  41. Get Unique Selected Items From Multiple ASP.NET ListBox and Merge them using LINQ
  42. How to Sort Data using LINQ
  43. Retrieve Selected Items of an ASP.NET ListBox using LINQ
  44. Replicating the ‘IN’ operator in LINQ
  45. Implementing Paging in a Generic List using LINQ
  46. LINQ to XML
  47. LINQ to XML Sorting
  48. Serialize XDocument in LINQ To XML
  49. Select Last N elements using LINQ to XML
  50. Create a XML Tree from a String