ABAP NATIVE SQL

[Former Member]

i need some information about NATIVE SQL.can anyone help it out

[Former Member]

hi veena,

in ABAP we will not use native sql as from one system to another system database wht we are using may differ for example if u write native sql commands for oracle as database in one system tht program may not work in the system whose database is other than oracle

any how if u want to write native sql commands then u have to write commands in between

exec.

end exec.

then in that case database utility will not come into picture

directly tht commands get executed..

for more information click the below link

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

Plzzzzzzzzzz Reward points if helpful.........

Thanks and Regards,

Sunil Kumar Mutyala

[Former Member]

HI

<b>Native SQL</b>

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.

<b>Native SQL Statements in ABAP Programs</b>

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 statement>

ENDEXEC.

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

In Native SQL statements, the data is transported between the database table and the ABAP program using host variables. These are declared in the ABAP program, and preceded in the Native SQL statement by a colon (:). You can use elementary structures as host variables. Exceptionally, structures in an INTO clause are treated as though all of their fields were listed individually.

If the selection in a Native SQL SELECT statement is a table, you can pass it to ABAP line by line using the PERFORMING addition. The program calls a subroutine <form> for each line read. You can process the data further within the subroutine.

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

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 system displays the following information:

The program uses the work area WA and the field C1 in the Native SQL SELECT statement. WA is the target area into which the selected data is written. The structure WA in the INTO clause is treated as though its components were all listed individually. INTO :WA-CONNID, :WA-CITYFROM, :WA-CITYTO. C1 is used in the WHERE clause. The subroutine LOOP_OUTPUT writes the data from WA to the screen.

<b>Scope of Native SQL</b>

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

<b>Native SQL and the Database Interface</b>

Native SQL statements bypass the R/3 database interface. There is no table logging, and no synchronization with the database buffer on the application server. For this reason, you should, wherever possible, use Open SQL to change database tables declared in the ABAP Dictionary. In particular, tables declared in the ABAP Dictionary that contain long columns with the types LCHR or LRAW should only be addressed using Open SQL, since the columns contain extra, database-specific length information for the column. Native SQL does not take this information into account, and may therefore produce incorrect results. Furthermore, Native SQL does not support automatic client handling. Instead, you must treat client fields like any other.

<b>Native SQL and Transactions</b>

To ensure that transactions in the R/3 System are consistent, you should not use any transaction control statements (COMMIT, ROLLBACK WORK), or any statements that set transaction parameters (isolation level...) using Native SQL.

<b>Stored Procedures</b>

To standardize the specific syntax of different database products, ABAP has a uniform syntax:

EXECUTE PROCEDURE <name> ( <parameter list> )

The parameters are separated by commas. You must also specify whether the parameter is for input (IN), output (OUT) or input and output (INOUT). For further information, refer to SAP Note 44977.

EXEC SQL

EXECUTE PROCEDURE proc1 ( IN , OUT :y )

ENDEXEC.

<b>Cursor Processing</b>

Cursor processing in Native SQL is similar to that in Open SQL:

OPEN <cursor name> FOR <statement>

FETCH NEXT <cursor name> INTO <target(s)>.

CLOSE <cursor name>

[Former Member]

Hi Veena,

As u Knw we open sql is used in ABAP .

if SQL statement native to Perticular Database called Native SQL.

U have to Use Keyword EXE Before Every statement. in open sql statement for using it as a Native SQL.

pls check & Revert....!!!

[Former Member]

hi,

open sql is the Sql statements we use in ABAP which are independent

of the underlying database.native sql statements are the ones which are directly related to the underlying database and only work with it. So use of native sql statements is avoided for portability purposes.

http://help.sap.com/saphelp_nw04/helpdata/en/51/ef9f94e4ba894d8e93b2eed7c9f82d/frameset.htm

regards,

pavan