How do I connect to a Remote Server or Computer for updating a SQLite Database

I want to connect to a Remote Server or Computer for updating a SQLite Database.  Do you have a small example code that allow me connect remotely to a computer.  Once connected, on my own, I have the destination URL of the SQL db and I can run my remote SQL statements.  But first I have to connect and login.  A second question, what type of protocol or connection in Java would this be?
Vanavah EdwardsAsked:
Who is Participating?
 
for_yanCommented:
That is how you connect to mySQL - and after that of course with SQL you can eiether select or insert or aupdate, tc.

import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

public class TestMySql {

     public static void main(String[] args) {
  System.out.println("MySQL Connect Example.");
  Connection conn = null;
  String url = "jdbc:mysql://host_name_or_ip_here:3306/";
  String dbName = "database_name";
  String driver = "com.mysql.jdbc.Driver";
  String userName = "users";
  String password = "password";
  try {
  Class.forName(driver).newInstance();
  conn = DriverManager.getConnection(url+dbName,userName,password);
      Statement stmt = conn.createStatement();
      ResultSet rs = stmt.executeQuery("select * from java_mysql.test_table");
      while(rs.next()){
          String s = rs.getString("a");
          float f = rs.getFloat("b");
          System.out.println("a: " + s + " b: " + f);
       
      }


  System.out.println("Connected to the database");
  conn.close();
  System.out.println("Disconnected from database");
  } catch (Exception e) {
  e.printStackTrace();
  }
  }

    
}

Open in new window

0
 
for_yanCommented:

look at this page:
http://www.zentus.com/sqlitejdbc/

You'll ned to do jdbc conection and work through jdbc
This is an example form the above page:

import java.sql.*;

public class Test {
  public static void main(String[] args) throws Exception {
    Class.forName("org.sqlite.JDBC");
    Connection conn =
      DriverManager.getConnection("jdbc:sqlite:test.db");
    Statement stat = conn.createStatement();
    stat.executeUpdate("drop table if exists people;");
    stat.executeUpdate("create table people (name, occupation);");
    PreparedStatement prep = conn.prepareStatement(
      "insert into people values (?, ?);");

    prep.setString(1, "Gandhi");
    prep.setString(2, "politics");
    prep.addBatch();
    prep.setString(1, "Turing");
    prep.setString(2, "computers");
    prep.addBatch();
    prep.setString(1, "Wittgenstein");
    prep.setString(2, "smartypants");
    prep.addBatch();

    conn.setAutoCommit(false);
    prep.executeBatch();
    conn.setAutoCommit(true);

    ResultSet rs = stat.executeQuery("select * from people;");
    while (rs.next()) {
      System.out.println("name = " + rs.getString("name"));
      System.out.println("job = " + rs.getString("occupation"));
    }
    rs.close();
    conn.close();
  }
}

Open in new window

0
 
Vanavah EdwardsAuthor Commented:
I can do a SQLite connection in Java already and it works well on my laptop.  What I am looking for is connecting to the host in order to execute the query.  This has to be done first before I run your posted code.  What type of connection must b establish first I need to know?  Is it ftp connection even though I am not transferring any files (can this work as it is basic), tcp/ip connection or what type of connection must I first establish and what code to use?
0
Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

 
for_yanCommented:
this is a question about ptython, neverethelsse it states this importnat thing:

http://stackoverflow.com/questions/2318315/connect-to-a-remote-sqlite3-database-with-python


      

SQLite is embedded-only. You'll need to mount the remote filesystem before you can access it. And don't try to have more than one machine accessing the SQLite database at a time; SQLite is not built for that. Use something like PostgreSQL instead if you need that.

If you want to use it form remote amchine you need to map the location of the
database file to your local machine

0
 
for_yanCommented:
The best way is to have the remote folder mapped to your local machine

Alternativley if you have ftp server running on that machine you can ftp the databse file(s) to
your local machine - but to do it from the code will not be that easy, not even talking about creating another unsyncronized copy which is always tourblesome for db
0
 
