Page 3 of 3 FirstFirst 123
Results 41 to 57 of 57
  1. #41
    Tolls is offline Moderator
    Join Date
    Apr 2009
    Posts
    11,863
    Rep Power
    19

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

    OK, say you wanted to search for 'b' and '4':
    Java Code:
    SELECT SMS, English
    FROM projectdb
    WHERE SMS IN ('b', '4')
    That'll give you all the entries for those two SMS "sounds", which you could cycle through and presumably stick each SMS part in a new List<String>.

    To have that as a PreparedStatement you'll want to build it up (this assumes individualk characters for the tokens):
    Java Code:
    StringBuilder sb = new StringBuilder();
    sb.append("SELECT SMS, English FROM projectdb WHERE SMS IN (");
    int bindMarkers = yourString.length();
    // Add a bind marker for each character you want to search on
    for (int i  0; i < bindMarkers; i++) {
        if (i > 0) {
            sb.append(",");
        }
        sb.append(?);
    }
    sb.append(")");
    PreparedStatement ps = conn.preparesEtc(sb.toString());
    // Now need to set each of the previous binds.
    for (int i = 0; i < yourString.length(); i++) {
        ps.setString(i+1, yourString.getCharAt(i)); // I think this'll work with char.
    }
    ResultSet rs = ps.execute();
    // Now go throught the result set, splitting out the different characters results.
    The above is cobbled together, so I fully expect typos.

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

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

    Thanks Tolls,

    It feels like I'm almost there haha. I've been trying to iron out the errors so that I can run and test and i'm just down to 2... Does the first error mean that it won't work on Char? I've tried the suggested quick fixes in eclipse but they don't resolve anything. For the first error it suggests I change to 'setLong...() or setNString()...' but that doesn't help. The 2nd error suggests change type 'rs' to 'boolean' but again this seems to make things worse :S

    Thank you for taking the time to help me with this, hopefully it's all nearly working. :)

    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;
            
            String smsWord = "ne.1";
    
            	String[] smsWordSplits = smsWord.split("\\.");
    
            	for(String smsWord1: smsWordSplits){
            	System.out.println(smsWord1);
            	}
            
            
            try {
                Class.forName("org.sqlite.JDBC");
                conn = DriverManager.getConnection("jdbc:sqlite:C:\\projectdb.sqlite"); 
                
                
                StringBuilder sb = new StringBuilder();
                sb.append("SELECT SMS, English FROM projectdb WHERE SMS IN (");
                int bindMarkers = smsWord.length();
                // Add a bind marker for each character you want to search on
                for (int i = 0; i < bindMarkers; i++) {
                    if (i > 0) {
                        sb.append(",");
                    }
                    sb.append(smsWord);
                }
                sb.append(")");
                PreparedStatement ps = conn.prepareStatement(sb.toString());
                // Now need to set each of the previous binds.
                for (int i = 0; i < smsWord.length(); i++) {
                    ps.setString(i+1, smsWord.charAt(i)); // I think this'll work with char. //the method setString (int, string) in the type prepared statement is not applicable for the arguments (int, char)
                }
                ResultSet rs = ps.execute();  //TYPE MISMATCH : CANNOT CONVERT FROM BOOLEAN TO ResultSet
                // Now go through the result set, splitting out the different characters results.
                
                while (rs.next()) {
                	String id = rs.getString("English");
                    System.out.println("Translation: " +id+ "" );
                }
            } 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();
                }
            }
        }
    }

  3. #43
    Join Date
    Nov 2010
    Posts
    87
    Rep Power
    0

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

    It didn't post the whole error line at line 42 in my code tag for some reason... it's...

    Line 42: the method setString (int, String ) in the type prepared statement is not applicable for the arguments (int, char )

    Line 44 : Type mismatch : cannot convert from boolean to ResultSet

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

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

    Now just the 1 error.... although I did use the quick fix 'ps.setLong' on line 42 which no doubt will be wrong as long is just for numbers and not characters :S

    CONSOLE OUTPUT:

    Exception in thread "main" java.lang.Error: Unresolved compilation problem:
    Type mismatch: cannot convert from boolean to ResultSet

    at PreparedStatementParameter.main(PreparedStatementP arameter.java:44)



    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;
            
            String smsWord = "ne.1";
    
            	String[] smsWordSplits = smsWord.split("\\.");
    
            	for(String smsWord1: smsWordSplits){
            	System.out.println(smsWord1);
            	}
            
            
            try {
                Class.forName("org.sqlite.JDBC");
                conn = DriverManager.getConnection("jdbc:sqlite:C:\\projectdb.sqlite"); 
                
                
                StringBuilder sb = new StringBuilder();
                sb.append("SELECT SMS, English FROM projectdb WHERE SMS IN (");
                int bindMarkers = smsWord.length();
                // Add a bind marker for each character you want to search on
                for (int i = 0; i < bindMarkers; i++) {
                    if (i > 0) {
                        sb.append(",");
                    }
                    sb.append(smsWord);
                }
                sb.append(")");
                PreparedStatement ps = conn.prepareStatement(sb.toString());
                // Now need to set each of the previous binds.
                for (int i = 0; i < smsWord.length(); i++) {
                    ps.setLong(i+1, smsWord.charAt(i)); // I think this'll work with char.
                }
                rs = ps.execute();
                // Now go through the result set, splitting out the different characters results.
                
                while (rs.next()) {
                	String id = rs.getString("English");
                    System.out.println("Translation: " +id+ "" );
                }
            } 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; 01-11-2012 at 07:06 PM.

  5. #45
    Tolls is offline Moderator
    Join Date
    Apr 2009
    Posts
    11,863
    Rep Power
    19

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

    Look at the API for PreparedStatement and see what you should be doing for the execute().
    I said I was working from memory.

    The other one is incorrect.
    Don't rely on Eclipse to give sensible choices...you want setString since the field you are searching on is a VARCHAR, so you'll need to change the char to a String somehow. It's probable the charAt() should be another method. Again, look at the API, for String in this case.

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

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

    Quote Originally Posted by Tolls View Post
    Look at the API for PreparedStatement and see what you should be doing for the execute().
    I said I was working from memory.
    I knew you were working from memory :) I didn't take it as a given. I checked out the API, (I'm glad you reminded me to look at it, I'm forgetting it's there to help far too often).


    ResultSet executeQuery()
    throws SQLException
    Executes the SQL query in this PreparedStatement object and returns the ResultSet object generated by the query.

    Returns:
    a ResultSet object that contains the data produced by the query; never null

    Throws:
    SQLException - if a database access error occurs; this method is called on a closed PreparedStatement or the SQL statement does not return a ResultSet object
    Therefore I inserted the line 'rs = ps.getResultSet() ;'

    The other one is incorrect.
    Don't rely on Eclipse to give sensible choices...you want setString since the field you are searching on is a VARCHAR, so you'll need to change the char to a String somehow. It's probable the charAt() should be another method. Again, look at the API, for String in this case.
    I referred to the API but I couldn't find anything useful other than to change 'ChatAt' to 'toString()); ' There aren't any errors now but when I run, I get the following in the output console :

    Java Code:
    ne
    1
    java.sql.SQLException: unrecognized token: ".1??"
    	at org.sqlite.DB.throwex(DB.java:288)
    	at org.sqlite.NestedDB.prepare(NestedDB.java:115)
    	at org.sqlite.DB.prepare(DB.java:114)
    	at org.sqlite.PrepStmt.<init>(PrepStmt.java:37)
    	at org.sqlite.Conn.prepareStatement(Conn.java:231)
    	at org.sqlite.Conn.prepareStatement(Conn.java:224)
    	at org.sqlite.Conn.prepareStatement(Conn.java:213)
    	at PreparedStatementParameter.main(PreparedStatementParameter.java:39)
    I've been trying to find examples across the net of this style of code... unfortunately I can only seem to find examples where by there are fixed defined items, I.e. not using i > i++ for bindMarkers such as this example, even though it is code to export and not query...

    Java Code:
     String expsql = 
                "call SYSCS_UTIL.SYSCS_EXPORT_TABLE (? , ? , ? , ?, ? , ?)";
            PreparedStatement ps = prepareStatement(expsql);
            ps.setString(1, schemaName);
            ps.setString(2, tableName);
            ps.setString(3, fileName);
            ps.setString(4, colDel);
            ps.setString(5, charDel);
            ps.setString(6, codeset);
            ps.execute();
            ps.close();
        }

  7. #47
    Join Date
    Nov 2010
    Posts
    87
    Rep Power
    0

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

    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;
            
            String smsWord = "ne.1";
    
            	String[] smsWordSplits = smsWord.split("\\.");
    
            	for(String smsWord1: smsWordSplits){
            	System.out.println(smsWord1);
            	}
            
            
            try {
                Class.forName("org.sqlite.JDBC");
                conn = DriverManager.getConnection("jdbc:sqlite:C:\\projectdb.sqlite"); 
                
                
                StringBuilder sb = new StringBuilder();
                sb.append("SELECT SMS, English FROM projectdb WHERE SMS IN (");
                int bindMarkers = smsWord.length();
               
                for (int i = 0; i < bindMarkers; i++) {
                    if (i > 0) {
                        sb.append(",");
                    }
                    sb.append(smsWord);
                }
                sb.append(")");
                PreparedStatement ps = conn.prepareStatement(sb.toString());
                
                for (int i = 0; i < smsWord.length(); i++) {
                	ps.setString(i+1, smsWord.toString()); 
                }
                rs = ps.executeQuery();
                rs = ps.getResultSet() ;
                
                
                while (rs.next()) {
                	String id = rs.getString("English");
                    System.out.println("Translation: " +id+ "" );
                }
            } 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();
                }
            }
        }
    }

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

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

    I did find this as an answer to someone's question on how to convert a string to char on yahoo answers... I don't fully understand it though.

    String input = <<retrieve your input here!>>;
    char[] characters = input.toCharArray();

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

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

    Use substring() rather than toChar(), using i as the start and 1 as the length.
    Also use executeQuery rather than execute in my example, which (as you show in your quote from the API) returns a ResultSet.

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

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

    I now get no errors but the console returns nothing at all when the program is run. I'm not sure where to actually input the search criteria though as if I put anything other than a column name in 'String smsWord = "";' it will give me an error saying whatever I have input isn't a column name.

    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;
            
            String smsWord = "";
    
            	String[] smsWordSplits = smsWord.split("\\.");
    
            	for(String smsWord1: smsWordSplits){
            	System.out.println(smsWord1);
            	}
            
            
            try {
                Class.forName("org.sqlite.JDBC");
                conn = DriverManager.getConnection("jdbc:sqlite:C:\\projectdb.sqlite"); 
                
                
                StringBuilder sb = new StringBuilder();
                sb.append("SELECT SMS, English FROM projectdb WHERE SMS IN (");
                int bindMarkers = smsWord.length();
               
                for (int i = 0; i < bindMarkers; i++) {
                    if (i > 0) {
                        sb.append(",");
                    }
                    sb.append(smsWord);
                }
                sb.append(")");
                PreparedStatement ps = conn.prepareStatement(sb.toString());
                
                for (int i = 0; i < smsWord.length(); i++) {
                	ps.setString(i+1, smsWord.substring(i+1)); 
                }
                rs = ps.executeQuery();
                rs = ps.getResultSet() ;
                
                
                while (rs.next()) {
                	String id = rs.getString("English");
                    System.out.println("Translation: " +id+ "" );
                }
            } 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();
                }
            }
        }
    }

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

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

    Look at my code again:
    Java Code:
    sb.append(?);
    not
    Java Code:
    sb.append(smsWord);
    The '?' is the bind placeholder that your later setString() calls set the real value of.

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

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

    I took the 'smsWord' out and tried to replace with the '?' but it gives me the error...

    'the method append (object) in the type string builder is not applicable for the arguments ()'
    'syntax error on token "?" delete this token'

    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;
            
            String smsWord = "";
    
            	String[] smsWordSplits = smsWord.split("\\.");
    
            	for(String smsWord1: smsWordSplits){
            	System.out.println(smsWord1);
            	}
            
            
            try {
                Class.forName("org.sqlite.JDBC");
                conn = DriverManager.getConnection("jdbc:sqlite:C:\\projectdb.sqlite"); 
                
                
                StringBuilder sb = new StringBuilder();
                sb.append("SELECT SMS, English FROM projectdb WHERE SMS IN (");
                int bindMarkers = smsWord.length();
               
                for (int i = 0; i < bindMarkers; i++) {
                    if (i > 0) {
                        sb.append(",");
                    }
                    sb.append();
                }
                sb.append(")");
                PreparedStatement ps = conn.prepareStatement(sb.toString());
                
                conn.setAutoCommit(false);
                
                for (int i = 0; i < smsWord.length(); i++) {
                	ps.setString(i+1, smsWord.substring(i+1)); 
                }
                rs = ps.executeQuery();
                rs = ps.getResultSet() ;
                
                
                while (rs.next()) {
                	String id = rs.getString("English");
                    System.out.println("Translation: " +id+ "" );
                }
            } 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();
                }
            }
        }
    }

  13. #53
    Tolls is offline Moderator
    Join Date
    Apr 2009
    Posts
    11,863
    Rep Power
    19

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

    You have no question mark there, so that's not the code giving that error.

  14. #54
    Join Date
    Nov 2010
    Posts
    87
    Rep Power
    0

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

    That's what it says when the question mark is there

  15. #55
    Tolls is offline Moderator
    Join Date
    Apr 2009
    Posts
    11,863
    Rep Power
    19

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

    Oh right, and looking back at my code I missed the quotes around it.
    Let's call it a test, rather than me being incompetent...

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

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

    No worries, I should have explained that I took it out as it wasn't working by itself before I posted the code, my fault. Although I have included the quotations now and unfortunately it still isn't giving any output. Is this probably dude to my 'system.out.println' code as I haven't adjusted it at all really since altering the rest of the code to its present state?

    As I was getting frustrated with this I went ahead with breaking my current database down into a more concise one that now only contains translations rules that my code should be putting together, some real words should come out in the input and some incorrect words made of incorrect combinations should also be produced. I will then look for and just make a note of how many came out with at least one correct translation... E.g.

    "ID" "rule" "precedent" "translation"

    "1","8","m8","ate"
    "2","8","str8","aight"
    "3","8","w8","ait"
    "4","8","Sk8er","at"
    "5","8","GR8","eat"
    "6","1","every1","Won"
    "7","1","1","One"
    "8","2","2u2","Too"
    "9","2","2","Two"
    "10","2","2u","to"
    "11","4","c4n","For"
    "12","4","4","Four"

    So if I search the word "gr8" I want a console output to give me E.g.

    "gr8" (always keeping the original search term in the results) "grate" "grait" "graight" etc etc

    -----------------

    As it's been a nightmare getting the code to work since this thread began I don't know whether to just start afresh, I doubt there's much point though as even though I've simplified the DB to its current form, it's all still the same code objective.

  17. #57
    Tolls is offline Moderator
    Join Date
    Apr 2009
    Posts
    11,863
    Rep Power
    19

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

    You don't need this:
    rs = ps.getResultSet();
    As the previous line gets the result set.

    Also, print out the text in the StringBuilder, just in case.

    Don't scrap the code, as that is pretty close to the sort of thing you'll need, even if you change the structure of the table.

Page 3 of 3 FirstFirst 123

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
  •