Android Question Help with SQL select statements using (?)

[mscientist33]

I have the following SQL select statement in my code:

Dim SenderFilter As Object = mSQL.ExecQueryAsync("SQL", "SELECT v, t FROM ? WHERE b=?", Array As String(selectedVersion,Books.SelectedIndex))

The first ? is the table that is set with a string variable and the 2nd just takes the selectedindex from a combobox

I keep getting sql error near ?

 

[Mahares]

he first ? is the table that is set with a string variable

Dim SenderFilter As Object = mSQL.ExecQueryAsync("SQL", "SELECT v, t FROM " & selectedversion & " WHERE b=?", Array As String(Books.SelectedIndex))

You cannot parameterize the table name in question mark

 

[mscientist33]

With the line you provided I get this error:

(SQLiteException) android.database.sqlite.SQLiteException: near "WHERE": syntax error (code 1): , while compiling: SELECT v, t FROM WHERE b=?

 

[KMatle]

? is a placeholder for variables to prevent SQL injection. As the tablename is static, it cannot be changed (which is good)

 

[mscientist33]

? is a placeholder for variables to prevent SQL injection. As the tablename is static, it cannot be changed (which is good)

It isn't good if the user wants data from another tablename with same type of data. The user selects the tablename based off of a combobox. The only part of the SQL statement that changes is the tablename. When the user changes the combobox selection, it automatically refreshes the data from the sql statement with the chosen tablename from the combobox.

 

[Mahares]

With the line you provided I get this error:

Put down you full code that executes the query or attach your project, so it can be debugged

 

[mscientist33]

Put down you full code that executes the query or attach your project, so it can be debugged

 

[Mahares]

Where is your code. You forgot to put it in your previous post

 

[mcqueccu]

It isn't good if the user wants data from another tablename with same type of data. The user selects the tablename based off of a combobox. The only part of the SQL statement that changes is the tablename. When the user changes the combobox selection, it automatically refreshes the data from the sql statement with the chosen tablename from the combobox.

Use selecteditem property of combobox to get the name of what the user selected and fix it in your query

 

[mscientist33]

Says attachment too large for server to process. I have a large database, 42mb. But here is the complete sub if that helps:

    Dim SenderFilter As Object = mSQL.ExecQueryAsync("SQL", "SELECT v, t FROM " & selectedVersion & " WHERE b=?", Array As String(Books.SelectedIndex))
    Wait For (SenderFilter) SQL_QueryComplete (Success As Boolean, rs As ResultSet)
    If Success Then
        Dim x As Int=1
        Do While rs.NextRow
            Log(rs.GetString2(0))
            Dim p As B4XView = xui.CreatePanel("")
            p.LoadLayout("VersesLO")
            lblVerse.Text=x
            lblText.Text=rs.GetString2(0)
            Dim iv As versevalues
            iv.Initialize
            iv.lblVerse=lblVerse
            iv.lblText=lblText
            Log("Added: " & iv.lblVerse.Text & " - " & iv.lblText.Text)
            Verses.Add(p,iv)
            x=x+1
        Loop
        rs.Close
    Else
        Log(LastException)
    End If
End Sub

Its a Bible app. I have a database with 7 different versions, which is the selectedVersion in this sub. When the user changes the combo to a different version it updates the xCustomListView with the updated verses (hopefully)

 

[mscientist33]

Here is the code just pasted:

#Region Shared Files
#CustomBuildAction: folders ready, %WINDIR%\System32\Robocopy.exe,"..\..\Shared Files" "..\Files"
'Ctrl + click to sync files: ide://run?file=%WINDIR%\System32\Robocopy.exe&args=..\..\Shared+Files&args=..\Files&FilesSync=True
#End Region

'Ctrl + click to export as zip: ide://run?File=%B4X%\Zipper.jar&Args=Project.zip

Sub Class_Globals
    Private Root As B4XView
    Private xui As XUI
    Public mSQL As SQL
    Public ApVals As Map
    Private bVerisons As B4XComboBox
    Private bChapters As B4XComboBox
    Private Books As B4XComboBox
    Private selectedVersion As String
    Public kvs As KeyValueStore
    Private Verses As CustomListView
    Private lblVerse As Label
    Private lblText As Label
    Type versevalues (lblVerse As Label, lblText As Label)
    
End Sub

Public Sub Initialize
    B4XPages.GetManager.LogEvents = True
    xui.SetDataFolder("DevoSettings")
    kvs.Initialize(xui.DefaultFolder, "devo.dat")
        CopyDBIfNeeded("bible.db")
        mSQL.Initialize(File.DirInternal, "bible.db", False)
        Log(File.DirInternal)
    
    
    
End Sub

'This event will be called once, before the page becomes visible.
Private Sub B4XPage_Created (Root1 As B4XView)
    Root = Root1
    Root.LoadLayout("bible")
    
    LoadBibleVersions
    LoadBibleBooks
    LoadBibleChapters
    LoadVerses
    
    
