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
PHPMicrosoft SQL Server 2005Microsoft SQL Server

Avatar of undefined
Last Comment
jbclelland

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
hernst42

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
SOLUTION
Richard Quadling

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
SOLUTION
Jonathan Kelly

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
SOLUTION
Nightman

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
SOLUTION
lahousden

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
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.
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck