Gears GWT API on Android behaving differently?

[Evan Ruff]

Hey guys,

I've got a pretty heavy gears app the works great in IE, Firefox and
Chrome; however, I'm getting some inconsistencies when running it on
Android using Gears GWT API 1.2.0. Whenever I seem to run ANY query, I
get a "Wrong number of SQL parameters" DatabaseException. Everything
works perfectly in the other browsers.

I'm debugging back to the client and my info is:
Exception Message: Wrong number of SQL parameters.
Method: selectUserByUserId
Parameters: [ 1 ]

Here is the method:

private static final String SELECT_USER = "SELECT user_id, sessionId,
name, email FROM user WHERE user_id = ?";

public User selectUserByUserId( long userId )
{
final String[] params = new String[] { Long.toString( userId ) };
try
{
ResultSet rs = db.execute( SELECT_USER, params );
for ( int i = 0; rs.isValidRow(); ++i, rs.next() )
{
User user = new User();
user.setId( rs.getFieldAsLong( 0 ) );
user.setCurrentSessionId( rs.getFieldAsString( 1 ) );
user.setName( rs.getFieldAsString( 2 ) );
return user;
}
rs.close();
}
catch ( DatabaseException e )
{
debugDBException( "selectUserByUserId", params, e );
}
return null;
}

The debugDBException method just rolls everything up an submits it to
the server.

Has anone else had this sort of issue working with Gears on Android?
I've tried the test 1.2 Gears LocalDB Test and it seems to fine. It
looks like I'm trying to do a VERY similar thing and I'm really
perplexed as to why it won't work.

Thanks!

Evan

PS. Should I cross post this to the gears group?

[Evan Ruff]

Oh also, I should add, that I checked out the DB structure using the
little DB explorer guy that is available in the vanilla gears distro.

