Database integrity Check

[Smee]

I have found this in the sqllite docs but how can i use it within B4A?


PRAGMA integrity_check(integer)

Anyone?

 

[poseidon]

re

1-you have to add reference to SQL library

2-then you can execute any PRAGMA command with

Dim SQL1 As SQL

SQL1.Initialize(General.AppPath , "test.db", False)
SQL1.ExecNonQuery("PRAGMA foreign_keys = ON")


the doc

http://www.b4x.com/android/help/sql.html

 

[Smee]

Thanks for the reply

I could not find any reference in the docs to the PRAGMA command where you referenced it. Am I missing something?

Anyway the command

SQL.ExecNonQuery("PRAGMA integrity_check(100)")
as does 
SQL.ExecNonQuery("PRAGMA integrity_check(1)")
as does
SQL.ExecNonQuery("PRAGMA integrity_check")

throws an error

android.database.sqlite.SQLiteException: unknown error: Queries can be performed using SQLiteDatabase query or rawQuery methods only.

 

[klaus]

Look at this documentation.

Best regards.

 

[poseidon]

re

yes, you got this error because the PRAGMA command returns a resultset
use
ExecQuery (Query As String) As android.database.Cursor

 

[Smee]

I can execute using

Cursor1=SQL.ExecQuery("integrity_check")

But i am unable to test for results

if i use

Cursor1.Position = 0
Log(Cursor1.GetColumnName(0))

I get an error.

How do i test the cursor for a reult?

 

[poseidon]

re

on a dbase I use it as :

Dim SQLReader As Cursor
   Dim i As Int
    SQLReader = SQL1.ExecQuery("PRAGMA integrity_check(100)")

   If SQLReader <> Null Then 
      
      lstvCAT.Clear
      For i = 0 To SQLReader.RowCount - 1
            SQLReader.Position = i

            lstv.AddTwoLines2(SQLReader.GetString("Code"),SQLReader.GetString("Name"),SQLReader.GetString("id"))

      Next

      SQLReader.Close

   End If

 

[poseidon]

re

now I checked more
use instead

ExecQuerySingleResult

you should get 'ok'

hmm but wait at SQLite doc says :

http://www.sqlite.org/pragma.html#pragma_integrity_check

If any problems are found, then strings are returned (as multiple rows with a single column per row) which describe the problems

If no errors are found, a single row with the value "ok" is returned.

so you have to use cursor.. on single row the column_name is integrity_check

 

[Smee]

Tks for the replies

But have you checked your code on a damaged dbase?

This statement

lstv.AddTwoLines2(SQLReader.GetString("Code"),SQLReader.GetString("Name"),SQLReader.GetString("id"))

looks like it is reading a specific table. Also the default is 100 so do you need to put the 100 in?

 

[Smee]

Poseidon,

ExecQuerySingleResult does not work nor is the field called integrity_check. Appreciate the help but have you checked the code you are posting? If you are then I must be missing something else.

This executes without an error

Cursor1=SQL.ExecQuery("integrity_check")
Cursor1.Position = 0
If Cursor1.RowCount > 0 Then
     .... errors

But i cannot test it on a bad database as yet

UPDATE:

Ok I have my corrupt database installed; run the command above and it just flows through without signalling a problem

 

[poseidon]

re

the above code ^posted as example on how I use cursor ofc the fieldnames not exist...

            Dim kk As Cursor
            kk= General.ExecQuery("PRAGMA integrity_check(100)")
            kk.Position=0
            Msgbox(kk.GetString2(0),"test") //returns ok
            Msgbox(kk.GetColumnName(0),"test") //returns integrity_check
            Msgbox(kk.ColumnCount,"test") //returns 1
            Msgbox(kk.RowCount,"test") //returns 1 , if returns > 1 there are errors

so the final code will be :

            Dim kk As Cursor
            kk= General.GetCursor("PRAGMA integrity_check(100)")
            
            If kk.RowCount > 1  Then 
               For i = 0 To kk.RowCount -1 
                  kk.Position=i
                  Msgbox(kk.GetString2(0),"error")   
               Next
            End If

 

[Smee]

Thanks Poseidon,

It works with the word PRAGMA. However without that word there are no errors and the code seems to execute.

Now a strange problem. I have tested on a database that appears corrupt. I get the message ok but i am unable to access ANY tables in the database. However if i open the db on the computer with Sqllite Administrator it opens ok and the tables are there

:BangHead::BangHead::BangHead:

 

[poseidon]

re

1-
never use the dbase from dirassets, always create it/copy it to dir.external

2-
useful free SQLite manager

http://www.pipiscrew.com/works/sqlite-manager-v1-0/

 

[Smee]

it is in File.DirInternal for security

 

[poseidon]

re

nah. always use external, or just do for this^ test..

 

[Smee]

I have the database there on many copies of the program without ill effect. I sent this particular copy to Erel when it became 'damaged'. he was unable to open it either. It is not the location, if i overwrite the db with another copy the program works fine. Besides I use the other location for backup copies

UPDATE:
Using that sqlite manager you pointed to gives me an error when i open the db "database disk image is malfrmed"

Yet it WILL open with Sqllite Administrator

So to Recap...
I HAVE A MALFORMED DATABASE which cannot be accessed on the tablet and not a single table exists when i try to open them on the tablet but the PRAGMA Integrity Check says it is ok

:BangHead::BangHead::BangHead:

 

[poseidon]

re

LOL! so dont use this specific pragma on android there is no reason!

btw maybe is something with your SQLite database version (aka the .db) ?

 

[Smee]

LOL! so dont use this specific pragma on android there is no reason!

Yep it seems like there is no way to test the database integrity with a pragma

btw maybe is something with your SQLite database version (aka the .db) ?

Dont think so. It was ok until a user reported the program could no longer access the db. when i transfered it to another tablet I found that it was Malformed according to the android error message but could open it with SOME sqlite managers but not all

Thanks for your help anyway

Joe

 

[poseidon]

re

Sqllite Administrator

you mean

http://sqliteadmin.orbmu2k.de/

?

looks good I have to test..

[edit]
this tool is DIAMOND!. btw maybe can open the broken dbase because uses sqlite3.dll v3.5.1 (369kb) vs SQLiteManager sqlite3.dll v3.7.12 (588kb)

try to replace each other dlls..

 

[Smee]

Yep thats the one. Its pretty good