Life has its funny ways to make you do things which you should not under normal cirumstances, things which you have sworn by yourself not to.  I came to a situation where I needed to format a money number following “#,.####” within the body of a T-SQL stored  procedure.  I simply had to do it there.  The requirement came in a very late moment of the testing phase, when moving this part out was definitely not feasible, considering the approaching deadline.

T-SQL has some limited functions to format data into string.  In reference to numbers - float and money, you can use CONVERT.  For float, there is no sensible option for ‘#,.####‘.  For money, CONVERT gives three options:

As you can see, ‘#,.####’ is a mix of 1 and 2.

Poor man is devil, Bulgarians say.  What I did was to get the string, result of CONVERT with style 1, and concatenate it with the last two characters of the string, result of CONVERT with style 2:

There is a bug in the above code, which produces wrong result in case the last two digits are greater than 49.  The correct solution would be:

As good as it gets, using Google too much has a dark side.  Developers often come to the point when they implement solutions with technologies they do not understand.  They are not troubled by the fact that they use code, written by someone else, and which they don’t understand.  If they run into a trouble with the sample solution they found, they will go to various forums to find a fix.  An old joke about how to write an algorithm to find a lion in the desert and lock it in a cage had it for Delphi that Delphi developers would go to all known forums and post “Does anyone have a component which finds a lion in the desert and locks it in a cage?”.  Well, I am sorry to inform you, smart Delph-haters out there, that is no longer a Delphi-thing only.  You all fell into the trap.

To be honest, that particular approach works quite often, and it works well.  Why am I making fuss about it, then?  Development goes with good speed, results are often nice.  Until the moment when Google doesn’t give an answer.  Or, to be presice, its answer is incomprehensible, because the developer has no understanding of the technologies involves whatsoever. 

The ease with which solutions are found in Google takes away the incentive to get into real understanding of how things actually work.  As a result, junior developers stay junior, and never learn basics which will take them to a higher level of skills.

That is the reason why I am no longer eager to give code samples.  People would never read them, just copy & paste it and even ask specific questions how to integrate them into their own code. I prefer explaining things, and give out only snippets of important code.  Who claims to be a developer should be capable of figuring the rest by oneself.

Thanks to  Stilgar for helping me out with this one.

Why does the LOG file grow that much is a different story, which I am not prepared to tell now.  The question is how to make the LOG file small again within the “normal” boundaries.  Using the “Shrink database” command does no good.  Backup and restore of the database gives you the same old large log file.

The only solution I know so far involves the following steps:

1. Create a new, empty database.
2. Script all the db structure, and use it to create an identical copy of your old one into the new database you just created.
3. Use bulk copy to transfer all data from the original database to the new one.

Voila, the LOG file is back to normal.

Note that you might need to disable certain triggers and foreign keys before the copy operations - that depends completely on your database.

Illegal DATETIME, DATE, or TIMESTAMP values are converted to the “zero” value of the appropriate type (’0000-00-00 00:00:00′ or ‘0000-00-00′).

What the fuck.  Really, come on.  The purpose of a database is to store valid and integral data.  Again.  Valid.  And.  Integral.  Data.  It is valid, integral and meaningful that counts.  Not just any rubbish.  If I want rubbish, I can just read back random bytes from the harddisk.  If someone tries to write crap into a database, which the database cannot handle, the database must refuse the operation, and give error. 

Really, I wonder what the hell designers of MySQL were thinking when they implemented this specific feature.  Pardon me, the word “feature” is not appropriate.  The correct word would be “disaster”.  It is a disaster indeed, because it silently causes data loss.  One might argue that it is developers’ responsibility to validate their data, and make sure they provide the database with valid input.  While this is definitely true, it is no excuse for the database to accept graciously any garbage, and on top of that convert it to something which has nothing to do with what it is supposed to be.  That is a major fail in design, and major fail to achieve a primary goal.

The behavior above is true for MySQL versions from 4.1 to 6.0.

This little story only proves that MySQL is only good as a backend for small and simple websites.  If you mean serious business, please reconsider.  There are a bunch of free databases out there, all of them much, much better and more mature.  Some of them even open source.

And yes, I know there is a general setting in MySQL 5.02 and greater to make it give errors.  And yes, I don’t think it makes the fail lesser.

I have huge respect for COM as a technology, and within a single workstation it is a wonderful instrument for application interoperability and extensibility.  However, when you stick your head out in the outer world, things get tough.  Every weathered COM developer will tell you what a nightmare it is to setup and configure DCOM in a local network.  Yes, once configured properly, it is solid.  But that is only when you configure it.  God forbid you have to setup DCOM in a network without Active Directory.  Even with AD, doing DCOM callbacks is as pleasant as a trip in a swamp, plagued by mosquitos and crocodiles.  DCOM over the Internet?  Please.  That works only in Microsoft papers from late 90s, when the Internet was not plagued by hackers, worms and trojans, and there was little need for firewalls.

By 2005 the product was successful and the number of installations was increasing, and we found ourselves wondering how the hell to get rid of COM, and replace it with something as high-performing, but easy to deploy.  Before you start giving bright ideas about the next hot shit on the block – Java RMI, NET Remoting, web-services, just think about the hundreds of thousands lines of native Win32 code we simply had to preserve.  We needed something that would plug in with minimal changes in the existing code base.

