Data Provider

[Guest]

Hello

I am working on a VB.Net project where I need to change the data provider from OLEDB to OracleClient. I am also looking to use "shaping" and I am looking for the equivalent to "Provider=MSDataShape.1" when using an OracleClient connection string. The provider syntax works fine with an OLEDB Connection String. Does anyone know how to do this? I am using the 1.1 framework, and Visual Studio vb.net

(Note ... I switched to OracleClient because I cannot use a SQL TO_DATE command with a four digit year mask, if the TO_DATE is nested within a DECODE. The OLEDB provider keeps adding 100 years to any years beginning with 1900 with such a scenario. The OracleClient data provider tested well in this regard and does not have this problem.

Thanks

Paul Deibel.

 

[Jay B. Harlow [MVP - Outlook]]

Paul,
With the flexibility of the DataSet do you really need to use the
MSDataShape object?

The DataSet allows you to define Multiple tables, plus the relationship
between those tables. Which effectively does the "shaping" for you.

David Sceppa's book "Microsoft ADO.NET - Core Reference" from MS Press
covers this plus a plethora of other items about ADO.NET. I highly recommend
it as a good tutorial for ADO.NET & a good desk reference once you know
ADO.NET.

Hope this helps
Jay

Hello:

I am working on a VB.Net project where I need to change the data provider

from OLEDB to OracleClient. I am also looking to use "shaping" and I am
looking for the equivalent to "Provider=MSDataShape.1" when using an
OracleClient connection string. The provider syntax works fine with an
OLEDB Connection String. Does anyone know how to do this? I am using the
1.1 framework, and Visual Studio vb.net.

(Note ... I switched to OracleClient because I cannot use a SQL TO_DATE

command with a four digit year mask, if the TO_DATE is nested within a
DECODE. The OLEDB provider keeps adding 100 years to any years beginning
with 1900 with such a scenario. The OracleClient data provider tested well
in this regard and does not have this problem.)

 

[Guest]

Jay,

Thanks for your quick reply. The application is query only, and depending upon what the user is querying, combined with a selection from a pull-down "menu" ... a different list/detail grid displays. That's why we didn't want the overhead of a full dataset. The shape command gives us the flexibility to dynamically create the SQL based upon what the user is interested in seeing. It works well under OLEDB; I'm very curious if it's available with OracleClient. It doen't look like it is.

P.D.

 

[Jay B. Harlow [MVP - Outlook]]

Paul,
Considering that the Shape provider is OLEDB based, and the OracleClient is
Oracle library based. I would not expect, nor try to get it to work.

Can you even get the Shape provider to FULLY work with System.Data.OleDb? I
would expect you would need to drop to the ADODB to get the full benefit of
the Shape Provider.

Out of curiosity, what are you basing your comment "That's why we didn't
want the overhead of a full dataset." on? Is there documentation someplace
that indicates what the actual difference between the overhead of a DataSet
is verses the overhead of the Shape Provider? Or did you perform CLR
Profiler & similar tests? Or are you simply surmising? I personally do not
know and sincerely would like to know.


Again, I would use the DataSet, and not bother with the Shape provider. As
the DataSet provides everything that I know the Shape provider provides, and
of course the DataSet is fully .NET friendly (I now it fully works)... Until
profiling proved that the DataSet had unacceptable overhead in this app, I
would not look for alternatives.

Before you make a decision I would recommend you read the chapter on the
DataSet & the comments on the Shape Provider in David Sceppa's book
"Microsoft ADO.NET - Core Reference" from MS Press. I find Sceppa's book to
be both a good tutorial & a good desk reference later.

Also, if you have not, you may want to solicit comments from the ADO.NET
gurus in the microsoft.public.dotnet.framework.adonet newsgroup.

Hope this helps
Jay

Jay,

Thanks for your quick reply. The application is query only, and depending

upon what the user is querying, combined with a selection from a pull-down
"menu" ... a different list/detail grid displays. That's why we didn't want
the overhead of a full dataset. The shape command gives us the flexibility
to dynamically create the SQL based upon what the user is interested in
seeing. It works well under OLEDB; I'm very curious if it's available with
OracleClient. It doen't look like it is.

 

[Guest]

Hi Jay

As I said, the query/menu/select/list/detail part of the project is read/query only. We are using a data reader within the shape command, which seems to work fine. It helps us to build relations and join several complex query strings, based upon what the user is selecting and doing. I assumed it would have less overhead than a dataset, based on the "read only" nature of that portion of the application. I could be wrong. I understand that "Shape" is based on older technology. However, a consultant helped us set it up this way, and it seems to do what we want. It's also compact, elegant, and easy to maintain. I am somewhat hesitant to re-write working (and fast running) code.

The larger issue is why we found what appears to be a Y2K bug in OLEDB, which is part of Microsoft's flagship Visual Studio product.

This is what forced us to try a different data provider as a work around. The Y2K issue showed up in the portion of the overall application that is being used for data maintenance. As I mentioned earlier, a TO_DATE function, nested within a DECODE, isn't working right, even if a four digit year mask is being used. For a 1900 series date, 100 years gets added, evidently by something the data provider is doing or calling. (It's not in the application code, that's for sure.) The TO_DATE function works fine if it is NOT nested within a DECODE. We are using Oracle as the back end database. DECODE is syntax specific to Oracle, it's similar to an "IF" statement, but it is SQL not PL/SQL. It's not ANSI standard, as far as I know, but it's useful and powerful. I did a response.write on the EXACT update string that the application was building, plopped it into Oracle's SQL*PLUS prompt, and the SQL worked fine. That's why I suspected the OLEDB data provider. When we switched to ORACLECLIENT as the data provider, the date bug did not re-appear

I would have contacted Microsoft support to report the bug, but I didn't want our organization to get charged $99.00 (or whatever the current cost is) for the support e-mail, so that is why I thought posting to a newsgroup might help

For now, I will stick with OLEDB for the portion of the application that is using Shape technology (as luck would have it there is no date logic on that screen, so far) and convert the rest to use OracleClient based connection objects. This seems to be a decision that makes sense, based on your confirmation of the fact that "Shape" is not available within OracleClient

Thanks for your help, your quick replies, and the book recommendation.

P.D.

 

[Jay B. Harlow [MVP - Outlook]]

Paul,

The larger issue is why we found what appears to be a Y2K
bug in OLEDB, which is part of Microsoft's flagship Visual Studio product.

If its a confirmed bug, then I understand that MS will not charge you the
$99 support call...

Also if you have MSDN Universal I understand you have 1 or 2 free support
calls (whether or not they are bugs).

Which may be a couple of options you may want to double check...

Hope this helps
Jay

Hi Jay:

As I said, the query/menu/select/list/detail part of the project is

read/query only. We are using a data reader within the shape command, which
seems to work fine. It helps us to build relations and join several complex
query strings, based upon what the user is selecting and doing. I assumed it
would have less overhead than a dataset, based on the "read only" nature of
that portion of the application. I could be wrong. I understand that
"Shape" is based on older technology. However, a consultant helped us set
it up this way, and it seems to do what we want. It's also compact, elegant,
and easy to maintain. I am somewhat hesitant to re-write working (and fast
running) code.

The larger issue is why we found what appears to be a Y2K bug in OLEDB,

which is part of Microsoft's flagship Visual Studio product.

This is what forced us to try a different data provider as a work around.

The Y2K issue showed up in the portion of the overall application that is
being used for data maintenance. As I mentioned earlier, a TO_DATE
function, nested within a DECODE, isn't working right, even if a four digit
year mask is being used. For a 1900 series date, 100 years gets added,
evidently by something the data provider is doing or calling. (It's not in
the application code, that's for sure.) The TO_DATE function works fine if
it is NOT nested within a DECODE. We are using Oracle as the back end
database. DECODE is syntax specific to Oracle, it's similar to an "IF"
statement, but it is SQL not PL/SQL. It's not ANSI standard, as far as I
know, but it's useful and powerful. I did a response.write on the EXACT
update string that the application was building, plopped it into Oracle's
SQL*PLUS prompt, and the SQL worked fine. That's why I suspected the OLEDB
data provider. When we switched to ORACLECLIENT as the data provider, the
date bug did not re-appear.

I would have contacted Microsoft support to report the bug, but I didn't

want our organization to get charged $99.00 (or whatever the current cost
is) for the support e-mail, so that is why I thought posting to a newsgroup
might help.

For now, I will stick with OLEDB for the portion of the application that

is using Shape technology (as luck would have it there is no date logic on
that screen, so far) and convert the rest to use OracleClient based
connection objects. This seems to be a decision that makes sense, based on
your confirmation of the fact that "Shape" is not available within
OracleClient.

 

[Kevin Yu [MSFT]]

Hi Paul,

Thank you for using MSDN Newsgroup! My name is Kevin, and I will be
assisting you on this issue.

First of all, I would like to confirm my understanding of your issue. From
your description, I understand that you believe that there's Y2K bug in the
OLEDB driver for Oracle database which causes canonical date conversion
function lose century information depending on server's date. Please feel
free to point me out if there's any misunderstanding.

Based on my research, this is a known issue. The problem is actually caused
by the Oracle ODBC driver. If a data base administrator wants to be Y2K
compliant he will need to change the default date format, NLS_DATE_FORMAT.
The application should not be setting the date format. There are a few
method of changing the NLS_DATE_FORMAT.

1) A registry entry can be added on the client or server: (This step
involves modifying the registry. Please backup the registry before making
chages to it.)

[HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE]
"NLS_DATE_FORMAT"="DD-MON-YYYY"
----> This can be any format the administrator wants. The key is that it
should include the century. The Format DD-MON-RR can be used if no dates
are before the year 1950. This also allows the default string to work on
old string formats. If the time is also a concern the time should be
included in this format.

2) The preferred method would be to have database administrator change the
init.ora database file and set the NLS_DATE_FORMAT in that file. This is a
better way because the database will behave the same for all clients.

