topic Re: Native SQL Statement to sql server with linked server in Application Development and Automation Discussions

Native SQL Statement to sql server with linked server

Former Member — Tue, 08 Sep 2015 15:06:38 GMT

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".

Re: Native SQL Statement to sql server with linked server

Former Member — Tue, 08 Sep 2015 20:44:28 GMT

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 )

Re: Native SQL Statement to sql server with linked server

rosenberg_eitan — Wed, 09 Sep 2015 03:07:55 GMT

Hi,

Please have a look at

Using SDB_ADBC package class for native SQL .

Regards.

Re: Native SQL Statement to sql server with linked server

Former Member — Wed, 09 Sep 2015 05:35:04 GMT

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".

Re: Native SQL Statement to sql server with linked server

Former Member — Wed, 09 Sep 2015 05:40:49 GMT

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, ? )"

Re: Native SQL Statement to sql server with linked server

rosenberg_eitan — Wed, 09 Sep 2015 06:35:45 GMT

Hi,

Did you had any success with any SQL statement ?

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

Regards.

Re: Native SQL Statement to sql server with linked server

Former Member — Wed, 09 Sep 2015 07:44:44 GMT

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( ).

Re: Native SQL Statement to sql server with linked server

rosenberg_eitan — Wed, 09 Sep 2015 08:57:17 GMT

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.

Re: Native SQL Statement to sql server with linked server

Former Member — Wed, 09 Sep 2015 09:47:49 GMT

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

The problem is my complex sql statement.

Re: Native SQL Statement to sql server with linked server

rosenberg_eitan — Wed, 09 Sep 2015 10:21:30 GMT

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.

Re: Native SQL Statement to sql server with linked server

Former Member — Wed, 09 Sep 2015 22:05:35 GMT

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.