Results 1 to 6 of 6

Thread: SQLite Problem

  1. #1
    flaquitqm is offline Member
    Join Date
    Jan 2010
    Posts
    10
    Rep Power
    0

    Default SQLite Problem

    Hello everybody. I have an update problem. I don't get any error when I trying to update a table, but when I go to my data base nothing happend. This is my code, please help me to find what I'm doing wrong.

    try{
    sql = "UPDATE Segmento SET NombFormAdic = '"+ NombreForm +"' , DirFormAdic = '" +Dir+ "' where IdSegmento = " + seg.getId();
    PreparedStatement ps = TConexion.getConexion().prepareStatement(sql);
    ps.executeUpdate();

    }
    catch (Exception e) {
    // TODO: handle exception
    JOptionPane.showMessageDialog(null, e.getMessage());
    }

  2. #2
    travishein's Avatar
    travishein is offline Senior Member
    Join Date
    Sep 2009
    Location
    Canada
    Posts
    684
    Rep Power
    6

    Default

    Maybe you are not seeing any error message becase you (again,) have not put any useful log output in the exception. like does the JOptionPane even work if this is a command line app. unless, this is somehow a GUI app ?


    whoa, the whole idea of using prepared statements is to have them escape these horrible parameters for us.

    I bet a pizza the code as it was now, if one of these strings contains a single quote, then this is breaking.

    for example consider, if the value of NombreForm is h'orderve. then the query string as it is now would become:
    Java Code:
    UPDATE Segmento SET NombFormAdic = 'h'orderve' , DirFormAdic = ....."
    And clearly this is bad sql, as in this case we inadvertantly have "orderve', DorFpr,Adic ="... etc. as an out of a string fragment, which is not sql, but our data. Related, this is called sql injection, and malicious intentioned people will use this to break out of the system, consider if the value for NombreForm is somehow ever "
    Java Code:
    '; drop table Segmento;'
    So prepared statements effectively provide an API to do the special character escaping for us to prevent sql injection. I guess alternatively you could write your own utility function to do that as well, but why reinvent what is already proven and a mature standard.

    Also, be sure to close the statement after use to free up any resources.

    Putting all this together.
    Java Code:
    PreparedStatement ps = null;
    try{
      String sql = "UPDATE Segmento SET NombFormAdic = ?, DirFormAdic = ? where IdSegmento = ?";
      ps = TConexion.getConexion().prepareStatement(sql);
      ps.setString(1, NombreForm);
      ps.setString(2, Dir);
      ps.setInt(3, seg.getId());
      ps.executeUpdate();
    }
    catch (SQLException e) {
      System.err.println(e.getMessage());
      e.printStackTrace();
    } 
    finally {
      if (ps != null) {
        try {
          ps.close();
        } catch (SQLException ex) {/ * empty */ }
     }
    }

  3. #3
    flaquitqm is offline Member
    Join Date
    Jan 2010
    Posts
    10
    Rep Power
    0

    Default

    First of all, thanks for your answer. I prove your code and still doesn´t work, I don't get an exception but the table in the data base remains the same. My project is a Gui Application, thats why I was trying to figure out what was happening with a JOptionPane message. I have a class that manage the prepared statement, that really isn't my code, but when I debug my program and I copy the value of the sql String, and I excecute that sql code directly in my sqlite database it really works, and I get that row updated. That mean that anything is wrong with the sql code I send throw the prepared statement and I dont have any idea of what is happening

  4. #4
    travishein's Avatar
    travishein is offline Senior Member
    Join Date
    Sep 2009
    Location
    Canada
    Posts
    684
    Rep Power
    6

    Default

    i wonder if it is the JDBC driver you are trying to use to connect to sql llite database. in that maybe its an older version. or there is an administrative privilege or property that makes connecting to the database be read-only mode ? SQLite is an embedded database, but maybe it has some growing pains in the making of things work in java. I remember a kind of native Java wrapper thing having to set up, or maybe something about needing to go through ODBC. And any time I have had to use the Java ODBC driver, it never ever really worked well for me.

    I guess you aren't able to try connecting to a different database, I mean, from our point of view as application developers it is just a String JDBC URL setting to change, and then to add a .jar file for the database specific jdbc driver to the classpath.

    I like to use that H2 database. It is pretty quick to get set up and running to play with, its a single .jar file for the database, and the same jar file is also the jdbc driver. as far as elegant embedded databases go, that h2 is my favorite now.

    or maybe mysql is a good quick get into using database too. Like, the idea is just to get a hang of having a test database that works, and that you can have your java application connect to, where you are able to update things with sql, until you get a feel for all the use cases that you want to do. It is also a good sanity test, in that if you are able to make an application that has a screen and pushing a button causes a given database operation to happen, if it works perfectly on mysql (for example) database, then you know now if it is not working on sqlite, then its not you, or your code that is wrong, but something in the implemention or the configuraion of the sqlite database.

    Where in the past I would set up a stand alone sandbox in my workspace to have my application test itself on the local test database, and when i'm done coding, i just need to release the application, change the url connection settings, and i know the application code will function as it is supposed to,a ssuming it will connect to a different existing classical network connected database that has the same table structure. But lately, I end up building applications exclusively designed to be ran on H2 embedded database and ship it like that. for most desktop applications people don't happen to have a mysql database installed already right.

  5. #5
    Tolls is offline Moderator
    Join Date
    Apr 2009
    Posts
    12,224
    Rep Power
    20

    Default

    Since this is an update, are you sure that there is an entry in your table for the segment id you're supplying? Print out the id and double check it's correct.

  6. #6
    flaquitqm is offline Member
    Join Date
    Jan 2010
    Posts
    10
    Rep Power
    0

    Default

    Thank you travishein for you answer. I followed your advice, I downloaded H2 database engine (last stable version), but I have another problem now. I can connect my database to my project but I can't get any data from my tables. I have data on the tables and the resultSet doesn´t get it. When I get an exception it tells me that is not data available, but when I run the sql script directly in H2 console, it works and I get the data I want. Again I don't know what is happening. The code I am using, is the one that you wrote me.

Similar Threads

  1. SQLite Result Set problem
    By nikosa in forum JDBC
    Replies: 4
    Last Post: 01-25-2010, 08:53 AM
  2. Trying to get JAVA to work with SQLite
    By mark8569 in forum JDBC
    Replies: 3
    Last Post: 04-25-2009, 02:42 AM
  3. netbeans and sqlite
    By witzode in forum JDBC
    Replies: 1
    Last Post: 08-16-2008, 01:45 AM
  4. netbeans and sqlite
    By witzode in forum NetBeans
    Replies: 1
    Last Post: 07-18-2008, 09:26 AM
  5. JAVA and SQLite
    By mark8569 in forum Advanced Java
    Replies: 0
    Last Post: 03-20-2008, 07:17 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
  •