topic Re: Open sql & native sql in Application Development and Automation Discussions

Open sql & native sql

Former Member — Tue, 04 Sep 2007 06:07:43 GMT

Hi,

What is the differenec b/w open sql & native sql?

Thanks........

Re: Open sql & native sql

Former Member — Tue, 04 Sep 2007 06:09:18 GMT

Open SQL - ABAP specific SQL

Native SQL - DB specific SQL

Sameer

Re: Open sql & native sql

Former Member — Tue, 04 Sep 2007 06:12:36 GMT

Hi Ankita,

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.

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.

Thanks,

Vinay

Re: Open sql & native sql

Former Member — Tue, 04 Sep 2007 06:17:04 GMT

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.

To use a Native SQL statement, you must precede it with the EXEC SQL statement, and follow it with the ENDEXEC statement as follows:

EXEC SQL [PERFORMING form].

Native SQL Anweisung

ENDEXEC.

e.g.

REPORT demo_native_sql.

DATA: BEGIN OF wa,

connid TYPE spfli-connid,

cityfrom TYPE spfli-cityfrom,

cityto TYPE spfli-cityto,

END OF wa.

DATA c1 TYPE spfli-carrid VALUE 'LH'.

EXEC SQL PERFORMING loop_output.

SELECT connid, cityfrom, cityto

INTO :wa

FROM spfli

WHERE carrid = :c1

ENDEXEC.

FORM loop_output.

WRITE: / wa-connid, wa-cityfrom, wa-cityto.

ENDFORM.

The Open SQL statement for reading data from database tables is:

SELECT result

INTO target

FROM source

[WHERE condition]

[GROUP BY fields]

[HAVING cond]

[ORDER BY fields].

Please go through the link:

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

Please reward the helpful entries.

Regards,

Raman.

Re: Open sql & native sql

Former Member — Tue, 04 Sep 2007 06:19:02 GMT

Hi ..

Here are some of the differences.

Open SQL , native SQL are the interfaces to create the database applicatons.

Open SQL is consistant across different types of existing Databases.

Native SQL is the database language specific to database.Its API is specific to the databse.

Open SQL API is consistent across all vendors

Open SQL allows you to access all database tables known to the SAP system, regardless of the database manufacturer. Sometimes, however, we may want to use database-specific SQL statements called Native SQL in your ABAP/4 program.

To avoid incompatibilities between different database tables and also to make ABAP/4 programs independent of the database system in use, SAP has created a set of separate SQL statements called Open SQL. Open SQL contains a subset of standard SQL statements as well as some enhancements which are specific to SAP.

A database interface translates SAP's Open SQL statements into SQL commands specific to the database in use. Native SQL statements access the database directly

Please reward if useful.

Re: Open sql & native sql

former_member198270 — Tue, 04 Sep 2007 06:19:57 GMT

Hi Ankita ,

Native SQL is the one which is Database understandable and

Open SQL is language specific SQL like ABAP Java etc .

A query is served in the following steps .

1. User requests for some data in the form of query from ABAP that is Open SQL .

2. That query goes to database interpreter which converts the Open SQL to DB/Native SQL which is understandable to data base only .

3. after retrieving data from DB Query is replied back to the user and hence the query is served .

Reward points if helpful .

Regards

Amber S

Re: Open sql & native sql

Former Member — Tue, 04 Sep 2007 06:22:43 GMT

Hi Ankita,

Native SQL allows you to use databasespecific

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.

Open SQL consists of a set of ABAP statements that perform operations on the central database

in the R/3 System. The results of the operations and any error messages are independent of the

database system in use. Open SQL thus provides a uniform syntax and semantics for all of the

database systems supported by SAP. ABAP programs that only use Open SQL statements will

work in any R/3 System, regardless of the database system in use. Open SQL statements can

only work with database tables that have been created in the ABAP Dictionary.

In other words, OPEN SQL is ABAP specific and NATIVE SQL is database specific.

I think that open sql is translated to nativeSQL. Open is much safer but it is somehow limited.

Open SQL refers to commands that can be translated into statements of any other SQL language.

For Example :

MS-SQL Server and Oracle both are databases. There are two different languages that are used to query these databases and get results. Somme commands exist only in MS-SQL and some only in Oracle.

Open SQL is used when we want tp write queries that will work on any underlying database. It contains only those commands which can be translated into any underlying databases command.

Open SQL is a subset of native SQL . SQL has been divided into two parts.

> Open SQL can recognized by the ABAP program.

> They are 10 SQL statements out of which we use only 5.

1. Select.

2. Insert.

3. Delete.

4. Modify.

5. Update.

6. Fetch.

7. Open Cursor.

8. Close Cursor.

9. Roll Back.

10. Commit.

out of which we use only the first 5.....

> Native SQL. cannot be recognized by the ABAP program.

> To execute Native SQL

Exec SQL.

.......

......

End Exec.

as it cannot be recognized by the ABAP we go for Open SQL.

Reward points if helpful.

Regards,

Hemant