Vanavah EdwardsAuthor Commented:
As it is free, could mySql be used for multi-user access.  Also, I don't want to map a location as this is only an automatic  database java upate of a few seconds from a remote computer and then the connection is immediately close.  I just want to establish a connection with the remote.  After that I can update any database using the appropiate drivers.  How do I establish the connection witht he remote first.  Can frp be used to establish a connection (see attached code) only and stay connected So that I want login to the database and run SQL.  The reson for this is that frp is a file transfer protocol and I am not transferring any files at this point only updating a database.  I understand that tcp/ip connection is what is used for data communication.  

import java.io.IOException;

import sun.net.ftp.FtpClient;

public class FtpConnectionDemo {

	public static int BUFFER_SIZE = 10240;
	private FtpClient m_client;
	private String host = "";
	private String user = "";
	private String password = "";
	private String sDir = "moca";

  public FtpConnectionDemo() {
    try {
      System.out.println("Connecting to host " + host);
      m_client = new FtpClient(host);
      m_client.login(user, password);

      System.out.println("User " + user + " login OK");
      System.out.println(m_client.welcomeMsg);
      m_client.cd(sDir);
      System.out.println("Directory: " + sDir);
      m_client.binary();
      System.out.println("Remote Connection Successful");

    } catch (Exception ex) {
      System.out.println("Error: " + ex.toString());
    }
  }
  
  protected void disconnect() {
    if (m_client != null) {
      try {
        m_client.closeServer();
      } catch (IOException ex) {
      }
      m_client = null;
    }
  }
}

Open in new window

0
 
for_yanCommented:
If you want to use mySQL - taht is quite another story
mySQL is normal dtabase - you don't need any mapping - you don't nneed any priror connection to the reomte - all that is done withi
n the jdbc connection, and you don;t need any FTP, or TCP/IP (actually TCP?IP is used I believe, but you as aprogrammer
don;t need to think of it ) . I'll send you the example of connection to mySQL shortly.
0
 
for_yanCommented:



You'll need o download driver from here:
http://dev.mysql.com/get/Downloads/Connector-J/mysql-connector-java-5.1.18.zip/from/http://mysql.mirrors.hoobly.com/

Unzip that zip file and you'll see in the top folder (or maybe in the bin or lib folder) this jar:

mysql-connector-java-5.1.15-bin.jar

It needs to be on your classpath.

0
 
Vanavah EdwardsAuthor Commented:
This look like a solutioin.  I will try it now.
0
 
Vanavah EdwardsAuthor Commented:
SQLite is an embedded database but many large organization such as Google allow useers to remotely connect to it.
I tried conneting to a remote server by modifying your code - see snippet.  But I am getting an error at line 22
java.lang.ClassNotFoundException: org.sqlite.JDBC
      at java.net.URLClassLoader$1.run(Unknown Source)
      at java.security.AccessController.doPrivileged(Native Method)
      at java.net.URLClassLoader.findClass(Unknown Source)
      at java.lang.ClassLoader.loadClass(Unknown Source)
      at sun.misc.Launcher$AppClassLoader.loadClass(Unknown Source)
      at java.lang.ClassLoader.loadClass(Unknown Source)
      at java.lang.ClassLoader.loadClassInternal(Unknown Source)
      at java.lang.Class.forName0(Native Method)
      at java.lang.Class.forName(Unknown Source)
      at RemoteSQLite.main(RemoteSQLite.java:22)

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class RemoteSQLite {

     public static void main(String[] args) {
		  System.out.println("SQLite Connection Example.");
		  Connection connection = null;
		  String driver = "org.sqlite.JDBC";
		  String url = "jdbc:sqlite://ftp.label-crafts.com/";  //for SQLite HOST URL 
		  String dbName = "MOCA\\MOCADBF.db";
		  String username = null;
		  String password = null;
		  String mimporter = "1066 INVESTMENTS LIMITED";

		    try { 
			      Class.forName(driver).newInstance();
			    } catch (Exception e) {
			      e.printStackTrace();
			      return;
			    }
			    
		  try {
			  connection = DriverManager.getConnection(url+dbName, username, password);
		      Statement st = connection.createStatement();
		      System.out.println("Connected to Server");

		      // TABLE QUERY BY VARIABLE
		      ResultSet rscontacts = st.executeQuery("SELECT IMP_CODE, IMPORTER, IMP_ADDR1, " +
	    			"IMP_ADDR2, IMP_COUN, IMPCITY, IMPSTATE, IMPZIP, VATNO " +
	    			"FROM IMPORTER WHERE IMPORTER = '"+mimporter+"'");
		      rscontacts.next();
	    	
			System.out.println(rscontacts.getString("IMP_CODE"));
			System.out.println(rscontacts.getString("IMP_ADDR1"));
			System.out.println(rscontacts.getString("IMP_ADDR2"));
			System.out.println(rscontacts.getString("IMPCITY"));
			System.out.println(rscontacts.getString("IMPSTATE"));

	    	connection.close();
	    	System.out.println("Disconnected from Server");
	    	
	        } catch (SQLException e) {
	          e.printStackTrace();
	        }
	 }
}

