0

What is the correct way to execute this code in Yii2. I get oci_new_cursor() expects parameter 1 to be resource, object given. 

// get DB instance
$connection = Yii::$app->db;
// set cursor
$refcur = oci_new_cursor($connection);

$q = "BEGIN :RC := REPORT_CARDS.GET_DATA(:I_YEAR); END;";
...
Jonnny
  • 4,939
  • 11
  • 63
  • 93
  • Disclaimer: I am not really familiar with Oracle DB or PHP OCI8. As far as I understand, Yii internally uses PDO, which in turn does not mingle all that well with OCI8. So, are you actually trying to do something for which the capacities provided by Yii are not sufficient? Will something like `\Yii::$app->db->createCommand($q)->execute();` work for you, here? – Thernys May 05 '16 at 20:25
  • @Thernys I don't think it will. createCommand wants an SQL statement. I am trying to create a new oci cursor, that takes the argument on the DB instance – Jonnny May 06 '16 at 11:02
  • I was mainly trying to confirm you aren't falling victim to the [XY problem](http://meta.stackexchange.com/questions/66377/what-is-the-xy-problem). Yii uses PDO. You can get the PDO instance from `\Yii::$app->db->pdo`, but I doubt you can further get access to whatever connection resource underlies the PDO object. The XY problem part is, then, do you really need a direct resource, and to use OCI8, or can you work through the PDO interface to achieve the same end goal. The last option is of course to work outside of Yii with raw PHP, or start overriding stuff starting from `yii\db\Connection`. – Thernys May 06 '16 at 12:39
  • @Thernys I am thinking that even if I can use `$refcur = oci_new_cursor(Yii::$app->db->pdo);` to create the cursor, which doesn't seem to work. I don't think Yii supports oci_bind_by_name(). Which I'd need to use to bind the cursor. Which is a shame :-( – Jonnny May 06 '16 at 12:43

1 Answers1

1

There are some important facts to keep in mind:

  1. Yii uses PDO
  2. PDO and OCI8 are completely different extensions and you cannot mix their use
  3. the PDO driver for Oracle databases (PDO_OCI) has limitations; for instance it seems that cursors aren't really supported and its use is in general not really recommended

You can get the PDO instance used by Yii with \Yii::$app->db->pdo, but as per point 2 above, this does not help you with the OCI8 functions you are trying to use in your example. As per point 3, it further does not seem a great idea to commit resources to learning to use PDO just to replace OCI8.

Still, if you want to give the 'Yii way' another shot for those problems that it can still solve, you can try and see how far you get with yii\db\Command. The PDO counterpart for oci_bind_by_name() you refer to in your comment is PDOStatement::bindParam which is basically proxied by yii\db\Command::bindParam. So, a very basic use example for a custom-built SQL query would be something like

// $var1 and $var2 somewhere
$command = \Yii::$app->db->createCommand('some query with :bound :values');
$command->bindParam(':bound', $var1, \PDO::PARAM_STR);
$command->bindParam(':values', $var2, \PDO::PARAM_STR || \PDO::PARAM_INPUT_OUTPUT); 
$command->execute();

I can't really test with your specific example, and based on what I've read I can't guarantee success if you try for yourself, but I wish you luck with the exploration. If it falls short of success, then I don't see an easy way around it; you'll just have to use a separate connection of your own, manually initialized with oci_connect or whatever, and then operate as usual through OCI8.

Community
  • 1
  • 1
Thernys
  • 723
  • 4
  • 13