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
24 // MARKER(update_precomp.py): autogen include statement, do not remove
25 #include "precompiled_connectivity.hxx"
26
27
28 #include "connectivity/dbconversion.hxx"
29 #include <connectivity/dbtools.hxx>
30 #include <com/sun/star/script/XTypeConverter.hpp>
31 #include <com/sun/star/sdbc/DataType.hpp>
32 #include <com/sun/star/util/NumberFormat.hpp>
33 #include <com/sun/star/util/XNumberFormatTypes.hpp>
34 #include <com/sun/star/sdb/XColumnUpdate.hpp>
35 #include <com/sun/star/sdb/XColumn.hpp>
36 #include <com/sun/star/beans/XPropertySet.hpp>
37 #include <comphelper/extract.hxx>
38 #include "TConnection.hxx"
39 #include "diagnose_ex.h"
40 #include <comphelper/numbers.hxx>
41 #include <rtl/ustrbuf.hxx>
42 #include <tools/diagnose_ex.h>
43
44
45 using namespace ::connectivity;
46 using namespace ::comphelper;
47 using namespace ::com::sun::star::script;
48 using namespace ::com::sun::star::sdb;
49 using namespace ::com::sun::star::sdbc;
50 using namespace ::dbtools;
51 using namespace ::com::sun::star::lang;
52 using namespace ::com::sun::star::beans;
53 using namespace ::com::sun::star::util;
54 using namespace ::com::sun::star::uno;
55 using namespace ::com::sun::star::util;
56 using namespace ::com::sun::star::beans;
57 // -----------------------------------------------------------------------------
toSQLString(sal_Int32 eType,const Any & _rVal,sal_Bool bQuote,const Reference<XTypeConverter> & _rxTypeConverter)58 ::rtl::OUString DBTypeConversion::toSQLString(sal_Int32 eType, const Any& _rVal, sal_Bool bQuote,
59 const Reference< XTypeConverter >& _rxTypeConverter)
60 {
61 ::rtl::OUStringBuffer aRet;
62 if (_rVal.hasValue())
63 {
64 try
65 {
66 switch (eType)
67 {
68 case DataType::INTEGER:
69 case DataType::BIT:
70 case DataType::BOOLEAN:
71 case DataType::TINYINT:
72 case DataType::SMALLINT:
73 if (_rVal.getValueType().getTypeClass() == ::com::sun::star::uno::TypeClass_BOOLEAN)
74 {
75 if (::cppu::any2bool(_rVal))
76 aRet.appendAscii("1");
77 else
78 aRet.appendAscii("0");
79 }
80 else
81 {
82 ::rtl::OUString sTemp;
83 _rxTypeConverter->convertToSimpleType(_rVal, TypeClass_STRING) >>= sTemp;
84 aRet.append(sTemp);
85 }
86 break;
87 case DataType::CHAR:
88 case DataType::VARCHAR:
89 case DataType::LONGVARCHAR:
90 if (bQuote)
91 aRet.appendAscii("'");
92 {
93 ::rtl::OUString aTemp;
94 _rxTypeConverter->convertToSimpleType(_rVal, TypeClass_STRING) >>= aTemp;
95 sal_Int32 nIndex = (sal_Int32)-1;
96 const ::rtl::OUString sQuot(RTL_CONSTASCII_USTRINGPARAM("\'"));
97 const ::rtl::OUString sQuotToReplace(RTL_CONSTASCII_USTRINGPARAM("\'\'"));
98 do
99 {
100 nIndex += 2;
101 nIndex = aTemp.indexOf(sQuot,nIndex);
102 if(nIndex != -1)
103 aTemp = aTemp.replaceAt(nIndex,sQuot.getLength(),sQuotToReplace);
104 } while (nIndex != -1);
105
106 aRet.append(aTemp);
107 }
108 if (bQuote)
109 aRet.appendAscii("'");
110 break;
111 case DataType::REAL:
112 case DataType::DOUBLE:
113 case DataType::DECIMAL:
114 case DataType::NUMERIC:
115 case DataType::BIGINT:
116 default:
117 {
118 ::rtl::OUString sTemp;
119 _rxTypeConverter->convertToSimpleType(_rVal, TypeClass_STRING) >>= sTemp;
120 aRet.append(sTemp);
121 }
122 break;
123 case DataType::TIMESTAMP:
124 {
125 DateTime aDateTime;
126 bool bOk = false;
127 if (_rVal.getValueType().getTypeClass() == ::com::sun::star::uno::TypeClass_DOUBLE)
128 {
129 double nValue = 0.0;
130 _rVal >>= nValue;
131 aDateTime = DBTypeConversion::toDateTime(nValue);
132 bOk = true;
133 }
134 else if (_rVal.getValueType().getTypeClass() == ::com::sun::star::uno::TypeClass_STRING)
135 {
136 ::rtl::OUString sValue;
137 _rVal >>= sValue;
138 aDateTime = DBTypeConversion::toDateTime(sValue);
139 bOk = true;
140 }
141 else
142 bOk = _rVal >>= aDateTime;
143
144 OSL_VERIFY_RES( bOk, "DBTypeConversion::toSQLString: _rVal is not datetime!");
145 // check if this is really a timestamp or only a date
146 if ( bOk )
147 {
148 if (bQuote)
149 aRet.appendAscii("{TS '");
150 aRet.append(DBTypeConversion::toDateTimeString(aDateTime));
151 if (bQuote)
152 aRet.appendAscii("'}");
153 break;
154 }
155 break;
156 }
157 case DataType::DATE:
158 {
159 Date aDate;
160 bool bOk = false;
161 if (_rVal.getValueType().getTypeClass() == ::com::sun::star::uno::TypeClass_DOUBLE)
162 {
163 double nValue = 0.0;
164 _rVal >>= nValue;
165 aDate = DBTypeConversion::toDate(nValue);
166 bOk = true;
167 }
168 else if (_rVal.getValueType().getTypeClass() == ::com::sun::star::uno::TypeClass_STRING)
169 {
170 ::rtl::OUString sValue;
171 _rVal >>= sValue;
172 aDate = DBTypeConversion::toDate(sValue);
173 bOk = true;
174 }
175 else
176 bOk = _rVal >>= aDate;
177 OSL_VERIFY_RES( bOk, "DBTypeConversion::toSQLString: _rVal is not date!");
178 if (bQuote)
179 aRet.appendAscii("{D '");
180 aRet.append(DBTypeConversion::toDateString(aDate));
181 if (bQuote)
182 aRet.appendAscii("'}");
183 } break;
184 case DataType::TIME:
185 {
186 Time aTime;
187 bool bOk = false;
188 if (_rVal.getValueType().getTypeClass() == ::com::sun::star::uno::TypeClass_DOUBLE)
189 {
190 double nValue = 0.0;
191 _rVal >>= nValue;
192 aTime = DBTypeConversion::toTime(nValue);
193 bOk = true;
194 }
195 else if (_rVal.getValueType().getTypeClass() == ::com::sun::star::uno::TypeClass_STRING)
196 {
197 ::rtl::OUString sValue;
198 _rVal >>= sValue;
199 aTime = DBTypeConversion::toTime(sValue);
200 bOk = true;
201 }
202 else
203 bOk = _rVal >>= aTime;
204 OSL_VERIFY_RES( bOk,"DBTypeConversion::toSQLString: _rVal is not time!");
205 if (bQuote)
206 aRet.appendAscii("{T '");
207 aRet.append(DBTypeConversion::toTimeString(aTime));
208 if (bQuote)
209 aRet.appendAscii("'}");
210 } break;
211 }
212 }
213 catch ( const Exception& )
214 {
215 OSL_ENSURE(0,"TypeConversion Error");
216 }
217 }
218 else
219 aRet.appendAscii(" NULL ");
220 return aRet.makeStringAndClear();
221 }
222 // -----------------------------------------------------------------------------
getNULLDate(const Reference<XNumberFormatsSupplier> & xSupplier)223 Date DBTypeConversion::getNULLDate(const Reference< XNumberFormatsSupplier > &xSupplier)
224 {
225 OSL_ENSURE(xSupplier.is(), "getNULLDate : the formatter doesn't implement a supplier !");
226 if (xSupplier.is())
227 {
228 try
229 {
230 // get the null date
231 Date aDate;
232 xSupplier->getNumberFormatSettings()->getPropertyValue(::rtl::OUString::createFromAscii("NullDate")) >>= aDate;
233 return aDate;
234 }
235 catch ( const Exception& )
236 {
237 }
238 }
239
240 return getStandardDate();
241 }
242 // -----------------------------------------------------------------------------
setValue(const Reference<XColumnUpdate> & xVariant,const Reference<XNumberFormatter> & xFormatter,const Date & rNullDate,const::rtl::OUString & rString,sal_Int32 nKey,sal_Int16 nFieldType,sal_Int16 nKeyType)243 void DBTypeConversion::setValue(const Reference<XColumnUpdate>& xVariant,
244 const Reference<XNumberFormatter>& xFormatter,
245 const Date& rNullDate,
246 const ::rtl::OUString& rString,
247 sal_Int32 nKey,
248 sal_Int16 nFieldType,
249 sal_Int16 nKeyType) throw(::com::sun::star::lang::IllegalArgumentException)
250 {
251 double fValue = 0;
252 if (rString.getLength())
253 {
254 // Muss der String formatiert werden?
255 sal_Int16 nTypeClass = nKeyType & ~NumberFormat::DEFINED;
256 sal_Bool bTextFormat = nTypeClass == NumberFormat::TEXT;
257 sal_Int32 nKeyToUse = bTextFormat ? 0 : nKey;
258 sal_Int16 nRealUsedTypeClass = nTypeClass;
259 // bei einem Text-Format muessen wir dem Formatter etwas mehr Freiheiten einraeumen, sonst
260 // wirft convertStringToNumber eine NotNumericException
261 try
262 {
263 fValue = xFormatter->convertStringToNumber(nKeyToUse, rString);
264 sal_Int32 nRealUsedKey = xFormatter->detectNumberFormat(0, rString);
265 if (nRealUsedKey != nKeyToUse)
266 nRealUsedTypeClass = getNumberFormatType(xFormatter, nRealUsedKey) & ~NumberFormat::DEFINED;
267
268 // und noch eine Sonderbehandlung, diesmal fuer Prozent-Formate
269 if ((NumberFormat::NUMBER == nRealUsedTypeClass) && (NumberFormat::PERCENT == nTypeClass))
270 { // die Formatierung soll eigentlich als Prozent erfolgen, aber der String stellt nur eine
271 // einfache Nummer dar -> anpassen
272 ::rtl::OUString sExpanded(rString);
273 static ::rtl::OUString s_sPercentSymbol = ::rtl::OUString::createFromAscii("%");
274 // need a method to add a sal_Unicode to a string, 'til then we use a static string
275 sExpanded += s_sPercentSymbol;
276 fValue = xFormatter->convertStringToNumber(nKeyToUse, sExpanded);
277 }
278
279 switch (nRealUsedTypeClass)
280 {
281 case NumberFormat::DATE:
282 case NumberFormat::DATETIME:
283 case NumberFormat::TIME:
284 DBTypeConversion::setValue(xVariant,rNullDate,fValue,nRealUsedTypeClass);
285 // xVariant->updateDouble(toStandardDbDate(rNullDate, fValue));
286 break;
287 case NumberFormat::CURRENCY:
288 case NumberFormat::NUMBER:
289 case NumberFormat::SCIENTIFIC:
290 case NumberFormat::FRACTION:
291 case NumberFormat::PERCENT:
292 xVariant->updateDouble(fValue);
293 break;
294 default:
295 xVariant->updateString(rString);
296 }
297 }
298 catch(const Exception& )
299 {
300 xVariant->updateString(rString);
301 }
302 }
303 else
304 {
305 switch (nFieldType)
306 {
307 case ::com::sun::star::sdbc::DataType::CHAR:
308 case ::com::sun::star::sdbc::DataType::VARCHAR:
309 case ::com::sun::star::sdbc::DataType::LONGVARCHAR:
310 xVariant->updateString(rString);
311 break;
312 default:
313 xVariant->updateNull();
314 }
315 }
316 }
317
318 //------------------------------------------------------------------------------
setValue(const Reference<XColumnUpdate> & xVariant,const Date & rNullDate,const double & rValue,sal_Int16 nKeyType)319 void DBTypeConversion::setValue(const Reference<XColumnUpdate>& xVariant,
320 const Date& rNullDate,
321 const double& rValue,
322 sal_Int16 nKeyType) throw(::com::sun::star::lang::IllegalArgumentException)
323 {
324 switch (nKeyType & ~NumberFormat::DEFINED)
325 {
326 case NumberFormat::DATE:
327 xVariant->updateDate(toDate( rValue, rNullDate));
328 break;
329 case NumberFormat::DATETIME:
330 xVariant->updateTimestamp(toDateTime(rValue,rNullDate));
331 break;
332 case NumberFormat::TIME:
333 xVariant->updateTime(toTime(rValue));
334 break;
335 default:
336 {
337 double nValue = rValue;
338 // Reference<XPropertySet> xProp(xVariant,UNO_QUERY);
339 // if ( xProp.is()
340 // && xProp->getPropertySetInfo()->hasPropertyByName(OMetaConnection::getPropMap().getNameByIndex(PROPERTY_ID_ISSIGNED))
341 // && !::comphelper::getBOOL(xProp->getPropertyValue(OMetaConnection::getPropMap().getNameByIndex(PROPERTY_ID_ISSIGNED))) )
342 // {
343 // switch (::comphelper::getINT32(xProp->getPropertyValue(OMetaConnection::getPropMap().getNameByIndex(PROPERTY_ID_TYPE))))
344 // {
345 // case DataType::TINYINT:
346 // nValue = static_cast<sal_uInt8>(rValue);
347 // break;
348 // case DataType::SMALLINT:
349 // nValue = static_cast<sal_uInt16>(rValue);
350 // break;
351 // case DataType::INTEGER:
352 // nValue = static_cast<sal_uInt32>(rValue);
353 // break;
354 // case DataType::BIGINT:
355 // nValue = static_cast<sal_uInt64>(rValue);
356 // break;
357 // }
358 // }
359 xVariant->updateDouble(nValue);
360 }
361 }
362 }
363
364 //------------------------------------------------------------------------------
getValue(const Reference<XColumn> & i_column,const Date & i_relativeToNullDate)365 double DBTypeConversion::getValue( const Reference< XColumn >& i_column, const Date& i_relativeToNullDate )
366 {
367 try
368 {
369 const Reference< XPropertySet > xProp( i_column, UNO_QUERY_THROW );
370
371 const sal_Int32 nColumnType = ::comphelper::getINT32( xProp->getPropertyValue( OMetaConnection::getPropMap().getNameByIndex( PROPERTY_ID_TYPE ) ) );
372 switch ( nColumnType )
373 {
374 case DataType::DATE:
375 return toDouble( i_column->getDate(), i_relativeToNullDate );
376
377 case DataType::TIME:
378 return toDouble( i_column->getTime() );
379
380 case DataType::TIMESTAMP:
381 return toDouble( i_column->getTimestamp(), i_relativeToNullDate );
382
383 default:
384 {
385 sal_Bool bIsSigned = sal_True;
386 OSL_VERIFY( xProp->getPropertyValue( OMetaConnection::getPropMap().getNameByIndex( PROPERTY_ID_ISSIGNED ) ) >>= bIsSigned );
387 if ( !bIsSigned )
388 {
389 switch ( nColumnType)
390 {
391 case DataType::TINYINT:
392 return static_cast<double>(static_cast<sal_uInt8>(i_column->getByte()));
393 case DataType::SMALLINT:
394 return static_cast<double>(static_cast<sal_uInt16>(i_column->getShort()));
395 case DataType::INTEGER:
396 return static_cast<double>(static_cast<sal_uInt32>(i_column->getInt()));
397 case DataType::BIGINT:
398 return static_cast<double>(static_cast<sal_uInt64>(i_column->getLong()));
399 }
400 }
401 }
402 return i_column->getDouble();
403 }
404 }
405 catch( const Exception& )
406 {
407 DBG_UNHANDLED_EXCEPTION();
408 return 0.0;
409 }
410 }
411 //------------------------------------------------------------------------------
getFormattedValue(const Reference<XPropertySet> & _xColumn,const Reference<XNumberFormatter> & _xFormatter,const::com::sun::star::lang::Locale & _rLocale,const Date & _rNullDate)412 ::rtl::OUString DBTypeConversion::getFormattedValue(const Reference< XPropertySet>& _xColumn,
413 const Reference<XNumberFormatter>& _xFormatter,
414 const ::com::sun::star::lang::Locale& _rLocale,
415 const Date& _rNullDate)
416 {
417 OSL_ENSURE(_xColumn.is() && _xFormatter.is(), "DBTypeConversion::getFormattedValue: invalid arg !");
418 if (!_xColumn.is() || !_xFormatter.is())
419 return ::rtl::OUString();
420
421 sal_Int32 nKey(0);
422 try
423 {
424 _xColumn->getPropertyValue(OMetaConnection::getPropMap().getNameByIndex(PROPERTY_ID_FORMATKEY)) >>= nKey;
425 }
426 catch (const Exception& )
427 {
428 OSL_ENSURE(false, "DBTypeConversion::getFormattedValue: caught an exception while asking for the format key!");
429 }
430
431 if (!nKey)
432 {
433 Reference<XNumberFormats> xFormats( _xFormatter->getNumberFormatsSupplier()->getNumberFormats() );
434 Reference<XNumberFormatTypes> xTypeList(_xFormatter->getNumberFormatsSupplier()->getNumberFormats(), UNO_QUERY);
435
436 nKey = ::dbtools::getDefaultNumberFormat(_xColumn,
437 Reference< XNumberFormatTypes > (xFormats, UNO_QUERY),
438 _rLocale);
439
440 }
441
442 sal_Int16 nKeyType = getNumberFormatType(_xFormatter, nKey) & ~NumberFormat::DEFINED;
443
444 return DBTypeConversion::getFormattedValue(Reference< XColumn > (_xColumn, UNO_QUERY), _xFormatter, _rNullDate, nKey, nKeyType);
445 }
446
447 //------------------------------------------------------------------------------
getFormattedValue(const Reference<XColumn> & xVariant,const Reference<XNumberFormatter> & xFormatter,const Date & rNullDate,sal_Int32 nKey,sal_Int16 nKeyType)448 ::rtl::OUString DBTypeConversion::getFormattedValue(const Reference<XColumn>& xVariant,
449 const Reference<XNumberFormatter>& xFormatter,
450 const Date& rNullDate,
451 sal_Int32 nKey,
452 sal_Int16 nKeyType)
453 {
454 ::rtl::OUString aString;
455 if (xVariant.is())
456 {
457 try
458 {
459 switch (nKeyType & ~NumberFormat::DEFINED)
460 {
461 case NumberFormat::DATE:
462 case NumberFormat::DATETIME:
463 {
464 // get a value which represents the given date, relative to the given null date
465 double fValue = getValue( xVariant, rNullDate );
466 if ( !xVariant->wasNull() )
467 {
468 // get the null date of the formatter
469 Date aFormatterNullDate( rNullDate );
470 try
471 {
472 Reference< XNumberFormatsSupplier > xSupplier( xFormatter->getNumberFormatsSupplier(), UNO_SET_THROW );
473 Reference< XPropertySet > xFormatterSettings( xSupplier->getNumberFormatSettings(), UNO_SET_THROW );
474 OSL_VERIFY( xFormatterSettings->getPropertyValue( ::rtl::OUString( RTL_CONSTASCII_USTRINGPARAM( "NullDate" ) ) ) >>= aFormatterNullDate );
475 }
476 catch( const Exception& )
477 {
478 DBG_UNHANDLED_EXCEPTION();
479 }
480 // get a value which represents the given date, relative to the null date of the formatter
481 fValue -= toDays( rNullDate, aFormatterNullDate );
482 // format this value
483 aString = xFormatter->convertNumberToString( nKey, fValue );
484 }
485 }
486 break;
487 case NumberFormat::TIME:
488 case NumberFormat::NUMBER:
489 case NumberFormat::SCIENTIFIC:
490 case NumberFormat::FRACTION:
491 case NumberFormat::PERCENT:
492 {
493 double fValue = xVariant->getDouble();
494 if (!xVariant->wasNull())
495 aString = xFormatter->convertNumberToString(nKey, fValue);
496 } break;
497 case NumberFormat::CURRENCY:
498 {
499 double fValue = xVariant->getDouble();
500 if (!xVariant->wasNull())
501 aString = xFormatter->getInputString(nKey, fValue);
502 } break;
503 case NumberFormat::TEXT:
504 aString = xFormatter->formatString(nKey, xVariant->getString());
505 break;
506 default:
507 aString = xVariant->getString();
508 }
509 }
510 catch(const Exception& )
511 {
512 aString = xVariant->getString();
513 }
514 }
515 return aString;
516 }
517 //------------------------------------------------------------------
518