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 // ----------------------------------------------------------------------------- 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 // ----------------------------------------------------------------------------- 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 // ----------------------------------------------------------------------------- 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 //------------------------------------------------------------------------------ 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 //------------------------------------------------------------------------------ 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 //------------------------------------------------------------------------------ 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 //------------------------------------------------------------------------------ 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