OLEDB and Dates and Timestamps

[Craig Wahlmeier]

Am I the only one in the world that selects dates and timestamps with
OLEDB?
V8 of UDB has brought me a big problem.

The PATCH2=24 setting no longer works the way it used to. The
settings of MAPDATEDESCRIBE=1, MAPTIMEDESCRIBE=1, and
MAPTIMESTAMPDESCRIBE=1 allow me to reverse that V8 change, almost.
Before V8, the format of a date column returned in OLEDB was based
upon the regional settings of the client. Now, it returns it in
YYYY-MM-DD. Guess what all my web servers have for their regional
settings! MM-DD-YYYY! Can you imagine the mass hysteria and peasant
land seizure that I am experiencing!

I see no mention of this on the message board. Is there something I
am missing? Does anyone have a slick way of dealing with timestamps,
dates, and times in OLEDB or ADO?

TIA,

Craig Wahlmeier
Senior Technical Administrator - Database Systems
Data-Tronics Corp.

[Mark Yudkin]

Define the datatype of your target variable as a date / timestamp variable;
then you'll get the native format (e.g., for VB6, you get a Date). You
problem only arises as your'e attempting to convert to a string
representation.

"Craig Wahlmeier" <cw********@data-tronics.com> wrote in message
news:ac**************************@posting.google.c om...

Am I the only one in the world that selects dates and timestamps with
OLEDB?
V8 of UDB has brought me a big problem.

The PATCH2=24 setting no longer works the way it used to. The
settings of MAPDATEDESCRIBE=1, MAPTIMEDESCRIBE=1, and
MAPTIMESTAMPDESCRIBE=1 allow me to reverse that V8 change, almost.
Before V8, the format of a date column returned in OLEDB was based
upon the regional settings of the client. Now, it returns it in
YYYY-MM-DD. Guess what all my web servers have for their regional
settings! MM-DD-YYYY! Can you imagine the mass hysteria and peasant
land seizure that I am experiencing!

I see no mention of this on the message board. Is there something I
am missing? Does anyone have a slick way of dealing with timestamps,
dates, and times in OLEDB or ADO?

TIA,

Craig Wahlmeier
Senior Technical Administrator - Database Systems
Data-Tronics Corp.

[Craig Wahlmeier]

Mark,

Thank you for your reply. We are using VBScript inside of ASP pages.
As you know, VBScript is untyped like REXX. IBM has really made this
V8 conversion a mess! To add insult to injury, I just noticed that
the MAPTIMESTAMPDESCRIBE=1 returns a timestamp that is missing a dash,
e.g. 2001-01-01 00.00.00.000000.

I opened PMR 46073,370 last November. I took me a week to convince
the rep that you could indeed select a timestamp via OLEDB! I figured
out the MAPDATEDESCRIBE myself and closed the PMR. I guess I will
open another. I still cannot believe that we are the only ones
accessing DB2 via OLEDB in Active Server Pages and having a problem.

Thanks,

Craig Wahlmeier
Senior Technical Administrator - Database Systems
Data-Tronics Corp.

[Phil Jackson]

We always select dates as CHAR(MyDateField) and then manipulate in the
ASP code,
as our DBAs like to store dates that have not happened - i.e.
termination_date
for an active employee as 0001-01-01, which gives ADO fits unless you
fetch as a charactor string. Have NEVER had any luck manipulating
timestamps from DB2 via ADO. Can compare timestamps as in ... where
A.ts = B.ts, but never able to execute a query, save ts in an ASP
variable and use again in a query via ASP/ADO...

Phil J.

Mark,

Thank you for your reply. We are using VBScript inside of ASP pages.
As you know, VBScript is untyped like REXX. IBM has really made this
V8 conversion a mess! To add insult to injury, I just noticed that
the MAPTIMESTAMPDESCRIBE=1 returns a timestamp that is missing a dash,
e.g. 2001-01-01 00.00.00.000000.

