Forum OpenACS Development: Response to PgBindCmd turns the empty string into NULL

Posted by Don Baccus on
Well ... it makes it compatible with Oracle, for one thing.  That wasn't  the only reason, though.

If we don't turn the empty string into NULL, what will we turn into NULL?  Remember you're not supplying the single-quote hints that differentiate a string from NULL in SQL proper.  How would the driver know that you meant the value NULL and not the string NULL in this case?

In other words you do SET FOO "I am a string" ,not SET FOO "'I am a string'".  Supporting the latter form would allow us to use SET FOO "NULL" for null and SET FOO "'NULL'" for 'NULL' but would be incompatible with Oracle and incompatible with the notion of bind variables (who knows, PG might get them some day!)

The driver surrounds the values with quotes all the time (SQL specifies that '123' is equivalent to 123, etc).

Since these are unquoted strings it is important to remember that you don''t have to do what I just did (use two single apostrophes).  And the driver escapes backslashes, too, to get around PG's non-standard use of them.

Hopefully this makes some sense ...