Please help - SQLite + Eclipse - Translate program

**Tolls** · 01-11-2012, 04:50 PM

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.

**Atia of the julii** · 01-11-2012, 06:41 PM

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();
            }
        }
    }
}

**Atia of the julii** · 01-11-2012, 06:45 PM

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

**Atia of the julii** · 01-11-2012, 07:03 PM

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();
            }
        }
    }
}

**Tolls** · 01-12-2012, 09:52 AM

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.

**Atia of the julii** · 01-12-2012, 11:43 PM

Originally Posted by Tolls

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();
    }

**Atia of the julii** · 01-12-2012, 11:46 PM

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();
            }
        }
    }
}

**Atia of the julii** · 01-13-2012, 12:04 AM

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();

**Tolls** · 01-13-2012, 09:32 AM

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.

**Atia of the julii** · 01-13-2012, 02:21 PM

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();
            }
        }
    }
}

**Tolls** · 01-13-2012, 02:38 PM

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.

**Atia of the julii** · 01-14-2012, 10:56 AM

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();
            }
        }
    }
}

**Tolls** · 01-16-2012, 11:13 AM

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

**Atia of the julii** · 01-16-2012, 05:22 PM

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

**Tolls** · 01-16-2012, 05:39 PM

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...

**Atia of the julii** · 01-17-2012, 12:46 AM

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.

**Tolls** · 01-17-2012, 09:31 AM

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.