Forum OpenACS Development: PgBindCmd turns the empty string into NULL
Shoot, I thought postgreSQL distinguished between the empty string and NULL, but I guess the bind variable stuff doesn't.
Here is what is in my server.log file for an update function:
Debug: PgBindCmd: sql = select product__set_attrs(:product_id, :mfg_id, :name, :short_description, :long_description, NULL) Debug: PgBindCmd: bind var: product_id = 4876 Debug: PgBindCmd: bind var: mfg_id = 4734 Debug: PgBindCmd: bind var: name = productnameeee Debug: PgBindCmd: bind var: short_description = ThisProduct One Debug: PgBindCmd: bind var: long_description = Debug: PgBindCmd: query with bind variables substituted = select product__set_attrs('4876', '4734', 'productnameeee', 'ThisProduct One', NULL, NULL)
Here, long_description is the empty string, but it gets converted to NULL.
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 ...
Yes, that is very understandable. There has to be one value that can't be set directly. It is the same problem I have in a function called obj__set_attrs, that checks if the passed in attribute is null, and if it isn't, it sets the attribute to the passed in value. This means that you can't pass in null and have it work out. So what I did was write a new function called obj__reset_attr which takes the attr name and the object_id and resets the attr to it's default value.
tcl variable = NULL and a tcl variable set to empty string equals empty string.
The problem can be handled in the application, so it really isn't a problem, at least for me. Here is what I did. I test the value of the attribute to see if it is equal to the empty string. If so, I don't use a bind variable, I just use ''. If you want to pass NULL to the database, you can either set the bind variable to the empty string, or just use an unquoted NULL in the pl or dml string. The only issue is having the database driver guess which is which. Note that in my application I use a passed in NULL (in the pg or oracle function) to determine if an attribute needs updating or not, so I still have to have a separate function to set attributes back to NULL.
BTW, I don't think tcl can tell the difference between NULL and '' either. Probably if you built an application in Oracle that relied on an attribute being either NULL or '', you would have a potential bug in the pg version.
So there's symmetry here.
Dave ... the db_null idea's a noble failure, unfortunately, because it doesn't work with either real bindvars or bindvar emulation. Or rather the only value that will work is the empty string, for any RDBMS we use, so it's pointless.
Stripping it out and all references to it is one of those very low-priority things on my personal list for us to clean up someday.
concatenation operator or certain functions.
That would be a maintenance headache.
Also using undefined Tcl vars as a flag for NULL would, in itself, be very error prone. A typo would lead to silent failure, not good.
I don't think it is a good idea or workable.