Skip to Content
0
Jan 07, 2010 at 01:50 PM

Transfer data from SAP to external MySQL DB

870 Views

I've setup a external DB connection in SAP using ST04_MSS. The DB is MySQL5.027 and is setup using ODBC on SAP application.

In SAP I created the DB connection using Connection Parameters MSSQL_DATASOURCE=<odbcname>

In my ABAP program I'm now able to connect to the DB using Native SQL.

exec sql.
    connect to '<DBNAME>'
  endexec.

exec sql performing loop_output.
    select Name,Efternavn into :wa
    from Test
  endexec.

If not shown declarations and the form loop_output! But above statement is working okay, it list outs the values from the MySQL DB.

However If I choose to insert records into the table of the MySQL DB, using this snippet:

exec sql.

insert into Test (Name,Efternavn) values ('Henrik','Mortensen')

endexec.

The ABAP program will dump. With SQL error.

I tried to log what happent in ODBC and MySQL. Shown underneath. It seems it adds the statement select @@ROWCOUNT right after the insert into Test (Name,Efternavn) values ('Henrik','Mortensen')

This statement MySQL dosent accept, and might be the reasson why the program dumps. Is there anyway I can get SAP to supress or disable that last stament it adds ??

MySQL ODBC log

-- Query logging

--

-- Driver name: MySQL ODBC 5.1 Driver Version: 05.01.0006

-- Timestamp: 100107 10:04:28

select user_name();

-- Query logging

--

-- Driver name: MySQL ODBC 5.1 Driver Version: 05.01.0006

-- Timestamp: 100107 10:04:27

COMMIT;

insert into Test ( Name, Efternavn ) values ( 'Henrik', 'Mortensen'' )select @@ROWCOUNT;

ROLLBACK;

ROLLBACK;

COMMIT;

ROLLBACK;

MYSQL query LOG

100107 10:13:12 86 Connect root@xxxxxxx

86 Query SET NAMES utf8

86 Query SET character_set_results = NULL

86 Query SET SQL_AUTO_IS_NULL = 0

86 Query select database()

86 Query select database()

85 Query COMMIT

85 Query SET AUTOCOMMIT=1

85 Query SET AUTOCOMMIT=0

85 Query insert into Test ( Name, Efternavn ) values ( 'Henrik', 'Mortensen'' )

select @@ROWCOUNT 85 Query ROLLBACK

85 Query SET AUTOCOMMIT=1

85 Query SET AUTOCOMMIT=0

86 Query SELECT @@tx_isolation

86 Query select user_name()

85 Query ROLLBACK

85 Query SET AUTOCOMMIT=1

85 Query SET AUTOCOMMIT=0

85 Query COMMIT

85 Query SET AUTOCOMMIT=1

85 Query SET AUTOCOMMIT=0

86 Quit

85 Query ROLLBACK

85 Query SET AUTOCOMMIT=1

85 Quit