Module: Database Interface Spider <-> Server / DB
Posted: Wed Aug 26, 2020 2:33 pm
DBI database interface module for SpiderBasic - Version 1.11 - 19/Oct/2020.
For a current project I needed a simple communication interface to allow my Spiderbasic application to communicate with a web server and a database running on it.
The resulting SpiderBasic module DBI (database interface) I would like to share with you. The module works with a fixed JSON structure to send commands to the webserver and to receive data. The JSON data is transferred via HTTPRequest in POST mode.
You can send as many self defined commands to the server as you like and receive data records with different structures. The data returned by the web server is available line by line in a list and per line in a map with all the fields.
(Please note: The images reflect version 1.00, but there are only marginal optical differences to version 1.10.)
For a better understanding of the workflow and the setup on the web server, I have created a small demo project, which is completely executable. It also contains several PHP files that have to be copied to the web server. Database access and user authentication is not included in the demo project. The database access is simulated by accessing a PHP array.
In this demo project there are three commands "GetCustomer", "GetProduct" and "GetProductDetail". All of these commands have at least one parameter that determines the ID of the product or customer to be delivered from the database.
To try out the demo project directly, a local or a remote web server must be available to which you have access. I use the local web server XAMPP.
The following files and directories must be copied from the ZIP archive to the 'htdocs' folder of the web server:
To use the DBI interface from within SpiderBasic, only the module "mod_DBI.sbi" is required. It contains a demo code in the CompilerIf section "CompilerIf #PB_Compiler_IsMainFile", which works directly with the PHP files of this demo project.
In general the following things have to be considered regarding the configuration and usage:
1)
In the controller files the commands are stored as string constants that SpiderBasic can call.
For example, in the products controller:
And in the customers controller:
These are the strings that SpiderBasic must send. You can change and extend them, but SpiderBasic must then send the matching command strings.
You also need to know what parameters are expected when you call it. For this demo project you can see which parameters are used by which command by looking at the file "test.html". In an own project you define the parameters yourself and therefore you know them.
2)
The following line can be found in the PHP file "config.php" in the config directory:
(Please check that the web server port in the SpiderBasic prefs matches the web server port in the config file (here it is 9080). SpiderBasic / Preferences / Compiler / Default web server port)
And in the demo code of the SpiderBasic DBI module the following line is included:
These lines are necessary so that you can start the module simply by pressing F5 from the IDE. If you start the module with F5, the code compiled by SpiderBasic is not located in the webserver directory and a well configured webserver will reject the requests because of the CORS restrictions (many thanks to the user Kiffi/Peter for his active support with some detailed questions about this project 
).
3)
Use of the DBI module:
Since HTTP requests run asynchronously in the background, we or the user need a callback procedure to process the data sent by the server.
a) The module does not require any special init routine. The server requests can be sent directly.
A server request is initiated with the following command:
sURL: Contains the URL of the server including the filename of the PHP file that receives and processes the JSON encoded command.
*CallBackProcedure: A pointer to a callback procedure that process the result set sent by the server. This callback procedure is called by the module after the HTTP callback has received the requested data.
sCommand: The command that the server should execute. The PHP file on the server must know this command and know what to do.
sParam1 - sParam10: Optional parameters that belong to the respective command. The PHP file on the server must know these parameters and know what to do.
b) The callback procedure that receives the data from the server must have a single structured parameter. The parameter must be of type 'structResultset', which is provided by the DBI module.
structResultset
Since the module can process several server requests in parallel, a separate result set is provided for each server request. Therefore the parameter of the callback procedure contains a pointer to the corresponding result set.
When the data from the result set has been read and processed, the result set should be released. Otherwise it would be kept in memory until the program ends.
You can also release all the result sets previously stored in memory at once by specifying #PB_All as a parameter to FreeResultSet().
An example of an event procedure could look like this:
Description of the fields of the result variable:
- The Command field contains the command belonging to the result set, which was sent to the server, e.g. "GetCustomers".
- The field Status contains a status defined in the PHP config (e.g. ERR, if an error occurred on the server side).
config.php:
- The Message field contains a message returned by the PHP controller, e.g. an error message.
- The Count field contains the number of data rows that were returned.
- The list \lsResultset() contains all data rows (each data row is a separate map). The data rows can be browsed with ForEach.
- The map \lsResultSet()\mpResultrow() contains the data of a single data row. The structure of the map is determined by the respective model or object model on the server side.
Download link for the source code and the demo project including all PHP files etc.
I hope that the module is useful. It is my first project with SpiderBasic.
Markus
For a current project I needed a simple communication interface to allow my Spiderbasic application to communicate with a web server and a database running on it.
The resulting SpiderBasic module DBI (database interface) I would like to share with you. The module works with a fixed JSON structure to send commands to the webserver and to receive data. The JSON data is transferred via HTTPRequest in POST mode.
You can send as many self defined commands to the server as you like and receive data records with different structures. The data returned by the web server is available line by line in a list and per line in a map with all the fields.
(Please note: The images reflect version 1.00, but there are only marginal optical differences to version 1.10.)
For a better understanding of the workflow and the setup on the web server, I have created a small demo project, which is completely executable. It also contains several PHP files that have to be copied to the web server. Database access and user authentication is not included in the demo project. The database access is simulated by accessing a PHP array.
In this demo project there are three commands "GetCustomer", "GetProduct" and "GetProductDetail". All of these commands have at least one parameter that determines the ID of the product or customer to be delivered from the database.
To try out the demo project directly, a local or a remote web server must be available to which you have access. I use the local web server XAMPP.
The following files and directories must be copied from the ZIP archive to the 'htdocs' folder of the web server:
The file "test.html" can be used to check if everything works properly without the need of Spiderbasic. You simply call the file in your browser and send commands to the controllers.Classes/classCustomers.php
Classes/classEncode.php
Classes/classObjectModel.php
Classes/classProducts.php
Controllers/controllerCustomers
Controllers/controllerProducts
Config/config.php
test.html
To use the DBI interface from within SpiderBasic, only the module "mod_DBI.sbi" is required. It contains a demo code in the CompilerIf section "CompilerIf #PB_Compiler_IsMainFile", which works directly with the PHP files of this demo project.
In general the following things have to be considered regarding the configuration and usage:
1)
In the controller files the commands are stored as string constants that SpiderBasic can call.
For example, in the products controller:
Code: Select all
const COMMAND_GETPRODUCT = 'GetProduct';
const COMMAND_GETPRODUCTDETAIL = 'GetProductDetail';Code: Select all
const COMMAND_GETCUSTOMER = 'GetCustomer';You also need to know what parameters are expected when you call it. For this demo project you can see which parameters are used by which command by looking at the file "test.html". In an own project you define the parameters yourself and therefore you know them.
2)
The following line can be found in the PHP file "config.php" in the config directory:
Code: Select all
// REMOVE THIS ON A PRODUCTION SYSTEM!
header("Access-Control-Allow-Origin: http://127.0.0.1:9080");
header("Access-Control-Allow-Credentials: true");And in the demo code of the SpiderBasic DBI module the following line is included:
Code: Select all
CompilerIf #PB_Compiler_Debugger = #True
; To bypass the restrictions of CORS (Cross-Origin Resource Sharing) on a local test system
; For security reasons, this line must be removed on a production system!
; Attention: In the PHP demo package for this module there is a similar entry in the
; configuration file (config.php), which must also be removed on a production system!
! $.ajaxSetup({xhrFields: {withCredentials: true }});
CompilerEndIf3)
Use of the DBI module:
Since HTTP requests run asynchronously in the background, we or the user need a callback procedure to process the data sent by the server.
a) The module does not require any special init routine. The server requests can be sent directly.
A server request is initiated with the following command:
Code: Select all
SendCommand(sURL.s, *CallBackProcedure, sCommand.s, sParam1.s="", sParam2.s="", sParam3.s="", sParam4.s="", sParam5.s="", sParam6.s="", sParam7.s="", sParam8.s="", sParam9.s="", sParam10.s="")*CallBackProcedure: A pointer to a callback procedure that process the result set sent by the server. This callback procedure is called by the module after the HTTP callback has received the requested data.
sCommand: The command that the server should execute. The PHP file on the server must know this command and know what to do.
sParam1 - sParam10: Optional parameters that belong to the respective command. The PHP file on the server must know these parameters and know what to do.
b) The callback procedure that receives the data from the server must have a single structured parameter. The parameter must be of type 'structResultset', which is provided by the DBI module.
structResultset
Code: Select all
Structure structResultRow
Map mpResultrow.s()
EndStructure
Structure structResultset
Command.s
Status.s
Message.s
Count.i
List lsResultset.structResultRow()
EndStructureWhen the data from the result set has been read and processed, the result set should be released. Otherwise it would be kept in memory until the program ends.
Code: Select all
FreeResultSet(*Resultset)An example of an event procedure could look like this:
Code: Select all
Procedure ProcessData(*Resultset.DBI::structResultset)
If *Resultset <> 0
Debug "Command: " + *Resultset\Command
Debug "Status: " + *Resultset\Status
Debug "Message: " + *Resultset\Message
Debug "Entries: " + *Resultset\Count
ForEach *Resultset\lsResultset()
ForEach *Resultset\lsResultset()\mpResultrow()
Debug MapKey(*Resultset\lsResultset()\mpResultrow()) + ": " + *Resultset\lsResultset()\mpResultrow()
Next
Next
DBI::FreeResultSet(*Resultset)
EndIf
EndProcedure
- The Command field contains the command belonging to the result set, which was sent to the server, e.g. "GetCustomers".
- The field Status contains a status defined in the PHP config (e.g. ERR, if an error occurred on the server side).
config.php:
Code: Select all
// Global Interface constants
define('_STATUS_OK_', 'OK');
define('_STATUS_ERR_', 'ERR');- The Count field contains the number of data rows that were returned.
- The list \lsResultset() contains all data rows (each data row is a separate map). The data rows can be browsed with ForEach.
- The map \lsResultSet()\mpResultrow() contains the data of a single data row. The structure of the map is determined by the respective model or object model on the server side.
Download link for the source code and the demo project including all PHP files etc.
I hope that the module is useful. It is my first project with SpiderBasic.
Markus
). The procedures PrepareStringParameter() and IsNumber() have been dropped.