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