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