[Solved] PDO vs. DAO: the same SQL query, different results

Yii 1.1 General Discussion
1

Hi there,

Either I’m missing something or doing something wrong or I get different results while querying the same SQL statement through PDO and DAO.

First part of code (PDO):


$connection = app()->db;

$dbh = new PDO($connection->connectionString, $connection->username, $connection->password);

$sql = "SELECT NAZW FROM PACJ WHERE rowid < 1000";

$dbh->query($sql);

Result (generated by my app):


Done selecting 1000 records (one column) using PDO! Execution time: 0,00344896317 seconds.

Second part of code (DAO):


$connection = app()->db;

$sql = "SELECT * FROM PACJ WHERE rowid < 1000";

$command = $connection->createCommand($sql);

$command->query();

Result: error "General error: 932 OCIStmtExecute: ORA-00932" saying about incorrect data types, expected ROWID, received NUMBER.

Got completely lost with this. Can anyone help me? Thanks!

2

Try exactly the same query?

3

Sorry, it was only my mistake in copying wrong parts of testing code.

I tested it using exactly the same query and both cases (selecting either one or all columns with *). In both cases I got the same result. PDO passes, DAO not.

Oracle (not tested on other RDBMS) doesn’t seems to be liking “WHERE rowid < 1000” part. When this one is removed, execution of query using both PDO and DAO passes.

4

maybe "rowid" is keyword?

and must be quoted?

5

DAO uses PDOStatement::execute() to execute the query. That’s the only difference between PDO and DAO.

6

Try adding rowid into select?

Also, you can use Yii::app()->db->pdoInstance->query(…) to work around it.

7

Unfortunately, not! And to be honest, I would be surprised, if this would change anything. IMHO if something works in PDO but not in DAO then this is DAO-related problem, lying somewhere in core code, not SQL-related one.

Tried both:


$sql = "SELECT * FROM WWW_USERS WHERE 'ROWID' < 300";

and:


$sql = 'SELECT * FROM WWW_USERS WHERE "ROWID" < 300';

No luck. The only difference is that with first notation I got different error. This time this is "1722 OCIStmtExecute: ORA-01722: incorrect number", while in second case I got the same errro (932) as written in first post in this topic.

It is also worth noting, that all three options (without quoting ROWID and with quoting it both with single and double quotes) works fine in PDF.

8

I haven’t used oracle and just googled a bit…

The ROWID is a column of yours (you have defined is as a number?) or it’s the oracle ROWID column?

I’m asking because for example here http://www.orafaq.com/wiki/ROWID - ROWID is displayed as letters, not numbers…

9

Qiang,

Both solutions proposed by you did not work. But I did a bit deeper research and found out that the problem relates to PDO not DAO. After pasting the very same SQL statement to Oracle SQL Developer I got the same error. I also checked what PDO is actually returning and found out that nothing. This assured me that SQL itself is wrong, and PDO is somehow hiding this error, while DAO is showing it.

ROWID in Oracle seems to be an object and you can’t limit number of records this way. Instead you have to use ROWNUM:


$sql = 'SELECT * FROM WWW_USERS WHERE ROWNUM < 50';

I found this Oracle article about querying Oracle DBs with using ROWID. You might want to read it, if you plan to add similar function to Oracle class in Yii.

Now, it seems that not. PDO is hiding errors like this one, while DAO shows them. A small stupidity of PDO in favor to DAO.

10

No, I’m talking about ROWID pseudocolumn of Oracle. Just like I explained in above post. It returns letters but seems to be an object. After all, it was my mistake that I used ROWID where I should use ROWNUM, but thanks to this I discovered an issue that PDO is hiding error in such situations. Thanks for Googling for me.

11