topic Re: what is native sql? in Application Development and Automation Discussions

what is native sql?

Former Member — Mon, 16 Jul 2007 04:36:02 GMT

hi all,

what is native sql? can any one provide me some examples with native sql? and when do we go for writing prgm in native sql?

is native sql stms are supported for pool and cluster tables?

regds

hari

Re: what is native sql?

Former Member — Mon, 16 Jul 2007 04:39:18 GMT

Hi,

what is native sql? can any one provide me some examples with native sql? and when do we go for writing prgm in native sql?

please check out the link below it will help you

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

is native sql stms are supported for pool and cluster tables?

yes it is supported.

**********please reward points if the information is helpful to you*************

Re: what is native sql?

Former Member — Mon, 16 Jul 2007 04:41:19 GMT

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.

Scope of Native SQL

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.

Native SQL and the Database Interface

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.

Native SQL and Transactions

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.

Example :

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.

Cursor Processing

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>


EXEC SQL
  OPEN c1 FOR
    SELECT client, arg1, arg2 FROM table_001
     WHERE client = '000' AND arg2 = :arg2
ENDEXEC.
DO.
  EXEC SQL.
    FETCH NEXT c1 INTO :wa-client, :wa-arg1, :wa-arg2
  ENDEXEC.
  IF sy-subrc <> 0.
    EXIT.
  ELSE.
    <verarbeite Daten>
  ENDIF.
ENDDO.
EXEC SQL.
  CLOSE c1
ENDEXEC.

This example opens a cursor, reads data line by line, and closes the cursor again. As in Open SQL, SY-SUBRC indicates whether a line could be read.

reward points if it is usefull....

Girish

Re: what is native sql?

Former Member — Mon, 16 Jul 2007 04:41:27 GMT

Hi Hari,

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.

Scope of Native SQL

)

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 :x, OUT :y )
ENDEXEC.

Cursor Processing

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>


EXEC SQL
  OPEN c1 FOR
    SELECT client, arg1, arg2 FROM table_001
     WHERE client = '000' AND arg2 = :arg2
ENDEXEC.
DO.
  EXEC SQL.
    FETCH NEXT c1 INTO :wa-client, :wa-arg1, :wa-arg2
  ENDEXEC.
  IF sy-subrc <> 0.
    EXIT.
  ELSE.
    <verarbeite Daten>
  ENDIF.
ENDDO.
EXEC SQL.
  CLOSE c1
ENDEXEC.

This example opens a cursor, reads data line by line, and closes the cursor again. As in Open SQL, SY-SUBRC indicates whether a line could be read.

Data Types and Conversions

Using Native SQL, you can

Transfer values from ABAP fields to the database

Read data from the database and process it in ABAP programs.

Native SQL works without the administrative data about database tables stored in the ABAP Dictionary. Consequently, it cannot perform all of the consistency checks used in Open SQL. This places a larger degree of responsibility on application developers to work with ABAP fields of the correct type. You should always ensure that the ABAP data type and the type of the database column are identical.

If the database table is not defined in the ABAP Dictionary, you cannot refer directly to its data type. In this case, you should create a uniform type description in the ABAP Dictionary, which can then be used by all application programs.

If the table is defined in the ABAP Dictionary, you should remember that the sequence of fields in the ABAP Dictionary definition may not be the same as the actual sequence of fields in the database. Using the asterisk (*) in the SELECT clause to read all columns into a corresponding work area would lead to meaningless results. In the worst case, it would cause an error.

The

Native SQL module

of the database interface passes a description of the type, size, and memory location of the ABAP fields used to the database system. The relevant database system operations are usually used to access and convert the data. You can find details of these operations in the manuals for the programming interface of the relevant database system. In some cases, Native SQL also performs other compatibility checks.

The documentation from the various database manufacturers provides detailed lists of combinations of ABAP data types and database column types, both for storing ABAP field values in database tables (INSERT, UPDATE) and for reading database contents into ABAP fields (SELECT). You can also apply these descriptions for the input and output parameters of database procedures. Any combinations not listed there are undefined, and should not be used.

The following sections provide details of the data types and conversions for individual databases. Although they are database-specific, there are also some common features.

Recommended type combinations are underlined. Only for these combinations is behavior guaranteed from release to release. For any other combinations, you should assume that the description only applies to the specified release.

The results of conversions are listed in a results column:

"OK": The conversion can be performed without loss of data.

Operations that fail are indicated by their SQL error code. Errors of this kind always lead to program termination and an ABAP short dump.

In some cases, data is transferred without an SQL error occurring. However, the data is truncated, rounded, or otherwise unusable:

Right truncation.

"Left" or "right" applies to the normal way of writing a value. So, for example, if a number is truncated, its decimal places are affected.

: Left truncation

: Number is rounded up or down during conversion

: A number that was "too small" is rounded to 0 (underflow)

: The conversion result is undefined.

There are several possible results. The concrete result is either not known at all, or can only be described using a set of rules that is too complicated for practical use.

: The conversion returns the SQL value NULL.

: The conversion is performed without fields and unchecked.

