itblog.team-holm.net
Scripts


How to write C# code against UCS Central

How to write C# code against UCS Central

-3rd of June 2014

This blog attempt will in short terms to try to describe how to write C# code against UCS Central. Visual Studio 2012 was used to write the code displayed here. It is assumed that you know programming already, and that you are able to fit these pieces of information to your own solution. Not every class and bit of code is shown here, only what is esssential to get the concept across. When I first needed to use the UCS Centrals API for automating tasks against it, I had no access to any API documentation. UCS Central uses a web GUI to talk to its backend API through an HTTPS session. Using Fiddler to monitor what calls are being made to the API from the web page, you can record so that you can later tweak and recreate these calls from C# to do what you need to do even with missing documentation.

1. What is UCS Central and what are we trying to do?

The Cisco Unified Computing system is a system where profiles representining setups of server systems are deployed on to bare metal computer systems. All hardware specifics are fetched from pools or definitions in the UCS system and assigned to these profiles that in turn can be assigned and reassigned to different hardware servers/rack blades/bare metal servers. Take a look at the following video representation from Cisco for a good explanation and illustration of the concept.

The whole system is arranged into domains that are each administered by UCS Managers. These domains and theyr managers are then in turn administered by a central solution called the UCS Central. The documentation for the UCS Managers command line interface, API solutions and PowerShell support is at the time of writing very good. From what I can find at the time being, this is not the case for UCS Central, which leads us to this blog article.

We will be using Fiddler to get the information we need to talk to the UCS REST API, and then C# to code the talking. To have a real system to talk to and experiment with, I will be using Cisco UCS Platform Emulator and then Cisco UCS Central on top of that. These two are set up in a private VMWare lab for this emulation.

2. Using Fiddler

Fiddler can be fetched at http://www.telerik.com/download/fiddler and is our first step to get the information we need. Go ahead and download it, then take a look at this guide for more about Fiddler. In our setup we want to capture information going over HTTPS, so we will set the options for Fiddler to handle that traffic (figure 1 on the right). To exemplify the usage of Fiddler we log in to UCS Central and watch what calls are being made in Fiddler (figure 2 on the right). Here we can see the XML being sent to the API (the upper box), and the headers being used. We also see the reply we get (the lower box) with headers. We will be using both to make a login and check the reply. Here is what we are sending:

						

And we can see in the reply the outCookie that we will be using in further interaction with the UCS Central REST API to identify our session (figure 2 on the right).

3. Using the result from Fiddler

From experimenting with Fiddler we know we will be sending the following XML to the API as shown below. The first example does the login which in turn gives us the outCookie as an xml answer that we use in our next step. The other is for our next step to create a VLAN via UCS Central using the outCookie from step 1 to identify our authenticated session.

3.1 The login:

						

3.2 The creation of a VLAN named 999-XXX-TESTXX with VLAN ID 999:

						
						  
						    
						      
						    
						  
						


In our C# code, we first need a piece of code that will send the request to the API and then another piece of code that will interpret the reply. The SendWebRequest class will be doing the sending and the XmlReaderLocal class will do the reply. The XmlReaderLocal class has actually been made to read an xml file which is not what is returned to us from the UCS Central API. I have because of that created a temporary file creator where there is code to create a a temporary file where the XML reply is stored so that the XmlReaderLocal class can read it. The code for the temporary file creator is not included here, but is used in section four 'The half-ready code.' You will have to write your own temporary file creator the way you see fits.

