Hello everybody,

today I want to leave another post about internals of Acumatica, about which you may heard something like "Never try it at home". In another words, sometime you may need to do the following in Acumaitca:

1. Insert directly into database ( for this purpose I'd recommend you to use PXDatabase.Insert )
2. Alter Table
3. Alter Schema of Table
4. Create column
5. Drop column
6. Drop Table
7. .... this list is not exhaustive

and any other system level activities. By default and for SOLId SOLID SOLID reasons you can't achieve it. But sometime you may need it. 

Below goes sample of code, which you can use for setting identity insert to on, and then execute some update onf the table, and then assign some value to it:

using System.Collections;
using System.Collections.Generic;
using PX.BulkInsert.Installer;
using PX.Data;
using PX.DbServices.Commands;
using PX.DbServices.Commands.Data;
using PX.DbServices.Points;
using PX.DbServices.Points.DbmsBase;
using PX.DbServices.Points.MsSql.Commands;
using PX.DbServices.QueryObjectModel;
using PX.Objects.PO;
using PX.Objects.SO;
 
 
namespace SomeSqlDemo
{
	public class POOrderEntryExt : PXGraphExtension<POOrderEntry>
	{
		public IEnumerable ExecuteSomeSql(PXAdapter adapter)
		{
			string tableName = "SOShipLineSplit";
			int companyId = 2;
			PointDbmsBase point = PXDatabase.Provider.CreateDbServicesPoint();
			ScriptExecutor scriptExecutor = new ScriptExecutor(point);
			ExecutionContext executionContext = new ExecutionContext(new SimpleExecutionObserver());
 
			point.executeCommands(new List<CommandBase>()
				{
					new CmdIdentityInsert(true) { TableName = tableName },
					
					new CmdUpdate(tableName)
					{
						Condition = Yaql.companyIdEq(companyId, point.getCompanies(), point.Schema.GetTable(tableName).HasCompanyMask(), 
						tableName).and(
							Yaql.column<SOShipLineSplit.shipmentNbr>().eq(Yaql.constant("000021"))).and(
							Yaql.column<SOShipLineSplit.lineNbr>().eq(Yaql.constant(1))).and(
							Yaql.column<SOShipLineSplit.splitLineNbr>().eq(Yaql.constant(2))),
 
						AssignValues = { { "UsrUniqueID", Yaql.constant(777) } }
					}
				},
				executionContext);
 
			return adapter.Get();
		}
	}
}

Besides that take a look on this screenshot:

as you can see from the screenshot I didn't mention even third part of available methods, which you can try sometimes in case if you really need to go hard core. Deeply hardcore.

Summary
As I mentioned already Yaql is not something, where you should go straight away. Mainly you can use that features as some kind of latest resort, which will migrate data of your customization/plugin between different versions of Acumatica or if you want to preinit tables of your customization with some data in C# code etc. For other cases I'd rather recommend to use BQL with SQL Scripts for this purpose.