Open in new window

0
 
for_yanCommented:
I guess this error is because you don't have this jar in your classptah:

http://files.zentus.com/sqlitejdbc/sqlitejdbc-v056.jar
0
 
for_yanCommented:
In general what you are sayng sounds weird to me.

People are writing SQLLite does not support remote access - google or no google.

Why do you mix-up database connection with ftp - afaik, they never had anything in common.

You had sound idea to direct your thoughts to mySQL - that is the right and normal thing to do - to have your java
connect to mySQL. Follow that path and it will be reasonable.

What is your general idea - do you want to generate some data, store it in relational databses for retrieeval at a later date,
perhpaps by different clients?

If so than it is a good ida to set up mySQL server on one of the hostas accessible to you and
access it from different java clients through jdbc/sql .
That is something which makes sense.


0
 
Vanavah EdwardsAuthor Commented:
First you were right I just installed the dirvers for the SQLite and I should have known better.  The SQLite application is used on employee smart phones where as the remote server may have a different type of SQL such as mySql or SQLite depending on the business.
0
 
Vanavah EdwardsAuthor Commented:
after the update of the drivers I run it and I am now getting the following error on line 28.  Because of the path problem, I made adjustments to line 14 and 15 - see snippet
Error-
SQLite Connection Example.
java.sql.SQLException: path to '//ftp.label-crafts.com/MOCA/MOCADBF.db': '\\' does not exist
      at org.sqlite.Conn.<init>(Conn.java:49)
      at org.sqlite.JDBC.connect(JDBC.java:64)
      at java.sql.DriverManager.getConnection(Unknown Source)
      at java.sql.DriverManager.getConnection(Unknown Source)
      at RemoteSQLite.main(RemoteSQLite.java:28)

String url = "jdbc:sqlite://ftp.label-crafts.com/MOCA/";  //for SQLite HOST URL 
		  String dbName = "MOCADBF.db";

Open in new window

0
 
for_yanCommented:
This makes all the sense in the world.

You need to know how to construct connection string specific for each database.
I could not find any connection string which points to remote datatabse for sqllite
- that is wehere I found that peopele are writing taht it does not work with the remote source.

Did you find some instruction that connection string for remote sqllite should be in that format?
If so, post a link.



0
 
Vanavah EdwardsAuthor Commented:
I just got the remote connection working by call in the class in snippet 8240733.  I am calling this snippet from my main program Snippet ID=8240734. tyring to print something from that database.  I am getting the path wrong for the remote in line 14 and 15 and this is causing an error.  It keeps looking at drive C and not the remote site "//ftp.label-crafts.com/MOCA/"  where MOCA is the folder on that site.  How do I get the remote path right and I think the problem is solved and I can close off.  