The original data is converted, but without its format being checked. The result may therefore be a value invalid for the result type, which cannot be processed further. An example of this is a date field containing the value "99999999" or "abcdefgh" after conversion.

Combinations of ABAP data type and database column type can be divided into finer subcategories. Here, for example, using the transfer direction ABAP ® database (INSERT, UPDATE):

If the width of the ABAP field is greater than that of the database column, the ABAP field may contain values for which there is not enough space in the database column. This can produce other cases: The concrete data value in ABAP finds space in the database column, or not.

If the ABAP field is at most as long as the database column, there is always space for the ABAP value in the database column.

Some types, such as numeric columns, expect values in a particular format. This is particularly important in connection with character types, for example, when you want to write an ABAP character field (type C) into an integer column.

Reward pts if found usefull 🙂

regards

Sathish

Re: what is native sql?

Former Member — Mon, 16 Jul 2007 04:41:27 GMT

Hi,

Open SQL

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

One of the ways to access the database from a program is Open SQL. These Open SQL statements are nothing but a subset of Standard SQL, which is fully integrated in ABAP. Irrespective of which database system the R/3 installation is using, they allow you to access data. When I said that, Open SQL consists of the Data Manipulation Language (DML). I meant that it allows you to read (i.e. to SELECT) and change (i.e. to INSERT, UPDATE, DELETE) data.

Moreover, Open SQL also goes beyond Standard SQL. This is to provide statements that can simplify or speed up database access in conjunction with other ABAP constructions. Apart from that, it also gives you the freedom to buffer certain tables on the application server, thereby enabling you to save excessive database access. In this case, the database interface is responsible for comparing the buffer with the database. As far as buffer storage is concerned, they may be stored in two parts: the working memory of the current work process, and the shared memory for all work processes on an application server. The data in the various buffers is synchronized at set intervals by buffer management where an R/3 system is distributed across more than one application server. It should be noted that data in the buffer is not always up to date when you are buffering the database. That is why you should only use the buffer for data which does not change often. You can specify whether a table can be buffered in its definition in the ABAP Dictionary.

Open SQL consists of a set of ABAP statements. These statements perform operations on the central database in the R/3 system. The results of the operations and any error messages which come out of it are independent of the current database system. Thus, uniform syntax and semantics for all of the database systems supported by SAP is provided by Open SQL. Regardless of the current database system, the ABAP programs, which use Open SQL statements only, will work in any R/3 system. Moreover, Open SQL statements work only with database tables that have been created in the ABAP Dictionary.

You have the freedom to combine columns belonging to different database tables to a database view (or view for short) in the ABAP Dictionary. Views are also handled in exactly the same way as database tables in Open SQL statements.

Some Open SQL keywords are as follows:

SELECT - It reads data from database tables.

INSERT - It adds rows to database tables.

UPDATE - It changes the contents of rows of database tables.

MODIFY - It inserts rows into database tables or changes the content of existing rows.

DELETE - It deletes rows from database tables.

OPEN CURSOR, FETCH, CLOSE CURSOR - 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:

SY-SUBRC: 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.

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

Native SQL

The other possible way to access the database from a program is Native SQL. It is only loosely integrated into ABAP. It allows access to all of the functions contained in the programming interface of the respective database system. Native SQL statements are not checked and converted as compared to Open SQL statements. Unlike Open SQL, these are sent directly to the database system. The function of the database-dependent layer remains minimal when you use Native SQL. The Programs which use Native SQL are written specifically for a database system. You should avoid using Native SQL wherever possible when writing R/3 applications. However, you can use it in some parts of the R/3 Basis System, for instance, for creating or changing table definitions in the ABAP Dictionary.

Regardless of the database platform that your R/3 system is using, Open SQL allows you to access database tables, which are declared in the ABAP Dictionary. 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. Aside from that, you can also integrate data that is not part of the R/3 system.

As a rule, an ABAP program that contains database-specific SQL statements will not run under different database systems. You have to use Open SQL statements only, if your program is used on more than one database platform.

You must proceed with the EXEC SQL statement, and follow the ENDEXEC statement to use a: Native SQL statement. For example

Listing 1

EXEC SQL [PERFORMING <form>].

ENDEXEC.

Regards

Gaurav

Re: what is native sql?

seshatalpasai_madala — Mon, 16 Jul 2007 04:41:43 GMT

Hi,

SAP R/3 system can be installed basing on any kind of Database. But each Database has its own SQL statements. Like Oracle and DB2 etc.

So We cannot use Database dependent queries in our program. So SAP has to use a language that is Database independent. So SAP as come up with Open SQL.

But if you want to write some database specific queires you can do that with the help of Native SQL. So SAP allows execution of Database dependant queries with the help of Native SQL statement.

You can execute them using EXEC SQL and ENDEXEC.

POOL and CLUSTER tables are SAP specific, they are not database specific, POOL and CLUSTER tables have only one database table in the underlying database. So You need to know the name of underlying Database table of the POOL table to be able to execute a Native SQL query.

Regards,

Sesh

Re: what is native sql?

Former Member — Mon, 16 Jul 2007 04:52:53 GMT

hi all,

thanxs a lot for everyone. I have assigned points for everyone.

regds

hari