BindValue and Oracle Question

Yii 1.1 General Discussion
1

I’m having a problem with bindvalue and Oracle. I use the same code as for mysql and it returns a value of 1. But when I use Oracle it returns a value of 0. I’m not getting any error messages.

Here is the code. This is in Chapter 8 of Agile Web Application Development with Yii1.1 and PHP5

[indent][/indent]public function isUserInProject($user)

{


	$sql = "SELECT USER_ID FROM TBL_PROJECT_USER_ASSIGNMENT WHERE PROJECT_ID = :projectId AND USER_ID = :userId";


            


	$command = Yii::app()->db->createCommand($sql);


	$command->bindValue(":projectId", $this->ID, PDO::PARAM_INT);


	$command->bindValue(":userId", $user->ID, PDO::PARAM_INT);


	return $command->execute()==1 ? true : false;


}

I’m at my wits end. I am so close to converting the Trackstar example into Oracle and I can’t figure out this piece.

2

First, this is Yii 1.0 thread, and you’re talking about 1.1.

Second, you’re trying to execute() SELECT statement, so the result will be the number of rows affected.

I think you should change execute() to queryColumn() (or similar).

PS. PDO’s rowCount() returns the number of rows affected by DELETE, INSERT, or UPDATE statements. I wonder why you have it working in MySQL.

3

I’m converting the Trackstar Application to Oracle and in Chapter 8 I’m having a problem with bindvalues. I’ve stepped through Trackstar in mysql and it will return 1 for true but in the Oracle version it returns 0 for false. The tables have the same information and the bind values look fine but it is not working.

Here is the code:

public function isUserInProject($user) 


{


	$sql = "SELECT USER_ID FROM TBL_PROJECT_USER_ASSIGNMENT WHERE PROJECT_ID = :projectId AND USER_ID = :userId";


            //$sql = "SELECT USER_ID FROM TBL_PROJECT_USER_ASSIGNMENT WHERE PROJECT_ID = :projectId";


	$command = Yii::app()->db->createCommand($sql);


	$command->bindValue(":projectId", $this->ID, PDO::PARAM_INT);


	$command->bindValue(":userId", $user->ID, PDO::PARAM_INT);


	return $command->execute()==1 ? true : false;


}

Maybe I should try a different approach to getting values from Oracle.

Thanks

4

Hi ladymtnbikrdr,

IIRC, the parameter binding must be done in one shot.

Maybe this?




$command->bindValues(array(":projectId" => $this->ID, ":userId" => $user->ID));

[EDIT]

I’m sorry. I didn’t remember right. :(

bindValue can be used multiple times. Forget the suggestion above, please.

5

[color="#008000"]/* notice — the first 2 posts were originally posted in Yii 1.0.x Discussions, but have been moved to the proper forum and merged with the duplicated posts */[/color]

6

Thank you so much SoftArk. I changed the code to the following and it worked like a charm.

            $rows=Yii::app()->db->createCommand()


		->select()


		->from('TBL_PROJECT_USER_ASSIGNMENT')


		->where('PROJECT_ID=:projectId AND USER_ID=:userId', array(


			':projectId'=>$this->ID,


			':userId'=>$user->ID


		))


		->queryAll();





	return count($rows)==1 ? true : false;

I’m glad I posted in version 1.0 because you are the only one that responded. I did check the mysql program and the original code does work, but doesn’t work for Oracle.

Thank you again. Very much appreciated. This has been quite a big learning curve for me and I appreciate all the help I have been given on this forum. I’m new to Frameworks, PHP, and object oriented programming but I’m really starting to like it.

7

Sorry, last post was to thank Orey.

8

This is not very good.

If somehow TBL_PROJECT_USER_ASSIGNMENT would contain two rows matching the criteria, your code will return false.

Try this:


return (Yii::app()->db->createCommand()

    ->select()

    ->from('TBL_PROJECT_USER_ASSIGNMENT')

    ->where('PROJECT_ID=:projectId AND USER_ID=:userId', array(

    ':projectId'=>$this->ID,

    ':userId'=>$user->ID

))->queryRow() !== false)