Here is the print out in from the console that I tracked - -->
Connecting to host ftp.label-crafts.com
User moca login OK
User moca logged in.
Directory: MOCA
Remote Connection Successful

ERRORS  Below - ==>
java.sql.SQLException: path to 'C:\Users\Vanavah\`My Documents\MOCA\MOCADBF.db': 'C:\Users\Vanavah\`My Documents' does not exist
      at org.sqlite.Conn.<init>(Conn.java:49)
      at org.sqlite.JDBC.connect(JDBC.java:64)
      at java.sql.DriverManager.getConnection(Unknown Source)
      at java.sql.DriverManager.getConnection(Unknown Source)
      at SimpleRemoteConnectionDb.main(SimpleRemoteConnectionDb.java:45)

import java.io.IOException;

import sun.net.ftp.FtpClient;

public class EstablishFTPConnection extends SimpleRemoteConnectionDb {

	public static int BUFFER_SIZE = 10240;
	private FtpClient m_client;
	private String host = "ftp.label-crafts.com";
	private String user = "moca";
	private String password = "coffee1";
	private String sDir = "MOCA";
	private String dataDir = null;

  public EstablishFTPConnection() {
    try {
      System.out.println("Connecting to host " + host);
      m_client = new FtpClient(host);
      m_client.login(user, password);

      System.out.println("User " + user + " login OK");
      System.out.println(m_client.welcomeMsg);
      m_client.cd(sDir);
//      dataDir = new m_client.cd(sDir);
      System.out.println("Directory: " + sDir);
      m_client.binary();
      System.out.println("Remote Connection Successful");

    } catch (Exception ex) {
      System.out.println("Error: " + ex.toString());
    }
  }
  
  protected void disconnect() {
    if (m_client != null) {
      try {
        m_client.closeServer();
      } catch (IOException ex) {
      }
      m_client = null;
    }
  }
}

Open in new window

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class RemoteSQLite {

     public static void main(String[] args) {
		  System.out.println("SQLite Connection Example.");
		  Connection connection = null;
		  String driver = "org.sqlite.JDBC";
		  String url = "jdbc:sqlite://ftp.label-crafts.com/MOCA/";  //for SQLite HOST URL 
		  String dbName = "MOCADBF.db";
		  String username = "moca";
		  String password = "coffee1";
		  String mimporter = "1066 INVESTMENTS LIMITED";

			// Establish Remote Connection
			new EstablishFTPConnection();
			
		  try { 
			  Class.forName(driver).newInstance();
		  } catch (Exception e) {
			  e.printStackTrace();
			  return;
		  }
			    
		  try {
			  connection = DriverManager.getConnection(url+dbName, username, password);
		      Statement st = connection.createStatement();
		      System.out.println("Connected to Server");

		      // TABLE QUERY BY VARIABLE
		      ResultSet rscontacts = st.executeQuery("SELECT IMP_CODE, IMPORTER, IMP_ADDR1, " +
	    			"IMP_ADDR2, IMP_COUN, IMPCITY, IMPSTATE, IMPZIP, VATNO " +
	    			"FROM IMPORTER WHERE IMPORTER = '"+mimporter+"'");
		      rscontacts.next();
	    	
			System.out.println(rscontacts.getString("IMP_CODE"));
			System.out.println(rscontacts.getString("IMP_ADDR1"));
			System.out.println(rscontacts.getString("IMP_ADDR2"));
			System.out.println(rscontacts.getString("IMPCITY"));
			System.out.println(rscontacts.getString("IMPSTATE"));

	    	connection.close();
	    	System.out.println("Disconnected from Server");
	    	
	        } catch (SQLException e) {
	          e.printStackTrace();
	        }
	 }
}

Open in new window

0
 
for_yanCommented:
I dont knwo what ou mean by this long numbers:
8240733  8240734

The fact that you connected to ft server using ftop client has absolutely nothing to do with JDBC connection.

There is nothing in common between them other than the network cable which goes out of your computer.

