As I wrote about in my last blog the Symphony Harmony namespace provides the ability to execute select queries and stored procedure style method execution using SQL style syntax. The queries and procedures can be executed in-process or via the Symphony Bridge on a remote server via industry standard HTTP/HTTPS/TCP protocols as well as via a Service Bus Relay protocol. In the 3.2.6 release we have added the ability to fully manage the data in the SDBMS files via SQL this style syntax.
Accessing or selecting data through the Symphony Harmony namespace is simple. First you need to define your “connection” to the database. To do this you create an instance of the DBConnector class. For example;
data connector = new DBConnector(“SymLocal:user/password!SimpleHarmonyTestLibrary.TableMapper.MapTableToFile”)
The connection string defines how you want to access the data: SymLocal indicates that it’s in-process and specifying SymRemote indicates it’s via Symphony Bridge. If you are using Symphony Bridge you must include the “server” you wish to connect to – I’ll include an example shortly. The username and password allow you to provide authentication but that’s for another blog. The “SimpleHarmonyTestLibrary.TableMapper.MapTableToFile” is the named method that Symphony Harmony uses to translate the table name to a physical SDBMS filename, and it’s something you can code generate.
Here is an example of a remote access connection;
data connector = new DBConnector(“SymRemote:user/password@localhost:8081!SimpleHarmonyTestLibrary.TableMapper.MapTableToFile”)
Notice the server name and the port the Symphony Bridge server is listening on. Other than the connection string the usage of Symphony Harmony is identical regardless if it’s local or remote data access. Although here we are talking about local and remote data access – we are referring to where the code to perform the query is being executed, either locally in-process or via Symphony Bridge. We are not referring to the physical location of the SDBMS data files which may be local or remote and accessed via xfServer. Both local (in-process) and remote connections via Symphony Bridge can access data locally or via xfServer.
To select records from a file you use the SQL style SELECT syntax and call the DataSelect.RunDataSelect method. You need to instance the Symphony Data Object that will define the response data structure. For example;
data partItem = new Part_Data()
foreach partItem in DataSelect.RunDataSelect(connector, “SELECT * FROM part”, partItem).Result
Console.WriteLine(“Created ID = ” + partItem.Id + “, ” + partItem.Description + ” Qty = ” + %string(partItem.Quantity) + ” Cost = ” + %string(partItem.Cost_price))
Note here that the case of the command is not important however the case of string values within a where clause is. You can limit the fields returned, filter the data and order the results as you require. All of these are valid;
DataSelect.RunDataSelect(connector, “SELECT id, description, quantity FROM part WHERE quantity > 10”, partItem).Result
DataSelect.RunDataSelect(connector, “SELECT description, id FROM part WHERE description like ‘BRAKE’”, partItem).Result
DataSelect.RunDataSelect(connector, “SELECT cost_price, id, description FROM part WHERE cost_price < 1.99 ORDER BY cost_price “, partItem).Result
The order of the fields list is not important. You also don’t have to hard-wire the filter values as you can pass positional arguments. For example;
Data qtyValue ,int ,10
DataSelect.RunDataSelect(connector, “SELECT id, quantity, description FROM part WHERE quantity > :1”, partItem, qtyValue).Result
data descValue ,string ,”BRAKE”
DataSelect.RunDataSelect(connector, “SELECT id, description FROM part WHERE description like :1″, partItem, descValue).Result
Data qtyValue ,int ,10
Data costValue ,decimal ,1.99
DataSelect.RunDataSelect(connector, “SELECT id, description, cost_price FROM part WHERE cost_price < :1 AND quantity > :2 ORDER BY cost_price “, partItem, costValue, qtyValue).Result
All of the above will return a collection of data objects of type Part_Data().
As I mentioned above you can now maintain the data in your SDBMS files through Symphony Harmony. We have added the ability to insert data into an SDBMS file using Symphony Harmony. The DataInsert.RunDataInsert() method accepts an INSERT command and a data object and stores the passed-in data object data to the appropriate file. For example;
data partItem = new Part_Data()
partItem.Id = “ID1”
partItem.Description = “this is the description for part 1”
partItem.Technical_info = “lots of additional information for part 1”
partItem.Quantity = 5
partItem.Cost_price = 12.34
DataInsert.RunDataInsert(connector, “INSERT INTO part”, partItem).Wait()
The data in the partItem data object is extracted, placed into a Synergy record and stored into the SDBMS file. Any errors, like duplicate key, will be thrown on the client.
We have also added the ability to update the data in an SDBMS file using Symphony Harmony. The DataUpdate.RunDataUpdate() method accepts an UPDATE command, which should include a WHERE clause, and a data object, and updates the file with the passed data object data. You can optionally pass a list of fields that restrict the data to be updated. So for example if you application wants to simply update a field for a given record;
data numUpdated ,long
partItem.Cost_price = 999.99
numUpdated = DataUpdate.RunDataUpdate(connector, “UPDATE part SET cost_price where QUANTITY = 13”, partItem).Result
Will update ALL records that have a quantity value of 13 with the updated cost_price value of 999.99. The resulting numUpdated field will contain the number of records that were updated in the file.
The field list is optional, but it’s recommended! If you don’t specify the field list then the whole record is updated, so you must ensure that the data object contains all the required information otherwise you may get exceptions if key values that are defined none-modifiable are changed. You may also inadvertently overwrite changes made by other processes.
And to provide the fully SDBMS data management capabilities we have added the ability to delete data from an SDBMS file using Symphony Harmony. The DataDelete.RunDataDelete() method accepts a DELETE command, which should include a WHERE clause, and a data object and deletes the matching records from the file.
data numDeleted ,long
numDeleted = DataDelete.RunDataDelete(connector, “DELETE FROM part WHERE id = :1”, partItem, “ID1”).Result
Removes the required records from the file. There is no need to first select or lock them. The numDeleted field will contain the number of records that were deleted from the file. The where clause is optional so the following is valid;
numDeleted = DataDelete.RunDataDelete(connector, “DELETE FROM part”, partItem).Result
And will result in clearing the entire file – you have been warned!!
All this capability is now available in version 3.2.6 of the Symphony Framework, Symphony Harmony and Symphony Harmony Unplugged packages on Nuget.