End Sub
Sub CopyDBIfNeeded (Filename As String)
    If File.Exists(File.DirInternal, Filename) = False Then
        File.Copy(File.DirAssets, Filename, File.DirInternal, Filename)
        Log("Copied")
    End If
End Sub

Sub LoadBibleChapters
    bChapters.cmbBox.Clear
    Dim mindex As Int
    If Books.cmbBox.SelectedIndex=-1 Then
    
     mindex =1
    Else
        mindex=Books.cmbBox.SelectedIndex+1
    End If
    Log("mindex: " & mindex)
    Dim SenderFilter As Object = mSQL.ExecQueryAsync("SQL", "SELECT c FROM key_english where b=?", Array As String(mindex))
    Wait For (SenderFilter) SQL_QueryComplete (Success As Boolean, rs As ResultSet)
    If Success Then
        Dim numofchapters As Int
        
        Do While rs.NextRow
            numofchapters= rs.GetInt("c")
        Loop
        
        For x=1 To numofchapters
            bChapters.cmbBox.Add(x)
        Next
        
    Else
        Log(LastException)
    End If
    rs.Close
    bChapters.cmbBox.SelectedIndex=kvs.GetDefault("Chapter",0)
End Sub

Sub LoadVerses
    
    
    Dim SenderFilter As Object = mSQL.ExecQueryAsync("SQL", "SELECT v, t FROM " & selectedVersion & " WHERE b=?", Array As String(Books.SelectedIndex))
    Wait For (SenderFilter) SQL_QueryComplete (Success As Boolean, rs As ResultSet)
    If Success Then
        Dim x As Int=1
        Do While rs.NextRow
            Log(rs.GetString2(0))
            Dim p As B4XView = xui.CreatePanel("")
            p.LoadLayout("VersesLO")
            lblVerse.Text=x
            lblText.Text=rs.GetString2(0)
            Dim iv As versevalues
            iv.Initialize
            iv.lblVerse=lblVerse
            iv.lblText=lblText
            Log("Added: " & iv.lblVerse.Text & " - " & iv.lblText.Text)
            Verses.Add(p,iv)
            x=x+1
        Loop
        rs.Close
    Else
        Log(LastException)
    End If
End Sub



Sub LoadBibleVersions
    Dim SenderFilter As Object = mSQL.ExecQueryAsync("SQL", "SELECT version FROM bible_version_key", Null)
    Wait For (SenderFilter) SQL_QueryComplete (Success As Boolean, rs As ResultSet)
    If Success Then
        Do While rs.NextRow
            Log(rs.GetString2(0))
            bVerisons.cmbBox.Add(rs.GetString2(0))
        Loop
        rs.Close
    Else
        Log(LastException)
    End If
    bVerisons.cmbBox.SelectedIndex=kvs.GetDefault("Version",0)
End Sub

Sub LoadBibleBooks
    Dim SenderFilter As Object = mSQL.ExecQueryAsync("SQL", "SELECT n FROM key_english", Null)
    Wait For (SenderFilter) SQL_QueryComplete (Success As Boolean, rs As ResultSet)
    If Success Then
        Do While rs.NextRow
            Log(rs.GetString2(0))
            Books.cmbBox.Add(rs.GetString2(0))
        Loop
        rs.Close
    Else
        Log(LastException)
    End If
    
    Books.cmbBox.SelectedIndex=kvs.GetDefault("Book",0)
    
End Sub

'You can see the list of page related events in the B4XPagesManager object. The event name is B4XPage.

Private Sub Button1_Click
    xui.MsgboxAsync("Hello world!", "B4X")
End Sub

Sub setVersionforSQL(vIndex As Int)
    Select Case vIndex
        Case 0
            selectedVersion="t_asv"
        Case 1
            selectedVersion="t_bbe"
        Case 2
            selectedVersion="t_dby"
        Case 3
            selectedVersion="t_kjv"
        Case 4
            selectedVersion="t_wbt"
        Case 5
            selectedVersion="t_web"
        Case 6
            selectedVersion="t_ylt"
    End Select
    kvs.Put("Version", vIndex)
    
End Sub




Private Sub bVerisons_SelectedIndexChanged (Index As Int)
    
    setVersionforSQL(Index)
End Sub

Private Sub bChapters_SelectedIndexChanged (Index As Int)
    
    kvs.Put("Chapter",Index)
    
End Sub

Private Sub Books_SelectedIndexChanged (Index As Int)
    
    kvs.Put("Book",Index)
End Sub

I am very new to this so don't laugh at me too much ha ha.

 

[Mahares]

I am very new to this so don't laugh at me too much ha ha.

You are doing great.
From the error, the variable selectedversion for the table is blank. That is why you got an error.

 

[mscientist33]

Okay, I got passed that by adding:

setVersionforSQL(kvs.GetDefault("Version",0))

above the LoadBibleVersions call.

It's just not loading the verses..... Now to figure out this xCustomListView

 

[Mahares]

Okay, I got passed that by adding:

Now to figure out this xCustomListView

There are many xClv knowledgeable people in the forum. When you get stuck, post your pertinent code or better yet a project with a small database