The Lessons We Learned from Upgrading Web Service 8.2 to 8.5 in eStore

Elliott Web Service 8.5 changed ORDER_NO, INV_NO, and other keys from numeric to alphanumeric to accommodate a bigger range. This results in changes in the Web Service interface. This article documents our experience when we upgraded the Web Service from 8.2 to 8.5 in Elliott eStore.

Elliott eStore is written in csharp and all codes are strongly typed. The process of the upgrade is relatively straightforward. The main steps are:
  1. Install Elliott Service 8.5.
  2. Configure all service references to point to the 8.5 Elliott Service and update all service references.
  3. Compile -> which will result in many compilation errors due to date type changes in the service references.
  4. Fix each error. The fixes are simply changing data type from int to string.
  5. Test.

For the upgrade, the actual code change is trivial. It's simply updating the data type from int to string. The most difficult part of the upgrade is to identify all the areas that need to be updated. If your code base is written in a strongly typed language where the compiler will point you to all the areas, the upgrade process is straightforward. However, if you don't have an easy way to identify all the areas that need to be updated easily in your application, the following side notes will be helpful to understand what problems you might run into if you miss an area. 

In the initial eStore upgrade, we only updated EliorderService and tested order creation. The order was created successfully but it wasn't showing in the eStore order list page. The reason: the order list page uses OrderInquiry service, which was also updated. The OrderInquiry service has many filters, one of which is the ORDER_NO. The order list page does not use the ORDER_NO filter. Prior to the upgrade, it specified 0 (the default value for int), which is "nothing" on the Web Service side, and the filter would not be applied. After the upgrade, the ORDER_NO is a string and specifying 0 would no longer be "nothing" on the Web Service side. As a result, the Web Service was trying to match an order with order no = "0." This was easily resolved once we identified what was happening and updated the order list page.

There are many Elliott Web Service methods that  return a dataset in the response. Dataset is not strongly typed. Even in a strongly typed language code base, the compiler would not give us any warnings if we accessed a data column that had been updated to string. These codes would fail at run time. To identify this type of potential problem, we searched for "ORDER_NO" and "INV_NO" (e.g., DataRow["ORDER_NO"] which is a common way to access data in DataSet). We reviewed the result and made any necessary changes.

Another area to consider is any code that uses the QueryTurnaround method. Since document number are now string instead of int type fields, queries may require single quotes around the document number. In our testing we have seen that a query like this will work correctly against a converted 8.5 database:
select * from "CPBOXSHP" WHERE CPBOXSHP_ORD_NO = 677506;    

However, a query like this will fail:
select * from "CPBOXSHP" WHERE CPBOXSHP_ORD_NO > 677500;

Adding the single quotes around the order number field will resolve the issue.
select * from "CPBOXSHP" WHERE CPBOXSHP_ORD_NO > '677500';  

This 8.2 QueryTurnaround code example will fail with an 8.5 database:

Dim Service As New webServicesQueryTurnaround.QueryTurnaround()
Dim Result As New webServicesQueryTurnaround.ExecuteQueryResult
Dim query As String = “ORDER_NO, ORDER-DATE, ORDER-TYPE, ORDER-SHIP-TO-NO FROM CPORDHDR WHERE ORDER_NO > 112487 AND ORDER_NO < 115877”
Dim numberOfRecords As String = “999”
Result = Service.ExecuteQuery(“”, “”, query, numberOfRecords)

If Result.ReturnCode = 0 Then
  If Result.Data.Tables(0).Rows.Count > 0 Then
    lblResult.Text = “First Matching Order No. is” & _
      Result.Data.Tables(0).Rows(0).Item("ORDER_NO")
  Else
    lblResult.Text = “No Matching Order.”
  End If
Else
  lblResult.Text = “Error”
  lblErrorCode.Text = Result.ReturnCode.ToString
End If

To resolve the issue, the query should contain single quotes around the order numbers:
Dim query As String = “ORDER_NO, ORDER-DATE, ORDER-TYPE, ORDER-SHIP-TO-NO FROM CPORDHDR WHERE ORDER_NO > '112487' AND ORDER_NO < '115877'”

Also, you will receive an error if running an 8.5 web service and the ElliottService configuration file is configured to use a ServerDSN that is not using version 8.5 DDFs. For example, if using the QueryTurnaround service with the following query:

  Dim query As String = “ORDER_NO FROM CPORDHDR WHERE ORDER_NO = 'AA0011'”  

You will receive the following error:

Pervasive.Data.SqlClient.PsqlException (0x80004005): Pervasive.Data.SqlClient.Lna.k: [LNA][PSQL][SQL Engine]Error in predicate: ORDER_NO = 'AA0011' 

