ADODB Filter with Date 1

[Andrzejek]

In my simple application I am trying to do this:
(Cn is my ADODB.Connection to Oracle)

Dim recDate As New ADODB.Recordset

strSQL = "SELECT MyDateField " & vbNewLine _
    & " FROM MyTable " & vbNewLine _
    & " ORDER BY MyDateField"

recDate.Open strSQL, Cn

[highlight #FCE94F]recDate.Filter = "MyDateField = TO_DATE('5/5/2015', 'MM/DD/YYYY')"[/highlight]

[tt]recDate.Filter [/tt] line is creating an error 3001
Arguments are of the wrong type, are out of acceptable range, or are in conflict with one another.

I used Filter many times before and I know it has some problems with NULLs, but how should I use it with Date field? Is there a Filter trick with Date?

Google showed some samples/tricks with DateValue in Access, but I don't have it in Oracle. I even tried to covert the Date to Char (TO_CHAR) but still had the same error.

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[Andrzejek]

With some trial and error, I re-visited my TO_CHAR approach with something like this:

Dim recDate As New ADODB.Recordset

strSQL = "SELECT [blue]TO_CHAR([/blue]MyDateField[blue], 'YYYYMMDD')[/blue] AS MyDateField " & vbNewLine _
    & " FROM MyTable " & vbNewLine _
    & " ORDER BY MyDateField"

recDate.Open strSQL, Cn

recDate.Filter = "MyDateField = [blue]'20150505'[/blue]"

So pretty much I converted my Date to a character field in the format of YYYYMMDD (so I can even do < or > comparison), but there must be a better way to deal with Dates and Filter.... ?

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[strongm]

The filter runs locally, whilst TO_DATE is an instruction to Oracle.

So, why use a filter? Why not put it into the query? Something like
[tt]
strSQL = "SELECT TO_CHAR(MyDateField, 'YYYYMMDD') AS MyDateField " & vbNewLine _
& " FROM MyTable " & vbNewLine _
& WHERE MyDateField = TO_DATE('5/5/2015', 'MM/DD/YYYY')
& " ORDER BY MyDateField"[/tt]

 

[dilettante]

Yes, it normally makes more sense to use a WHERE clause, but:

recDate.Filter = "MyDateField = #5/5/2015#"

The date literal is always in invariant locale (i.e. Earth Standard) format, i.e. MM/DD/YYYY. If you use string delimiters (') it may work though a bit more slowly as it chugs through coercion.

This is documented at MSDN and in your MSDN Library CD docs.

 

[Andrzejek]

So, why use a filter? Why not put it into the query?

Good question.
I work on a report presented in Excel. So if I need to show, let's say, 10 years of data, I would need to go 10 X 12 = 120 times to the DB, once for each month. I want to go only once to DB, grab the info, and then filter it as many times as I need to.

"The filter runs locally, whilst TO_DATE is an instruction to Oracle."
[tt]recDate.Filter = "MyDateField = #5/5/2015#" [/tt]

That got me thinking... Yes, Filter is local, TO_DATE is Oracle, but # looks like Access, but I tried it anyway. Seams to be working.

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[strongm]

># looks like Access

Well, it's a bit more generic to Microsoft langauages than that. Here's the Backus-Naur definition:

[tt]DateLiteral ::= # [ Whitespace+ ] DateOrTime [ Whitespace+ ] #
DateOrTime ::=

[tt]DateValue Whitespace+ TimeValue |
DateValue |
TimeValue
[/tt]​

DateValue ::=

[tt]MonthValue / DayValue / YearValue |
MonthValue – DayValue - YearValue
[/tt]​

TimeValue ::=

[tt]HourValue : MinuteValue [ : SecondValue ] [ WhiteSpace+ ] [ AMPM ]
[/tt]​

MonthValue ::= IntLiteral
DayValue ::= IntLiteral
YearValue ::= IntLiteral
HourValue ::= IntLiteral
MinuteValue ::= IntLiteral
SecondValue ::= IntLiteral
AMPM ::= AM | PM
[/tt]

 

[dilettante]

When your program talks to Oracle it does so through many layers of software. The Filter property does not accept pass-through SQL at all, and if any SQL is involved (e.g. a server-side cursor) then ADO will ask the OLEDB Provider below it to generate that SQL and squirt it at the DBMS.

This can result in excessive round-tripping though, so you might want to tweak a few Recordset properties people often allow to just default. CacheSize for example, though that can be ineffective unless you raise the "Maximum Open Rows" extended property (assuming the Oracle Provider you are using even supports it). Then you can run into concurrency issues by locking excessive rows though... so you must have your wits about you.

All bets are off if you are relying on the MSDASQL "adapter" Provider and thunking through that into an ODBC Driver for Oracle. The more layers the more risk of losing fidelity and flexibility, not to mention performance.

If you are using client-side cursors then all of the filtering gets done locally and Oracle never knows about it.

 

[Andrzejek]

What I have when connecting to Oracle is:

Set Cn = New ADODB.Connection
Cn.ConnectionString = "Driver={Microsoft ODBC for Oracle};..."
Cn.CursorLocation = [blue]adUseClient[/blue]
Cn.Open
...

and I would assume the recordset object 'inherits' the CursorLocation from Connection object, which would make it [tt]adUseClient[/tt]

Thanks to both of you for shading some light on the issue. it is a little 'over my head' but I appreciate it very much.

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[dilettante]

I wouldn't use that crusty old Desktop ODBC Driver, but if you can live with its performance problems, limitations, and it isn't destroying any data I suppose it saves you installing the proper Oracle Client package.

INFO: Limitations of Microsoft Oracle ODBC Driver and OLEDB Provider

I'm not sure why you are using client-side cursors, since they impose numerous limitations. But I guess I wouldn't change it because from what I've found most programmers don't understand what is involved and rely on ancient snippets based on VBScript from the Classic ASP days and probably would have trouble getting their programs to work with server-side cursors. So they just live with the performance problems, high memory consumption, concurrency bottlenecks, and round-tripping overhead on updates - in exchange for a sort of "automatic transmission" that lets them ignore so many things.

If it is working and your users, DBAs, and network guys are not screaming at you I suppose that's ok.

 

[Andrzejek]

Users don't know, and don't care, DBA is fine with it as long as it does not create any problems, network guys don't care (app is run from just 2 Citrix servers) and I wish I would make it a lot better/faster/more efficient, but I am not in charge. And the person in charge (and the manager) will not do it. Period.

But I do appreciate your comments.

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[dilettante]

No complaints here. Nearly all of the VB6 work I get these days is to come in and fix those very sorts of performance and concurrency problems.

So, more contract work for me I suppose.

But these days the shift away from Windows has me doing a lot more work targeting Android and Java servlets. The world has left Microsoft behind.

 

[strongm]

Hmm - whilst it certainly appears to be true (e.g. a quick glance at Gooroo's regular reports on programming languages' salaries and demand) that demand for VB/VBA is down, demand for C# remains high, around about the same as for Java and JavaScript - which would indicate that world hasn't quite left Microsoft behind just yet.

 

[dilettante]

Plenty of Cobol jobs yet too.

 

[waelaswad]

use this string format for date:

m_DateStr as String
m_DateSTr = Format(Now, "yyyy-mm-dd hh:nn:ss")

Wael
VoIP Expert

http://www.tele-finity.com

 

[strongm]

Not quite sure how that would help

 

[denimined]

I don't really know Oracle at all, but my first question is, on the format string - are the month and day field sizes simply restrictive or requirements?
You have "MM/DD/YYYY" for the date string, but provided "5/5/2015" as the date string (instead of "05/05/2015"). Is it that simple?

 

[SkipVought]

Andy,

Have been accessing Oracle databases from Excel for some 20 years both simply using MS Query or via VBA using ADO objects.

You could quite simply do a parameter query via MS Query with ZERO VBA code or by using a modest amount of code in less than 5 minutes.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[Andrzejek]

I don’t know why this tread is back (thanks to waelaswad)...
I was happy with dilettante’s suggestion – a star for him.

The issue was with ADODB’s Filter with Date, Excel part was just to show one example of how I use the Filter with ADODB.

Thanks Skip – I would use your approach, but in my case I push data from Oracle to Excel using VB6. The way I understand your way is to pull data (by Excel) from Oracle. Would work either way.

Case closed (?)


Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.