This time we approached the problem very carefully and full of suspicions about every new thing out there.  Rumors already had it that Remoting was not as hot as it seemed at first, and its abilities for bi-directional communications were more trouble that real use.  Microsoft was already murmuring about the next hottest thing – WCF, which was supposed to solve all communication problems again.  Yeah, right.

We considered really many things, including .NET Framework.  We designed a dozen of bridge applications, only to scrap them all.  Our analysis indentified that the types of data we were transferring were standard types as integer, float and string, ADO Recordsets, and Variants.  And the server-to-client communication, of course, that was supposed to work even for clients behind firewalls.  The common types are trivial to marshal across a network with any technology out there.  The other two are sweet with COM.  Marshalling ADO Recordsets is not that hard actually, because ADO can save and load recordsets to streams.  Sweet.  Marshalling Variants is a bigger issue, as COM keeps this part strictly undocumented.  However, we managed to do that, too.

Having achieved that, we made our decision.  We implemented our own RPC protocol, based on TCP/IP.  It was capable of marshalling all necessary data types, and supported bi-directional communications through firewalls.

The DNA->Remoting->WS->WCF technology transition is not the only one in the world.  Sun Java also adopted web services and kind of forgot about RMI.  Microsoft recently made it clear that WinForms will be retired with honors to give way to WPF.

Technology is dynamic, and so is the world.  I do not blame Microsoft, neither anyone else.  The price of progress is the low long-term value of moment achievements.

The moral of the story is: never, ever trust newest technologies as the best cure for all problems.  Always test them against your case.  And, sometimes, you’d better do it your own way.  More often than not the investment is worthy.

1. There is no more a homepage for Windows DNA.  www.microsoft.com/dna quitely forwards you to NET Framework.

Great as they are, ListView contols can be very slow with large number of items (like tens of thousands), especially while populating with data.  Furthermore, by default ListView stores all its items internally.  In case you populate the List View from an in-memory collection, you end up with doubled memory usage.  Another thing to be worried about when working with tons of data.

The solution to both the performance and memory-usage issues is to use Virtual ListViews.

In this article I will not go into details about how to use virual list views in a generic Win32 application.  I will make my examples in Windows Forms.  Win32 and Delphi fans, do stay disappointed.  There is nothing actually different in using Virtual ListViews in any platform.  You either respond to messages (Win32 fashion), or attach to events (Delphi and NET).  Anyways, you are expected to do basically the same thing.

ListView controls support a special operating mode - virtual mode.  In this mode, the list view control does not store the items it displays internally.  In fact, it only stores the number of items it contains, and nothing else.  Then, when it needs to display an item, it simply sends a message to its parent window to provide the actual data of the item - caption, columns, icon index.  As a result, the listview doesn’t consume any memory, and it is very fast to initialize it - you simply don’t populate it with data.

To make a ListView control virtual, set property VirtualMode to true, and set proprery VirtualListSize to the number of elements in your collection with data.

The minimal functionality you have to provide is to add an event handler to LisiView’s RetrieveVirtualItem event.  A simple code would go something like this:

Though being clearly obvious, let me explain.  MyDataClass is a custom class I defined, which happens to have two properties of type String - Title and Description.  Of course, you are not limited or required to have such; you can have anything you need.  data is a collection of my data  (I can’t believe I typed this, but my experience at the university taught me better).

Basically, you are done. 

Virtual ListViews are even more powerful than that.  You can cache your data for improved performance, and handle extra events to support searching.  If I got you interested, just take a look at MSDN.

Consider the following code:

Nothing special, right?  It looks absolutely logical that the call to myFunction in main() would invoke void myFunction(String, String, bool = false).  Well, it won’t.  The function that will get called is void myFunction(String, bool = false).

Well, I must admit that I myself would not figure out easily.  Further reading revealed the explanation - the compiler is using the more cost-efficient conversion of char * to bool, rather than creating a new instance of class String.  Further reading proved that the behaviour of the compiler is strictly according to the C++ Standards!

Feel free to call me any names you can think of, but for me the authors of C++ Standards live in a parallel, sick universe.  Guys, have you heard that code has to be readable, too?

P.S. Edit: fixing typos.

I have two tables: table T1 with primary key field ID, and table T2 with field ID which is a foreign key to ID from T1, and field VAL.  How do I select all records from T1, for which there are records from T2 with values X and Y? 

For example, considering the following data, I want all records from T1, where exist records records from T2 with values 1 and 2.

T1 has records
1
2
3

T2 has records

1

1

1

2

2

1

3

1

3

2

Desired output is records 1 and 3 from table 1.

The question is actually interesing only if we want to select records from table T1 for which there are an arbitrary number of records with respective values in T2.

There are a few ways to implement this.  Here is my choice number one for an arbitrary number of values:

If we are considering a few number of values - like four or five, I would suggest an alternative, which might prove even more efficient:

Providing we have a foreign key on T2.ID to T1.ID, the JOIN-approach can be really efficient.