Jump to content
Sign in to follow this  
lynkfs@gmail.com

Database connectivity - 2

Recommended Posts

To see if this concept actually works, I made up a quick demo-code of a combined TDatabase, TQuery, TDataSource component

 

This (temporary) TDataSource is tasked with 

- connecting to a database

- submitting a query

- accepting the query results

- creating enough TFields on the fly to store these results

- binding to relevant visual components

 

results here

 

Project here (proof of concept code only, nothing too fancy)

 

Since this works, next steps would be to expand and streamline

 

I understand the SMS team is about to publish a database connectivity framework, so I might wait a bit for that

Share this post


Link to post
Share on other sites

Thanks for these examples, as you said this may be even easier than in Delphi. In Delphi, TDataSet, TFields are quite complex so for SMS I believe only necessary functionality should be added. With watcher I guess you don't need data aware controls (not sure about DBgrid), effects would be like live binding in Delphi.

 

With TTable/TQuery issue might be giving too much details about database structure, so I'm more of a fan of having queries in server and just passing data to client.

Share this post


Link to post
Share on other sites

It would very interesting to have a starting point for building full offline aware master-slave replication. The application has almost full functionality when is offline, whereas each slave can add or modify records even in offline, each client (the mobile app) must decide when to sync with the server.

 

...before, before this happen, it would be nice to have a simple starting point. It could be the UI coupled with the data using a offline global content. A little demo like the EWB DataBound example

 

.............................................................................................................................................................................................................

 

In the Elevate Web Builder, you can easily bind a control to a specific dataset, assign an existing TDataSet instance to the DataSet property of the control.  
 
Just drag n'drop a TDataSet component into the designer:

 
There's a special property called "Columns" (define columns for this dataset here), just a double click at this property at the "Object Inspector" and you have a visual "Column Editor" in the Object Inspector. EWB will generate the following metadata:

{
"ClassName" : "TDataSet",
"Name" : "Customers",
"Left" : 328,
"Top" : 144,
"Properties" : {
"Columns" : {
	"Count" : 16,
	"Items" : [{
			"Name" : "CustomerID",
			"DataType" : 1,
			"Length" : 10
		}, {
			"Name" : "CompanyName",
			"DataType" : 1,
			"Length" : 50
		}, {
			"Name" : "Address1",
			"DataType" : 1,
			"Length" : 50
		}, {
			"Name" : "Address2",
			"DataType" : 1,
			"Length" : 50
		}, {
			"Name" : "City",
			"DataType" : 1,
			"Length" : 50
		}, {
			"Name" : "StateProvince",
			"DataType" : 1,
			"Length" : 25
		}, {
			"Name" : "ZipPostalCode",
			"DataType" : 1,
			"Length" : 15
		}, {
			"Name" : "Country",
			"DataType" : 1,
			"Length" : 25
		}, {
			"Name" : "Terms",
			"DataType" : 1,
			"Length" : 20
		}, {
			"Name" : "Notes",
			"DataType" : 1
		}, {
			"Name" : "Contact",
			"DataType" : 1,
			"Length" : 50
		}, {
			"Name" : "ContactEmail",
			"DataType" : 1,
			"Length" : 60
		}, {
			"Name" : "ContactPhone",
			"DataType" : 1,
			"Length" : 20
		}, {
			"Name" : "ContactFax",
			"DataType" : 1,
			"Length" : 20
		}, {
			"Name" : "ContactPhoto",
			"DataType" : 8
		}, {
			"Name" : "ContactPhoto_ContentType",
			"DataType" : 1,
			"Length" : 40
		}
	]
},
"DataSetName" : "Customer",
"AfterSave" : "CustomersAfterSave",
"AfterDelete" : "CustomersAfterSave",
"OnLoadError" : "CustomersLoadError",
"AfterLoad" : "CustomersAfterLoad"
}
}
 

For most EWB controls, you have data binding done by assigning a column name to the DataColumn property, y must also specify which column in the dataset to bind to. For instance, you drag'n drop an EWB (TEdit component), you have to assign DataColumn to "Contact" and specify the dataset "Customers".


{
"ClassName" : "TEdit",
"Name" : "ContactNameEdit",
"Properties" : {
	"Top" : 0,
	"Left" : 72,
	"Height" : 34,
	"Width" : 156,
	"Animations" : {},
	"Constraints" : {},
	"DataColumn" : "Contact",
	"DataSet" : "Customers",
	"TabOrder" : 0,
	"Text" : ""
}
}

 
On the main form "onShow event", you have: 

