Welcome!

Welcome to the official BlackBerry Support Community Forums.

This is your resource to discuss support topics with your peers, and learn from each other.

inside custom component

Java Development

Reply
New Developer
Sultan14
Posts: 47
Registered: ‎05-11-2012
My Device: Blackberry Bold 9930

sqllite checking for values

Hi,

I'm wondering if there is a specific code that I can use to check if the sqllite database have values.

Something like

 

Select * from < > where tablename = <>; 

 

Thank you!

 

Please use plain text.
Developer
crispyoz
Posts: 223
Registered: ‎10-01-2011
My Device: Bold 9900

Re: sqllite checking for values

Like this:

 

 

URI uri = URI.create("file:///SDCard/databases/mydb/mydb.db");
db = DatabaseFactory.openOrCreate(uri);
stmt = db.createStatement("SELECT * FROM <tablename> WHERE <fieldname>=?");
stmt.prepare();
stmt.bind(1, searchtext);
Cursor crsr = stmt.getCursor();
while(crsr.next())
{
   Row row = crsr.getRow();
}
stmt.close();
db.close();

Please use plain text.
New Developer
Sultan14
Posts: 47
Registered: ‎05-11-2012
My Device: Blackberry Bold 9930

Re: sqllite checking for values

Thank you so much. In my code, I need to put this code in a different method. The first one for creating the database and the second for inserting the values. When I used your code it's giving me two errors (I changed the stmt to st)

 

1- stmt is not defined so I took it out and defined it in the class ( I hope this is right and it will work).

2- searchtext is not available and the eclipse suggestion is to use url.

 

Waiting for your help, thanks again!

Please use plain text.
Developer
crispyoz
Posts: 223
Registered: ‎10-01-2011
My Device: Bold 9900

Re: sqllite checking for values

[ Edited ]

Here are your imports:

 

 

import net.rim.device.api.database.Cursor;
import net.rim.device.api.database.Database;
import net.rim.device.api.database.DatabaseFactory;
import net.rim.device.api.database.Row;
import net.rim.device.api.database.Statement;
import net.rim.device.api.io.URI;

 

Here is the code a little enhanced so the compiler won't complain, note that  <table name > and <field name>  need to be replaced with the name of the table and field you are querying. Also  searchtext   is a string defining what you want to search for.

 

 

URI uri = URI.create("file:///SDCard/databases/mydb/mydb.db");
Database db = DatabaseFactory.openOrCreate(uri);
Statement stmt = db.createStatement("SELECT * FROM <tablename> WHERE <fieldname>=?");
stmt.prepare();
stmt.bind(1, searchtext);
Cursor crsr = stmt.getCursor();
while(crsr.next())
{
Row row = crsr.getRow();
}
stmt.close();
db.close(); }

 

 

 

 

Please use plain text.
New Developer
Sultan14
Posts: 47
Registered: ‎05-11-2012
My Device: Blackberry Bold 9930

Re: sqllite checking for values

[ Edited ]

Thank you again. I really appreciate your help.

But I want to check in the beginning weather the database have values or not. I think with your code it would look for a value after the database was filled. Sorry, maybe my question was not so clear.

 

 e.x. so I would be able to say  say in my code

 

 if (stmt ==null)

     {

    // do something }

else

   .....

}

 

Thank you so much again. And I hope you can help me with this.

 

 

Please use plain text.
Developer
crispyoz
Posts: 223
Registered: ‎10-01-2011
My Device: Bold 9900

Re: sqllite checking for values

OK first we need to get the terminology correct.  The "Database" is actually a file like in my example it is a file called /SDCard/databases/mydb/mydb.db.  Are you trying to see if that database exists already? Is that your question?

 

 

Please use plain text.
New Developer
Sultan14
Posts: 47
Registered: ‎05-11-2012
My Device: Blackberry Bold 9930

Re: sqllite checking for values

[ Edited ]

Sorry for that mistake. Thank you for correcting me.

I meant the values in the table. I already checked for the availability of the database. But now I want to check for the availability of the values. If the values is there I won't execute a specific code if not it will call a json server.

 

Should I put the code that you gave me in a method that return a Boolean?

 

Do you have any suggestions ?

 

Thank you again.

Please use plain text.
New Developer
Sultan14
Posts: 47
Registered: ‎05-11-2012
My Device: Blackberry Bold 9930

Re: sqllite checking for values

Do you think a code like that would work?

 

publicbooleancheckingForValues(){

 

if (DB!= null)

 

{

st = DB.createStatement("SELECT * FROM Managers WHERE sysId = ?");

 

st.bind(1, "sysId");

Cursor crsr =st.getCursor();

 

while(crsr.next())

{

Row row = crsr.getRow();

}

st.close();

 

DB.close();

}

if (st== null){

 

return true;

}

else{

 

return false;

 

Please use plain text.
Developer
crispyoz
Posts: 223
Registered: ‎10-01-2011
My Device: Bold 9900

Re: sqllite checking for values

[ Edited ]

Yes the code I gave you is querying the database table to see if a specific field contains a value you specified.  For example i fyou have a table named "NAMES"  containing a list of names with a single field named "USERNAME"  you would create a function like this::

 

 public boolean nameExists(String searchName) {

 

String searchtext = "hello";
URI uri;
try {
uri = URI.create("file:///SDCard/databases/mydb/mydb.db");
Database db = DatabaseFactory.openOrCreate(uri);
DatabaseFactory.exists(uri);
Statement stmt = db.createStatement("SELECT * FROM NAMES  WHERE USERNAME=?");
stmt.prepare();
stmt.bind(1, searchName);
Cursor crsr = stmt.getCursor();
if  (crsr.next()) 

return true;

else 

  return false;


stmt.close();
db.close();
} catch (Exception  e) {
 e.printStackTrace();

}

 

Because crsr.next() return fasle if no matching records are found.

 

If you are using this funciton a lot you might keep the database open and make it a static variable to avoid the load of opening the database each time yo call the function.

Please use plain text.
New Developer
Sultan14
Posts: 47
Registered: ‎05-11-2012
My Device: Blackberry Bold 9930

Re: sqllite checking for values

[ Edited ]

Cris, thank you again. Did you tried the code? It keep giving me errors. Is the return true, false in the right place? I tried it and it keep saying that it need a return value?


Sorry again for keep bothering you with my questions.

Please use plain text.