MexiCode Ideas en codigo

MySQL INSERT statement with steroids

Posted on March 21, 2010

Do you remember when you first learned about basic SQL statements? INSERT, UPDATE, DELETE and SELECT?

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:

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

Tagged as: No Comments

Drag & Drop from ListView to TreeView

Posted on March 9, 2010

I started working on a new project at work. This is the "fun stage" of the project: the prototypes and the investigation part.

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:

None The drop target does not accept the data.
Copy The data from the drag source is copied to the drop target.
Move The data from the drag source is moved to the drop target.
Link The data from the drag source is linked to the drop target.
Scroll The target can be scrolled while dragging to locate a drop position that is not currently visible in the target.
All The combination of the Copy, Move, and Scroll effects.

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;

New Books

Posted on March 2, 2010

It's been kinda quiet lately. I've been busy reading.

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 :D

Filed under: Uncategorized No Comments

Eclipse DPT JDBC Drivers

Posted on February 22, 2010

I just installed eclipse-dpt for my database access (works great, btw).

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.

Filed under: Uncategorized No Comments

Ubuntu Quirks (ongoing…)

Posted on February 21, 2010

Windows Key not detected

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.

Filed under: Uncategorized No Comments

Read IMAP Emails with PHP

Posted on February 20, 2010

Well, at work we are all about google app for your domain, we use it for virtually anything. So, we started a new project, in which we had to read emails from our web application, and I remembered reading something about php_imap somewhere, so I decided to check it out.

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

Tagged as: , No Comments

MySQL search engine (MATCH … AGAINST)

Posted on February 20, 2010

I was writing a full fledged tutorial on performing searches using MySQL, but I think is best if you just look directly into the documentation:

Full-Text Search Functions

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

Tagged as: , No Comments

Remote connection to SQL Server Express 2008

Posted on February 4, 2010

I don't know why SQL Server Express doesn't have remote access "out-of-the-box" but anyways... here is how to enable it.

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.

Formatting DataGridView cell with custom DataSource

Posted on January 13, 2010

When you use a custom DataSource (in my case a generic list) the properties become the columns and the property names, become the column header.

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;

Connect PHP and Sql Server (ntwdblib.dll)

Posted on January 12, 2010

If you need to connect PHP with SQL Server (in a Windows installation), go to your PHP install folder, and replace the file ntwdblib.dll with this one.

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

Tagged as: , No Comments