Please help - SQLite + Eclipse - Translate program

[Atia of the julii]

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

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

[Atia of the julii]

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

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

[Tolls]

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.

[Atia of the julii]

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

[Tolls]

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.

[Atia of the julii]

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.

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!

[Tolls]

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.

[Atia of the julii]

Working on this now, thanks

[Atia of the julii]

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

[Atia of the julii]

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

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

[Atia of the julii]

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'

[Atia of the julii]

Or would this be more appropraite?

preparedStatementName.setString(
intOfPositionToSet, stringToSet);

[Atia of the julii]

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.

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

[Tolls]

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.

[Atia of the julii]

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 :)

[Atia of the julii]

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)

[Atia of the julii]

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

[Atia of the julii]

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?

[Atia of the julii]

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

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)

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

[Atia of the julii]