1 /************************************************************************* 2 * 3 * DO NOT ALTER OR REMOVE COPYRIGHT NOTICES OR THIS FILE HEADER. 4 * 5 * Copyright 2000, 2010 Oracle and/or its affiliates. 6 * 7 * OpenOffice.org - a multi-platform office productivity suite 8 * 9 * This file is part of OpenOffice.org. 10 * 11 * OpenOffice.org is free software: you can redistribute it and/or modify 12 * it under the terms of the GNU Lesser General Public License version 3 13 * only, as published by the Free Software Foundation. 14 * 15 * OpenOffice.org is distributed in the hope that it will be useful, 16 * but WITHOUT ANY WARRANTY; without even the implied warranty of 17 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the 18 * GNU Lesser General Public License version 3 for more details 19 * (a copy is included in the LICENSE file that accompanied this code). 20 * 21 * You should have received a copy of the GNU Lesser General Public License 22 * version 3 along with OpenOffice.org. If not, see 23 * <http://www.openoffice.org/license.html> 24 * for a copy of the LGPLv3 License. 25 * 26 ************************************************************************/ 27 package complex.connectivity.dbase; 28 29 import com.sun.star.uno.UnoRuntime; 30 import com.sun.star.sdbc.*; 31 import com.sun.star.beans.XPropertySet; 32 33 import com.sun.star.lang.XMultiServiceFactory; 34 import complex.connectivity.TestCase; 35 import complex.connectivity.SubTestCase; 36 37 public class DBaseDateFunctions extends SubTestCase 38 { 39 40 private final String where = "FROM \"biblio\" \"biblio\" where \"Identifier\" = 'BOR00'"; 41 private final XMultiServiceFactory m_xORB; 42 43 public DBaseDateFunctions(final XMultiServiceFactory _xORB, final TestCase i_testCase) 44 { 45 super( i_testCase ); 46 m_xORB = _xORB; 47 } 48 49 public void testFunctions() throws com.sun.star.uno.Exception, com.sun.star.beans.UnknownPropertyException 50 { 51 final XRowSet xRowRes = (XRowSet) UnoRuntime.queryInterface(XRowSet.class, 52 m_xORB.createInstance("com.sun.star.sdb.RowSet")); 53 54 getLog().println("starting DateTime function test!"); 55 // set the properties needed to connect to a database 56 final XPropertySet xProp = (XPropertySet) UnoRuntime.queryInterface(XPropertySet.class, xRowRes); 57 xProp.setPropertyValue("DataSourceName", "Bibliography"); 58 59 xProp.setPropertyValue("CommandType", Integer.valueOf(com.sun.star.sdb.CommandType.COMMAND)); 60 61 try 62 { 63 curdate(xRowRes); 64 } 65 catch (SQLException ex) 66 { 67 assure("upper " + ex.getMessage(), false); 68 throw ex; 69 } 70 try 71 { 72 curtime(xRowRes); 73 } 74 catch (SQLException ex) 75 { 76 assure("lower " + ex.getMessage(), false); 77 throw ex; 78 } 79 try 80 { 81 dayname(xRowRes); 82 } 83 catch (SQLException ex) 84 { 85 assure("ascii " + ex.getMessage(), false); 86 throw ex; 87 } 88 try 89 { 90 dayofmonth(xRowRes); 91 } 92 catch (SQLException ex) 93 { 94 assure("char_len " + ex.getMessage(), false); 95 throw ex; 96 } 97 try 98 { 99 dayofweek(xRowRes); 100 } 101 catch (SQLException ex) 102 { 103 assure("concat " + ex.getMessage(), false); 104 throw ex; 105 } 106 try 107 { 108 dayofyear(xRowRes); 109 } 110 catch (SQLException ex) 111 { 112 assure("locate " + ex.getMessage(), false); 113 throw ex; 114 } 115 try 116 { 117 hour(xRowRes); 118 } 119 catch (SQLException ex) 120 { 121 assure("substr " + ex.getMessage(), false); 122 throw ex; 123 } 124 try 125 { 126 minute(xRowRes); 127 } 128 catch (SQLException ex) 129 { 130 assure("ltrim " + ex.getMessage(), false); 131 throw ex; 132 } 133 try 134 { 135 month(xRowRes); 136 } 137 catch (SQLException ex) 138 { 139 assure("rtrim " + ex.getMessage(), false); 140 throw ex; 141 } 142 try 143 { 144 monthname(xRowRes); 145 } 146 catch (SQLException ex) 147 { 148 assure("space " + ex.getMessage(), false); 149 throw ex; 150 } 151 try 152 { 153 now(xRowRes); 154 } 155 catch (SQLException ex) 156 { 157 assure("replace " + ex.getMessage(), false); 158 throw ex; 159 } 160 try 161 { 162 quarter(xRowRes); 163 } 164 catch (SQLException ex) 165 { 166 assure("repeat " + ex.getMessage(), false); 167 throw ex; 168 } 169 try 170 { 171 second(xRowRes); 172 } 173 catch (SQLException ex) 174 { 175 assure("insert " + ex.getMessage(), false); 176 throw ex; 177 } 178 try 179 { 180 week(xRowRes); 181 } 182 catch (SQLException ex) 183 { 184 assure("left " + ex.getMessage(), false); 185 throw ex; 186 } 187 try 188 { 189 year(xRowRes); 190 } 191 catch (SQLException ex) 192 { 193 assure("right " + ex.getMessage(), false); 194 throw ex; 195 } 196 } 197 198 private XRow execute(final XRowSet xRowRes, final String sql) throws com.sun.star.uno.Exception, com.sun.star.beans.UnknownPropertyException 199 { 200 final XPropertySet xProp = (XPropertySet) UnoRuntime.queryInterface(XPropertySet.class, xRowRes); 201 xProp.setPropertyValue("Command", "SELECT " + sql + where); 202 xRowRes.execute(); 203 final XResultSet xRes = (XResultSet) UnoRuntime.queryInterface(XResultSet.class, xRowRes); 204 assure("No valid row! ", xRes.next()); 205 206 return (XRow) UnoRuntime.queryInterface(XRow.class, xRes); 207 } 208 209 private void dayofweek(final XRowSet xRowRes) throws com.sun.star.uno.Exception, com.sun.star.beans.UnknownPropertyException 210 { 211 final XRow row = execute(xRowRes, "DAYOFWEEK('1998-02-03') "); 212 assure("DAYOFWEEK('1998-02-03') failed!", row.getInt(1) == 3); 213 } 214 215 private void dayofmonth(final XRowSet xRowRes) throws com.sun.star.uno.Exception, com.sun.star.beans.UnknownPropertyException 216 { 217 final XRow row = execute(xRowRes, "DAYOFMONTH('1998-02-03') "); 218 assure("DAYOFMONTH('1998-02-03') failed!", row.getInt(1) == 3); 219 } 220 221 private void dayofyear(final XRowSet xRowRes) throws com.sun.star.uno.Exception, com.sun.star.beans.UnknownPropertyException 222 { 223 final XRow row = execute(xRowRes, "DAYOFYEAR('1998-02-03') "); 224 assure("DAYOFYEAR('1998-02-03') failed!", row.getInt(1) == 34); 225 } 226 227 private void month(final XRowSet xRowRes) throws com.sun.star.uno.Exception, com.sun.star.beans.UnknownPropertyException 228 { 229 final XRow row = execute(xRowRes, "month('1998-02-03') "); 230 assure("month('1998-02-03') failed!", row.getInt(1) == 2); 231 } 232 233 private void dayname(final XRowSet xRowRes) throws com.sun.star.uno.Exception, com.sun.star.beans.UnknownPropertyException 234 { 235 final XRow row = execute(xRowRes, "DAYNAME('1998-02-05') "); 236 assure("DAYNAME('1998-02-05') failed!", row.getString(1).equals("Thursday")); 237 } 238 239 private void monthname(final XRowSet xRowRes) throws com.sun.star.uno.Exception, com.sun.star.beans.UnknownPropertyException 240 { 241 final XRow row = execute(xRowRes, "MONTHNAME('1998-02-05') "); 242 assure("MONTHNAME('1998-02-05') failed!", row.getString(1).equals("February")); 243 } 244 245 private void quarter(final XRowSet xRowRes) throws com.sun.star.uno.Exception, com.sun.star.beans.UnknownPropertyException 246 { 247 final XRow row = execute(xRowRes, "QUARTER('98-01-01'),QUARTER('98-04-01'),QUARTER('98-07-01'),QUARTER('98-10-01') "); 248 assure("QUARTER('98-01-01') failed!", row.getInt(1) == 1); 249 assure("QUARTER('98-04-01') failed!", row.getInt(2) == 2); 250 assure("QUARTER('98-07-01') failed!", row.getInt(3) == 3); 251 assure("QUARTER('98-10-01') failed!", row.getInt(4) == 4); 252 } 253 254 private void week(final XRowSet xRowRes) throws com.sun.star.uno.Exception, com.sun.star.beans.UnknownPropertyException 255 { 256 final XRow row = execute(xRowRes, "WEEK('1998-02-20') "); 257 assure("WEEK('1998-02-20') failed!", row.getInt(1) == 7); 258 } 259 260 private void year(final XRowSet xRowRes) throws com.sun.star.uno.Exception, com.sun.star.beans.UnknownPropertyException 261 { 262 final XRow row = execute(xRowRes, "YEAR('98-02-03') "); 263 assure("YEAR('98-02-03') failed!", row.getInt(1) == 98); 264 } 265 266 private void hour(final XRowSet xRowRes) throws com.sun.star.uno.Exception, com.sun.star.beans.UnknownPropertyException 267 { 268 final XRow row = execute(xRowRes, "HOUR('10:05:03') "); 269 assure("HOUR('10:05:03') failed!", row.getInt(1) == 10); 270 } 271 272 private void minute(final XRowSet xRowRes) throws com.sun.star.uno.Exception, com.sun.star.beans.UnknownPropertyException 273 { 274 final XRow row = execute(xRowRes, "MINUTE('98-02-03 10:05:03') "); 275 assure("MINUTE('98-02-03 10:05:03') failed!", row.getInt(1) == 5); 276 } 277 278 private void second(final XRowSet xRowRes) throws com.sun.star.uno.Exception, com.sun.star.beans.UnknownPropertyException 279 { 280 final XRow row = execute(xRowRes, "SECOND('10:05:03') "); 281 assure("SECOND('10:05:03') failed!", row.getInt(1) == 3); 282 } 283 284 private void curdate(final XRowSet xRowRes) throws com.sun.star.uno.Exception, com.sun.star.beans.UnknownPropertyException 285 { 286 final XRow row = execute(xRowRes, "CURDATE() "); 287 final com.sun.star.util.Date aDate = row.getDate(1); 288 getLog().println("CURDATE() is '" + aDate.Year + "-" + aDate.Month + "-" + aDate.Day + "'"); 289 } 290 291 private void curtime(final XRowSet xRowRes) throws com.sun.star.uno.Exception, com.sun.star.beans.UnknownPropertyException 292 { 293 final XRow row = execute(xRowRes, "CURTIME() "); 294 final com.sun.star.util.Time aTime = row.getTime(1); 295 getLog().println("CURTIME() is '" + aTime.Hours + ":" + aTime.Minutes + ":" + aTime.Seconds + "'"); 296 } 297 298 private void now(final XRowSet xRowRes) throws com.sun.star.uno.Exception, com.sun.star.beans.UnknownPropertyException 299 { 300 final XRow row = execute(xRowRes, "NOW() "); 301 final com.sun.star.util.DateTime aTime = row.getTimestamp(1); 302 getLog().println("NOW() is '" + aTime.Year + "-" + aTime.Month + "-" + aTime.Day + "'"); 303 getLog().println("'" + aTime.Hours + ":" + aTime.Minutes + ":" + aTime.Seconds + "'"); 304 } 305 } 306