EXEC SQL

[Former Member]

Hi,

What does an EXEC SQL stmt do in ABAP?

What is the disadvantage of using it?

Regards,

Kalai

[Former Member]

Hi,

EXEC SQL is the native SQL language that one can use to fetch the data from the database just like the Open SQL statements but we should avoid using them as they are database dependent.

In case of Open SQL,you write the database statements rrespectve of the database installed because the database interface converts the Open SQL commands into Native SQL commands which are database specfic,hence making them database independent and portable which is not possible with Native or EXEC SQL.

Please refer to the below lnk for further information.

http://help.sap.com/saphelp_46c/helpdata/en/9f/dba50d35c111d1829f0000e829fbfe/frameset.htm

In case you have any further clarifications,do let me know.

Regards,

Puneet Jhari.

[Former Member]

http://help.sap.com/saphelp_nw70/helpdata/en/fc/eb3b8b358411d1829f0000e829fbfe/frameset.htm

Native SQL

Open SQL allows you to access database tables declared in the ABAP Dictionary, regardless of the database platform you are using. Native SQL allows you to use database-specific SQL statements in an ABAP program. This means that you can use database tables that are not managed by the ABAP Dictionary, and therefore integrate data that is not part of the SAP Web AS ABAP System.

As a rule, an ABAP program containing database-specific SQL statements will not run under different database systems. If your program will be used on more than one database platform, only use Open SQL statements.

[Former Member]

Please give me reward points..

[former_member404244]

Hi,

check the below links

Disadvantages of Native SQL

Native SQL bypasses SAP's internal database interface, thereby, creating the following problems:

No table logging on SAP level during Native SQL operations

Synchronous match codes are not updated by Native SQL operations

No synchronization of the SAP table buffer

SQL statements become database dependent. This may cause numerous porting problems between database versions

Advantages of Native SQL

With the new SQL capabilities of SAP Release 4.0 Native SQL is only of interest in the following cases:

Use of set operations (UNION, INTERSECT, MINUS)

Use of database specific features

If Native SQL must be used and has been approved, adhere to the following guidelines:

Don't use Native SQL for operations that make database structure modifications

Isolate EXEC SQL statements in their own INCLUDE files

Document the reason for using Native SQL with detailed comments inside the INCLUDE file

Be careful when reading long fields and raw fields! Length information is additionally stored within the field, invisible for Open SQL.

Never use SELECT * when selecting from SAP tables since the Data Dictionary can create fields on the DB in a different order than displayed in SAP

http://help.sap.com/saphelp_nw04/helpdata/en/fc/eb3b8b358411d1829f0000e829fbfe/content.htm

http://help.sap.com/saphelp_46c/helpdata/en/fc/eb3b8b358411d1829f0000e829fbfe/content.htm

http://www.erpgenie.com/abaptips/content/view/240/62/

Reward if helpful.

Regards,

Nagaraj

[Former Member]

Hi

<b>EXEC SQL</b>

EXEC SQL [PERFORMING subr].

...

ENDEXEC.

These statements define an area in an ABAP program in which one or more Native SQL statements are to be carried out. The area between EXEC and ENDEXEC is not completely checked by the syntax check. The statements entered there are passed to the Native SQL interface and processed there as follows:

Almost all SQL statements that are valid for the addressed database system can be included between EXEC and ENDEXEC, in particular the DDL statements. These SQL statements are passed from the Native SQL interface to the database system largely unchanged. The syntax rules are specified by the database system, in particular the case sensitivity rules for database objects. If the syntax allows a separator character between individual statements, you can include several Native SQL statements between EXEC and ENDEXEC. Generally, the semicolon ( is used as the separator character.

You can also include SAP-specific Native SQL language elements between EXEC and ENDEXEC. These statements are not passed directly from the Native SQL interface to the database, but are converted appropriately. These SAP-specific language elements are::

<b>Host variables</b>

Statements for cursor processing

Database procedure calls

Statements for establishing database connections

All Native SQL statements bypass SAP buffering. Automatic client handling is not performed.

<b>System fields</b>

The statement ENDEXEC sets the system fields sy-subrc and sy-dbcnt.

sy-subrc Meaning

0 The statements between EXEC and ENDEXEC were executed successfully.

4 The statements between EXEC and ENDEXEC were not executed.

The ENDEXEC statement sets sy-dbcnt to the number of table rows processed in the last Native SQL statement.

Programs with Native SQL statements are generally dependent on the database system used, so that they cannot be executed in all SAP Systems. This is especially true for the examples in this section, which was written for Informix database systems.

<b>Example</b> Inserting two rows in the database table SCARR. If neither of these rows exists, sy-subrc is set to 0 by ENDEXEC and sy-dbcnt to 1. Otherwise, an exception is raised and handled.

DATA: exc_ref TYPE REF TO cx_sy_native_sql_error,

error_text TYPE string.

TRY.

EXEC SQL.

INSERT INTO scarr

(MANDT, CARRID, CARRNAME, CURRCODE, URL)

VALUES ('000', 'FF', 'Funny Flyers', 'EUR',

'http://www.ff.com');

INSERT INTO scarr

(MANDT, CARRID, CARRNAME, CURRCODE, URL)

VALUES ('000', 'EF', 'Easy Flyers', 'EUR',

'http://www.ef.com');

ENDEXEC.

CATCH cx_sy_native_sql_error INTO exc_ref.

error_text = exc_ref->get_text( ).

MESSAGE error_text TYPE 'I'.

ENDTRY.

<b>Reward if usefull</b>