Is there any way to send an empty string to a DB table column? We are doing an ACF11 → Lucee 5.4 conversion and I have just come up against this issue.
Rather than having to enable something, at the server level, only, maybe we could also have a way of setting it at a Query level.
We could use an attribute in both:
cfqueryparam
cfprocparam
Like:
allowEmptyStrings = true [false by default]
Or you could have a setting in the Server Admin that allows NULL behaviour to be altered for Queries, independently.
Query: allow empty strings: true/false
We already made a breaking change in Lucee 6 to match ACF behaviour
LDEV-4410 stop queryparam casting empty strings to nulls
what datatype is the queryparam?
varchar never had this auto casting to null?
}
xml.setString(Caster.toString(value));
stat.setObject(parameterIndex, xml, type);
return;
case Types.VARCHAR:
case Types.LONGVARCHAR:
case Types.LONGNVARCHAR:
case Types.NVARCHAR:
case CFTypes.VARCHAR2:
stat.setObject(parameterIndex, Caster.toString(value), type);
//// stat.setString(parameterIndex,Caster.toString(value));
return;
case Types.DATE:
try {
stat.setDate(parameterIndex, new Date(Caster.toDate(value, tz).getTime()), JREDateTimeUtil.getThreadCalendar(tz));
// stat.setDate(parameterIndex,new Date((Caster.toDate(value,null).getTime())));
}
catch (PageException pe) {
if (!fns && value instanceof String && StringUtil.isEmpty((String) value)) stat.setNull(parameterIndex, item.getType());
other types like numeric did
// if(str!=null && str.length()==0) str=null;
stat.setObject(parameterIndex, str, type);
//// stat.setString(parameterIndex,str);
return;
case Types.DECIMAL:
case Types.NUMERIC:
try {
stat.setDouble(parameterIndex, (Caster.toDoubleValue(value)));
}
catch (PageException pe) {
if (!fns && value instanceof String && StringUtil.isEmpty((String) value)) stat.setNull(parameterIndex, item.getType());
else throw pe;
}
return;
case Types.DOUBLE:
case Types.FLOAT:
try {
if (type == Types.FLOAT) stat.setFloat(parameterIndex, Caster.toFloatValue(value));
else if (type == Types.DOUBLE) stat.setDouble(parameterIndex, Caster.toDoubleValue(value));
else stat.setObject(parameterIndex, Caster.toDouble(value), type);
but no longer in 6
*/
return;
case Types.CHAR:
String str = Caster.toString(value);
// if(str!=null && str.length()==0) str=null;
stat.setObject(parameterIndex, str, type);
//// stat.setString(parameterIndex,str);
return;
case Types.DECIMAL:
case Types.NUMERIC:
stat.setDouble(parameterIndex, (Caster.toDoubleValue(value)));
return;
case Types.DOUBLE:
case Types.FLOAT:
if (type == Types.FLOAT) stat.setFloat(parameterIndex, Caster.toFloatValue(value));
else if (type == Types.DOUBLE) stat.setDouble(parameterIndex, Caster.toDoubleValue(value));
else stat.setObject(parameterIndex, Caster.toDouble(value), type);
return;
case Types.VARBINARY:
case Types.LONGVARBINARY:
@Charlesr please don’t cross post on both Jira ticket and the mailing list, and don’t mix unrelated topics
All discussion, triage etc should happen on here first
just drop the urls for the tickets or posts into a new thread. I am going to split this topic out and delete your comments on the ticket
1 Like
It is:
cfprocparam
CHAR
Any empty string is sent as a NULL to the DB, which causes an error, because we have this column set to ALLOW NULL = false
UPDATE:
I have set it to VARCHAR and everything works as expected. Thanks for the explanation.
yeah, char is a strange old beast
null is a very generic topic! cfparam only shares a name with with cfquery/proc param.
ok, I have confirmed locally (with mysql), please file a bug
1 Like
* stat.setClob(parameterIndex,SQLUtil.toClob(stat.getConnection(),value)); }
*
* } else stat.setClob(parameterIndex,SQLUtil.toClob(stat.getConnection(),value));
*/
}
catch (PageException pe) {
if (!fns && value instanceof String && StringUtil.isEmpty((String) value)) stat.setNull(parameterIndex, item.getType());
else throw pe;
}
return;
case Types.CHAR:
String str = Caster.toString(value);
// if(str!=null && str.length()==0) str=null;
stat.setObject(parameterIndex, str, type);
//// stat.setString(parameterIndex,str);
return;
case Types.DECIMAL:
case Types.NUMERIC:
try {
stat.setDouble(parameterIndex, (Caster.toDoubleValue(value)));
}
Is that a comment:
// if(str!=null && str.length()==0) str=null;
In
case Types.CHAR:
String str = Caster.toString(value);
// if(str!=null && str.length()==0) str=null;
stat.setObject(parameterIndex, str, type);
//// stat.setString(parameterIndex,str);
Telling us what will happen to a CHAR? I can’t tell? Or is it actual code that was commented out and just left there? If it is just a description, then I guess we have the reason, why it is being passed as NULL?
Anyway, I will file a bug for this…
file the bug, i already have a fix and test case coverage
fix in 6.0.0.517 and 5.4.2.13
if you don’t already, please consider supporting us!
1 Like
Hi Zac
I tried to become a monthly contributor, and I want to pay by PayPal, but the payment button just keeps animating with no action.
I cannot get to the next page.
I am using the latest version of Safari on my iPhone 8+ iOS16.5.1
As soon as this is fixed I will sign up again.
