Database Access

[Peter M.]

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

[Peter M.]

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

[Walter]

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

[Peter M.]

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

[Walter]

1.  Prefer GUID's in this case.

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

Walter

[Peter M.]

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

[Kevin Powick]

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.......There can also be 4 basic functions that can be used to call stored procedures instead of sending the SQL from the client

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

 

[Peter M.]

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

[Kevin Powick]

Hi Peter,

instead of using the Automatic Sessions which get generated by RTC, I’m creating my own

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

Getting through firewalls is a requirement so a standard protocol like XML is important.
(I’ll have to get over my concern about “bloat”.)

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.
 

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?

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

[Peter M.]

...the overhead of the XML format was insignificant to performance

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

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

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

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

   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

[Kevin Powick]

Hi Peter,

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

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 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.)

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.

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?

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

[Peter M.]

Kevin,

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

-Peter

[Peter M.]

Kevin,

What database are you using for this project?

Thanks,
-Peter

[Kevin Powick]

Hi Peter,

What database are you using for this project?

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

[Peter M.]

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?