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