Exporter - Http Error Response

When you are exporting data to a Web URL and you are exporting multiple records using the Upload XML file containing all records or Upload Zipped XML file containing all records HttpPostMode it would be a shame for your remote code to error out midway though a batch update of your database.

Ideally upon a failed insert or update of a particular record you probably want to continue onto the next rather the page error out, you (or your developer) can code the page do this but then you are in a situation where there could have been errors but you don't know which records failed to insert/update, or there could be no error at all but you just don't know and are left wondering.

These are not ideal choices, either have the page error out on the first error meaning all records after the failed one will be skipped, or ignore any errors and continue onto the next but never knowing if or what errors occurred.

This is where the Http Error Response feature comes in, by checking a box in the job editor to let the application know your using this feature the job will always expect the remote page to return XML in a specific format.

The XML will contain any error information about any and all errors that occurred, this XML must be generated within the page and it is your or your web developers responsibility to generate the appropriate XML.

Internally, regardless of where its exporting date to, the application stores error details on any errors in a .NET dataset, the data from this dataset is saved to the file system in XML format so any error information that occurred during the last export is retained even when the application is closed and re-opened.

This is the XML that should be returned from your page, this returned XML becomes the jobs active error list, even when there are no errors you still must return the base XML for the dataset.

When there are no errors

the base XML that must be returned when no errors occurred in the page is:

<?xml version="1.0" standalone="yes"?>

When there are errors

when errors do occur in the page you should include the appropriate XML elements to return the error information to the application, an example of this XML would be:

<?xml version="1.0" standalone="yes"?>

<ErrorTitle>Insert Failed</ErrorTitle>

<ErrorMessage>no value provided for required parameter</ErrorMessage>

<ExportData>Stockcode=WID1 Description=Widget 1</ExportData>

</ErrorLogs>

<ErrorTitle>Insert Failed</ErrorTitle>

<ErrorMessage>no value provided for required parameter</ErrorMessage>

<ExportData>Stockcode=WID2 Description=Widget 2</ExportData>

</ErrorLogs>

</ExportErrors>

the supported fields that can be returned are:

Field Name	Description
ErrorID	This is a numeric value used to identify unique errors and must be unique, each error must bear a unique ErrorID
ErrorTitle	A short title for the error
ErrorMessage	The actual error message
ExportData	some date that the user of the application can used to identify which record failed to insert/update
StackTrace	extended error information, ASP.NET exceptions contain important error information in their stacktrace properties that you can place in this field
IsHtml	if your error message contains html set this field to true and the application will render the html for the end user when the error is viewed within the applications error viewer screen.

ASP.NET

If your page is an ASP.NET page generating the XML is very easy to do, simply create an instance of the DataSet class, add the table & columns then use the DataSet's GetXML method to return the XML.

VB.NET Example, code needed for XML error response is highlighted in yellow.

<%@ Page Language="VB" %>

<%@ Import Namespace="System.IO" %>

<%@ Import Namespace="System.Data" %>

<%@ Import Namespace="System.Data.OleDb" %>

Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs)

Dim ExportErrors As New DataSet("ExportErrors")

Dim TblErrors As DataTable = ExportErrors.Tables.Add("ErrorLogs")

Dim Col As DataColumn

Col = TblErrors.Columns.Add("ErrorID", GetType(Int32))

Col.AutoIncrement = True

Col.AutoIncrementSeed = 1

Col.AutoIncrementStep = 1

Col = TblErrors.Columns.Add("IsHtml", GetType(Boolean))

Col.DefaultValue = False

TblErrors.Columns.Add("ErrorTitle", GetType(String))

TblErrors.Columns.Add("ErrorMessage", GetType(String))

TblErrors.Columns.Add("ExportData", GetType(String))

TblErrors.Columns.Add("StackTrace", GetType(String))

If Request.Files.Count > 0 Then

Dim Ds As New DataSet

Ds.ReadXml(Request.Files(0).InputStream)

Dim ConStrBuilder As New OleDbConnectionStringBuilder()

ConStrBuilder.Provider = "Microsoft.Jet.OLEDB.4.0"

ConStrBuilder.DataSource = Path.Combine(Request.PhysicalApplicationPath, "DemoData.mdb")

ConStrBuilder.PersistSecurityInfo = False

Dim Conn As New OleDbConnection(ConStrBuilder.ConnectionString)

Conn.Open()

For Each Row As DataRow In Ds.Tables("STOCK").Rows

Dim IsInsert As Boolean

Dim Cmd As New OleDbCommand("SELECT COUNT(ProductCode) FROM Products WHERE ProductCode=?", Conn)

Cmd.Parameters.Add(New OleDbParameter("@ProductCode", Row("STOCK_CODE"))).DbType = DbType.String

Dim RecordExists As Boolean = Convert.ToInt32(Cmd.ExecuteScalar()) > 0

If RecordExists Then

IsInsert = False

Cmd.CommandText = "UPDATE Products SET Description=?, SalesPrice=?, Category=? WHERE ProductCode=?"

Else

IsInsert = True

Cmd.CommandText = "INSERT INTO Products (Description, SalesPrice, Category, ProductCode) VALUES(?, ?, ?, ?)"

End If

Cmd.Parameters.Clear()

Try

Cmd.Parameters.Add(New OleDbParameter("@Description", Row("DESCRIPTION"))).DbType = DbType.String

