How can I connect to MySQL external database

[Former Member]

Hello.

I have a SAP system running in SQL Server 2003.

I need to connect to external MySQL DB., to operate with this information in ABAP program.

I have done the step necesary... I mean I go to DBCO transaction and configure the connection like this:

DB CONNECTION --> AFIS

DBMS --> MSS

user name --> xxxxxx

DBpass -->xxxxxx / xxxxxx

Conn Info -->MSSQL_SERVER=192.168.1.233 MSSQL_DBNAME=alliance OBJECT_SOURCE=alliance

I do a test program, when I do the statement CONNECT TO, sy-subrc 0 and connection = DEFAULT... I mean, with this form I cannot connect to MySQL Database...

Can you help me to do this?? I think the problem it's the connection string in DBCO... but I'm not sure...,

Would be possible to connect by MySQL ODBC??? I mean , installing the ODBC driver in my SAP server, and using this in ABAP Program??

Thks.

DATA: BEGIN OF wa,

cod_modelo(20),

END OF wa.

DATA: dbs TYPE dbcon-con_name.

DATA: con(20) TYPE c.

DATA : ls_wa LIKE wa.

con = 'AFIS'. "DB Connection in DBCO above

EXEC SQL.

CONNECT TO :con

ENDEXEC.

WRITE sy-subrc. ---> The result it's 4

EXEC SQL.

GET CONNECTION :con

ENDEXEC.

WRITE : con. --> The result it's DEFAULT

EXEC SQL.

SET CONNECTION DEFAULT

ENDEXEC.

write : con. --> The result it's DEFAULT

[markus_doehr2 · Accepted solution]

Check if

Note 178949 - MSSQL: Database MultiConnect with EXEC SQL

can help you.

Markus

[Former Member · Accepted solution]

Dear Markus.

This note explains very well how connect to MSSQL System... but my problem is that the Database system it's MySQL , and with this configuration I cannot get to connect with my database.

Do you know if it's possible to connect with this kind of database???

I see that in DBCO transaction the types of database that I can connect are this... What kind I must select?? ...

ADA SAP DB (ADABAS/D)

DB2 DB2 UDB para OS/390

DB4 DB2 UDB para AS/400

DB6 DB2 UDB para Unix y Windows

INF Informix

MSS Servidor Microsoft SQL

ORA Oracle

In this note 1248222 I see that I can connect by ODBC using the parameter MSSQL_DSN to connect by ODBC... I use this but I cannot connect yet.

Another idea??...

I think the key point it's to know if MySQL it's supported by DBCO..., because this note 1248222 explain how connect to external db but, MSSQL DB... not MySQL DB

Thks for your help.

Edited by: Pedro Gallardo Guzman on Apr 17, 2009 10:16 AM

[markus_doehr2 · Accepted solution]

> I think the key point it's to know if MySQL it's supported by DBCO..., because this note 1248222 explain how connect to external db but, MSSQL DB... not MySQL DB

I misread that - sorry.

It's not possible to connect to an MySQL database.

To to external connections to other databases you need

- a database client for the database you want to access

- a database interface library (lib_dbsl) for the target database

You may be able to install the MySQL client for Windows but the database interface library is missing. That library is responsible to translate the ABAP calls into the SQL dialect for the target database. Since that is not existing for MySQL it's not possible to connect.

Markus

[Former Member · Accepted solution]

Dear Marcus.

Then, if I understand your reply...I cannot do something.... SAP not support external connection to MySQL because MySQL not have an interface library (lib_dbsl)...

It's for it, that when I go to DBCO... in DBMS ---... I can select Oracle, MSSQLServer, DB2... for this Databases..., exists the library (lib_dbsl)???

In resume:

IT'S NOT POSSIBLE CONNECT TO MYSQL!!! ... I cannot believe it!!!...,

[Former Member · Accepted solution]

Hi,

>IT'S NOT POSSIBLE CONNECT TO MYSQL!!! ... I cannot believe it!!!...,

Why ? SAP products don't run on MYSQL. Why should it be supported ?

And I think that direct dblinks are very bad from an architectural point of view.

Just develop an interface. Why not implementing a web service able to fetch the necessary data from MYSQL and call this web service from you SAP software ?

Regards,

Olivier

[Former Member · Accepted solution]

Dear Olivier.

Accept your solution..., but my knowledge it's not very big.

How can I implementing a web service??

How can I call this web service from SAP??

Can you help me?

[markus_doehr2 · Accepted solution]

> It's for it, that when I go to DBCO... in DBMS ---... I can select Oracle, MSSQLServer, DB2... for this Databases..., exists the library (lib_dbsl)???

yes, for all those databases exists the database interface library.

> In resume:

> IT'S NOT POSSIBLE CONNECT TO MYSQL!!! ... I cannot believe it!!!...

Well - as far as I remember there were some times ago efforts to port SAP applications to MySQL. That would explain why there's also a file "DDLMYS.TPL" created if you execute R3ldctl during a system copy - amongst DDL files for all other databases. I believe this was at the time MySQL was promoting SAPDB/MaxDB.

MySQL is historically not a database engine for software, that requires transactional integrity; there were extensions to support that (InnoDB or others) and there was no customer demand in getting MySQL as engine for SAP applications. And only develop an interface to be able to connect to an external MySQL engine is not worth the effort.

However, there is hope Some BusinessObjects applications also run with and against MySQL engines, depending on how the strategy to integrate those into the SAP software stack there may (or may not) be an interface for that database in the future.

Markus

[Former Member · Accepted solution]

Thks Markus and all for your help.

[Former Member · Accepted solution]

-

[gudmundur_karlsson · Accepted solution]

Hi Pedro,

