Module: Database Interface Spider <-> Server / DB

Share your advanced knowledge/code with the community.
User avatar
Kurzer
Posts: 90
Joined: Mon May 26, 2014 9:33 am

Module: Database Interface Spider <-> Server / DB

Post by Kurzer »

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.

Image

Image
(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:
Classes/classCustomers.php
Classes/classEncode.php
Classes/classObjectModel.php
Classes/classProducts.php
Controllers/controllerCustomers
Controllers/controllerProducts
Config/config.php
test.html
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.

Image

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';
And in the customers controller:

Code: Select all

const COMMAND_GETCUSTOMER =         'GetCustomer';
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:

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");
(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:

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 }});
   CompilerEndIf
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:

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="")
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

Code: Select all

	Structure structResultRow
		Map mpResultrow.s()
	EndStructure
	
	Structure structResultset
		Command.s
		Status.s
		Message.s
		Count.i
		List lsResultset.structResultRow()
	EndStructure
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.

Code: Select all

FreeResultSet(*Resultset)
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:

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
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:

Code: Select all

// Global Interface constants
define('_STATUS_OK_', 'OK');
define('_STATUS_ERR_', 'ERR');
- 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
Last edited by Kurzer on Mon Oct 19, 2020 8:00 pm, edited 5 times in total.
SB 2.32 x86, Browser: Iron Portable V. 88.0.4500.0 (Chromium based), User age in 2023: 55y
"Happiness is a pet." | "Never run a changing system!"
User avatar
Kurzer
Posts: 90
Joined: Mon May 26, 2014 9:33 am

Re: Module: Database Interface Spider <-> Server / DB

Post by Kurzer »

Updated to Version 1.02.

The modul is now ready for parallel communication with a server. Each server query get's it's own result set.
The description in the first post was updated.
SB 2.32 x86, Browser: Iron Portable V. 88.0.4500.0 (Chromium based), User age in 2023: 55y
"Happiness is a pet." | "Never run a changing system!"
User avatar
Kurzer
Posts: 90
Joined: Mon May 26, 2014 9:33 am

Re: Module: Database Interface Spider <-> Server / DB

Post by Kurzer »

Updated to Version 1.10.

The module has been simplified and optimized.
It is no longer necessary to deal with user-defined events.
When sending a server command the user specifies a callback procedure. This procedure is called automatically by the module after the corresponding server data has been received.
SB 2.32 x86, Browser: Iron Portable V. 88.0.4500.0 (Chromium based), User age in 2023: 55y
"Happiness is a pet." | "Never run a changing system!"
Fred
Site Admin
Posts: 1506
Joined: Mon Feb 24, 2014 10:51 am

Re: Module: Database Interface Spider <-> Server / DB

Post by Fred »

Very nice tool !
User avatar
Kurzer
Posts: 90
Joined: Mon May 26, 2014 9:33 am

Re: Module: Database Interface Spider <-> Server / DB

Post by Kurzer »

Updated to Version 1.11.

The module has been optimized. The encoding of data sent to the server is now done using the JSON functions (I have no idea why I manually escaped the data before :shock:). The procedures PrepareStringParameter() and IsNumber() have been dropped.
SB 2.32 x86, Browser: Iron Portable V. 88.0.4500.0 (Chromium based), User age in 2023: 55y
"Happiness is a pet." | "Never run a changing system!"
Post Reply