Data manipulation
You can write code to manipulate data that is stored in the database.
- Use a
select
method to declare the data to modify. - An
insert
method is used to add one or more records to a table. - An
update
method is used to modify the current record in the appropriate system fields. - Use a
where
clause to specify a condition that theupdate
method tests as it processes each row of the table. - A
delete
method is used to remove records from a table. Before using anupdate
ordelete
method, you must use aselect
method to define the data to update or delete. - To select multiple records or fields, you initially use a
select
method and then use anext
method to fetch the next record in a table. - To loop over many records that meet specific criteria, you can use a
while select
method.
The select
statement fetches or manipulates data from the database using X++. You can use the select
statement to fetch one record or field. You can use other statements such as next
to fetch more records, or use a while select
to traverse multiple records.
For example, the following code sample fetches all columns in the first row of the CustTable, then it prints the value in the AccountNum column of that row:
CustTable custTable;
select firstonly custTable; //this is a short notation for 'select firstonly * from custTable;'
info("AccountNum: " + custTable.AccountNum);
The following are code samples for insert
, update
, and delete
methods. The samples use a fictitious MyCustomerTable table containing fictitious fields for AccountNumber and CustomerName.
insert
method//This will insert a new record into a sample MyCustomerTable table. The only mandatory field that is set on this table is AccountNumber. private void AddCustomer() { MyCustomerTable myCustomerTable; ttsBegin; myCustomerTable.AccountNumber = "1234"; //The new record will have the account number 1234. myCustomerTable.insert(); ttsCommit; }
update
method//This will update the record where the AccountNumber field is set to 1234 by adding more information in the CustomerName field. private void UpdateCustomer() { MyCustomerTable myCustomerTable ttsBegin; select forUpdate myCustomerTable where myCustomerTable.AccountNumber == "1234"; //Now we will update the CustomerName field to be Sally myCustomerTable.CustomerName = "Sally"; myCustomerTable.update(); ttsCommit; }
delete
method//This will delete the record from the MyCustomerTable table where the AccountNumber field is set to 1234. private void DeleteCustomer() { ttsBegin; while select forUpdate myCustomerTable where myCustomerTable.AccountNumber == "1234" { myCustomerTable.delete(); } ttsCommit; }