Results 1 to 12 of 12
  1. #1
    Matko is offline Member
    Join Date
    Feb 2012
    Location
    Slovenia
    Posts
    8
    Rep Power
    0

    Default SQLite database at FTP server

    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!

  2. #2
    Matko is offline Member
    Join Date
    Feb 2012
    Location
    Slovenia
    Posts
    8
    Rep Power
    0

    Default Re: SQLite database at FTP server

    Anyone? Please :)

  3. #3
    Matko is offline Member
    Join Date
    Feb 2012
    Location
    Slovenia
    Posts
    8
    Rep Power
    0

    Default Re: SQLite database at FTP server

    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?

  4. #4
    Tolls is offline Moderator
    Join Date
    Apr 2009
    Posts
    11,856
    Rep Power
    19

    Default Re: SQLite database at FTP server

    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.

  5. #5
    Matko is offline Member
    Join Date
    Feb 2012
    Location
    Slovenia
    Posts
    8
    Rep Power
    0

    Default Re: SQLite database at FTP server

    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:
    Java Code:
        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?

  6. #6
    Tolls is offline Moderator
    Join Date
    Apr 2009
    Posts
    11,856
    Rep Power
    19

    Default Re: SQLite database at FTP server

    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.

  7. #7
    DarrylBurke's Avatar
    DarrylBurke is offline Member
    Join Date
    Sep 2008
    Location
    Madgaon, Goa, India
    Posts
    11,184
    Rep Power
    19

    Default Re: SQLite database at FTP server

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

    db
    If you're forever cleaning cobwebs, it's time to get rid of the spiders.

  8. #8
    Matko is offline Member
    Join Date
    Feb 2012
    Location
    Slovenia
    Posts
    8
    Rep Power
    0

    Default Re: SQLite database at FTP server

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

  9. #9
    Tolls is offline Moderator
    Join Date
    Apr 2009
    Posts
    11,856
    Rep Power
    19

    Default Re: SQLite database at FTP server

    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?

  10. #10
    Matko is offline Member
    Join Date
    Feb 2012
    Location
    Slovenia
    Posts
    8
    Rep Power
    0

    Default Re: SQLite database at FTP server

    :(

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

  11. #11
    Matko is offline Member
    Join Date
    Feb 2012
    Location
    Slovenia
    Posts
    8
    Rep Power
    0

    Default Re: SQLite database at FTP server

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

  12. #12
    Tolls is offline Moderator
    Join Date
    Apr 2009
    Posts
    11,856
    Rep Power
    19

    Default Re: SQLite database at FTP server

    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.

Similar Threads

  1. JSON Array Loop Inserting into Android SQLite Database
    By viviosoft in forum New To Java
    Replies: 6
    Last Post: 09-26-2011, 10:34 AM
  2. Database connection for server
    By JavaDreams in forum New To Java
    Replies: 5
    Last Post: 03-14-2011, 10:25 AM
  3. Replies: 12
    Last Post: 08-26-2010, 10:14 AM
  4. Replies: 1
    Last Post: 12-18-2009, 01:16 AM
  5. database server??
    By sweet angle in forum JDBC
    Replies: 1
    Last Post: 07-29-2008, 11:42 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •