sybperl-l Archive

From: Michael Peppler <mpeppler at peppler dot org>
Subject: Re: DBD::Sybase - Inserting null character
Date: Jul 8 2004 12:30PM

On Wed, 2004-07-07 at 17:12, Peter Sinnott wrote:
> On Wed, Jul 07, 2004 at 03:51:56PM +0200, Michael Peppler wrote:
> > On Wed, 2004-07-07 at 15:40, Peter Sinnott wrote:
> > > Hi all,
> > >      is it possible to quote null characters in order to allow them to be
> > >      inserted via a stored procedure(the field is varchar)?
> > 
> > Do you mean include NULL bytes in a varchar() column?
> >
> 
> That is indeed what I mean. I'm not sure what else to call them but it
> does cause some confusion and makes searching for answers difficult.
>  
> > I'm not sure (haven't tried it) but I suspect that there are various
> > problems with this.
> > 
> > Could you post a minimal example of what you are trying to achieve?
> >
> 
> I have a table that I need to update some rows in. The update procedure
> available requires that I pass all the fields of the row into it. Some
> of the existing rows have null characters. I'd like to preserve 
> null characters in the columns I'm not changing.

OK - I looked at the code again.

The first thing is that things will definitely not work when using
"normal" SQL (and $dbh->quote), because of using CS_NULLTERM as the
length specified when sending a SQL statement to the server.

When using placeholders the issue is slightly different, although right
now CHAR strings are *also* passed with the CS_NULLTERM indicator (hence
your problems!)

In dbdimp.c, function _dbd_rebind_ph() we have:

	  default:
	    phs->datafmt.datatype = CS_CHAR_TYPE;
	    value = phs->sv_buf;
	    value_len = CS_NULLTERM; /*Allow embedded NUL bytes in strings?*/
	    /* PR/446: should an empty string cause a NULL, or not? */
	    if(*(char*)value == 0) {
		if(imp_dbh->bindEmptyStringNull) {
		    value = NULL;
		    value_len = CS_UNUSED;
		} else {
		    value = " ";
		}
	    }
	    break;

Try commenting out that "value_len = CS_NULLTERM", rebuild, and then use
the $sth = $dbh->prepare("exec proc_name ?, ?") form to run your proc.
The NUL bytes *should* now get sent to the server.

Michael
-- 
Michael Peppler                              Data Migrations, Inc.
mpeppler@peppler.org                       http://www.peppler.org/
Sybase T-SQL/OpenClient/OpenServer/C/Perl developer available for short
or long term contract positions - http://www.peppler.org/resume.html