While SELECT is a whole different beast, the other three are pretty much the same across different database engines, so you usually learn the basics only.

The thing is, you should REALLY read the documentation for your specific engine, because most of the time, each engine adds cool functionality to the standards (OK, this will break your code when you change from database X to Y, but in the meantime, it makes development so much easier)

How many times, have you written code that looks like this:

1
2
3
4
5
6
7
8
9

bool exist = CheckIfDuplicate(key);
if(!exist)
{
	//insert
}
else
{
	//update
}

I hope not as many as me... This is the day I learned about:

• REPLACE
• INSERT IGNORE
• INSERT ... ON DUPLICATE KEY UPDATE

So if you are not familiar with this syntax and you're a heavy MySQL user, please go read the documentation. Go. Now.

One of the UI requirements was to drag and drop elements from a ListView to a TreeNode. So, this is how to enable Drag&Drop:

First, you have to enable the AllowDrop property for your TreeView

treeView1.AllowDrop = true;

Then, in your ListView, you have to add code to the ItemDrag event:

listView1.DoDragDrop(listView1.SelectedItems, DragDropEffects.Move);

The DoDragDrop method begins a Drag&Drop operation. The first parameter, is the element(s) you want to drag around. The second, is the type of dragging-and-dropping you want to do. Here is a list:

You can combine more than 1 DragDropEffect by bitwise ORing them:

listView1.DoDragDrop(listView1.SelectedItems, DragDropEffects.All | DragDropEffects.Link);

In my project, I only need DragDropEffects.Move functionality.

Well. Now we only have two more things to do. First, we have to take care of the DragOver event. This is raised by your target control (in this case a TreeView) every time the mouse moves over the target's visible area.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

private void treeView1_DragOver(object sender, DragEventArgs e)
{
    if (e.Data.GetDataPresent(typeof(ListView.SelectedListViewItemCollection)))
    {
        e.Effect = DragDropEffects.Move;
    }
 
    Point position = treeView1.PointToClient(new Point(e.X, e.Y));
    TreeNode tn = treeView1.GetNodeAt(position);
 
    if (tn != null)
    {                
        treeView1.SelectedNode = tn;                
        if (!tn.IsExpanded)
        {
            tn.Expand();
        }                
    }
}

There are some interesting bits of code in there:

if (e.Data.GetDataPresent(typeof(ListView.SelectedListViewItemCollection)))
{
    e.Effect = DragDropEffects.Move;
}

Our data (the one we are dragging around) is stored in the Data property of the event. The GetDataPresent method determines whether this data can be converted to the specified format.

In this case, our format is ListView.SelectedListViewItemCollection. Why? you may ask. Well, because this is the returning type of listView1.SelectedItems (which is the object we are dragging around, remember?).

We could put this particular piece of code inside the target's (TreeView) DragEnter event. This way, it only has to be executed once.

This is also important:

Point position = treeView1.PointToClient(new Point(e.X, e.Y));
TreeNode tn = treeView1.GetNodeAt(position);

The DragEventArgs object holds some information about the event. In this case we are using e.X and e.Y to know the location of the mouse at any given time. But the thing is that the mouse location is relative to the screen, so we have to convert it to client coordinates. That's exactly what PointToClient does.

Now that we have the position relative to the client, we can get the TreeNode under that position. If there is no TreeNode under that position (maybe the mouse is moving over whitespace) it returns null.

The rest of the code is trivial, it expands a TreeNode if its not expanded already.

Lastly, we have the DragDrop event, in which we release the mouse button over our target TreeNode.

1
2
3
4
5
6
7
8
9
10
11
12
13
14

private void treeView1_DragDrop(object sender, DragEventArgs e)
{
    Point position = treeView1.PointToClient(new Point(e.X, e.Y));
 
    if (e.Data.GetDataPresent(typeof(ListView.SelectedListViewItemCollection)))
    {
        TreeNode tn = treeView1.GetNodeAt(position);
        var li = (ListView.SelectedListViewItemCollection)e.Data.GetData(typeof(ListView.SelectedListViewItemCollection));
        foreach (ListViewItem item in li)
        {
            MessageBox.Show(tn.Text + " = " + item.Text);                 
        }                
    }
}

The only interesting or new line in here is this one:

var li = (ListView.SelectedListViewItemCollection)e.Data.GetData(typeof(ListView.SelectedListViewItemCollection));