3.3 The SendWebRequest class to send our request:

					  class SendWebRequest {
					      private string __apiUrl;
					      private string __stringData;

					      public SendWebRequest(string apiUrl, string stringData) {
					         __apiUrl = apiUrl;
					         __stringData = stringData;
					      }
					      public HttpWebResponse execute() {
					         CookieContainer cookieJar = new CookieContainer();
					         HttpWebRequest firstRequest = (HttpWebRequest)WebRequest.Create(__apiUrl);

					         ASCIIEncoding encoding = new ASCIIEncoding();
					         byte[] data = encoding.GetBytes(__stringData);

					         firstRequest.CookieContainer = cookieJar;
					         firstRequest.KeepAlive = true;
					         firstRequest.Method = "POST";
					         firstRequest.ContentType = "application/xml";
					         firstRequest.ContentLength = data.Length;
					         firstRequest.Accept = "*/*";
					         firstRequest.Referer = "https://" + __apiUrl.Split('/')[2] + "/apps/Multi_UCS.swf/[[DYNAMIC]]/4";
					         firstRequest.UserAgent = "Mozilla/5.0 (Windows NT 6.1; WOW64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/35.0.1916.114 Safari/537.36";
					         firstRequest.Host = __apiUrl.Split('/')[2];

					         System.Net.ServicePointManager.ServerCertificateValidationCallback +=
					            delegate(object sender, System.Security.Cryptography.X509Certificates.X509Certificate certificate,
					                                    System.Security.Cryptography.X509Certificates.X509Chain chain,
					                                    System.Net.Security.SslPolicyErrors sslPolicyErrors) {
					                                       return true; // **** Always accept SSL Certificate
					                                    };

					         Stream newStream = firstRequest.GetRequestStream();
					         newStream.Write(data, 0, data.Length);
					         newStream.Close();
					         return (HttpWebResponse)firstRequest.GetResponse();
					      }
					   }

3.4 The XmlReaderLocal class to read the reply from UCS central and the AttributeCollection class to reply our answer:
Note: I see there are some syntax errors presented in the code by the syntax highlighter. Be sure to check this when you use the code.

public class AttributeCollection {
  public string NodeName { get; set; }
  public Dictionary Attributes { get; set;}

  public AttributeCollection(string nodeName) {
     this.NodeName = nodeName;
     Attributes = new Dictionary();
  }
}
public class XmlReaderLocal {
  XmlTextReader reader;
  String xmlFil;
  public XmlReaderLocal(String xmlFil) {
     this.xmlFil = xmlFil;
  }
  /// 
  ///  Read all attributes occurrences of one named node taken as input
  /// 
  public AttributeCollection ReadFirstNodeAttributes(String nodename) {
     reader = new XmlTextReader(xmlFil);
     AttributeCollection attrCollection = new AttributeCollection(reader.Name);
     while (reader.Read()) {            
        switch (reader.NodeType) {
           case XmlNodeType.Element:
              if (reader.Name.ToString() == nodename) {
                 if (reader.HasAttributes) {
                    //Console.WriteLine("Attributes of <" + reader.Name + ">");                                                
                    while (reader.MoveToNextAttribute()) {
                       attrCollection.Attributes.Add(reader.Name, reader.Value);
                    }
                    reader.MoveToElement();                        
                    reader.Close();                        
                 }                     
              }
              break;
           case XmlNodeType.Text:
              break;
           case XmlNodeType.EndElement:
              break;
        }
     }
     reader.Dispose();
     return attrCollection.Attributes.Count > 0 ? attrCollection : null;
  }
}

4. The half-ready code

Note: I see there are some syntax errors presented in the code by the syntax highlighter. Be sure to check this when you use the code. This seems to be some sort of bug in the highlighter.

Here is where we will use our code and knowledge that we have this far. First we demonstrate a login procedure, and then we demonstrate the creation of the VLAN we want to create. Have in mind that this code is not complete and is only intended to show a strain of thought. Simplified versions of the functions GetCookieString and SetUCSVLAN are displayed below, but the whole class is omitted and some depending classes are omitted. You will have to interpret your way through this code and the comments, then implement your own solution for these missing pieces. The strain of thought is still thought to be displayed for tutorial purposes in this article, and thus giving the value intended.

