Welcome To The Home Of The Visual FoxPro Experts  
home. signup. forum. archives. search. google. articles. downloads. faq. members. weblogs. file info. rss.
 From: Ryan Lashway
  Where is Ryan Lashway?
 Warrensburg
 New York - United States
 Ryan Lashway
 To: Bernard Bout
  Where is Bernard Bout?
 Brisbane
 Australia
 Bernard Bout
 Tags
Subject: RE: Why ODBC and Not OLEDB?
Thread ID: 110533 Message ID: 115057 # Views: 22 # Ratings: 0
Version: Visual FoxPro 9 Category: ODBC, ADO and OLEDB
Date: Monday, December 04, 2006 9:02:45 PM         
   


It may not mean anything to anyone but I also prefer ODBC over OLEDB.


With the ODBC connection you can take InstallShield, allow the user to place the database anywhere they like and InstallShield will modify the ODBC based on the Dir.

Also, ODBC is in my mind better for reporting tools (ex. Crystal Reports) from within your application there is no need to create the connection manually for the reports to run, all you need is your viewer and the ODBC creates the connection and credentials.

I have found that speed is not an issue unless you are deploying an application for a large number of users.








> > > > Foxperts,
> > > >
> > > > I found this site while surfing on the net to use ODBC instead of OLEDB and on the link below shows that there are three major reasons that they were not using OLEDB:
> > > >
> > > > http://www.15seconds.com/Issue/961210.htm?voteresult=5
> > > >
> > > > Reason not to use OLEDB:
> > > > OLEDB does not support connection caching.
> > > > OLEDB is slower then ODBC.
> > > > OLEDB requires more code.
> > > >
> > > > but some says OLEDB is much faster than ODBC....
> > > >
> > > >
> > > > I'm wondering what would be your comment....
> > > >
> > > >
> > > > Regards
> > > >
> > > > Zed

> > >
> > >
> > > HI,
> > >
> > > Just some facts on the two.
> > >
> > > OLEDB is the successor to ODBC, a set of software components that allow a "front end" such as GUI based on VB, C++, Access or whatever to connect with a back end such as SQL Server, Oracle, DB2, mySQL etal. In many cases the OLEDB components offer much better performance than the older ODBC.
> > >
> > > OLEDB is a different type of data provider that came about with MS's Universal Data Access in 1996 and does not require that you set up a DSN. It is commonly used when building VB apps and is closely tied to ADO. It works with COM, and DCOM.
> > >
> > > Using ODBC is generally slower than OLEDB. When you use ODBC you actually accessing ODBC via a special OLEDB Provider for ODBC Drivers.
> > >
> > > There are two problems with ODBC. The first is that ODBC is a C API and so is not COM accessible. The second problem is that ODBC is designed solely as an interface to relational databases. ODBC API's are designed specifically for SQL to an RDBMS. Many of the ODBC method names even contain the word SQL. Although this is certainly no problem if you are only interested in accessing relational databases, it is awkward if you want to access non-relational databases that don't understand SQL. Microsoft wanted to remove this inherent limitation, and as part of something it called Universal Data Access (UDA) MS wanted a more generic way of accessing any type of data from any type of data source. It should be possible to query an Excel spreadsheet in the same way you can query a database table. It should further be possible to join an Excel spreadsheet with a relational database table. Thus, OLE-DB became the preferred back-interface.
> > >
> > > Pete from the Great White North. (Only in Canada, ay.) Over and Out ...