I opened PMR 46073,370 last November. I took me a week to convince
the rep that you could indeed select a timestamp via OLEDB! I figured
out the MAPDATEDESCRIBE myself and closed the PMR. I guess I will
open another. I still cannot believe that we are the only ones
accessing DB2 via OLEDB in Active Server Pages and having a problem.

Thanks,

Craig Wahlmeier
Senior Technical Administrator - Database Systems
Data-Tronics Corp.

[Mark Yudkin]

I wish you'd said VBScript / ASP up front; I assumed you were using a
compiled language. VBScript is very much a mess. But I'd still like to see
your ADO code for defining the field.

"Craig Wahlmeier" <cw********@data-tronics.com> wrote in message
news:ac**************************@posting.google.c om...

Mark,

Thank you for your reply. We are using VBScript inside of ASP pages.
As you know, VBScript is untyped like REXX. IBM has really made this
V8 conversion a mess! To add insult to injury, I just noticed that
the MAPTIMESTAMPDESCRIBE=1 returns a timestamp that is missing a dash,
e.g. 2001-01-01 00.00.00.000000.

I opened PMR 46073,370 last November. I took me a week to convince
the rep that you could indeed select a timestamp via OLEDB! I figured
out the MAPDATEDESCRIBE myself and closed the PMR. I guess I will
open another. I still cannot believe that we are the only ones
accessing DB2 via OLEDB in Active Server Pages and having a problem.

Thanks,

Craig Wahlmeier
Senior Technical Administrator - Database Systems
Data-Tronics Corp.

[Craig Wahlmeier]

Mark,

Below is some vbscript. On a V7 fp10 client I get this output -
ts = 2001-01-01-00.00.00.000000
dt = 01/01/2001

On a V8 fp4 client, I get this output -
ts = 01/01/2001
dt = 01/01/2001

Both db2cli.ini files have this for the Sample database -
[sample]
DBALIAS=SAMPLE
LOBMAXCOLUMNSIZE=1048575
LONGDATACOMPAT=1
PATCH1=132096
PATCH2=24,6
SYSSCHEMA=SYSIBM
TXNISOLATION=1

If I add MAPTIMESTAMPDESCRIBE=1 to the V8 client, the output looks
like this -
ts = 2001-01-01 00:00:00.000000
dt = 01/01/2001

If the date column has '0001-01-01', then you have to either add
MAPDATEDESCRIBE=1, or take Phil's advice at use the CHAR funtion. I
think Phil's advice is better because the MAPDATEDESCRIBE=1 causes
this output -
ts = 2001-01-01 00:00:00.000000
dt = 2001-01-01

The date field format is no longer based on your regional settings.

Thanks for your input. Here is the script

Craig
<Job id="DB2 SQL Tester">

<script language="VBScript">
Option Explicit
Const ForReading = 1
Const adUseClient = 3
Const adLockReadOnly = 1
Const adOpenForwardOnly = 0

dim rstDB2
dim sSQL
dim cnnDB2
dim sDB2DBname
dim sDB2UserID
dim sDB2Password

sDB2DBname = "SAMPLE"
sDB2UserID = "xxxxxxx"
sDB2Password = "yyyyyy"

Set cnnDB2 = WScript.CreateObject("ADODB.Connection")
Set rstDB2 = WScript.CreateObject("ADODB.Recordset")

sSQL = "Select COL1, COL2 FROM DTC.TABLE1 "
cnnDB2.Provider = "IBMDADB2;dsn=" & sDB2DBname & ";uid=" &
sDB2UserID & ";pwd=" & sDB2Password
cnnDB2.CursorLocation = adUseClient
cnnDB2.Open
rstDB2.Open sSQL,cnnDB2,adOpenForwardOnly,adLockReadOnly
wscript.echo ("ts = " & rstDB2("COL1"))
wscript.echo ("dt = " & rstDB2("COL2"))

rstDB2.close
cnnDB2.close
set cnnDB2 = Nothing
set rstDB2 = Nothing

</Script>
</Job>

[Mark Yudkin]

