FiveTech Support Forums

Thanks Antonio.

I just posted my suggestion of using a flag to solve the problem on the Developer's forum. It seems to be the only practical solution.

Thanks to all, my friends.

EMG

James,

Yes, I saw your new post, thanks

Enrico,

My pleasure to help you and all Harbour users

I just saw the discussions there. They think the change is requested only for Excel.
No.
All SQL databases expect assignment of NULL for empty dates, whether we use ADO or not,
So we need to convert empty dates as NULLs while assigning through ADO. In other words our OLE library has to convert empty dates as NULLs while assigning.

There was also mention about the limitation of excel about the lowest being 1900-01-01. This is the limitation of other Microsoft SQL databases also. Other SQL DBMS like Oracle, MySql all accept all dates.

Our request is not purely Excel centric as they are discussing.
Issue of dates lower than 1900-01-01 has to be handed by the programmer ( even using pure SQL ), We wanted only empty dates to be assigned as NULLs.

It appears to me that really serious ADO programmers are not still using Harbour.

Mr EMG

Even assuming that Harbour provides the same functionality as that of xHarbour, we still may have to adopt some kind of conditional coding for assignment of Dates if we want our code to be generic ( not targeted to single DBMS only)

Example:
oRs:Fields( "SomeDate" ):Value := STOD( "17000101" )
This works for Oracle, MYSql and all non-Microsoft products.
But errors our for Microsoft products like Excel, MSSQL.

Out generic code needs to be something like this:

<ole/adoref>:Value := If( lMicrosoft .and. ValType( v ) $ "DT" .and Year( v ) < 1900, AdoNull(), v )
( and lMicrosoft is set to .t. for Excel, Access, MSSQL, etc)

There is a reason why I used AdoNull() but not NIL.
In case of MSSQL, assigning "nil" results in assigning 1899-12-30 ( numeric 0 in OLE dates)
So it is safer to use AdoNull() in all cases rather than NIL.

Here is the thread from the xHarbour group that I raised several years ago and the ADO fix for ctod("") writing null to Sql Server .. the same modifications may be able to be ported to Harbour.

https://groups.google.com/forum/?fromgr ... NneK8sbdgJ

Rick Lipkin

Rick Lipkin wrote:Here is the thread from the xHarbour group that I raised several years ago and the ADO fix for ctod("") writing null to Sql Server .. the same modifications may be able to be ported to Harbour.

https://groups.google.com/forum/?fromgr ... NneK8sbdgJ

Rick Lipkin

Dear Mr. Nages,

In my opinion, it is very important that you post your previous responses to the Harbour Developer´s Group.

They will have another very qualified experience from your high profile.

Thank you.

+1

I think posts also should be done in Harbour developers group. Otherwise they wont see this messages.

Thanks

2015-01-14 13:00 UTC+0100 Przemyslaw Czerpak (druzus/at/poczta.onet.pl)
* contrib/hbwin/olecore.c
+ added new PRG function to control empty date conversion to OLE variant:
__oleVariantNullDate( [<lNewNullFlag>] ) -> <lPrevNullFlag>
This is per thread switch and by default initialized to .F. for each
new thread. When set to .T. Harbour empty dates are translated to
VT_NULL.
Warning: it's possible that this function will be removed or replaced
by other solution in the future.

Great! Antonio, can you give me the link to the new Harbour build so I can go on with my tests?

EMG

Up!

EMG