this method retrieves the data associated with the specified data format (at least, that's what MSDN says) but you still have to cast it accordingly.

Once you have your data, you can do anything you want with it (i'm just displaying which TreeNode received the Items and the Items' text)

Happy hacking.

UPDATE

If you want to see which TreeNode you're selecting while dragging, just add this to your code:

1

treeView1.HideSelection = false;

Yup, that's right. Reading.

I got a book shipment from amazon.com last week and I have been reading since then. These are the books I got:

• Head First Design Patterns
• Design Patterns
• Refactoring
• Mythical Man Month
• Programming Pearls
• Coders at Work

So, as you can see, I have a *LOT* to read. The good thing about my job, is that it allows me some spare time to educate myself.

I haven't done any programming in the last week, but I promise I'll write a post on blackberry development in the next few days, just let me finish my book first

But I needed some JDBC "drivers" for my SQL Engines (namely MySQL and MS SQL). So, to save you the burden of looking for the files all over the internet, here it is, in a single zip file

JDBC Drivers for MySQL and MSSQ

The zip contains 3 jar files:

• mysql-connector-java-5.1.12-bin.jar - For MySQL v5.1 or lower
• sqljdbc.jar - MS SQL versions 2005 and 2000
• sqljdbc4.jar - This works only for MS SQL 2008

Just uncompress the file into some folder, and add the appropiate drivers from eclipse dpt and you're done!

Update: This listing includes links to JDBC drivers for all major database engines.

If your "Windows Key" (Super_L) is not detected, you will have to run this command:

xmodmap -e "remove mod4 = Super_L"

It seems to work even after rebooting the machine

Can't install Eclipse plugins

In order to install eclipse plugins, after installing eclipse-platform, you *must* install the eclipse-pde package (which comes with the eclipse-jdt as a dependency, oh well...)

This is an ongoing effort to write the odd things I have to do to get my OS to behave as I want.

Lucky as I always am, our hosting provider already had this module installed and working, so I didn´t have to go thru any extra steps, just right on programming.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33

// Open pop mailbox
if (!$mbox = imap_open ("{imap.gmail.com:993/ssl/novalidate-cert}", "mail@googledomain.com", "password"))
{
    die ('Cannot connect/check pop mail! Exiting');
}
 
if ($hdr = imap_check($mbox))
{
    $msgCount = $hdr->Nmsgs;
}
else
{
    die("Failed to get mail");
}
 
//Read $msgCount from INBOX
$MN = $msgCount;
$emails = imap_fetch_overview($mbox,"1:$MN",0);
 
//print email information and delete each email
foreach($emails as $email)
{
    echo "<pre>";
    echo "TO : {$email->to}<br />";
    $file = extract_attachments($mbox, $email->msgno);
    print_r($file);
    echo "</pre>";
    imap_delete($mbox, $email->msgno);
}
 
//delete emails marked for deletion and close connection
imap_expunge($mbox);
imap_close($mbox);

Well, thats shows how to connect to a google domain. I'll talk about the "extract_attachments" function in another post, I'm pretty tired right now.

You should really check the PHP imap function list. There is some really cool stuff going on in there.

NOTE TO SELF the novalidate-cert bit on the connection string to the imap inbox, saves you from quite a few headaches

Full-Text Search Functions

Man, there is some serious voodoo shit going on in there.

In the server:

1. Open SQL Server Configuration Manager
2. Under SQL Server Network Configuration, select Protocols for SQLEXPRESS and make sure TCP/IP is enabled. Otherwise, enable it
3. Then, select SQL Server Services, and make sure SQL Server Browser is on Start Mode: Automatic

We need to be sure, that our server accepts SQL Authentication. If you know this option is already enabled in your server, skip the next steps. If you know they are not, or you're not sure, follow this steps:

1. Open Management Studio (in the server) and connect to your database, using Windows Authentication Mode
2. Right click on your database, and select Properties
3. Under Security, select SQL Server and Windows Authentication mode
4. Under Connections, check "Allow connections to this server"

I read somewhere that you should reboot the server. Just restarting the SQL Server Service doesn't work (I can confirm this).

Now, just remember ALWAYS to add the SQLEXPRESS part to your hostname. For instance, if your server name is win2008server, then you should connect to win2008server\sqlexpress in your applications.

I needed to format a DateTime field, so I did this:

1
2
3
4
5
6
7
8
9

private void dataGridView1_CellFormatting(object sender, DataGridViewCellFormattingEventArgs e)
{
    if (dataGridView1.Columns[e.ColumnIndex].DataPropertyName == "Date")
    {	
        DateTime value = (DateTime)e.Value;
        e.Value = value.ToString("G");
        e.FormattingApplied = true;
    }
}

In this case, "Date" is the property name of a DateTime field.

In case you are curious, the DataSource assignment is like this:

1
2
3

List<GoodReceipts> grrs = new List<GoodReceipts>();
grrs.AddRange(logic.GetGRRData(consumptions, validSince, validTo));     
dataGridView1.DataSource = grrs;

Apparently, PHP is not allowed to include this Dll without getting sued or something.