4.1 Authenticate with UCS Central:

						String errorStatus = "None";
						string outCookie = "";

						//This class holds the GetCookieString method that authenticates with UCS Central and gets the Cookie String we need for our next step:
						AuthenticateWithUCS awucs = new AuthenticateWithUCS(); 
						Dictionary returnAttributes = awucs.GetCookieString(uCSServer, username, password);
						status = returnAttributes.ContainsKey("status") ? returnAttributes["status"] : "None";
						errorStatus = returnAttributes.ContainsKey("errorStatus") ? returnAttributes["errorStatus"] : "None";
						outCookie = returnAttributes.ContainsKey("outCookie") ? returnAttributes["outCookie"] : "None";

						Console.WriteLine(Environment.NewLine + "**After authentication: ");
						Console.WriteLine("JobStatus: " + status);
						Console.WriteLine("ErrorStatus: " + errorStatus);
						Console.WriteLine("outCookie: " + outCookie);

4.2 Create VLAN in UCS Central:

						//This class holds the SetUCSVLAN method that creates a VLAN in UCS Central and reads the reply:
						CreateUCSVLAN cucsvlan = new CreateUCSVLAN();
			            returnAttributes = cucsvlan.SetUCSVLAN(uCSServer, vlanID, vlanName, outCookie);
			            status = returnAttributes.ContainsKey("status") ? returnAttributes["status"] : "None";
			            errorStatus = returnAttributes.ContainsKey("errorStatus") ? returnAttributes["errorStatus"] : "None";
			            var ucsStatus = returnAttributes.ContainsKey("ucsStatus") ? returnAttributes["ucsStatus"] : "None";

			            Console.WriteLine(Environment.NewLine + "**After VLAN Creation: ");
			            Console.WriteLine("JobStatus: " + status);
			            Console.WriteLine("ErrorStatus: " + errorStatus);
			            Console.WriteLine("ucsStatus: " + ucsStatus);

4.3 GetCookieString example code:

				public Dictionary GetCookieString(string uCSServer, string username, string password) {
					Dictionary returnAttributes = new Dictionary();
					var statusArray = Enum.GetValues(typeof(Status));
					Status status = (Status)statusArray.GetValue(2);
					string errorStatus = "None";
					string outCookie = "";
					string ucsStatus = "None";

					string loginUrl = "https://" + uCSServer + "/xmlIM/mgmt-controller";
					string stringData = "";

					try {
						//This is the SendWebRequest class in section 3.3 above:
						SendWebRequest swr = new SendWebRequest(loginUrl, stringData); 
						//The ReadUCSWebResponse class is not shown in this article, but its usage is obvious:
						ReadUCSWebResponse rUcsWebRes = new ReadUCSWebResponse(swr, status, errorStatus, ucsStatus, "aaaLogin", "aaaLogin", "GetCookieString", "outCookie"); 
						Dictionary parseAttributes = rUcsWebRes.execute();
						status = parseAttributes.ContainsKey("status") ? (Status)Enum.Parse(typeof(Status), parseAttributes["status"]) : Status.error;
						errorStatus = parseAttributes.ContainsKey("errorStatus") ? parseAttributes["errorStatus"] : "None";
						outCookie = parseAttributes.ContainsKey("ucsStatus") ? parseAttributes["ucsStatus"] : "None";
					} catch (Exception ex) {
						status = (Status)statusArray.GetValue(6);
						errorStatus = "103 in GetCookieString(): " + ex.Message;
					}

					returnAttributes.Add("errorStatus", errorStatus);
					returnAttributes.Add("status", status.ToString());
					returnAttributes.Add("outCookie", outCookie);
					return returnAttributes;
			    }