The SQLite DB looks identical as the one on the other platforms, just
without data ('cause of all the SQL errors, I assume)

Thanks!

E

[Evan Ruff]

Ok I seriously think I'm about to lose it here. This is driving me
nuts!!!

For some reason, I cannot get the following class to run in Android
without throwing a "Wrong Number of SQL Parameters" Exception. It
throws the error on my G1 (Android 1.1) and on my Cupcake Emulator. It
works without fail on every other platform I've tried it on. Also, it
seems to work find if I just copy/paste the insert user method into
the DatabaseDemo app from the Gears Sample application. Does anyone
have any idea what could be happening here? I'm using GWT 1.5.3 and
Gears 1.2.0. I feel like I've tried every possible permutation of this
code and still have not been successful!

Could someone maybe give this thing an old run and test with the
Android Emulator?

Here is the stand alone class:

public class TestGears implements EntryPoint
{
/*--------------------------------------------
| C O N S T A N T S |
============================================*/

private static final String DB_NAME = "moose_db";
private static final String INSERT_USER = "INSERT INTO userTable
(user_id, sessionId, name, email) values ( ?, ?, ?, ? )";
private static final String CREATE_USER_TABLE = "CREATE TABLE IF NOT
EXISTS userTable ( user_id TEXT NOT NULL, sessionId TEXT, name TEXT,
email TEXT )";

/*--------------------------------------------
| I N S T A N C E V A R I A B L E S |
============================================*/

private Database db;

/*--------------------------------------------
| C O N S T R U C T O R S |
============================================*/

/*--------------------------------------------
| P U B L I C A P I M E T H O D S |
============================================*/

public void onModuleLoad()
{
User testUser = new User();
testUser.id = 100L;
testUser.name = "Forced Manual" ;
testUser.currentSessionId = "FAKESESSION";

this.db = Factory.getInstance().createDatabase();
this.db.open( DB_NAME );
try
{
db.execute( "DROP TABLE IF EXISTS userTable" );
this.db.execute( CREATE_USER_TABLE );

}
catch ( DatabaseException e )
{

Window.alert( e.getMessage() );
}

insertUser( testUser );

}

/*--------------------------------------------
| N O N - P U B L I C M E T H O D S |
============================================*/

public void insertUser( User theUser )
{
final String[] params = new String[] { Long.toString( theUser.id ) ,
theUser.currentSessionId , theUser.name , "" };
try
{
db.execute( INSERT_USER, params );

}
catch ( DatabaseException e )
{

String parameterString = new String();
parameterString += "[ ";
for ( String param : params )
{
parameterString += param + ", ";
}
parameterString = parameterString.substring( 0,
parameterString.length() - 2 );
parameterString += " ]";

Window.alert( "insertUser: " + e.getMessage() + " - " +
parameterString );
}
}

/*--------------------------------------------
| I N L I N E C L A S S E S |
============================================*/

public class User
{
public Long id;
public String name = new String();
public String currentSessionId = new String();

}
}



On May 5, 2:24 pm, Evan Ruff <evan.r...@gmail.com> wrote:
> Oh also, I should add, that I checked out the DB structure using the
> little DB explorer guy that is available in the vanilla gears distro.
>
> The SQLite DB looks identical as the one on the other platforms, just
> without data ('cause of all the SQL errors, I assume)
>
> Thanks!
>
> E
>

[Eric Ayers]

I looked at your code and didn't see anything obviously wrong.  I searched on some of the Gears groups and found a similar report, but no answer. 

 

It is worth nothing that the version of Gears on android is different (older) than the version you are getting when you install Gears on a desktop browser.  Can we rule out the GWT bindings?  Could you write a simple program that reproduces (or fails to reproduce) the problem in straight JavaScript?

-Eric.

--
Eric Z. Ayers - GWT Team - Atlanta, GA USA
http://code.google.com/webtoolkit/

[Eric Ayers]

If it turns out to be reproducible outside of GWT, I was going to point you at the android-developers google group.

http://groups.google.com/group/android-developers

[Evan Ruff]

Hey Eric,

I'm pretty much terrified of Javascript.

I'm a little hesitant to make any determination whatsoever as to the
cause of the problem. When I run the GWT Gears Sample DatabaseDemo
project, it works as expected. I can even replace all of the DB
specifics with the internals of my tables/queries and it seems to
work! It's just so freakin' frustrating.

RememberTheMilk and the mobile GMail seem to make use of Gears on
Android without issue, so I really don't know where to go from here.
Any other suggestions?

Here's the replaced DatabaseDemo code, fwiw:

public class Gears implements EntryPoint
{
private static final int NUM_SAVED_ROWS = 3;
private static final int NUM_DATA_TABLE_COLUMNS = 3;

private final Button addButton = new Button( "Add" );
private final Button clearButton = new Button( "Clear Database" );
private Database db;
private final TextBox input = new TextBox();
private final FlexTable dataTable = new FlexTable();

public void onModuleLoad()
{
VerticalPanel outerPanel = new VerticalPanel();
outerPanel.setSpacing( 10 );
outerPanel.getElement().getStyle().setPropertyPx( "margin", 15 );

HorizontalPanel textAndButtonsPanel = new HorizontalPanel();
textAndButtonsPanel.add( new Label( "Enter a Phrase: " ) );
textAndButtonsPanel.add( input );
textAndButtonsPanel.add( addButton );
textAndButtonsPanel.add( clearButton );
outerPanel.add( textAndButtonsPanel );
outerPanel.add( new Label( "Last 3 Entries:" ) );
outerPanel.add( dataTable );

for ( int i = 0; i <= NUM_SAVED_ROWS; ++i )
{
dataTable.insertRow( i );
for ( int j = 0; j < NUM_DATA_TABLE_COLUMNS; j++ )
{
dataTable.addCell( i );
}
}
dataTable.setWidget( 0, 0, new HTML( "<b>Id</b>" ) );
dataTable.setWidget( 0, 1, new HTML( "<b>Phrase</b>" ) );
dataTable.setWidget( 0, 2, new HTML( "<b>Timestamp</b>" ) );

// Create the database if it doesn't exist.
try
{
db = Factory.getInstance().createDatabase();
db.open( "database-demo" );

db.execute( "DROP TABLE IF EXISTS user" );
db.execute( "CREATE TABLE IF NOT EXISTS user ( user_id TEXT NOT
NULL, sessionId TEXT, name TEXT, email TEXT )" );

}
catch ( DatabaseException e )
{

RootPanel.get( "demo" ).add( new HTML( "Error opening or creating
database: <font color=\"red\">" + e.toString() + "</font>" ) );
// Fatal error. Do not build the interface.
return;
}

input.addKeyboardListener( new KeyboardListenerAdapter()
{
@Override
public void onKeyDown( Widget sender, char keyCode, int modifiers )
{
if (keyCode == KeyboardListener.KEY_ENTER)
{
insertPhrase();
}
}
} );

addButton.addClickListener( new ClickListener()
{
public void onClick( Widget sender )
{
insertPhrase();
}
} );

clearButton.addClickListener( new ClickListener()
{
public void onClick( Widget sender )
{
clearPhrases();
displayRecentPhrases();
}
} );

RootPanel.get( "demo" ).add( outerPanel );
displayRecentPhrases();
}

/**
* Remove all phrases from the database.
*/
private void clearPhrases()
{
try
{
db.execute( "DELETE FROM Phrases" );

}
catch ( DatabaseException e )
{

Window.alert( e.toString() );
}
}

/**
* Fill the labels with the phrases from the database.
*/
private void displayRecentPhrases()
{
try
{
ResultSet rs = db.execute( "SELECT name, email FROM user" );
int i;

for ( i = 1; rs.isValidRow(); ++i, rs.next() )
{
if (i <= NUM_SAVED_ROWS)
{
dataTable.setText( i, 0, i+"" );
dataTable.setText( i, 1, rs.getFieldAsString( 0 ) );
dataTable.setText( i, 2, new Date( rs.getFieldAsLong
( 1 ) ).toString() );
}
else
{
db.execute( "DELETE FROM user" );
}
}
// If a phrase has been removed, clear the label.
for ( ; i <= NUM_SAVED_ROWS; i++ )
{
for ( int j = 0; j < NUM_DATA_TABLE_COLUMNS; j++ )
{
dataTable.clearCell( i, j );

}
}
rs.close();
}
catch ( DatabaseException e )
{

Window.alert( e.toString() );
}
}

/**
* Add a new phrase to the database.
*/
private void insertPhrase()
{
try
{
this.db.execute( "INSERT INTO user (user_id, sessionId, name,
email) values ( ?, ?, ?, ? )" , new String[] { "stupid", "fake",
"shit", "balls" } );

displayRecentPhrases();
input.setText( "" );

}
catch ( DatabaseException e )
{

Window.alert( e.toString() );

}
}




On May 6, 1:47 pm, Eric Ayers <zun...@google.com> wrote:
> If it turns out to be reproducible outside of GWT, I was going to point you

> at the android-developers google group.http://groups.google.com/group/android-developers

[Evan Ruff]

Ok so something really fishy is going on with the Gears API.

I created my own "prepareSQLStatement" that converts the SQL Call and
parameter list into a string.

private String prepareSQLStatement( String sqlStatement, String[]
params ) throws DatabaseException
{
try
{
String statement = new String( sqlStatement );

for ( int i = 0; ( statement.indexOf( "?" ) > 0 ); i++ )
{
int index = statement.indexOf( '?' );
String firstPart = statement.substring( 0, index );
String secondPart = statement.substring( index + 1 );

StringBuffer sb = new StringBuffer();
sb.append( firstPart ).append( "\"" ).append( params[ i ] ).append
( "\"" ).append( secondPart );
statement = sb.toString();
}

return statement;
}
catch ( IndexOutOfBoundsException indexOut )
{
throw new DatabaseException( "Index out of bounds. SQL Parameter
Error" );
}
}

When I use this method to prepare the statement before hitting
db.execute, all of my sql statements work as expected.

Could Android implement it's string manipulation stack slightly
differently than all the other platforms? I've got a sneaky suspecion
that the RegExp that's used to parse the '?' in the SQL commands might
be failing?

Furthermore, can anyone give me an ideas as how to improve the
performance of the above method? It creates a noticable delay. Did I
read somewhere that GWT has a slow string manipulation engine?

E

[Eric Ayers]

Your best bet for android specific stuff would be to ask on the android-users group.  Here are some general observations inline:

On Thu, May 7, 2009 at 2:12 PM, Evan Ruff <evan...@gmail.com> wrote:

Ok so something really fishy is going on with the Gears API.

I created my own "prepareSQLStatement" that converts the SQL Call and
parameter list into a string.

       private String prepareSQLStatement( String sqlStatement, String[]
params ) throws DatabaseException
       {
               try
               {
                       String statement = new String( sqlStatement );

performance: There's no need to make a copy of the string here.  Strings are immutable.
 

                       for ( int i = 0; ( statement.indexOf( "?" ) > 0 ); i++ )

You probably want a while loop here:  while(statement.indexOf("?") > 0)  { ... }
 

                       {

                               int index = statement.indexOf( '?' );
                               String firstPart = statement.substring( 0, index );
                               String secondPart = statement.substring( index + 1 );

                               StringBuffer sb = new StringBuffer();

performance: Try using StringBuilder instead of StringBuffer.  http://java.sun.com/j2se/1.5.0/docs/api/java/lang/StringBuilder.html
 

                               sb.append( firstPart ).append( "\"" ).append( params[ i ] ).append
( "\"" ).append( secondPart );
                               statement = sb.toString(); 

performance: I don't know how big your SQL statement is or how many parameters you have, but you are scanning it over and over from the beginning of the string.  You really only need to analyze it from the end if the previous indexOf() line.
 

[Vitali Lovich]

Javascript & Java have different regular expressions, so if you are running your stuff on desktop browsers in Hosted Mode but compiled mode in Android, then you might be running into that problem.

A performance improvement should be:

String statement = new String( sqlStatement );

StringBuffer sb = new StringBuffer(statement.length());
int index;
int previous = 0;
int i = 0;

while ((index = statement.indexOf('?')) != -1)
{
   String firstPart = statement.substring( previous, index );
   sb.append (firstPart).append('"').append(params[i++]).append('"');
   previous = index + 1;
}

return sb.toString();

or try

String statement = new String( sqlStatement );

String [] parts = statement.split("[?]");
StringBuffer sb = new StringBuffer(statement.length + parts.length * AVERAGE_PARAM_LENGTH);

for (int i = 0; i < parts.length; i++)
{
   sb.append(parts[i]).append('"').append(params[i]).append('"')
}

AVERAGE_PARAM_LENGTH can be anything, but you want the resultant expression to be as close as possible to the actual final length as possible, without going under (assuming this portion of code is your hotpath - otherwise, you won't notice the difference).

On Thu, May 7, 2009 at 2:12 PM, Evan Ruff <evan...@gmail.com> wrote:

[Vitali Lovich]

Oh yeah - didn't notice that you were using StringBuffer.  StringBuilder is the appropriate one.  I had assumed that sqlStatement was perhaps an array or something - if it's another string, then yes, you do not need to create a copy like that since they are immutable.

The while loop instead of for loop shouldn't save him anything in this case.

[Evan Ruff]

StringBuilder! that was what I was looking for.

Thanks Eric, I'll take this over to the gears-users group.

E

FWIW, this is still really slow, but it does work!

On May 7, 2:42 pm, Eric Ayers <zun...@google.com> wrote:
> Your best bet for android specific stuff would be to ask on the
> android-users group.  Here are some general observations inline:
>

> On Thu, May 7, 2009 at 2:12 PM, Evan Ruff <evan.r...@gmail.com> wrote:
>
> > Ok so something really fishy is going on with the Gears API.
>
> > I created my own "prepareSQLStatement" that converts the SQL Call and
> > parameter list into a string.
>
> >        private String prepareSQLStatement( String sqlStatement, String[]
> > params ) throws DatabaseException
> >        {
> >                try
> >                {
> >                        String statement = new String( sqlStatement );
>
> performance: There's no need to make a copy of the string here.  Strings are
> immutable.
>
>
>
> >                        for ( int i = 0; ( statement.indexOf( "?" ) > 0 );
> > i++ )
>
> You probably want a while loop here:  while(statement.indexOf("?") > 0)  {
> ... }
>
>
>
> >                        {
> >                                int index = statement.indexOf( '?' );
> >                                String firstPart = statement.substring( 0,
> > index );
> >                                String secondPart = statement.substring(
> > index + 1 );
>
> >                                StringBuffer sb = new StringBuffer();
>

> performance: Try using StringBuilder instead of StringBuffer.http://java.sun.com/j2se/1.5.0/docs/api/java/lang/StringBuilder.html

> ...
>
> read more »

[Evan Ruff]

Vitali,

Thanks for your response!

That RegExp for the ? was giving me trouble, as it wouldn't match.

I also tried [\\?] without success as well. Any idea on how to find
that guy?

Thanks,

E

> ...
>
> read more »

[Vitali Lovich]

What's the code for the regexp that you are trying & what is the source string that you are matching against.

[Evan Ruff]

Vitali,

I was just trying to match '?' in the SQL statement, such as:

INSERT into users ( name, city ) VALUES( ?, ? )

so I can mash up my own prepare SQL statement.

Also, I can run the test on the android phone to see if that might be
the problem.

E

On May 7, 4:36 pm, Vitali Lovich <vlov...@gmail.com> wrote:
> What's the code for the regexp that you are trying & what is the source
> string that you are matching against.
>

> ...
>
> read more »

[Vitali Lovich]

What is the exact Java code snippet that you are using?

[Evan Ruff]

I was going with the ol:

myString.replace( "[?]", newString );

but that would not find the ?, so I went with a "\\?", also
unsuccessfully.

E

On May 8, 12:00 am, Vitali Lovich <vlov...@gmail.com> wrote:
> What is the exact Java code snippet that you are using?
>

> ...
>
> read more »

[Vitali Lovich]

Have you read the Javadoc?  replace replaces string literals.  You need myString.replaceAll("[?]", newString); or myString.replace("?", newString).  In any case, it's not what you want since it replaces all instances of ? with the same string.  Try this:

Matcher m = Pattern.compile("[?]").matcher(myString);
int i = 0;
while (!m.hitEnd())
{
  m.replaceFirst('"' + params[i++] + '"');
}

You could use String.replaceFirst, but this should be better performing.  Otherwise, you have to do:

String newString;
int i = 0;
while (!(newString = myString.replaceFirst("[?]", '"' + params[i++] + '"')).equals(myString))
{
   myString = newString;
}

but that might be slower than even the split method I gave earlier.

And if this code is really slow, there's a faster JSNI way because Javascript has a faster way for literal regexps & I don't believe the compiler makes that optimization yet.

In any case, I would really recommend trying to figure out why your SQL stuff doesn't work first, because that is the core of the problem.  All of this stuff is just hacky workarounds.

You really should write the equivalent little javascript snippet & see if that works.  If it doesn't, then post to the android list because it's a problem with your code.

[Evan Ruff]

Eric,

I was able to make contact with the gentleman that posted in the gears
group with the same problem. It turns out that he is also using the
GWT Gears API as well. I've asked him for more details and he did
suggest he had a work around, but I imagine it is something similar to
what I've cooked up.

At this point, I'm really thinking there is a problem with GWT /
Gears / Android compatibility. I will follow up when I get more
information from Pedro.

E

[Evan Ruff]

Vitali,

GWT does not seem too excited about Matcher and bombs out completely.
I don't believe it's compatible client side, as it says
java.util.regex cannot be imported.

I've confirmed that Gears on Android does not support sentences with
another user.

I misspoke when I posted my code snippet, I was using replaceFirst. As
I stated before that Regexp ([?]) does not appear to match ? as
replaceFirst does not ever replace the ?. "\\?" does not seem to work
in replaceFirst either; however, "\\?" does work with .split.

So, I think the two solutions I've got so far are:

for ( int i = 0; ( statement.indexOf( "?" ) > 0 ); i++ )
{
int index = statement.indexOf( '?' );
String firstPart = statement.substring( 0, index );
String secondPart = statement.substring( index + 1 );

StringBuilder sb = new StringBuilder();

sb.append( firstPart ).append( "\"" ).append( params[ i ] ).append
( "\"" ).append( secondPart );
statement = sb.toString();
}
return statement;

OR
StringBuilder sb = new StringBuilder();
String[] splits = statement.split( "\\?" );
int j = 0;

sb.append( splits[ 0 ] );
for( int i = 1; i < splits.length; i++ )
{
sb.append( '"' ).append( params[ j ] ).append( '"' ).append( splits
[ i ] );
j++;
}
if ( j < params.length )
{
sb.append( '"' ).append( params[ j ] ).append( '"' );
j++;
}
if ( j != params.length )
{
throw new IndexOutOfBoundsException();
}
return sb.toString();

With neither method being particularly quick. I suspect the second
method will give better general performance.

E

On May 8, 10:46 am, Vitali Lovich <vlov...@gmail.com> wrote:
> Have you read the Javadoc?  replace replaces string literals.  You need
> myString.replaceAll("[?]", newString); or myString.replace("?", newString).

> In any case, it's not what you want since it replaces *all* instances of ?

> with the same string.  Try this:
>
> Matcher m = Pattern.compile("[?]").matcher(myString);
> int i = 0;
> while (!m.hitEnd())
> {
>   m.replaceFirst('"' + params[i++] + '"');
>
> }
>
> You could use String.replaceFirst, but this should be better performing.
> Otherwise, you have to do:
>
> String newString;
> int i = 0;
> while (!(newString = myString.replaceFirst("[?]", '"' + params[i++] +
> '"')).equals(myString))
> {
>    myString = newString;
>
> }
>
> but that might be slower than even the split method I gave earlier.
>
> And if this code is really slow, there's a faster JSNI way because
> Javascript has a faster way for literal regexps & I don't believe the
> compiler makes that optimization yet.
>
> In any case, I would really recommend trying to figure out why your SQL
> stuff doesn't work first, because that is the core of the problem.  All of
> this stuff is just hacky workarounds.
>
> You really should write the equivalent little javascript snippet & see if
> that works.  If it doesn't, then post to the android list because it's a
> problem with your code.
>

> ...
>
> read more »

[Vitali Lovich]

yeah... not really sure what you're trying to do with the split algorithm - it's unnecessarily messy.  try just using JSNI - I'm not sure why you're having problems with the Java regexp.  Have you tried 1.6?  It seems like this is a bug in GWT.

private static native String fixQuery(String query, String value) /*--{
  return query.replace(/[?]/, value);
} --*/;

for (int i = 0; i < params.length; i++) {
   String fixed = fixQuery(statement, params[i]);
   assert (!statement.equals(fixed));
   statement = fixed;
}

Again, I still think you are doing something wrong with the Gears API if you have to use this hack (I haven't used gears.  Have you tried writing the Javascript equivalent?

You could also try to use JSNI - maybe the GWT gears API has some problems:

private void nativeGears(Database db, String sql, long userid, String sessionid, String name, String email)
{
  db.execute(sql, [userid, sessionid, name, email]);
}

try {
  tryGears(db, statement, theUser.id, theUser.currentSessionId , theUser.name , "");
} catch (Exception e) {
   e.printStackTrace();
}

If this works, then there's probably something wrong with the Java code (or maybe a bug with GWT).  Try upgrading to 1.6 if that doesn't work.  If it still doesn't work, write the Javascript equivalent.

Also, you've got a logic problem with selectUserByUserId algorithm

First of all, the for loop should be  for (; rs.isValidRow(); rs.next() ).  The i counter is redundant.  This brings me to the next point - the for loop is redundant in your algorithm & you will never execute rs.close unless there is nothing in your result set.  Maybe this is what you were trying for?

public User selectUserByUserId( long userId )
{
       final String[] params = new String[] { Long.toString( userId ) };

       User user = null;
       ResultSet rs = null;
       try
       {

               rs = db.execute( SELECT_USER, params );

               if (rs.isValidRow())
               {

                    user = new User();
                    user.setId( rs.getFieldAsLong( 0 ) );
                    user.setCurrentSessionId( rs.getFieldAsString( 1 ) );
                    user.setName( rs.getFieldAsString( 2 ) );

                    if (!GWT.isScript()) {
                      rs.next();
                      assert (!rs.isValidRow());
                      user = null;

                    }
               }
       }
       catch ( DatabaseException e )
       {

               debugDBException( "selectUserByUserId", params, e );

       } finally {
            if (rs != null)
            {
               rs.close();
            }
       }
       return user;
}

By the way, there might be a problem with your attempt to manually bind your values when you insert.  Since you always append quotes around the paramters, they will always be treated as strings.  Since SQLite does not honour the column type, you may see issues appear (at the very best, it'll manifest as performance problems).