blog.padowi.se

Edit: Refurbished the post with a better title, as suggested by mra.

A friend from my old class at the University sent me a message earlier tonight asking me some programming advice. I was in the process of stepping away from the computer but he caught me just in time, and he’s a good friend, and I have to admit that his sporadic inquiries, which also gives me some slight insight into how code in the corporate world can look like, sometimes prove quite challenging, and I do enjoy trying to provide help, guidance and the occasional solution. So I sat back down, made myself comfortable and read his situation:

One thing I have come to notice, whomever it is that asks me, is that they have a pre-conceived notion about how they would like to solve the problem. More often than not, the help I provide is more in line of  offering an alternative way of looking at the problem. And the key my apparent success lies in that I gather as much information beforehand, and then try to either remember a similar situation, or think up a solution based on previous situation or knowledge bordering the problem at hand.

He described that there existed a loop, in which values were being generated, and used to build a query, which was then generated (inside the loop) resulting in a result set. And following the loop, he wanted to make use of the result set derived from the iterations of the loop.

The primary problem, at first, seemed to be that the result set was being overwritten with newer information at every iteration, and at the loops end, there was only data in the result set from the last iterations query.

Problem solving mode kicked in, but soon reported that there was too many unknowns to come up with something resembling a solution, more information was needed.

Well, no, the first thought was how difficult it would be to go into the internal storage-structure of the result set object and “append” the information returned in this query with the result set from the previous query. I rejected that idea within a few seconds time of deliberation. (Don’t know about you, but I would not feel comfortable fracking around with the internal state of a Java object I really don’t know anything about.)

So, more information, did query X+1 depend on the information returned in query X? If not, what was the data being generated in the loop, which was fed to the query?

I am ashamed to admit it, but my next idea was almost as big an ugly hack as the first idea, which essentially was to create a result set array, and store the result set from each query in that array, and instead of manipulating one big result set at the end of the loop, just manipulate each element in the array individually. I honestly cannot say why I thought of that idea, but I think that may be a rather “hard-coded” notion I get when I see Java code… in any case, all the while I was examining this hair-brained idea for flaws (I cannot say that I liked the idea, it would probably work, but it didn’t feel “right”) I started getting answers to my previously stated questions. The generated data was integers, id numbers to be specific, and they did not relate depend on the previous query.

The code, then, was basically something along the lines of:

for (i=0; i<something_predetermined; i++)
{
    resultSet = SQLquery(query, i);
}

The little code snippet I got to see looked a little different, it has been obfuscated to preserve his anonymity

That little tidbit of information made me think about what he had going into the problem, where the problem could be broken up into smaller pieces etc. Basically, he had a list of id numbers, or could at least generate such a list, independently from the loop and queries. That’s when I remembered a piece of SQL syntax that I have never had a chance to try out for myself. Something I’d read, but never had any use of personally. Something along the lines of:

SELECT * FROM foo WHERE id IN [1,2,3,4];

And I was almost correct. My only hurdles was that I a.) wasn’t sure that it was correct syntax, and b.) the specification use parentheses to represent a list of values, not square brackets. I.e.:

SELECT * FROM foo WHERE id IN (1,2,3,4);

This solution was something I would be able to live with. Not that I really had any stake here, but I couldn’t really recommend the usage of a piece of code I wouldn’t want to be caught dead using myself, but this, outsourcing the work to the database, work it was written to excel at performing, yes, this felt “right”.

Next question, would this syntax work in whatever database his project was powered by? As it turns out, it would seem so. A quick Google revealed that SQL Server should indeed support this syntax. My friend did some limited testing and that also checked out, so I considered my work, at least for now, done.