However, if the problem still persists, please feel free to let me know.
I'm glad if I can provide some further assistance.

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."

 

[Guest]

Thanks Kevin:

I have forwarded your reply to our Data Base Administrator for follow-up.

P.D.

 

[Kevin Yu [MSFT]]

Hi Paul,

If that cannot resolve the problem, please feel free to let me know.

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."

 

[Guest]

Kevin:

Our DBA says:

"We are running with the defaults provided by using nls_language - AMERICAN. The default nls_date_format is 'DD-MON-RR'."

Since this is a valid format, there is an issue with the OLEDB Data Provider, in my opinion.

(Please see my other posting, entered today, about a different problem with the OracleClient Data Provider.)

P.D.

 

[Kevin Yu [MSFT]]

Hi Paul,

If the NLS_DATE_FORMAT is 'DD-MON-RR', we have to make sure that the no
dates are before the year 1950 in the database.

Looking at the nature of this issue, it would require intensive
troubleshooting which would be done quickly and effectively with direct
assistance from a Microsoft Support Professional through Microsoft Product
Support Services. You can contact Microsoft Product Support directly to
discuss additional support options you may have available, by contacting us
at 1-(800)936-5800 or by choosing one of the options listed at
http://support.microsoft.com/default.aspx?scid=sz;en-us;top. If there is
really an issue with the OLEDB Data Provider, there will be no charge at
all.

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."