4.4 SetUCSVLAN example code:

					public Dictionary SetUCSVLAN(string uCSServer, string vlanid, string vlanName, string cookieString) {
				         Dictionary returnAttributes = new Dictionary();
				         var statusArray = Enum.GetValues(typeof(Status));
				         Status status = (Status)statusArray.GetValue(2);
				         string errorStatus = "None";
				         string ucsStatus = "None";

				         string accessUrl = "https://" + uCSServer + "/xmlIM/resource-mgr";
				         string stringData = 
				            "" +
				               "" +
				                  "" +
				                     "" +
				                  "" +
				               "" +
				            "";
				         try {
				         	//This is the SendWebRequest class in section 3.3 above:
				            SendWebRequest swr = new SendWebRequest(accessUrl, stringData); 
				            //The ReadUCSWebResponse class is not shown in this article, but its usage is obvious:
				            ReadUCSWebResponse rUcsWebRes = new ReadUCSWebResponse(swr, status, errorStatus, ucsStatus, "fabricVlan", "configConfMos", "SetUCSVLAN", "status"); 
				            Dictionary parseAttributes = rUcsWebRes.execute();
				            status = parseAttributes.ContainsKey("status") ? (Status)Enum.Parse(typeof(Status), parseAttributes["status"]) : Status.error;
				            errorStatus = parseAttributes.ContainsKey("errorStatus") ? parseAttributes["errorStatus"] : "None";
				            ucsStatus = parseAttributes.ContainsKey("ucsStatus") ? parseAttributes["ucsStatus"] : "None";
				         } catch (Exception ex) {
				            status = (Status)statusArray.GetValue(6);
				            errorStatus = "103 in SetUCSVLAN(): " + ex.Message;
				         }
				         returnAttributes.Add("errorStatus", errorStatus);
				         returnAttributes.Add("status", status.ToString());
				         returnAttributes.Add("ucsStatus", ucsStatus);
				         return returnAttributes;
				    }


As mentioned earlier, not every bit of code is included here, but this should get you started. Note that some of the C# code does get displayed with syntax errors that are probably due to bugs in the syntax highlighter that is used on this web page.

Source: Fiddler and the imagination.

Tagged as: ScriptsToolsHowTo

JavaScript Essentials

JavaScript Essentials

-24th July 2014

Here follows some simple fiddles on jsfiddle.net that display some basics in JavaScript. On the first row, first cell, we see an example of an custom error handler and examples on how numbers are treated in comparison to numbers as text. Read the comments for more infomation. Forward to the the second cell on the right, first row, shows how to create custom exceptions, use them to validate input and throw them as a response to invalid values. On the second row, first cell to the left, we see object oriented JavaScript being examplified. To the right on the second row, we see object oriented JavaScript again, this time testing the scope and availability of variables.


You can edit these fiddles in JsFiddle if you want to test and play around with them.
                                                                                                                                                                                                                                                                                                                           
Source: Collection over time

Tagged as: Scripts

SQL 2008 How to trunc a log file

SQL 2008 How to trunc/shrink a log file

-23rd July 2013

How to truncate a log file with TSQL:
Every once in a while log files can grow too large and need to be truncated and/or shrinked in order to save valuable disk space. The steps below will guide you, given that you know the database name and log name. These can be found via the database properties and then selecting "Files."

Normally, taking transaction log backups will truncate the transaction log. The space used on the disk will not shrink, but more space inside the log file is freed to be available for future use. Transaction logs should be handled as a part of the backup routine. To actually shrink the log file, the steps below will make that happen.

1. The following code will set TestDb to simple recovery mode, then shrink it, and finally set it back to full recovery mode:
USE [TestDb]
GO
ALTER DATABASE [TestDb] SET RECOVERY SIMPLE WITH NO_WAIT
DBCC SHRINKFILE(TestDbLog, 1)
ALTER DATABASE [TestDb] SET RECOVERY FULL WITH NO_WAIT
GO

2a. To list the placement of all instance databases for viewing, then the following SQL query might be of use:
SELECT DB_NAME(dbid),* FROM sys.sysaltfiles;

2b. Alternatively, this query wich also lists all the instance log file locations might be of use:
SELECT name, physical_name AS current_file_location FROM sys.master_files;


Source: N/A

Tagged as: SQL 2008ScriptsHowTo

SQL Joins

SQL Joins

-4th August 2012

This blog attempt will in short terms try to describe various joins available in Oracle.

