Re: [PHP-DB] No resultset with ocibindbyname
Roberto Mansfield wrote: >> PIDNOT NULLCHAR(8) > I believe the problem has to do with your field type for PID. When you run: > > select * FROM projekte_generisch where pid='u0test' > > Oracle either autotrims or autopads (I'm not sure which) so that > 'u0test' matches PID even though PID has a fixed 8-character length. But > when you use bind variables, this doesn't happen. So you'll need to use: Hi Roberto, Well spotted! I can see a minor inconsistency between oci_bind_array_by_name() and the much older oci_bind_by_name() call. You can pass SQLT_AFC (i.e the CHAR type) to the former but not the latter. If anyone volunteers to write some test cases I can merge a patch to OCI8 to allow: oci_bind_by_name($s, ':bv', $bv, -1, SQLT_AFC); Chris -- Christopher Jones, Oracle Email: [EMAIL PROTECTED]Tel: +1 650 506 8630 Blog: http://blogs.oracle.com/opal/ Free PHP Book: http://tinyurl.com/f8jad -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] No resultset with ocibindbyname
Hi, > select * FROM projekte_generisch where trim(pid)=:pid Thanks Roberto! This solution with trim() works like a charm! Cheers, Manuel -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] No resultset with ocibindbyname
> And here is the output of "desc projekte_generisch": > Name Null? Type > PID NOT NULLCHAR(8) > ANFANGNOT NULLVARCHAR2(8) > ENDE VARCHAR2(8) > LAENGENOT NULLNUMBER I believe the problem has to do with your field type for PID. When you run: select * FROM projekte_generisch where pid='u0test' Oracle either autotrims or autopads (I'm not sure which) so that 'u0test' matches PID even though PID has a fixed 8-character length. But when you use bind variables, this doesn't happen. So you'll need to use: select * FROM projekte_generisch where trim(pid)=:pid Or you can change the field definition on PID to varchar2. I tested this on one of our tables which has a char(4) primary key to verify all this: select count(*) from subject_area where subject_area = 'EE'; returns 1. But using a bind variable: declare v_sa varchar2(10); v_count number; begin v_sa := 'EE'; select count(*) into v_count from subject_area where subject_area = v_sa; dbms_output.put_line(v_count); end; returns a 0. -Roberto -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] No resultset with ocibindbyname
Manuel Schölling wrote: Hi Christopher, thanks for caring about my problem. ;) I couldn't reproduce your problem. What does your table look like? What version of PHP & OCI8? What version of Oracle? I using PHP 5.2.5 on a Linux 2.6.9 machine. The version of OCI8 is 1.2.4 (Revision 1.269.2.16.2.38, Oracle Instant Client Version 10.2). The output of "select * from v$version" is: Oracle8i Enterprise Edition Release 8.1.7.0.0 - Production PL/SQL Release 8.1.7.0.0 - Production CORE 8.1.7.0.0 Production TNS for IBM/AIX RISC System/6000: Version 8.1.7.0.0 - Developmen NLSRTL Version 3.4.1.0.0 - Production And here is the output of "desc projekte_generisch": NameNull? Type PID NOT NULLCHAR(8) ANFANG NOT NULLVARCHAR2(8) ENDEVARCHAR2(8) LAENGE NOT NULLNUMBER And of course the data record I am searching for. select * FROM projekte_generisch where pid='u0test' PID ANFANG ENDELAENGE u0test utest 8 Hope this helps diagnosting the error. Do other queries with binds work? Is PHP using the correct NLS language configuration? Chris -- Christopher Jones, Oracle Email: [EMAIL PROTECTED]Tel: +1 650 506 8630 Blog: http://blogs.oracle.com/opal/ Free PHP Book: http://tinyurl.com/f8jad -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] No resultset with ocibindbyname
Hi Christopher, thanks for caring about my problem. ;) > I couldn't reproduce your problem. What does your table look like? > What version of PHP & OCI8? What version of Oracle? I using PHP 5.2.5 on a Linux 2.6.9 machine. The version of OCI8 is 1.2.4 (Revision 1.269.2.16.2.38, Oracle Instant Client Version 10.2). The output of "select * from v$version" is: Oracle8i Enterprise Edition Release 8.1.7.0.0 - Production PL/SQL Release 8.1.7.0.0 - Production CORE 8.1.7.0.0 Production TNS for IBM/AIX RISC System/6000: Version 8.1.7.0.0 - Developmen NLSRTL Version 3.4.1.0.0 - Production And here is the output of "desc projekte_generisch": NameNull? Type PID NOT NULLCHAR(8) ANFANG NOT NULLVARCHAR2(8) ENDEVARCHAR2(8) LAENGE NOT NULLNUMBER And of course the data record I am searching for. select * FROM projekte_generisch where pid='u0test' PID ANFANG ENDELAENGE u0test utest 8 Hope this helps diagnosting the error. Cheers, Manuel -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] No resultset with ocibindbyname
Manuel Schölling wrote:
Hi guys,
sorry for spamming your mailing list.
I have a strange problem with ocibindbyname():
I use this simple code to start an SQL query:
But this query doesn't give me any data record (no error; empty resultset).
I couldn't reproduce your problem. What does your table look like?
What version of PHP & OCI8? What version of Oracle?
I tried this script:
';
while ($row = oci_fetch_array($cur, OCI_RETURN_NULLS)) {
print '';
foreach ($row as $item) {
print ''.($item?htmlentities($item):' ').'';
}
print '';
}
print ''
?>
The output is:
$ ~/php/bin/php t1.php
resource(5) of type (oci8 connection)
resource(9) of type (oci8 statement)
string(48) "Select * from projekte_generisch where pid=:data"
bool(true)
bool(true)
einepideinepid
(The value is repeated because the array contains numerically and
associatively indexed values.)
Chris
--
Christopher Jones, Oracle
Email: [EMAIL PROTECTED]Tel: +1 650 506 8630
Blog: http://blogs.oracle.com/opal/ Free PHP Book: http://tinyurl.com/f8jad
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] No resultset with ocibindbyname
Hi guys,
sorry for spamming your mailing list.
I have a strange problem with ocibindbyname():
I use this simple code to start an SQL query:
$sql = "Select * from projekte_generisch where pid=:data";
$conn = oci_connect("secret", "secret", "secret");
$cur = oci_parse($conn, $sql);
my_dump($conn, $cur, $sql);
$pid = "einepid";
my_dump(ocibindbyname($cur, ":data", $pid));
my_dump(oci_execute($cur));
print '';
while ($row = oci_fetch_array($cur, OCI_RETURN_NULLS)) {
print '';
foreach ($row as $item) {
print ''.($item?htmlentities($item):' ').'';
}
print '';
}
print '';
But this query doesn't give me any data record (no error; empty resultset).
When I use
$sql = "Select * from projekte_generisch where pid='einepid'";
php returns the data record I'm searching for.
What could be the reason? Is there any bug in ocibindbyname or am I
using it the wrong way.
When I use adodb (http://adodb.sf.net) I get the same error:
Using :variable in my query doesn't work but putting the string directly
into the query string it works fine.
What the f*** am I doing wrong?
Any hints are appreciated.
Cheers,
Manuel
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