Cmd.Parameters.Add(New OleDbParameter("@SalesPrice", Row("SALES_PRICE"))).DbType = DbType.Double

Cmd.Parameters.Add(New OleDbParameter("@Category", Row("STOCK_CAT"))).DbType = DbType.String

Cmd.Parameters.Add(New OleDbParameter("@ProductCode", Row("STOCK_CODE"))).DbType = DbType.String

Cmd.ExecuteNonQuery()

Catch ex As Exception

Dim NewErrorRow As DataRow = TblErrors.NewRow

NewErrorRow("ErrorTitle") = IIf(IsInsert, "Insert Record Failed", "Update Record Failed")

NewErrorRow("ErrorMessage") = ex.Message

NewErrorRow("StackTrace") = ex.StackTrace

NewErrorRow("ExportData") = ""

For Each Column As DataColumn In Row.Table.Columns

NewErrorRow("ExportData") &= Column.ColumnName & "=" & Row(Column.ColumnName) & vbCrLf

TblErrors.Rows.Add(NewErrorRow)

End Try

Conn.Close()

End If

Response.Clear()

Response.Write("<?xml version=""1.0"" standalone=""yes""?>" & vbCrLf & ExportErrors.GetXml())

Response.End()

End Sub

</script>

C#.NET Example, code needed for XML error response is highlighted in yellow.

<%@ Page Language="C#" %>

<%@ Import Namespace="System.IO" %>

<%@ Import Namespace="System.Data" %>

<%@ Import Namespace="System.Data.OleDb" %>

protected void Page_Load(object sender, System.EventArgs e)

{

DataSet ExportErrors = new DataSet("ExportErrors");

DataTable TblErrors = ExportErrors.Tables.Add("ErrorLogs");

DataColumn Col = default(DataColumn);

Col = TblErrors.Columns.Add("ErrorID", typeof(Int32));

Col.AutoIncrement = true;

Col.AutoIncrementSeed = 1;

Col.AutoIncrementStep = 1;

Col = TblErrors.Columns.Add("IsHtml", typeof(bool));

Col.DefaultValue = false;

TblErrors.Columns.Add("ErrorTitle", typeof(string));

TblErrors.Columns.Add("ErrorMessage", typeof(string));

TblErrors.Columns.Add("ExportData", typeof(string));

TblErrors.Columns.Add("StackTrace", typeof(string));

if (Request.Files.Count > 0)

{

DataSet Ds = new DataSet();

Ds.ReadXml(Request.Files[0].InputStream);

OleDbConnectionStringBuilder ConStrBuilder = new OleDbConnectionStringBuilder();

ConStrBuilder.Provider = "Microsoft.Jet.OLEDB.4.0";

ConStrBuilder.DataSource = Path.Combine(Request.PhysicalApplicationPath, "DemoData.mdb");

ConStrBuilder.PersistSecurityInfo = false;

OleDbConnection Conn = new OleDbConnection(ConStrBuilder.ConnectionString);

Conn.Open();

foreach (DataRow Row in Ds.Tables["STOCK"].Rows)

{

bool IsInsert = false;

OleDbCommand Cmd = new OleDbCommand("SELECT COUNT(ProductCode) FROM Products WHERE ProductCode=?", Conn);

Cmd.Parameters.Add(new OleDbParameter("@ProductCode", Row["STOCK_CODE"])).DbType = DbType.String;

bool RecordExists = Convert.ToInt32(Cmd.ExecuteScalar()) > 0;

if (RecordExists)

{

IsInsert = false;

Cmd.CommandText = "UPDATE Products SET Description=?, SalesPrice=?, Category=? WHERE ProductCode=?";

}

else

{

IsInsert = true;

Cmd.CommandText = "INSERT INTO Products (Description, SalesPrice, Category, ProductCode) VALUES(?, ?, ?, ?)";

}

Cmd.Parameters.Clear();

try

{

Cmd.Parameters.Add(new OleDbParameter("@Description", Row["DESCRIPTION"])).DbType = DbType.String;

Cmd.Parameters.Add(new OleDbParameter("@SalesPrice", Row["SALES_PRICE"])).DbType = DbType.Double;

Cmd.Parameters.Add(new OleDbParameter("@Category", Row["STOCK_CAT"])).DbType = DbType.String;

Cmd.Parameters.Add(new OleDbParameter("@ProductCode", Row["STOCK_CODE"])).DbType = DbType.String;

Cmd.ExecuteNonQuery();

}

catch (Exception ex)

{

DataRow NewErrorRow = TblErrors.NewRow();

NewErrorRow["ErrorTitle"] = (IsInsert ? "Insert Record Failed" : "Update Record Failed");

NewErrorRow["ErrorMessage"] = ex.Message;

NewErrorRow["StackTrace"] = ex.StackTrace;

NewErrorRow["ExportData"] = "";

foreach (DataColumn Column in Row.Table.Columns)

{

NewErrorRow["ExportData"] += Column.ColumnName + "=" + Row[Column.ColumnName] + "\n";

}

TblErrors.Rows.Add(NewErrorRow);

}

Conn.Close();

}

Response.Clear();

Response.Write("<?xml version=\"1.0\" standalone=\"yes\"?>" + "\n" + ExportErrors.GetXml());

Response.End();

}

</script>