Android Question Determine SQLite query type

RB Smissaert

Well-Known Member
Licensed User
Longtime User
Is it possible in B4A to determine the query type, that is data producing or non-data producing, without
parsing the SQL? I can this in VB6 no problem as I have access to the lower level SQLite functions, eg
PrepareStatementxxx, but I can't see a simple way to do this in B4A.

RBS
 

Jeffrey Cameron

Well-Known Member
Licensed User
Longtime User
You should have access to all the same functions, you may have to interact with the actual SQL objects instead of the DBUTILS but if you can get to in VB6 I would think you can do it B4X.

If not, the simplest way would be to take "SELECT COUNT(rowid) AS [RowCount] FROM " and then add the table name and WHERE clause from your SQL that you are curious about.
 
Upvote 0

RB Smissaert

Well-Known Member
Licensed User
Longtime User
You should have access to all the same functions, you may have to interact with the actual SQL objects instead of the DBUTILS but if you can get to in VB6 I would think you can do it B4X.

If not, the simplest way would be to take "SELECT COUNT(rowid) AS [RowCount] FROM " and then add the table name and WHERE clause from your SQL that you are curious about.

Not sure what you are saying there. Are you saying I could somehow run preparestatementxxx?
Not sure again how doing select count(rowed) etc. would help me here.

RBS
 
Upvote 0

Jeffrey Cameron

Well-Known Member
Licensed User
Longtime User
Using the Select count and appending the WHERE clause from your SQL statement will tell how you many rows will be effected.

Ultimately, VB6 is using some SQLite function(s) to handle your "PrepareStatementxxx", whatever those functions are, the SQLite library in B4X probably supports them. A quick search of the SQLite site finds: https://www.sqlite.org/c3ref/prepare.html
 
Upvote 0

RB Smissaert

Well-Known Member
Licensed User
Longtime User
Using the Select count and appending the WHERE clause from your SQL statement will tell how you many rows will be effected.

Ultimately, VB6 is using some SQLite function(s) to handle your "PrepareStatementxxx", whatever those functions are, the SQLite library in B4X probably supports them. A quick search of the SQLite site finds: https://www.sqlite.org/c3ref/prepare.html

Sorry, don't get you still.

Say we have this statement:
pragma table_info(table1)
Unless I parse the SQL I can't see if this statement will (more precisely: can) produce data or not.

RBS
 
Upvote 0

klaus

Expert
Licensed User
Longtime User
Is it possible in B4A to determine the query type, that is data producing or non-data producing, without
Sorry, but I don't understand what exactly you want to do?
As Jeffery Cameron already posted, with his SELECT * FROM .. query you know if there is a result or not with Cursor.RowCount.
 
Upvote 0

RB Smissaert

Well-Known Member
Licensed User
Longtime User
Sorry, but I don't understand what exactly you want to do?
As Jeffery Cameron already posted, with his SELECT * FROM .. query you know if there is a result or not with Cursor.RowCount.

I have an editbox from which I want to run any SQL. So there is only one button to run the SQL, no matter what type of SQL.
I think I have solved it like this:


B4X:
Sub btnRunSQL_Click()
 
 Dim strSQL As String
 Dim CursorSQLEdit As Cursor

 strSQL = edtSQL.Text

 If strSQL.Length < 8 Then 'any shorter than select 1  ?
    Return
 End If
 
 Try
     SQL1.ExecNonQuery(strSQL)
     Return
 Catch
     Try
        CursorSQLEdit = SQL1.ExecQuery(strSQL)
     Catch
          Return
     End Try
 End Try
 
 If CursorSQLEdit.RowCount > 0 Then
    tblSQLResult.LoadSQLiteDB4(SQL1, CursorSQLEdit, True, Array As String(""), Array As String(""))
    tblSQLResult.Visible = True
    CursorSQLEdit.Close
 End If
 
End Sub


RBS
 
Upvote 0

Jeffrey Cameron

Well-Known Member
Licensed User
Longtime User
I'm not sure if that will work... Any valid SQL may be executed as a non-query it will return the number of rows inserted, updated, or deleted or "-1 " for SELECT statements.

I would use Klaus' example and just check the Cursor.RowCount returned from the ExecQuery and act upon that.

Sorry for the confusion in my answer, I misread your question to mean you wanted to "preview" how many items would be affected by your SQL statement before you executed it.
 
Upvote 0

OliverA

