Description

The main purpose of queries based on the IISERPQuery interface is to retrieve data from the database. However, it is possible to use the interface for adding, deleting or changing data in custom tables. A user must have at least read rights for the program code 100009980 (Data via Integration Server) to run a query. Creating a query object and filling in the properties do not require any rights, as opposed to retrieving or changing data. So users can only retrieve or change data from Isah if they have sufficient rights.

Retrieving data

To run a query carry out the following steps:

• Create a query object.
• Enter the SQL statement that must be executed. For example, SELECT * FROM T_LANGUAGE that retrieves all fields and records from the language table.
• Run the Open method that retrieves all data from the database and assigns the data to the query object.
• Read the data set with a method such as First or Next. You can also read the field information with the field name or the field position. This will produce results faster in case of large data sets.

The following code reads the language table and provides an overview of all languages present:

Dim oQuery As IERPLink.IISERPQuery

Dim i As Integer

Dim lMsg As String

Set oQuery = oERPLink.CreateQuery()

oQuery.SQL = "SELECT * FROM T_LANGUAGE"

oQuery.Open()

oQuery.First()

lMsg = "Number of languages: " & oQuery.RecordCount & vbCrLf

While not oQuery.Eof

lMsg = lMsg & (i + 1) & ": " & _

oQuery.FieldByName("LangCode").AsString & _

", " & oQuery.FieldByName("Description").AsString & vbCrLf

oQuery.Next()

End While

MsgBox lMsg

oQuery.Close()

Set oQuery = Nothing

Adding, Deleting or Changing records

Usually stored procedures are used for adding, deleting or changing data in Isah tables and even in custom tables because of the data integrity. A couple of examples of modifying a custom table with a query are provided here for completeness.

Adding Records

To add a record to a custom table with a query carry out the following steps:

• Fill in the result set of the query with the Open method.
• Assign the SQL statement.
• Run the Insert and the Post method. The record will be added to the result set.
• Run the ApplyUpdates method to apply the changes to the database.

The following code adds a record to a custom table:

Dim oQuery As IERPLink.IISERPQuery

Set oQuery = oERPLink.CreateQuery()

oQuery.SQL = "SELECT * FROM T_<Table name>"

oQuery.Open()

oQuery.InsertSQL = "INSERT into T_<Table name>(Code, Description)

VALUES('CODE1', 'Description')"

oQuery.Insert()

oQuery.Post()

oQuery.ApplyUpdates()

Changing Records

Changing a record is very similar to adding a record except for the ModifySQL that you fill with an update statement. Enter the new field values in the FieldValues property. The character ':' refers to a field in the active record of which the value will be used whereas ':OLD_' refers to the original field value.

Dim oQuery As IERPLink.IISERPQuery

Dim oField As IISERPField

Set oQuery = oERPLink.CreateQuery()

oQuery.SQL = "SELECT * FROM T_<Table name>"

oQuery.Open()

oQuery.Locate "Code", "CODE1", False, False

Set oField = oQuery.FieldByName("Description")

oQuery.ModifySQL = "UPDATE T_<Table name> SET Description = :Description

WHERE Code = :OLD_Code"

oQuery.Edit()

oQuery.FieldValues(oField.Index) = "New description"

oQuery.Post()

oQuery.ApplyUpdates()

Deleting Records

In order to delete a record you select the record in the result set. In that case, :OLD_Code will contain the proper value while running the query.

Dim oQuery As IERPLink.IISERPQuery

Set oQuery = oERPLink.CreateQuery()

oQuery.SQL = "SELECT * FROM T_<Table name>"

oQuery.Open()

if oQuery.Locate("Code", "CODE1", False, False)

oQuery.DeleteSQL = "DELETE FROM T_<Table name> WHERE Code = :OLD_Code"

oQuery.Delete()

oQuery.ApplyUpdates()

End If