Networking / Database newbie question

Hi all,

I haven’t done ANYTHING with networking yet so I would appreciate some advice on where to get started.

I’m writing an app which is both for single users as well as groups.

Unfortunately PostGres, MySQL, etc are not an option as Database administration is way beyond my users comfort zone.

Those apps have to be able to communicate with each other to synchronize their data (most likely SQLite databases) as new records could have ben added locally (like when you are out of the lab) or in a “main” lab database.

I’m thinking of a “Server app” which handles the main database.

My questions as a total networking-newbie:

Which secure networking protocol should I use?

How can I send RecordSets? Binary? As XML? Json? Plain text?



Set up a server app. The client apps could communicate with the server with HTTPSocket + json. The server reads the json, processes the DB.

I personally use PHP as the server app, with a simple API which wraps a DB.

I’ve been working on a database server app for SQLite. I’m using Json to send queries and recordsets.

I’m a newbie at this also, so it’s been a good learning project for me.

Markus, if you have a Windows computer available (A Windows 7 computer with modest specs will do very well), you may want to take a look at MS SQL Server (The Express Edition is free, and in small to medium projects, it performs just as well as the big iron versions). It is very easy to set-up and maintain. Perhaps it is just because I am used to it, but I find it much much easier to manage than Postgre. Express with Advanced Services includes the Management Studio and basically anything you need to run a multi-user project on it. . MS SQL Server Express Edition is in my opinion vastly superior to SQLite and its server variants.

The down side is that you have a bit more work to do to connect to it from a Mac of a Linux workstation. There are threads on the subject on the Forum.

@Louis: Thanks, but MS SQL Server is not an option. Many labs are Mac only, and it still requires administration.

Basically my solution needs to be completely transparent and self-contained. The most I can expect of a user is to enter the IP of the server (though auto-discovery would be better).


No. For several reasons.

How about SAP Advantage web platform?, or local? They claim “No Administration”. (Well, such a claim coming from SAP AG needs to be taken with a certain level of caution…) I gather that you can use built-in web services to access and post data.

I don’t think it is free however.

Thanks guys but I’m not looking for third-party tools.

I need to have full control over my software. It might not be as good or full featured as CubeSQL or MS SQL or whatever, but I can adapt it to my needs - which are NOT filled by any of the commercial solutions. For example a user might need to display his company logo on the server app …

It sounds like you will have to go down the same path as described earlier by @Johnny Harris. Please let us know about the outcome!

you could build a web app that uses handlespecialurl to build out an API. recieve queries and send responses via json
then display the json in a listbox. you can then store the data in sqllite database

it would be fairly trivial to get a proof of concept up and running, i think there is an example in xojo web.

@Eli Ott: I had looked into CubeSQL. I’m also aware that CubeSQL Amin is open source and gave it a try just now. Nice, but too complicated and at the same time not suitable as is. It is still too much written from the point of view of a computer person, not the point of view of an end user.

Just an example: How to give privileges to a user (not a group) to access certain tables in the database but not others? It should be straight-forward but isn’t.

The success of FileMaker is build purely on making these things easy. My app needs to be even easier than FileMaker.

Nichts hasse ich so sehr wie FM – die schlimmste je entwickelte Software. Ich glaub wir sind von verschiedenen Planeten…

In one of my apps, I manage security within the app. The app is designed in transactions. At the beginning of each transaction, before any window is shown, a security check is done, resulting in either access or a message box telling why access is not granted. Individual user profiles are maintained in a set of tables in the database (there is an admin transaction to manage that), where each transaction is allowed or disallowed, allowed for display only, etc. Users are also graded as user, limited user, admin, superadmin (me, basically). Typically, users are limited to their own organizational units (but I have it configurable almost ad nauseam). Admins are given larger rights. I get everything (writing the app does come with certain privileges, but I can lock myself out of the app). In short, you can manage accesses within the app and keep full control without resorting to the built-in security of the RDBMS. It keeps the connections easier to manage overall. And the whole thing can be managed with just a few methods in a User class.

That said, I also implemented some security on the database itself, to provide specific accesses to the app user, and admin accesses to a small number of superadmins, and to hopefully keep everyone else away.

In short, I did not manage table access, but I chose instead to manage the content that is accessible wthin the tables. I would think that this approach is easily adaptable to the requirement that you described. It does require that the tables and entries are keyed to suitable organization or other actionable parameters. For example, a customer is maintained in several tables. One is a common table where the customer number is assigned. Specific sales data is maintained in a separate table where the key includes organisational unit information. A sales person can only access clients that can be selected with the organisational units granted to that sales person. A Master Data administrator can access any base table entry, but could be restricted to only some of the sales data.

Food for thought and a nice challenge. :slight_smile:

Filemaker makes me gag. Yes, it’s easy. Until you want to do something that needs something extra. Or a nice user interface.

Additionally, it’s flakey as heck. And it’s Java software. Did I mention the memory leaks? And scripts are done async.

Did I mention that Filemaker is awful?

FileMaker is easy to use. I need THAT level of easy for my users (and more), therefore my software goes beyond what I get from third-party solutions.

That’s all I said. No need to throw up on me :stuck_out_tongue_winking_eye:

Btw according to my wife I’m from Vulcan. Very locical until my emotions get aroused, then I go berserk …

Hi Markus

I did a sample project for a newbie last year that you could use to start. Server Project Client Project
These are both native Xojo projects using Eddies demo database. You’ll need to adjust the location of the database file in the event of the server project.

There is no attempt at security so you’ll have to make your own.


Thanks Wayne, much appreciated! :slight_smile: