#14013 [Com]: ocibindbyname strips trailing spaces

ID: 14013 Comment by: m dot ford at lmu dot ac dot uk Reported By: az at i7 dot com dot au Status: Closed Bug Type: OCI8 related Operating System: Linux 2.2, Solaris 2.6 PHP Version: 4.0.6 New Comment: Thanks, Rasmus -- very informative reference. I see that if you specify your bind variable as external datatype SQLT_CHR, trailing spaces are indeed stripped, so this is presumably what PHP is using at the moment. However, there are other SQLT_ types where this stripping is not performed, and I wonder if there is any chance that one of these could be used instead. In particular, the external datatype SQLT_STR utilizes a C-style null-terminated string -- I don't know enough about PHP internals to know if this would be easy to manage or not, or if there would be a bad performance hit, but as we seem to have a fairly active OCI maintenance team at the moment, perhaps someone could look into it? Cheers! Mike Previous Comments: ------------------------------------------------------------------------ [2004-02-02 04:00:50] rasmus@php.net Guys, I realize the explanation for the closure wasn't very good, but it is correct. For some reason the underlying Oracle OCI call strips trailing spaces on a bind but not on a regular insert/update on a varchar2 field. You would have to ask Oracle to explain why. Read the VARCHAR2 section of this URL: http://sales.esicom.com/sales/oracle/appdev.816/a76975/oci03typ.htm ------------------------------------------------------------------------ [2003-12-09 12:38:30] russ at iris dot washington dot edu I also wonder why this is closed. Just encountered a simpler case: I cannot select using this field: LOCATION NOT NULL VARCHAR2(2) those records which have ' ' (two spaces) for that LOCATION's value. Non-space values work fine. Interactive SQLPLUS confirms those rows do exist. Oracle 8 PHP 4.3.1 Solaris SunOS dmc 5.7 sun4u sparc SUNW,Ultra-Enterprise ------------------------------------------------------------------------ [2003-08-18 11:52:36] m dot ford at lmu dot ac dot uk My mistake -- it's Closed, not Open ("green means Closed, green means Closed!"), but, to my mind, still without satisfactory explanation. Cheers! ------------------------------------------------------------------------ [2003-08-18 11:49:55] m dot ford at lmu dot ac dot uk There still doesn't seem to be a satisfactory explanation posted here, and it's still Open, so could someone please address the salient issue: If the database column is defined as VARCHAR2, why does this strip trailing blanks on the inserted value: $text = " this line has spaces "; $st = ociparse($db, "insert into test values (:text)"); ocibindbyname($st, ":text", &$text, 2000); ociexecute($st); whilst this does not: $text = " this line has spaces "; $st = ociparse($db, "insert into test values '$text')"); ociexecute($st); ------------------------------------------------------------------------ [2003-01-31 09:36:03] jens dot reibiger at rp dot vaw dot com It seems, that I have the same problem using PHP 4.3.0 on Apache 1.3.22 with Oracle 8.1.7: When I use a OciBindByName, the string is trimed at the end. This small program uses the DUAL from Oracle just to return the input: $conn = OciLogon ("x","y","z"); $val = " X X "; // last letter is a " " (blank) // direct way without a bind variable $stm1 = OciParse($conn, "select '".$val."' from dual"); OciExecute($stm1); OciFetch($stm1); echo "<b>", OciResult($stm1, 1), "</b><br>\n"; // now using a bind variable: $stm2 = OciParse($conn, "select :input from dual"); OciBindByName($stm2, ":input", &$val, 10); OciExecute($stm2); OciFetch($stm2); echo "<b>", OciResult($stm2, 1), "</b><br>\n"; OciLogoff($conn); The output is: <b> X X </b><br> <b> X X</b><br> But I want to get the same output for the direct way and when I use a bind variable. Thank you for any idea how to get the string with tailing spaces right into Oracle using a bind variable. Best wishes, Jens ------------------------------------------------------------------------ The remainder of the comments for this report are too long. To view the rest of the comments, please view the bug report online at http://bugs.php.net/14013 -- Edit this bug report at http://bugs.php.net/?id=14013&edit=1