Can't add first patient

drpwayne wrote on Sunday, August 16, 2009:

Hi. I downloaded OpenEMR 3.0.1 and got it going, but when I try to add a new patient, I get a SQL error that the id is invalid.  It doesn’t matter whether I autoassign the ID or put in “1”, I get the same error message:
ERROR: insert failed: replace into patient_data set id=’’, title=‘Mr.’, fname=‘Peter’, lname=‘WAYNE’, mname=‘K’, sex=‘Male’, DOB=‘2000-08-15’, street=’’, postal_code=’’, city=’’, state=’’, country_code=’’, drivers_license=’’, ss=’’, occupation=’’, phone_home=’’, phone_biz=’’, phone_contact=’’, status=’’, contact_relationship=’’, referrer=’’, referrerID=’’, email=’’, language=’’, ethnoracial=’’, interpretter=’’, migrantseasonal=’’, family_size=’’, monthly_income=’’, homeless=’’, financial_review=’’, pubpid=‘1’, pid = 1, providerID = ‘’, genericname1 = ‘’, genericval1 = ‘’, genericname2 = ‘’, genericval2 = ‘’, phone_cell = ‘’, pharmacy_id = ‘0’, hipaa_mail = ‘’, hipaa_voice = ‘’, hipaa_notice = ‘’, hipaa_message = ‘’, squad = ‘0’, fitness=’’, referral_source=’’, regdate=‘2009-08-15’, pricelevel=‘standard’, date=NOW()

Error: Incorrect integer value: ‘’ for column ‘id’ at row 1

Thanks for any guidance.

bradymiller wrote on Sunday, August 16, 2009:

hey,

What your operating system, OpenEMR package (XAMPP vs ubuntu vs source), php version, mysql version?

I faintly remember this being discussed before; anybody know what the outcome of this was?

-brady

drpwayne wrote on Sunday, August 16, 2009:

Thanks for your reply. i tried a search of the forums on “incorrect integer value” but didn’t get anything useful.
I have Windows XP, MySQL 5.0.45, php 5.3, OpenEMR 3.0.1 downloaded as the zip package, not the XAMPP since I already had Apache and MySQL running on my system.
Shouldn’t php be trying to insert a NULL or a zero into the auto-increment field, and not an empty string? An id of ‘’ is not an integer value.  Where can I find the code to look for these little bugs?

drpwayne wrote on Sunday, August 16, 2009:

I’m trying to track my way through the php code. Is there a version of the code that is formatted for Windows with carriage returns and linefeeds, so it is easier to read?

bradymiller wrote on Sunday, August 16, 2009:

hey,
Ensure you have the following settings in php configuration file (php.ini), and re-start the apache server if you make any changes:
short_open_tag = On
max_execution_time = 60
max_input_time = 90
memory_limit = 128M
display_errors = Off
log_errors = On
register_globals = Off
post_max_size = 30M
magic_quotes_gpc = On
file_uploads = On
upload_max_filesize = 30M
error_reporting = E_ALL & ~E_NOTICE & ~E_STRICT

Let us know if bug still there after above changes.

-brady

-brady

drpwayne wrote on Sunday, August 16, 2009:

The problem is still there. Here’s what I’ve done. I’ve been picking my way through the php scripts – and parenthetically, I’d like to compliment the many authors, the code is generally clear even to someone like me with no php experience – and the problems arise with the newPatientData() function in patient.inc.   The function sets several integer and date values to empty strings and causes MySQL to reject the INSERT (or REPLACE INTO, in this case).  The problems arise with the columns “id”, “financial_review”, “provider_id” – I don’t know how many other columns will generate errors because I am working through them one at a time. I can do something like this in the patient.inc file:
  // next two if statements added by pkw 8/16/09
      if ($db_id==’’)  {$db_id=1;}
      if ($financial_review==’’)
{
$nextyear = mktime(0,0,0,date(“m”),date(“d”),date(“Y”)+1);
$financial_review=date(“Y/m/d”,$nextyear);
}

So this takes care of the ‘id’ field and the financial_review field, at least for the first patient, but then the REPLACE INTO fails on the provider_id field.  There is no provider table, as  far as I can tell. I entered one user in the user table as a physician but when I add a new patient there’s no place to specify a provider, and I wouldn’t want to default it to the first provider entered. Is there some other way of entering patients? I’m doing it from the “add new patient” radio button on the navigation bar.
Thanks.
- Peter

drpwayne wrote on Sunday, August 16, 2009:

