Jump to content


Photo

Database connectivity - 2


  • Please log in to reply
6 replies to this topic

#1 Nico Wouterse

Nico Wouterse
  • Moderators
  • 249 posts
  • LocationAustralia

Posted 17 May 2017 - 07:42 AM

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


  • ielite and Igor Savkic like this
Nico Wouterse

#2 Igor Savkic

Igor Savkic
  • Members
  • 184 posts

Posted 17 May 2017 - 11:42 AM

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.


  • ielite and Nico Wouterse like this

#3 Laksekjønn

Laksekjønn
  • Members
  • 440 posts

Posted 22 May 2017 - 05:41 PM

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.elevateso...=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.


  • Nico Wouterse likes this

#4 Nico Wouterse

Nico Wouterse
  • Moderators
  • 249 posts
  • LocationAustralia

Posted 23 May 2017 - 06:18 AM

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
 

  • ielite and Igor Savkic like this
Nico Wouterse

#5 Laksekjønn

Laksekjønn
  • Members
  • 440 posts

Posted 23 May 2017 - 08:03 PM

... just to reference, some time ago MarkusJa - I think he has abandoned smart cause lack of support from the official team, anyway he has shared me the SMS Data Provider prototype.


  • Nico Wouterse likes this

#6 Nico Wouterse

Nico Wouterse
  • Moderators
  • 249 posts
  • LocationAustralia

Posted 24 May 2017 - 04:23 PM

Very impressive prototype from Markus

thanks for sharing

 

I'm having a good look at his code


Nico Wouterse

#7 Nico Wouterse

Nico Wouterse
  • Moderators
  • 249 posts
  • LocationAustralia

Posted 24 May 2017 - 04:28 PM

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.co...DBx/TDBx5/hello    which simply returns a 'hello world' string
- http://www.lynkfs.co...x/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.co...?var=everything    :  select * from table (select * from ADK)
- http://www.lynkfs.co...ata?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

Nico Wouterse




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users