MSSQL_DSN should work. You should create your DSN using the ODBC Data Source Administrator (in Administrative Tools). Test it and make sure it works to the server and database you need.

Then create the DBCON (in transaction DBCO) with DBMS=MSS and your MySQL User/passwd.

The Conn. info (field CON_ENV) should only contain:

MSSQL_DSN=<your_dsn_name>

That's all - IF you're using the ODBC DBSL interface on your application server.

If you're using the older OLEDB DBSL (7.00 on sql2005 or older), then you must in addition put MSSQL_DATASOURCE=<your_dsn_name> in the CON_ENV field.

Hope this works for you.

Gudmundur

[Former Member · Accepted solution]

Dear Gudmundur.

Thks for your help..., That it's the solution.

Thks very much!!!!!....

[Former Member · Accepted solution]

I want to connect to a MySQL-DB on the client. Does anyone know how to do this and what settings I have to set in DBCON, on the client and on the server?

[gudmundur_karlsson · Accepted solution]

Hi Markus,

the client side DBCON settings are described above in my earlier response to Pedro. On the server you just have to set up the user/password and make sure the correct database context is used.

Gudmundur

[Former Member · Accepted solution]

>

> Hi Markus,
> the client side DBCON settings are described above in my earlier response to Pedro.  On the server you just have to set up the user/password and make sure the correct database context is used.
> Gudmundur

Hi Gudmundur,

I still don't really know what I have to do and on which system.

I have the SAP ERP on a server (probably Windows Server 200x) and I have my Notebook with Windows XP and a MySql 5.1 Server.

Where do I have to configure the ODBC? On my Laptop or on the Server 200x-System?

Do I have to add something like localhost in the DSN-name in the DBCON?

Is it possible to connect to my local server without using my Win XP Username + Password? I don't know where to pass both.

I always get the runtime error u201CConnection to database system not possible with identifier u201C<Identifier>u201D, but I donu2019t know what the problem is.

Markus

[gudmundur_karlsson · Accepted solution]

Hi Markus,

Your program to access the remote MySQL server is running on the application server of your ERP system (I assume). So the (System) DSN must be created on the application server on which the abap will run. The application server must be a windows server for this to work, and dbmssslib.dll must be installed on it.

You can use the ODBC Data Source Administrator on the SAP appserver to create and test the DSN.

In DBCON you add MSSQL_DSN=<dsn name> in the CON_ENV field. Just the dsn name, nothing else (no localhost).

The DBCON-USER_NAME should contain the MySQL user (not the XP os user), and the DBCON-PASSWORD field should contain the (encrypted) password for that MySQL user.

You may have to make sure your MySQL server on your laptop is able to accept connections from across the network. Check the MySQL docs for that.

Good luck,

Gudmundur

[Former Member · Accepted solution]

Thx a lot so far. It seems as if it might work. The last problem: Where can I get the dbmssslib.dll? I searched for several hours, but I couldn't find it. Can anyone post a link or give me a brief description how I can download it from the Service Marketplace?

[gudmundur_karlsson · Accepted solution]

Hi Markus, it should be something like:

Support Packages and Patches -> Entry by Application Group -> SAP NetWeaver -> SAP Net Weaver -> SAP NETWEAVER 7.0 -> Entry by Component -> Application Server ABAP -> SAP KERNEL 7.00 64-BIT UNICODE (or whatever your platform is) -> Windows Server on x64 64bit -> MS SQL SERVER -> Downloads -> lib_dbsl* (the latest).

Hope this helps,

Gudmundur

[Former Member · Accepted solution]

Hi Gudmundure,

i need to know if i can connect SAP ERP run on sql server 2008 DB to sql server 2003 or not and how i can connect .

kind regards,

Amr

[former_member326276 · Accepted solution]

Hi Gudmundur,

Nice workaround! Have you tried something similar with a linux aplication server?

Is it possible?

thanks.

best regards,

Sergio.

[wanjiru]

i had this error in SM21

AFTER I SET the Conn info parameters correctly the connection was successful

DB CONNECTION --> AFIS

DBMS --> MSS

user name --> dbuser

DBpass -->password / password

Conn Info --> MSSQL_SERVER=192.168.50.128\MSSQLSERVER2019E MSSQL_DBNAME=name OBJECT_SOURCE=SAPMonitor

[detlef_grau]

Hi Gudmundur,

good idea to set MSSQL_DATASOURCE=<your_dsn_name>. After that, the trace told me to install the client softfware for provider MSDASQL. I installed MSDASQL and the connection to mySQL works.

Thank you

Detlef

[detlef_grau]

Hi Gudmundur,

yes, I have dbmsslib.dll installed on the SAP System.

[gudmundur_karlsson]

hi Detlef,

can you post the developer trace section for the attempt to connect? From dev_w<??> for your workprocess?

Gudmundur

[gudmundur_karlsson]

Detlef,

also add MSSQL_DATASOURCE=<your_dsn_name> to the Conn info. It doesn't hurt to have both.

Gudmundur

[detlef_grau]

We have SAP ERP 6.0 running with DB6 on Windows 2003 64-Bit. I tried to get an ODBC connection to mySQL like described above, but it didn't work. I have defined the ODBC DSN on the SAP System and the test did work. But with DBCO definition (DBMS = MSSQL , Conn.info = MSSQL_DSN=<dsn name>) there is no connection possible (ERROR SQL error 1-: [DBNETLIB][ConnectionOpen (Connect()).]SQL Serverdoes not exist or access denied.).

Have you any idea what could be wrong??

[gudmundur_karlsson]

do you have dbmssslib.dll installed on your windows appserver?

It's required if you're using connection type MSSQL in DBCON.

[Former Member]

-