/*---------------------------------------------------------------------
$Header: /Perl/OlleDB/datetime.cpp 6 11-08-07 23:55 Sommar $
All routines converting between Perl values and the datetime data types
in SQL Server.
Copyright (c) 2004-2011 Erland Sommarskog
$History: datetime.cpp $
*
* ***************** Version 6 *****************
* User: Sommar Date: 11-08-07 Time: 23:55
* Updated in $/Perl/OlleDB
* Cannot use abs to be 64-bit safe.
*
* ***************** Version 5 *****************
* User: Sommar Date: 11-08-07 Time: 23:22
* Updated in $/Perl/OlleDB
* Suppress warnings about data truncation on x64.
*
* ***************** Version 4 *****************
* User: Sommar Date: 09-07-26 Time: 12:44
* Updated in $/Perl/OlleDB
* Determining whether an SV is defined through my_sv_is_defined to as
* SvOK may return false, unless we first do SvGETMAGIC. This proved to be
* an issue when using table-valued parameters with threads::shared.
*
* ***************** Version 3 *****************
* User: Sommar Date: 08-01-06 Time: 23:33
* Updated in $/Perl/OlleDB
* Replaced all unsafe CRT functions with their safe replacements in VC8.
* olledb_message now takes a va_list as argument, so we pass it
* parameterised strings and don't have to litter the rest of the code
* with that.
*
* ***************** Version 2 *****************
* User: Sommar Date: 08-01-02 Time: 22:49
* Updated in $/Perl/OlleDB
* Fixed memory leak.
*
* ***************** Version 1 *****************
* User: Sommar Date: 07-12-24 Time: 21:40
* Created in $/Perl/OlleDB
---------------------------------------------------------------------*/
#include "CommonInclude.h"
#include "handleattributes.h"
#include "convenience.h"
#include "init.h"
#include "internaldata.h"
#include "errcheck.h"
#include "datetime.h"
// These hash keys are used for datetime hashes when working with the
// date/time data types.
static char *datetime_keys[] =
{"Year", "Month", "Day", "Hour", "Minute", "Second", "Fraction",
"TZHour", "TZMinute"};
// And here is a enum that goes with it. The is for declaring arrays only.
typedef enum datetime_key_enum
{ DT_year, DT_month, DT_day, DT_hour, DT_minute, DT_second, DT_fraction,
DT_tzhour, DT_tzminute, no_of_datetime_keys
} datetime_key_enum;
// This is called by SV_to_datetimetypes when the value is a HASH.
static BOOL HV_to_datetimetypes (SV * sv,
DBTYPE typeind,
DBTIMESTAMPOFFSET &dtoffset,
BOOL present_keys[no_of_datetime_keys],
SV * olle_ptr)
{
HV * hv;
BOOL needsdate = (typeind != DBTYPE_DBTIME2 &&
typeind != DBTYPE_SQLVARIANT);
BOOL needstime = (typeind == DBTYPE_DBTIME2);
typedef struct {datetime_key_enum part;
BOOL ismandatory;
BOOL isshort;
BOOL isunsigned;} params_struct;
params_struct get_hash_valueparams[no_of_datetime_keys] =
{{DT_year, needsdate, TRUE, FALSE},
{DT_month, needsdate, TRUE, TRUE},
{DT_day, needsdate, TRUE, TRUE},
{DT_hour, needstime, TRUE, TRUE},
{DT_minute, needstime, TRUE, TRUE},
{DT_second, FALSE, TRUE, TRUE},
{DT_fraction, FALSE, FALSE, TRUE},
{DT_tzhour, FALSE, TRUE, FALSE},
{DT_tzminute, FALSE, TRUE, FALSE}};
// Verify that the sv is really a hash reference.
if (strncmp(SvPV_nolen(sv), "HASH(", 5) != 0)
return FALSE;
hv = (HV *) SvRV(sv);
for (int ix = DT_year; ix <= DT_tzminute; ix++) {
params_struct *p = &get_hash_valueparams[ix];
SV ** svp;
SV * sv = NULL;
svp = hv_fetch(hv, datetime_keys[p->part],
(int) strlen(datetime_keys[p->part]), 0);
if (svp != NULL)
sv = *svp;
present_keys[ix] = my_sv_is_defined(sv);
if (! present_keys[ix]) {
if (! p->ismandatory) {
continue;
}
else {
olledb_message(olle_ptr, -1, 1, 10,
L"Mandatory part '%S' missing from datetime hash.",
datetime_keys[p->part]);
return FALSE;
}
}
IV partvalue = SvIV(sv);
if (_abs64(partvalue) > 32767 && p->isshort ||
partvalue < 0 && p->isunsigned) {
olledb_message(olle_ptr, -1, 1, 10,
L"Part '%S' in datetime hash has illegal value %d.",
datetime_keys[p->part], partvalue);
return FALSE;
}
switch (p->part) {
case DT_year : dtoffset.year = (SHORT) partvalue;
break;
case DT_month : dtoffset.month = (USHORT) partvalue;
break;
case DT_day : dtoffset.day = (USHORT) partvalue;
break;
case DT_hour : dtoffset.hour = (USHORT) partvalue;
break;
case DT_minute : dtoffset.minute = (USHORT) partvalue;
break;
case DT_second : dtoffset.second = (USHORT) partvalue;
break;
case DT_fraction : dtoffset.fraction = (ULONG) (SvNV(sv) * 1000000);
break;
case DT_tzhour : dtoffset.timezone_hour = (SHORT) partvalue;
break;
case DT_tzminute :
// TZ Minute without hour is not permitted.
if (! present_keys[DT_tzhour]) {
olledb_message(olle_ptr, -1, 1, 10,
"TZMinute appears in datetime hash, but TZHour is missing.");
return FALSE;
}
dtoffset.timezone_minute = (SHORT) partvalue;
break;
default : croak("Seroius error in DT_to_datetimetypes");
}
}
return TRUE;
}
// And this is another that examines the end of a string to see if there
// is a time-zone indicator. If there is, a string terminator is written
// to where the TZ indicator begins, and the string and strlen is modified.
static BOOL get_time_zone (char * str,
STRLEN &strlen,
DBTIMESTAMPOFFSET &dtoffset)
{
// Which state: we can be after a delimiter, before it, or within a number
// of digits. This controls whether space is permitted and what we are
// looking for next.
enum {afterdelim, indigits, beforedelim} state = afterdelim;
enum {minute, hour} part = minute;
int num;
BOOL happy_end = FALSE;
for (size_t ix = strlen - 1; ix >= 0; ix--) {
char ch = str[ix];
switch (state) {
case afterdelim :
if (isdigit(ch)) {
state = indigits;
num = ch - '0';
}
else if (! isspace(ch)) {
return FALSE;
}
break;
case indigits :
if (part == minute && ch == ':') {
state = afterdelim;
part = hour;
dtoffset.timezone_minute = num;
}
else if (part == hour && (ch == '+' || ch == '-')) {
happy_end = TRUE;
}
else if (isspace(ch)) {
state = beforedelim;
}
else if ( isdigit(ch)) {
num += (ch - '0') * 10;
}
else {
return FALSE;
}
break;
case beforedelim :
if (part == minute && ch == ':') {
state = afterdelim;
part = hour;
dtoffset.timezone_minute = num;
}
else if (part == hour && (ch == '+' || ch == '-')) {
happy_end = TRUE;
}
else if (! isspace(ch)) {
return FALSE;
}
break;
}
if (happy_end) {
dtoffset.timezone_hour = num;
if (ch == '-') {
dtoffset.timezone_hour *= -1;
dtoffset.timezone_minute *= -1;
}
str[ix] = '\0';
strlen = ix;
return TRUE;
}
}
return FALSE;
}
// This functions parses str under the assumption that it obeys the ISO
// format with YYYY-MM-DD or YYYYMMDD and HH:MM[:SS.ffffffff], with space
// or T between date and time. Date may also be terminated with Z. The
// function also permits for time-only strings. This function does not
// perform any validation that the numbers are valid. Two-digit years
// are not handled. The result is put into dtoffset. However, the function
// assumes that the time zone has already been extracted from the string.
static BOOL parse_iso_string(char * str,
STRLEN stringlen,
DBTIMESTAMPOFFSET &dtoffset) {
enum {afterdelim, indigits, beforedelim} state = afterdelim;
typedef struct {datetime_key_enum part;
char delim[5];
int maxdigits;
BOOL OK_as_final;} part_struct;
part_struct parts[no_of_datetime_keys] =
{{DT_year, "-:TZ", 8, FALSE}, // 8? Yes, this is for YYYYMMDD.
{DT_month, "-", 2, FALSE},
{DT_day, "TZ ", 2, TRUE},
{DT_hour, ":", 2, FALSE},
{DT_minute, ":", 2, TRUE},
{DT_second, ".", 2, TRUE},
{DT_fraction, "\0", 7, TRUE},
{DT_tzhour, "", 0, FALSE},
{DT_tzminute, "", 0, FALSE}};
datetime_key_enum part = DT_year;
int num = 0;
int no_of_digits = 0;
BOOL savepart = FALSE;
for (STRLEN ix = 0; ix <= stringlen; ix++) {
char ch = str[ix];
if (ix < stringlen) {
switch (state) {
case afterdelim :
if (isdigit(ch)) {
state = indigits;
num = ch - '0';
no_of_digits = 1;
}
else if (! isspace(ch)) {
return FALSE;
}
break;
case indigits :
if (strchr(parts[part].delim, ch) ||
part == DT_year && no_of_digits > 4 && isspace(ch)) {
state = afterdelim;
savepart = TRUE;
}
else if (isspace(ch)) {
state = beforedelim;
}
else if (isdigit(ch) && ++no_of_digits <= parts[part].maxdigits) {
num = num*10 + (ch - '0');
}
else {
return FALSE;
}
break;
case beforedelim :
if (strchr(parts[part].delim, ch)) {
state = afterdelim;
savepart = TRUE;
}
else if (! isspace(ch)) {
return FALSE;
}
break;
}
}
else {
savepart = TRUE;
}
if (savepart) {
switch (parts[part].part) {
case DT_year :
if (no_of_digits == 8 &&
(strchr(parts[DT_day].delim, ch) || ix == stringlen)) {
dtoffset.year = num / 10000;
dtoffset.month = (num % 10000) / 100;
dtoffset.day = num % 100;
part = DT_hour;
}
else if (no_of_digits == 4 && ch == '-') {
dtoffset.year = num;
part = DT_month;
}
else if (no_of_digits <= 2 && ch == ':') {
dtoffset.hour = num;
part = DT_minute;
}
else {
return FALSE;
}
break;
case DT_month :
dtoffset.month = num;
part = DT_day;
break;
case DT_day :
dtoffset.day = num;
part = DT_hour;
break;
case DT_hour :
dtoffset.hour = num;
part = DT_minute;
break;
case DT_minute :
dtoffset.minute = num;
part = DT_second;
break;
case DT_second :
dtoffset.second = num;
part = DT_fraction;
break;
case DT_fraction :
dtoffset.fraction = num * pow10(9 - no_of_digits);
part = DT_tzhour;
break;
case DT_tzhour :
case DT_tzminute :
return FALSE;
}
// If T is the day delimiter, there must be hour and seconds. But if
// there is a Z, the string must have no more parts.
if (ch == 'T') {
parts[DT_day].OK_as_final = FALSE;
}
else if (ch == 'Z') {
// This is tricker than it looks, because after Z we are in the
// the state afterdelim, and normally in this state, end-of-string
// is not permitted. So this is an ugly trick.
sprintf_s(parts[DT_hour].delim, 1, "");
parts[DT_hour].maxdigits = 0;
state = beforedelim;
parts[DT_hour].OK_as_final = TRUE;
parts[DT_minute].OK_as_final = FALSE;
}
savepart = FALSE;
num = 0;
}
}
// If we came here, it's OK, if we have a good part and we are in a good
// state. The part is the part after the last part we found.
return ((state == indigits || state == beforedelim) &&
parts[part-1].OK_as_final);
}
// Converts an SV that is known to be a string to DBTIMESTAMPOFFSET, which is
// a catch-all for all datetime data types.
static BOOL SVstr_to_datetimetypes (SV * sv,
DBTYPE typeind,
DBTIMESTAMPOFFSET &dtoffset,
BOOL &hastz)
{
STRLEN orglen;
char * orgstr = SvPV(sv, orglen);
char * copystr;
STRLEN copylen;
BSTR bstr;
DBLENGTH bstrlen;
DATE dateval;
HRESULT ret;
// We work with a copy of the perl string, because we do weed out the
// time-zone we need to mainpulate the string, and we don't want to
// change the callers value.
New(902, copystr, orglen + 1, char);
memcpy(copystr, orgstr, orglen + 1);
copylen = orglen;
// See if there is an time-zone indicator at the end of the string.
// if get_time_zone finds one, it will update the string and the
// string length, to get the time zone out of the equation.
hastz = get_time_zone (copystr, copylen, dtoffset);
// Parse the string for ISO format, and be content if it fits.
if (parse_iso_string(copystr, copylen, dtoffset)) {
Safefree(copystr);
return TRUE;
}
// ISO did not work out and we will try regional format. This requires
// a BSTR.
bstr = char_to_BSTR(copystr, copylen, TRUE, &bstrlen);
Safefree(copystr); // Not needed any more.
// We use VarDateFromStr rather than IDataConvert, as IDataConvert tends
// to accept junk at the end of the string. This is bad if the user have
// an incorrectly formated TZ-offset. This gives a float vbalue.
ret = VarDateFromStr(bstr, NULL, NULL, &dateval);
SysFreeString(bstr);
// If that failed, we have to give up.
if (FAILED(ret))
return FALSE;
// Step 2, convert the float value to DBTYPE_DBTIMESTAMP.
ret = data_convert_ptr->DataConvert(
DBTYPE_DATE, DBTYPE_DBTIMESTAMP, sizeof(DATE), NULL,
&dateval, &dtoffset, NULL, DBSTATUS_S_OK, NULL,
NULL, NULL, 0);
return SUCCEEDED(ret);
}
static DBTIMESTAMPOFFSET default_dtoffset(void) {
DBTIMESTAMPOFFSET dtoffset;
dtoffset.year = 1899;
dtoffset.month = 12;
dtoffset.day = 30;
dtoffset.hour = 0;
dtoffset.minute = 0;
dtoffset.second = 0;
dtoffset.fraction = 0;
dtoffset.timezone_hour = 0;
dtoffset.timezone_minute = 0;
return dtoffset;
}
static BOOL illegal_dateval(datetime_key_enum part,
int value,
int minval,
int maxval,
SV * olle_ptr)
{
if (value < minval || value > maxval) {
olledb_message(olle_ptr, -1, 1, 10,
L"Part '%S' in datetime value has illegal value %d.",
datetime_keys[part], value);
return TRUE;
}
else {
return FALSE;
}
}
// This helper routine is used to validate data in a datetimeoffet record.
static BOOL validate_dtoffset(DBTIMESTAMPOFFSET &dtoffset,
SV * olle_ptr,
int firstyear,
int lastyear,
BYTE scale)
{
if (illegal_dateval(DT_year, dtoffset.year, firstyear, lastyear, olle_ptr))
return FALSE;
if (illegal_dateval(DT_month, dtoffset.month, 1, 12, olle_ptr))
return FALSE;
int lastday = 31;
switch (dtoffset.month) {
case 1 :
case 3 :
case 5 :
case 7 :
case 8 :
case 10 :
case 12 :
lastday = 31;
break;
case 4 :
case 6 :
case 9 :
case 11 :
lastday = 30;
break;
case 2 :
// Yes, we ignore that some whole centuries are not leap years.
// We are after all only trying to avoid un ugly error message.
lastday = (dtoffset.year % 4 == 0 ? 29 : 28);
break;
}
if (illegal_dateval(DT_day, dtoffset.day, 1, lastday, olle_ptr))
return FALSE;
if (illegal_dateval(DT_hour, dtoffset.hour, 0, 23, olle_ptr))
return FALSE;
if (illegal_dateval(DT_minute, dtoffset.minute, 0, 59, olle_ptr))
return FALSE;
if (illegal_dateval(DT_second, dtoffset.second, 0, 59, olle_ptr))
return FALSE;
if (illegal_dateval(DT_fraction, dtoffset.fraction, 0, 999999999, olle_ptr))
return FALSE;
// OLE DB does not like if there are two decimals. We are more permissive
// and don't flag this as an error, but truncate instead.
if (scale == 0) {
dtoffset.fraction = 0;
}
else {
LONG factor = pow10(7 - scale + 2);
dtoffset.fraction = (dtoffset.fraction / factor) * factor;
}
if (illegal_dateval(DT_tzhour, dtoffset.timezone_hour, -14, 14, olle_ptr))
return FALSE;
int firstval = (dtoffset.timezone_hour <= 0 ? -59 : 0);
int lastval = (dtoffset.timezone_hour >= 0 ? 59 : 0);
if (illegal_dateval(DT_tzminute, dtoffset.timezone_minute,
firstval, lastval, olle_ptr))
return FALSE;
// If we come here, all is OK.
return TRUE;
}
// This is a generic function that handle all that is common to the datetime
// types. The value is returned in a DBTIMESTAMPOFFSET struct, since this struct
// covers all types.
static BOOL SV_to_datetimetypes (SV * sv,
DBTYPE typeind,
BYTE scale,
DBTIMESTAMPOFFSET &dtoffset,
BOOL &hastz,
SV * olle_ptr,
int firstyear = 1,
int lastyear = 9999)
{
// Assume that no time-zone has been given, no matter the type.
hastz = FALSE;
// Set defaults
dtoffset = default_dtoffset();
if (SvROK(sv)) {
BOOL ispresent[no_of_datetime_keys];
// Fork of HV_to_datetimetypes to crack the supposed hash.
if (! HV_to_datetimetypes(sv, typeind, dtoffset, ispresent, olle_ptr)) {
return FALSE;
}
hastz = ispresent[DT_tzhour];
}
else if (SvNOK(sv) || SvIOK(sv)) {
// A float value. This is easy. Just convert to DBTIMESTAMP and smile.
DATE dateval = SvNV(sv);
HRESULT ret;
ret = data_convert_ptr->DataConvert(
DBTYPE_DATE, DBTYPE_DBTIMESTAMP, sizeof(DATE),
NULL, &dateval, &dtoffset, NULL, DBSTATUS_S_OK, NULL,
NULL, NULL, 0);
if (FAILED(ret)) return FALSE;
}
else {
// Looks like it is a string. At least we treat it as such.
if (! SVstr_to_datetimetypes(sv, typeind, dtoffset, hastz)) {
return FALSE;
}
}
// We need to validate the values in dtoffset. Well, actually we don't,
// because OLE DB is going to yell on illegal values. But it will not
// tell us what is wrong, or even which parameter. So let's test ourselves.
return validate_dtoffset(dtoffset, olle_ptr, firstyear, lastyear, scale);
}
BOOL SV_to_date (SV * sv,
DBDATE &date,
SV * olle_ptr)
{
DBTIMESTAMPOFFSET dtoffset;
BOOL hastz;
BOOL ret;
ret = SV_to_datetimetypes(sv, DBTYPE_DBDATE, 0, dtoffset, hastz, olle_ptr);
date.year = dtoffset.year;
date.month = dtoffset.month;
date.day = dtoffset.day;
return ret;
}
BOOL SV_to_time (SV * sv,
BYTE scale,
DBTIME2 &time,
SV * olle_ptr)
{
DBTIMESTAMPOFFSET dtoffset;
BOOL hastz;
BOOL ret;
ret = SV_to_datetimetypes(sv, DBTYPE_DBTIME2, scale, dtoffset, hastz, olle_ptr);
time.hour = dtoffset.hour;
time.minute = dtoffset.minute;
time.second = dtoffset.second;
time.fraction = dtoffset.fraction;
return ret;
}
BOOL SV_to_datetime(SV * sv,
BYTE scale,
DBTIMESTAMP &datetime,
SV * olle_ptr,
int firstyear,
int lastyear)
{
DBTIMESTAMPOFFSET dtoffset;
BOOL hastz;
BOOL ret;
ret = SV_to_datetimetypes(sv, DBTYPE_DBTIMESTAMP, scale,
dtoffset, hastz, olle_ptr, firstyear, lastyear);
datetime.year = dtoffset.year;
datetime.month = dtoffset.month;
datetime.day = dtoffset.day;
datetime.hour = dtoffset.hour;
datetime.minute = dtoffset.minute;
datetime.second = dtoffset.second;
datetime.fraction = dtoffset.fraction;
return ret;
}
BOOL SV_to_datetimeoffset(SV * sv,
BYTE scale,
tzinfo TZOffset,
DBTIMESTAMPOFFSET &dtoffset,
SV * olle_ptr)
{
BOOL hastz;
BOOL ret;
ret = SV_to_datetimetypes(sv, DBTYPE_DBTIMESTAMPOFFSET, scale,
dtoffset, hastz, olle_ptr);
// If there is no timezone in the value, get it from TZOffset.
if (! hastz) {
if (TZOffset.inuse) {
dtoffset.timezone_hour = TZOffset.sign * TZOffset.hour;
dtoffset.timezone_minute = TZOffset.sign * TZOffset.minute;
}
else {
// Go for UTC.
dtoffset.timezone_hour = 0;
dtoffset.timezone_minute = 0;
}
}
return ret;
}
// This routine is called from SV_to_ssvariant to see if the SV may be a
// datetime hash. It returns false, if it looks close enough but is in
// error. But if the hash is something completely different we return TRUE,
// but don't set the variant.
BOOL SV_to_ssvariant_datetime(SV * sv,
SSVARIANT &variant,
SV * olle_ptr,
provider_enum provider)
{
// Get the value as a DBTIMESTAMPOFFSET, and which fields that actually was there.
DBTIMESTAMPOFFSET dtoffset = default_dtoffset();
BOOL ispresent[no_of_datetime_keys];
if (HV_to_datetimetypes(sv, DBTYPE_SQLVARIANT, dtoffset, ispresent,
olle_ptr)) {
if (provider >= provider_sqlncli10 && OptSqlVersion(olle_ptr) >= 10) {
// If we have SQL 2008 and SQL Native Client 10, then we have the
// full range of data types available.
if (! validate_dtoffset(dtoffset, olle_ptr, 1, 9999, 7)) {
return FALSE;
}
if (ispresent[DT_year] && ispresent[DT_month] && ispresent[DT_day]) {
if (ispresent[DT_tzhour]) {
variant.vt = VT_SS_DATETIMEOFFSET;
variant.DateTimeOffsetVal.tsoDateTimeOffsetVal = dtoffset;
variant.DateTimeOffsetVal.bScale = 7;
}
else if (ispresent[DT_hour] || ispresent[DT_minute] ||
ispresent[DT_second] || ispresent[DT_fraction]) {
variant.vt = VT_SS_DATETIME2;
variant.DateTimeVal.tsDateTimeVal.year = dtoffset.year;
variant.DateTimeVal.tsDateTimeVal.month = dtoffset.month;
variant.DateTimeVal.tsDateTimeVal.day = dtoffset.day;
variant.DateTimeVal.tsDateTimeVal.hour = dtoffset.hour;
variant.DateTimeVal.tsDateTimeVal.minute = dtoffset.minute;
variant.DateTimeVal.tsDateTimeVal.second = dtoffset.second;
variant.DateTimeVal.tsDateTimeVal.fraction = dtoffset.fraction;
variant.DateTimeVal.bScale = 7;
}
else {
variant.vt = VT_SS_DATE;
variant.dDateVal.year = dtoffset.year;
variant.dDateVal.month = dtoffset.month;
variant.dDateVal.day = dtoffset.day;
}
}
else if (ispresent[DT_hour] && ispresent[DT_minute] &&
! ispresent[DT_year] && ! ispresent[DT_month] &&
! ispresent[DT_day]) {
variant.vt = VT_SS_TIME2;
variant.Time2Val.tTime2Val.hour = dtoffset.hour;
variant.Time2Val.tTime2Val.minute = dtoffset.minute;
variant.Time2Val.tTime2Val.second = dtoffset.second;
variant.Time2Val.tTime2Val.fraction = dtoffset.fraction;
variant.Time2Val.bScale = 7;
}
}
else {
// Legacy provider or an earlier version of SQL Server. Only
// datetime supported.
if (ispresent[DT_year] && ispresent[DT_month] && ispresent[DT_day]) {
if (! validate_dtoffset(dtoffset, olle_ptr, 1753, 9999, 3)) {
return FALSE;
}
variant.vt = VT_SS_DATETIME;
variant.tsDateTimeVal.year = dtoffset.year;
variant.tsDateTimeVal.month = dtoffset.month;
variant.tsDateTimeVal.day = dtoffset.day;
variant.tsDateTimeVal.hour = dtoffset.hour;
variant.tsDateTimeVal.minute = dtoffset.minute;
variant.tsDateTimeVal.second = dtoffset.second;
variant.tsDateTimeVal.fraction = dtoffset.fraction;
}
}
}
return TRUE;
}
//---------------------------------------------------------------------
// Here follows routines for converting datetime values from SQL Server
// to SV.s.
//---------------------------------------------------------------------
static SV * datetimetypes_to_SV (SV * olle_ptr,
DBTIMESTAMPOFFSET datetime,
DBTYPE datatype,
formatoptions opts,
BYTE precision,
BYTE scale)
{
// This routine handles all datetime data types: date, time, datetime(2)
// and datetimeoffset. The value is passed as DBTIMESTAMPOFFSET, as this
// type as all fields used by the other types.
SV * perl_value;
// IDataConvert does not support the SQL Server-specific DBTYPE_DBTIME2 and
// DBTYPE_DBTIMESTAMPOFFSET, so we need to use DBTIMEOFFSET for these.
DBTYPE typeind = (datatype == DBTYPE_DATE ? datatype : DBTYPE_DBTIMESTAMP);
int typesize = (datatype == DBTYPE_DATE ? sizeof(DBDATE)
: sizeof(DBTIMESTAMP));
// Because of the type issue, we need to modify the precision to work with
// time and datetimeoffset.
if (datatype == DBTYPE_DBTIME2) {
precision += 11;
}
else if (datatype == DBTYPE_DBTIMESTAMPOFFSET) {
precision -= 7;
}
// For dates there is a multitude of options.
switch (opts.DatetimeOption) {
case dto_hash : {
HV * hv = newHV();
if (datatype != DBTYPE_DBTIME2) {
SV * year = newSViv(datetime.year);
SV * month = newSViv(datetime.month);
SV * day = newSViv(datetime.day);
hv_store(hv, datetime_keys[DT_year],
(I32) strlen(datetime_keys[DT_year]), year, 0);
hv_store(hv, datetime_keys[DT_month],
(I32) strlen(datetime_keys[DT_month]), month, 0);
hv_store(hv, datetime_keys[DT_day],
(I32) strlen(datetime_keys[DT_day]), day, 0);
}
if (datatype != DBTYPE_DBDATE) {
SV * hour = newSViv(datetime.hour);
SV * minute = newSViv(datetime.minute);
SV * second = newSViv(datetime.second);
SV * fraction = newSVnv(datetime.fraction / 1000000.0);
hv_store(hv, datetime_keys[DT_hour],
(I32) strlen(datetime_keys[DT_hour]), hour, 0);
hv_store(hv, datetime_keys[DT_minute],
(I32) strlen(datetime_keys[DT_minute]), minute, 0);
hv_store(hv, datetime_keys[DT_second],
(I32) strlen(datetime_keys[DT_second]), second, 0);
hv_store(hv, datetime_keys[DT_fraction],
(I32) strlen(datetime_keys[DT_fraction]), fraction, 0);
}
if (datatype == DBTYPE_DBTIMESTAMPOFFSET) {
SV * TZhour = newSViv(datetime.timezone_hour);
SV * TZminute = newSViv(datetime.timezone_minute);
hv_store(hv, datetime_keys[DT_tzhour],
(I32) strlen(datetime_keys[DT_tzhour]), TZhour, 0);
hv_store(hv, datetime_keys[DT_tzminute],
(I32) strlen(datetime_keys[DT_tzminute]), TZminute, 0);
}
perl_value = newSV(NULL);
sv_setsv(perl_value, sv_2mortal(newRV_noinc((SV *) hv)));
}
break;
case dto_iso : {
DBLENGTH strlen;
char str[35];
DBSTATUS strstatus;
HRESULT ret;
ret = data_convert_ptr->DataConvert(
typeind, DBTYPE_STR, typesize, &strlen, &datetime,
&str, 35, DBSTATUS_S_OK, &strstatus, precision, scale, 0);
check_convert_errors("Convert datetime-to-str", strstatus, ret);
// DataConvert does not fill in msecs if they are zero.
if (precision > 19 && strlen == 19) {
sprintf_s(&str[19], 15, ".0000000");
}
// Post-manipulate the string for time and timeoffset.
if (datatype == DBTYPE_DBTIME2) {
perl_value = newSVpvn(&str[11], precision - 11);
}
else if (datatype == DBTYPE_DBTIMESTAMPOFFSET) {
sprintf_s(&str[precision], 35 - precision, " %+2.2d:%2.2d",
datetime.timezone_hour, abs(datetime.timezone_minute));
perl_value = newSVpvn(str, precision + 7);
}
else {
perl_value = newSVpvn(str, precision);
}
}
break;
case dto_regional : {
// This conversion requires a double conversion. First to DATE.
// and then to string.
DATE dateval;
BSTR bstr;
DBSTATUS dbstatus;
HRESULT ret;
DWORD bstr_flags;
ret = data_convert_ptr->DataConvert(
typeind, DBTYPE_DATE, typesize, NULL, &datetime,
&dateval, sizeof(DATE), DBSTATUS_S_OK, &dbstatus,
precision, scale, 0);
check_convert_errors("Convert datetime-to-date", dbstatus, ret);
if (datatype == DBTYPE_DBDATE) {
bstr_flags = VAR_DATEVALUEONLY;
}
else if (datatype == DBTYPE_DBTIME2) {
bstr_flags = VAR_TIMEVALUEONLY;
}
else {
bstr_flags = 0;
}
ret = VarBstrFromDate(dateval, 0, bstr_flags, &bstr);
check_convert_errors("Convert date-to-str", dbstatus, ret);
perl_value = BSTR_to_SV(bstr);
SysFreeString(bstr);
// For a datetimeoffset value, we should add the timezone.
if (datatype == DBTYPE_DBTIMESTAMPOFFSET) {
STRLEN strlen = SvCUR(perl_value);
char *str = SvGROW(perl_value, strlen + 10);
SvCUR_set(perl_value, strlen + 7);
sprintf_s(&str[strlen], 10, " %+2.2d:%2.2d",
datetime.timezone_hour, abs(datetime.timezone_minute));
}
}
break;
case dto_float : {
DATE dateval;
DBSTATUS dbstatus;
HRESULT ret;
ret = data_convert_ptr->DataConvert(
typeind, DBTYPE_DATE, typesize, NULL, &datetime,
&dateval, sizeof(DATE), DBSTATUS_S_OK, &dbstatus,
NULL, NULL, 0);
check_convert_errors("Convert datetime-to-date", dbstatus, ret);
// For time the date sent into us was 1900-01-01, which gives
// value between 2 and 3, since OLE DB day 0 is 1899-12-30.
if (datatype == DBTYPE_DBTIME2) {
dateval -= 2;
}
perl_value = newSVnv(dateval);
}
break;
case dto_strfmt : {
// For this format, there is no special handling for time or datetimeoffset
struct tm tm_date;
size_t len;
size_t msec_len = 0;
char str[256];
if (opts.DateFormat == NULL || ! *opts.DateFormat) {
olle_croak(olle_ptr, "Datetime option set to dt_strfmt, but there is no format defined");
}
// Move over data to the tm_struct.
tm_date.tm_hour = datetime.hour;
tm_date.tm_isdst = 0; // Seriously, we don't know.
tm_date.tm_mday = datetime.day;
tm_date.tm_min = datetime.minute;
tm_date.tm_mon = datetime.month - 1;
tm_date.tm_sec = datetime.second;
tm_date.tm_wday = 0;
tm_date.tm_yday = 0;
tm_date.tm_year = datetime.year - 1900;
// Convert the beast
len = strftime(str, 256, opts.DateFormat, &tm_date);
if (len <= 0) {
olle_croak(olle_ptr, "strftime failed for dateFormat '%s'", opts.DateFormat);
}
// Are we also requested to format milliseconds?
if (scale > 0 && opts.MsecFormat && * opts.MsecFormat) {
msec_len = _snprintf_s(&str[len], 256 - len, _TRUNCATE,
opts.MsecFormat,
datetime.fraction / 1000000);
if (msec_len <= 0) {
olle_croak(olle_ptr, "sprintf_s failed for msecFormat '%s'", opts.MsecFormat);
}
}
perl_value = newSVpv(str, len + msec_len);
}
break;
default :
olle_croak(olle_ptr, "Illegal value for DatetimeOption %d", opts.DatetimeOption);
}
return perl_value;
}
SV * date_to_SV (SV * olle_ptr,
DBDATE dateval,
formatoptions opts)
{
DBTIMESTAMPOFFSET dtoffset = {1900,1,1,0,0,0,0,0,0};
dtoffset.year = dateval.year;
dtoffset.month = dateval.month;
dtoffset.day = dateval.day;
return datetimetypes_to_SV(olle_ptr, dtoffset, DBTYPE_DBDATE,
opts, 10, 0);
}
SV * time_to_SV (SV * olle_ptr,
DBTIME2 timeval,
formatoptions opts,
BYTE precision,
BYTE scale)
{
DBTIMESTAMPOFFSET dtoffset = {1900,1,1,0,0,0,0,0,0};
dtoffset.hour = timeval.hour;
dtoffset.minute = timeval.minute;
dtoffset.second = timeval.second;
dtoffset.fraction = timeval.fraction;
return datetimetypes_to_SV(olle_ptr, dtoffset, DBTYPE_DBTIME2,
opts, precision, scale);
}
SV * datetime_to_SV (SV * olle_ptr,
DBTIMESTAMP datetime,
formatoptions opts,
BYTE precision,
BYTE scale)
{
DBTIMESTAMPOFFSET dtoffset = {1900,1,1,0,0,0,0,0,0};
dtoffset.year = datetime.year;
dtoffset.month = datetime.month;
dtoffset.day = datetime.day;
dtoffset.hour = datetime.hour;
dtoffset.minute = datetime.minute;
dtoffset.second = datetime.second;
dtoffset.fraction = datetime.fraction;
return datetimetypes_to_SV(olle_ptr, dtoffset, DBTYPE_DBTIMESTAMP,
opts, precision, scale);
}
SV * datetimeoffset_to_SV (SV * olle_ptr,
DBTIMESTAMPOFFSET dtoffset,
formatoptions opts,
BYTE precision,
BYTE scale)
{
if (! opts.TZOffset.inuse) {
// Simple, just pass the bucket.
return datetimetypes_to_SV(olle_ptr, dtoffset, DBTYPE_DBTIMESTAMPOFFSET,
opts, precision, scale);
}
else {
// If TZOffset is set, then we modify the value, and pretend that we got
// a datetime2 value.
DBTIMESTAMP valuecopy;
DBTIMESTAMP tzvalue;
DBTIMESTAMP tzoption;
int tzvaluesign;
DATE valuefloat;
DATE valuetzfloat;
DATE optionfloat;
HRESULT ret;
DBSTATUS dbstatus;
// Copy the value from SQL Server, but skip seconds and fractions.
valuecopy.year = dtoffset.year;
valuecopy.month = dtoffset.month;
valuecopy.day = dtoffset.day;
valuecopy.hour = dtoffset.hour;
valuecopy.minute = dtoffset.minute;
valuecopy.second = 0;
valuecopy.fraction = 0;
// And get the time-zone offset into separate DBTIMESTAMP value with
// with OLE DB's zero date. We need to store the sign of the time zone
// separately.
tzvalue.year = 1899;
tzvalue.month = 12;
tzvalue.day = 30;
tzvalue.hour = abs(dtoffset.timezone_hour);
tzvalue.minute = abs(dtoffset.timezone_minute);
tzvalue.second = 0;
tzvalue.fraction = 0;
tzvaluesign = (dtoffset.timezone_hour < 0 ||
dtoffset.timezone_minute < 0) ? -1 : 1;
// And the same for the TZOption, but here the sign is already separated
// for us.
tzoption.year = 1899;
tzoption.month = 12;
tzoption.day = 30;
tzoption.hour = opts.TZOffset.hour;
tzoption.minute = opts.TZOffset.minute;
tzoption.second = 0;
tzoption.fraction = 0;
// Now we convert these three to float.
ret = data_convert_ptr->DataConvert(
DBTYPE_DBTIMESTAMP, DBTYPE_DATE, sizeof(DBTIMESTAMP), NULL,
&valuecopy, &valuefloat, sizeof(DATE), DBSTATUS_S_OK,
&dbstatus, NULL, NULL, 0);
check_convert_errors("Convert datetimeoffset-to-date", dbstatus, ret);
ret = data_convert_ptr->DataConvert(
DBTYPE_DBTIMESTAMP, DBTYPE_DATE, sizeof(DBTIMESTAMP), NULL,
&tzvalue, &valuetzfloat, sizeof(DATE), DBSTATUS_S_OK,
&dbstatus, NULL, NULL, 0);
check_convert_errors("Convert time zone to date", dbstatus, ret);
ret = data_convert_ptr->DataConvert(
DBTYPE_DBTIMESTAMP, DBTYPE_DATE, sizeof(DBTIMESTAMP), NULL,
&tzoption, &optionfloat, sizeof(DATE), DBSTATUS_S_OK,
&dbstatus, NULL, NULL, 0);
check_convert_errors("Convert TZOption to date", dbstatus, ret);
// Compute the time in the requesed time-zone. This is messier than it
// looks like because the DATE data type is discontinuous before
// 1899-12-30, so that -2.25 is 1899-12-28 06:00, and not 18:00.
// newtime is the new time portdion.
DATE newtime = abs(valuefloat) - floor(abs(valuefloat)) -
tzvaluesign * valuetzfloat +
opts.TZOffset.sign * optionfloat;
// If newtime is not in [0,1[, we need to modify valuefloat with the
// integer part, with special consideration around 1899-12-30, as
// 0.24 = -0.24 in this context.
if (valuefloat >= 0 && valuefloat + floor(newtime) < 0) {
valuefloat += floor(newtime) - 1;
}
else if (valuefloat < 0 && valuefloat + floor(newtime) >= 0) {
valuefloat += floor(newtime) + 1;
}
else {
valuefloat += floor(newtime);
}
// Remove integer part from newtime.
newtime = newtime - floor(newtime);
// And add newtime to the integer part of valuefloat.
if (valuefloat >= 0) {
valuefloat = floor(valuefloat) + newtime;
}
else {
valuefloat = ceil(valuefloat) - newtime;
}
// And convert back to DBTIMESTAMP
ret = data_convert_ptr->DataConvert(
DBTYPE_DATE, DBTYPE_DBTIMESTAMP, sizeof(DATE), NULL,
&valuefloat, &valuecopy, sizeof(DBTIMESTAMP), DBSTATUS_S_OK,
&dbstatus, NULL, NULL, 0);
check_convert_errors("Convert valuecopy to DBTIMESTAMP ", dbstatus, ret);
// And update the value with the fields that might have change.d
dtoffset.year = valuecopy.year;
dtoffset.month = valuecopy.month;
dtoffset.day = valuecopy.day;
dtoffset.hour = valuecopy.hour;
dtoffset.minute = valuecopy.minute;
// And so call datetimetypes_to_SV for the real conversion, but
// pretent that we have a datetime2 value.
return datetimetypes_to_SV(olle_ptr, dtoffset, DBTYPE_DBTIMESTAMP,
opts, precision - 7, scale - 7);
}
}