Problem with ColdFusion communicating with MySQL database

Question

Hi,

I have been working to migrate a non-profit website from a local server (running Windows XP) to a GoDaddy hosting account (running Linux). Most of the pages are written in ColdFusion. Things have gone smoothly, up until this point. There is a flash form within the site (see this page: http://www.preservenet.cornell.edu/employ/submitjob.cfm) which, when completed, takes the visitor to this page: submitjobaction.cfm . I'm not quite sure what to make of this error, since I copied exactly what had been in the old MySQL database, and the .cfm files are exactly as they had been when they worked on the old server. Am I missing something?

Below is the code from the database that the error seems to be referring to. When I change "Positionlat" to some default value in the database as it suggests in the error, it says that another field needs a default value, and it's a neverending chain of errors as I try to correct it.

This is probably a stupid error that I'm missing, but I've been working at it for days and can't find what I'm missing. I would really appreciate any help.

Thanks!

-Greg

DROP TABLE IF EXISTS employopp;

CREATE TABLE employopp (

POSTID int(10) NOT NULL auto_increment,

USERID varchar(10) collate latin1_general_ci default NULL,

STATUS varchar(10) collate latin1_general_ci NOT NULL default 'ACTIVE',

TYPE varchar(50) collate latin1_general_ci default 'professional',

JOBTITLE varchar(70) collate latin1_general_ci default NULL,

NUMBER varchar(30) collate latin1_general_ci default NULL,

SALARY varchar(40) collate latin1_general_ci default NULL,

ORGNAME varchar(70) collate latin1_general_ci default NULL,

DEPTNAME varchar(70) collate latin1_general_ci default NULL,

ORGDETAILS mediumtext character set utf8 collate utf8_unicode_ci,

ORGWEBSITE varchar(200) collate latin1_general_ci default NULL,

ADDRESS varchar(60) collate latin1_general_ci default 'none given',

ADDRESS2 varchar(60) collate latin1_general_ci default NULL,

CITY varchar(30) collate latin1_general_ci default NULL,

STATE varchar(30) collate latin1_general_ci default NULL,

COUNTRY varchar(3) collate latin1_general_ci default 'USA',

POSTALCODE varchar(10) collate latin1_general_ci default NULL,

EMAIL varchar(75) collate latin1_general_ci default NULL,

NOMAIL varchar(5) collate latin1_general_ci default NULL,

PHONE varchar(20) collate latin1_general_ci default NULL,

FAX varchar(20) collate latin1_general_ci default NULL,

WEBSITE varchar(200) collate latin1_general_ci default NULL,

POSTDATE varchar(10) collate latin1_general_ci default NULL,

POSTUNTIL varchar(20) collate latin1_general_ci default 'select date',

POSTUNTILFILLED varchar(20) collate latin1_general_ci NOT NULL default 'until filled',

texteHTML mediumtext character set utf8 collate utf8_unicode_ci,

HOWTOAPPLY mediumtext character set utf8 collate utf8_unicode_ci,

CONFIRSTNM varchar(30) collate latin1_general_ci default NULL,

CONLASTNM varchar(60) collate latin1_general_ci default NULL,

POSITIONCITY varchar(30) collate latin1_general_ci default NULL,

POSITIONSTATE varchar(30) collate latin1_general_ci default NULL,

POSITIONCOUNTRY varchar(3) collate latin1_general_ci default 'USA',

POSITIONLAT varchar(50) collate latin1_general_ci NOT NULL,

POSITIONLNG varchar(50) collate latin1_general_ci NOT NULL,

PRIMARY KEY (POSTID)

) ENGINE=MyISAM AUTO_INCREMENT=2007 DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;

UPDATE:

Where I think the "submitjobaction.cfm" page communicates with the database:

   <CFINSERT DATASOURCE="mysqlcf_preservenet" TABLENAME="employopp" FORMFIELDS=" TYPE, JOBTITLE, NUMBER, SALARY, ORGNAME, DEPTNAME, ORGDETAILS, ORGWEBSITE, ADDRESS, ADDRESS2, CITY, STATE, COUNTRY, POSTALCODE, EMAIL, NOMAIL, PHONE, FAX, WEBSITE, POSTDATE, POSTUNTIL, texteHTML, HOWTOAPPLY, CONFIRSTNM, CONLASTNM, POSITIONCITY, POSITIONSTATE, POSITIONCOUNTRY">
          <CFINSERT DATASOURCE="mysqlcf_preservenet" TABLENAME="user" FORMFIELDS=" ORGNAME, WEBSITE, ADDRESS, ADDRESS2, CITY, STATE, COUNTRY, POSTALCODE, EMAIL, PHONE, FAX, CONFIRSTNM, CONLASTNM" >

Answer 1

I use none of mysql, cfform or cfinsert so ymmv with this answer, but it seems like the problem is in the database configuration.

This blog post from 2007 suggests changing an ini setting for sql-mode. You'll need to talk to your host to check the current value and try to have it changed.

It looks like the form is sending zero-length values for unanswered fields and the database is rejecting those values.

Another approach is to replace the cfinsert with a normal cfquery - this will give you more flexibility with how you supply 'empty' values.

Answer 2

I get the following error message:

---

Error Executing Database Query.

Field 'CONFIRSTNM' doesn't have a default value

Resources:

...

What seems to happen here is that some form field (named CONFIRSTNM - I assume that's the Contact Firstname) is empty in the POST and the database doesn't have a default value set. Because your cf code is using CFINSERT you don't actually write the SQL code yourself but CF is supposed to do it for you. Hard to debug from the distance, but I think what you should do is to make sure the database was 1:1 migrated from the old environment to the new environment. Not sure how the migration was done, but it might have lost some information along the way.

Another suggestion for good coding practice is to rewrite CFINSERT with a CFQUERY tag and write the SQL statement yourself. Also make sure you use CFQUERYPARAM for all incoming parameters.