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 '

Re: 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:


$conn = oci_connect('hr', 'hrpwd', '//localhost/XE');

$stmtarray = array(
"drop table projekte_generisch",
"create table projekte_generisch (pid varchar2(40))",
"insert into projekte_generisch (pid) values ('einepid')"
);

foreach ($stmtarray as $stmt) {
$s = oci_parse($conn, $stmt);
@oci_execute($s);
}

$sql = "Select * from projekte_generisch where pid=:data";
$cur = oci_parse($conn, $sql);
var_dump($conn, $cur, $sql);

$pid = "einepid";
var_dump(ocibindbyname($cur, ":data", $pid));
var_dump(oci_execute($cur));

print '

Re: 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":
Name Null? Type
PID NOT NULL CHAR(8)
ANFANG NOT NULL VARCHAR2(8)
ENDE VARCHAR2(8)
LAENGE NOT NULL NUMBER

And of course the data record I am searching for.

select * FROM projekte_generisch where pid='u0test'
PID ANFANG ENDE LAENGE
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: 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":
> Name Null? Type
> PID NOT NULL CHAR(8)
> ANFANG NOT NULL VARCHAR2(8)
> ENDE VARCHAR2(8)
> LAENGE NOT NULL NUMBER
>
> And of course the data record I am searching for.
>
> select * FROM projekte_generisch where pid='u0test'
> PID ANFANG ENDE LAENGE
> 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: christopher.jones@oracle.com 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: No resultset with ocibindbyname

> And here is the output of "desc projekte_generisch":
> Name Null? Type
> PID NOT NULL CHAR(8)
> ANFANG NOT NULL VARCHAR2(8)
> ENDE VARCHAR2(8)
> LAENGE NOT NULL NUMBER

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: 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: No resultset with ocibindbyname

Roberto Mansfield wrote:

>> PID NOT NULL CHAR(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: christopher.jones@oracle.com 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