SQL statements

[Former Member]

What are the different SQL Statements in ABAP?

[Former Member]

Hi..

2 Types:-

1, Open SQL

2, native SQL.

ABAP Native SQL allows you to include database-specific SQL statements in an ABAP program. Most ABAP programs containing database-specific SQL statements do not run with different databases. If different databases are involved, use Open SQL. To execute ABAP Native SQL in an ABAP program, use the statement EXEC.

Open SQL (Subset of standard SQL statements), allows you to access all database tables available in the R/3 System, regardless of the manufacturer. To avoid conflicts between database tables and to keep ABAP programs independent from the database system used, SAP has generated its own set of SQL statements known as Open SQL..

Reward useful answers.

Regards

Bala.

[Former Member]

hi Jeya,

Different SQL statements used in ABAP are

(1) Open SQL

(2) Native SQL

Open SQL consists of a set of ABAP statements that perform operation on the central database in R/3 System. Open SQL provides a uniform syntax and semantics for all the database systems supported by SAP.

Native SQL is SQL for database in use. Besides OPEN SQL, if you need you can use the native SQL for databases.But an ABAP program containing database-specific SQL statements, i.e., native sql statements, it will not run under different database systems. If your program is to be used on more than one database platform, then use only Open SQL statements.

• Reward if found useful

Regards

Sayee

[Vijay]

hi...

there r 2 types of SQL

Native SQL

Open SQL

Open SQL commands

SELECT

INSERT

UPDATE

MODIFY

DELETE

OPEN CURSOR, FETCH, CLOSE CURSOR

regards

vijay

[Former Member]

SQL Statements

1)

ENDEXEC.There is no period after Native SQL statements. Also, using quotation marks (") or an asterisk (*) at the beginning of a native SQL statement's line does not introduce a comment as it would in normal ABAP syntax. You need to know if the table and field names are case-sensitive in your chosen database.

The data is transported between the database table and the ABAP program using host variables in Native SQL statements. These are preceded in a Native SQL statement by a colon ( and are declared in the ABAP program. The elementary structures can be used as host variables. The structures of an INTO clause are treated exceptionally, as though all of their fields are listed individually. If the selection in a Native SQL SELECT statement is a table, then you can pass it to ABAP line by line using the PERFORMING addition. For each line read, the program calls a subroutine

. Further, you can process the data within the subroutine.

After the ENDEXEC statement, SY-DBCNT contains the number of lines processed as it does in Open SQL. In almost all cases, SY-SUBRC contains the value a after the ENDEXEC statement. Cursor operations form an exception: after FETCH, SY-SUBRC is 4 if no more records could be read. This is also applied when you read a result set using EXEC SQL PERFORMING.

Native SQL Scope

Native SQL is very important as it allows you to execute nearly all available statements through the SQL programming interface (usually known as SQL Call Interface or similar) for directly executing SQL program code (using EXEC IMMEDIATE or a similar command). The statements that are not supported are listed in the following section:

·

Native SQL and the Database Interface,

· Native SQL and Transactions

· Native SQL and the Database Interface

Native SQL statements bypass the R/3 database interface. With the database buffer on the application server, there is no table logging, and no synchronization. Therefore, you should use Open SQL to change database tables declare in the ABAP dictionary wherever possible. Since the columns contain extra database specific length information for the column tables declared in the ABAP dictionary, containing long columns with the type LCHAR or LRAW should only be addressed using Open SQL. Native SQL may not produce the correct result, as it does not take this information into account. Native SQL does not support automatic client handling. Instead, you must treat a client field like any other field

Native SQL and Transactions

One should not use any transaction control statement such as COMMIT, ROLLBACK WORK, or any statements that set transaction parameters using Native SQL to ensure that transaction in the R/3 System are consistent.

ABAP Dictionary

The ABAP Dictionary is nothing but a part of the ABAP Workbench. It allows you to create and administer database tables. There are no statements from the DDL part of Standard SQL in Open SQL. It should be noted that normal application programs should not create or change their own database tables.

To create and change database tables, the ABAP Dictionary has to use the DDL part of Open SQL. Besides this, it also administers the ABAP Dictionary in the database. In addition, the ABAP Dictionary contains meta-descriptions of all database tables in the R/3 system. Here, only database tables appears in the Dictionary, which you have created using the ABAP Dictionary. Open SQL statements can only access tables, which exists in the ABAP Dictionary.

Authorization and Consistency Checks

With regard to authorization and consistency checks, the DCL part of Standard SQL is not used in R/3 programs. Whereas, the work processes which are within the R/3 system are logged into the database system as users with full rights. By using the R/3 authorization concept, the authorizations of programs or users to read or change database tables is administered within the R/3 system. In addition, transactions must equally ensure their own data consistency using the R/3 locking concept.

The R/3 lock concept allows you to monitor your system with regards to lock logics. The R/3 lock concept works closely together with the R/3 updates.

As an example, say that a travel agent wants to book a flight for a customer who wants to fly to a particular city with a certain airline on a certain day. If there are still available seats on the flight, then the booking will be possible, otherwise it will lead to overbooking. Hence, the database entry corresponding to the flight must be locked against access from other transactions to avoid the possibility of overbooking. This is because two agents might both be doing this same thing at the same time, and we need to make sure that we don't overbook.

Lock Mechanisms

When the database system receives change statements (INSERT, UPDATE, MODIFY, DELETE) from a program, it automatically sets database locks. Database locks are locks on the database entries affected by statements to prevent problems. Since the lock mechanism uses a, lock flag in the entry, you can only set a lock for an existing database entry. After each database commit, these flags are automatically deleted. This means that database locks can never be set for longer than a single database LUW, a single dialog step in an R/3 application program.

Therefore, physical locks in the database system are insufficient for the requirements of an R/3 transaction. Locks in the R/3 system must remain set for the duration of a whole SAP LUW, that is, over several dialog steps. They must also be capable of being handled by different work processes and application servers. As a result, each lock must apply on all servers in that R/3 system.

for more details refer to the link below

1)http://aspalliance.com/1136_Database_Access_using_Advanced_Business_Application_Programming_ABAP

2)
http://help.sap.com/saphelp_nw2004s/helpdata/en/fc/eb3969358411d1829f0000e829fbfe/content.htm

