Well, I had an error report from a client of my ex-company. As I have an agreement with them to cover these situations I looked at it and thought "Oh no! The damn 'null null' error!".

So what was going on? Follow me:

I have a report that permits the client to find specific information about who receives referral fee over their contracts. So the client selects an user to search for and submits.

The ColdFusion template then search for referral fees this user receive in an referential table, which returns the contracts IDs. Then I search the contracts for details and loop over the contracts to mark which of them has already paid some referral to that user, do some formatting on the client's name and re-sort all data by paid, expiration date, finished date and then contract title.

To clarify, some code (I use "SELECT *" just for simplicity, don't do that on your code):

<cfquery datasource="dsn" name="qReferrals">
   SELECT DISTINCT(contract_id) AS contract 
   FROM   contract_referrals 
   WHERE  user_id = 
          <cfqueryparam cfsqltype="cf_sql_integer" value="#form.user_id#" />
</cfquery>

<cfif qReferrals.recordCount>
    <cfquery datasource="dsn" name="qContracts">
      SELECT *,
             0 AS paid,
             '' AS clientName
      FROM   contract
      WHERE  contract_id IN 
             <cfqueryparam cfsqltype="cf_sql_integer" list="yes" 
                 value="#valueList(qReferrals.contract)#" />
    </cfquery>
</cfif>

So what the heck am I doing here? Well, I search for the referrals, verify if there is any, then I search for my contracts, creating two "to-be-filled" fields:

             0 AS paid,
             '' AS clientName

I thought the first one (paid) was being created as a NUMBER, a INT or TINYINT, I really doesn't care - but a damn number. The second field doesn't matter for this example. Alright? Let's go on:

<cfloop query="qContracts">
    <cfquery datasource="dsn" name="qPaid">
        SELECT movement_id
        FROM   movement
        WHERE  contract_id = <cfqueryparam cfsqltype="cf_sql_integer" 
                                 value="#contract_id#" />
                 AND
               movement_paid = 1
    </cfquery>
    <cfif qPaid.recordCount>
        <cfset qContracts["paid"][currentRow] = 1 />
    </cfif>
</cfloop>

Here I go through all the contracts found and search one by one for paid movements on the movement table. If I found one or more, I change the "paid" flag from 0 to 1. Pretty simple, uh?

Well, not all that simple. Let's see the final piece of code, the one that throws an error:

<cfquery dbtype="query" name="qContracts">
    SELECT * FROM qContracts ORDER BY
        paid, expirationDate, finishedAt, title
</cfquery>

Again, pretty simple. I'm just reordering the query, using a query of queries and the already filled field that I created within the first run of the query.

But this code generates a null null error. Why? I don't really know. The solution, for me, was to change the assigment of the "paid" field to string and then everything worked fine. I even tried to use another name for my final query (I thought: well, maybe CF is "coldfused" about the same name and overriding something), but it seems that the field creation above wasn't in a numeric type at all, so CF becomes all lost about the data type, returning the "null null" error. Here goes the final code:

<cfquery datasource="dsn" name="qReferrals">
   SELECT DISTINCT(contract_id) AS contract 
   FROM   contract_referrals 
   WHERE  user_id = 
          <cfqueryparam cfsqltype="cf_sql_integer" value="#form.user_id#" />
</cfquery>

<cfif qReferrals.recordCount>
    <cfquery datasource="dsn" name="qContracts">
      SELECT *,
             '0' AS paid,
             '' AS clientName
      FROM   contract
      WHERE  contract_id IN 
             <cfqueryparam cfsqltype="cf_sql_integer" list="yes" 
                 value="#valueList(qReferrals.contract)#" />
    </cfquery>
</cfif>
<cfloop query="qContracts">
    <cfquery datasource="dsn" name="qPaid">
        SELECT movement_id
        FROM   movement
        WHERE  contract_id = <cfqueryparam cfsqltype="cf_sql_integer" 
                                 value="#contract_id#" />
                 AND
               movement_paid = 1
    </cfquery>
    <cfif qPaid.recordCount>
        <cfset qContracts["paid"][currentRow] = "1" />
    </cfif>
</cfloop>
<cfquery dbtype="query" name="qContracts">
    SELECT * FROM qContracts ORDER BY
        paid, expirationDate, finishedAt, title
</cfquery>

And this is the code that works, but just 'cause the type of my data doesn't matter at all (I can sort from numeric or string types easily), but if that wasn't the case, I would be in trouble.

If someone has a better explanation on this one, please comment. Ah! I was almost forgetting: this code runned on a CFMX 6.1 with all updates on a Linux Enterprise (not sure which version) machine and MySQL 4.1.12.

Labels: adobe, cfml, coldfusion, work

4 comments | Links to this text |

Jeffrey Zeldman wrote about the redesign of Happy Cog Studios web-site. This lead me through a really long line of thought.

As I pointed at previous post, design is important and ColdFusion being a programming language attached - since Macromedia's days - to the design world is a great thing.

That being said, I see ColdFusion development being fragmented these days. Some people argue for more programmatic, Object Oriented recurses. Some jumps to the boat to fight for more easy-to-use solutions. There's no consensus at the community.

To me is far more important to implement things quickly and easily - that meaning more webapps with less time - to make great systems and more money. As an entrepreneur, I'm interested in tools, tags and features for that. We have a handful of ideas but no time to implement all of them, even using CF, which we believe to be a RAD.

To accomplish that, Adobe should focus on two things:

• A great IDE to allow developers to do MORE with LESS. I have to say I'm not using Dreamweaver, Eclipse nor Flex tools and I don't know the state of these softwares. But first key concept to focus on is IDE. Period.
• To map the most common tasks and create really easy ways to implement then. CF price is high and I can bet we never used some of the most advanced features it delivers. We just haven't need of then.

I'm not saying that Adobe should forget all the great and advanced features. It has to be there and we like to know that, if we need, they'll be available without the need to spent more money. But the main focus should be RAD solutions, and the "Flex+ColdFusion" package seems to me as a sign that Adobe really understand things this way.

As a side note, Happy Cog new design is amazing. I wanted to do THAT with this web-site but, as you can see... I'm in lack of design skills to do so. =)

Labels: adobe, coldfusion, entrepeneur, flex, ide, rad, simplicity, work

Leave a comment | Links to this text |

The numbers are huge:

• 660% growth in number of cities with broadband internet access in the last 3 years
• 20% growth in the last year
• Computer (PC) market 46% growth last year
• Positivo is the market leader, with 835.000 computers sold in 2006

This was made possible by a happy combination of many factors, like reduced taxes, increasing obligation of computer knowledge to apply for jobs, entertainment possibilities, etc. The bottom line is: more people here has internet access and computers. And that's fantastic for the Brazilian technology market.

Looking 10 years back, when I started, things were so different... I felt like I was begging for something or, better saying, felt like I was stealing something from them. "Internet is just fashion", "It'll pass by", "Wait, this amount of money for just a web-site?"... I heard a lot of that back in that days.

Now it's not SO different, but people look for the internet (and web, specially) and recognizes its importance, its influence over the crowds. And it reflects back to the computer and internet access market, and then back to companies like GrupoW, thanks god!

Being respected for what you do and getting some money for that is not a crime, is what every worker should have. And I'm glad we (internet builders, webdevelopers, designers, entrepreneurs and all people that work with internet related business) are finally starting to get it.

Related links for "Brazilians more connected":

Positivo sells 835.000 computers and make R$ 1,349 bi (about US$ 600.000) in 2006, in Portuguese

"First PC" pushes Brazilian market sells, in Portuguese

Brazil: 20% more cities with broadband internet access, in Portuguese

Labels: brasil, computer market, entrepeneur, internet business, respect, work

1 comment | Links to this text |