Re: YNT: Re: Can the result sets produced in SQL procedures be left open for the use of the calling program?

On 06/01/2017 03:51 PM, gulsumramazanoglu wrote:
> Adrian thank you for your prompt reply and enlightening info.. though i
> need to read the sql document thorougly, but for now i just looked at
> the CREATE FUNCTION command and its compatibility note at the end, and
> saw that there is an iso standard (and also a cross-platform)
> compatibility issue.. actually it seems none of the databases provide
> full compatibility for this command (and for more) i think.. is there
> any other way of writing sql scripts (say functions, procedures..) which
> will provide compatibility with iso standards and other db.s? I want to
> write backend stuff in functions and also stick to the standards..

Short answer:

No

Long answer:

Full cross compatibility is a myth for anything but an extremely simple
use case and very few applications stay at this stage. If you want to
keep logic in the database you will end up writing to that database's
implementation of the standard and how it creates/use functions. This is
one of the reasons 3-tier architecture was developed:

https://en.wikipedia.org/wiki/Multitier_architecture#Three-tier_architecture

So a middle layer between the UI and the database where business logic
resides. Even that falls down when you start making use of database
specific features. In that case the logic layer grows an adaption layer
to deal with various databases.

Your best bet is to pick a database you really want to use and build
against that. Then if you want to use other databases, convert what is
needed. From the posts that hit the various pgsql* lists that will be
time consuming. There are tools that can help, for example moving from
Oracle and MySQL to Postgres:

http://ora2pg.darold.net/

It still needs a good deal of oversight on the part of the developer.

>
> Maybe i should ask more specifically: say for instance i want to send
> some input data to the function and wait for a result set depending on
> those parameters (inputs).. may i code such a function in a compatible
> way with iso standards and make it cross-platform as well?
> Maybe a too naive question, but still i want to ask..

See above.

>
> Thanks again..
>
> Samsung cihazımdan gönderildi
>

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com