RESTful Web APIS, by Leonard Richardson & Mike Amundsen has an example in chapter 2 (A Simple API) on calling a HTTP Get using wget, but does not show how to call the Post using the same tool.

From the book:

$ wget -S -O - http://www.youtypeitwepostit.com/api/

calls the API with a Get and displays the output to the console (I'm using Termux in Android)

The response includes a template to be used when calling the Post method, so to save typing I called the Get again but saved the response to a file (msg)

$ wget -S -O msg http://www.youtypeitwepostit.com/api/

Then edited the response file in nano - still in Termux to just contain the body of my Post, and saved as msg1

{
    "template" : {
        "data" : [
            {"prompt" : "Text of message", "name" : "text", "value" : "wibble"}
         ]
    }
}



The wget command for the Post is:

$ wget --post-file=msg1 -S -O - --header="Content-Type: application/vnd.collection+json" http://www.youtypeitwepostit.com/api/

Which results in a 201 response and the URL to the newly created message on the server:

  HTTP/1.1 201 Created
  Server: Cowboy
  Connection: keep-alive
  Location: http://www.youtypeitwepostit.com/api/5486216361168772
  Date: Thu, 12 Sep 2019 04:06:23 GMT
  Transfer-Encoding: chunked
  Via: 1.1 vegur


Additional:
wget installed in Termux by "pkg install wget"
nano installed in Termux by "pkg install nano"

Putty to Pi, via a Jump Server, With X11, Tunnel-tastic

Senario: I have a Rasberry Pi I want to remote(SSH) to and run python GUI's from. The only public route to my Pi's is via the one public Pi that acts as a jump server.

Things I've already done:

1. SSH with X11 activated on the Jump Server - (JS)
2. A port forward from my home router to the SSH port on the Jump Server (JS)
3. SSH with X11 activated on the Target Pi (tPi)
4. A windows machine with Putty and XMing installed (Lappy)

First create a Putty session on Lappy to JS.

The IP address 66.66.66.66 is the IP (altered to protect the innocent) of the home router, Port 66 is the port on the router that has been forwarded to the SSH port on JS (this is all done in the config of the router)

 In Putty, expand the SSH section and select Tunnel,

The Source port (2222) is a the start of the tunnel and will be created on Lappy by Putty when this session is opened, the destination is the ip address of tPi inside my home network, and the port tPi uses for SSH.

What does this do? when the Putty session (I saved the details as gPiTunnel) is started Putty on Lappy uses SSH to connect to JS, it then creates a tunnel in the SSH session that starts at port 2222 on Lappy and ends at the SSH port of tPi.

Now create a Putty session to port 2222 on Lappy:

This seems odd at first, but in the previous step a tunnel was opened from 2222 through the interwebs and ending at tPi, this session by connecting to 2222, jumps through the tunnel and pops out at the SSH port on tPi, letting you log on to the Pi there.

Expand the SSH section of this second session, select X11 and enter the details below:

 This allows tPi to send X11 programs to the Xming service running on Lappy, port 0 is the display port. save this session as TunnelDoor.


To use it:
On Lappy run Xming, run Putty, load session gPiTunnel, log in. (user and password for JS)
Still on Lappy run Putty, load session TunnelDoor, log in. (user and password for tPi)

Now any X11 apps started on tPi in the Putty session will open and display on Lappy, to test it run xclock on tPi

And done, now I can cruft up my Python GUI in the tPi session, run it and the UI opens on Lappy

SQL Sever Database Rename

Hello blog, long time no post :-(

Rename a database in SQL Server:

USE master
GO
ALTER DATABASE dbToChange
SET AUTO_UPDATE_STATISTICS_ASYNC OFF
GO
ALTER DATABASE dbToChange
SET SINGLE_USER
WITH ROLLBACK IMMEDIATE;
GO
ALTER DATABASE dbToChange
Modify Name = newDBName;
GO
ALTER DATABASE newDBName
SET MULTI_USER;
GO

Simple App Config

There is a load of great config stuff out on the net, for a full explaination of whats going on and how to use it try Jon Rista's superb set of articles on CodeProject.

I find the simplest way of doing basic config is to use AppSettingReader along with an App.Config file.

Add a new item to the project, select Application Configuration and an App.Config file will be added to the solution with very little in it:

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
</configuration>
For this example, I wanted to store a set of numbers that would be used to determine how many samples to take from a set of data. So a comma seperated string will do the trick. "10,5,3"

The AppSettingReader will look for an AppSettings node in the config file, so:


<?xml version="1.0" encoding="utf-8" ?>
<configuration>
    <appSettings>
    </appSettings>
</configuration>

And a value is stored in  KeyValue pair node:


<?xml version="1.0" encoding="utf-8" ?>
<configuration>
    <appSettings>
        <add key="SampleCount" value="10,5,3" />
    </appSettings>
</configuration>

My setting item is called "SampleCount" and the value is........ well, there to read :-)

To read the value, create an instance of AppSettingsReader and use the GetValue method to look for the setting by Key:

//read number of samples to take from each file
AppSettingsReader asr = new AppSettingsReader();
String[] sampleCounts = (asr.GetValue("SampleCount", typeof(string)).ToString()).Split(new Char[]{ ',' });

The config file is available for anyone to edit so its best to be a bit defensive when using the values

Int32 sampleCount = 0;
Int32.TryParse(sampleCounts[i], out sampleCount);

Compiling a Module from the Command Console

Using Command line syntax to compile a type into a module

>csc.exe /out:Program.exe /target:exe Program.cs

which breaks down to:
csc.exe - C Sharp Compiler
/out:Program.exe - the output is an executable file called Program.exe
/target:exe - the target file type will be a Win32 console application
Program.cs - source code file  name

In this example the out and target switches match the defaults, so I could use
>csc.exe Program.cs


And this is a simple console app source code example

public sealed class Program {
  public static void Main() {
    System.Console.WriteLine("Hello World");
  }
}

And if you want to add a reference to a couple of dll's
/lib:"C:\Program Files\Reference Assemblies\Microsoft\Framework\v3.0" /r:UIAutomationClient.dll /r:UIAutomationTypes.dll

AutoDeletion and Sorting of Outlook Sent Items

Option Explicit
Public WithEvents SentItemsAdd As Outlook.Items
'Code to be applied once logon is complete
Private Sub Application_MAPILogonComplete()
    'create the event handler on items being added to the Sent Items folder
    Set SentItemsAdd = Application.GetNamespace("MAPI").GetDefaultFolder(olFolderSentMail).Items
   
    'Daily Maintenence on the Sent Items folder, mails will be moved to a subfolder by To name
    'Any items whose auto delete period had been reached will be squished
    scheduleSentProcesing
End Sub
Private Sub Application_Quit()
    ProcessInbox
End Sub
'Run the Sent Items Processing code if this is the first time this Method is called today
Private Sub scheduleSentProcesing()
    Dim sLastRun As String
    sLastRun = Format(Now, "ddddd")
   
    'Retrieve the date of the last run from the registry
    If GetSetting("ProcessSentItems", "Settings", "LastRun", "") = sLastRun Then
        Exit Sub
    End If
    ProcessSentItems
    'store the last run date as today
    SaveSetting "ProcessSentItems", "Settings", "LastRun", sLastRun
End Sub
'Runs every time an item is added to the Sent Items folder
Private Sub SentItemsAdd_ItemAdd(ByVal Item As Object)
   
    'Only items marked for immediate deletion will be effected here
    Call deleteItem(Item, True)
End Sub
'Delete this item if appropriate
Private Function deleteItem(Item As Object, autoDefault As Boolean) As Boolean
    Dim strMonthsToKeep As String
    Dim strSubject  As String
    Dim iSubjectLength As Integer
    Dim iDeleteStartPosistion As Integer
    Dim iMonthsToKeep As Integer
    Dim dteSentOn As Date
    Dim dteDeleteDate As Date
    Dim iDaysDifferent As Integer
   
    'The deletion indicator is at the end of the subject and will be:
    '   :.  =   Delete now
    '   :dn =   Delete n months after sending
    If Item.Subject Like "*:." Then
        'Delete Now
        Item.Delete
        deleteItem = True
       
    ElseIf Item.Subject Like "*:d*" Then
        'get the number element from the subject, when I've added it it will be at the end and a number, anything else
        'is a false find
        strSubject = Item.Subject
        iSubjectLength = Len(strSubject)
        iDeleteStartPosistion = InStr(strSubject, ":d") + 1
        strMonthsToKeep = Right(strSubject, (iSubjectLength - iDeleteStartPosistion))
       
        On Error Resume Next
            iMonthsToKeep = CInt(strMonthsToKeep)
        On Error GoTo 0
       
        If iMonthsToKeep > 0 Then
            dteSentOn = Item.SentOn
            dteDeleteDate = DateAdd("M", -iMonthsToKeep, Date)
            iDaysDifferent = DateDiff("d", dteSentOn, dteDeleteDate)
           
            If iDaysDifferent >= 0 Then
                Item.Delete
                deleteItem = True
            End If
        End If
       
    ElseIf autoDefault = True Then
        'an auto delete has not been set, which is naughty.
        'Default it two two months
        Item.Subject = Item.Subject & ":d2"
        Item.Save
    End If
   
End Function
'Trawl the Sent Items Folder, go through all items and all sub folders
'Items not in a sub folder will be moved to one
Public Sub ProcessSentItems()
    Dim oSentItems As Outlook.Items
    Dim oSentFolder As MAPIFolder
    Dim oSentSubFolder As MAPIFolder
    Dim oItem As Object
   
    'Get instances of the Sent Items folder and the items collection in it
    Set oSentFolder = Application.GetNamespace("MAPI").GetDefaultFolder(olFolderSentMail)
    Set oSentItems = oSentFolder.Items
   
    'Delete each item or move it to a sub folder
    For Each oItem In oSentItems
        If deleteItem(oItem, True) = False Then
            'the item wasn't marked for imediate deletion so move it to a sub folder
            moveToSentTidyFolder oSentFolder, oItem
        End If
    Next oItem
   
    'Check the items in each sub folder for delayed deletion
    For Each oSentSubFolder In oSentFolder.Folders
        tryToDeleteItemsInFolder oSentSubFolder
       
        'remove empty folders
        If oSentSubFolder.Items.Count = 0 Then
            oSentSubFolder.Delete
        End If
    Next oSentSubFolder
   
    Set oSentFolder = Nothing
    Set oSentSubFolder = Nothing
    Set oSentItems = Nothing
    Set oItem = Nothing
End Sub
'Trawl the Sent Items Folder, go through all items and all sub folders
'Items not in a sub folder will be moved to one
Public Sub ProcessInbox()
    Dim oInboxFolder As MAPIFolder
    Dim oStorageFolder As MAPIFolder
   
    'Get instance of the inbox folder
    Set oInboxFolder = Application.GetNamespace("MAPI").GetDefaultFolder(olFolderInbox)
    tryToDeleteItemsInFolder oInboxFolder
   
    'Get instance of the Storage folder,
    'this only works because I've manually created a folder under inbox called 'store'
    'where I sling mails that I'm keeping, but only for a defined anount of time
    Set oStorageFolder = oInboxFolder.Folders.Item("store")
    tryToDeleteItemsInFolder oStorageFolder
   
    Set oInboxFolder = Nothing
    Set oStorageFolder = Nothing
End Sub
Private Sub tryToDeleteItemsInFolder(folder As MAPIFolder)
    Dim oFolderItems As Outlook.Items
    Dim oItem As Object
   
    'Get instance of the items collection in the folder
    Set oFolderItems = folder.Items
   
    'try to delete each item
    For Each oItem In oFolderItems
        deleteItem oItem, False
    Next oItem
   
    Set oFolderItems = Nothing
    Set oItem = Nothing
End Sub
'move items in the root of a folder to a sub folder names bu the To field (or applicable alernative)
Private Sub moveToSentTidyFolder(ByRef SourceFolder As MAPIFolder, Item As Object)
    Dim strFolderName As String
    Dim oMoveToFolder As MAPIFolder
   
    'Get the name of the folder to be used
    strFolderName = getSentToName(Item)
   
    'Does new folder exist?
    On Error Resume Next
        Set oMoveToFolder = SourceFolder.Folders(strFolderName)
    On Error GoTo 0
   
    If oMoveToFolder Is Nothing Then
        Set oMoveToFolder = SourceFolder.Folders.Add(strFolderName)
    End If
   
    Item.Move oMoveToFolder
   
   
    Set oMoveToFolder = Nothing
End Sub

'Decide what type of Item this is and therefore what field to use for the folder name
Private Function getSentToName(Item As Object) As String
       
    Dim strFolderName As String
    strFolderName = "Unknown Type"
       
    Select Case True
    Case TypeOf Item Is MailItem
        strFolderName = Item.To
       
    Case TypeOf Item Is MeetingItem
        strFolderName = Item.SenderName
       
    Case TypeOf Item Is AppointmentItem
        strFolderName = Item.Organizer
       
    Case TypeOf Item Is MAPIFolder
        'Dont move Sub Folders
        'this doesn't get hit as folders aren't in the items collection of a folder
        'but if I forget in the future and call this method, passing in a folder as object
        'it could get a bit messy
        Exit Function
    End Select
    getSentToName = strFolderName
End Function
'Allows a keyboard shortcut to be set up for setting a mail item for immediate deletion
Public Sub MarkForDeletion()
    Dim oItem As MailItem
    Set oItem = Application.ActiveExplorer.Selection.Item(1)
    DelNow oItem
   
    Set oItem = Nothing
End Sub
Private Sub DelNow(Item As MailItem)
    Item.Subject = Item.Subject & ":."
    Item.Save
End Sub
'Allows a keyboard shortcut to be set up for setting a mail item for deletion in one month
Public Sub MarkForDeletion1()
    Dim oItem As MailItem
    Set oItem = Application.ActiveExplorer.Selection.Item(1)
    Del1 oItem
   
    Set oItem = Nothing
End Sub
Private Sub Del1(Item As MailItem)
    Item.Subject = Item.Subject & ":d1"
    Item.Save
End Sub
'Allows a keyboard shortcut to be set up for setting a mail item for deletion in twelve months
Public Sub MarkForDeletion12()
    Dim oItem As MailItem
    Set oItem = Application.ActiveExplorer.Selection.Item(1)
    Del12 oItem
   
    Set oItem = Nothing
End Sub
Private Sub Del12(Item As MailItem)
    Item.Subject = Item.Subject & ":d12"
    Item.Save
End Sub
'Allows a keyboard shortcut to be set up for setting a mail item for deletion in six months
Public Sub MarkForDeletion6()
    Dim oItem As MailItem
    Set oItem = Application.ActiveExplorer.Selection.Item(1)
    Del6 oItem
   
    Set oItem = Nothing
End Sub
Private Sub Del6(Item As MailItem)
    Item.Subject = Item.Subject & ":d6"
    Item.Save
End Sub
'Allows a keyboard shortcut to be set up for setting a mail item for deletion in twenty four months
Public Sub MarkForDeletion24()
    Dim oItem As MailItem
    Set oItem = Application.ActiveExplorer.Selection.Item(1)
    Del24 oItem
   
    Set oItem = Nothing
End Sub
Private Sub Del24(Item As MailItem)
    Item.Subject = Item.Subject & ":d24"
    Item.Save
End Sub

Selecting the next editable cell in a Datagridview

One aspect of DataGridViews that you can often find being discussed in a healthy and vigorous fashion on the internet is read-only cells, specifically the lack of TabStop = false functionality. It's very common to see dev's trying to force the selected cell to change when a Read-only one is focused and then they post on a board somewhere about a Reentrant Exception from SetCurrentCellAddressCore, the following code will stop the exception happening and find the next available cell (or control):

        //a delegate is needed to avoid a circular loop when selecting a cell when in a cell selection event

        private delegate void SetColumnAndRowOnGrid(DataGridView grid, int i, int o);

        //can this cell be entered?
        //***********************************************
        //The Cell Enter Event Handler - First step in the code flow
        //***********************************************

        private void grid_CellEnter(object sender, DataGridViewCellEventArgs e)

        {

            //typesafe check

            if (sender is DataGridView)

            {

                DataGridView grid = (DataGridView)sender;

                if (grid.Rows[e.RowIndex].Cells[e.ColumnIndex].ReadOnly)

                {

                    //this cell is readonly, find the next tabable cell

                    if (!setNextTabableCell(grid, e.ColumnIndex, e.RowIndex))

                    {

                        //or tab to the next control

                        setNextTabableControl();

                    }

                }

            }

            else

            {

                throw new InvalidOperationException("this method can only be applied to controls of type DataGridView");

            }

        }

        //***********************************************
        //Find the next cell that we want to be selectable
        //***********************************************

        private bool setNextTabableCell(DataGridView grid, int nextColumn, int nextRow)

        {

            //keep selecting each next cell until one is found that isn't either readonly or invisable

            do

            {

                //at the last column, move down a row and go the the first column

                if (nextColumn == grid.Columns.Count - 1)

                {

                    nextColumn = 0;

                    //at the last row and last column exit this method, no cell can be selected

                    if (nextRow == grid.Rows.Count - 1)

                    {

                        return false;

                    }

                    else

                    {

                        nextRow = Math.Min(grid.Rows.Count - 1, nextRow + 1);

                    }

                }

                else

                {

                    nextColumn = Math.Min(grid.Columns.Count - 1, nextColumn + 1);

                }

            }

            while (grid.Rows[nextRow].Cells[nextColumn].ReadOnly == true ||

                        grid.Rows[nextRow].Cells[nextColumn].Visible == false);

            //a cell has been found that can be entered, use the delegate to select it

            SetColumnAndRowOnGrid method = new SetColumnAndRowOnGrid(setGridCell);

            grid.BeginInvoke(method, grid, nextColumn, nextRow);

            //that's all I have to say about that

            return true;

        }

        //***********************************************

        // Method pointed to by the delegate
        //***********************************************

        private void setGridCell(DataGridView grid, int columnIndex, int rowIndex)

        {

            grid.CurrentCell = grid.Rows[rowIndex].Cells[columnIndex];

            grid.BeginEdit(true);

        }

        //***********************************************
        // All the cells in the grid have been tried, none could be tabbed
        // to so move onto the next control
        //***********************************************

        private void setNextTabableControl()

        {

            this.SelectNextControl(this, true, true, true, true);

        }