Natural Join

			# Example:
			1. SELECT REGION_NAME FROM REGIONS NATURAL JOIN COUNTRIES WHERE COUNTRY_NAME='Canada';
			2. SELECT REGION_NAME FROM REGIONS JOIN COUNTRIES USING (REGION_ID) WHERE COUNTRY_NAME='Canada';
			3. SELECT COUNTRY_NAME FROM COUNTRIES JOIN REGIONS ON (COUNTRIES.REGION_ID=REGIONS.REGION_ID) WHERE REGION_NAME='Americas';
		

Query 1 joins the table REGION with the table COUNTRIES assuming a common relationship between the two(primary key->foreign key relationship).
Query 2 specifies specificly what common column those two tables should join on.
Query 3 specifies two columns, one in each table, that should be used in the join. Those need not have the same name.

Outer Joins

The examples below are for displaying syntax only. The data that is selected from is not checked to see if it is usable for this example.

			# Example:
			1. SELECT LAST_NAME, DEPARTMENT_NAME FROM EMPLOYEES, DEPARTMENTS WHERE EMPLOYEES.DEPARTMENT_ID (+) = DEPARTMENTS.DEPARTMENT_ID;
			2. SELECT LAST_NAME, DEPARTMENT_NAME FROM EMPLOYEES RIGHT OUTER JOIN DEPARTMENTS ON(EMPLOYEES.DEPARTMENT_ID = DEPARTMENTS.DEPARTMENT_ID);
			3. SELECT LAST_NAME, DEPARTMENT_NAME FROM EMPLOYEES, DEPARTMENTS WHERE EMPLOYEES.DEPARTMENT_ID = DEPARTMENTS.DEPARTMENT_ID (+);
			4. SELECT LAST_NAME, DEPARTMENT_NAME FROM EMPLOYEES LEFT OUTER JOIN DEPARTMENTS ON(EMPLOYEES.DEPARTMENT_ID = DEPARTMENTS.DEPARTMENT_ID);
			5. SELECT LAST_NAME, DEPARTMENT_NAME FROM EMPLOYEES FULL OUTER JOIN DEPARTMENTS ON(EMPLOYEES.DEPARTMENT_ID = DEPARTMENTS.DEPARTMENT_ID);
		

Query 1 retrieves all rows from the employees and departments tables with matching department_id's, and also includes those rows that do not match in the employees table.
Query 2 is the same as query 1. Retrieves all rows from the employees and departments tables with matching department_id's, and also includes those rows that do not match in the employees table
Query 3 retrieves all rows from the employees and departments tables with matching department_id's, and also includes those rows that do not match in the departments table.
Query 4 is the same as query 3. Retrieves all rows from the employees and departments tables with matching department_id's, and also includes those rows that do not match in the departments table
Query 5 retrieves all rows from the employees and departments tables with matching department_id's, and also includes those rows that do not match in the employees table and those who do not match in the departments table.

Cartesian Joins / Cross Joins

			# Example:
			1. SELECT * FROM REGIONS, COUNTRIES;
		

The above query will select all rows from the regions table and match them with all rows in the countries table without considering any match condition. If there are 10 rows in each table, the result will be 100 rows after the join. This is done to simply perform a join, or to simulate a reasonable amount of data.

Self join

			# Example:
			1. SELECT f1.name Dad, f2.name child FROM family f1 JOIN family f2 ON (f1.id=f2.father_id)
		

The above query will join a table to itself, using the imaginary table family as en axample. The relationship between a father and a child can be displayed this way.

Semijoins

			# Example:
			SELECT * FROM departments
				WHERE EXISTS
				(SELECT * FROM employees
					WHERE departments.department_id = employees.department_id
					AND employees.salary > 2500)
			ORDER BY department_name;
		

Semijoins are joins that use EXISTS / IN in join as shown above.

Antijoins

		# Example:
		SELECT * FROM employees
WHERE department_id NOT IN
(SELECT department_id FROM departments
WHERE location_id = 1700)
ORDER BY last_name;

Antijoins are joins that use NOT IN / NOT EXISTS in join as shown above.

