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 import com.sun.star.lang.XMultiServiceFactory;
29 import complex.connectivity.SubTestCase;
30 import complex.connectivity.TestCase;
31 
32 public class DBaseStringFunctions extends SubTestCase
33 {
34     private String where = "FROM \"biblio\" \"biblio\" where \"Identifier\" = 'BOR00'";
35     private final XMultiServiceFactory m_xORB;
36 
DBaseStringFunctions(final XMultiServiceFactory _xORB,final TestCase i_testCase)37     public DBaseStringFunctions(final XMultiServiceFactory _xORB,final TestCase i_testCase)
38     {
39         super( i_testCase );
40         m_xORB = _xORB;
41     }
42 
testFunctions()43     public void testFunctions() throws com.sun.star.uno.Exception, com.sun.star.beans.UnknownPropertyException
44     {
45         final XRowSet xRowRes = (XRowSet) UnoRuntime.queryInterface(XRowSet.class,
46                 m_xORB.createInstance("com.sun.star.sdb.RowSet"));
47 
48         getLog().println("starting String function test");
49         // set the properties needed to connect to a database
50         final XPropertySet xProp = (XPropertySet) UnoRuntime.queryInterface(XPropertySet.class, xRowRes);
51         xProp.setPropertyValue("DataSourceName", "Bibliography");
52 
53         xProp.setPropertyValue("CommandType", Integer.valueOf(com.sun.star.sdb.CommandType.COMMAND));
54 
55         try
56         {
57             upper(xRowRes);
58         }
59         catch (SQLException ex)
60         {
61             assure("upper " + ex.getMessage(), false);
62             throw ex;
63         }
64         try
65         {
66             lower(xRowRes);
67         }
68         catch (SQLException ex)
69         {
70             assure("lower " + ex.getMessage(), false);
71             throw ex;
72         }
73         try
74         {
75             acsii(xRowRes);
76         }
77         catch (SQLException ex)
78         {
79             assure("ascii " + ex.getMessage(), false);
80             throw ex;
81         }
82         try
83         {
84             char_length(xRowRes);
85         }
86         catch (SQLException ex)
87         {
88             assure("char_len " + ex.getMessage(), false);
89             throw ex;
90         }
91         try
92         {
93             concat(xRowRes);
94         }
95         catch (SQLException ex)
96         {
97             assure("concat " + ex.getMessage(), false);
98             throw ex;
99         }
100         try
101         {
102             chartest(xRowRes);
103         }
104         catch (SQLException ex)
105         {
106             assure("char " + ex.getMessage(), false);
107             throw ex;
108         }
109         try
110         {
111             locate(xRowRes);
112         }
113         catch (SQLException ex)
114         {
115             assure("locate " + ex.getMessage(), false);
116             throw ex;
117         }
118         try
119         {
120             substring(xRowRes);
121         }
122         catch (SQLException ex)
123         {
124             assure("substr " + ex.getMessage(), false);
125             throw ex;
126         }
127         try
128         {
129             ltrim(xRowRes);
130         }
131         catch (SQLException ex)
132         {
133             assure("ltrim " + ex.getMessage(), false);
134             throw ex;
135         }
136         try
137         {
138             rtrim(xRowRes);
139         }
140         catch (SQLException ex)
141         {
142             assure("rtrim " + ex.getMessage(), false);
143             throw ex;
144         }
145         try
146         {
147             space(xRowRes);
148         }
149         catch (SQLException ex)
150         {
151             assure("space " + ex.getMessage(), false);
152             throw ex;
153         }
154         try
155         {
156             replace(xRowRes);
157         }
158         catch (SQLException ex)
159         {
160             assure("replace " + ex.getMessage(), false);
161             throw ex;
162         }
163         try
164         {
165             repeat(xRowRes);
166         }
167         catch (SQLException ex)
168         {
169             assure("repeat " + ex.getMessage(), false);
170             throw ex;
171         }
172         try
173         {
174             insert(xRowRes);
175         }
176         catch (SQLException ex)
177         {
178             assure("insert " + ex.getMessage(), false);
179             throw ex;
180         }
181         try
182         {
183             left(xRowRes);
184         }
185         catch (SQLException ex)
186         {
187             assure("left " + ex.getMessage(), false);
188             throw ex;
189         }
190         try
191         {
192             right(xRowRes);
193         }
194         catch (SQLException ex)
195         {
196             assure("right " + ex.getMessage(), false);
197             throw ex;
198         }
199     }
200 
execute(final XRowSet xRowRes, String sql)201     private XRow execute(final XRowSet xRowRes, String sql) throws com.sun.star.uno.Exception, com.sun.star.beans.UnknownPropertyException
202     {
203         final XPropertySet xProp = (XPropertySet) UnoRuntime.queryInterface(XPropertySet.class, xRowRes);
204         xProp.setPropertyValue("Command", "SELECT " + sql + where);
205         xRowRes.execute();
206         final XResultSet xRes = (XResultSet) UnoRuntime.queryInterface(XResultSet.class, xRowRes);
207         assure("No valid row! ", xRes.next());
208 
209         return (XRow) UnoRuntime.queryInterface(XRow.class, xRes);
210     }
211 
upper(final XRowSet xRowRes)212     private void upper(final XRowSet xRowRes) throws com.sun.star.uno.Exception, com.sun.star.beans.UnknownPropertyException
213     {
214         final XRow row = execute(xRowRes, "upper('test'),UCASE('test') ");
215         assure("upper('test') failed!", row.getString(1).equals("TEST"));
216         assure("ucase('test') failed!", row.getString(2).equals("TEST"));
217     }
218 
lower(final XRowSet xRowRes)219     private void lower(final XRowSet xRowRes) throws com.sun.star.uno.Exception, com.sun.star.beans.UnknownPropertyException
220     {
221         final XRow row = execute(xRowRes, "lower('TEST'),LCASE('TEST') ");
222         assure("lower('TEST') failed!", row.getString(1).equals("test"));
223         assure("lcase('TEST') failed!", row.getString(2).equals("test"));
224         final String temp = where;
225         where = "FROM \"biblio\" \"biblio\" where LOWER(\"Identifier\") like 'bor%'";
226         execute(xRowRes, "lower('TEST'),LCASE('TEST') ");
227         where = temp;
228     }
229 
acsii(final XRowSet xRowRes)230     private void acsii(final XRowSet xRowRes) throws com.sun.star.uno.Exception, com.sun.star.beans.UnknownPropertyException
231     {
232         final XRow row = execute(xRowRes, "ASCII('2') ");
233         assure("acsii('2') failed!", row.getInt(1) == 50);
234     }
235 
char_length(final XRowSet xRowRes)236     private void char_length(final XRowSet xRowRes) throws com.sun.star.uno.Exception, com.sun.star.beans.UnknownPropertyException
237     {
238         final XRow row = execute(xRowRes, "char_length('test'),character_length('test'),OCTET_LENGTH('test') ");
239         assure("char_length('test') failed!", row.getInt(1) == 4);
240         assure("character_length('test') failed!", row.getInt(2) == 4);
241         assure("OCTET_LENGTH('test') failed!", row.getInt(3) == 4);
242     }
243 
concat(final XRowSet xRowRes)244     private void concat(final XRowSet xRowRes) throws com.sun.star.uno.Exception, com.sun.star.beans.UnknownPropertyException
245     {
246         final XRow row = execute(xRowRes, "CONCAT('Hello',' ','World') ");
247         assure("CONCAT('Hello',' ',,'World') failed!", row.getString(1).equals("Hello World"));
248     }
249 
locate(final XRowSet xRowRes)250     private void locate(final XRowSet xRowRes) throws com.sun.star.uno.Exception, com.sun.star.beans.UnknownPropertyException
251     {
252         final XRow row = execute(xRowRes, "LOCATE('bar', 'foobarbar') ");
253         assure("LOCATE('bar', 'foobarbar') failed!", row.getInt(1) == 4);
254     }
255 
substring(final XRowSet xRowRes)256     private void substring(final XRowSet xRowRes) throws com.sun.star.uno.Exception, com.sun.star.beans.UnknownPropertyException
257     {
258         final XRow row = execute(xRowRes, "SUBSTRING('Quadratically',5) ");
259         assure("SUBSTRING('Quadratically',5) failed!", row.getString(1).equals("ratically"));
260     }
261 
ltrim(final XRowSet xRowRes)262     private void ltrim(final XRowSet xRowRes) throws com.sun.star.uno.Exception, com.sun.star.beans.UnknownPropertyException
263     {
264         final XRow row = execute(xRowRes, "LTRIM('  barbar') ");
265         assure("LTRIM('  barbar') failed!", row.getString(1).equals("barbar"));
266     }
267 
rtrim(final XRowSet xRowRes)268     private void rtrim(final XRowSet xRowRes) throws com.sun.star.uno.Exception, com.sun.star.beans.UnknownPropertyException
269     {
270         final XRow row = execute(xRowRes, "RTRIM('barbar  ') ");
271         assure("RTRIM('barbar  ') failed!", row.getString(1).equals("barbar"));
272     }
273 
space(final XRowSet xRowRes)274     private void space(final XRowSet xRowRes) throws com.sun.star.uno.Exception, com.sun.star.beans.UnknownPropertyException
275     {
276         final XRow row = execute(xRowRes, "space(6) ");
277         assure("space(6) failed!", row.getString(1).equals("      "));
278     }
279 
replace(final XRowSet xRowRes)280     private void replace(final XRowSet xRowRes) throws com.sun.star.uno.Exception, com.sun.star.beans.UnknownPropertyException
281     {
282         final XRow row = execute(xRowRes, "REPLACE('www.OOo.com', 'w', 'Ww') ");
283         assure("REPLACE('www.OOo.com', 'w', 'Ww') failed!", row.getString(1).equals("WwWwWw.OOo.com"));
284     }
285 
repeat(final XRowSet xRowRes)286     private void repeat(final XRowSet xRowRes) throws com.sun.star.uno.Exception, com.sun.star.beans.UnknownPropertyException
287     {
288         final XRow row = execute(xRowRes, "REPEAT('OOo', 3) ");
289         assure("REPEAT('OOo', 3) failed!", row.getString(1).equals("OOoOOoOOo"));
290     }
291 
insert(final XRowSet xRowRes)292     private void insert(final XRowSet xRowRes) throws com.sun.star.uno.Exception, com.sun.star.beans.UnknownPropertyException
293     {
294         final XRow row = execute(xRowRes, "INSERT('Quadratic', 3, 4, 'What') ");
295         assure("INSERT('Quadratic', 3, 4, 'What') failed!", row.getString(1).equals("QuWhattic"));
296     }
297 
left(final XRowSet xRowRes)298     private void left(final XRowSet xRowRes) throws com.sun.star.uno.Exception, com.sun.star.beans.UnknownPropertyException
299     {
300         final XRow row = execute(xRowRes, "LEFT('foobarbar', 5) ");
301         assure("LEFT('foobarbar', 5) failed!", row.getString(1).equals("fooba"));
302     }
303 
right(final XRowSet xRowRes)304     private void right(final XRowSet xRowRes) throws com.sun.star.uno.Exception, com.sun.star.beans.UnknownPropertyException
305     {
306         final XRow row = execute(xRowRes, "RIGHT('foobarbar', 4) ");
307         assure("RIGHT('foobarbar', 4) failed!", row.getString(1).equals("rbar"));
308     }
309 
chartest(final XRowSet xRowRes)310     private void chartest(final XRowSet xRowRes) throws com.sun.star.uno.Exception, com.sun.star.beans.UnknownPropertyException
311     {
312         final XRow row = execute(xRowRes, "CHAR(ascii('t'),ascii('e'),ascii('s'),ascii('t')) ");
313         assure("CHAR(ascii('t'),ascii('e'),ascii('s'),ascii('t')) failed!", row.getString(1).equals("test"));
314     }
315 }
316