Leonardo M. Ram? wrote 50 lines
On 2010-10-15 14:24:48 +0200, Michael Van Canneyt wrote:
>> After removing the TSqlQuery and TSQLTransaction components from my app,
>> a new question came to my mind, in the case of complex inserts/updates
>> that involves more than one database table, is it possible to use
>> transactions?.
>
> Hm. Not if you use the TSQLDBWebDataProvider. To do that, you would
> have to use TSQLSuery and a regular TWebDataProvider, and hook into
> the Before/after post events of the TSQLQuery.
>
> But I'm certainly open for suggestions for improvements.
>
> Michael.
Well, It's not an issue at all, I could create a stored procedure in
charge of receiveing the params from the dataprovider, then adapt them to
their respective tables. It's not an issue because I usually work with
Firebird and PostgreSql, and they have stored procedures.
I ask this because I'm thinking of adding a new layer of abstraction by
letting the TFPWebProviderDataModule load a configuration from, say, an
XML file with the list of Providers and its Select, Insert, Update, and
Delete statements, like this:
<Module name="ClientesProvider">
<Provider name="all-customers">
<Select>select * from customers</Select>
</Provider>
<Provider name="one-customer">
<Select>
select * from customers where IdCustomer=:IdCustomer
</Select>
<Insert>
insert into customers(Name, Age)
values(:Name, :Age)
</Insert>
<Update>
update customers set Name=:Name, Age=:Age
where IdCustomer=:IdCustomer
</Update>
<Delete>
delete from customers where IdCustomer=:IdCustomer
</Delete>
</Provider>
</Module>
With this data, the cgi/fcgi app could dynamically create the
TPWebProviderDataModule and everything else.
What do you think about this?
But you can do these things already, I believe.
I had multiple tables and some simple access rights to different people so my SQL statements had to be different for everyone to get different records from the tables.
What I did is, I started from the first demo example found in fcl-web/examples/webdata/demo/
and created ONE action called "CRUD" that handles all the crud request from the extjs clients. However, I am passing some additional parameters that specifies which table is to be used and what needs to be done (Create, Read, Update, Delete). From the session I got who is the user, so I was able to prepare the proper SQL statements myself.
For example, the extjs data store proxy looks like this in the client side extjs:
===========
var myproxy = new Ext.data.HttpProxy ( {
api : {
//XML
// create: GetCGIURL('/CRUD?format=xml&t=0&d=0'), // read: GetCGIURL('/CRUD?format=xml&t=0&d=1'), // update: GetCGIURL('/CRUD?format=xml&t=0&d=2'), // destroy: GetCGIURL('/CRUD?format=xml&t=0&d=3') //end of XML
//JSON
create: GetCGIURL('/CRUD?t=0&d=0'),
read: GetCGIURL('/CRUD?t=0&d=1'),
update: GetCGIURL('/CRUD?t=0&d=2'),
destroy: GetCGIURL('/CRUD?t=0&d=3')
//end of JSON
}
});
==========
You can see that I am passing the "t" and "d" additional parameters. "t" can be the table/database to use and "d" can be the action to be performed (create, read, update, delete), etc. In the web module "crud" action I just need a case statement for the different SQL statements based on these passed parameters.
Of course it is more complicated to build the actually executed SQL statements in my app, because I also process the sort order, the number of records to be returned, and have some record filtering coming form the extjs client, and as mentioned above, based on the user different records needs to be returned, etc.
Plus, I created some additional actions in the webmodule for other things like a "keepalive" call from the client so the session is not closed if the user uses the client for a long time but there are no server calls to refresh the session, and so on for other things and responses, web pages with templates, like login/logout, etc.
AB
