MySQL & Empty string values

Post Reply
User avatar
Quincunxian
Posts: 40
Joined: Sunday 25th June 2017 12:14am
Location: Western Australia

MySQL & Empty string values

Post by Quincunxian » Saturday 15th December 2018 11:33pm

When I try to save a record to a MySQL instance and there is an empty string for one of the fields then it returns an error saying that Null values are not allowed.
Having an empty string for this particular field is expected and only has data occasionally for any record.

The field(s) in question are set to NOT NULL but I have always understood that an empty string and Null are very different and I've searched the MySQL website for any possible fix as well as anything that Google can return ( StackOverflow articles ect....), I can't find anything to explain the behavior.

I 'have' done this with Gambas about 18 months ago so I know it 'did' work at some stage.
The MySQL Instance is on a newly built Ubuntu Server (Binary Beaver) and if I fill any empty string fields with data, the record writes fine. All other functions work fine - Just fields with empty strings.

I know I can set these fields to allow Null values to solve this issue but would very much like to understand why.

Any assistance would be greatly appreciated.
Cheers - Quin.
I code therefore I am

User avatar
Quincunxian
Posts: 40
Joined: Sunday 25th June 2017 12:14am
Location: Western Australia

Re: MySQL & Empty string values

Post by Quincunxian » Saturday 15th December 2018 11:36pm

Oh and if anyone wants to build a Ubuntu server on their own local network, I'm happy to post the build docs once i clean them up a bit.
Cheers - Quin.
I code therefore I am

didier18
Posts: 38
Joined: Monday 19th December 2016 10:08pm

Re: MySQL & Empty string values

Post by didier18 » Sunday 16th December 2018 9:06am

Hello Quincunxian

Except that a null string may not exist, I don't see any difference between a null string and an empty string.

On the other hand, an empty string ("") and a string that contains the character chr$(32) (so a string that contains a space like this (" ") is quite different).
In fact, the second string is not empty since it contains a character (even if visually you can't see anything).

Maybe'try scommandesql' immediately followed by a
If Error then
serreur = Error.Text
ierror = Error.Code
End If
could help you trap the mistake.

I think you can also use this subterfuge...
If not scommandesql then scommandesql = "E"

There are certainly many other possibilities, more elegant and more practical, but it is already a start.
I hope this will help you....

User avatar
Quincunxian
Posts: 40
Joined: Sunday 25th June 2017 12:14am
Location: Western Australia

Re: MySQL & Empty string values

Post by Quincunxian » Monday 17th December 2018 3:45am

Thanks Didier,
I'll give what you have suggested a try.
Cheers - Quin.
I code therefore I am

Post Reply