So I'm asking you did you see this form of connection string for the JDBC connection to SQLlite databse
  String url = "jdbc:sqlite://ftp.label-crafts.com/MOCA/";  
Post where you found that format.

If you inveneted it your self - whay are you surprised that it is not working?
The driver clasee in the jar file which you provided does not know this format and rejects it

People are writing as i posted that this type of databse (and of course its driver) are not designed to access remote databses,
other than like a file on mapped remote directiory ( then driover thinks that it is local).
Did you read somewhere opposite?

Isf so, please point to the link.

Otherwise don't try to uinvent connection string - it is not arbitrary format - you would not be able
to invent it and if it is inddeed not cdesigned for remote connection then
it can be used this way.



0
 
Vanavah EdwardsAuthor Commented:
Your code String url = "jdbc:mysql://host_name_or_ip_here:3306/"; asked for the host name to I wrote String url = "jdbc:sqlite://ftp.label-crafts.com/MOCA/";.  You are saying that this host name can only be a local host.
0
 
for_yanCommented:
Look, my code was for mySQL and it has nothing to do with sqlLite - why you mix everyrthing togetehr

and if that host ftp.label-crafts.com has ftp serevr it does not mean that it has ssqllite server or even mySQL server -
those are all absolutely different things.

You acnnot do it this way - each clienet is designe for each server
Each jdbc drover is cdesigned for its server and its own format of connection string

You are just wasting time cmbining things arbitrarily.
0
 
Vanavah EdwardsAuthor Commented:
I am not combining anything.  I have a SQLite database on that server and was trying to connect and query.  So I modified your code to see if I can do it.  I will try to upload a mysql on that server  and try your code again.
0
 
for_yanCommented:
You cannot do it - my code was for mySQL.
There is general cframework - ilike classes names coming from SQL specification - DriverManager, Connection, etc.
But even the name of the actual driver class like
com.mysql.jdbc.Driver and
the format of connection string and the actual contents of the driver classes is absolutely different - and each databse
will be different - Oracle, mySQL, MS SQL, Postre SQL, DB2 SQL - all have their own types of connection strings, driver class names,
and absolutely different contents ifthe drivers wghich know how to navigate thorogh the data of each particula databse.

So if it is true that sqllite was not cdesigned for remote - than it wmay not even have a formmat of the connection string which will
addres the server name or ip addres or something like that; but if it does , we need to kneo wexcactly the format specific
to sqllite.

FTP is absolutely differnt animal even never was sitting next to databses.
0
 
Vanavah EdwardsAuthor Commented:
I followed your instructions.  I have MySQL installed on a remote MySQL database server.  I am now getting an access error as follows -

MySQL Remote Connection
com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Access denied for user 'cloudmoc_vanavah'@'%' to database 'cloudmoc_CLOUDMOCA_MYSQL'
      at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
      at sun.reflect.NativeConstructorAccessorImpl.newInstance(Unknown Source)
      at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(Unknown Source)
      at java.lang.reflect.Constructor.newInstance(Unknown Source)
      at com.mysql.jdbc.Util.handleNewInstance(Util.java:411)
      at com.mysql.jdbc.Util.getInstance(Util.java:386)
      at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1052)
      at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3597)
      at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3529)
      at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:935)
      at com.mysql.jdbc.MysqlIO.secureAuth411(MysqlIO.java:4116)
      at com.mysql.jdbc.MysqlIO.doHandshake(MysqlIO.java:1300)
      at com.mysql.jdbc.ConnectionImpl.coreConnect(ConnectionImpl.java:2337)
      at com.mysql.jdbc.ConnectionImpl.connectOneTryOnly(ConnectionImpl.java:2370)
      at com.mysql.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:2154)
      at com.mysql.jdbc.ConnectionImpl.<init>(ConnectionImpl.java:792)
      at com.mysql.jdbc.JDBC4Connection.<init>(JDBC4Connection.java:47)
      at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
      at sun.reflect.NativeConstructorAccessorImpl.newInstance(Unknown Source)
      at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(Unknown Source)
      at java.lang.reflect.Constructor.newInstance(Unknown Source)
      at com.mysql.jdbc.Util.handleNewInstance(Util.java:411)
      at com.mysql.jdbc.ConnectionImpl.getInstance(ConnectionImpl.java:381)
      at com.mysql.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:305)
      at java.sql.DriverManager.getConnection(Unknown Source)
      at java.sql.DriverManager.getConnection(Unknown Source)
      at RemoteDbConnection.main(RemoteDbConnection.java:18)

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

