Discussions
Categories
- 196.9K All Categories
- 2.2K Data
- 239 Big Data Appliance
- 1.9K Data Science
- 450.4K Databases
- 221.7K General Database Discussions
- 3.8K Java and JavaScript in the Database
- 31 Multilingual Engine
- 550 MySQL Community Space
- 478 NoSQL Database
- 7.9K Oracle Database Express Edition (XE)
- 3K ORDS, SODA & JSON in the Database
- 546 SQLcl
- 4K SQL Developer Data Modeler
- 187.1K SQL & PL/SQL
- 21.3K SQL Developer
- 295.9K Development
- 17 Developer Projects
- 138 Programming Languages
- 292.6K Development Tools
- 107 DevOps
- 3.1K QA/Testing
- 646K Java
- 28 Java Learning Subscription
- 37K Database Connectivity
- 155 Java Community Process
- 105 Java 25
- 22.1K Java APIs
- 138.1K Java Development Tools
- 165.3K Java EE (Java Enterprise Edition)
- 18 Java Essentials
- 160 Java 8 Questions
- 86K Java Programming
- 80 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
- 204 Java User Groups
- 24 JavaScript - Nashorn
- Programs
- 443 LiveLabs
- 38 Workshops
- 10.2K Software
- 6.7K Berkeley DB Family
- 3.5K JHeadstart
- 5.7K Other Languages
- 2.3K Chinese
- 171 Deutsche Oracle Community
- 1.1K Español
- 1.9K Japanese
- 232 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]);
}