Gettings data from an sql database?


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 13 of 13

Thread: Gettings data from an sql database?

  1. #1
    Join Date
    Oct 2004
    Posts
    1

    Gettings data from an sql database?

    I'm quite new to java, although i've recently started a degree in it so i know the basics.

    I'm currently making a simple database project. It works without connecting to an online sql database. Just would look really good if i could connect to something online.

    Is this at all possible to do?

    Oh yeh without pages and pages of code? I'd like to understand what i'm doing.

    Any help would be great!

  2. #2
    Join Date
    Oct 2004
    Posts
    11

    Connecting to online database

    Connecting to an online database is easy enough.

    Have you used JDBC to connect to a database at all? (I.e. a local database, rather than an 'online' database.)

    The principle for connecting to a database is the same whether the database is local or online; you simply need to specify the database URL when obtaining a connection.

    Basically, it works like this:

    First you register the database driver with the classloader using a line like:

    Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");

    This example registers the Sun Type 1 JDBC-ODBC bridge driver which is often used for development. Drivers are typically supplied by the database vendor; sometimes they're expensive and sometimes they're free. Furthermore, drivers are classified by type; Type 4 drivers are generally considered the best.

    Then you need to obtain a Connection to the database from the DriverManager using a line like this:

    Connection con = DriverManager.getConnection(dbUrl, "bob","builder");

    Here, "bob" is the userid and "builder" is the password.

    In this example, dbUrl is a String variable containing the JDBC URL. Note that the exact format of the URL depends on the database you're connecting to (e.g. DB2, SQL Server, MySQL, etc). For example, if connecting to a MySQL database, the URL will look something like:

    jdbc:mysql://hostort/dbName

    However, most drivers will allow you to specify a URL that will point to another server - hence 'online'.

    You probably already know most of this stuff, but just in case... Once you have your Connection object, you use it obtain a Statement object (or PreparedStatement or whatever). You then use this Statement object to execute your SQL query. When the query is a select statement, it will return a ResultSet (which is basically a wrapper for a database cursor). You can then simply iterate through the ResultSet row by row using a while loop.

    As requested, very little code!

    (P.S. J2EE applications would typically obtain a connection from a datasource using a JNDI lookup. This method for obtaining a connection is a lot different to the method described above.)

  3. #3
    Join Date
    Oct 2004
    Posts
    311
    Ok, got the MySQL driver somewhat working here, but got a majow problem logging in. Somehow my app add @ractoc.demon.nl at the end of my username, thus corrupting it to an unusuable one...
    Any thoughts on this one?

  4. #4
    Join Date
    Oct 2004
    Posts
    11
    If you created a user in MySQL called pleb who had access to the database server named ractoc.demon.nl, then MySQL calls this user pleb@ractoc.demon.nl.

    However, in your application, you should not need to append @ractoc.demon.nl to your user String, so just remove that bit! Provided the database URL that you pass when obtaining the Connection object references a server that the user has access to (sorry for this hideous run-on sentence!), all should be well.

  5. #5
    Join Date
    Oct 2004
    Posts
    311
    Originally posted by Dash Rendar
    If you created a user in MySQL called pleb who had access to the database server named ractoc.demon.nl, then MySQL calls this user pleb@ractoc.demon.nl.

    However, in your application, you should not need to append @ractoc.demon.nl to your user String, so just remove that bit! Provided the database URL that you pass when obtaining the Connection object references a server that the user has access to (sorry for this hideous run-on sentence!), all should be well.
    I haven't added it to my username in the connect string, it somehow got there automatically, see the code snippet below:

    Code:
    Class.forName("com.mysql.jdbc.Driver").newInstance(); 
                
    Connection conn = DriverManager.getConnection("jdbc:mysql://daforums.ractoc.com/DAForums?user=DAForums&password=DAForums");

  6. #6
    Join Date
    Oct 2004
    Posts
    11
    But this is how it is supposed to work!

    If your MySQL server is indeed called daforums.ractoc.com, then the user will be DAForums@daforums.ractoc.com. This is not a corrupt username; it is a perfectly valid one. You need to ensure that this user does exist for this host. That user needs to have appropriate permissions for the specified database on the specified host.

  7. #7
    Join Date
    Oct 2004
    Posts
    311
    I already have it working in php on my webserver. There I use daforums.ractoc.com as mySQL server url, so that should be correct. (You could try connecting to it via browser as well, it will take you to the phpMyAdmin site.) if you enter the UN/PW there you login, no prob

  8. #8
    Join Date
    Oct 2004
    Posts
    11
    What happens if you try using the postac... dbURL, rather than daforums.ractoc.com (as seen when you log into your phpMyAdmin)?

  9. #9
    Join Date
    Oct 2004
    Posts
    311
    this results in a different Exception:

    java.sql.SQLException: Unable to connect to any hosts due to exception: java.net.ConnectException: Connection refused: connect

  10. #10
    Join Date
    Oct 2004
    Posts
    11

  11. #11
    Join Date
    Oct 2004
    Posts
    311
    when I use the code stated earlier I get this exception:

    java.sql.SQLException: Invalid authorization specification message from server: "Access denied for user: 'DAForums@ractoc.demon.nl' (Using password: YES)"

  12. #12
    Join Date
    Oct 2004
    Posts
    11
    Ractoc, I think the Java is fine.

    The problem is that in MySQL, you have to explicitly grant privileges to a database and specify from what hosts a user can connect *from*.

    So, if I try and connect your server from my box, I get:

    Access denied for user: 'DAForums@whatever-my.domain.is'

    Your URL is fine. What you need to do, therefore, is grant privileges to the DAForums database.

    While not the most secure solution, this should do the trick:

    grant all privileges on DAForums.* to 'DAForums'@'%' identified by 'DAForums'

    Then run:

    flush privileges

    Using DAForums for database, user, and password makes this a little tricky to explain. However, DAForums.* means all tables in the DAForums databse. The 'DAForums'@'%' means that these privileges are allowed for a user called 'DAForums' coming from any host. (Here, the % is a wildcard. You may want to be more explicit for your testing.)

    Hope this works...

  13. #13
    Join Date
    Oct 2004
    Posts
    311
    The prob with this is that I'm not hosting the DB myself, my hosting provider is. I have a web-panel there where I can set user hosts, and I already added % to the host list of DAForums. Somehow that doesn't seem enough though.

    Ah well, I guess I will need to send an email to the support departement about it.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center