|
ADO is a wrapper for a technology called OLE DB, although it has little to do with OLE (Object Linking and Embedding). The database-specific layer (called a Provider) is a set of COM objects which implement interfaces designed by Microsoft. The client talks directly to the OLE DB provider with no intermediary. ADO is a classic VB-friendly wrapper for OLE DB.
ODBC (Open Database Connectivity) is based around a manager/driver model. Applications talk to the ODBC manager, which talks to the drivers. The extra overhead is generally minimal. ODBC is the ANSI standard interface defined in the SQL standard.
To enable transition to ADO/OLE DB from ODBC, i.e. so that client application developers could use the new interface before the database developers released OLE DB providers, Microsoft made a special provider called the OLE DB Provider For ODBC available (its ProgID, which you specify in the Provider connection string parameter, is MSDASQL). You should only use this where there is no native OLE DB provider. This provider adds extra overhead and implements a reduced feature set compared to native OLE DB providers. Unfortunately it's also the default provider - it's what you get if you don't specify a Provider in the connection string.
The relative speed of ODBC and ADO is all down to the provider and driver, but in general, all else being equal, ADO should be slightly faster than ODBC if you're using a native OLE DB provider.
ADO.NET is slightly misnamed in the .NET Framework. It's really a general umbrella name for 'how .NET does data access'. The desktop Framework ships with four Providers (the name is the same but the concepts have changed) - two generic (OLE DB, ODBC) and two specific (SQL Server, Oracle [since 1.1]). You can also obtain third-party Providers. The cost of calling a COM object is slightly higher than calling a 'flat' API like ODBC in the .NET Framework, so OLE DB's small performance advantage is mostly negated.
You can access a third-party database like MySQL either through a native .NET provider or via an OLE DB provider (using OleDbConnection) or ODBC driver (using OdbcConnection). Generally you should prefer a native .NET provider as it's likely to have more features and less translation.
|
|
|
|
|
Wow.....thanks for the great explination. Okay so if the database creator....for better use of the word....provides a .NET provider then it's best to use it. And if I wanted to add an "Other Database" option to my program...I can always use ODBC to connect to the "Other" database and run a query.
I guess the great thing about ODBC is that I would not have to install all the providers for each of the different types of DB's out there. So now I understand why I keep getting asked if I'm using ODBC.
DING....the light came on
Tom Wright
tawright915@gmail.com
|
|
|
|
|
It's worth knowing that the native providers, e.g. the SQL Server providers, generally use the databases native API, so you get the best speed because they aren't dealing with the plumbing that technologies that ODBC put in place.
|
|
|
|
|
Hi,
In SQL Server, for seeing the table structure, we use the query sp_help <tablename>. Is there anything similar in Sybase like this? Pls help.
Thanks
Meeram395
|
|
|
|
|
The Sybase equivalent is "sp_help <tablename>". At one time, MS SQL-Server and Sybase SQL-Server (or whatever they call it these days) used to have the same code-base and system catalog tables.
If you want to thank me for my help, please vote my message by clicking one of numbers beside "Rate this message".
|
|
|
|
|
To get the list of columns and their types with any SQL database I get an IDataReader for
SELECT * FROM table , then I simply call its GetSchemaTable() method (I don't read the data).
|
|
|
|
|
in the mysql command-line I did:
mysql> create database myDB;
mysql> use myDB;
mysql> source mySource.sql;
and I get lines as -
Query OK, x rows affected, y warnings (z sec)
My question: How\where can I see what were the warnings, so I can fix them?
Thanks,
Atara
|
|
|
|
|
found it:
mysql> SHOW WARNINGS;
Atara
|
|
|
|
|
Hi all,
In VS2005, I use OracleConnection class of Oracle.DataAccess.Client Namespace so connect to Oracle database throw exception: "ORA-12705: Cannot access NLS data files or invalid environment specified".But i use OracleConnection class of System.Data.OracleClient is fine.
Please help me.Thankx
|
|
|
|
|
I have these three Tables,
customers, cust_order, cust_debit
customers primary ID is added to cust_order and cust_order primary key is added to cust_debit.
I want records from customers and cust_order table on a condition which is associated to cust_debit. Tables Structure is as follow
cutomers cust_order cust_debit
cust_id fname, lname, ...... order_id, cust_id, order_date ID, order_id, amount
I need fname, lname, order_id, order_date from customers and cust_order where cust_debit.amount >=2000,
Now I need only one record of a particular customers.I used the following query
SELECT cust_order.order_id as order_id, customers.fname as fname , customers.lname as lname, cust_order.order_date as order_date,cust_order.order_status as order_status from cust_order,customers, cust_debit where customers.customer_id=cust_order.customer_id and cust_order.order_id = cust_debit.order_id and cust_debit.amount >=200
It works fine, But I need only one record of a customers, like when we use DISTINCT.
How can I use that.
Pls Help me
Thanks in Advance.
|
|
|
|
|
|
I have vote u 5.
Ya the link u have given I have already seen that, but I cant understand that. Pls
Help me and read my post carefully and then set query and then post if u dont mind it.
Thanks in Advance.
|
|
|
|
|
I'm not really sure what you are trying to acheive with the "cust_debit" table. It looks like it contains the amount outstanding for each order.
If it is the amount outstanding then the following change would give you the latest order for any customer who has a total of more than 2000 outstanding:
....
from (
select customer_id, max(order_id)
from cust_order
inner join cust_debit
on cust_debit.order_id = cust_order.order_id
group by customerId
having sum(cust_debit.amount) >= 2000
) as lastest_order_id
.... The following uses a correlated-subquery to give you customers where any one single order has more than 2000 outstanding:
....
from (
select customer_id, max(order_id)
from cust_order
where 2000 <= (
select sum(cust_debit.amount) from cust_debit
where cust_debit.order_id = cust_order_id)
group by customerId
) as lastest_order_id
.... Both examples give the details of the most-recent order. If you want the oldest order then use min(order_id) . Hopefully you should now be able to solve your problem using one of these examples.
Regards
Andy
|
|
|
|
|
Dear there is a problem in ur query its not working correctly, infact system hangs when I execute ur query.
Pls help me. I m tied of this query.
|
|
|
|
|
Not half as tired as I am! I don't mind helping people, but it doesn't seem like you are trying very hard.
How about you putting some effort into understanding what the queries are trying to do. The SQL that I provided is basically three separate select-statements layered inside each other. I would suggest that you get the inner-most query working first:
select customer_id, max(order_id)
from cust_order
inner join cust_debit
on cust_debit.order_id = cust_order.order_id
group by customerId
having sum(cust_debit.amount) >= 2000 and the build up gradually to your final solution. If your machine is hanging then that may indicate that your database is not indexed properly.
|
|
|
|
|
Hi,
What sort of rigts needed to create tablespace in oracle?
Best Regards,
Mushq
Mushtaque Ahmed Nizamani
Software Engineer
Ultimus Pakistan
"English is my second language, so please don't mind if i do some grammatical or spelling mistakes in my messages."
|
|
|
|
|
GRANT create tablespace TO #user id#;
-- modified at 4:22 Wednesday 18th July, 2007
If you want to thank me for my help, please vote my message by clicking one of numbers beside "Rate this message".
|
|
|
|
|
Hi i have table like this format:
Id sMonth sYear value1
608 6 2007 100
608 7 2007 200
608 8 2007 150
608 6 2008 652
608 7 2008 120
608 8 2008 234
Suppose i need the records between the 6th month 2007 to 6th month 2008
O/P:
Id sMonth sYear value1
608 6 2007 100
608 7 2007 200
608 8 2007 150
608 6 2008 652
How to write the query???,
If its in date format we can use between condition...,
But in this case how u can compare the months and years ???,
Regards,
Magi
|
|
|
|
|
How about
select * from MyTable
where (sYear > 2007 or (sYear = 2007 and sMonth >= 6))
and (sYear < 2008 or (sYear = 2008 and sMonth <= 6)) or
select * from MyTable
where convert(varchar(6), (Year*100)+sMonth) between '200706' and '200806' Regards
Andy
-- modified at 14:19 Wednesday 18th July, 2007
If you want to thank me for my help, please vote my message by clicking one of numbers beside "Rate this message".
|
|
|
|
|
Hi andy,
Thank u for ur reply...,
Ya i getting o/p for the first query...,
But second query is not giving proper o/p...,
Anyway one solution is enough...,
Thank's lot...,
keep in touch,
Regards,
Magi
|
|
|
|
|
You're welcome.
The second query should have used (sYear*100) . I hadn't woken-up properly
|
|
|
|
|
5 votes to you man. I was about to say bad database design
|
|
|
|
|
Sorry Magi,
Did not wanted to hurt your feelings about the design, But it is a bad practice, you should always use DateTime format. If you want to look at month and year only, you can still do that using datetime column. As you proceed further you will find data Access more and more difficult. That is why they say that make sure your design is right. Consult your senior developers about the design.
And finally, as you said, it might be right in your case, so thats good
Cheers
|
|
|
|
|
S repli,
I know what ur trying to say...,
But i cant get date from my end user...,
Becoz a month full records i am storing in one row itself...,
So how i can tell the date in this?...,
So in this we can use table deisgn like that only...,
Sorry yar i think my words also make u to feel..,
Once again sorry for that,
Keep in touch,
Regards,
Magi
|
|
|
|
|
andyharman wrote: select * from MyTable where convert(varchar(6), (Year*12)+sMonth) between '200706' and '200806'
Actually, (Year*12)+sMonth wont get you those values at all
|
|
|
|