ISO8583 Message Converter to XML SQL CSV interface specification mapping

Posted on 25th Jan 2022 3655 views

Sample ISO8583 conversion to SQL and XML

This article describes how the ISO8583 Converter creates an XML,  SQL and CSV object from an incoming TCP/IP ISO8583 binary message, and then sends it to a HTTp host as a POST, and a Mysql database, and a flat file on disk respectively. Go to ISO8583 converter to JSON for more details and download links.

Analysis of a sample ISO8583 to JSON conversion, from the Converter. 

ISO8583 Request

25-01-22 06:42:35:783:- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
25-01-22 06:42:35:784:CNV: Message received by Converter
25-01-22 06:42:35:801:CNV: MessageType :0100
25-01-22 06:42:35:804:CNV: Bitmap :723C46D128E0B000 bits:0111001000111100010001101101000100101000111000001011000000000000
25-01-22 06:42:35:809:CNV: F02_PAN :9876500000306082
25-01-22 06:42:35:810:CNV: F03_ProcessingCode :010000
25-01-22 06:42:35:810:CNV: F04_AmountTransaction :000000005000
25-01-22 06:42:35:811:CNV: F07_TransmissionDateTime :0125064235
25-01-22 06:42:35:811:CNV: F11_SystemTraceAuditNumber :006232
25-01-22 06:42:35:812:CNV: F12_DateTimeLocalTransaction :220125
25-01-22 06:42:35:812:CNV: F13_DateLocalTxn :0125
25-01-22 06:42:35:813:CNV: F14_DateExpiration :3012
25-01-22 06:42:35:813:CNV: F18_MerchantType :5961
25-01-22 06:42:35:814:CNV: F22_POSDataCode :020
25-01-22 06:42:35:814:CNV: F23_CardSequenceNumber :000
25-01-22 06:42:35:815:CNV: F25_MessageReasonCode :00
25-01-22 06:42:35:815:CNV: F26_CardAcceptorBusinessCode :53
25-01-22 06:42:35:816:CNV: F28_DateReconciliation :000000000
25-01-22 06:42:35:818:CNV: F32_AcquiringInstitutionIdenti:27610000001
25-01-22 06:42:35:819:CNV: F35_Track2Data :9876500000306082=30121011123123000
25-01-22 06:42:35:819:CNV: F37_RetrievalReferenceNumber :423541700000
25-01-22 06:42:35:820:CNV: F41_CardAcceptorTerminalIdenti:20390059
25-01-22 06:42:35:820:CNV: F42_CardAcceptorIdentification:111120000012000
25-01-22 06:42:35:825:CNV: F43_CardAcceptorNameLocation :contact@neaPay.comAlmere-AmsterdamNeth
25-01-22 06:42:35:826:CNV: F49_CurrencyCodeTransaction :566
25-01-22 06:42:35:826:CNV: F51_CurrencyCodeCardholderBill:566
25-01-22 06:42:35:826:CNV: F52_PINData :FEE8CA6A604F09F0
25-01-22 06:42:35:828:- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

ISO8583 Response

25-01-22 06:42:36:343:- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
25-01-22 06:42:36:344:CNV: Message response sent by Converter
25-01-22 06:42:36:344:CNV: MessageType :0110
25-01-22 06:42:36:345:CNV: Bitmap :723040012EE08400 bits:0111001000110000010000000000000100101110111000001000010000000000
25-01-22 06:42:36:346:CNV: F02_PAN :9876500000306082
25-01-22 06:42:36:346:CNV: F03_ProcessingCode :010000
25-01-22 06:42:36:346:CNV: F04_AmountTransaction :000000005000
25-01-22 06:42:36:347:CNV: F07_TransmissionDateTime :0125064236
25-01-22 06:42:36:347:CNV: F11_SystemTraceAuditNumber :006232
25-01-22 06:42:36:348:CNV: F12_DateTimeLocalTransaction :220125
25-01-22 06:42:36:348:CNV: F18_MerchantType :5961
25-01-22 06:42:36:349:CNV: F32_AcquiringInstitutionIdenti:27610000001
25-01-22 06:42:36:349:CNV: F35_Track2Data :9876500000306082=30121011123123000
25-01-22 06:42:36:350:CNV: F37_RetrievalReferenceNumber :423541700000
25-01-22 06:42:36:350:CNV: F38_ApprovalCode :940262
25-01-22 06:42:36:354:CNV: F39_ActionCode :00
25-01-22 06:42:36:354:CNV: F41_CardAcceptorTerminalIdenti:20390059
25-01-22 06:42:36:355:CNV: F42_CardAcceptorIdentification:111120000012000
25-01-22 06:42:36:355:CNV: F43_CardAcceptorNameLocation :contact@neaPay.comAlmere-AmsterdamNeth
25-01-22 06:42:36:356:CNV: F49_CurrencyCodeTransaction :566
25-01-22 06:42:36:357:CNV: F54_AdditionalAmounts :0002504C000055102.020001504+000006030701
25-01-22 06:42:36:357:- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

