Android Question Determine SQLite query type

[RB Smissaert]

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

 

[RB Smissaert]

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:

Log(QueryReturnsData ("INSERT INTO Table1  VALUES(25)"))  'returns 0

Yes, true, but if you are going to run user SQL then you will need to run it in a Try Catch in any case if it is a non-data producing query.
The point is that there is no point in running the actual SQL fully in the type tester function (returning -1 for invalid, 0 for non-data producing and 1 for data producing) if it needs further error handling in any case. So, if the type tester function returns 0 we run ExecNonQuery in a Try Catch. So, as far as I see it the posted Type tester function
does what I need. I think if the type tester function returns 1 then we can run the query safely and it won't error, but I might be wrong there.

RBS

 

[OliverA]

Why don't you just see if the query starts with "select" and then use the query function, otherwise the non-query function? Technically, SELECT is the only DML that is throwing the ExecNonQuery, no?

 

[RB Smissaert]

Why don't you just see if the query starts with "select" and then use the query function, otherwise the non-query function? Technically, SELECT is the only DML that is throwing the ExecNonQuery, no?

How about pragma's?
How about queries starting with With?

RBS

 

[OliverA]

Pramga's are non-query, no? With "With" you have to cases to check. Anything else? Not an expert (see signature), just asking questions.

 

[RB Smissaert]

Pramga's are non-query, no? With "With" you have to cases to check. Anything else? Not an expert (see signature), just asking questions.

Pragma's can return data.

Try this interesting recursive query:

WITH RECURSIVE input(sud) AS (VALUES('53..7....6..1.5....98....6.8...6...34..8.3..17...2...6.6....2.....4.9..5....8..79')), digits(z, lp) AS (VALUES('1', 1) UNION ALL SELECT CAST(lp + 1 AS TEXT), lp + 1 FROM digits WHERE lp < 9), x(s, ind) AS (SELECT sud, instr(sud, '.') FROM input UNION ALL SELECT substr(s, 1, ind - 1) || z || substr(s, ind + 1), instr(substr(s, 1, ind - 1) || z || substr(s, ind + 1), '.') FROM x, digits AS z WHERE ind > 0 AND NOT EXISTS (SELECT 1 FROM digits AS lp WHERE z.z = substr(s, ((ind - 1) / 9) * 9 + lp, 1) OR z.z = substr(s, ((ind - 1) %9) + (lp - 1) * 9 + 1, 1) OR z.z = substr(s, (((ind - 1)/3) % 3) * 3 + ((ind - 1) / 27) * 27 + lp + ((lp - 1) / 3) * 6, 1))) SELECT s as solution FROM x WHERE ind = 0 order by s asc

RBS

 

[OliverA]

Looks like your try/case solution also distinguishes between

PRAGMA case_sensitive_like=1
and
PRAGMA table_info(someTable)

Interesting...

So what did this in VB (distinguishing result from non-result statements)?

 

[RB Smissaert]

Looks like your try/case solution also distinguishes between

PRAGMA case_sensitive_like=1
and
PRAGMA table_info(someTable)

Interesting...

So what did this in VB (distinguishing result from non-result statements)?

330      lRetVal = cSQL.PrepareStatement(strSQL, lReturnStatementHandle, lDBHdl, "GetSQLType")

340      If lRetVal <> 0 Then
350         GetSQLType = Invalid
360         Exit Function   'as PrepareStatement will have shown the error
370      End If

380      If bCheckWithReadOnly Then
            'sqlite3_stmt_readonly(X) interface returns true (non-zero) if and only if the
            'prepared statement X makes no direct changes to the content of the database file
390         If sqlite3_stmt_readonly(lReturnStatementHandle) > 0 Then
400            GetSQLType = DataProducing
410         Else
420            GetSQLType = Non_DataProducing
430         End If
440      Else    'If bCheckWithReadOnly
450         lColumns = cSQL.GetColumnCount(lReturnStatementHandle, "GetSQLType", bFinalize = False, lDBHdl)
            'no need here to check for invalid statement (would produce lColumns = -1) as this is done in PrepareStatement
            '-------------------------------------------------------------------------------------------------------------
460         If lColumns > 0 Then
470            GetSQLType = DataProducing
480         Else
490            GetSQLType = Non_DataProducing
500         End If
510      End If    'If bCheckWithReadOnly

Maybe your idea to use the explain statement is neater and will have a look at that.

RBS

 

[OliverA]

Maybe your idea

Sadly, it was not mine

sqlite3_stmt_readonly

Android will not let you have access to this. You would have to compile and install your own version of sqlite. I can't find anything similar in the Android Developer documentation.

GetColumnCount

I have not seen anything (but that's just me) on Android that gets column information from a prepared statement without having executed it first.

 

[RB Smissaert]

Sadly, it was not mine

Sorry, mixed up OliverA and Oliveira.

RBS

 

[OliverA]

FYI: In regards to SQLite's EXPLAIN, this is from SQLite's site (https://www.sqlite.org/lang_explain.html):

The output from EXPLAIN and EXPLAIN QUERY PLAN is intended for interactive analysis and troubleshooting only. The details of the output format are subject to change from one release of SQLite to the next. Applications should not use EXPLAIN or EXPLAIN QUERY PLAN since their exact behavior is variable and only partially documented.

 

[Claudio Oliveira]

FYI: In regards to SQLite's EXPLAIN, this is from SQLite's site (https://www.sqlite.org/lang_explain.html):

That's true. They advise not to use Explain in applications, and sticking to this rule won't hurt anybody.
On the other hand, the opcode ResultRow has been added to SQLite Virtual Database Engine back in 2008 and the opcode itself haven't changed ever since (its parameters did have), so I believe it's reasonably safe to use it in this particular and rather simple way - I mean, only checking if it is present in an Explain statement result set - even though it isn't guaranteed that changes won't come in the future...