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.
http://www.webappdevelopments.com/downloads/PeertoPeer.zip
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
- Reset all variables, in case they were changed above
- You will need to enter the SQLCommand for your database
- Enter, comment out or delete the WHERE, ORDER BY, GROUP BY and LIMIT commands if not needed
- CommonSQL.doPreparedArraysWAD() is only needed for INSERT and UPDATE commands. Enter the field names and data types for all fields, one per line.
- 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.
- 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