Expected functionality and behaviour

The way the ISO8583 Converter works is to immediately convert the binary ISO8583 message to a JSON

This JSON is available in the javaScript engine, which we can then convert into any XML, SQL, CSV

Default business action of the neaPay Converter

Usually customers want that neapay handles all ISO8583-related data.

That is why, by default, the converter pre-builds the response message to the acquirer (POS, ATM, mobile app, e-commerce, bank, switch).

Also, by default, the converter declines the transaction

Action code 99 (to be changed according to the functional specification)

Therefore, we compose the decline reponse transaction, convert it and and send it to the Issuer.

The JSON response

[{"name":"MessageType","value":"0110"},{"name":"Bitmap","value":"723040012EE08400"},{"name":"F02_PAN","value":"9876500000306082"},{"name":"F03_ProcessingCode","value":"010000"},{"name":"F04_AmountTransaction","value":"000000005000"},{"name":"F05_AmountReconciliation","value":""},{"name":"F06_AmountCardholderBilling","value":""},{"name":"F07_TransmissionDateTime","value":"0125064236"},{"name":"F09_ConvRateReconciliation","value":""},{"name":"F10_ConvRateBill","value":""},{"name":"F11_SystemTraceAuditNumber","value":"006232"},{"name":"F12_DateTimeLocalTransaction","value":"220125"},{"name":"F13_DateLocalTxn","value":""},{"name":"F14_DateExpiration","value":""},{"name":"F15_DateSettlement","value":""},{"name":"F16_DateConversion","value":""},{"name":"F17_DateCapture","value":""},{"name":"F18_MerchantType","value":"5961"},{"name":"F19_AcquiringInstitutionCountryCode","value":""},{"name":"F21_CntryCodeFrdInst","value":""},{"name":"F22_POSDataCode","value":""},{"name":"F23_CardSequenceNumber","value":""},{"name":"F24_FunctionCode","value":""},{"name":"F25_MessageReasonCode","value":""},{"name":"F26_CardAcceptorBusinessCode","value":""},{"name":"F27_ApprovalCodeLength","value":""},{"name":"F28_DateReconciliation","value":""},{"name":"F29_ReconciliationIndicator","value":""},{"name":"F30_AmountsOriginal","value":""},{"name":"F32_AcquiringInstitutionIdentificationCode","value":"27610000001"},{"name":"F33_ForwardInstitutionIdentificationCode","value":""},{"name":"F34_PANExtended","value":""},{"name":"F35_Track2Data","value":"9876500000306082=30121011123123000"},{"name":"F36_Track3Data","value":""},{"name":"F37_RetrievalReferenceNumber","value":"423541700000"},{"name":"F38_ApprovalCode","value":"940262"},{"name":"F39_ActionCode","value":"00"},{"name":"F40_ServiceCode","value":""},{"name":"F41_CardAcceptorTerminalIdentification","value":"20390059"},{"name":"F42_CardAcceptorIdentificationCode","value":"111120000012000"},{"name":"F43_CardAcceptorNameLocation","value":"contact@neaPay.comAlmere-AmsterdamNeth"},{"name":"F44_AdditionalResponseData","value":""},{"name":"F45_Track1Data","value":""},{"name":"F46_AmountFees","value":""},{"name":"F48_AdditionalDataPrivate","value":""},{"name":"F49_CurrencyCodeTransaction","value":"566"},{"name":"F50_CurrencyCodeReconciliation","value":""},{"name":"F51_CurrencyCodeCardholderBilling","value":""},{"name":"F52_PINData","value":""},{"name":"F53_Security_CtlInfo","value":""},{"name":"F54_AdditionalAmounts","value":"0002504C000055102.020001504+000006030701"},{"name":"F55_ICCData","value":""},{"name":"F56_OriginalDataElements","value":""},{"name":"F57_AuthorisationLifeCycleCode","value":""},{"name":"F58_AuthorisingAgentInstitutionIDCode","value":""},{"name":"F59_TransportData","value":""},{"name":"F60_CSMReserved","value":""},{"name":"F62_Reserved","value":""},{"name":"F63_PrivateData","value":""},{"name":"F64_MAC","value":""},{"name":"F67_ExtendedPaymentData","value":""},{"name":"F68_ReceivingInstitutionCountryCode","value":""},{"name":"F70_AuthInstCntryCode","value":""},{"name":"F72_DataRecord","value":""},{"name":"F90_OriginalDataElements","value":""},{"name":"F93_TxnDestInstID","value":""},{"name":"F94_TxnOrgInstId","value":""},{"name":"F95_ReplacementAmounts","value":""},{"name":"F96_KeyManagementData","value":""},{"name":"F100_RecInstIdCode","value":""},{"name":"F102_AccountId1","value":""},{"name":"F103_AccountId2","value":""},{"name":"F123_Reserved","value":""},{"name":"F124_NFC","value":""},{"name":"F127_ReservedPrivate","value":""},{"name":"F128_MAC","value":""}]

