Discussions
Categories
- 197K All Categories
- 2.5K Data
- 546 Big Data Appliance
- 1.9K Data Science
- 450.8K Databases
- 221.9K General Database Discussions
- 3.8K Java and JavaScript in the Database
- 31 Multilingual Engine
- 552 MySQL Community Space
- 479 NoSQL Database
- 7.9K Oracle Database Express Edition (XE)
- 3.1K ORDS, SODA & JSON in the Database
- 556 SQLcl
- 4K SQL Developer Data Modeler
- 187.2K SQL & PL/SQL
- 21.4K SQL Developer
- 296.4K Development
- 17 Developer Projects
- 139 Programming Languages
- 293.1K Development Tools
- 110 DevOps
- 3.1K QA/Testing
- 646.1K Java
- 28 Java Learning Subscription
- 37K Database Connectivity
- 159 Java Community Process
- 105 Java 25
- 22.1K Java APIs
- 138.2K Java Development Tools
- 165.3K Java EE (Java Enterprise Edition)
- 19 Java Essentials
- 162 Java 8 Questions
- 86K Java Programming
- 81 Java Puzzle Ball
- 65.1K New To Java
- 1.7K Training / Learning / Certification
- 13.8K Java HotSpot Virtual Machine
- 94.3K Java SE
- 13.8K Java Security
- 205 Java User Groups
- 24 JavaScript - Nashorn
- Programs
- 471 LiveLabs
- 39 Workshops
- 10.2K Software
- 6.7K Berkeley DB Family
- 3.5K JHeadstart
- 5.7K Other Languages
- 2.3K Chinese
- 175 Deutsche Oracle Community
- 1.1K Español
- 1.9K Japanese
- 233 Portuguese
OCI keeps retrieving only 1 record when I see 3 in the database...
The SQL I'm trying to use in my PHP script is as follows:
SELECT my_id FROM my_table WHERE my_id IN ('id1', 'id2', 'id3');
As you can see, it's nothing too complex.
Here's my entire PHP with the above SQL in it:
$search_string = array('id1', 'id2', 'id3'); $placeholders = array(); $bind = array(); $conn = oci_connect('admin', '1234', 'testdb.mydomain.com/TEST'); foreach($search_string as $index => $filter) { $placeholders[] = ':filter'.$index; $bind[':filter'.$index] = $filter; } $stid = ' SELECT my_id FROM my_table WHERE my_id IN ('.implode(',',$placeholders).') '; $stid = oci_parse($conn,$stid); foreach($bind as $k=>$v){ oci_bind_by_name($stid, $k, $v); } $r = oci_execute($stid); oci_fetch_all($stid, $res, 0, -1, OCI_FETCHSTATEMENT_BY_ROW); print '<pre>'; var_dump($res); print '</pre>'; oci_free_statement($stid); oci_close($conn)
Again, nothing too complex. My problem is that the above PHP retrieves only 1 record when 3 are apparent in the database using the *EXACT* same SQL.
What might be causing this issue? Is there maybe something associated with Oracle configurations that prevent the entire result from being obtained in the PHP execution?
Any insights would be appreciated. Thanks in advance.
Best Answer
-
This can be marked as solved for all intents and purposes... I went instead with PDO. I think the OCI stuff is a little buggy... (Using PDO, I can make my query and execute the IN logic without issues.)
Moderators (or whoever): please mark this as solved if you want.
Answers
-
This can be marked as solved for all intents and purposes... I went instead with PDO. I think the OCI stuff is a little buggy... (Using PDO, I can make my query and execute the IN logic without issues.)
Moderators (or whoever): please mark this as solved if you want.
-
OCI8 is better than PDO_OCI in many respects.
At a wild guess, I'd say you hadn't committed your data when you were trying OCI8.
-
At a more detailed look, check out example #3 in PHP: oci_bind_by_name - Manual :
foreach ($ba as $key => $val) {
// oci_bind_by_name($stid, $key, $val) does not work
// because it binds each placeholder to the same location: $val
// instead use the actual location of the data: $ba[$key]
oci_bind_by_name($stid, $key, $ba[$key]);
}