Please help - SQLite + Eclipse - Translate program

**Atia of the julii** · 12-02-2011, 08:08 PM

Hi Tolls,

I hope you didn't read all of those posts above! Sorry if you did! I have temporary success! After wrestling with everything for hours I finally changed 'id' to 'English' in the right place and I can now input any term into the string and it will translate it. Now I have to carry on and work out how to do the same thing with sentences/whole messages so i'll carry on but I'm definitely feeling a sense of accomplishment and not just failure now. Thanks so much. I hope you don't mind helping to show me where i'm going wrong as I continue.

It would be nice to also be able to enter the sentence/message with the program prompting the user to enter the text so i'll look into this also.

RESULT...

Translation: Mom

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 = ?";
            prepStmt = conn.prepareStatement(sqlStmt);
           
 
            prepStmt.setString (1, "303");  
            rs = prepStmt.executeQuery();
            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** · 12-02-2011, 09:03 PM

I think i'll stop for this evening as my eyes are hurting :P

My last question for today... would I have to create a whole new string tokenizer class or would I be integrating this code into this existing class?

AlphaSupport · 12-03-2011, 09:25 AM

Originally Posted by Atia of the julii

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

Java Code:

...

            String sqlStmt = "SELECT English FROM smsdb1 WHERE SMS = ?";
.....

This is a question that i can easily answer. In your SELECT statement you are only selecting the column "English", hence the "id" column is never returned and can not be used.

Either use a "SELECT *" or "SELECT id, English" if you want to use the id column.

**Atia of the julii** · 12-03-2011, 02:14 PM

Yeah thanks for the tip Alpha, it's all good now anyway, it still gives me the same result I need which is the translation of whatever word I input into the string. I just need to expand upon it now.

**Tolls** · 12-05-2011, 01:10 PM

And, back at work...:)

Your query is:
"SELECT English FROM smsdb1 WHERE SMS = ?"
SO there is no id column being returned in the query...which is why your getString("id") call is complaining. You don't need that anyway as you just want the English translation for the txtspk. So get rid of that line (rs.getString("id");).

**Atia of the julii** · 12-05-2011, 03:29 PM

Hey :)

The way I currently have that line is...

String id = rs.getString("English");

I just changed it from 'id' to 'English' and it works. I tried removing it altogether as you suggested but then I start getting errors so I thought maybe it was OK to leave it as it works... unless it will stop me from my goal of translating whole sentences etc using the tokenizer method.

Speaking of which, I'm a bit confused about where to begin on incorporating this into what I have so far. Will I be adding code to this class or starting a new one for example? I also wondered this for including a prompt/text box so that the user can enter text from the command line instead of having to enter the message into the actual code itself. Would I need a separate class for this or will everything end up in this one big class?

Thanks Tolls :)

**Tolls** · 12-05-2011, 04:45 PM

Ah, missed page 2!

This is just a method at the moment, which will probably form part of your database translation bit.
You'll now need something to break up a String message into tokens, each of which can be translated by calling a method like this (call it translateToken or something like that) for each token.

**Atia of the julii** · 01-07-2012, 04:58 PM

Hi! I've been working on other things for a while but I've returned to working on this again. In my database I have...

SMS English
b be
4 for
4 fore
4 four

I want to be able to enter 'b4' and the program returns all possible possibilities. E.g. it will return 'befour' 'before' 'befor' but I am at a loss at the moment. I am having trouble working out how to use the string tokenizer to do this. When I simply enter 'b4' nothing actually returns even though I also have 'b4' = 'before' also in the database. Any help appreciated once again :)

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 = ?";
            prepStmt = conn.prepareStatement(sqlStmt);
           
 
            prepStmt.setString (1, "PPL");  
            rs = prepStmt.executeQuery();
            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-07-2012, 05:14 PM

I have added the code

Java Code:

StringTokenizer st = new StringTokenizer("this is a test");
        while (st.hasMoreTokens()) {
            System.out.println(st.nextToken());
        }

The console now returns the following... but I need to get that tokenizer to work so that when I input 'b4' it will offer me all the different combinations of 'before' 'befour' 'befor'

this
is
a
test
Translation: for
Translation: fore
Translation: four

Not sure how to properly integrate this so that it will work how I described in the previous post :S

Java Code:

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.StringTokenizer;
 
