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
|