Native SQL Statement to sql server with linked server

[Former Member]

I´ve created an native sql connection and now I want to execute the following statement.

If i execute the program with a static parameter in the where clause, it works.

If i execute the program with a dynamic parameter, I get the following dump.

How could I execute the script with a parameter?

CODE:

EXEC SQL.

      CONNECT TO 'MSSQL_KANT01' AS 'V'

    ENDEXEC.

  EXEC SQL.

    SET CONNECTION 'V'

  ENDEXEC.

  EXEC SQL.

    open c4 for SELECT password FROM OPENQUERY ( KANT, 'SELECT password FROM order.V_persons where personnelnumber = :lv_int')

  ENDEXEC.

  DO.

    EXEC SQL.

      FETCH NEXT c4 into :lv_str

    ENDEXEC.

    IF sy-subrc <> 0.

      EXIT.

  ENDDO.

  EXEC SQL.

    CLOSE c4

  ENDEXEC.

STATIC:

open c4 for SELECT password FROM OPENQUERY ( KANT, 'SELECT password FROM order.V_persons where personnelnumber = 4100')

DYNAMIC:

open c4 for SELECT password FROM OPENQUERY ( KANT, 'SELECT password FROM order.V_persons where personnelnumber = :lv_int')

DUMP:

ERROR 7350.

Cannot get the column information from OLE DB provider "MSDASQL" for linked server "KANT".

[former_member226239]

I guess you just have one value to pass to the SQL statement. Are you writing ABAP code here? If yes, try this and let me know.....

data: lv_cdata type c length 200,

         lv_value type I.

lv_value = 4100.

concatenate 'SELECT password FROM order.V_persons where personnelnumber =' lv_value

                     into lv_cdata

                     separated by space.

open c4 for SELECT password FROM OPENQUERY ( KANT, lv_cdata )

[Former Member]

I also tried it with one value to pass, like your version.

No success.

I get the following dump:

Incorrect syntax near '@P1'.

SQL: "SELECT password FROM OPENQUERY (

KANT, ?  )"

[former_member226239]

Try this...

data: lv_stmt type string,

         lv_parnr type parnr.

lv_parnr = '1234567890'.

