Page 1 of 3 123 LastLast
Results 1 to 20 of 57
  1. #1
    Join Date
    Nov 2010
    Posts
    87
    Rep Power
    0

    Default Please help - SQLite + Eclipse - Translate program

    Hi,

    I'm hoping I can get some advice with this. I have an SQLite DB that contains 2 tables in the following form...

    _______________________
    SMS ENGLISH

    2u2 To you too
    2nite Tonight
    4eva Forever
    _______________________

    I am using the JDBC driver and my database is called 'smsdb'. I have tested that the database connects by running the following...

    Java Code:
    import java.sql.Connection;  
     import java.sql.DriverManager;  
     import java.sql.ResultSet;  
     import java.sql.Statement;  
     
     public class ConnectSQLite 
     {  
      public static void main(String[] args) 
      {  
         Connection connection = null;  
         ResultSet resultSet = null;  
         Statement statement = null;  
    
         try 
         {  
             Class.forName("org.sqlite.JDBC");  
             connection = DriverManager.getConnection("jdbc:sqlite:C:\\smsdb.sqlite");  
             statement = connection.createStatement();  
             resultSet = statement  
                     .executeQuery("SELECT English FROM sms");  
             while (resultSet.next()) 
             {  
                 System.out.println("English translation:"  
                         + resultSet.getString("English"));  
             }  
         } 
         catch (Exception e) 
         {  
             e.printStackTrace();  
         }
         finally 
         {  
             try 
             {  
                 resultSet.close();  
                 statement.close();  
                 connection.close();  
             } 
             catch (Exception e) 
             {  
                 e.printStackTrace();  
             }  
         }  
      }
    }
    This works and prints the desired result just to test it.

    I want a user to be able to enter a sentence of SMS form text. Then I want the program to check each word of that sentence against the database and return a translated sentence so that the result is as close to proper English as possible but I am inexperienced in this area, especially using SQLite. I am unsure of how and where to enter the code that allows a user to enter the initial SMS text message and also what method I use to translate it using the database.

    Please could anyone advise on how to achieve this.

    Many thanks for help rendered

  2. #2
    Join Date
    Nov 2010
    Posts
    87
    Rep Power
    0

    Default Re: Please help - SQLite + Eclipse - Translate program

    I have found this code on the forum and have slightly altered it to apply to my program...

    Java Code:
    public String option4 (String user)
    {
    StringBuffer sb = new StringBuffer ();
    try
    {
    boolean data = false;
    Statement stmt = conn.createStatement ();
    ResultSet b = stmt.executeQuery ("SELECT English FROM sms WHERE ID =(SELECT ID FROM SMS WHERE sms like '" + user + "')");
    
    while (b.next ())
    {
    data = true;
    String afri = b.getString ("English");
    sb.append (English);
    }
    //stmt.close();
    
    if (data == false)
    {
    new InformationUI ().setVisible (true);
    }
    }
    catch (Exception e)
    {
    System.out.println ("Error in option 3: " + e.toString ());
    }
    return sb.toString ();
    }
    Where this code refers to '+ User +' does that mean he has a separate class designed solely for the user to enter text? I think the above code will only translate and return 1 word and really I want my program to translate a whole sentence/message... does that render this code useless to me?

    Thanks

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

    Default Re: Please help - SQLite + Eclipse - Translate program

    I think you might be running before you can walk.
    Do you understand SQL and how databases work?

    Once you know that then start small...get your code to translate one piece of text speak.
    Once you have that then you canexpand that to a sentence, by breaking the sentence into tokens and translating each one using the code you wrote for translating the single piece.

  4. #4
    Join Date
    Nov 2010
    Posts
    87
    Rep Power
    0

    Default Re: Please help - SQLite + Eclipse - Translate program

    Thanks for your advice Tolls,

    I do understand how SQL databases work, I have done a fair amount with mySQL just never with SQLite but there doesn't seem to be a great deal of difference so far, it was just a case of finally getting the JDBC driver to work and adjust classpaths etc.

    Does that code I posted in my previous post look like the sort of 'small step' you refer to? I'm not sure where to place this code in my project to try it... should it be part of the main class (confused as it is not 'public class..' but 'public string...' instead.

    Thanks for any further assistance

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

    Default Re: Please help - SQLite + Eclipse - Translate program

    If you know SQL, then you know that you'd need (at the most basic) a "SELECT translation FROM sms WHERE txtspk = ?"
    Then use a PreparedStatement to bind the particular bit of txtspk you are trying to translate.

    That is step 1.

  6. #6
    Join Date
    Nov 2010
    Posts
    87
    Rep Power
    0

    Default Re: Please help - SQLite + Eclipse - Translate program

    Hi Tolls, I have been working on this. I have attached my code so far... I am getting a few errors that I can't seem to address.
    Java Code:
    import java.sql.Connection;  
     import java.sql.DriverManager;  
    import java.sql.PreparedStatement;
     import java.sql.ResultSet;  
    import java.sql.Statement;  
     
    public class PreparedStatementParameter {
      
      public static void main(String[] args) 
      {  
         Connection connection = null;  
         ResultSet resultSet = null;  
         Statement statement = null;  
    
         try 
         {  
             Class.forName("org.sqlite.JDBC");  
             connection = DriverManager.getConnection("jdbc:sqlite:C:\\smsdb.sqlite");  
          // PreparedStatement for SELECT statement with one parameter
             PreparedStatement sta = connection.prepareStatement(
       	"SELECT English FROM sms WHERE SMS = ?");
    
    
       // Execute the PreparedStatement as a query
             ResultSet res = sta.executeQuery();
    
       // Get value out of the ResultSet
             res.next();
             String Translation = res.getString("Translation");
             System.out.println("English translation:" 
                     + resultSet.getString("English"));
    
       // Close ResultSet and PreparedStatement
             res.close();
             sta.close();
    
             connection.close();        
           } catch (Exception e) {
             System.err.println("Exception: "+e.getMessage());
             e.printStackTrace();
           }
         }
       }
    13 Statement statement = null; (the local variable statement is never used)

    29 String Translation = res.getString("Translation"); (the local variable Translation is never read)

    31 + resultSet.getString("English")); (Null pointer access: the variable resultSet can only be null at this location)

    I am trying to follow your advice, I knew it wouldn't get there all in one go!
    Last edited by Atia of the julii; 12-02-2011 at 01:09 PM.

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

    Default Re: Please help - SQLite + Eclipse - Translate program

    Statement should be a PreparedStatement, which you should then be using...
    You need to bind the real value you are searching on (that '?' there) using one of the set??? methods in PreparedStatement, in this case setString. Before executing the query.

    Before trying to access any data in the result set make sure something has been returned...next() returns a boolean that tells you whether it has found anything.

  8. #8
    Join Date
    Nov 2010
    Posts
    87
    Rep Power
    0

    Default Re: Please help - SQLite + Eclipse - Translate program

    Working on this now, thanks

  9. #9
    Join Date
    Nov 2010
    Posts
    87
    Rep Power
    0

    Default Re: Please help - SQLite + Eclipse - Translate program

    Im trying to get a better understanding of this from this example I found...

    PreparedStatement preparedStatement =
    connection.prepareStatement(
    "Select favoritefood from favoritefoods where catname = ?");
    preparedStatement.setString(1, "Cappuccino");

    In the place of (1, "Cappuccino"); ...what could I put in there to apply to mine? Or would I simply leave mine as (); ?

  10. #10
    Join Date
    Nov 2010
    Posts
    87
    Rep Power
    0

    Default Re: Please help - SQLite + Eclipse - Translate program

    This is currently how my code looks with errors such as 'preparedStatement cannot be resolved' on line 22

    Java Code:
    import java.sql.Connection;  
    import java.sql.DriverManager;  
    import java.sql.PreparedStatement;
    import java.sql.ResultSet;    
    
    public class PreparedStatementParameter {
      
      public static void main(String[] args) 
      {  
         Connection connection = null;  
         ResultSet resultSet = null;  
         PreparedStatement sta = null;
    
    
         try 
         {  
             Class.forName("org.sqlite.JDBC");  
             connection = DriverManager.getConnection("jdbc:sqlite:C:\\smsdb.sqlite");  
          // PreparedStatement for SELECT statement with one parameter
             PreparedStatement Sta = connection.prepareStatement(
       	"SELECT English FROM sms WHERE SMS = ?");
             preparedStatement.setString();
    
       // Execute the PreparedStatement as a query
             preparedStatement.executeQuery();
    
             
             ResultSet resultSet = preparedStatement.executeQuery();
             
             res.next();
             String Translation = res.getString("Translation");
             System.out.println("English translation:" 
                     + resultSet.getString("English"));
    
       // Close ResultSet and PreparedStatement
             res.close();
             sta.close();
    
             connection.close();        
           } catch (Exception e) {
             System.err.println("Exception: "+e.getMessage());
             e.printStackTrace();
           }
         }
       }

  11. #11
    Join Date
    Nov 2010
    Posts
    87
    Rep Power
    0

    Default Re: Please help - SQLite + Eclipse - Translate program

    PreparedStatement preparedStatement =
    connection.prepareStatement(
    "Select English from sms where SMS = ?");
    preparedStatement.setString(1, "I have a comment");

    This would apply to mine if I was entering the first database entry I have where the SMS is '!' and the English translation is 'I have a comment'

  12. #12
    Join Date
    Nov 2010
    Posts
    87
    Rep Power
    0

    Default Re: Please help - SQLite + Eclipse - Translate program

    Or would this be more appropraite?

    preparedStatementName.setString(
    intOfPositionToSet, stringToSet);

  13. #13
    Join Date
    Nov 2010
    Posts
    87
    Rep Power
    0

    Default Re: Please help - SQLite + Eclipse - Translate program

    Sorry for my rapid posting... i've attempted to put all of the above into practice in the following more coherent code but I now have one remaining error and I'm sure I've messed something up! I think it's looking better though.

    Java Code:
    import java.sql.Connection;
    import java.sql.DriverManager;
    import java.sql.PreparedStatement;
    import java.sql.ResultSet;
    import java.sql.SQLException;
     
    public class PreparedStatementParameter {
        public static void main(String[] args) {
            Connection conn = null;
            PreparedStatement prepStmt = null;
            ResultSet rs = null;
            try {
                Class.forName("org.sqlite.JDBC");
                Connection connection = DriverManager.getConnection("jdbc:sqlite:C:\\smsdb.sqlite");
                conn = DriverManager.getConnection(null);
                String sqlStmt = "SELECT English FROM sms WHERE SMS = ?";
                System.out.println("SQL Statement" + sqlStmt);
                prepStmt = conn.prepareStatement(sqlStmt);
                System.out.println("Prepared Statement" + prepStmt.toString());
     
    // I'm sure i've messed things up more from here
     
                prepStmt.setString (1, "I have a comment");  
                System.out.println("Prepared Statement" + prepStmt.toString());
                rs = prepStmt.executeQuery();
                while (rs.next()) {
                    String id = rs.getString("id");
                    String English = rs.getString("English");
                    System.out.println("Translation: " + id + ", Proper English: " + English ++ );
                }
            } catch (Exception e) {
                e.printStackTrace();
            } finally {
                try {
                    if (rs != null)
                        rs.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
                try {
                    if (prepStmt != null)
                        prepStmt.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
                try {
                    if (conn != null)
                        conn.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
    }
    ERROR...

    Line 29 - English Highlighted... Type mismatch cannot convert from String to int
    Last edited by Atia of the julii; 12-02-2011 at 03:23 PM.

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

    Default Re: Please help - SQLite + Eclipse - Translate program

    OK, up to your comment is great, and the handling of closing your resource is great.

    setString() on PreparedStatement (see the link to the API I gave earlier) needs an index (ie the position of the '?' in the SQL string) and a String, which is the value to go in there. So setString(1, <whatever bit of txtspk you want to translate>) in your case.

    getString() on a ResultSet needs an index or name for the field you are reading. In your case either getString(1) or getString("English").

    As to what you pass into the setString for your query, pick a single sms entry (ie "2nite"). That should test it works.

  15. #15
    Join Date
    Nov 2010
    Posts
    87
    Rep Power
    0

    Default Re: Please help - SQLite + Eclipse - Translate program

    Thanks Tolls, I really appreciate your guidance with this.

    Just to clarify on your last post... are lines 23 and 28 (in the most recent code i've posted) already set to this and correctly done? Thinking I was maybe editing the post and changing the code as you were writing your last post and that it is actually now in the correct form already as far as your comments go? Sorry if it still needs changing.

    As to what you pass into the setString for your query, pick a single sms entry (ie "2nite")
    Is this just a case of altering the word I chose to input in LINE 23... prepStmt.setString (1, "I have a comment");

    I've run the program with no errors and this is the output: -

    SQL StatementSELECT English FROM sms WHERE SMS = ?
    Prepared Statementorg.sqlite.PrepStmt@33dff3a2
    Prepared Statementorg.sqlite.PrepStmt@33dff3a2

    Hmm.. what have I done haha!?

    Thanks :)
    Last edited by Atia of the julii; 12-02-2011 at 04:05 PM.

  16. #16
    Join Date
    Nov 2010
    Posts
    87
    Rep Power
    0

    Default Re: Please help - SQLite + Eclipse - Translate program

    This is what I get when I try to run the program changing the setString on line 23 to (1, "2nite");

    In my database the word 2nite is on line 18.. also I have set the 'SMS' column of my database to be the primary key. Starting to think it's the actual database settings that may be a problem? It is a very simple database so hopefully if the problem lies here it won't need much alteration.

    SQL StatementSELECT English FROM sms WHERE SMS = ?
    Prepared Statementorg.sqlite.PrepStmt@50a9ae05
    Prepared Statementorg.sqlite.PrepStmt@50a9ae05
    java.sql.SQLException: no such column: 'id'
    at org.sqlite.RS.findColumn(RS.java:116)
    at org.sqlite.RS.getString(RS.java:247)
    at PreparedStatementParameter.main(PreparedStatementP arameter.java:26)

  17. #17
    Join Date
    Nov 2010
    Posts
    87
    Rep Power
    0

    Default Re: Please help - SQLite + Eclipse - Translate program

    Database captures as seen in Firefox DBMS plug-in :)

    Please help - SQLite + Eclipse - Translate program-dbpic.jpg
    Please help - SQLite + Eclipse - Translate program-structure.jpg

  18. #18
    Join Date
    Nov 2010
    Posts
    87
    Rep Power
    0

    Default Re: Please help - SQLite + Eclipse - Translate program

    Is it because I don't technically have a column 'id' in my database even though it looks as though I do from my Firefox screen capture?

  19. #19
    Join Date
    Nov 2010
    Posts
    87
    Rep Power
    0

    Default Re: Please help - SQLite + Eclipse - Translate program

    OK... so I have now recreated the database to include an auto-incremented 'id' column that I have now set as the primary key with type INTEGER.
    The SMS column is type VARCHAR
    The English column is type VARCHAR

    I have also renamed the DB smsdb1 as it was a bit confusing before with sms and SMS.

    The problem is that when I run the program it is still telling me that 'id' column doesn't exist, even though I have now created it and have the correct pathway in the code for the newly created smsdb1.sqlite

    This is the output when I run...

    Java Code:
    SQL StatementSELECT English FROM smsdb1 WHERE SMS = ?
    Prepared Statementorg.sqlite.PrepStmt@199a0c7c
    Prepared Statementorg.sqlite.PrepStmt@199a0c7c
    java.sql.SQLException: no such column: 'id'
    	at org.sqlite.RS.findColumn(RS.java:116)
    	at org.sqlite.RS.getString(RS.java:247)
    	at PreparedStatementParameter.main(PreparedStatementParameter.java:26)

    Java Code:
    import java.sql.Connection;
    import java.sql.DriverManager;
    import java.sql.PreparedStatement;
    import java.sql.ResultSet;
    import java.sql.SQLException;
     
    public class PreparedStatementParameter {
        public static void main(String[] args) {
            Connection conn = null;
            PreparedStatement prepStmt = null;
            ResultSet rs = null;
            try {
                Class.forName("org.sqlite.JDBC");
                conn = DriverManager.getConnection("jdbc:sqlite:C:\\smsdb1.sqlite");  
                String sqlStmt = "SELECT English FROM smsdb1 WHERE SMS = ?";
                System.out.println("SQL Statement" + sqlStmt);
                prepStmt = conn.prepareStatement(sqlStmt);
                System.out.println("Prepared Statement" + prepStmt.toString());
     
    // I'm sure i've messed things up more from here
     
                prepStmt.setString (1, "2nite");  
                System.out.println("Prepared Statement" + prepStmt.toString());
                rs = prepStmt.executeQuery();
                while (rs.next()) {
                    String id = rs.getString("id");
                    String English = rs.getString("English");
                    System.out.println("Translation: " + id + ", Proper English: " + English );
                }
            } catch (Exception e) {
                e.printStackTrace();
            } finally {
                try {
                    if (rs != null)
                        rs.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
                try {
                    if (prepStmt != null)
                        prepStmt.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
                try {
                    if (conn != null)
                        conn.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
    }
    Last edited by Atia of the julii; 12-02-2011 at 05:19 PM.

  20. #20
    Join Date
    Nov 2010
    Posts
    87
    Rep Power
    0

Page 1 of 3 123 LastLast

Similar Threads

  1. application translate to an applet
    By gladwda in forum New To Java
    Replies: 3
    Last Post: 10-25-2011, 10:58 PM
  2. Translate from C# to Java
    By Squezee in forum Java 2D
    Replies: 2
    Last Post: 02-07-2011, 06:07 PM
  3. How to translate english to hindi
    By nitin_sinha in forum Advanced Java
    Replies: 2
    Last Post: 12-20-2010, 09:44 PM
  4. Can translate?
    By workmason in forum New To Java
    Replies: 3
    Last Post: 10-22-2010, 09:11 AM
  5. Translate from C to java
    By ighor10 in forum New To Java
    Replies: 6
    Last Post: 03-27-2010, 09:24 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
  •