> >
> >
> >
> > Sir,
> >
> > According to you there are two problems with ODBC. You also said that ODBC is designed solely as an interface to relational database and ODBC API's are designed specifically for SQL to an RDBMS. Kindly confirm would this mean that ODBC is much better to use than OLEDB if your backend is SQL Server or any other RDMBS.
> >
> > I been using ODBC to connect different type of Database ACCESS, SQL, BTRIEVE and even Excel.
> >
> > If you have read the whole documentation on the link that I've provided they have reason why not to use OLEDB because it does not support connection caching and more. I read some site that connection caching is very important. I even found some site saying OLEDB is faster than ODBC but some site saying that ODBC is much better than and faster than OLEDB.
> >
> > I attached an image that shows the layers from applicatios-->ODBC/OLEDB/ADO-->DataSource if my understanding is correct it's like ADO/OLEDB will not perform well without ODBC. Correct me if I'm wrong.
> >
> > Here's the link:
> > https://www.foxite.com/uploads/66e08a5e-55af-4c61-9fc5-89e4d5683a3e.zip
> >
> > I would like to know which one is better so that going forward I will be using who has the best performance and as possible less connection issue.
> >
> >
> > Regards,
> >
> > Zed

>
> DEX
>
> Bottom line is if you are using VFP to access backend data:
>
> With ODBC you don't need a dedicated DSN. A SQLString Connection will do.
> You can create CURSORS!!!!
> You can use every one of the 1000+ functions of VFP to manuiplate these cursors!!!!!!!!!!
>
> Best of all you can bind your fields on your form right back to the data source!!!!!!!!!!!! You only have to see how badly this binding was implemented in Net to know what a gem you have.
>
> With OLEDB the speed difference as you found out is arbitary. It may connect about 100 miliseconds faster and bring down data 200 miliseconds faster. In the scheme of things, who cares.
>
> Like I said if using VFP and if you want to use OLEDB then throw away Cursors, throw away data binding, throw away SPT - might as well throw away VFP!
>
> Also most important you only get a record set with OLEDB and that is NOT compatible with VFP controls or grids without some manipulation.
>
> The only selling point is that with VFP 8 & 9 you can use Cursor Adapters to connect using OLEDB and get Cursors.
>
> Other than that, if using VFP, then stick with ODBC or learn to use Cursor Adapters, as SPT will not work with OLEDB.
>
> I use ODBC. I tried using OLEDB but found it was too much work "working" with recordsets and VFP.
>
> IMHO.



Ryan J. Lashway
http://www.lashtech.com

ENTIRE THREAD

