Database Examples

Use the database methods to create, update or delete records from the database. ALWAYS reset the database before modifying the database records.

The following table definition will be used for the first 5 examples, 

Example 1. Create a STOCKTAKE record.

Database.Reset()

Database.AddColumn("ID", intID)

Database.AddColumn("BARCODE", edtBarcode)

Database.AddColumn("DESCRIPTION", edtDescription)

Database.AddColumn("LOCATION", edtLocation)

Database.AddColumn("QTY", edtQty)

intResult = Database.AddRecord("STOCKTAKE")

Note:

Example 2. Update the quantity of a STOCKTAKE record.

Database.Reset()

intResult1 = Database.SetQueryParam("pStocktakeID", intID)

Database.AddColumn("QTY", edtQty)

IF intResult1 == 0

{

intResult2 = Database.UpdateRecords("qUpdateStocktake")

...

}

Note:

Example 3. Delete a STOCKTAKE record.

Database.Reset()

intResult1 = Database.SetQueryParam("pStocktakeID", intID)

IF intResult1 == 0

{

intResult2 = Database.DeleteRecords("qUpdateStocktake")

...

}

Note:

Example 4. Delete ALL STOCKTAKE records.

Database.Reset()

intResult = Database.DeleteRecords("qStocktake")

Note:

Example 5. Delete ALL STOCKTAKE records locally.

Database.Reset()

intResult = Database.DeleteRecordsLocally("qStocktake")

Note:

Example 6. Deleting records with  ExecuteUpdate method.

// Discover the engine type being used

IF Database.GetDatabaseEngine() == DB_MS_ACCESS

{

    strDateDel = "#"

}

ELSE

{

    strDateDel = "'"

}

 

// Format date parameter

// Delete job records locally after 24 hours = 60 sec*60 mins*24 hrs

dtDeleteDate = DateTime.Now() - 86400

strDate = DateTime.Format(dtDeleteDate, "yyyy-MM-dd hh:mm:ss")

 

// Delete JOBS that are Processed, Returned and Rejected

Database.Reset()

strSql = "DELETE FROM JOB WHERE JOB_STATUS IN (‘Processed’,‘Returned’,‘Rejected’) AND DT_STATE_CHANGED <= "

strSql = strSql & strDateDel & strDate & strDateDel

intResult = Database.ExecuteUpdate(strSql)

IF intResult <> 0

{

    Form.MessageBox("Error", "Could not delete processed job records !", MB_ICONERROR)

    EXIT

}

Note:

Example 7: Exporting the database.

edtFile = Form.ShowFileOpenDialog()

intResult = Database.ExportToDatabase(edtFile, "EMPLOYEE", "qExportEmp", true)

IF intResult <> 0

{

    Form.MessageBox("Export”, "Error in export. Pls check trace file. ", MB_ICONERROR)

    EXIT

}

Note:

Example 8. Resetting the timestamp before synchronisation.

Database.ResetTimestamp("CUSTOMER")

Synchroniser.DisableAll()

Synchroniser.EnableSyncRule("SyncCust", true)

Synchroniser.EnableSyncRule("SyncProduct", true)

Form.ShowSyncDialog(false)

Note:

Example 9. Using the lock and release server records method on a direct access application.

edtID = ID

Database.AddColumn("ID", edtID)

Variable1 = Database.LockServerRecord("LOCK_TABLE")

Settings.SettingIntLockID = Database.GetServerLockId()

VarLockOwner = Database.GetServerLockOwner()

IF Variable1 <> 0

{

    Form.CanModify = false

    Form.MessageBox("Lock Records", "The record is locked. Please wait to modify.", MB_OK)

    edtName = NAME

    edtSalary = SALARY

}

ELSE

{

    edtName = Database.GetLockedRecordColumn("NAME")

    edtSalary = Database.GetLockedRecordColumn("SALARY")

    Form.CanModify = true

}

Note: