OLE DB MS SQL DATETIME_Null

Post found bugs and possible workarounds.
MSchmidt
Germany
Posts: 358
Joined: 03 Jul 2017, 09:28
Location: Germany

OLE DB MS SQL DATETIME_Null

Post by MSchmidt » 20 Nov 2008, 01:04

TD 5.1 OLE DB to SQL Server 2005

when we make an update or insert to a column defined as datetime we have problems when the corresponding bind variable
has no value .
We tried to set the var to DATETIME_Null but the effect is the same.

Maybe there is a Set Parameter for the SQL Server to prevent this.

so it would be nice if someone has experience with OLEDB

when we use ODBC all is working fine

MSchmidt
Germany
Posts: 358
Joined: 03 Jul 2017, 09:28
Location: Germany

OLE DB MS SQL DATETIME_Null

Post by MSchmidt » 21 Nov 2008, 09:06

sorry but we need help

UVS

OLE DB MS SQL DATETIME_Null

Post by UVS » 21 Nov 2008, 12:26

Way back I ran into a problem with passing NULL dates to stored procedures using ODBC. Took me awhile to figure out why the field was coming up 1/1/1900, yet the DB date time column was set to NULL's.

So I put code in to check for STRING_Null, and if null, then concatenated the string "NULL" into the SQL update code.

Code: Select all

If NOT dtDSTRCTN_DATE = DATETIME_Null
   Call SalDateToStr( dtDSTRCTN_DATE, strDSTRCTN_DATE)
   Set strDSTRCTN_DATE = '\'' || SalStrLeftX( SalStrRightX( strDSTRCTN_DATE, 21), 6)
               || SalStrLeftX( strDSTRCTN_DATE, 4) || '\''
Else
   Set strDSTRCTN_DATE = 'NULL'
Then concatenate your date string into the SQL String:

Code: Select all

Set strCommand = '
      declare @nItemID int
      select @nItemID =  '|| SalNumberToStrX( nITEMID,0 )  ||
      ' Exec   Update_ITEM  \'' ||
      strINITIAL_RCPT_DATE || ', ' ||
      strDSTRCTN_DATE || ', \'' ||
      @nItem_Nmbr = @nITEMID OUTPUT
      Select    @nITEMID'
Call OdrPrepareProc( hSqlUpdateItem, strCommand, ':strIntoList' )
Call SqlExecute (hSqlUpdateItem)
As you can see I also then made sure the string data for a real date was formatted correctly for SQL Server. This might be too much if you need to change code in a lot of places. I had it running in a Function.

Martin

MSchmidt
Germany
Posts: 358
Joined: 03 Jul 2017, 09:28
Location: Germany

OLE DB MS SQL DATETIME_Null

Post by MSchmidt » 26 Nov 2008, 14:21

now we've found the reason why!

if you format a datetime field as TIME or DATE the error occurs.
(only when the field has no value and when you use OLEDB)

after we changed the field to dd.MM.yy all is running well.

so unify please change this in the next release!
our customers want to have their own format of date and time configured in windows)


(German XP , windows system configuration for time: 23:02:48 and for date: 26.11.2008)

Jean-Marc Gemperle

OLE DB MS SQL DATETIME_Null

Post by Jean-Marc Gemperle » 22 Dec 2008, 05:26

Helo,

Indeed only WITH OLEDB and DF formated as DATE or TIME. OK via ODBC. Also OK with ORACLE NATIVE/OLEDB

TD-5576
OLEDB SQLSERVER error "Data Conversion failed" when insert/update of 'empty' DateTime datafield (DATE_Null) formated as DATE or TIME bind variable (DATE_Null)

JM

MSchmidt
Germany
Posts: 358
Joined: 03 Jul 2017, 09:28
Location: Germany

OLE DB MS SQL DATETIME_Null

Post by MSchmidt » 20 Feb 2009, 17:07

TD-5576
OLEDB SQLSERVER error "Data Conversion failed" when insert/update of 'empty' DateTime datafield (DATE_Null) formated as DATE or TIME bind variable (DATE_Null)

