Empty string inserted into varchar field fills with blanks

General discussion on Zend Server for IBM System i
Post Reply
erich_hieden
Posts: 393
Joined: Tue Jul 07, 2009 9:01 am

Empty string inserted into varchar field fills with blanks

Post by erich_hieden » Thu Oct 06, 2011 12:27 pm

Using ibm_db2 extension the following happens. I just post it here as well, as nobody seems to be interested in this problem at PECL (Bug #24354)


Description:
------------
When trying to insert an empty string into a varchar field, the field is
filled with blanks, i.e. the length isn't set to zero. This happens
regardless of allowing null for this field or not.

Reproduce code:
---------------
Assuming there's the table 'blank_tbl' in library 'blank' containing of
a single varchar field with maximum length 10.

Code: Select all

A          R PHPS
A            BLANK_FLD     10          VARLEN

Code: Select all

<?php
$dbname = '*';
$username = '*';
$password = '*';

if (($database = db2_connect($dbname, $username, $password)) === false)
die(db2_conn_errormsg());

$sql = <<<EOT
INSERT INTO blank.blank_tbl(blank_fld)
VALUES (?)
EOT;

$data = array('');

if (($stmt = db2_prepare($database, $sql)) === false)
print_r(db2_stmt_errormsg());

if ($stmt && db2_execute($stmt, $data) === false)
print_r(db2_stmt_errormsg($stmt));

db2_close($database);
?>
Expected result:
----------------
A row should be inserted into the table, with the single field beeing an
empty string.

Actual result:
--------------
The row is inserted, but the string consists of blanks, filled up to the
maximum length.

Taking a look at the data directly (aka PDM) shows these hex contents:
004444444444
0A0000000000

chris_hird
Posts: 171
Joined: Fri Apr 10, 2009 12:41 am
Location: Toronto
Contact:

Re: Empty string inserted into varchar field fills with blan

Post by chris_hird » Thu Oct 06, 2011 6:32 pm

Erich

I see you are using assignment to populate the values, the data you are passing is a single blank field so it is assigning a blank to the field. As the fields are populated with blanks anyhow you are just replacing one of them with a blank. If you passed in " " it should be a NULL terminated string? I think this is what I would expect?

Chris...
Shield Advanced Solutions Ltd
Home of JobQGenie and the Receiver Apply Program
http://www.shield.on.ca/Blog

erich_hieden
Posts: 393
Joined: Tue Jul 07, 2009 9:01 am

Re: Empty string inserted into varchar field fills with blan

Post by erich_hieden » Fri Oct 07, 2011 9:38 am

Well Chris, thanks for your response, but unfortunately this is not true.

I'm not inserting a single blank field, but a completely empty string. If I would insert a single blank, it would work as expected, without any NULL termination needed. Every other string I can think of works like a charm, but not an empty one.

Best regards

chris_hird
Posts: 171
Joined: Fri Apr 10, 2009 12:41 am
Location: Toronto
Contact:

Re: Empty string inserted into varchar field fills with blan

Post by chris_hird » Fri Oct 07, 2011 12:49 pm

erich,

I have not tried it so I am not sure, I would have though that "$data array(' ');" would provide an array with its elements set to blanks. $data = " "; is a NULL terminated array of 10 blanks? That is what I would do if I was trying to carry out the same operation in C.

Chris...
Shield Advanced Solutions Ltd
Home of JobQGenie and the Receiver Apply Program
http://www.shield.on.ca/Blog

chris_hird
Posts: 171
Joined: Fri Apr 10, 2009 12:41 am
Location: Toronto
Contact:

Re: Empty string inserted into varchar field fills with blan

Post by chris_hird » Fri Oct 07, 2011 12:51 pm

The above post looks wrong because the string of 10 characters was truncated by BB. It should be a pair of " with 10 spaces in between.

Chris..
Shield Advanced Solutions Ltd
Home of JobQGenie and the Receiver Apply Program
http://www.shield.on.ca/Blog

erich_hieden
Posts: 393
Joined: Tue Jul 07, 2009 9:01 am

Re: Empty string inserted into varchar field fills with blan

Post by erich_hieden » Fri Oct 07, 2011 3:05 pm

chris_hird wrote:erich,

I have not tried it so I am not sure, I would have though that "$data array(' ');" would provide an array with its elements set to blanks. $data = " "; is a NULL terminated array of 10 blanks? That is what I would do if I was trying to carry out the same operation in C.

Chris...
Hell no Chris, "$data = array('');" gives you an array with the sole element being an empty string. (see php.net)

erich_hieden
Posts: 393
Joined: Tue Jul 07, 2009 9:01 am

Re: Empty string inserted into varchar field fills with blan

Post by erich_hieden » Fri Dec 16, 2011 7:47 am

News on this (Bug #24354):
bestgen at us dot ibm dot com wrote:Martin, This is a bug in the prepare/execute path. A fix will be provided through normal channels. As a circumvention the execdirect path does work with zero length strings.
Thanks Rob!

Post Reply