We have moved our forum to GitHub Discussions. For questions about Phalcon v3/v4/v5 you can visit here and for Phalcon v6 here.

Using Oracle TO_DATE with Phalcon

We are converting a Zend Framework app to use phalcon and I am having a problem executing Oracle functions. In Zend, I would just do this:

"BASE_PRICE_DATE" =>  new Zend_Db_Expr("TO_DATE('" . $data["dateIntroduced"] . "', 'mm/dd/yyyy')")

Since I can't find a similar functionality in Phalcon, I tried the following:

"BASE_PRICE_DATE" => "TO_DATE(" . $data["basePriceDate"] . ", 'mm/dd/yyyy')"

Which doesn't work. I get the following error, which I have run into before, so I know it is from trying to update the date field:

ORA-01858: a non-numeric character was found where a numeric was expected

And I am logging the database, which helps in a lot of cases, except it seems with bind variables and I get this in the log:

[Fri, 06 Dec 13 10:19:03 -0600][INFO] UPDATE "MODEL_INSTANCE" SET "EQUIPMENT_SUBTYPE_SIZE_ID" = ?, "MODEL_YEAR_FROM" = ?, "MODEL_YEAR_TO" = ?, "PUBLISH_PRICE_FLAG" = ?, "MODEL_ID" = ?, "MODEL_TYPE_ID" = ?, "BASE_PRICE" = ?, "BASE_WEIGHT" = ?, "BASE_PRICE_DATE" = ?, "BASE_PRICE_SOURCE_ID" = ?, "CONFIG_PRICE" = ?, "CONFIG_WEIGHT" = ?, "UPDATED_USER_ID" = ? WHERE MODEL_INSTANCE.ID = 170169

Which is useless in this case

Here is the full code below:

$updateData = array(
                "EQUIPMENT_SUBTYPE_SIZE_ID" => $classification->child->child->child->id,
                //"DATE_INTRODUCED" => "TO_DATE('" . $data["dateIntroduced"] . "', 'mm/dd/yyyy')",
                //"DATE_DISCONTINUED" => "TO_DATE('" . $data["dateDiscontinued"] . "', 'mm/dd/yyyy')",
                "MODEL_YEAR_FROM" => $data["modelYearFrom"],
                "MODEL_YEAR_TO" => $data["modelYearTo"],
                "PUBLISH_PRICE_FLAG" => $data["publishPriceFlag"],
                "MODEL_ID" => $modelId,
                "MODEL_TYPE_ID" => 1,
                "BASE_PRICE" => $data["basePrice"],
                "BASE_WEIGHT" => $data["baseWeight"],
                "BASE_PRICE_DATE" => "TO_DATE(" . $data["basePriceDate"] . ", 'mm/dd/yyyy')",
                "BASE_PRICE_SOURCE_ID" => $data["basePriceSource"],
                "CONFIG_PRICE" => $data["configuredPrice"],
                "CONFIG_WEIGHT" => $data["configuredWeight"],
                //"CONFIG_PRICE_DATE" => "TO_DATE('" . $data["configuredPriceDate"] . "', 'mm/dd/yyyy')",
                //"CONFIG_PRICE_SOURCE_ID" => $data["configuredPriceSource"],
                "UPDATED_USER_ID" => $data["userId"]
            );

            $result = $db->update(
                "MODEL_INSTANCE",
                array_keys($updateData),
                array_values($updateData),
                "MODEL_INSTANCE.ID = $modelInstanceId"
            );

Is there anyone who can point me in the right direction to either debug this or use to_date with Oracle from Phalcon.

I think after hunting for an hour then finally posting here, I found it.

Replace 'new Zend_Db_Expr' with 'new \Phalcon\Db\RawValue' and I think it works.

Reposting here in case anyone else has troubles.

Thanks for this! I'm preparing to convert a non-frameworked app that interfaces with Oracle to Phalcon so I definitely will come across this issue.