Source: OCA Oracle Database 11g SQL Fundamentals I Exam Guide. ISBN: 9780071597869

Tagged as: Scripts

SQL Select Limiting

SQL Select Limiting

-14th September 2013

The following article is a collection of example queries and theyr explanations regarding select limiting. It is assumed that the reader has at least basic knowledge of SQL queries and how to use them before attempting to make use of this short form article.

Single row select limiting

		# Example:
		1. SELECT * FROM TABLE WHERE COLUMN1 = COLUMN2;
		2. SELECT * FROM TABLE WHERE COLUMN1 < COLUMN2;
		3. SELECT * FROM TABLE WHERE COLUMN1 BETWEEN 3000 AND 4000;
		4. SELECT * FROM TABLE WHERE COLUMN1 IN (1000, 2000, 3000);
		5. SELECT * FROM TABLE WHERE COLUMN1 = COLUMN2 AND COLUMN1 != 4000;
		6a. SELECT * FROM TABLE WHERE COLUMN1 = COLUMN2 AND COLUMN1 != 4000 OR COLUMN3 < 2000;
		6b.	Interpreted as: SELECT * FROM TABLE WHERE (COLUMN1 = COLUMN2 AND COLUMN1 != 4000) OR (COLUMN3 < 2000);
		7. SELECT * FROM TABLE WHERE COLUMN1 LIKE '%STRING%';
		8. SELECT * FROM TABLE WHERE COLUMN1 LIKE '%STRING_REST'; # character _ is a wildcard for one sign.
		9. SELECT * FROM TABLE WHERE COLUMN1 LIKE '%STRING\_REST' ESCAPE '\'; # character _ is now not a wild card.
		10. SELECT * FROM TABLE WHERE COLUMN1 IS NULL;
		11. SELECT * FROM TABLE WHERE COLUMN1 LIKE '%STRING%' OR NOT COLUMN2 < 0,35;
		12. SELECT * FROM TABLE WHERE COLUMN1 ORDER BY COLUMN2;
13. SELECT * FROM TABLE WHERE COLUMN1 ORDER BY COLUMN2, 3 DESC, 4 NULLS FIRST | LAST; ASC ER DEFAULT 14. SELECT * FROM TABLE WHERE &VARIABLE ORDER BY COLUMN2;

To handle variables:
		# Example: 
		DEFINE VARIABLE='TEST';
		UNDEFINE VARIABLE;
	
Disallow defining: SET DEFINE OFF;
Allow defining: SE DEFINE ON;
Show input to variables on screen? SET VERIFY ON | OFF;

Query 1 selects everything from TABLE where COLUMN1 is the same as COLUMN2.
Query 2 selects everything from TABLE where COLUMN1 is less then COLUMN2.
Query 3 selects everything from TABLE where COLUMN1 is between the values 3000 and 4000.
Query 4 selects everything from TABLE where COLUMN1 is either 1000, 2000 or 3000.
Query 5 selects everything from TABLE where COLUMN1 is the same as COLUMN2, and COLUMN2 is not 4000.
Query 6 selects everything from TABLE where COLUMN1 is the same as COLUMN2 and COLUMN1 is not 4000. If that condition is no true, rows from the table will be selected if COLUMN3 is less then 3000.
Query 7 selects everything from TABLE where COLUMN1 has the word STRING in it.
Query 8 selects everything from TABLE where COLUMN1 has the word STRING in it, followed by an arbitrary character, followed by the word REST.
Query 9 selects everything from TABLE where COLUMN1 has the word STRING in it, followed by an underscore character, followed by the word REST.
Query 10 selects everything from TABLE where COLUMN1 is NULL.
Query 11 selects everything from TABLE where COLUMN1 contains the word STRING or where COLUMN2 is less then 0,35.
Query 12 selects everything from TABLE where COLUMN1 and orders the result by COLUMN2.
Query 13 selects everything from TABLE where COLUMN1 and orders the result by COLUMN2, sets third column in descending order and puts nulls first in fourth column.
Query 14 selects everything from TABLE where where predefined variable contains the cinditions and orders by COLUMN2.