Why ODBC and Not OLEDB? Posted by Dexter Carlit @ 10/19/2006 10:00:17 PM
RE: Why ODBC and Not OLEDB? Posted by Pete Sass @ 10/20/2006 2:44:12 PM
RE: Why ODBC and Not OLEDB? Posted by Dexter Carlit @ 10/20/2006 5:22:23 PM
RE: Why ODBC and Not OLEDB? Posted by Pete Sass @ 10/20/2006 7:30:36 PM
RE: Why ODBC and Not OLEDB? Posted by Ken Murphy @ 10/20/2006 7:37:19 PM
RE: Why ODBC and Not OLEDB? Posted by Bernard Bout @ 10/23/2006 10:38:51 AM
RE: Why ODBC and Not OLEDB? Posted by Ryan Lashway @ 12/4/2006 9:02:45 PM
RE: Why ODBC and Not OLEDB? Posted by Andy Kramek @ 10/23/2006 1:57:47 PM
RE: Why ODBC and Not OLEDB? Posted by Eric den Doop @ 10/23/2006 2:43:15 PM
RE: Why ODBC and Not OLEDB? Posted by Andy Kramek @ 10/24/2006 12:17:38 PM
RE: Why ODBC and Not OLEDB? Posted by Nilson Rishi @ 10/23/2006 3:32:12 PM
RE: Why ODBC and Not OLEDB? Posted by Ken Murphy @ 10/23/2006 6:37:52 PM
RE: Why ODBC and Not OLEDB? Posted by Nilson Rishi @ 10/24/2006 5:17:33 AM
RE: Why ODBC and Not OLEDB? Posted by Bernard Bout @ 10/24/2006 6:18:13 AM
RE: Why ODBC and Not OLEDB? Posted by Nilson Rishi @ 10/24/2006 8:31:50 AM
RE: Why ODBC and Not OLEDB? Posted by tushar @ 10/24/2006 7:22:47 AM
RE: Why ODBC and Not OLEDB? Posted by Nilson Rishi @ 10/24/2006 10:00:29 AM
RE: Why ODBC and Not OLEDB? Posted by Andy Kramek @ 10/24/2006 1:32:41 PM
RE: Why ODBC and Not OLEDB? Posted by tushar @ 10/25/2006 9:14:35 AM
RE: Why ODBC and Not OLEDB? Posted by Andy Kramek @ 10/25/2006 1:10:22 PM
RE: Why ODBC and Not OLEDB? Posted by Andy Kramek @ 10/24/2006 12:26:39 PM
RE: Why ODBC and Not OLEDB? Posted by Nilson Rishi @ 10/24/2006 1:03:04 PM
RE: Why ODBC and Not OLEDB? Posted by Andy Kramek @ 10/24/2006 1:21:46 PM
RE: Why ODBC and Not OLEDB? Posted by Pete Sass @ 10/24/2006 10:23:04 PM
RE: Why ODBC and Not OLEDB? Posted by Ken Murphy @ 10/25/2006 1:05:40 AM
RE: Why ODBC and Not OLEDB? Posted by Nilson Rishi @ 10/25/2006 4:31:57 AM
RE: Why ODBC and Not OLEDB? Posted by tushar @ 10/25/2006 9:28:28 AM
RE: Why ODBC and Not OLEDB? Posted by Andy Kramek @ 10/25/2006 3:04:41 PM
RE: Why ODBC and Not OLEDB? Posted by Pete Sass @ 10/25/2006 4:10:42 PM
RE: Why ODBC and Not OLEDB? Posted by Ken Murphy @ 10/25/2006 4:47:32 PM
RE: Why ODBC and Not OLEDB? Posted by Bernard Bout @ 10/25/2006 6:13:41 PM
RE: Why ODBC and Not OLEDB? Posted by Ken Murphy @ 10/25/2006 6:40:19 PM
RE: Why ODBC and Not OLEDB? Posted by Bernard Bout @ 10/26/2006 5:55:08 AM
RE: Why ODBC and Not OLEDB? Posted by Ken Murphy @ 10/26/2006 2:33:34 PM
RE: Why ODBC and Not OLEDB? Posted by Bernard Bout @ 10/26/2006 11:33:28 PM
RE: Why ODBC and Not OLEDB? Posted by Randy Smith @ 11/28/2006 8:59:48 PM
RE: Why ODBC and Not OLEDB? Posted by Andy Kramek @ 11/29/2006 1:15:09 PM
RE: Why ODBC and Not OLEDB? Posted by Randy Smith @ 11/29/2006 2:58:16 PM
RE: Why ODBC and Not OLEDB? Posted by Andy Kramek @ 11/29/2006 7:27:04 PM
RE: Why ODBC and Not OLEDB? Posted by Randy Smith @ 11/30/2006 11:14:13 PM
RE: Why ODBC and Not OLEDB? Posted by Andy Kramek @ 12/1/2006 1:00:35 PM
RE: Why ODBC and Not OLEDB? Posted by Randy Smith @ 12/1/2006 4:03:51 PM
RE: Why ODBC and Not OLEDB? Posted by Andy Kramek @ 12/1/2006 5:21:24 PM
RE: Why ODBC and Not OLEDB? Posted by Randy Smith @ 12/5/2006 1:25:04 AM
RE: Why ODBC and Not OLEDB? Posted by Glenn Villar @ 2/24/2009 4:28:19 AM
RE: Why ODBC and Not OLEDB? Posted by Boudewijn Lutgerink @ 10/27/2006 11:33:27 AM
RE: Why ODBC and Not OLEDB? Posted by Ken Murphy @ 10/27/2006 2:27:33 PM
RE: Why ODBC and Not OLEDB? Posted by Boudewijn Lutgerink @ 12/5/2006 12:55:03 PM
RE: Why ODBC and Not OLEDB? Posted by Ken Murphy @ 12/5/2006 3:12:45 PM