Native SQL

[Former Member]

Hi ABAPers i want to know about Native SQL my requirment is to create a new data-base table using Native SQL in ABAP

if any screen shots please help me out and i want this Native SQL in detail help me out ...

Thanks in Advance

<REMOVED BY MODERATOR>

Edited by: Alvaro Tejada Galindo on Apr 15, 2008 10:58 AM

[Former Member]

avoid such requirements, and aks your consultant to overthink his concept there is under NO circumstances a need to create a database table without creating it throug SAP and a transparent table.

[Former Member]

Hi Ravi,

Native SQL 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.

All Native SQL statements bypass SAP buffering.

The ENDEXEC statement sets sy-dbcnt to the number of table rows processed in the last Native SQL statement. After implicit cursor processing with PERFORMING, sy-dbcnt contains the total number of lines read.

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

Example

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.

But it is not advisable to write the native sql commands as see different systems in landscape can have diff database so it may not get executed in other systems in the landscape so avoiod using this native sql statements

[Former Member]

hi ,

go throught this link

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

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

definately u will get the idea.

regard ,

sandeep

[Former Member]

hi ravichandra,

Native SQL

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.

Native SQL Statements in ABAP Programs

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

<REMOVED BY MODERATOR>

Thanks and regards,

A.kalyan.

Edited by: Alvaro Tejada Galindo on Apr 15, 2008 4:53 PM

[Former Member]

Hi Ravi,

the main difference between nativesql and opensql is native sql will directly interact with the database but open sql will interact with database interface which then convert the statement respective to the database we are using.it is strictly adviced that not to use native sql.why because if you write a nativesql statement to create a ztable it will directly interact with the underlying database and if you execute the report it will work for that specific database only.the statements will vary in nativesql based upon the database.but in open sql once you write a statement to create a ztable in a report it will work for any type of database so you can use or send this to any client.

but when coming to coding there is no much difference between sql and nativqsql.you will use exec sql..end exec in extra for native sql.

to create a table in native sql you use the syntax

exec sql.

create table ztable (f1 type t1,f2 type t2).

end exec.

<REMOVED BY MODERATOR>

Edited by: Alvaro Tejada Galindo on Apr 15, 2008 4:54 PM

[Former Member]

Hi ,

Check these links .

http://help.sap.com/saphelp_470/helpdata/EN/fc/eb3b8b358411d1829f0000e829fbfe/content.htm

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

Regards,

Muneesh Gitta.