Multiple row select limiting

1. HAVING and GROUP BY:

	# Examples:
	SELECT COUNT(*), SUM(LIST_PRICE), product_status FROM PRODUCT_INFORMATION 
		WHERE LOWER(PRODUCT_STATUS) <> 'orderable' 
		GROUP BY PRODUCT_STATUS HAVING SUM(LIST_PRICE) > 4000;
	
2. IN, NOT IN:
	# Examples:
	SELECT LAST_NAME FROM EMPLOYEES WHERE DEPARTMENT_ID IN
		(SELECT DEPARTMENT_ID FROM DEPARTMENTS WHERE DEPARTMENT_NAME LIKE 'IT%') AND 
		SALARY > (SELECT AVG(SALARY) FROM EMPLOYEES);
	
3. ANY, ALL:
		# Example:
		SELECT LAST_NAME FROM EMPLOYEES WHERE SALARY > ALL
			(SELECT SALARY FROM EMPLOYEE WHERE LAST_NAME = 'Taylor') ORDER BY LAST_NAME;
	

Query 1: The functions COUNT() and SUM() are multiple row functions. GROUP BY PRODUCT_STATUS states that the multiples of rows fed to these functions are in groups of the column PRODUCT_STATUS. The HAVING clause limits these groups to only include rows where the SUM of LIST_PRICE is larger then 4000. Thus GROUP BY and HAVING are both used in multiple row limiting.
Query 2: This example shows a query with a subquery where the result is limited to contain only rows where DEPARTMENT_ID is IN the result of the subquery that follows the IN clause. The IN clause is used here because the subquery returns multiple rows.
Query 3: Here the result is limited by the clause '> ALL' followed by a subquery that returns multiple rows. This would select LAST_NAME from the EMPLOYEES table where the SALARY is more then the highest SALARY returned by the subquery.

Below is an explanation to the various forms of these clauses:

< ANYLess then the largest
> ANYMore then the smallest
= ANYIN
< ALLLess then the smalles - the opposite of "more then the smallest(> ANY)"
> ALLMore then the largest - the opposite of "less then the biggest(< ANY)"


Source: OCA Oracle Database 11g SQL Fundamentals I Exam Guide. ISBN: 9780071597869

Tagged as: Scripts

Syntax Comparisons

Syntax Comparisons

-3rd August 2012

This short description displays a comparison of syntax between Bash, Perl and Powershell. Nice to have when scripting in multiple languages.

BASHPERLPOWERSHELL
if [ ! $A ]; then
    echo "No A."
elif [ $A ]; then
   echo "Theres an A."
else
   echo "Dunno."
fi
if (not exists $var) {
   [...]
} elsif ($var){
   [...]
} else {
   [...]
}
If (!$var) {
   [...]
} elseif ($var){
   [...]
} else {
   [...]
}
for item in variabel
do
   [...]
done
foreach $i (@array) {
   [...]
}
Foreach ($i in $array) {
   [...]
}
While [ $var –lt 4 ]
Do
   [...]
done
Do {
   [...]
} While ($var);
Do
{
   [...]
} While ($var)
While [ ..betingelser.. ]
do
   [...]
done
While ($var ne $string) {
   [...]
}
While ($var –ne $string) {
   [...]
}
for (( i = 1 ; i < 6 ; i++ ))
do
   [...]
