CreoleSQLExecTask fails with Oracle

[vsa@…]

I want to execute SQL code on a Oracle 10gR2 base but the Oracle driver throws an exception:

ORA-00911: invalid character

So I looked to the code and found that the trailing delimiter is not removed. Mysql doesn't care about it but Oracle does. This is the patch I use to fix it:

Index: classes/phing/tasks/ext/CreoleSQLExecTask.php
===================================================================
--- classes/phing/tasks/ext/CreoleSQLExecTask.php	(revision 31)
+++ classes/phing/tasks/ext/CreoleSQLExecTask.php	(copie de travail)
@@ -390,7 +390,7 @@
                         || $this->delimiterType == self::DELIM_ROW
                         && $line == $this->delimiter) {
                     $this->log("SQL: " . $sql, PROJECT_MSG_VERBOSE);
-                    $this->execSQL(StringHelper::substring($sql, 0, strlen($sql) - strlen($this->delimiter)), $out);
+                    $this->execSQL(StringHelper::substring($sql, 0, strlen($sql) - strlen($this->delimiter)) - 1, $out);
                     $sql = "";
                 }
             }

I don't think many of you use this task but it would be cool to fix that for us in the next release.

Thanks

[mrook]

As I don't have the ability to test the CreoleSQLExecTask locally, can you confirm this fix does not introduce any unwanted side-effects in other database drivers (such as the MySQL driver)?

Thanks

[vsa@…]

It's not a issue about the creole drivers. If you look the original code, it tries to remove the delimiter string from the sql string with the call of the substring method. Since the index starts from 0, it must end with the index strlen($sql) - 1 and then remove the length of the delimiter. In others word, if I have this sql string:

$sql = "select id from my_table;"

and the delimiter is ";", the current code doesn't remove ";".
Moreover, in the creole API, the method executeQuery does not need the final ";" to run. So to answer your question, this change does not have any side-effects on MySql. I don't know about other databases but I think there's no problem.

I've found a work around to achieve this with the current CreoleSQLExec task by giving the string " ;" (space before ;) as delimiter to run on Oracle and it still works with MySql.

I hope I was clear in my explaination.

[mrook]

Fixed in r45. Thanks!

[mrook]

Comments by Christian Mayer:

$this->execSQL(StringHelper::substring($sql, 0, strlen($sql) -
strlen($this->delimiter)) - 1, $out);

should probably be:

$this->execSQL(StringHelper::substring($sql, 0, strlen($sql) -
strlen($this->delimiter) - 1), $out);

[mrook]

Fixed in r83.