h3. Confession I am a bad programmer writing dirty code. So don't try the following anywhere else but at home. But try it. It's fun, easy and powerful. h3. Purpose In his interesting recent weblog on Data Standardization (/people/pravin.bhute/blog/2006/03/24/data-standardization-building-strong-foundation-for-mdm-150-part-1) Pravin Buthe states that +Corporations having heterogeneous landscapes today are facing the need for a single source of true 'data'.+ I'd like to challenge this postulate. Data, or more precisely information according to Pravin, has a context. This context has it's own ontology, allowing the maximum expression of facts in this special context. Data standardization in my opinion is an attempt to neglect the eligibility of the parallel existence of different contexts or ontolgies and therefore if widely adopted would lead to a loss of expressiveness of information. Hospital patients and members of Harley Davidson clubs are simply different although they are some kind of persons. Technically it would probably make sense to standardize languages or to use a single language at all (e.g. Esperanto) but culturally it does not. Who would deny that there are differences in languages, things that can only be expressed in either innuit, hindi, german or whatever language. Recent technologies and concepts like service orientation, distributed computing, P2P networks or web ontology language OWL allow for a different approach. Leave data or information in it's natural and/or specialized (and/or performant) distributed realms and, if necessary, provide that data to an 'integration context' by means of services, ontolgy mediations, or intelligent directories. This said, let's come down to a simple example showing how to access data stored in an arbitrary remote database from within ABAP. We will do this by creating a data hub web service in PHP, accepting an SQL statement and specification of the database to use as input parameters and returning a recordset to the caller. This service will be called by a webservice client in ABAP and the results be put into a generic internal table. Types will not be mapped properly in this example. Conceptually this has to be done by the client since the service is agnostic of clients and their data types. The service supports the client by returning information about the source's data types. h3. Prerequisites The examples shown here require ABAP WAS 6.40+ and PHP 5.x. The default soap libraries of PHP are used. Example data is stored in a mysql database. h3. The DB service WSDL We want to pass the service some general information (database, credentials to use) as well as an SQL query. Our service has to return a description of the structure of the resultant recordset as well as the content. Furthermore it should return an error information if one occurs. This structure is layed out in our server WSDL looking like this: In short, this WSDL defines input parameters DBName, DBUser, DBPass and QueryString and output parameters TableDescription (a table describing each column by Name and Type), TableContent(a table of rows containing the values of the components) and an ErrorString (being filled whenever something goes wrong). A weakness of this construct is the missing relation between the table description an the content, therefore we have to make sure that content components come in the right order. The rest of the WSDL defines administrational data like message names, binding to SOAP and the adress location. h3. The service in PHP The next step is to create the webservice based on this WSDL. This is very easy. We define a service script named according to the soap address parameter (...sqlserver.php), instanciate the service with the WSDL, add a function according to the WSDL's input function parameter ZtwSql and finally implement the SQL query in this function and return the output parameters. function ZtwSql($name) { $DatabaseName = $name->DBName; $DbHostName = "localhost"; $DbUserName = $name->DBUser; $DbPassWord = $name->DBPass; if(mysql_connect("$DbHostName", "$DbUserName", "$DbPassWord")) { if(mysql_select_db("$DatabaseName")){ $query = $name->QueryString; $result = mysql_query($query); if($result) { $fields = mysql_num_fields($result); $table = mysql_field_table($result, 0); for ($i=0; $i < $fields; $i++) { $arhc['Name'] = mysql_field_name($result, $i); $arhc['Type'] = mysql_field_type($result, $i); // $len = mysql_field_len($result, $i); $arh[] = $arhc; } while ($line = mysql_fetch_array($result, MYSQL_ASSOC)) { if(isset($arl)) { unset($arl); }; foreach ($line as $key => $col_value) { $arl[] = $col_value; } $ar[] = $arl; } } else { $errorstring = mysql_error(); }; $td = $arh; } else { $errorstring = "Database Error: " . mysql_error(); } } else { $errorstring = "Connection Error: " . mysql_error(); } $outarray = array( TableDescription => $td, TableContent => $ar, ErrorString => $errorstring); return ($outarray); } $server = new SoapServer("ztwsql1.wsdl"); $server->addFunction("ZtwSql"); $server->handle(); ?> h3. Testing the service locally using a client in PHP Let's create a little test script to test the functionality locally. // local $wsdlurl ="http://tonysub/ws/ztwsql1.wsdl"; $login = ""; $password = ""; function maketable($header,$node) { echo ''; foreach($header as $ra1) { echo ''; foreach($ra1 as $key => $ra2) { echo ''; } echo ''; } foreach($node as $ra1) { foreach($ra1 as $ra2) { echo ''; foreach($ra2 as $ra3) { if (is_array($ra3)) { foreach($ra3 as $ra4) { echo ''; } } else { echo ''; } } echo ''; } } echo ' | ' . $ra2->Name . ' | | ' . $ra4 . ' | ' . $ra3 . ' | '; return; } ?> $client = new SoapClient($wsdlurl, array('login' => $login, 'password' => $password, 'trace' => true, 'exceptions' => true)); $qustr = "Select * from remote_person where ID > '1'"; try { $ra = $client->ZtwSql(array( 'DBName' => 'test', 'DBUser' => 'sapuser', 'DBPass' => 'sap123', 'QueryString' => $qustr)); if(strlen($ra->ErrorString) == 0) { echo 'The Query was: ' . $qustr . ' ---- '; maketable($ra->TableDescription, $ra->TableContent); } else { echo $ra-> ErrorString; } } catch (SoapFault $e) { echo 'Caught an Error: (' . $e->faultcode . ') - ' . $e->faultstring; } echo " ----
"; echo "Request :
".htmlspecialchars($client->__getLastRequest()) ."
"; echo "Response:
".htmlspecialchars($client->__getLastResponse())."
"; echo " "; ?> In our RDBMS we create a simple database 'test' and a test table 'remote_person' looking like this:
Now we are ready to run the test client against the web service. As you can see in the script code, we specify the database parameters and run a query +Select * from remote_person where ID > '1'+. The result ist this 
So, we've got our column names and the content according to the selection. If we change the password to a wrong one, we get a non-initial ErrorString shown below 
Both images show the debugging info at the page's foot containing the raw messages. h3. The web service client in ABAP Now we are ready to access this service from within ABAP. First we have to create a webservice proxy object. Goto SE80 and proceed as follows: 
Choose URL or HTTP Location and enter the location of the WSDL. In the next window select a package (e.g. $tmp) and a prefix for the various objects created. Proceed through the wizard and finally have the proxy object and anything else created. The last step is to activate the proxy object. 
Next go to transaction LPCONFIG and create a default logical port. 
Now we are ready to write a simple test report to test our proxy object. REPORT ZTW_CALLSQL_WS. data: client type ref to ZTW_CO_ZTW_SQL, input type ztw_ztw_sql, output type ztw_ztw_sql_response. input-dbname = 'test'. input-dbuser = 'sapuser'. input-dbpass = 'sap123'. input-query_string = 'select * from remote_person'. create object client. *TRY. CALL METHOD CLIENT-> ZTW_SQL EXPORTING INPUT = input IMPORTING OUTPUT = output . * CATCH CX_AI_SYSTEM_FAULT . * CATCH CX_AI_APPLICATION_FAULT . *ENDTRY. break bcuser. ZTW_SQL EXPORTING INPUT = input IMPORTING OUTPUT = output. * CATCH CX_AI_SYSTEM_FAULT . * CATCH CX_AI_APPLICATION_FAULT . *ENDTRY. if output-error_string EQ ''. loop at output-table_description-item into tdi. cin-name = tdi-name. cin-type = 'ttype'. append cin to cin_tab. endloop. loop at cin_tab into cin. comp-name = cin-name. comp-type ?= cl_abap_typedescr=>describe_by_name( cin-type ). APPEND comp TO new_comp_tab. endloop. structype = cl_abap_structdescr=>create( new_comp_tab ). READ table cin_tab index 1 into cin. APPEND cin-name TO key. tabletype = cl_abap_tabledescr=>create( p_line_type = structype p_table_kind = cl_abap_tabledescr=>tablekind_sorted p_key = key ). **** create data ***** CREATE DATA dref TYPE HANDLE tabletype. ASSIGN dref->* TO . endloop. ** ---------------------------------------------- loop at This report does the following: It asks for the input parameters and calls the PHP web service. From the returned TableDescription table it takes the column names and (source) types. From this information a structure type +structype+ is created. In this simple example all original components are mapped to an ABAP type +ttype+ which is character 32. Of course we could define some further ABAP types and map to them according to the source type information. From the structure type a table type +tabletype+ is created. The types created are used to create data objects and subsequently a generic table ++. The content of the returned table TableContent is assigned to accordingly. In this example the generic table is only used to output the tabular data received to the screen but it could handily be used for further processing. In case of an error the error message is dumped to the screen. The result looks like this: 
"; echo "Request :
".htmlspecialchars($client->__getLastRequest()) ."
"; echo "Response:
".htmlspecialchars($client->__getLastResponse())."
"; echo " "; ?> In our RDBMS we create a simple database 'test' and a test table 'remote_person' looking like this:
Now we are ready to run the test client against the web service. As you can see in the script code, we specify the database parameters and run a query +Select * from remote_person where ID > '1'+. The result ist this So, we've got our column names and the content according to the selection. If we change the password to a wrong one, we get a non-initial ErrorString shown below Both images show the debugging info at the page's foot containing the raw messages. h3. The web service client in ABAP Now we are ready to access this service from within ABAP. First we have to create a webservice proxy object. Goto SE80 and proceed as follows: Choose URL or HTTP Location and enter the location of the WSDL. In the next window select a package (e.g. $tmp) and a prefix for the various objects created. Proceed through the wizard and finally have the proxy object and anything else created. The last step is to activate the proxy object. Next go to transaction LPCONFIG and create a default logical port. Now we are ready to write a simple test report to test our proxy object. REPORT ZTW_CALLSQL_WS. data: client type ref to ZTW_CO_ZTW_SQL, input type ztw_ztw_sql, output type ztw_ztw_sql_response. input-dbname = 'test'. input-dbuser = 'sapuser'. input-dbpass = 'sap123'. input-query_string = 'select * from remote_person'. create object client. *TRY. CALL METHOD CLIENT-> ZTW_SQL EXPORTING INPUT = input IMPORTING OUTPUT = output . * CATCH CX_AI_SYSTEM_FAULT . * CATCH CX_AI_APPLICATION_FAULT . *ENDTRY. break bcuser. ZTW_SQL EXPORTING INPUT = input IMPORTING OUTPUT = output. * CATCH CX_AI_SYSTEM_FAULT . * CATCH CX_AI_APPLICATION_FAULT . *ENDTRY. if output-error_string EQ ''. loop at output-table_description-item into tdi. cin-name = tdi-name. cin-type = 'ttype'. append cin to cin_tab. endloop. loop at cin_tab into cin. comp-name = cin-name. comp-type ?= cl_abap_typedescr=>describe_by_name( cin-type ). APPEND comp TO new_comp_tab. endloop. structype = cl_abap_structdescr=>create( new_comp_tab ). READ table cin_tab index 1 into cin. APPEND cin-name TO key. tabletype = cl_abap_tabledescr=>create( p_line_type = structype p_table_kind = cl_abap_tabledescr=>tablekind_sorted p_key = key ). **** create data ***** CREATE DATA dref TYPE HANDLE tabletype. ASSIGN dref->* TO . endloop. ** ---------------------------------------------- loop at This report does the following: It asks for the input parameters and calls the PHP web service. From the returned TableDescription table it takes the column names and (source) types. From this information a structure type +structype+ is created. In this simple example all original components are mapped to an ABAP type +ttype+ which is character 32. Of course we could define some further ABAP types and map to them according to the source type information. From the structure type a table type +tabletype+ is created. The types created are used to create data objects and subsequently a generic table ++. The content of the returned table TableContent is assigned to accordingly. In this example the generic table is only used to output the tabular data received to the screen but it could handily be used for further processing. In case of an error the error message is dumped to the screen. The result looks like this:
3 Comments