we have lots of countries in europe
nearly each country has an own format of time and date

so this is not a "nice to to have "
it is a must!

since maybe 15 years it is a standard in windows, that a user can choose his own standard format for time and date.

we've messaged the bug in november. in Dezember it was made to TD-5576

we have lots of datetime fields. And in many cases the fields could be empty, cause they were filled out later or never.

so it's time for SP6

M.Schmidt

Ana Paula Bonani

OLE DB MS SQL DATETIME_Null

Post by Ana Paula Bonani » 23 Feb 2009, 16:49

Hi,

This has been targeted for a fix in TD5.2.

Please send me a private message in case this presents a business impact for you at this point.

Thanks,

tlauzi

OLE DB MS SQL DATETIME_Null

Post by tlauzi » 24 Feb 2009, 01:42

Hello,

this does also happen on ORACLE and it seems to be a general problem in the router.
This prevented us from using OLEDB instead of native routers, because we wern´t able to insert empty dates.

So please fix it for ALL kind of DB types.

Regards,
Thomas L.

Ana Paula Bonani

OLE DB MS SQL DATETIME_Null

Post by Ana Paula Bonani » 24 Feb 2009, 18:58

Hi Thomas,

This defect is specific to SQL Server OLEDB so it's better that we log a separate one for Oracle and link them internally.

Could you please provide an Oracle OLEDB testcase just to be safe?

Thanks,

MSchmidt
Germany
Posts: 358
Joined: 03 Jul 2017, 09:28
Location: Germany

OLE DB MS SQL DATETIME_Null

Post by MSchmidt » 16 Apr 2009, 07:12

no solution in SP6

error was described in november 2008

and now we should wait and pay for TD 5.2

please change the product name from team- to waitdevelopper.

but please please we don't want to have new functions.

we only want to have a version of TD stable as 1.51 but with unicode and a better look and feel.

now we have a version of TD: very slow (loading,compilation and at runtime), not stable and with some bugs inside, unify is not able to change.

Ana Paula Bonani

OLE DB MS SQL DATETIME_Null

Post by Ana Paula Bonani » 20 Apr 2009, 08:05

Hello mschmidt,

I'll send you a private message about this issue so we can come up with a solution for you.

If you don't receive it for some reason please feel free to send me a message with your phone number and I'll call you back.

Thanks,

Kitchman

OLE DB MS SQL DATETIME_Null

Post by Kitchman » 25 Feb 2010, 18:26

Same issue with OLE DB and ORACLE.

But, I found a tip.

If the name of my field is df1 (with property Date), I write :

If df1 = DATETIME_Null
Set dDate = DATETIME_Null
Else
Set dDate = df1

Then : UPDATE TABLE SET TABLE_DATE = :dDate ....

I know, it's stupid, but it works... :wink:

MSchmidt
Germany
Posts: 358
Joined: 03 Jul 2017, 09:28
Location: Germany

OLE DB MS SQL DATETIME_Null

Post by MSchmidt » 25 Feb 2010, 19:06

as described in November 2008 (not 2009) it depennds on the datafield settings.

Please control your settings.

In my app the field or column was formated as date/time (so windows controls the format)

in Germany f.e.: 24.02.2010

the error occurs when the field or column has no value.

if you format the field f.e. dd.MM.YYYY all is working fine even if the field has no value.

maybe the problem is fixed in 5.2 or in SP1 of 5.2
It would be nice to hear something about this problem

Ana Paula Bonani

OLE DB MS SQL DATETIME_Null

Post by Ana Paula Bonani » 02 Mar 2010, 02:28

Hi All,
maybe the problem is fixed in 5.2 or in SP1 of 5.2
It would be nice to hear something about this problem
The specific issue reproduced by Unify on this thread (TD-5576) is confirmed fixed in TD5.2 base release.

If you can still reproduce it with this version please create a new thread and provide a new reprocase.

Thanks in advance,

Return to “Bug Reports”

Who is online

Users browsing this forum: [Ccbot] and 0 guests