procedure TMainForm.MainFormShow(Sender: TObject);
begin
  Database.DatabaseName:= 'ExampleData';
  Database.LoadRows(Customers);
end;

 
So, when you start/load the EWB app, it will load data from the database called "ExampleData"
http://www.elevatesoft.com:8081/databound/databases?method=rows&database=ExampleData&dataset=Customer

{
"rows" : [{
	"CustomerID" : "DM",
	"CompanyName" : "Dunder Mifflin, Inc.",
	"Address1" : "78 Branch Street",
	"Address2" : null,
	"City" : "Scranton",
	"StateProvince" : "Ohio",
	"ZipPostalCode" : "38410",
	"Country" : "United States",
	"Terms" : "Net 30",
	"Notes" : "Very weird manager.",
	"Contact" : "Michael Scott",
	"ContactEmail" : "michael.scott@dundermifflin.com",
	"ContactPhone" : "360-555-4410",
	"ContactFax" : "360-555-4411",
	"ContactPhoto" : "?method=load&database=ExampleData&dataset=Customer&column=ContactPhoto&row=DM",
	"ContactPhoto_ContentType" : "image\/png"
}, {
	"CustomerID" : "INI",
	"CompanyName" : "Initech, Inc.",
	"Address1" : "250 Industrial Parkway",
	"Address2" : null,
	"City" : "Austin",
	"StateProvince" : "South Dakota",
	"ZipPostalCode" : "45100",
	"Country" : "United States",
	"Terms" : "Net 30",
	"Notes" : null,
	"Contact" : "Bill Lumbergh",
	"ContactEmail" : "bill.lumbergh@initech.com",
	"ContactPhone" : "328-555-1247",
	"ContactFax" : "328-555-1248",
	"ContactPhoto" : "?method=load&database=ExampleData&dataset=Customer&column=ContactPhoto&row=INI",
	"ContactPhoto_ContentType" : "image\/png"
}, {
	"CustomerID" : "VI",
	"CompanyName" : "Vandelay Industries----",
	"Address1" : "102 West 34th Street",
	"Address2" : "sdafsafa",
	"City" : "New York",
	"StateProvince" : "New Jersey",
	"ZipPostalCode" : "61578",
	"Country" : "United States",
	"Terms" : "Net 30",
	"Notes" : "Do not give credit to Mr. Costanza - appears to be fake business.\u000A\u000A5\/4\/2017 5:33 AM: ",
	"Contact" : "George Costanza",
	"ContactEmail" : "g.costanza@vandelay.com",
	"ContactPhone" : "212-555-7450",
	"ContactFax" : "212-555-7451",
	"ContactPhoto" : "?method=load&database=ExampleData&dataset=Customer&column=ContactPhoto&row=VI",
	"ContactPhoto_ContentType" : "image\/png"
}
]
}

This kind of "schema-full" approach designed for TDataset/EWB is useful for some kind of applications. It is transparent and automatic but the big issue: the EWB generate a big JS metadata overhead. 

 

I think it's possible to implement a lighter SMS version of this EWB databound example.

Share this post


Link to post
Share on other sites
Thanks for that.

 

Your EWB example sort of follows the same path as my tiny demo project on the top of this thread

The data returned from the sql query in my demo has the same structure as your EWB example :

 



{"smsrows":[
  {"challenge":"Tupper Lake Triad",
   "mountain":"Mt. Arab",
   "descr":"Mt. Arab has a restored fire tower and observers cabin at its summit. There is a small museum located in the observers cabin...."
  },
  {"challenge":"Tupper Lake Triad",
   "mountain":"Coney Mtn",
   "descr":"Gradual looping trail leading to an open rock summit. Excellent view of Coney Mt in the distance. An excellent Snowshoe trail"
  },
  {"challenge":"Tupper Lake Triad",
   "mountain":"Goodman Mtn",
   "descr":"Trail begins at Lumberjack Spring. The first quarter mile is wheelchair accessible. Turning sharply left..."
  },
  {"challenge":"Saranac Lake 6er",
   "mountain":"McKenzie",
   "descr":"Lorem ipsum dolor sit amet, consectetur adipiscing elit. Vestibulum a ipsum leo. 
  }]
}


 