Try defining the target field as datetime, rather than leaving it to default
(to a string). That way, you'll have a VBA [OLE] Date type rather than a
string, which you can format however you like using the VBA Format$ function
and related routines.

"Craig Wahlmeier" <cw********@data-tronics.com> wrote in message
news:ac**************************@posting.google.c om...

<Job id="DB2 SQL Tester">

<script language="VBScript">
Option Explicit
Const ForReading = 1
Const adUseClient = 3
Const adLockReadOnly = 1
Const adOpenForwardOnly = 0

dim rstDB2
dim sSQL
dim cnnDB2
dim sDB2DBname
dim sDB2UserID
dim sDB2Password

sDB2DBname = "SAMPLE"
sDB2UserID = "xxxxxxx"
sDB2Password = "yyyyyy"

Set cnnDB2 = WScript.CreateObject("ADODB.Connection")
Set rstDB2 = WScript.CreateObject("ADODB.Recordset")

sSQL = "Select COL1, COL2 FROM DTC.TABLE1 "
cnnDB2.Provider = "IBMDADB2;dsn=" & sDB2DBname & ";uid=" &
sDB2UserID & ";pwd=" & sDB2Password
cnnDB2.CursorLocation = adUseClient
cnnDB2.Open
rstDB2.Open sSQL,cnnDB2,adOpenForwardOnly,adLockReadOnly
wscript.echo ("ts = " & rstDB2("COL1"))
wscript.echo ("dt = " & rstDB2("COL2"))

rstDB2.close
cnnDB2.close
set cnnDB2 = Nothing
set rstDB2 = Nothing

</Script>
</Job>

[Craig Wahlmeier]

Mark,

You lost me. VBScript is an untyped language...

Craig

[Mark Yudkin]

In ADO, you define how your recordset's fields tie up by using statements:
myRS.Fields.Append name, type, defined_size, attributes
In your case, you would specify type as adDate.

Since you don't do this yourself, you're getting the default bindings. These
are often not what you want; in your case, they obviously aren't, as you've
complained about it.

Note: we're not talking about declaring VBScript variables, we're talking
about declaring the bindings between the ADO source and the data consumer
(your VBScript), as processed by ADO. Also. VBScript typing is irrelevant as
for data access you're using ADO's recordset to access the field:
myRS!FieldName.

To ask ADO to give you what you want, you have to tell ADO how to do it.
Otherwise it second-guesses, based on the database content (which is fine
for simple types like integers or strings).

"Craig Wahlmeier" <cw********@data-tronics.com> wrote in message
news:ac**************************@posting.google.c om...

Mark,

You lost me. VBScript is an untyped language...

Craig

[Craig Wahlmeier]

I finally got some relief from IBM. I opened pmr 85254,370. If I
apply V8 fixpak 5 to both the client and the server, add patch2=24,33
and maptimestampdescribe=1 to the db2cli.ini, my problem is fixed.
(With one exception.) I don't know if the patch1=131072 is also
required, but I have it.

Now, my date fields are observing the regional settings of my client.
My timestamp fields are displaying as true timestamps. The only
problem I am left with concerns date fields containing 0001-01-01.
Those cause ado/oledb to trap. I could add a mapdatedescribe=1 to fix
that error. But, that changes my date layout away from my regional
settings to yyyy-mm-dd. So, I will be forced to find all the queries
that could be affected and add a CHAR function to the SQL.

Thanks for all your replies. We plan to start using the CHAR function
for date and timestamp columns in all our queries in the future. I
don't know what IBM tests back at the lab, but it isn't OLEDB.

Thanks,

Craig Wahlmeier

[Mark Yudkin]

"Craig Wahlmeier" <cw********@data-tronics.com> wrote in message

I don't know what IBM tests back at the lab, but it isn't OLEDB.

Unfortunately, I fear that you are correct, and it isn't only OLEDB
(although that does seem to be very high on the list of "never test"). I had
a discussion about this with Serge a while back after FP4 introduced a HIPER
APAR (FP4 got replaced by FP4a as a result), but we hit the bug first and
had to open the PMR.