Link to home
Create AccountLog in
Avatar of jbclelland
jbclelland

asked on

MSSQL Server from PHP allowing empty string as a valid numeric insert value

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', 'ashley.coker@infogov.co.uk', '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.

Thanks
ASKER CERTIFIED SOLUTION
Avatar of hernst42
hernst42
Flag of Germany image

Link to home
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
See answer
SOLUTION
Link to home
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
SOLUTION
Avatar of Jonathan Kelly
Jonathan Kelly
Flag of Ireland image

Link to home
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
SOLUTION
Link to home
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
SOLUTION
Link to home
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
Avatar of jbclelland
jbclelland

ASKER

Unfortunately I think the only way forward for me is to change the code so that the quotes are replaced by the correct value when I build the SQL string in PHP.

I was really looking for a global solution but it appears that one does not exist.