Connecting to external databases via RFC-server
On my recent project I have faced the requirement to communicate with external database (MSSQL).
It was impossible to use DBConnect because application server running on AIX. Attempt to use UDConnect ended here.
So, I decided to create RFC server, that allow to communicate with external database via ODBC.
I hope it may be useful for someone who faced the same requirement.
Also it may be useful as RFC programming example.
It’s not as convenient as “usual” usage of SQL in ABAP, but the advantage is there is no no need to create database table with the same name and identical type in the ABAP Dictionary of the local AS ABAP, as DBConnect does.
How to make it work: in this scenario we assume to connect with NorthWind database on MSSQL.
- Download it from GitHub.
- Create ABAP objects: Z_SQL_PARAM structure, Z_SQL_PARAM_TT table type, Z_SQL_EXEC Z_SQL_COMMIT, Z_SQL_ROLLBACK and Z_SQL_SEL2CORRESPONDING function modules, ZEXAMPLE report.
- Set up RFC destination ZNORTHWIND in transaction SM59. Set Activation Type=Registered Server Program, Program ID= ZNORTHWIND.
- Set up RFC server. It can be done on the same machine where MSSQL running. At least you have to install MSSQL native client and Microsoft.VC80.CRT.
- Create some directory and put there rfcsrv_odbc.exe, sapnwrfc.ini and libsapucum.dll, sapnwrfc.dll from NWRFC.
- Edit sapnwrfc.ini :
DEST=DEST1 <– this is the label for connection entry, pass it as first parameter when run rfcsrv_odbc.exe
ASHOST=XX.XX.XX.XX <– sap application server ip-address
CLIENT=100 <– client number
TRACE=1 <– trace level
USER=BATCH <– user name for rfc server to connect with SAP AS
PASSWD=****** <– user’s password
SYSNR=00 <– system number
TPNAME=ZNORTHWIND <– Program ID.
- Set up ODBC DSN for NorthWind database. Choose “MSSQL native client” driver type.
- Run rfcsrv_odbc.exe, specify only first parameter DEST1: rfcsrv_odbc.exe DEST1 It pops up connection string build dialog. Choose NorthWind DSN, user name, password, and other options, click OK. RFC server prints out complete connection string. Copy it from console.
- Run rfcsrv_odbc.exe, specify DEST1 as first parameter and connection string as second parameter (see run.bat): rfcsrv_odbc.exe DEST1 “CONNSTR”.If everything fine, it prints out the following:
If not – see .trc file for details.
- In transaction SM59 perform connection test for ZNORTHWIND.
- Run (and examine) example report ZEXAMPLE.