Forum OpenACS Development: How to prevent empty string -> NULL conversion


I haven't worked with OpenACS for a while, so perhaps my memory isn't entirely up to date. I'm trying to execute a plpgsql procedure, and I want to supply an empty string as parameter, i.e.

set variable ""
db_exec_plsql my_function {SELECT my_function(:variable)}

This empty string is converted to NULL by db_bind_var_substition, which is not what I want.

How do I supply the an empty string to the function?

Posted by Don Baccus on
Probably the easiest thing to do is to have your function substitute the empty string for null ...

This behavior is mimicking an Oracle-ism, which we do in order to make it possible to write portable code that works with both RDBMS's. Also, without these semantics you couldn't pass NULL in a bindvar.

Another possibility is to do '$variable' rather than :variable. If this variable comes from a form, though, be wary of "SQL smuggling" (avoiding this source of hackability is the reason we use bindvars in the first place).