XDC Presentation Code

TLDR; I have been asked to show the code I use to create my Peer-to-peer networking from my XDC presentation. Please use or discard or improve it as you wish.


It is all in API 1.0 form as I will only convert when Web 2.0 is out, as all my work is cross-environmental. I try to make sure it works on SQLite, MySQL, PostgreSQL and MSSQL. Note that you must use MBS SQL for this to work. Sorry I don’t have all this in a running example application.

I chop the SQL commands into pieces. This is what I place at the top of my Methods where I need to access the database. Whether local or remote, it is all the same:

Dim rs As RecordSet Dim SQLCommand As String Dim WHERECommand As String Dim ORDERBYCommand As String Dim GROUPBYCommand As String Dim LIMITNumber As Integer = -1 Dim FieldNames() As String Dim FieldTypes() As String Dim FieldValues() As Variant Dim isDBError As Boolean = False Dim DBErrorMessage As String Dim DBErrorCode As Integer

Then each time I want to access the database I use this:

CommonURLConnection.doResetURLConnectionParameters(SQLCommand, WHERECommand, ORDERBYCommand, GROUPBYCommand, LIMITNumber, FieldNames, FieldTypes, FieldValues) SQLCommand = "SELECT XXXXXX FROM XXXXXX" '"INSERT INTO XXXXXX", "DELETE FROM XXXXXX", "UPDATE XXXXXX", "CREATE XXXXXX" or "ALTER XXXXXX" WHERECommand = "" ORDERBYCommand = "" GROUPBYCommand = "" LIMITNumber = -1 CommonSQL.doPreparedArraysWAD(fieldNames, fieldTypes, fieldValues, "XXXXXX", "Text", XXXXXX) ' CommonURLConnection.doURLConnectionDB(CommonURLConnection.isURLConnectionDefault, CommonURLConnection.URLConnectionDB, isDBError, DBErrorMessage, DBErrorCode, rs, SQLCommand, WHERECommand, ORDERBYCommand, GROUPBYCommand, LIMITNumber, FieldNames, FieldTypes, FieldValues) If isDBError Or rs = Nil Then CommonWindow.doDisplayErrorWAD(DBErrorMessage, CurrentMethodName, DBErrorCode) End If

  1. Reset all variables, in case they were changed above
  2. You will need to enter the SQLCommand for your database
  3. Enter, comment out or delete the WHERE, ORDER BY, GROUP BY and LIMIT commands if not needed
  4. CommonSQL.doPreparedArraysWAD() is only needed for INSERT and UPDATE commands. Enter the field names and data types for all fields, one per line.
  5. Run it locally (or remotely) using CommonURLConnection.doURLConnectionDB. Note that the CommonURLConnection.doURLConnectionDB Method must have been set up with the database settings when you first logged in.
  6. Use the isDBError, DBErrorMessage, DBErrorCode and rs to determine what you do next below

Here are the Methods it calls:
Reset all variables

Protected Sub doResetURLConnectionParameters(ByRef SQLCommand As String, ByRef WHERECommand As String, ByRef ORDERBYCommand As String, ByRef GROUPBYCommand As String, ByRef LIMITNumber As Integer, FieldNames() As String, FieldTypes() As String, FieldValues() As Variant) 'reset all the parameters to their default values, in case you need to call the routine twice in one method … End Sub

Run the SQL command locally or remotely (set on first login), you don’t care, you just want the results. If you don’t care about sharing data, it can run the SQL Command locally (down below). Alternatively, it is sent to the remote PC then that PC uses the same Method to run it locally for you. It creates a Dictionary of these SQL components, convert this Dictionary to a Variant, converts this Variant to JSON, compresses the JSON, encrypts the JSON, converts the JSON to Base64 and sends it to the remote PC using URLConnection.SendSync(). It then passes sets the ByRef … variables and Returns.

I pass the UUID to/from the Client and Master for logging purposes. I pass the current date to ensure this is not a hacker’s re-used command. I pass a Listen Password to ensure the Client is allowed to talk with this server.

Protected Sub doURLConnectionDB(isURLConnection As Boolean, db As SQLDatabaseMBS, ByRef isDBError As Boolean, ByRef DBErrorMessage As String, ByRef DBErrorCode As Integer, ByRef rs As RecordSet, SQLCommand As String, WHERECommand As String, ORDERBYCommand As String, GROUPBYCommand As String, LIMITNumber As Integer, FieldNames() As String, FieldTypes() As String, FieldValues() As Variant, DatabaseName As String = "") … End Sub

Add a field, data type and value to the array. Convert BLOBs to Base64 since JSON doesn’t like Binary data.

Protected Sub doPreparedArraysWAD(fieldNames() As String, fieldTypes() As String, fieldValues() As Variant, fieldName As String, dataType As String, fieldValue As Variant) … End Sub

Bind the data types for the SQL statement. This might be done on the remote Master PC.

Protected Sub doPreparedBindTypeWAD(ByRef mySQLPreparedStatementMBS As SQLPreparedStatementMBS, fieldNames() As String, fieldTypes() As String, fieldValues() As Variant) … End Sub

This is the code I use on the Master PC. When Aloe Express receives a Request, it passes it to this Method to look up the database and return the results.

Protected Sub doProcessRequest(Request As AloeExpress.Request) … End Sub

It isn’t really “peer to peer” it there is a server in the middle is it?

[myPC] <-> [yourPC] is peer to peer
[myPC] <-> [SERVER] <-> [yourPC]

You are right, technically it is Client-Server, not PtP.

The idea is that any one of your applications can be the Server without a different application or installing a special database.

Try it again now.

Thank you very much.
(Original post erased.)