smithproject.org

I'm attempting to run this query:

Code:

<cfquery name="updateDetails" datasource="#application.dsn#">
     UPDATE Customer_HostingPlan
     SET
         HostingPlanID=<cfqueryparam cfsqltype="CF_SQL_INTEGER" value="#form.planid#" null="#hpidnull#"/>
         , PaymentsPerYear=<cfqueryparam cfsqltype="CF_SQL_INTEGER" value="#form.paymentsperyear#" null="#ppynull#"/>
         , ServerID=<cfqueryparam cfsqltype="CF_SQL_INTEGER" value="#form.serverid#" null="#srvnull#"/>
         , BillingStartDate=<cfqueryparam cfsqltype="CF_SQL_DATE" value="#dateformat(form.billingstartdate, 'yyyy-mm-dd')#" null="#bsdnull#"/>
     WHERE id=<cfqueryparam cfsqltype="CF_SQL_INTEGER" value="#val(form.chp_id)#"/>
 </cfquery>

And getting this error:

Error message: Error parsing date 2006-01-12!
Error type: database

This is running against the latest MySQL. The query was originally working just fine - and what I did was add the BillingStartDate field, which is defined as the "date" type in the database. As the value in the form field, I've tried "2006-12-01", and "12/1/2006" (without quotes, of course). I've also tried removing the dateformat from the cfqueryparam line, and this makes no difference.

Any idea what's wrong here?

Hi JAT,

in Smith, java.text.SimpleDateFormat.parse(String) is used to parse the value of cfqueryparam cfsqltype="CF_SQL_DATE". The instance of SimpleDateFormat is constructed using the default pattern and date format symbols for the default locale. All this is done in SmithQuery class. See the code below.

You could execute the following java code to see date format for the default locale on your machine, which will do with smith queryparam of CF_SQL_DATE type:

Code:

 
 System.out.println((new SimpleDateFormat()).format(new Date()));

According to javadocs, you don't have to provide the complete date, with all the details: "By default, parsing is lenient: If the input is not in the form used by this object's format method but can still be parsed as a date, then the parse succeeds."

Here's how Smith handles queryparam of CF_SQL_DATE type (SmithQuery class, about line 516):

Code:

 
     } else if (param.cfsqltype.equals(QueryUtil.CF_SQL_DATE)) {
         try {
             SimpleDateFormat sDF = new SimpleDateFormat();    // <<<<<
             if (param.nul) {
                 ps.setNull(counter, Types.DATE);
             } else {
                 ps.setDate(counter, (java.sql.Date) sDF.parse(param.value));    // <<<<<
             }
         } catch (ParseException pExc) {
             throw new DatabaseException(new ErrMsg(
                     "error.sql.parseerror", param.value), pExc);
         }
     }

orcus

I'm sorry, but this doesn't really close the gap for me. I'm not a java programmer and don't know how to compile and execute the code you provided to see what my local date/time format should be.

Is there a Linux command to see the same thing?

Can you give examples of other date formats I could try?

A simple solution might be to use cfsqltype="CF_SQL_VARCHAR" and let MySQL automatically convert from a string to a date. MySQL will do this.

re: the Java code:
Using the Java SimpleDateFormat looks like it might be a mistake.
The default format for SimpleDateFormat is often an unusual one - for example, in the U.S. locale it requires a string like "12/01/06 00:00 AM".
Even if this string is used, it is illegal to cast from a java.util.Date to a java.sql.Date, so it still fails.

I'll test some more and enter a bug if I confirm this. Too bad it missed being in time for 1.3b5.

-tom-

Thanks Tom!

we can make an extra "merry christmass" release