SQLite database at FTP server

[Matko]

Hey

My first post here and I hope not the last one. Well short explenation what is my problem. So far I know how to make connection with a SQLite DB, do with sql statements and so on. Now the problem is that I wrote a program that store certain informations in this database while file is stored next to program location (or anywhere on disk, it doesn't matter as I know how to set path to it) but company that want to buy this program have two locations and I want to move DB on ftp server so both can access it at the same time.

I know that SQLite DB can have only one connection at a time so I also add timeout at 30s. In case one client is accessing from one location, in worst case scenario the connection will not be open for more than 10s, so this problem is eliminated. Now, each row in DB is changed 2 times and then locked for reading only.

My questions are:
1. What is the best way to access DB? I was thinking about this steps: download DB(file), edit it and then upload back again. That would make DB faster as it would be saved localy for editing, but what if in the same time another client want's to write into it? Then I have a problem as some work will be lost due to overwrite.
2. How time consuming is leaving DB at server and accessing it via ftp? Also how can I make this happen in program? Any reference to a good example or explenation would be welcome.
3. Is it possible to connect with program login, to know which client inserted which row?

If you need any more explenation I can give you but please, I have a deadline soon so I'm kinda tight with time.

Thanks!

[Matko]

Anyone? Please :)

[Matko]

Ok let's try with something I wrote so far:
private static final String DRIVER = "org.sqlite.JDBC";
private static final String user="username";
private static final String pass="pass";
private static final String url = "jdbc:sqlite:ftp://username:pass@server.com/temp/database.db:21";
private static final int iTimeout = 30;
private static Connection conn = null;
private static Statement stat = null;

Then in methods I create connection like that:
Class.forName(DRIVER);
conn = DriverManager.getConnection(url);

I catch exception and I get this:
java.sql.SQLException: path to 'ftp://username:pass@@server.com/temp/database.db:21': 'E:\NetBeans workspace\PotniNalogi\ftp:' does not exist

This is my local folder:
E:\NetBeans workspace\PotniNalogi\ftp:
and I don't understand why this is pointing to local folder. Is it because of "@" that break the link?

[Tolls]

Don't use SQLLite?
Or provide an interface that you expose to the network (may even be a simple web service), and that interface interacts with the database via the single connection?

The idea of controlling this via FTP sounds fraught with problems.

[Matko]

Thanks for answer!
What do you suggest then instead of SQLite? It should be free db as well. I choosed it because someone who suggested it to me, but he is working with visual studio...

It is not single connection. I have main program and calling methods in this class. I don't make object that has constant connection open as DB has to be available for more clients.

Code example:

    private static final String DRIVER = "org.sqlite.JDBC"; 
    private static final String user="user";
    private static final String pass="pass";
    private static final String POVEZI = "jdbc:sqlite:ftp://user:pass@mladivoznik.com/temp1/nalogi.db:21";
    private static final int iTimeout = 30;
    private static Connection conn = null;
    private static Statement stat = null;

static void ustvari() {
        try {
            Class.forName(DRIVER);
            conn = DriverManager.getConnection(POVEZI);
            System.out.println("manager OK");
            stat = conn.createStatement();
            stat.executeUpdate("CREATE TABLE IF NOT EXISTS nalogi"
                    + "(stNaloga, prviDatum, prvaUra, nalogodajalec, "
                    + "lokacija, zNalogo, drugiDatum, stDni, vozilo, placnik, "
                    + "dnevnica, predujem, datumPredujema, id, ime, priimek, "
                    + "naslov, delMesto,  odredbodajalec, koncani,"//20
                    + "datumPrihoda, uraPrihoda, odsotnost, stCelihD, "
                    + "stPolovicnihD, stMalihD, vrednostD, vnDnevnice1, "
                    + "sprDnevnico1, stSprDnevnic1, prSpremembe1, "
                    + "vnDnevnice2, sprDnevnico2, stSprDnevnic2, "
                    + "prSpremembe2, vnDnevnice3, sprDnevnico3, "
                    + "stSprDnevnic3, prSpremembe3, vnDnevnice4, "//40
                    + "sprDnevnico4, stSprDnevnic4, prSpremembe4, "
                    + "razlikaD, pot, KM, vrednostKM, drugiS1, vDrugihS1, "
                    + "drugiS2, vDrugihS2, drugiS3, vDrugihS3, drugiS4, "
                    + "vDrugihS4, drugiS5, vDrugihS5, drugiS6, vDrugihS6, "//59
                    + "drugiS7, vDrugihS7, drugiS8, vDrugihS8, drugiS9, "
                    + "vDrugihS9, drugiS10, vDrugihS10, vVsehDrugihS, "
                    + "priloga1, priloga2, priloga3, priloga4, priloga5, "
                    + "priloga6, lokShranjeno, datumZakljucka, predRacuna, "
                    + "odobril, likvidator, drugiDelKoncan);");//80
            conn.close();
        } catch (ClassNotFoundException | SQLException e) {
            try {
                conn.close();
            } catch (Exception e2) {}
            System.out.println("Exception: "+e);
        }
    }

