Details
-
Bug
-
Status: Closed
-
Blocker
-
Resolution: Fixed
-
2.0-a1
-
None
-
WindowsXP, Oracle 9.2.0.1, Tomcat 4.1.29, J2SE1.4.2_03
Description
The newly added src/sql/populate-userinfo-for-default-psml.sql cannot be run against an Oracle database (and probably others).
The problem is that it contains database specific sql which won't work on Oracle. It contains insert statements with string value for date columns. The format used isn't compatible with Oracle.
Example:
INSERT INTO PREFS_NODE VALUES(1,NULL,'',0,'/','2004-05-22 14:57:53.586','2004-05-22 14:57:53.586');
Oracle requires the sql function to_date(value,format) to be used if using non-default formatted date strings. What the default is cannot easily be determined: its configurable.
So, for Oracle the sql function must be used in this case.
Another issue with this example is the usage of miliseconds in the date value. Oracle doesn't support that. Its resolution for date columns goes no further than seconds.
Getting this example inserted in Oracle thus requires:
INSERT INTO PREFS_NODE VALUES(1,NULL,'',0,'/',to_date('2004-05-22 14:57:54','YYYY-MM-DD HH24:MI:SS'),to_date('2004-05-22 14:57:53','YYYY-MM-DD HH24:MI:SS'));
Another column type problem is with boolean. Hsql does understand boolean columns, Oracle doesn't. Torque generates number(1) columns for those in which 0 means false and 1 means true.
I've created a new version of the sql script for oracle and put it into a new oracle subfolder.
I will attach a patch shortly which fixes the deploy for oracle and in which I also reordered the execution of this script to be done from the portal/maven.xml db.entities goal like the others.
I haven't mysql running so I'm not sure if the default script will work for mysql. Maybe David Sean Taylor can test that.
I don't think this is the road to go though and I will start a discussion on the dev list concerning these database dependent sql scripts.