Android 2.3 sqlite problem

[birledaniel]

i wrote a java application for android using v2.2 with sqlite
and its working ok
when i try my app on v2.3 its not working

i have a sqlite db in assets and its copied on device
i use dataBaseHelper

public class DataBaseHelper extends SQLiteOpenHelper {

//The Android's default system path of your application database.
private static String DB_PATH = "/data/data/org.map20.vital_citiri/databases/";
private static String DB_NAME = "Vital.db";
private SQLiteDatabase myDataBase;
private final Context myContext;

/**
* Constructor
* Takes and keeps a reference of the passed context in order to access to the application assets and resources.
* @param context
*/
public DataBaseHelper(Context context) {
super(context, DB_NAME, null, 1);
this.myContext = context;
}

/**
* Creates a empty database on the system and rewrites it with your own database.
* */
public void createDataBase() throws IOException {
boolean dbExist = checkDataBase();

if (dbExist) {
//do nothing - database already exist
} else {



//By calling this method and empty database will be created into the default system path
//of your application so we are gonna be able to overwrite that database with our database.
this.getReadableDatabase();
try {
copyDataBase();
} catch (IOException e) {
throw new Error("Error copying database");
}
}
}

/**
* Check if the database already exist to avoid re-copying the file each time you open the application.
* @return true if it exists, false if it doesn't
*/
private boolean checkDataBase() {
SQLiteDatabase checkDB = null;
try {
String myPath = DB_PATH + DB_NAME;
checkDB = SQLiteDatabase.openDatabase(myPath, null, SQLiteDatabase.OPEN_READONLY);
} catch (SQLiteException e) {
//database does't exist yet.
}
if (checkDB != null) {
checkDB.close();
}

return checkDB != null ? true : false;
}

/**
* Copies your database from your local assets-folder to the just created empty database in the
* system folder, from where it can be accessed and handled.
* This is done by transfering bytestream.
* */
private void copyDataBase() throws IOException {
//Open your local db as the input stream
InputStream myInput = myContext.getAssets().open(DB_NAME);
// Path to the just created empty db
String outFileName = DB_PATH + DB_NAME;
//Open the empty db as the output stream
OutputStream myOutput = new FileOutputStream(outFileName);
//transfer bytes from the inputfile to the outputfile
byte[] buffer = new byte[1024];
int length;
while ((length = myInput.read(buffer)) > 0) {
myOutput.write(buffer, 0, length);
}
//Close the streams
myOutput.flush();
myOutput.close();
myInput.close();
}
this is my code
googled everything and nothing

[Phyll]

Hi birledaniel,

What is it that's not working? Do you have something from log cat or something?

Thanks,

Phyll

[birledaniel]

nothing from log
everything its ok
the db is being copied
but when i try first select sql is empty
SqlDataAccessLayer sdal = new SqlDataAccessLayer(this);
try {
java.sql.ResultSet rs = sdal.ExecuteQuery("SELECT * FROM tblutilizator WHERE tblutilizator.utilizator='" + sUserName + "' AND parola='" + sPassword + "'");
if (rs != null) {
rs is allways null
i think is something about jdbc driver

[Phyll]

Daniel,

This code looks suspiciously like the code on this website:

reigndesign.com/blog/using-your-own-sqlite-database-in-android-applications/

You may find your answer there or at least this guy knows way more than I do about it. Sorry about the de-http'ed link, but it seems if I post a link, it takes longer to post the message.

Just a quick look through his code and yours I would say you have the problem two thirds of the responders did and you need "this.close();" after the "this.getReadableDatabase();". At least its not there in your code.

You know, I studied this some more and found this:

Now you can create a new instance of this DataBaseHelper class and call the createDataBase() and openDataBase() methods. Remember to change the "YOUR_PACKAGE" to your application package namespace (i.e: com.examplename.myapp) in the DB_PATH string. -Fluxa

The openDataBase and a couple other things are missing from your example code. Was that intentional? I tried the "this.close()" and the helper works fine with that in there. That's a pretty long blog and the responders had a lot of different problems.

Phyll

[birledaniel]

i tried everything there but its not working

[Phyll]

Hi Daniel,

So what is that SqlDataAccessLayer that doesn't work? I couldn't find it referenced in the Android docs. Is it something you've brought from c# or show me where it is. Because this works fine in the event example main activity I've been working with (adapted from the example I sited earlier) and in either 2.1 or 2.3.3:

    private Cursor getEvents() {
      SQLiteDatabase db = eventsData.getReadableDatabase();
      Cursor cursor = db.query(EventDataSQLHelper.TABLE,
         null, null, null, null,null, null);
      startManagingCursor(cursor);
      return cursor;
    }
    
    private void showEvents(Cursor cursor) {
      StringBuilder ret = new StringBuilder("Saved Events:\n\n");
      while (cursor.moveToNext()) {
        long id = cursor.getLong(0);
        long time = cursor.getLong(1);
        String title = cursor.getString(2);
        ret.append(id + ": " + time + ": " + title + "\n");
        }//while
      this.put.setText(ret.toString());
    }//showevents

I know this isn't a very advanced example. I would guess that the idea of your program is to start out with a data base that is already initialized with some data (like the example) and that the SqlDataAccessLayer class is supposed to get data from the data base. I'm guessing that's what isn't working and it is returning a null instead of some tool into the data. Anyway its not in your original code so I'm not sure what to make of it. You're right about one thing, I Googled all over for that and I couldn't find anything except your original question and some references to c#.

Phyll

[birledaniel]

public class SqlDataAccessLayer extends SqlDatabaseImpl {

private String DbError = "";
private Connection con = null;

public SqlDataAccessLayer() {
this.activity = null;
}

public SqlDataAccessLayer(android.content.Context context) {
super(context);
}

public SqlDataAccessLayer(android.app.Activity activity) {
super(activity);
}

public SqlDataAccessLayer(String DbDriver, String DbName, String DbUserName, String DbPassword) {
super(DbDriver, DbName, DbUserName, DbPassword);
}

public String getDbError() {
return this.DbError;
}

public Connection getConnection() {
return this.con;
}

public void OpenConnection() {
try {
Class.forName(DbDriver);
} catch (java.lang.ClassNotFoundException e) {
DbError = e.getMessage();
}

try {
DriverManager.setLoginTimeout(120);
con = DriverManager.getConnection(DbName, DbUserName, DbPassword);
} catch (java.sql.SQLException e) {
DbError = e.getMessage();
}

}

public void CloseConnection() {
try {
if (con != null) {
if (!con.isClosed()) {
con.close();
}
}
} catch (java.sql.SQLException e) {
DbError = e.getMessage();
}
}

public ResultSet ExecuteQuery(String strSQL, int resultSetType, int resultSetConcurrency) {
ResultSet rs = null;

OpenConnection();

try {
Statement stmt = con.createStatement(resultSetType, resultSetConcurrency);
rs = stmt.executeQuery(strSQL);
} catch (java.sql.SQLException e) {
DbError = e.getMessage();
CloseConnection();
} catch (java.lang.NullPointerException e) {
DbError = e.getMessage();
CloseConnection();
}

return rs;
}

public ResultSet ExecuteQuery(String strSQL) {
return ExecuteQuery(strSQL, ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
}

public String ExecuteQueryToString(String strSQL) {
String result = "";
ResultSet rs = null;

OpenConnection();

try {
Statement stmt = con.createStatement();
rs = stmt.executeQuery(strSQL);
if (rs != null) {
ResultSetMetaData rsmd = rs.getMetaData();
while (rs.next()) {
for (int i = 1; i <= rsmd.getColumnCount(); i++) {
result += rsmd.getColumnName(i) + ":";
try {
result += rs.getString(i).replaceAll("\"", "\\\\\'");
} catch (Exception ex) {
}
result += "\n";
}
}
}
} catch (java.sql.SQLException e) {
DbError = e.getMessage();
CloseConnection();

} catch (java.lang.NullPointerException e) {
DbError = e.getMessage();
CloseConnection();

}
return result;
}

public int ExecuteNonQuery(String strSQL) {
int result = 0;
DbError = "";

OpenConnection();

try {
Statement stmt = con.createStatement();
stmt.execute(strSQL);
try {
ResultSet rs = stmt.executeQuery(" SELECT last_insert_id() ");
if (rs.next()) {
result = rs.getInt(1);
}
} catch (Exception ei) {
}
stmt.close();
} catch (java.sql.SQLException e) {
DbError = e.getMessage();
} catch (java.lang.NullPointerException e) {
DbError = e.getMessage();
}

CloseConnection();
return result;
}

public void ExecuteNonQuery(String strSQL, ByteArrayInputStream parameter, int parameter_length) {
DbError = "";

OpenConnection();

try {
PreparedStatement pstmt = con.prepareStatement(strSQL);
pstmt.setBinaryStream(1, parameter, parameter_length);
pstmt.execute();
pstmt.close();
} catch (java.sql.SQLException e) {
DbError = e.getMessage();
} catch (java.lang.NullPointerException e) {
DbError = e.getMessage();
}

CloseConnection();
}

public void ExecuteNonQuery(String strSQL, Object parameter) {
DbError = "";

OpenConnection();

try {
PreparedStatement pstmt = con.prepareStatement(strSQL);
pstmt.setObject(1, parameter);
pstmt.execute();
pstmt.close();
} catch (java.sql.SQLException e) {
DbError = e.getMessage();
} catch (java.lang.NullPointerException e) {
DbError = e.getMessage();
}

CloseConnection();
}

public List<HashMap<String,String>> Results2Array(ResultSet rs) throws SQLException {
ResultSetMetaData metaData = rs.getMetaData();
int columns = metaData.getColumnCount();
List<HashMap<String,String>> list = new ArrayList<HashMap<String,String>>();
while (rs.next()) {
HashMap<String,String> record = new HashMap<String,String>();
for (int i = 1; i <= columns; i++) {
record.put(metaData.getColumnName(i), rs.getString(i));
}
list.add(record);
}
return list;
}

public ArrayList ConvertToArray(ResultSet rs) {
try {
ArrayList al = new ArrayList();
if (rs != null) {
ResultSetMetaData rsmd = rs.getMetaData();
while (rs.next()) {
ArrayList alr = new ArrayList();
for (int i = 1; i <= rsmd.getColumnCount(); i++) {
alr.add(rs.getString(i));
}
al.add(alr);
}
rs.close();
}
return al;
} catch (Exception ex) {
return null;
}
}
}

this is my sqldataacesslayer

[Phyll]

Hi Daniel,

Wow. Maybe your original question should have been, "Why doesn't my implementation of FUSE work?" This question is still diverging for me. The code in the beginning does indeed work. It's this other stuff which is hard to make work so that I can watch it not work.

It would appear that you have a third party jdbc at work here and yes you could be absolutely right that there could be something wrong with that. I would suspect not, but its hard to know.

Of course I now need this: com.fuse.storage.sql.SQLDatabaseImpl (I think) to make that code work and probably a bunch of other stuff.

I did find some references to changes that were made to sqlite that had caused problems from one version to the next so your problem could be internal to the imported code. But I did not find exactly this problem.

You probably know more about this than I do.

So what can we do?

First you should add some Logs so you can see whether there were errors even if you don't call getDbError(). Like here in the OpenConnection():

try {
Class.forName(DbDriver);
} catch (java.lang.ClassNotFoundException e) {
DbError = e.getMessage();
}

And here in the ExecuteQuery():

try {
Statement stmt = con.createStatement(resultSetType, resultSetConcurrency);
rs = stmt.executeQuery(strSQL);
} catch (java.sql.SQLException e) {
DbError = e.getMessage();
CloseConnection();
} catch (java.lang.NullPointerException e) {
DbError = e.getMessage();
CloseConnection();
}

Maybe you are looking at the error string someplace but I didn't catch it.

Since that's all you are calling it must be failing in just that small bit of code and if there are no errors than it must be in what you don't have control over. It would seem to me that in order to send back a null one of these things must fail.

Phyll

[birledaniel]

tried everything
not working
i got to this error

public void OpenConnection() {
try {
Class.forName("SQLite.JDBC Driver");
} catch (java.lang.ClassNotFoundException e) {
DbError = e.getMessage();
}

try {
DriverManager.setLoginTimeout(120);
con = DriverManager.getConnection(DbName, DbUserName, DbPassword);
} catch (java.sql.SQLException e) {
DbError = e.getMessage();
}

}

the Class.forName returnd error java.lang.ClassNotFoundException
i have added in netbeans/libraries SQLite with classpath towards sqlitejdbc-v056.jar
and its not working

edited the project.properties
and checked external.lib.dirs
added there sqlitejdbc056jar and mysqlconnector.jar
still nothing
i get error "No suitable driver"

[Phyll]

Hi Daniel,

Have you tried leaving the " Driver" off of that string? That doesn't look right.

Have a look here: xyzws.com/Javafaq/what-does-classforname-method-do/17

Phyll