Re: DB_DataObject producing incorrect SQL

On 11/13/06, Michael Henry <michael.k.henry@gmail.com> wrote:

Justin Patrin wrote:

The one difference between my code and the example is that I am using find() instead of get(). This may be related to the problem but from what I can tell the behaviour should be the same. The resulting SQL to perform the update is incorrect:

Yes, this is your problem. find() runs the query and gets ready for you to call fetch(). get() gets a record (it implicitly calls find() and fetch()). You've only done a find but no fetch so you don't have a record in your dataobject. I'm passing "true" to find() which fetch()es the first (and only) record. If what you want to do is update a specific record then you should call fetch() before calling update(). You also don't need to clone the DO in this case. OK, I've removed the call to clone():

    $do->lang = $lang;
    $do->string_id = $stringID;
    $count = $do->find(true);

    // ASSERT: $count == 1
    if ($count == 1) {
        $do->translation = $value;
        $do->update();

        if (PEAR::isError($do)) {
            throw new Exception("Failed to update translation<BR>" .
                "Lang: {$lang} String ID: {$stringID} Value: {$value}<BR>" .
                 $do->getMessage());
        } else {
            $updates .= "Lang: {$lang} String ID: {$stringID} Value:
    {$value}\n";
        }
    } else {
        throw new Exception('Translation not found in the database');
    }

This still produces the wrong "where" clause:

    UPDATE mlci.translations
    SET id = 104 , string_id = 209 , page = 'add_org' , lang = 'to' ,
    translation = 'qwerqwer'
    WHERE ( mlci.translations.id = 104 )
      AND ( mlci.translations.string_id = 209 )
      AND ( mlci.translations.page = 'add_org' )
      AND ( mlci.translations.lang = 'to' )
      AND ( mlci.translations.translation = 'qwerqwer' )

In addition this statement sets all of the fields. Passing $original to update() prevents this because DB_DataObject only sets the changed field "translation" (which was my intention). It looks like I'll have to dig around in the where-clause generation code. Wish me luck!

Ah, ok, that's why you're cloning. I also wasn't aware of the true parameter to find(). Your original code should have been fine then. What you need to do is look at your db.ini file for the entry for this table and see what it has set as your key. It should automatically detect the PK but perhaps the DBMS or schema you're using is funky somehow. What does DB_DO have for the keys for this table? What does the table's creation SQL look like? -- Justin Patrin