Android Question Selecting an individual record from a database

[Colin Evans]

Hi. trying to get to grips with DBUTIls and SQL etc, and I've utilised one of the examples given by Erel but I'm sadly at a lost now, whilst the code works populating the 'Spinner' from the database, when I select an item from the list I can't seem to get the syntax right for selecting the information relevant to the entry from the database

Sub FillSpinners
    Private Query As String
    Query = "SELECT DISTINCT knot FROM " & DBTableName & " ORDER BY knot ASC"
    spinner.Clear
    DBUtils.ExecuteSpinner(SQL1, Query, Null, 0, spinner)
End Sub

Sub FillText(FCall As String)
    Private Query As String
    Query = "SELECT DISTINCT info FROM " & DBTableName & " WHERE knot = " & FCall
    SQL1.ExecQuerySingleResult(Query)
    lblText.Text = Query
End Sub

Sub spnSelect_ItemClick (Position As Int, Value As Object)
    Activity.RemoveAllViews
    Activity.LoadLayout("Spinner")
    FillSpinners
    spinner.SelectedIndex = Position
    Dim FishCall As String
    FishCall = spinner.SelectedItem
    Activity.LoadLayout("SeaKnots")
    FillText(FishCall)
    player1.Initialize("player")
    player1.Prepare(player1.CreateFileSource(File.DirAssets, FishCall & ".mp4"))
    SimpleExoPlayerView1.Player = player1
    player1.Play
End Sub

so when I select an item it fails with the following error

An error has occurred in
sub:knots_filltext(java line:452)
android database.sqlite.SQLiteException:
no such column: info(code1):, while compiling:
SELECT DISTINCT info FROM SeaKnots WHERE knot = Arbor

Arbor is the selection form the spinner and info is definitely in the table with a value
I can't understand the error given its using the same database in FillSpinners to populate the spinner

Hope that makes some sense and you can point me in the right direction

Cheers

 

[Colin Evans]

I amended the code slightly but still getting nowhere

Sub FillText(FCall As String)
    Private Query As String
    Private Result As String
    Query = "SELECT info FROM " & DBTableName & " WHERE knot = " & FCall
    Result = SQL1.ExecQuerySingleResult(Query)
    lblText.Text = Result
End Sub

it now states that there is no column Arbor, which is the selected item from the Spinner and as you can see in the database table the column 'knot' contains the data 'Arbor' all I want to do is then select the text held in the info column relevant to the selection from the Spinner, in this case 'Arbor'

Hope someone can help, cheers

 

[Colin Evans]

Bloody hell, when you know the syntax is simple, I was simply missing the ' quotes around the variable FCall i.e.

 Query = "SELECT info FROM " & DBTableName & " WHERE knot = '" & FCall & "'"

Now working fine, sorry for troubling you but hope the solution helps anyone else stepping the world of SQL

 

[Computersmith64]

Bloody hell, when you know the syntax is simple, I was simply missing the ' quotes around the variable FCall i.e.

 Query = "SELECT info FROM " & DBTableName & " WHERE knot = '" & FCall & "'"

Now working fine, sorry for troubling you but hope the solution helps anyone else stepping the world of SQL

That will work - however you'll probably get replies about SQL injection concerns (https://en.wikipedia.org/wiki/SQL_injection).

To get around this you should use parameterized queries - eg:

ExecQuery2("SELECT info FROM ? WHERE knot = ?", Array As String(DBTableName, FCall))

- Colin.

 

[Colin Evans]

Hi, thanks for the reply really appreciated, however as I said I am a novice at this and I can't say I understand enough to work out your logic, I tried changing the code to yours but I get an error around the ?, I assumed I set my Result = SQL1.ExecQuery2( etc. but can't figure out how to get it working, Not sure I understand the SQL Injection concern as the database is a small database held directly on my phone so I don't think I'll be targeted.

Thanks again

 

[Computersmith64]

Hi, thanks for the reply really appreciated, however as I said I am a novice at this and I can't say I understand enough to work out your logic, I tried changing the code to yours but I get an error around the ?, I assumed I set my Result = SQL1.ExecQuery2( etc. but can't figure out how to get it working, Not sure I understand the SQL Injection concern as the database is a small database held directly on my phone so I don't think I'll be targeted.

Thanks again

Yeah - it's been a while... You probably can't parameterize the table name, so you'd need to write it as:

ExecQuery2($"SELECT info FROM ${DBTableName} WHERE knot = ?"$, Array As String(FCall)) '(I'm assuming "DBTableName" is a string that holds the table name. If it's the actual table name, then remove the ${} around it.)

Regardless of whether you think your database is at risk, it's always a good idea to get into the habit of writing your code in "best practice" ways so that it just becomes second nature. That way when you do have a database that's potentially worthwhile hacking, you'll automatically be writing secure queries.

- Colin.