SELECT answer FROM forum WHERE level = 'newbie'


Last Updated:

  1. beedge

    beedge New Member This Topic's Starter

    Joined:
    Jul 20, 2010
    Messages:
    2
    Likes Received:
    0
    Hey all.

    My background is as a php developer. I Learnt Java in college but that was all of 10 years ago and I have gotten a tad rusty since then,

    Im trying to get my head around android and I have been doing ok until now... but.....

    I am trying to execute an sql query to the sqlite database,

    in PHP no problem, $sql = "select from table where `username` = '$username' and `password` = '$password'

    however I am stumped trying to achieve this android.

    what I have is:

    Cursor cur = db.query("escar_users", null, orgWhere, null, null, null, null);

    but its the where clause that has gotten me stuck

    my string orgWhere = "username = "+sUserName+" AND password = "+sPassWord

    where sUsername and sPassWord are input variables captured from a login screen., but it crashes every time.

    please. am I doing something obviously wrong here?

    Thanks in advance, beedge.

     

    Advertisement
  2. android2dev

    android2dev Active Member

    Joined:
    Jul 15, 2010
    Messages:
    32
    Likes Received:
    4
    Although I have no idea why the crash is occurring since I cannot see the stack trace. When searching a varchar type field you should have single quotes (') around the value.
    This should provide you with a valid WHERE clause.

    string orgWhere = "WHERE username = '" + sUserName + "' AND password = '" + sPassWord + "'";

    (notice the single quotes before the double quotes where necessary).
     
  3. cp1

    cp1 Well-Known Member

    Joined:
    Apr 30, 2010
    Messages:
    375
    Likes Received:
    35
    Check the DDMS output to see why it is crashing (click DDMS in the upper right hand corner of Eclipse, or if it doesn't show, click the little box with the + sign and pick DDMS).

    In general you should use the where arguments parameter instead of concatenating your sUsername and sPassword into your where string. If you have special characters (which the password probably does) it will do bad things when searching the database.

    I don't know if you can have the columns option be null either, that should contain the columns you want to return

    instead try:

    String columns[] = {whatever_your_column_name(s)_are};
    String args[] = {sUserName, sPassWord};
    String orgWhere = "username=? AND password=?"
    Cursor cur = db.query("escar_users", columns, orgWhere, args, null, null, null);
     
  4. jason_borne

    jason_borne New Member

    Joined:
    Apr 20, 2011
    Messages:
    1
    Likes Received:
    0
    Hi,

    I am also stuck with the same problem but still cannot find the correct solution neither of them is working for me.

    Need some help asap.

    Thanks,
    Ishan
     
  5. BobPaul

    BobPaul Well-Known Member

    Joined:
    Aug 2, 2010
    Messages:
    166
    Likes Received:
    10
    Why don't you just use a select statement in Java like you are doing in PHP?

    I admit I am a SQLite newbie but with decades of relational DB experience I find
    it very cumbersome to go with the API calling convention that does not
    use SELECT, INSERT and UPDATE.
     
  6. RazzleFnDazzle

    RazzleFnDazzle Well-Known Member

    Joined:
    Sep 30, 2010
    Messages:
    123
    Likes Received:
    15
    What android2dev said is correct. You need to quote it. If you want to bypass the need to quote then you should use the following code in the android framework

    String[] param = new String[]{sUserName, sPassWord};
    Cursor cur = db.query("escar_users", null, "username = ? AND password = ?", param , null, null, null);
     
  7. JordiDroid

    JordiDroid Active Member

    Joined:
    Apr 20, 2011
    Messages:
    38
    Likes Received:
    2
    To get rid of these issues, you can consider using an ORM like www.ormlite.com
     
  8. BobPaul

    BobPaul Well-Known Member

    Joined:
    Aug 2, 2010
    Messages:
    166
    Likes Received:
    10
    Why?

    Why pollute something as simple and elegant as SQL with Java crap?

    The op'ers problem is not using pure SQL but rather the crap of SQLite, no?
     

Share This Page

Loading...