Re: advantages of using embedded sql

Besides Alan's excellent answer, I would like to add that many times
(IMHO), SQL's syntax is a lot more self-explaining and simpler than
its RPG "native" equivalent code, which is a big plus for many
newcomers to the IBM i family.

Regards,

Luis Rodriguez



On 9/7/11, Alan Campin <alan0307d@xxxxxxxxx> wrote:

Two different questions. Embedded SQL can be used with DDS or DDL. Doesn't
really matter except maybe a slight performance advantage to SQL.

When DDS based files are written there is no validation of the data but
every time you read a record it is validated. With DDL described files the
opposite is true. The OS validates the data in the record every time a
record is written and does not validate on a read. Since normally you read a
record many, many times to every write you should get better performance
with DDL created tables.

You also have a GUI in Ops Nav for creating DDL based tables which is lot
better than just typing by hand the DDS based tables.

Also, indexes created through CREATE Index (DDL) are faster although you
create indexes for DDS based tables.

DDL based tables also have capabilities that DDS do not including the most
important, the ability to define identity keys.

If you have a choice, I would always define a table through DDL.

As to using embedded SQL, the reasons are legion but the absolutes number
one reason is database independence. If you add up the cost of not using
database independence the cost is incredible.

Every time you compile a program using file I/O you are compiling the
physical view of the data into the program. From that moment on your
database if frozen. You can only change the database by recompiling the
program which no one wants to do. The gyrations that people go through to
keep from making a change to the database are unreal. (Putting multiple
values in fields, extension tables, extension, extension tables, numeric
values in alpha field, on and on instead of just adding a new field to the
database which you could do if you used SQL).

SQL allows you to create a logical view of the data that is separate from
the physical. You can join multiple tables, select only the fields you need,
etc, etc.

One other advantage I see is that I can build my SQL statement external,
test it and then import into my program.

I can just do all kinds of things with SQL that require all kinds of code
with file I/O.

The number one thing that I hear against is that it is slower but I have
just no seen it and that argument begin to fade when you start joining
multiple files. We have over 100 programs being fired by triggers using 100%
SQL and we have not seen any decrease in performance.

The last thing and maybe one of the most important is SQL is the standard
used everywhere in the world. Any other language, any other system file
access is done with SQL. Being proficient at SQL for a programmer is just
like breathing. Unless you are planning to retire in the next full years or
whatever and do care where you career is going learn and become proficient
in SQL.


On Wed, Sep 7, 2011 at 1:13 PM, Dave <dfx1@xxxxxxxxxxxxxx> wrote:

Hi all, I need to list some advantages of using embedded sql in rpg
programmes over using ordinary rpg code. We don't actually use DDL, so I'm
assuming that there are far less advantages in our case. Right or wrong?
Thank you very much!!
--
This is the RPG programming on the IBM i / System i (RPG400-L) mailing
list
To post a message email: RPG400-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/rpg400-l.

--
This is the RPG programming on the IBM i / System i (RPG400-L) mailing list
To post a message email: RPG400-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/rpg400-l.