RTC Forums

I have been watching the RTC SDK development over the last few years and I have always wanted to see a set of simple n-tier/middleware components that use the SDK as their foundation.

I've read the 10 archived "RTC SDK Articles" by Glynn Owen from 2007, and I also read the "Database access with the RTC SDK" article by Dennis Ortiz.

Rather than reinvent the wheel from scratch, I'd like to know if anyone has actually used the SDK for database access?

(I just want to know if you've gotten it to work, and if so then what were the lessons/challenges along the way.)

Thanks,
-Peter

By the way, I know there are several directions to take this ... right now I'm leaning toward the Server application having 5 basic functions which can get called by the Client app:

- Login:  This is a process which looks up a username/password in the Users table.
            (If found then it creates a session GUID which is used in all future Requests.)

- Delete: The client sends a SQL such as "DELETE FROM CUSTOMERS WHERE CUSTOMER_ID = 1001"
            (The server returns back a boolean result of True/False.)

- Insert: "INSERT INTO CUSTOMERS (CUSTOMER_ID, CUSTOMER_NAME) VALUES (1001, 'JOHN SMITH')"
            (The server returns back either "0" if unsuccessful or the CUSTOMER_ID if ok.)

- Select: "SELECT * FROM CUSTOMERS ORDER BY CUSTOMER_NAME"
            (The server returns back a boolean Result of True/False and the selected records.)

- Update: "UPDATE CUSTOMERS SET CUSTOMER_NAME = 'MARY SMITH' WHERE CUSTOMER_ID = 1001"
            (The server returns back a boolean result of True/False.)


There can also be 4 basic functions that can be used to call stored procedures instead of sending the SQL from the client:
      ("StoredDelete", "StoredInsert", "StoredSelect", "StoredUpdate").


The client app will use a generic TDataset memory table (there are several to choose from).


Thanks for any comments/questions/suggestions.

-Peter

Peter

I have done this kind of Server/Client app with RTC and it works flawlessly.

From the lessons and articles you mention... I did the same thing, read them, then built a multi threaded server with a MS SQL DB Pool .... I basically had 3 functions Login, Select, Execute (which handles Delete, Insert, Update).  I build the sql statement in client and send to server.

With the select I used the rtc RTC2Dataset stuff to fill a TClientDataset when retieving data.

I also have another app that used DBISAM at the backend...

Walter

Thanks for the info Walter ... very helpful!

1. Out of curiosity, did you use AutoInc fields as your primary keys with DBISAM?
   (I'm asking because I haven't found a way to INSERT a record in DBISAM and then get back the AutoInc value within the same SQL statement.)

2. When using the RTC2Dataset process do you always include the "Field Definitions" or do you save bandwidth by only returning the "Field Values" back to the client?

Thanks in advance.
-Peter

1.  Prefer GUID's in this case.

2.  Yes - include definitions.  I use the TRtcDataSet to return the results of the query.   

Walter

Very good.  If I may ask 2 more questions...

1. How many simultaneous database connections were you able to handle?
   (This is very important for us ... we currently have 1,000 and it will be up to 10,000 by the end of 2011.)

2. Since you have a working version for MS SQL and for DBISAM, do you feel these would be a good starting point for other RTC users?
   (In other words, with Danijel's permission, these could be put into a download "repository" from which other SDK Pro users could build on them to add more functionality / other database engines / etc.)


And now 2 comments...
1. Although browser-based app development is very popular, they don't currently give us the speed and functionality which our customers require.  That's why I want to pursue this thin-client process.

2. To make this database process bullet-proof, someone can create the same type of testing environment which Danijel used to prove RTC -- multiple computers, multiple users, etc.


Again, I really appreciate your feedback.
(I do wish others would share their experiences...)

Feel free to email me privately if necessary.

Thanks,
-Peter

Personally, I'm not a fan of this type of a approach.  Sending straight SQL commands or stored procedure names from the client has the following disadvantages:

• For security purposes, it's not a good idea to send straight SQL or even stored procedure names across the wire.
• You limit yourself to SQL and a particular dialect.  A database change in the future would mean changes required to your clients.
• Any changes to your business logic likely requires a change to your client code, resulting in client redeployment.
• The approach is so little a departure from straight client/server that it does not offer the true advantage of n-tier design.

What I would do instead, is to think of your RTC server in terms of an application server.  This server provides an API against which clients are coded.  You want to remove from the client any details having to do with the actual implementation of any server functions.  Why? Because it is not necessary, nor desired, that clients be aware of such implementation details.  Your RTC sever should essentially be a "black box" of which clients only need to know its API.

So, one of your server API functions might be Login with two parameters - UserID and Password, returning a SessionID.  That is all the client needs to know.  How that SessionID is generated is irrelevant to the client.  Also, if the SessionID generation method changes in the future, your clients may not need to be altered at all.

While you might end up with a loose relationship between a server API call and the name of a stored procedure you actually do use in your database (Login, for example), The advantage of the API approach is that if for some reason you needed to change or replace your stored procedure i.e NewLogin, you can do so without altering your clients; They still call Login, but the server now runs NewLogin.

Now, if you really want to be flexible, you could use RTC data provider components on the server to make a "universal" application server.  Provide your data over HTTP in XML-RPC, custom XML, JSON, or some other custom format.  RTC supports XML-RPC directly.  Others would take a little more work.  This has the huge advantage of making client development language agnostic, because the only requirement is that the client can act as an HTTP client, consuming the services dished-up by your server.  Examples of this would be PHP, JavaScript, iPhone, Android, Java,  C++, .Net, etc, etc.

--
Kevin Powick

 

Kevin,

First of all, thanks for posting to this thread -- I’ve read some of your other posts and I was hoping you’d respond!

I’m glad you brought up the strategy of not sending SQL from the Client.  My initial reason for creating the SQL in the Client App was to free up that task from the Server App ... in other words, I know the Server App is going to have a lot of processing to do, so why give it another task which could easily be done within the Client App?

However, that being said, I do agree with your list of “disadvantages”.  And realistically, the extra processing to create the SQL statements should not have a huge negative impact on performance. 
(But if it did then I’d probably need to use a load balancing strategy with multiple server machines anyway.)

Regarding the “Login” function only returning SessionID (as a GUID), I agree.  However, instead of using the Automatic Sessions which get generated by RTC, I’m creating my own and storing them in a memory table within the Server App.
(I’m using a memory table for speed ... if I have to reboot the Server App then the SessionID records are lost, but I’m able to handle this scenario with another function/process.)

Regarding the “universal” application server (ex: XML-RPC via HTTP), I really like this idea!  Getting through firewalls is a requirement so a standard protocol like XML is important.
(I’ll have to get over my concern about “bloat”.)

Questions:
1. Out of curiosity, have you created either this type of “universal” project (or something similar)?
2. If so, is it just a test project or is it actually in production?
3. Did you use the Connection Pooling code from Glynn Owen’s article?


By the way, if there is anyone else out there who has either thought about creating a database access app with RTC SDK, or has experimented with this type of app, or has successfully created this type of app then I’d really appreciate your comments.
(And I’m sure there are others who would also appreciate it :)

Thanks,
-Peter

Hi Peter,


We don't use RTC sessions either.  Nothing wrong with them, but we too handle our own session management.


We found that in our real-world operations, the overhead of the XML format was insignificant to performance.  That being said, I wouldn't mind one day seeing JSON handled by RTC out of the box.  Although, with the flexibility of RTC, there is nothing from stopping you from quite easily implementing JSON, or any other format, today.
 

1. Yes, we have RTC application servers simultaneously used by PHP, Delphi client apps, MS Excel (VBScript), RealBasic client apps on OSX, and an iPhone application.  The RealBasic and iPhone apps were developed by a 3rd party for their own internal use.  I haven't actually seen them, but the company says they work great.  We only had to provide them with our API.

2. It is a production system that has been running without a hitch for 3 years.

3. We do use database connection pooling, but not from the article you mention.

--
Kevin Powick

   Glad to hear this, and I agree that JSON out-of-the-box could be very useful.

   That’s awesome!  (You’ve definitely earned “bragging rights”.)

   Okay, now you’ve got me curious … basically, what did you do differently?


Kevin, I personally see a huge benefit to other RTC developers, as well as to Danijel and his company, if a rock-solid set of classes/components were created for database access.
(The fact that Walter's “RTC-DS” thread from April 2010 had 567 views, which was more than any other thread, suggests there is strong interest in this.)

Since you’ve been able to achieve amazing success with this type of project (3 years without a hitch – wow!), would you be “able and willing” to share some of your (non-proprietary) code and expertise?
(I’m willing to initially take on the role of coordinating the effort.)

Thanks,
-Peter

Hi Peter,


First, I don't think we looked at the code posted by G.O.  Second, I think we're using a simple object queue.  A queue of connection objects are created at start-up, then connection objects are popped off as needed by requesting processes, then pushed back onto the queue when no longer needed.

The queue itself is not a global variable.  It is privately contained within a data module that has two public methods -- Lock and Unlock(ConnObj).  The Lock function returns a connection object popped off the queue, and the Unlock(ConnObj) procedure pushes the connection object back onto the queue.  There are also tuneable mechanisms to handle how many attempts and  how long a process will try to attain a connection object before timing out with an error.


I understand why a lot of people want something like DA components for RTC, and I'm sure they would generate sales for Danijel.  However, I do not feel the same about such a feature.  As I mentioned in that thread, RTC-DS (officially RTC-DBA) would be great for Delphi RTC clients talking to Delphi RTC servers, but that's about it.  In terms of application design, I think that is quite limiting in a lot of ways, especially when considering how heterogeneous environments are these days.  I want to be able to say "Yes" when a customer asks if they can use technology "ABC" to communicate with their application server.


The stability of the project is mainly due to the rock-solid stability of the RTC SDK. :)  We've done nothing special in terms of coding, and I think that under the hood, you would find it all quite basic and ordinary.  All of the hard stuff is being done by RTC.  We're simply accessing a couple of databases and returning results in XML format.  To aid with the XML, we use NativeXML from Simdesign.  That's about it.

If someone has a specific question, I'll do my best to answer, but as far as coding against the RTC SDK, the RTC docs and example programs are pretty much what we based our own code on.

--
Kevin Powick

Kevin,

Thanks for this info, for the suggestion about NativeXML, and for your willingness to answer future questions.

-Peter

Kevin,

What database are you using for this project?

Thanks,
-Peter

Hi Peter,


We're using PostgreSQL, a traditional SQL/RDBMS, and D3 from TigerLogic (Formerly Raining Data, and originally Pick Systems).  D3 belongs to a class of NoSQL databases known as MultiValue databases.  Others in this class include OpenQM, jBASE, UniVision & UniData (U2), Reality, mvBase, and some others.

--
Kevin Powick

Funny you should mention PostgreSQL because that is exactly what I want to start with.
(I was also considering Firebird.)

What data access components did you use to connect to PostgreSQL?

We're using PgDAC from DevArt (formerly Core Lab).

http://www.devart.com/pgdac

I've always really liked DevArt DACs, but they didn't have one for Postgres until last year.  So, prior to using their pgDAC product, we were using the components from MicroOLAP.

Considering the other databases we're working with, a great deal of consideration is being given to switching to either DevArt's UniDAC components, or the very popular AnyDAC from DA-Soft.

--
Kevin Powick

I haven't been back it a bit....

I have to agree with Kevin's approach - API etc.

The RTC servers I have created were for very specific requirements which needed 3 or 4 functions.

From day of deployment they have been rock solid and never failed.

Walter

Walter, thanks again for your feedback. 


BTW, I'm currently working on the connection pool process...
a. Trying to decide whether to use a TList within a Critical Section, or a TThreadList.

b. Trying to decide whether to remove the DBConnection objects from the List using List.Delete and then add back using List.Add (based on archived articles),
   or whether to add an "Available" boolean property to the DBConnection which I can set to False when being used and True when done.
   (The advantage to "Available" is to keep track of the DBConnection objects and make sure they get freed in case of unexpected termination.)


If anyone has any thoughts on either of these items, I'd appreciate it!

Thanks,
-Peter

Peter,

Check out TObjectQueue of the Contnrs unit.

--
Kevin Powick

Kevin, thanks for your feedback.

I had read about the TObjectQueue which can automatically free it's TObjects whenever they are removed from the Queue.
(I'm still leaning towards TThreadList ... only because I want a thread-safe List of object pointers.)

However, I do see an unrelated usage for the TObjectQueue in my app ... so thanks again for the tip.

-Peter

Kevin,

Currently i am evaluating uniDAC for my next year project with RealthinSDK as the app server. From what i understand on UniDAC, it has advanced pooling mechanim in the TUniconnection component. So do we still need to implement the DB connection pooling ?

I don' t know.  I've never used UniDAC.

--
Kevin Powick

lionheart,

Questions:
1. What is the maximum number of simultaneous users/connections you will have?
2. Will any of your clients have firewall issues (do they need to communicate via http)?
3. Will the Requests/Results be handling large amounts of data (+100k, +500k, +1M, etc)?
4. Although Kevin is using PgDAC which is made by Devart (they also make UniDAC), what database will you be using?

Thanks,
-Peter

Kevin,
Thanks for your reply, I was trying my luck that you have converted your app to unidac :). I am using the trial version of UniDAC because i am evaluating the components, therefore i have not the source codes to verify the connection pooling implementation.

Peter,
Thanks for your reply, Before i answer your question please allow me share with you that i understand the importance of DB connection pooling in multi-user environment. The reason i am asking the necessity of it is specific to UniDAC (TUniConnection) which has its own pooling mechanism
and i thought Kevin, yrself or other gurus may share your experiences on it perhaps not to use the feature of the component due to it doesnt work well with RTC etc .. maybe.. :)

1. What is the maximum number of simultaneous users/connections you will have?
Somewhere ard 30

2. Will any of your clients have firewall issues (do they need to communicate via http)?
Should not be a problem because i work hand-in-hand with their MIS.

3. Will the Requests/Results be handling large amounts of data (+100k, +500k, +1M, etc)?
This depends on the parameters the clients set. I have DBgrids with some parameters (editboxes/comboboxes etc) for the users to set
which will be sent to the app server and use as query parameters to reduce the resultant dataset. However if the client does not put any parameters, it will be equivalent to returning the whole table ('SELECT column1, column2.. FROM table') which is not encourage but cannot hinder the user from doing so.

4. Although Kevin is using PgDAC which is made by Devart (they also make UniDAC), what database will you be using?
I will be using firebird for this project. The reason I am looking at UniDAC is for standardization (on my side)to meet other/future clients' request to use their choice SQL server.

Thanks for your answers.

If you will only have a maximum of 30 simultaneous connections, perhaps you don't need DB Connection pooling...

Out of curiosity, how did you decide on Firebird instead of PostgreSQL?
(I'm asking because I have to make the same decision.)

Thanks,
-Peter

Although the decision to use connection pooling is driven by factors such as the number of licensed server seats, the number of users,  server load, and usage patterns, I pretty much always implement it in any multi-user server application. Why waste resources and limit your ability to scale when the implementation of connection pooling is pretty easy?

--
Kevin Powick

Peter M.

Firebird is the preferred choice of the customer. To me it will be the same coding because i have all the SQLs statements at the app server (no store procs etc) and i am quite happy with my testing with unidac so far which makes it quite easy to switch to other SQL server like PostgresSQL etc.. :D

Kevin,
i agree with you on putting dbconnection pool on all multi-user application. What i am testing now is to use the pooling of the TUniConnection of unidac and see what happens next ;P.

This is what i am testing, please feel free to share your thoughts
1. I use jvpluginframework to modularize the application.
2. Place a UniConnection in one of the plugin. Enable the pooling feature of the component.
3. Place a RtcHttpServer in another plugin.
4. When loading the other plugins, Set all the RtcDataServerLink.server to the RtcHttpServer component in plugin (3) and datasets connection to the UniConnection component in plugin (2) (RtcDataServerLink is great in making this happen wih ease, try to do that in datasnap... hell break loose  :o)
5. .. still in progress... :) doing Rtcfuncs etc

If anyone is still searching for a place to start, take a look at the "rtcDB.pas" unit (added in RTC SDK v4.27), which contains a "DelphiDataSetToRTC" function (Server-side, to "pack" TDataSet data into a TRtcDataSet structure - ready for transport to the Client), "TRtcMemDataSet" and "TRtcDataSetMonitor" components (Client-side, for working wih in-memory TDataSet descendants which can be linked to DB-aware components to display TRtcDataSet data received from the Server) and the "TRtcDataSetChanges" class (Server-side, for accessing changes to a DataSet received from a Client). These new components, classes and functions provide basic functionality for working with DB-aware components when using the RTC SDK for communication, either through RTC remote functions or RTC Linkes Objects.

Best Regards,
Danijel Tkalcec

Hi Danijel,

A small demo would help to get of the ground

Would a "Fishfact Demo" be enough, or does it have to be something more complex?

Best Regards,
Danijel Tkalcec

That would be sufficient.

Probably good enough, but it might be nice to include a simple master/detail example, as it is a common real world data scenario.

RealThinClient SDK v4.28 is now ready for download, with "FishFactClient" and "FishFactServer" Projects. If there are any questions or suggestions about the "FishFactClient" and "FishFactServer" Projects, please post them to the "PRO Support" section.

A master/detail example should be ready in a few days.

Best Regards,
Danijel Tkalcec

RealThinClient SDK v4.29 is now also ready for download, introducing a "FishFactServer2" Project which demonstrates the use of SQL-capable components on the Server to fetch data from a Database, send it to the Client, then receive changes from the Client (INSERT/UPDATE/DELETE) and execute them on a Database by using SQL.

"FishFactClient" Project (introduced in v4.28) can be used for testing the "FishFactServer2" as well as the "FishFactServer" Project, because both Server-side implementations use the same "protocol".

All "FishFact" Projects can be found in the "Demos\DB_Access" folder.

Best Regards,
Danijel Tkalcec

thanks for the extensive demos they are very usefull, I have already tested the demos and I am impressed
I will post on the pro support section if I have any technical remarks or questions.

I've added another Client-side Database Demo Project by using TClientDataSet as Client-side in-memory storage, utilizing the TRtcDataSetMonitor component to catch any changes made to the DataSet and send them to the Server. This new Project is called "FishFactClient2", can be found in the "Demos\DB_Access" folder and is fully compatible with "FishFactServer" and "FishFactServer2" Projects, just like the "FishFactsClient" Project.

The idea behind using a TRtcDataSetMonitor (as in this new example) is to allow you to work with any 3rd-party TDataSet descendant component on the Client, or even mix different TDataSet implementations, while keeping your Server-side code unchanged. There are a lot of good in-memory TDataSet implementations available for Delphi and you can use any one of them with the RTC SDK, or use the simple and light-weight TRtcMemDataSet, which is now a part of the RTC SDK.

Best Regards,
Danijel Tkacec

RealThinClient SDK v4.36 is now ready for download, introducing a new "MasterDetailClient" example Project (under "Demos\DB_Access"), which uses asynchronous communication and the TRtcMemDataSet to work with DB-aware controls for displaying and editing of three related Tables received from the "BDEDemoServer" Project: Customers (master table), Orders (customer details) and Items (order details). Should anyone have questions about this new example Project, please post them to the "PRO Support" Forum section.

Best Regards,
Danijel Tkalcec

RealThinClient SDK v4.37 is now also ready for download, adding a new DB Client example which uses TClientDataSet and TRtcDataSetMonitor components (instead of TRtcMemDataSet) to demonstrates the use of 3rd-party in-memory DataSets for implementing Master/Detail relations in RTC Clients.

Best Regards,
Danijel Tkalcec