3)

http://www.itcserver.com/blog/2006/06/26/open-sql-vs-native-sql/

regards
ravish

plz reward if helpful

[jayanchembada]

Open SQL allows you to access database tables declared in the ABAP Dictionary

regardless of the database platform that you R/3 System is 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 administered by the ABAP Dictionary, and therefore integrate data that is not part of the R/3 System.

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

Regards

Jayan Chembadathil.

[Former Member]

he <u><b>Structured Query Language (SQL)</b></u> is a largely standardized language, which is used for accessing relational databases. It can be divided as follows:

<b>

Data Manipulation Language (DML)</b>

These statements are for reading and changing data in database tables.

<b>Data Definition Language (DDL)</b>

These statements are for creating and administering database tables.

<b>

Data Control Language (DCL)</b>

These statements are used for authorization and consistency checks.

<b>

Open SQL</b> are statements that make up a subset of Standard SQL which is fully integrated in ABAP. Open SQL consists of Data Manipulation Language (DML) which is a part of Standard SQL.

Some Open SQL keywords are as follows:

<b>SELECT </b>- It reads data from database tables.

<b>INSERT</b> - It adds rows to database tables.

<b>UPDATE</b> - It changes the contents of rows of database tables.

<b>

MODIFY</b> - It inserts rows into database tables or changes the content of existing rows.

<b>DELETE</b> - It deletes rows from database tables.

<b>OPEN CURSOR, FETCH, CLOSE CURSOR</b> - It reads rows of database tables using the cursor.

Return Codes

The following two system fields are filled with return codes by all Open SQL statements:

<b>SY-SUBRC:</b> The system field SY -SUBRC contains the value 0 after every Open SQL statement if the operation was successful. When a value is other than 0, then it is unsuccessful.

<b>SY-DBCNT:</b> The system field SY-DBCNT contains the number of database lines processed after an open SQL statement.

<u><b>Native SQL Scope</b></u>

Native SQL is very important as it allows you to execute nearly all available statements through the SQL programming interface (usually known as SQL Call Interface or similar) for directly executing SQL program code (using EXEC IMMEDIATE or a similar command).