My screen for adding a new patient doesn’t look anything like the screen shown in the documentation. All I see when I cilck on “New Patient” is a small frame with space for the title, first name, middle, last name, sex, date of birth, and patient id with an option to leave the ID blank.
This screencast shows what I’m doing:
http://screencast.com/t/aredkmECbp8k

drpwayne wrote on Monday, August 17, 2009:

I put these lines into the newPatientData function:
    // next four if statements added by pkw 8/16/09
     if ($db_id==’’)  {$db_id=1;}
     if ($financial_review==’’)
{
$nextyear = mktime(0,0,0,date(“m”),date(“d”),date(“Y”)+1);
$financial_review=date(“Y-m-d”,$nextyear);
}
    if ($providerID==’’) {$providerID=0;}
    if ($fitness==’’) {$fitness=0;}
// next line is the original
    $query = ("replace into patient_data set

But now, after the patient is added, I get an error in the newHistoryData function, namely, that the table “history_data” doesn’t exist. And indeed when I look at the tables in my OpenEMR installation, there is no history_data table. 

bradymiller wrote on Monday, August 17, 2009:

Hey,
Cool, looks like we got another php developer. The problems your having seem global, and it’s very odd that your missing this table (this points to a major problem which is causing all of your bizarre behavior). I’d suggest doing a full re-install(also re-download the file). To remove openemr, just delete the mysql openemr database and openemr web directory, and re-install. Hopefully your problem will completely go away. If still there, then we should look for some mysql settings that are possibly the culprit.

Also, your new patient screen is consistent with 3.0.1 (same as 3.0.0.1).
-brady

drpwayne wrote on Monday, August 17, 2009:

Thanks. I just downloaded and reinstalled OpenEMR and got the same bugs all over again. I think the version in the zip file must be the issue.  If I have time tonight I’ll try a previous version. I can’t try the XAMPP version because I don’t want to overwrite my Apache and MySQL running systems.

bradymiller wrote on Monday, August 17, 2009:

hey,

Been doing some google research on this. Found these links; may have to do with your sql-mode setting in my.ini mysql configuration file.
http://bugs.mysql.com/bug.php?id=18551
http://dev.mysql.com/doc/refman/5.0/en/server-sql-mode.html

-brady

drpwayne wrote on Monday, August 17, 2009:

Thank you, Brady!
That did it.
I had not used MySQL before version 5 and never had a reason with my own programming to change the SQL mode away from STRICT.
Now I hope to explore OpenEMR and possibly contribute to its development.
Thanks again. I’m sure I’ll come up with more questions in the next few weeks.
- Peter

bradymiller wrote on Monday, August 17, 2009:

hey,
Here’s another useful link regarding sql-mode in mysql:
http://dev.mysql.com/doc/refman/5.1/en/faqs-sql-modes.html

The default value of sql-mode for mysql is empty, however, it does seem like some windows releases are set to STRICT_TRANS_TABLES or TRADITIONAL, which will throw the errors your getting.

-brady

drpwayne wrote on Tuesday, August 18, 2009:

Brady,
It’s not just Windows – my Linux my.cnf files also include STRICT_TRANS_TABLES as part of the default installation configuration for 5.0.45.
There are comments in the installation file regarding changes to the php.ini file. I would suggest a similar comment be made regarding modifying the my.ini or my.cnf file for MySQL.

bradymiller wrote on Tuesday, August 18, 2009:

hey,
That’s too bad, since according to MySQL documentation, they set it to empty by default. For windows, did you download vanilla mysql or get a distribution? Also, which linux OS set this in their my.cnf file (mandriva mysql 5.0.45 doesn’t touch the sql-mode setting). I think this practice is very uncommon, since it would just break a lot of legacy software. If this practice is more mainstream, then I agree we should warn users in installation instructions.
-brady

drpwayne wrote on Wednesday, August 19, 2009:

well, the my.cnf file on my centos system and my windows system each have the sql mode set to strict. the my.cnf file on ubuntu doesn’t. i guess it depends on the genealogy of the system – ubuntu is, if i’m not mistaken, a debian derivative, and centos is red hat enterprise.

bradymiller wrote on Thursday, August 20, 2009:

hey,
I’ll figure out a subtle way to work this into the installation documentation. Have to find the right balance of warning the minority of pertinent users, but at the same time not throw off all the users (pain to even find the mysql config file on some systems).  Almost all windows users will use the XAMPP package, and CentOS is not widely used (redhat seems to not set sql-mode per what i could find on google), but agree need a way to warn these users.
-brady