Forum OpenACS Development: PgBindCmd turns the empty string into NULL

1: PgBindCmd turns the empty string into NULL

Posted by Tom Jackson on 02/07/02 01:55 AM

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.

2: Response to PgBindCmd turns the empty string into NULL (response to 1)

Posted by Don Baccus on 02/07/02 02:55 AM

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 ...

3: Response to PgBindCmd turns the empty string into NULL (response to 1)

Posted by Tom Jackson on 02/07/02 05:45 AM

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.

4: Response to PgBindCmd turns the empty string into NULL (response to 1)

Posted by David Walker on 02/07/02 06:10 PM

I think a logical solution (probably not a realistic one though) is that an unset
tcl variable = NULL and a tcl variable set to empty string equals empty string.

5: Response to PgBindCmd turns the empty string into NULL (response to 1)

Posted by Don Baccus on 02/07/02 06:13 PM

That's not how Oracle bind vars work. Or, even more fundamentally, how '' works in Oracle (it's NULL in DML statements). Remember ... we're trying to maintain common source here. Supporting different semantics in the PG and Oracle versions of the db api would be hell ...

6: Response to PgBindCmd turns the empty string into NULL (response to 1)

Posted by Dave Bauer on 02/07/02 06:25 PM

Isn't this what the db_null proc was supposed to be for? Can we somehow use that to work around this? Or does it have to be done in the database driver?

7: Response to PgBindCmd turns the empty string into NULL (response to 1)

Posted by Tom Jackson on 02/07/02 07:07 PM

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.

8: Response to PgBindCmd turns the empty string into NULL (response to 1)

Posted by Don Baccus on 02/07/02 08:14 PM

I meant to make this last point myself, Tom, and forgot. NULL and the empty string are both passed back as empty Tcl strings in the ns_set built by the drivers (ours and the sybase one written by the AOLserver team - actually, the PG driver was originally written by them, too, though bindvar emulation was added by Dan Wickstrom and me).

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.

9: Response to PgBindCmd turns the empty string into NULL (response to 1)

Posted by David Walker on 02/07/02 08:23 PM

I'm concerned these NULLs will bite us later when trying to use the
concatenation operator or certain functions.

10: Response to PgBindCmd turns the empty string into NULL (response to 1)

Posted by Don Baccus on 02/07/02 10:02 PM

Well ... yes, they will. But this is how Oracle works and we can't change that, really, so it's irrelevant in a sense. The empty string is NULL in Oracle so the suggestion to use undefined Tcl vars as a flag and the empty string as the empty string would introduce differences in behavior between the Oracle and PG version.

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.

11: Response to PgBindCmd turns the empty string into NULL (response to 1)

Posted by David Walker on 02/07/02 10:12 PM

I should have been more clear about that. I meant that it is "logically right".
I don't think it is a good idea or workable.