public class PreparedStatementParameter {
    public static void main(String[] args) {
        Connection conn = null;
        PreparedStatement prepStmt = null;
        ResultSet rs = null;
        
        StringTokenizer st = new StringTokenizer("this is a test");
        while (st.hasMoreTokens()) {
            System.out.println(st.nextToken());
        }
        
        
        try {
            Class.forName("org.sqlite.JDBC");
            conn = DriverManager.getConnection("jdbc:sqlite:C:\\smsdb1.sqlite");  
            String sqlStmt = "SELECT English FROM smsdb1 WHERE SMS = ?";
            prepStmt = conn.prepareStatement(sqlStmt);
           
 
            prepStmt.setString  (1, "4");  
            rs = prepStmt.executeQuery();
            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-09-2012, 03:00 PM

Please can an admin re-open this!

**Tolls** · 01-09-2012, 03:37 PM

I'm not sure how you'll manage this in the case of your 'b4' example.
Surely that's a single token that maps to 'before' in your db?
From what you've typed, though, you want to split it into a 'b' and a '4' and then merge those together?

That strikes me as fraught with problems...you'll have to define the rules of how you split these things up because I can't see them.
How will you identify which words to split in the following sentence:
This is a sentence with b4 in it.
?

If it's just working on identifiable words that's easy (which is what I thought this was going to be), but breaking up the individual words into characters?

**Atia of the julii** · 01-09-2012, 05:12 PM

Hi Tolls, thanks for your reply. I am going to ignore the acronyms in my database and only focus on things like homophones that I can realistically 'model'. At the moment I plan on just being able to put in things like 'b4' instead of whole sentences. I have added all possibilities for 'b' and '4' to my DB so that all possible candidates or combinations can be returned... 'befor' 'before' 'befour' etc. As long as I can match one of the possibilities to a real English word as defined on WordNet.com this will be sufficient. For all other SMS like phonological entries such as 'NME' = 'Enemy' I will eventually look at incorporating a Java spell checker called Jazzy that suggests phonological alternatives etc but don't worry I won't trouble you with that as I know someone who has experience with that part.

So for the moment, as far as the tokenizer I just need it to be able to split the 'b' and '4'. Once that is working I can get on with entering all possibilities for other homophones such as 'W8' 'D8' 'L8'. Does this make the problem simpler at this stage?

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

So...define how you split 'b4' into 'b' and '4' and don't split 'hello' into 'h', 'e', 'l','l' and 'o'.
Once you can do that then you might be able to get somewhere.

**Atia of the julii** · 01-10-2012, 03:45 PM

Ok, I've decided to use '.' as a delimiter to breakdown whatever homophones etc I want translated so 'b4' will be input as 'b.4'. I have swapped out the stringTokenizer code I put in for the split code below and I'll show the result so far...

Java Code:

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.StringTokenizer;
 
public class PreparedStatementParameter {
    public static void main(String[] args) {
        Connection conn = null;
        PreparedStatement prepStmt = null;
        ResultSet rs = null;
        
        String smsWord = "b.4”";

        	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");  
            String sqlStmt = "SELECT English FROM projectdb WHERE SMS = ?";
            prepStmt = conn.prepareStatement(sqlStmt);
           
 
            prepStmt.setString  (1, "4");  
            rs = prepStmt.executeQuery();
            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();
            }
        }
    }
}

OUTPUT

b
4”
Translation: for
Translation: fore
Translation: four

I'm not sure how to tie it together so that

Java Code:

prepStmt.setString  (1, "4");

this line will take its information straight from the split string result and search the database for that. Again I suppose it would all look much nicer if there was a prompt like 'User please enter SMS' and it then could do this and just print the possible results like 'before' 'befor' 'befour'. I have a tendency to take what should be an easy task like that and go and mess up all of my code though so I'm unsure if it's wise for me to do that.

**Tolls** · 01-10-2012, 04:38 PM

I still don't see why you don't just have a list of translations...:)

**Atia of the julii** · 01-10-2012, 06:54 PM

Ah it's because I am attempting to 'model', if I just do direct translations i'm not really 'modelling' the SMS language. For example, if I have 'a' as 'a', 'b' as 'be' and '8' as 'ate' and but no direct translation listed for 'abate' my code will output all possibilities of a.b.8 and if I can match any of these possibilities to an official word as on WordNet dictionary, I can suggest it as a possible translation, even if the result yields more than one possible match.

**Atia of the julii** · 01-11-2012, 03:51 AM

I've spent hours on this tonight, trying to make use of Multiple Result Sets to solve the problem... to no avail. Most of the coverage I come across concerned with Multiple RS is concerned with UPDATE statements which isn't very helpful :(

I've been playing around with many different versions of this sort of example but none have worked...

Java Code:

PreparedStatement prepStmt = con.prepareStatement(
"UPDATE DEPT SET MGRNO=? WHERE DEPTNO=?"); 1
prepStmt.setString(1,mgrnum1); 2a
prepStmt.setString(2,deptnum1);
prepStmt.addBatch(); 2b
prepStmt.setString(1,mgrnum2);
prepStmt.setString(2,deptnum2);
prepStmt.addBatch();
int [] numUpdates=prepStmt.executeBatch(); 3
for (int i=0; i < numUpdates.length; i++) { 4a
if (numUpdates[i] == SUCCESS_NO_INFO)
System.out.println("Execution " + i +
": unknown number of rows updated");
else
System.out.println("Execution " + i +
"successful: " numUpdates[i] + " rows updated");
}
con.commit(); 4b
} catch(BatchUpdateException b) {
/

**Tolls** · 01-11-2012, 09:42 AM

You could probably do it as a single SQL statement.
Well, one that you build anyway.

Off the top of my head (and I really can't guarantee effectiveness):

Java Code:

SELECT a.English, b.English...etc (build this up based on the number of tokens)
FROM projectdb a JOIN
     projectdb b ON a.SMS <> b.SMS JOIN
     projectdb c ...etc etc (build up this bit based on number of tokens)
WHERE a.SMS = ?
   AND b.SMS = ?
etc etc (build up again)

That will give you every combination of your tokens in the order a, b, c so long as a is joined to b and b is joined to c.
Actually, I'm not sure about the '<>' clause. If two of the same token are allowed then that is pointless.

As I say, this is off the top of my head and I've probably made a mistake somewhere, but that would be my concept.

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

Hey Tolls,

Thanks for this suggestion. I'm going to search the net for a similar example of code if there is one as you're right... it is confusing me a little haha.

**Atia of the julii** · 01-11-2012, 04:17 PM

I'm not having much luck with this... if I took it back to the simplistic idea that I just want to be able to query multiple rows and have their 'English' column results returned in the console (not joined together just available to see, I will connect them manually) is that a lot simpler to achieve?