Error in Connect to Database

[Former Member]

Dear Experts,

I have a requirement where i am connecting a table of a schema, this table exist in schema. When i try to execute the follwoing statements

EXEC SQL.
CONNECT TO 'D01'
ENDEXEC.
EXEC SQL.
Delete from DB1.arcust
ENDEXEC.

EXEC SQL.
commit
ENDEXEC.

Here 'D01' is my sy-sysid , 'DB1' is my database schema name and 'ARCUST' is my table name, i get the dump like this

Runtime Errors        DBIF_DSQL2_OBJ_UNKNOWN

Exception              CX_SY_NATIVE_SQL_ERROR

Short text

    Table does not exist in database

Find below screenhot for the same.

I am not able to locate where exactly the issue is, as the table exist in the schema and  the statements are also of valid syntax.

Please guide me on this.

Best Regards,

[nabheetscn]

Hi Prachi

Which database is it..? Did you try using DB1.ARCUST in caps?

Nabheet

[Former Member]

Hi Nabheet,

Thanks for the prompt reply. Yes i tried in CAPS DB1.ARCUST but it did not worked.

Also DB1 is the schema name of the database.

BR.

[nabheetscn]

What is the database from which you are using trying to delete the data..?

Nabheet

[Former Member]

Hi Nabheet,

Database is MaxDB.

Regards,

[nabheetscn]

Prachi Please check this link along with steps. Please search for native SQL using MAXdb you will find the right way.

http://scn.sap.com/thread/3248457

[Former Member]

Hi Nabheet,

Thanks for the link.

But can you tell me in detail how can i check in SAP whether my MAXDb connection is working or not? What steps i need to follow in SAP?.

Thanks.

[Former Member]

Find below the DBM operator rights screenshot. Might be this can be of any help in analyzing the problem:

[nabheetscn]

Hi Prachi

Has the basis set up in Dbconnect been done..? parameter con type should be  dbcon-con_name with lowercase option.

What is value of sy-subrc you are getting after connect to?

Nabheet

[Former Member]

Thanks a lot nabheet for your prompt replies.

Database connection is UP and sy-subrc value is 0. Our problem is related to schema.

Find below screenshot of DBCON entry.

[venkateswaran_k]

Hi Prachi,

Did you define the DBCON - parameters?

Is your database is on same server or at different server?

If it is in different server, is connection / access is established ( firewall etc)

Regards,

Venkat

[Former Member]

Hi Venkat,

Yes we have define DBCON parameters as shown in screenshot above. Also my database is on the same server.

Regards,

Prachi

[venkateswaran_k]

Hi Prachi,

Use the following template

Example:

delete from [D01].[DB1].arcust

that is,  Connection ID .  Schema ID.  Table Name

Regards,

Venkat

[nabheetscn]

Or check this weblog from Graham

http://scn.sap.com/community/abap/connectivity/blog/2008/10/27/using-abap-to-access-non-sap-database...

[Former Member]

Hi venkat,

I tried by your synatx and this time got some new dump like this.

[nabheetscn]

Prachi

did you check the blog it is doing same thing as you want to perform

[Former Member]

Nabheet, I read that blog, though the requirement is same but i did not got anything useful in it, as i am able to connect to D01 but not to schema, which is no where mentioned in the blog.

[nabheetscn]

Prachi.. that blog has a source code attached to it which unforunately i can not access.. If possible have you gone through it?

[Former Member]

Yes i have gone through the source code: It is accessing in the same manner like we are doing that is SCHEMANAME.TABLENAME

Find below the blog source code:

REPORT  zmona_read_customer_addr.

TYPES: BEGIN OF mona_cust_addr_type,

         cno(4)        TYPE n,

         title(7)      TYPE c,

         name(40)      TYPE c,

         zip(5)        TYPE c,

         city(3)       TYPE c,

         state(2)      TYPE c,

         address(40)   TYPE c,

       END OF mona_cust_addr_type.

DATA: ls_custaddr  TYPE           mona_cust_addr_type,

      lt_custaddr  TYPE TABLE OF  mona_cust_addr_type.

* Connect to MONA database

EXEC SQL.

  CONNECT TO 'MONA'

ENDEXEC.

IF sy-subrc <> 0.

  MESSAGE 'Unable to connect to MONA' TYPE 'E' DISPLAY LIKE 'I'.

  RETURN.

ENDIF.

* Define database cursor

EXEC SQL.

  OPEN dbcur FOR

    SELECT cno, title, name, zip, city, state, address

           FROM HOTEL.CUSTOMER_ADDR

ENDEXEC.

* Fill customer itab

DO.

  EXEC SQL.

    FETCH NEXT dbcur INTO :ls_custaddr-cno,

                          :ls_custaddr-title,

                          :ls_custaddr-name,

                          :ls_custaddr-zip,

                          :ls_custaddr-city,

                          :ls_custaddr-state,

                          :ls_custaddr-address

  ENDEXEC.

  IF sy-subrc <> 0.

    EXIT.

  ELSE.

    APPEND ls_custaddr TO lt_custaddr.

  ENDIF.

ENDDO.

* Close connection to MONA

EXEC SQL.

  CLOSE dbcur

ENDEXEC.

* Reset to "default connection"

EXEC SQL.

  SET CONNECTION DEFAULT

ENDEXEC.

* Print 20 records

LOOP AT lt_custaddr INTO ls_custaddr.

  WRITE: /,

          ls_custaddr-cno,

          ls_custaddr-title,

          ls_custaddr-name,

          ls_custaddr-zip,

          ls_custaddr-city,

          ls_custaddr-state,

          ls_custaddr-address.

  IF sy-tabix > 20.

    EXIT.

  ENDIF.

ENDLOOP.

[nabheetscn]

Hi Prachi

Can you please ask your basis people to check following note whether they have done as mentioned here.

955670 - DB multiconnect with MaxDB as secondary database

Seconldy can you please check whether the user SAPD01 has access for deleting the data in MAXDB table..Can you please try to delete the entry using this id

Thanks

Nabheet

[venkateswaran_k]

Hi Prachi,

try this code and let me know what is the result.  I believe, your connection is not set. 

Using the Try catch, you can narrow down.

Try this code temporarily, by chaning yur connection parameters.

data: DBN(128).

TRY .
      EXEC SQL.
         CONNECT TO 'D01'
      ENDEXEC.
      EXEC SQL.
        SET CONNECTION 'D01'
      ENDEXEC.

      EXEC SQL.
        SELECT db_name() INTO :DBN FROM SVERS
      ENDEXEC.

      WRITE: / 'current database name 1', DBN.

      EXEC SQL.
           SET CONNECTION DEFAULT
      ENDEXEC.

      EXEC SQL.
           SELECT db_name() INTO :DBN FROM SVERS
      ENDEXEC.

           CATCH cx_sy_native_sql_error INTO exc_ref.
              error_text = exc_ref->get_text( ).
        MESSAGE error_text TYPE 'I' RAISING sql_error.

ENDTRy.

[venkateswaran_k]

Also, please share your code.