What do you suggest instead of SQLite and FTP access?

[Tolls]

A proper database that is either on a server on their local network, or if it's not local then behind a web server possibly?
Not knowing your clients set up or requirements or what your system does it will be almost impossible to say.
But FTPing the database files around is probably not the route to take.

As for the "single connection" thing, you said in your OP that SQLLite can only have a single conneciton at a time. So therefore your app has to share this single connection.

As for free dbs that allow more than a single connection, you could try Derby/JavaDB I suppose. There are others that may be more suitable for you.

[DarrylBurke]

Don't double post. Your other thread has been closed.

db

[Matko]

@DarryBurke
Sorry for double post, noobish act indeed.

@Tolls
Thanks for reply!

Only file(database) itself is on a ftp server that can be accesed with username and password. There is also a web page on that ftp(if that helps). Client program is whole based on computer and should access only that database via ftp. I managed to find out how to download and upload that file, but I cannot find any tutorial or any help, how to only access file with sql connection (Connection conn = DriverManager.getConnection(url);).

My program has special class that has methods like createBase(the one I copied in previos post) searchDate, addSecondPart, addFirstPart, getOpenedTasks, ... In fact each method create connection and when it's done with what has to be done, it closes it.
I also have multiple jForms in program. Example, when I open jForm1, program automaticly check if correct database exist. Then when you enter all fields, it connect to base again to upload changes in database. Then when someone open jForm2, gets those tasks on the list, that aren't finished yet and has the ability to choose, which task he will work on to finish it. That means at least two calls for database at the begining and one when user save changes. Then I have third jForm that access all finished tasks. This can be solved by downloading whole database as it would work faster then if it is on ftp server(many rows inserted already). Well that was my plan to do but I stuck on the beginning, how to get access to change values in database while it is located on ftp server.

I don't quite understand if that's what you wanted to know or was it something else. Or did you want me to paste whole project (now already much more than 7k lines)? Even that class that should accessing to database is huge...

Otherwise, SQLite works pretty much the same as MySQL but I will check those two that you mention.

[Tolls]

Really there's not much I can say.
There's no way I can go through your program to figure out the best solution.
It sounds to me like you're trying to shoehorn a single-user desktop app into a multi-user one.
There may be something about the nature of your app that means that FTPing a database makes (some) sense, but to me it sounds like a hack.
What happens when your client decides they need 3 or 4 or 5 of these accessing the same file?

[Matko]

:(

All I want is to someone tell me how can I access database that is on server. Then tell me how other people do this? Let's say you have a game for android and you save best results to web server. How they acomplish this? I think that the problem is in server as it shouldn't be like mine. I checked what is supported in my server and SQLite is supportet. I also installed SQLite manager on it and make a base, but I cannot access it from outside of that manager. I gave all permissions possible for that file but it still give me err...

*edit*
And to be clear, I don't want to hack anything :)

[Matko]

Guys problem solved. SQLite is not meant to access accross the web...


"This will only work on a local network or file system. Don't try and use it to access a database accross the web. HTTP protocol, or any internet based protocol for that matter, won't work.
NOTE: SQLite does not have users or passwords for access"
Taken from here:
http://students.cs.byu.edu/~cs340ta/...se1/SQLite.php

Thanks for your help anyway. Next time I'll use another database :)

[Tolls]

When results are sent to a server on the web it is, as you say, a web server. Probably in the form of a RESTful web service.
That service will talk to a database either on the same box, or on the same network. The idea being that it is not a good idea to expose your database directly to the web.

SQLite (as you've just seen) is not the tool for the job. It is often used in handheld devices as the local db. It's Androids database for example.

As a note: By "hack" I use the term in a development sense ("I hacked a solution together"), which is generally used to mean you put in a fix for a bug that is clearly cobbled together and not the best solution, but the only one possible within short timescales.