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