lv_stmt = 'SELECT ...... where personnelnumber = '. (make sure you have space after 😃

concatenate lv_stmt lv_parnr '''' into lv_stmt.    (after lv_parnr it is ' ' ' ' without spaces).

If it does not work please let me know the lv_stmt data after above concatenate statement.

[rosenberg_eitan]

Hi,

Please have a look at

Using SDB_ADBC package class for native SQL .

Regards.

[Former Member]

Hi,

I tried it with SDB_ADBC with no success.

CODE:

DATA:

    l_col1     TYPE i,

    p_con_ref  TYPE REF TO cl_sql_connection,

    l_col2     TYPE string,

    p_con_name TYPE dbcon-con_name.

c_tabname = 'order.V_persons'.

  sql_statment = `SELECT password FROM OPENQUERY ( KANT, 'SELECT password FROM order.V_persons where nr = ?' )`.

  p_con_name = 'MSSQL_KANT'.

  p_con_ref = cl_sql_connection=>get_connection( p_con_name ).

  " create the query string

  DATA(l_stmt) = sql_statment ##no_text.

  DATA(l_stmt_ref) = p_con_ref->create_statement( tab_name_for_trace = c_tabname ).

  l_stmt_ref->set_param( REF #( l_col1 ) ).

  l_col1 = 4100.

  DATA(l_res_ref) = l_stmt_ref->execute_query( l_stmt ).

  l_res_ref->set_param( REF #( l_col2 ) ).

  WHILE l_res_ref->next( ) > 0.

    break username.

  ENDWHILE.

  l_res_ref->close( ).

I get the following dump.

CX_SQL_EXCEPTION

Cannot execute the query "SELECT password FROM order.V_persons

where nr = ?" against OLE DB provider "MSDASQL" for linked server

"KANT".

[rosenberg_eitan]

Hi,

Did you had any success with any SQL statement ?

Try a simple one to verify that you have a working environment .

Regards.

[Former Member]

Hi,

Yes. With a simple statement I had success:

simple one:

DATA: sql_statment TYPE string,

        c_tabname    TYPE c LENGTH 30.

  DATA:

    l_col1     TYPE i,

    p_con_ref  TYPE REF TO cl_sql_connection,

    l_col2     TYPE i,

    p_con_name TYPE dbcon-con_name.

* Generate SQL statment .

  c_tabname = 'ADBC_DEMO__'.

  p_con_name = 'MSSQL_KANT'.

  IF p_con_name IS INITIAL.

    p_con_ref = NEW #( ).

  ELSE.

    p_con_ref = cl_sql_connection=>get_connection( p_con_name ).

  ENDIF.

  " create the query string

  DATA(l_stmt) =

      `select * from ` && c_tabname && ` where COL1 >= ?` ##no_text.

  DATA(l_stmt_ref) = p_con_ref->create_statement( tab_name_for_trace = c_tabname ).

  l_stmt_ref->set_param( REF #( l_col1 ) ).

  l_col1 = 2.

  DATA(l_res_ref) = l_stmt_ref->execute_query( l_stmt ).

  l_res_ref->set_param( REF #( l_col2 ) ).

  WHILE l_res_ref->next( ) > 0.

    new-LINE.

    WRITE l_col2.

  ENDWHILE.

  l_res_ref->close( ).

First try:

DATA: sql_statment TYPE string,

        c_tabname    TYPE c LENGTH 30.

  DATA:

    l_col1     TYPE string,

    p_con_ref  TYPE REF TO cl_sql_connection,

    l_col2     TYPE string,

    p_con_name TYPE dbcon-con_name.

* Generate SQL statment .

  c_tabname = 'order.V_persons'.

  p_con_name = 'MSSQL_KANT'.

  IF p_con_name IS INITIAL.

    p_con_ref = NEW #( ).

  ELSE.

    p_con_ref = cl_sql_connection=>get_connection( p_con_name ).

  ENDIF.

  " create the query string

  DATA(l_stmt) =

      `select password from OPENQUERY ( KANT, ? )` ##no_text.

  DATA(l_stmt_ref) = p_con_ref->create_statement( tab_name_for_trace = c_tabname ).

  l_stmt_ref->set_param( REF #( l_col1 ) ).

  l_col1 = `SELECT password FROM ` && c_tabname && ` where personnelnumber = 4100`.

  DATA(l_res_ref) = l_stmt_ref->execute_query( l_stmt ).

  l_res_ref->set_param( REF #( l_col2 ) ).

  WHILE l_res_ref->next( ) > 0.

    new-LINE.

    WRITE l_col2.

  ENDWHILE.

  l_res_ref->close( ).

Second try:

DATA: sql_statment TYPE string,

        c_tabname    TYPE c LENGTH 30.

  DATA:

    l_col1     TYPE i,

    p_con_ref  TYPE REF TO cl_sql_connection,

    l_col2     TYPE string,

    p_con_name TYPE dbcon-con_name.

* Generate SQL statment .

  c_tabname = 'order.V_persons'.

  p_con_name = 'MSSQL_KANT'.

  IF p_con_name IS INITIAL.

    p_con_ref = NEW #( ).

  ELSE.

    p_con_ref = cl_sql_connection=>get_connection( p_con_name ).

  ENDIF.

  " create the query string

  DATA(l_stmt) =

      `select password from OPENQUERY ( KANT, 'SELECT password FROM ` && c_tabname && ` where personnelnumber = ?' )` ##no_text.

  DATA(l_stmt_ref) = p_con_ref->create_statement( tab_name_for_trace = c_tabname ).

  l_stmt_ref->set_param( REF #( l_col1 ) ).

  l_col1 = 4100.

  DATA(l_res_ref) = l_stmt_ref->execute_query( l_stmt ).

  l_res_ref->set_param( REF #( l_col2 ) ).

  WHILE l_res_ref->next( ) > 0.

    new-LINE.

    WRITE l_col2.

  ENDWHILE.

  l_res_ref->close( ).

[rosenberg_eitan]

Hi,

Try something simple .

Regards.

REPORT z_r_eitan_test_28_02 .

START-OF-SELECTION.

  PERFORM at_start_of_selection .

*----------------------------------------------------------------------*

FORM at_start_of_selection .

  DATA: it_sflight TYPE TABLE OF sflight . " Flight

  DATA: it_tab_dref TYPE REF TO data . "  class

  GET REFERENCE OF it_sflight INTO it_tab_dref.

  DATA: sql_string TYPE string .

  sql_string = 'SELECT * FROM SFLIGHT' .

  DATA: ob_sql_connection         TYPE REF TO cl_sql_connection . " Database Connection

  DATA: ob_sql_statement          TYPE REF TO cl_sql_statement . " SQL statement

  DATA: ob_sql_result_set         TYPE REF TO cl_sql_result_set . " Resulting Set of an SQL Query

  DATA: ob_sql_exception          TYPE REF TO cx_sql_exception . " Exception Class for SQL Error

  DATA: rows_ret TYPE i . " Ret of type Integers

  ob_sql_connection = cl_sql_connection=>get_connection(  ) .

  ob_sql_statement  = ob_sql_connection->create_statement( ).

  ob_sql_result_set = ob_sql_statement->execute_query( sql_string ).

  ob_sql_result_set->set_param_table( it_tab_dref ).

  CHECK ob_sql_result_set IS BOUND .

  ob_sql_result_set->next_package( ).

  ob_sql_result_set->close( ).

  ob_sql_connection->close( ) .

  BREAK-POINT .

  DATA: ob_salv_table TYPE REF TO cl_salv_table . " Basis Class for Simple Tables

  CALL METHOD cl_salv_table=>factory

    IMPORTING

      r_salv_table = ob_salv_table

    CHANGING

      t_table      = it_sflight.

  ob_salv_table->display( ) .

ENDFORM.

[Former Member]

I already tried a working simple example. That is working. (See my answer overhead.)

The problem is my complex sql statement.

[rosenberg_eitan]

Hi,

This is good.

I am no expert on Microsoft server so I cannot relate to "OPENQUERY"  .

Have you tried this query on non SAP client ?

Any logs on the server ?

Regards.