done
For ($x =1; $<10; $x++) {   [...]
}
for ( $x=0; $x -ne 10; $x++) {
   [...]
}
String comparisons:
Binary operator are used; > and > and <= etc, =
Binary comparisons:
Word operator are used; -lt and -gt and -eq etc.
String comparisons:
Word operatosr are used; lt and gt and eq etc.
Binary comparisons:
Binary operators are used.
String comparisons:
Word operator are used; -lt og -gt og -eq osv.
Binary comparisons:
Word operator are used; -lt og -gt og -eq osv.
Declaration:
Function function_name() {}
Usage:
Function_name $var
Declaration:
Sub function_name {}
Usage:
&function_name($var);
Declaration:
Function function_name() {}
Usage:
Function_name $varName value
Case “$var” in
Case_1)
;;&
Case_2)
;;
Case_3)
;;
*)
;;
esac
SWITCH: for ($choice) {
   $_ == 1 && die "You chose number one\n";
   $_ == 2 && print "You chose number two\n";
   $_ == 3 && do { "You chose number three\n";
   Last SWITCH;
};
#Default
   Warn ”message”;
}
Switch ($var) {
   Case_1 {   [...]   }
   Case_2 {   [...]   }
   Case_3 {   [...]   }
   Default {   [...]   }
}
Array = (one two three)
%hash = (navn => "Atle Holm",adresse => "Skogveien 14");
@array = ("Atle Hom", "Skogvegen 14");
@array = qw(one two three);
$hash = @{"navn" = "Atle Holm"; "adresse" = "Skogveien 14"};
$array = @("Atle Holm", "Skogvegen 14");


Various relevant informational links:

                                                                                                                                                                                                                                                                                                                           
Source: Collection over time

Tagged as: Scripts

Upsert against MongoDB with PowerShell

MongoDB Upsert with PowerShell

-18th January 2014

The Story
It turned out that I needed to upsert to a MongoDB database. After a quick Google search I found Kristof Kowalski's blog article, that helped me out alot. However, upserting was not explained. How to upsert is no big-brainer, if you just know how to. And when you don't know how, you spend time searching for the solution. Here it is explained without further time needed to be spent.

Upserting is done by adding the enum type MongoDB.Driver.UpdateFlags.Upsert when calling the update method as the third argument of the collection you have chosen. However, in PowerShell that would not work the way I tried to do it. To remedy that, I would need the values for the corresponding class. By using Visual Studio and creating a new solution, then adding references to the required DLLs I could browse through to the definitions (see picture on the right). As you can see, upsert is the equivalent to the value 1, so the value 1 is what I want to pass as the third optional argument. See the example code below for a demonstration.
		Add-Type -Path "C:\bin\MongoDB_Drivers\MongoDB.Bson.dll"
		Add-Type -Path "C:\bin\MongoDB_Drivers\MongoDB.Driver.dll"

		$databaseName = "database";
		$MONGO_URL = "mongodb://serverIPorDNS/$databaseName?safe=true;slaveok=true";
		$vmname="virtualServer"

		$db = [MongoDB.Driver.MongoDatabase]::Create($MONGO_URL)
		$collection = $db['ourCollection']

		[MongoDB.Bson.BsonDocument] $doc = @{   
			"_id"= [MongoDB.Bson.ObjectId]::GenerateNewId();
		    "ServerName"= "$vmname";
		};

		#We are not doing an insert, we are doing an upsert - the following would be an insert:
		#$collection.Insert($doc);

		#Here we get ready for the upsert:
		$updates = @{'$set' = @{'ServerName'= "$vmname"}};
		$query = @{"ServerName"= $doc['ServerName']}
		try {
			#This is where we do the upsert, notice the value 1 at the end of the method call:
			$collection.Update([MongoDB.Driver.QueryDocument]$query, [MongoDb.Driver.UpdateDocument]$updates, 1);
		} catch {
			#Some error handling here
		}
		#Example - To remove the vm with that name:
		#$collection.Remove([MongoDB.Driver.QueryDocument]@{"ServerName"= "$vmname"});

		#Example - o Test the values
		#$collection.FindAll();
	

There is also a PowerShell provider for MongoDB.

Source: Kristof Kowalski, MongoDB C# Documentation.

Tagged as: ScriptsHowTo

Main page
Security
Crypto
Microsoft
Linux
Scripts
VMWare Hyper-V General
Taglist
Active Directory
Citrix
Commands
Cryptography
General
Hyper-V
Linux
Network
Scripts
SQL 2008
SQL 2016
Tools
VMWare
Games
HowTo
Routers