Connect to a database on a different computer

Databases
1

Hello.

I just talked to a client’s customer (I wrote a new version of an existing program). The customer’s computer for this product is in some closet and they can only access it by standing on a table. (That’s the motivation for my question.)

The product consists of two programs: an Administrative program and a Display program. (The previous version had all that in one program. I separated the functionality).

I would like to have the Administrative program run on a different computer than the Display program. This way the customer can do the various (and often needed) administrative functions and a safe, productive setting.

The question: how to connect the Admin program to the database. They’re running Windows. Database is Sqlite. It will remain single-user.

-Bob

2

Can you not just map the drive on Computer “A” that hosts the database to be a drive letter on Computer “B”?

Then you app just needs to know the database is on “Drive Z:” (or whatever)

3

Dave,

That’s exactly what I need to know. My problem is that I do almost all my work on Macintoshes.

So, I would map the drive on Computer A to a drive letter on computer B and then I can simply use the path to the database, and I’m good to go?

4

[quote=265550:@Dave S]Can you not just map the drive on Computer “A” that hosts the database to be a drive letter on Computer “B”?

Then you app just needs to know the database is on “Drive Z:” (or whatever)[/quote]

Yeah that’s likely to suck - see http://sqlite.org/faq.html#q5
Even if you have no locking issues others having done this have said its very slow

5

Norman,

Good to know. My bet is that it will be better than climbing on a table (which is what they are doing now).

-Bob

6

Well under OSX, I have an SQLIte database on a NAS server, and its accessed by 4 Macs (never more than one at a time), and it seems to suffer no performance issues at all…

Just need to address the location of the database with a complete path is all (and of course have file sharing active for OSX)

7

Hows the NAS mounted - NFS , SMB, AFP ?
Different protocols support different things and the recommendation by the sqlite folks is a general “dont do this as its not reliable”

YMMV

8

Then back the DB up regularly, like every time you start your app, as when you lose it or it gets corrupted, which it likely will, then you have some means to recover

9 
afp://DiskStation._afpovertcp._tcp.local
10

Could you maybe configure the Windows computer so you can remote desktop to it from another computer? Then you could just keep everything on the Windows computer.

11

Create a Xojo webapp on the server to handle Sqlite database and the sql-queries and deliver the results to a browser on the client-pc.

12

[quote=265568:@Dave S] afp://DiskStation._afpovertcp._tcp.local [/quote]
AFP - not sure if it does locking properly or not
Better question for the sqlite folks is whether it works as they expect things to work since thats who says “dont do this”

13

hence [quote] (never more than one at a time)[/quote]
I don’t attempt to use it in a multi-user mode, and since it is just my wife and myself its not an issue

and the OP also stated [quote]. It will remain single-user.[/quote]

14

Multi user is not the only issue noted in that link
It is definitely the most common failure cause though

Just sharing the db between two computers may cause issues
Access had similar issues sharing this way

IF you do set things up this way back up the db every time you open it so WHEN it goes corrupt you have a backup to go back to

15

Why not run the free version of Valentina server on the customer computer?

16

If it definitely a single-user then why not create a free Dropbox account and place the database on there? Then link both computers to the dropbox account and the programs can run on the ‘shared’ database.

17

MSSQL Express is free for db’s up to 20Gb, PostgreSQL is free and very easy to set up on Windows, mySQL is also easy (if you can work out the license). SQLite is a brilliant product, but I wouldn’t use it in a multiuser/multiclient environment, I do use it all the time for in memory db’s, and single user apps.

To me it’s a matter of choosing the right tool to do the job.

18

Or write it as a Web app that talks to a local SQLite database. Fixes the multi-user, cross-platform and updates issues in one!

19

Regarding drive mapping and sharing, I set up a small NAS unit recently that talks to OSX. By default, when sharing, both afp and smb were enabled and the network was shockingly slow.

After reading a bit on the net, I disabled smb altogether and stuck with afp only. Performance went from around 8-9 Mb/s to just over 90 Mb/s. The before and after are reproducible too. I’ve sent a report to Apple regarding this.

There have been no penalties or downsides so far by switching to afp only.

20

There are known issues in Apple’s rewritten SMB client