topic Re: Trying to connect to SQL SERVER in Application Development and Automation Discussions

Trying to connect to SQL SERVER

Former Member — Tue, 15 Jul 2014 06:37:02 GMT

We have SAP on Oracle database but as we are implementing our HR system on SQL Server so we need to connect to SQL SERVER through ABAP in order to retrieve some information. We created the Connection successfully but when trying to retrieve the data it is giving us following dump :

Runtime Errors DBIF_DSQL2_INVALID_CURSOR

Except. CX_SY_NATIVE_SQL_ERROR

Invalid interruption of a database selection

The code through which i am trying to retrieve the data is as follows :

DATA: c1 TYPE cursor.

DATA : BEGIN OF EMP_DATA OCCURS 0,

FNAME(5000) TYPE C,

END OF EMP_DATA.

DATA : fnm TYPE STRING.

EXEC SQL.

CONNECT TO 'HRMS'

ENDEXEC.

if sy-subrc eq 0.

MESSAGE 'Connected..' TYPE 'I'.

else.

MESSAGE 'Not Connected..' TYPE 'I'.

endif.

EXEC SQL.

OPEN c1 for

SELECT FNAME

FROM view_EMPDATAPayroll_New

ENDEXEC.

if sy-subrc eq 0.

MESSAGE 'Access Successful..' TYPE 'I'.

else.

MESSAGE 'Access Not Successful..' TYPE 'I'.

endif.

DO.

EXEC SQL.

FETCH NEXT c1 INTO :EMP_DATA.FNAME;

ENDEXEC.

APPEND EMP_DATA.

IF sy-subrc <> 0.

EXIT.

ENDIF.

ENDDO.

EXEC SQL.

CLOSE c1

ENDEXEC.

EXEC SQL.

DISCONNECT 'HRMS'

ENDEXEC.

We have tried a lot to solve the error but every time we are getting the same dump. It is giving dump at the following command :

FETCH NEXT c1 INTO :EMP_DATA.FNAME;

The connection properties are also as follows :

DB Connection HRMS

DBMS MSS

User Name sa

Conn. info MSSQL_SERVER=192.168.0.60\SQLEXPRESS MSSQL_DBNAME=HRAlign_Rubamin

Please help me out.

Tarun.

Re: Trying to connect to SQL SERVER

Arun_Prabhu_K — Tue, 15 Jul 2014 06:46:58 GMT

Hello Tarun.

Semicolon should not come at the end of FETCH NEXT c1 INTO :EMP_DATA.FNAME

Regards.

Re: Trying to connect to SQL SERVER

Former Member — Wed, 16 Jul 2014 05:03:09 GMT

Thanks for your timely response Arun. Solved the issue as I was using Message inside the cursor , I removed that and problem is solved.

When I am retrieving only 2 columns in select statement then it is getting executed but when I am giving more than 2 columns then it is giving error "Incorrect Syntax Near".

But now I made following changes in program and getting dump (with different message).

The code is as under.

DATA: c1 TYPE cursor.

DATA : BEGIN OF EMP_DATA OCCURS 0,

FNAME(100) TYPE C,

LNAME(100) TYPE C,

JOINING_CTC(16) TYPE P DECIMALS 2,

END OF EMP_DATA.

DATA : fnm(100) TYPE c,

lnm(100) TYPE c,

jng_ctc(16) TYPE P DECIMALS 2.

EXEC SQL.

CONNECT TO 'HRMS'

ENDEXEC.

EXEC SQL.

OPEN c1 for

SELECT FNAME LNAME JOINING_CTC FROM view_EMPDATAPayroll_New.

ENDEXEC.

DO.

EXEC SQL.

FETCH NEXT c1 INTO :EMP_DATA

ENDEXEC.

APPEND EMP_DATA.

IF sy-subrc <> 0.

EXIT.

ENDIF.

ENDDO.

EXEC SQL.

CLOSE c1

ENDEXEC.

EXEC SQL.

DISCONNECT 'HRMS'

ENDEXEC.

LOOP AT EMP_DATA.

WRITE: EMP_DATA-FNAME , EMP_DATA-LNAME , EMP_DATA-JOINING_CTC.

ENDLOOP.

At execution I am getting dump with the following message:

Short text

SQL error 102 occurred when executing Native SQL.

What happened?

The error 102 occurred in the current database connection "HRMS".

Error Text of Database: "Incorrect syntax near 'JOINING_CTC'."

SQL Statement Responsible:"SELECT FNAME LNAME JOINING_CTC FROM

view_EMPDATAPayroll_New."

Error analysis

An exception occurred that is explained in detail below.

The exception, which is assigned to class 'CX_SY_NATIVE_SQL_ERROR', was not

caught and

therefore caused a runtime error.

The reason for the exception is:

Error Text of the Database: "Incorrect syntax near 'JOINING_CTC'."

Regards.

Tarun.

Re: Trying to connect to SQL SERVER

Former Member — Wed, 16 Jul 2014 05:18:51 GMT

Hi,

When using native sql , dont use any full stop (.) at the end of syntax. Also comma missed after each field. In native sql it should be with comma

Change below syntax and try:


OPEN c1  for
SELECT FNAME,
                 LNAME , 
                 JOINING_CTC  FROM view_EMPDATAPayroll_New  ". Remove full stop from this syntax

Regards,

Re: Trying to connect to SQL SERVER

Arun_Prabhu_K — Wed, 16 Jul 2014 06:28:30 GMT

Tarun,

Chandra is right.

Better have a look at basic native SQL syntax.

Regards.

Re: Trying to connect to SQL SERVER

Former Member — Wed, 16 Jul 2014 07:05:06 GMT

Thanks for your help Chandra & Arun. Its solved as Comma was missing between the fields and I removed fullstop at the end.