Ticket #9 (closed defect: fixed)

Opened 3 years ago

Last modified 1 year ago

CreoleSQLExecTask fails with Oracle

Reported by: vsa@piaction.com Assigned to: mrook
Priority: major Milestone: 2.2.0
Component: Version: 2.2.0RC1
Keywords: Cc:

Description

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

Attachments

Change History

03/09/06 14:09:09 changed by mrook

  • owner set to mrook.
  • status changed from new to assigned.

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

03/10/06 14:38:35 changed by vsa@piaction.com

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.

03/13/06 13:43:39 changed by mrook

  • status changed from assigned to closed.
  • resolution set to fixed.

Fixed in r45. Thanks!

07/07/06 18:13:41 changed by mrook

  • status changed from closed to reopened.
  • resolution deleted.

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);

07/07/06 18:17:31 changed by mrook

  • status changed from reopened to closed.
  • resolution set to fixed.

Fixed in r83.

Add/Change #9 (CreoleSQLExecTask fails with Oracle)




Action