public class RemoteDbConnection {

     public static void main(String[] args) {
  System.out.println("MySQL Remote Connection");
  Connection conn = null;
  String url = "jdbc:mysql://184.154.231.11:3306/";  //ip and port
  String driver = "com.mysql.jdbc.Driver";
  String dbName = "cloudmoc_CLOUDMOCA_MYSQL";
  String userName = "cloudmoc_vanavah";
  String password = "marketing";
  try {
  Class.forName(driver).newInstance();
  conn = DriverManager.getConnection(url+dbName,userName,password);
      Statement stmt = conn.createStatement();
      ResultSet rs = stmt.executeQuery("select * from java_mysql.sohead");
      while(rs.next()){
          String s1 = rs.getString("SONUM");
          String s2 = rs.getString("SODATE");
          System.out.println("sonum - " + s1 + " sodate - " + s2);
      }


  System.out.println("Connected to the database");
  conn.close();
  System.out.println("Disconnected from database");
  } catch (Exception e) {
  e.printStackTrace();
  }
  }

    
}

Open in new window

0
 
for_yanCommented:
Are you sure you have correct database name, user and password ?
Can you login to database through some other means - not through code -  with this username and password?
Did you install the databses and set up everything yourself  or do you have some DBA who was doing it ?
0
 
Vanavah EdwardsAuthor Commented:
I installed the db myself and all the tables.  I checked with the webhosing company and there was a problem with only the administrator being assigned to the database and not the other authroized users.  however, I am now getting an error at line 20 as per snippet.  The error is -->

com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: SELECT command denied to user 'cloudmoc_vanavah'@'65.48.246.67' for table 'sohead'
May be users have to be allowed access to individual tables.  I would now have to verify this with them.
ResultSet rs = stmt.executeQuery("select * from java_mysql.sohead");

Open in new window

0
 
for_yanCommented:
yes, I gues they need to say

grant select on java_mysql.sohead to public

or to your user in particular or

grant all on java_mysql.sohead to Your_user_name

at least that is how it is done in oracle
0
 
Vanavah EdwardsAuthor Commented:
I don't understand.  Write the code for this line.
0
 
Vanavah EdwardsAuthor Commented:
I am getting the same error.  Here isi tjhe report from the hosters: --->

No, when a user is assigned to a database you should be able to operate without any problems with it. I have assigned the user with all privileges however please note that the SUPER privilege is missing in the configuration of your account due to security reasons.
I have re-set the privileges for your database user. Please try executing your query again.

0
 
for_yanCommented:
well, send them the error,  it explicitly says that you don't have permissions to select from the table


try to do it wiethout java_mysql, just select

select * from sohead

who created this table sohead ?
0
 
Vanavah EdwardsAuthor Commented:
Problem solved. It now works perfect.  I removed part of the line you had in -- java_mysql -  from the line -- ResultSet rs = stmt.executeQuery("select * from java_mysql.sohead"); to -- ResultSet rs = stmt.executeQuery("select * from sohead"); -- and it worked.  Was all this syntax -- java_mysql.test_table -- in your code the name of the db because I just replaced the test_table portion.
0
 
for_yanCommented:
Good!
Now, I guess you should be able to insert into that table and select  from it
0
 
Vanavah EdwardsAuthor Commented:
I thank you so much.  I am going and get some sleep.  I am closing off.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.