We have just converted to using MSSQL from MySQL. We have insert statements like the following:
INSERT INTO proteus_compliance.dbo.users (username, email, displayName, title, firstname, surname, position, phone_office, phone_mobile, phone_fax, notes, site, supplier_id, admin_level, welcome_email_sent, initial_password, language, date_format, ldap, external_only, supplier_ownership) VALUES ('A_COKER', 'firstname.lastname@example.org', 'Mr Ashley Coker', 'Mr', 'Ashley', 'Coker', 'Principal Developer', '', '', '', '', '3', '0', '0', '0', 'm3xp67vs', 'English', '', '0', '', '');
This worked fine in MySQL, however in MsSQL it reports the error:
"Error converting data type varchar to numeric."
This is caused by the '' fields where the empty string is trying to be inserted into a numeric field. However, if a number is inside the string such as '3' it works fine. Is there a way to set the server or mssql connection parameter for the mssql library from PHP to allow the empty string to be used as an insert into the database without triggereing an error. Otherwise, we will have to modify a lot of our code to use the NULL keyword in its place or the appropriate default value. A global fix to acceot the empty string as a valid numeric value would be better for us.