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