Expert
Licensed User
Longtime User
I'm not sure if that will work.
In B4J, it does not (due to the reason's you mention). In B4A, it does throw the following
android.database.sqlite.SQLiteException: unknown error (code 0): Queries can be performed using SQLiteDatabase query or rawQuery methods only.
 
Upvote 0

RB Smissaert

Well-Known Member
Licensed User
Longtime User
I would use Klaus' example and just check the Cursor.RowCount returned from the ExecQuery and act upon that.

But how about if it is a valid select sql that just produces no rows?
For now I am not interested in how my code behaves in B4J.

RBS
 
Upvote 0

Mahares

Expert
Licensed User
Longtime User
But how about if it is a valid select sql that just produces no rows?

How about if you involve the column count and do this. The columnCount is always 0 in a Non Query:
B4X:
Dim strSQL As String="INSERT INTO Table1  VALUES(25)"
    Try
        cursor1=SQL1.ExecQuery(strSQL)
        Log("column count: " & cursor1.ColumnCount)
        Log("number of records: " & cursor1.RowCount)
        If cursor1.ColumnCount >0 Then
            Log("my query is a Select query")
        Else 
            Log("my query is an Action non query")
        End If
    Catch
        Log("error: " & LastException.Message)
    End Try
You can try these other statements:
B4X:
Dim strSQL As String="SELECT * FROM table1 WHERE Sample =30"  'works even if 0 records
B4X:
Dim strSQL As String="SELECT * FROM table1 WHERE Sam =30"  'error since field name does not exist
 
Upvote 0

Claudio Oliveira

Active Member
Licensed User
Longtime User
Maybe you could try the SQLite explain statement.
The explain statement doesn't execute the query, instead SQLite returns the sequence of virtual machine instructions it would have used to execute such a command.

The code below returns True if the query passed as parameter is supposed to return data, and False otherwise.
B4X:
Sub QueryReturnsData (strSQL as String) as Boolean
Dim rs As ResultSet

rs=SQL1.ExecQuery($"explain ${strQuery}"$)

Do While rs.NextRow
    If rs.GetString("opcode")="ResultRow" Then
        Return True
    End If
Loop

Return False

End sub
 
Upvote 0

RB Smissaert

Well-Known Member
Licensed User
Longtime User
Maybe you could try the SQLite explain statement.
The explain statement doesn't execute the query, instead SQLite returns the sequence of virtual machine instructions it would have used to execute such a command.

The code below returns True if the query passed as parameter is supposed to return data, and False otherwise.
B4X:
Sub QueryReturnsData (strSQL as String) as Boolean
Dim rs As ResultSet

rs=SQL1.ExecQuery($"explain ${strQuery}"$)

Do While rs.NextRow
    If rs.GetString("opcode")="ResultRow" Then
        Return True
    End If
Loop

Return False

End sub

Thanks, that looks a nice solution indeed.
Only lightly tested, but works fine sofar.

RBS
 
Upvote 0

RB Smissaert

Well-Known Member
Licensed User
Longtime User
Maybe you could try the SQLite explain statement.
The explain statement doesn't execute the query, instead SQLite returns the sequence of virtual machine instructions it would have used to execute such a command.

The code below returns True if the query passed as parameter is supposed to return data, and False otherwise.
B4X:
Sub QueryReturnsData (strSQL as String) as Boolean
Dim rs As ResultSet

rs=SQL1.ExecQuery($"explain ${strQuery}"$)

Do While rs.NextRow
    If rs.GetString("opcode")="ResultRow" Then
        Return True
    End If
Loop

Return False

End sub

This seems to cover all:

B4X:
Sub QueryReturnsData(strSQL As String) As Int
 
Dim rs As ResultSet
 
 Try
    rs = SQL1.ExecQuery($"explain ${strSQL}"$)
 Catch
    Return -1
 End Try

 Do While rs.NextRow
    If rs.GetString("opcode") = "ResultRow" Then
       Return 1
    End If
 Loop

Return 0

End Sub


RBS
 
Upvote 0

Claudio Oliveira

Active Member
Licensed User
Longtime User
Thanks, that looks a nice solution indeed.
Only lightly tested, but works fine sofar.

RBS

Good! :)

Even though Explain statement behaves like a query and returns a cursor, it's not really a query and doesn't support regular queries clauses, so if you try to do something like
B4X:
SELECT COUNT(*) FROM (Explain <query>) WHERE opcode = "ResultRow"
SQLite will report a syntax error, so you have to step through all records in cursor, searching for the right opcode.
Not as efficient and elegant, but works... ;)
 
Upvote 0

Mahares

Expert
Licensed User
Longtime User
Even though Explain statement behaves like a query and returns a cursor, it's not really a query and doesn't support regular queries clauses
This seems to cover all:

I have a table that has 1 column as INTEGER PRIMARY KEY. If I try to enter a new record that is duplicate using your code, it should return an error (-1) (PRIMARY KEY constraint), but based on your code, it returns a 0 which is a non query. What is then the benefit of using 'Explain' and knowing whether a query is QUERY that returns data or NON QUERY if the query statement will return an error when applied to a table:
B4X:
Log(QueryReturnsData ("INSERT INTO Table1  VALUES(25)"))  'returns 0
 
Upvote 0

OliverA

Expert
Licensed User
Longtime User
I can this in VB6 no problem as I have access to the lower level SQLite functions, eg
How was this done in VB6? My Google-Fu seems to be weak and a curious mind would like to know.
 
Upvote 0

RB Smissaert

Well-Known Member
Licensed User
Longtime User
How was this done in VB6? My Google-Fu seems to be weak and a curious mind would like to know.

I compiled the SQLite amalgation std_call. Then wrote a .tlb to access the SQLite functions (and structures etc.) from VBA/VB6. Then I also wrote a VB6 ActiveX dll to wrap things up further + added an interface in that ActiveX dll (forms) to be accessed from Excel VBA.
All working very nicely.

I think it would be nice if you B4X provided access to the pure, unwrapped SQLite API.

RBS
 
Upvote 0

OliverA

Expert
Licensed User
Longtime User
Last edited:
Upvote 0
Top