EWB formats the meta data (methinks) by issuing another call to the database to extract its data structure (something like 'SHOW COLUMNS FROM TABLE' in MySQL dialect) and stores that internally. I could have done that in this demo as well, but to avoid the additional call I instead extracted the metadata from the query results itself, and used that to produce TFields on the fly and bind these to the respective UI controls.

Not much of a difference, both ways produce results

and I think the overhead is not too bad either way

 

The other difference of course is that EWB, like Delphi, has a designer with specialised component attributes

I'm sure this will come to SMS but at the moment we can't use the visual designer to handle database connectivity at design time, so have to do the binding etc in code. 

 

At the moment I'm trying to get my head around the server-side side of things.

I'm comfortable using php/mysql as server components, and sort of node/sockets as well

However there is a multitude of other possibilities out there (Google Drive, Redis, NoSQL, etc etc), provisioned by big-name companies (AWS, Google, Microsoft, IBM) and some smaller ones, each with their own hosting environments and proprietary programming interfaces. 

Bewildering.

 

I'm reading up on Docker to see if that possibly could be a unifying approach

Docker at least can handle distributed data, but also distributed functionalities (micro services)

 

In the meantime here is another little project

based on Igor's remark who said he would like to push query statements to the server so as not to expose any database structure

The project shows a login screen to a database with the options of where to handle authorisation (client or server) and where to define any sql statements (client or server). 

Connecting shows a poor-man's database grid (a TMemo) with the meta-data and data extracted

The meta-data is used to construct a TDataset (because we can)

and also it generates a php-file and a project file where the contents depend on what was selected in the radio-button options

 

If you switch authorisation from server to client, and press 'generate server script' again, you see the differences

 

BTW, the generated code is only partly formatted correctly, proof of concept only I'm afraid, but you'll get the gist

 


Share this post


Link to post
Share on other sites

REST

 
I haven't used REST services much, but since the subject of this post is on database connectivity, REST deserves a place here
Basically REST services are a set of pre-defined database calls which can be invoked by accessing specific url's
So for instance a list of employees could be accessed by a http 'get' call to "//yourserver/hr/getemployees" or a particular employee could be 'gotten' through "//yourserver/hr/getemployees/123"
Database inserts would be handled through 'post' calls, updates through 'put' or 'patch' and deletions through 'delete' calls. 
 
I've created a REST server for the MySQL test database used in these posts.
This REST server has 2 endpoints :
- http://www.lynkfs.com/Experiments/TDBx/TDBx5/hello    which simply returns a 'hello world' string
- http://www.lynkfs.com/Experiments/TDBx/TDBx5/getdata  which is bound to a select query
 
This last url has a variable attached to it, to further identify the type of query :
- http://www.lynkfs.com/Experiments/TDBx/TDBx5/getdata?var=everything    :  select * from table (select * from ADK)
- http://www.lynkfs.com/Experiments/TDBx/TDBx5/getdata?var=limited       :  select a couple of fields from table (select challenge, mountain, descr from ADK)
 
Usage is not much different from a regular http request to a mysql/php server script :
 
procedure TDataSource.RequestSQLSelect;
begin
  FHttp := TW3HttpRequest.Create;
  FHttp.OnDataReady := RetrieveSQLSelect;
  FHttp.open("GET",'http://www.lynkfs.com/Experiments/TDBx/TDBx5/getdata?var=everything');
  FHttp.setRequestHeader("Content-type","application/x-www-form-urlencoded");
 
  FHttp.Send();
end;
 
procedure TDataSource.RetrieveSQLSelect(Sender: TW3HttpRequest);
begin
  writeln(sender.responsetext);
end;
 
 
Alternatively the SmartCL.Inet.REST unit can be used to define client handling
 
procedure TFormMain.btnIPClick(Sender: TObject);
begin
  REST['http://www.lynkfs.com/Experiments/TDBx/TDBx5/getdata?var=everything', ''].Call
    .OnPrepare(LogHttpDetails)
    .NoRandomize
    .OnDone(LogResult)
    .OnError(LogRESTError)
    .Get;
end;
 
procedure TFormMain.LogResult(http: TW3HttpRequest);
begin
  Log(http.ResponseText);
end;
 
with the same results

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
Sign in to follow this  

×