CodeCharge Studio
search Register Login  

Visual Web Reporting

Visually create Web Reports in PHP, ASP, .NET, Java, Perl and ColdFusion.
CodeCharge.com

YesSoftware Forums -> CodeCharge -> Tips & Solutions

 Stop CCS from mistaking a "" for a NULL

Print topic Send  topic

Author Message
Frosty555

Posts: 7
Posted: 06/13/2006, 7:18 AM

I've been using this fix in just about every project I use.

I discovered after some debugging that CodeCharge was passing nulls to my stored procs when i performed update/inserts/deletes. After some playing around i found out that it was because CCS was trying to interpret a blank string (ex: the user just doesn't specify a field value)

When CodeCharge creates SQL statements, whether it be SELECTS, EXEC statements for stored procs, etc. It uses the ToSQL() function to convert the user data into a string that can be used in an SQL statement. This function, due to it's logic, treats any data with a length of 0 as a null value. Blank strings fit in this category.

Unfortunately then, it converts blank strings "" into NULLs. So if a user does not fill out a field in a form, and that field doesn't have a default value, instead of passing a blank string to a stored procedure, it passes null.

In SQL Server there's a big difference between a null passed to a parameter, vs a blank string. Often there are times when you want an explicit blank string passed and not null.

I modified the ToSQL() method as follows:

  
    // NOTE: I made a custom change to the ToSQL() method to stop blank strings ("")  
    // from being returned as NULL values. An additional elseif clause  
    // has been added.  
    function ToSQL($Value, $ValueType, $List = false)  
    {  
        $RealValue = $Value;  
        if (is_array($Value) && $List) {  
            $Values = array();  
            foreach ($Value as $Val)   
                $Values[] = $this->ToSQL($Val, $ValueType);  
            return $Values;  
        } elseif (is_array($Value) && !$List) {  
            $Value = count($Value) ? $Value[0] : null;  
        }  
        if (($ValueType == ccsDate && is_array($RealValue)) || strlen($Value) || ($ValueType == ccsBoolean && is_bool($Value)))  
        {  
            if($ValueType == ccsInteger || $ValueType == ccsFloat)  
            {  
                return doubleval(str_replace(",", ".", $Value));  
            }  
            else if($ValueType == ccsDate)  
            {  
                if (is_array($RealValue)) {  
                    $Value = CCFormatDate($RealValue, $this->DateFormat);  
                }  
                return "'" . str_replace("'", "''", $Value) . "'";  
            }  
            else if($ValueType == ccsBoolean)  
            {  
                if(is_bool($Value))  
                    $Value = CCFormatBoolean($Value, $this->BooleanFormat);  
                else if(is_numeric($Value))  
                    $Value = intval($Value);  
                else if(strtoupper($Value) == "TRUE" || strtoupper($Value) == "FALSE")  
                    $Value = strtoupper($Value);  
                else  
                    $Value = "'" . str_replace("'", "''", $Value) . "'";  
                return $Value;  
            }  
            else  
            {  
                return "'" . str_replace("'", "''", $Value) . "'";  
            }  
        }  
        elseif($Value == "")      // Custom  
        {  
            return "''";  
        }  
        else  
        {  
            return "NULL";  
        }  
    }  

Applying this fix will cause the entire connection class to be in "user-edited" mode, which means CCS will not touch it or auto-generate it anymore. However! All the normal settings in the connection for your project are not set in that part of Common.php, so your database name/password will still update as normal using the CCS user interface.

Enjoy,

-Gordon
View profile  Send private message

Add new topic Subscribe to topic   


These are Community Forums for users to exchange information.
If you would like to obtain technical product help please visit http://support.yessoftware.com.

MS Access to Web

Convert MS Access to Web.
Join thousands of Web developers who build Web applications with minimal coding.

CodeCharge.com

Home   |    Search   |    Members   |    Register   |    Login


Powered by UltraApps Forum created with CodeCharge Studio
Copyright © 2003-2004 by UltraApps.com  and YesSoftware, Inc.