To resolve this, you must use a ServerDSN that is using version 8.5 DDFs.
  
The following table lists all of the services, methods and the structures that were changed for V8.5. 

Service

Method or Structure

Change

El2crcrdService

Structure: SalesTrxInput

INV_NO

Structure: SalesTrxResult

CreditDocumentNo

Structure: ForceInput

INV_NO

Structure: ForceResult

CreditDocumentNo

Structure: RefundInput

INV_NO

Structure: RefundResult

CreditDocumentNo

El2getfrService

GetFrtByOrder

OrderNo

EliaptrxService

Structure: CreateTrxHeaderInput

VoucherNo, CheckNo

Structure: CreateTrxHeaderResult

VoucherNo, InvoiceNo,CheckNo

Structure: CreateTrxDistInput

VoucherNo

Structure: CreateTrxDistResult

VoucherNo, InvoiceNo, CheckNo, ApplyToNo

PostTrx

VoucherNo

Structure: PostTrxInput

VoucherNo

Structure: PostTrxResult

VoucherNo, CheckNo

EliarachService

Structure: ViewOrderACHAcctInput

OrderNo

Structure: AssignOrderACHAcctInput

OrderNo

EliautdpService

Structure: AutoDepositInput

ApplyTo

Structure: AutoDepositResult

CheckNo

ElibmordService

AddWorkOrder

OrderNo

ChangeWorkOrder

OrderNo

DeleteWorkOrder

OrderNo

PrintWorkOrder

OrderNo

AddProductionTrx

OrderNo

ChangeProductionTrx

OrderNo

DeleteProductionTrx

OrderNo

PostProductionTrx

OrderNo

Structure: AddWorkOrderResult

OrderNo

ElicshtxService

DeleteCashTrx

CheckNo

Structure: CashTrxForAdd

CheckNo

Structure: CashReceiptApplyToForAdd

ApplyToNo

EliinvocService

ReadHeader

INV_NO

ReadInvItem

INV_NO

Structure: ReadHeaderResult

INV_NO, INV_APPLY_TO_NO, INV_CHECK-NO, INV_ORDER_NO

Structure: ReadInvItemResult

INV_ITM_INV_NO, INV_ITM_ORG_BK_ORDNO, INV_ITM_BM_ORDER_NO

EliorderService

DeleteOrder

ORDER_NO

FinishOrder

ORDER_NO

ChangeOrder

ORDER_NO

ReapplyCredit

ORDER_NO, ORDER_CHECK_NO

ViewOrder

ORDER_NO

ViewLineLs

ORDER_NO

RecreateOrder

OrderNo

CancelOrder

OrderNo

CloseOrder

OrderNo

CloseOrCancelOrder

OrderNo

Structure: CreateHeaderInput

ORDER_NO, ORDER_APPLY_TO_NO, ORDER_NO_ALT, ORDER_CHECK_NO, ORDER_INVOICE_NO

Structure: ChangeHeaderInput

ORDER_NO, ORDER_CHECK_NO, ORDER_APPLY_TO_NO, ORDER_NO_ALT, ORDER_INVOICE_NO

Structure: CreateLineItemInput

LINE_ITM_ORDER_NO, LINE_ITM_ORD_NO_ALT1, LINE_ITM_ORD_NO_ALT2, LINE_ITM_BM_ORDER_NO, LINE_ITM_BKT_ORD_NO

Structure: CreateHeaderResult

ORDER_NO

Structure: ViewOrderResult

ORDER_APPLY_TO_NO, ORDER_CHECK_NO, ORDER_INVOICE_NO, ORDER_NO

Structure: ViewLineLSResult

ORD_LINE_LS_ORD_NO

Structure: CreateOrderResult

OrderNo

Structure: RecreateOrderResult

OrderNo

Structure: CreateOrderInput

ORDER_NO, ORDER_APPLY_TO_NO, ORDER_NO_ALT, ORDER_CHECK_NO, ORDER_INVOICE_NO

Structure: LineItem

LINE_ITM_ORDER_NO, LINE_ITM_ORD_NO_ALT1, LINE_ITM_ORD_NO_ALT2, LINE_ITM_BM_ORDER_NO, LINE_ITM_BKT_ORD_NO

Structure: SendOrderConfirmationEmailInput

ORDER_NO

EliordiqService

Structure: OrderInquiryOrderNoInput

OrderNo

InvoiceInquiry

GetOneInvoiceDetail

invoiceNo

OrderInquiry

GetOrder

orderNo

GetOneOrderDetail

orderNo

VYC, CLS

Feedback and Knowledge Base