Document.ExecuteQuery Method

This method executes a SQL Select or Update command in the context of the output record for the document just scanned. Unlike ADO, this method supports un-trusted environments such as a web page from the internet security zone running in the browser’s default security environment.

Scenarios where this method is useful

• Access one field as a string
• Access one or more fields of current document in XML or JSON format
• Update one or more fields of current document
• Apply an edit profile and return the list of data errors in XML or JSON format

Method Signature

String ExecuteQuery (string Command)

Input Parameters

Command: This is a SQL Select or Update command.

To return a single column, use a normal select statement that returns that column.

"select [Gender] from [Simple View of " & doc.DocumentName & "]"

To return the data as XML, use a SQL Select statement with the FOR XML clause.

doc.SQLSimpleDocument & " with NCSH for xml auto, root('MyRoot'),xmlschema('MyUri'),elements xsinil"

When multiple rows are possible as with edit failures, the “PATH” query must be formulated correctly with a named row element and a named root element so that the resulting XML is well-formed.

For example:

sea.Document.SQLEditFailures + " for xml PATH('EditFailure'), ROOT('EditFailures')"

This query produces the following XML output.

<EditFailures>

<!--select * from [Edit Failures] where [Serial Number] = 1 for xml path('EditFailure'), root('EditFailures')-->

<EditFailure>

<EditFailureID>000001000001</EditFailureID>

<Serial_x0020_Number>1</Serial_x0020_Number>

<Document_x0020_Name>GPAS #221666 Dual Sided</Document_x0020_Name>

<Field_x0020_Name>Name</Field_x0020_Name>

<Sheet_x0020_Number>1</Sheet_x0020_Number>

<Column_x0020_Ordinal>1</Column_x0020_Ordinal>

<Edit_x0020_Ordinal>1</Edit_x0020_Ordinal>

<Omits>-1</Omits>

<Multiples>-1</Multiples>

<Message>Area should not contain multiples. Area should not be blank. </Message>

<Value>*LL RIGHT </Value>

<FailureMode>1</FailureMode>

</EditFailure>

<EditFailure>

<EditFailureID>000001000002</EditFailureID>

<Serial_x0020_Number>1</Serial_x0020_Number>

<Document_x0020_Name>GPAS #221666 Dual Sided</Document_x0020_Name>

<Field_x0020_Name>Gender</Field_x0020_Name>

<Sheet_x0020_Number>1</Sheet_x0020_Number>

<Column_x0020_Ordinal>2</Column_x0020_Ordinal>

<Edit_x0020_Ordinal>2</Edit_x0020_Ordinal>

<Omits>-1</Omits>

<Multiples>-1</Multiples>

Area should not be blank. </Message>

<Value>*LL RIGHT </Value>

<FailureMode>1</FailureMode>

</EditFailure>

<EditFailure>

<EditFailureID>000001000002</EditFailureID>

<Serial_x0020_Number>1</Serial_x0020_Number>

<Document_x0020_Name>GPAS #221666 Dual Sided</Document_x0020_Name>

<Field_x0020_Name>Gender</Field_x0020_Name>

<Sheet_x0020_Number>1</Sheet_x0020_Number>

<Column_x0020_Ordinal>2</Column_x0020_Ordinal>

<Edit_x0020_Ordinal>2</Edit_x0020_Ordinal>

<Omits>-1</Omits>

<Multiples>-1</Multiples>

<Message>Area should not contain multiples. Area should not be blank. </Message>

<Value>*</Value>

<FailureMode>1</FailureMode>

</EditFailure>

</EditFailures>

Return Value

For Select commands, it is the query result. This return value is an empty string for an Update command. Resolved data is returned as a string. This method returns an empty string if the query returns no rows. This can happen if querying for edit failures and there are none. This return value is an empty string for an Update command.

Exceptions

The method returns an error if the query syntax is incorrect or if an execution error occurs.

NOTE: You cannot use INSERT or DELETE SQL statements at scan time because there is only one output record. Specifying an empty string is also an error.

Code Samples

Following example shows how to obtain and consume XML formatted data record within the .Net environment. Code assumes that the DocComplete event handler is active

ScanEventArgs sea

Dim MyCommand= sea.Document.SQLSimpleDocument + " wth NCSH for xml auto, root('MyRoot'),xmlschema('MyUri'),elements xsinil"

Dim xmlData As String = sea.Document.ExecuteCommand(MyCommand)

‘Consume the xml

Dim strReader As StringReader = New StringReader(xmlData)

Dim xmlReader As XmlReader = XmlReader.Create(strReader)

while (xmlReader.Read())

{

Dim NodeAsString as string = xmlReader.ReadString();

}

See Also

Document.ConnectionString

Zero-PC Footprint Scanning