Are there any good reason why to use native api instead of ODBC ?

[Boris shtrasman]

On 18/10/2011, ik <idokan at gmail.com> wrote:
> On Tue, Oct 18, 2011 at 23:07, Boris shtrasman <borissh1983 at gmail.com>wrote:
>
>> Hi ,
>>
>> Today I chatted with an experienced person and we had our
>> disagreements between database types and an interesting issue rose up
>> about using Native Api or ODBC ,
>> from what I experienced in the last years I can't find any good reason
>> why not use ODBC:
>>
>> Pro:
>> 1. using ODBC is more prone to api change.
>> 2. it is easier (I think) to maintain ODBC based code , I've seen code
>> based with more then  15 years and it is still maintainable,  and the
>> code stores db back compatibility (from SQL 2000 to SQL 2010).
>> 3. didn't find any indication for performance penalty more then 10%
>> (didn't see that just found this number in a written benchmark a
>> fellow did few years ago).
>> 4. don't violate GPL if your using a driver and just using the DB as
>> storage backed (*IANAL).
>>
>> so as long you stick to good SQL (ANSI) code your pretty safe (yes I
>> know that the dbs have wonderful extensions the problem they have the
>> tendency to change api and not support what the others have)
>>
>> But I'm pretty sure there situation when you should avoid using ODBC
>> (the problem I don't what they are).
>>
>> Do you have any suggestions when should I use native api instead of ODBC ?
>>
>>
> Boris, you'ved missed a lot of what I have said in the conversation, as you
> see only narrow sight on things.
>
> If you take Ruby, Python and even Delphi/FPC they all binding to the
> official API rather then ODBC.
> They use so because ODBC as you mentioned is very limited.
> For example if I want to use stored procedures, udf, event driven work
> (Firebird and PostgreSQL support TCP based events that you can trigger on
> things), capture exceptions (user defined ones) etc... Well, using ODBC is
> almost impossible for some of the things. It is more generic way of using
> databases.
>
> When all I require is simple connection to the database and run simple
> tasks, it might provide good job. But when you are starting to have more
> demands, or do not wish to place your whole data logic in your application
> (amm MySQL anyone ?), then how ODBC can help you with that ?
>
>
> Ido
>

for ODBC you can use SP ,  UDFs , and even calling an external
functionality but then you use db specific implementation (PL/SQL ,
T-SQL etc ...)
basicly if you can code it in SQL you can pass it via the connection
(there are the db specific issues like TDS but this is a side note).

about the data logic I agree if you don't wish to develop on low level
(and I'm talking on O/RM level) you most choose some middle wear.

perhaps I didn't get the opportunity but for the last 2 years I never
had a need that could not be satisfied via ODBC connection (small note
I'm a C + Perl dev so perhaps the needs in different languages is more
common).