Standard behaviour of the converter TO XML and SQL.

 ISO8583 to SQL conversion mapping

The converter by default uses the same names of the ISO8583 fields for the database table columns.

But it also has the capability to define custom names, within the ISO8583 definition.

Example: MessageType:{name:"MessageType",length_type:"fixed 4",data_type:"binary",base:"10",fill:"zeros",value:"",db_column:"msg_type"},

A sample conversion to SQL, with default table column names:

25-01-22 06:42:36:069: SQL Converter wrote this SQL record to reports//SQLFile-2201250642.sql INSERT INTO transaction_table ( MessageType, Bitmap, F02_PAN, F03_ProcessingCode, F04_AmountTransaction, F05_AmountReconciliation, F06_AmountCardholderBilling, F07_TransmissionDateTime, F09_ConvRateReconciliation, F10_ConvRateBill, F11_SystemTraceAuditNumber, F12_DateTimeLocalTransaction, F13_DateLocalTxn, F14_DateExpiration, F15_DateSettlement, F16_DateConversion, F17_DateCapture, F18_MerchantType, F19_AcquiringInstitutionCountryCode, F21_CntryCodeFrdInst, F22_POSDataCode, F23_CardSequenceNumber, F24_FunctionCode, F25_MessageReasonCode, F26_CardAcceptorBusinessCode, F27_ApprovalCodeLength, F28_DateReconciliation, F29_ReconciliationIndicator, F30_AmountsOriginal, F32_AcquiringInstitutionIdentificationCode, F33_ForwardInstitutionIdentificationCode, F34_PANExtended, F35_Track2Data, F36_Track3Data, F37_RetrievalReferenceNumber, F38_ApprovalCode, F39_ActionCode, F40_ServiceCode, F41_CardAcceptorTerminalIdentification, F42_CardAcceptorIdentificationCode, F43_CardAcceptorNameLocation, F44_AdditionalResponseData, F45_Track1Data, F46_AmountFees, F48_AdditionalDataPrivate, F49_CurrencyCodeTransaction, F50_CurrencyCodeReconciliation, F51_CurrencyCodeCardholderBilling, F52_PINData, F53_Security_CtlInfo, F54_AdditionalAmounts, F55_ICCData, F56_OriginalDataElements, F57_AuthorisationLifeCycleCode, F58_AuthorisingAgentInstitutionIDCode, F59_TransportData, F60_CSMReserved, F62_Reserved, F63_PrivateData, F64_MAC, F67_ExtendedPaymentData, F68_ReceivingInstitutionCountryCode, F70_AuthInstCntryCode, F72_DataRecord, F90_OriginalDataElements, F93_TxnDestInstID, F94_TxnOrgInstId, F95_ReplacementAmounts, F96_KeyManagementData, F100_RecInstIdCode, F102_AccountId1, F103_AccountId2, F123_Reserved, F124_NFC, F127_ReservedPrivate, F128_MAC ) VALUES ( '0110', '723040012EE08400', '9876500000306082', '010000', '000000005000', '', '', '0125064236', '', '', '006232', '220125', '', '', '', '', '', '5961', '', '', '', '', '', '', '', '', '', '', '', '27610000001', '', '', '9876500000306082=30121011123123000', '', '423541700000', '940262', '00', '', '20390059', '111120000012000', 'contact@neaPay.comAlmere-AmsterdamNeth', '', '', '', '', '566', '', '', '', '', '0002504C000055102.020001504+000006030701', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '' )