Problem with ColdFusion communicating with MySQL database
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 neverendi开发者_如何学JAVAng 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" >
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.
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.
精彩评论