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

« Back to Tecnologia | | Create a Link | Leave a comment »

---

4 comments at the moment:

just putting my name down so I can subscribe to the comments. I've got a dozen idea for this, all of them wrong.
by:  barry.b @ 31-Jan-2008 10:25:00  


I'm not sure about the query of queries typing error, but I know from experience that QofQ can be fraught with problems and they're almost always related to typing.

However, the best solution, not only to avoid the QofQ error but for performance in general, is to do this in one query instead of using multiple queries, loops, etc.
by:  Brian @ 31-Jan-2008 14:12:00  


Brian, I agree with you. I should have made a single query, but I really do not know if MySQL supports the final syntax, using left join and using a search in a case switch (or there's another way that I'm not realizing here?).

The code originally was made for a MySQL 3 server and I kept it instead of searching for the supported syntax in MySQL 4.
by:  Fernando S. Trevisan - http://fernandotrevisan.com.br/ @ 31-Jan-2008 15:45:00  


Instead of:
qContracts["paid"][currentRow] = 1

Try:
QuerySetCell(qContracts, "paid", 1, currentrow)

I had a similar problem and QuerySetCell worked.
by:  Kevin Koltz @ 02-Jun-2009 13:22:00  

---

Links to this text:

---