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

1: How to prevent empty string -> NULL conversion

Posted by Daniël Mantione on 07/18/05 04:24 PM

Hi,

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?

2: Re: How to prevent empty string -> NULL conversion (response to 1)

Posted by Don Baccus on 07/19/05 01:01 AM

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