Help with writing, opening, reading and updating SQlite database

[kaboekieman]

Hi Malkom... could you help me out with the basic beginning steps of writing a sq-lite database file?
I have experience in php/mysql... so the concept is clear, but implementation or even making a simple sq-lite db file prooved too difficult for me sorry... thought I was smarter hihi

I looked at the recent help file (v1.16) and looked at the "8 how to" (blog) but could you help me with these steps?

1a. If the HAC application starts it has to look if the database file exists, if so, open it... if not create it (after which it will be open)... I guess I should setup this code into the script and then the main code of HAC (at the StartUp location)?

1b. Create database file in root of app, create table (creating tables with rows, primary key, auto increment I know). I saw the example database project but it didn't save ... so it was a little puzzeling how this works.

2. read database file and extract information via statements... I know how this is accomplished via MySQL queries and/or PHP statements (for example if then functions) but is there a list of statements or queries I can use in HAC in combination with SQlite??? During my php exam writing good mysql queries was one of the most difficult parts of the education, is this also true for SQlite? I know if you manage to write good queries you have a very powerfull tool at your side. 


My first goal will be to insert text from a table-cell into the app for every page and to implement a save location feature that will 'look' at the hac page the user last visited. After the user ends and starting the app up again it would automatically go to this location (save state). I already made this with a flatfile but this should also be possible with SQlite I guess.   

Well that's about it. Sorry that I have so many requests and questions. I do appriciate all your help

With kind regards, Jeroen

[kaboekieman]

another question is could you use a 3rd party 'software' to edit the database file created with hac? For example phpliteadmin (https://code.google.com/p/phpliteadmin/)

[Malkom]

Hi Malkom... could you help me out with the basic beginning steps of writing a sq-lite database file?
I have experience in php/mysql... so the concept is clear, but implementation or even making a simple sq-lite db file prooved too difficult for me sorry... thought I was smarter hihi

I looked at the recent help file (v1.16) and looked at the "8 how to" (blog) but could you help me with these steps?

1a. If the HAC application starts it has to look if the database file exists, if so, open it... if not create it (after which it will be open)... I guess I should setup this code into the script and then the main code of HAC (at the StartUp location)?

1b. Create database file in root of app, create table (creating tables with rows, primary key, auto increment I know). I saw the example database project but it didn't save ... so it was a little puzzeling how this works.

2. read database file and extract information via statements... I know how this is accomplished via MySQL queries and/or PHP statements (for example if then functions) but is there a list of statements or queries I can use in HAC in combination with SQlite??? During my php exam writing good mysql queries was one of the most difficult parts of the education, is this also true for SQlite? I know if you manage to write good queries you have a very powerfull tool at your side. 


My first goal will be to insert text from a table-cell into the app for every page and to implement a save location feature that will 'look' at the hac page the user last visited. After the user ends and starting the app up again it would automatically go to this location (save state). I already made this with a flatfile but this should also be possible with SQlite I guess.   

Well that's about it. Sorry that I have so many requests and questions. I do appriciate all your help

With kind regards, Jeroen

Hi Jeroen

I'm glad you are making progress and thanks for explaining your problems clearly.

Yes, the Startup section of MainCode is a good place to initialise or open the database.

By the way, I don't think Android will allow a file to be created in the root of the app - I had problems attempting this and had to use the database folder inside the app. For my own Android apps I nearly always use the local folder that a HAC app makes on the SDcard as some versions of Android had problems handling files inside the app folder.

The code below checks to see if the database file exists and either opens it or creates it

Code: [Select]

Local dbname,path,exists,error
Local version,table,structure,dbid

@ filename
Put 'info.db' into dbname

@ --- form source path ---
Put DatabasePathFN into path
Append '/' onto path
Append dbname onto path

@ --- try to open - if fails then create it --- 
Put DbExistsFN(path) into exists
If exists=1 then
   Put DbOpenFn(path,1) into error
   if error=1 then
      Message 'Could not open DB'
   endif
else
   Put 1 into version
   Put 'books' into table

   Put '(' into structure
   Append '_id integer primary key autoincrement,' onto structure
   Append  ' title text not null,' onto structure
   Append  ' author text not null,' onto structure
   Append  ' comment text not null);' onto structure

   @ --- Create ---
   Put DbCreateFN(path,version,table,structure) into dbid
   if dbid=0 then
      Message 'Could nopt create DB'
   endif
endif


I'm not sure if you have the debugger open while you work? - the DDMS - its available form the Go menu and when running shows lots of things happening inside the device including if a database fails to open etc.




Yes queries can be difficult. I work with PHP/MySQL most nights and still can't remember some queries and syntax. Therefore I either use a file I previously made on the website or use Google to search. Stackoverflow is a great site  for detail and sites like RoseIndia are a good place to start

http://www.roseindia.net/mysql/


Queries in HAC are very similar to those in SQL/PHP except that HAC queries might need extra single quotes or a character 39 in places.

There are 2 parts to using queries - making the query and then using any results return from the query

This query search for titles having Android in them:

Code: [Select]

Put 'SELECT _id, title FROM books WHERE title LIKE ' into query
Append ChrFN(39) onto query
Append '%Android%' onto query
Append ChrFN(39) onto query

Put query into field 3

Put DbExecQueryFN(path,query) into field 1


This uses the results from the query and iterates over every result:-

Code: [Select]

Put DbQueryCountFN(path) into count

Put count into field 4

Clear field 5
if count>0 then
    for n=1 to count
        Put DbQueryResultFN(path,n,'title') after field 5
    endfor
endif


I hope this makes things clearer

Malkom

[Malkom]

another question is could you use a 3rd party 'software' to edit the database file created with hac? For example phpliteadmin (https://code.google.com/p/phpliteadmin/)

Yes you should be able to as HAC just uses the Android OS to create and modify databases.

On an Android device i have used aSQLiteManager to open and verify if the database created was okay.
Its available on Google Market.

[kaboekieman]

Hi Malkom thx for this extensive answer... but the code didn't save I have made a 'Local' map in the root of the app (in Windows) then copy/paste this code in the main code (at the start up section) ... just to see if a file was made, but no "info.db" file was made... if I echo the output (use message dbid) I saw the output was 0 and no file (table) was created...

Did I do something wrong?

Code: [Select]

Local dbname,path,exists,error
Local version,table,structure,dbid

@ filename
Put 'info.db' into dbname

@ --- form source path ---
Put DatabasePathFN into path
Append '/' onto path
Append dbname onto path

Put 1 into version
Put 'books' into table

Put '(' into structure
    Append '_id integer primary key autoincrement,' onto structure
    Append  ' title text not null,' onto structure
    Append  ' author text not null,' onto structure
    Append  ' comment text not null);' onto structure

@ --- Create ---
Put DbCreateFN(path,version,table,structure) into dbid

message dbid
grts, Jeroen

[kaboekieman]

woeps, just thought of something... I used to test the sql files I made a couple of months ago with localhost and phpmyadmin combo... How could I forget  hmmms gonna test that first

[kaboekieman]

that raises a question in my mind ... is it possible to test an application (run it inside HAC) in windows with a sq lite database file as local file (just like normal flatfiles)? or do i need to use another approach? like the http://localhost/ (xampp server) for example 

[Malkom]

Hi Jeroen

I'm having a look at this now, should have something today.

Malkom

[Malkom]

Hi Jeroen

I'm sorry for the delay in getting back to you.

There is a bug with HAC regarding creating an internal  database although creating a local databse works fine.

I'll track the internal problem down and try to see what went wrong as it was definitely working when we tested it for v1.15 - perhaps when I was optimising the DB code I introduced a bug.


This is the code i use in the Startup section - note i commented out the internal database path setting

Code: [Select]

Global dbName,dbPath

@ filename
Put 'info.db' into dbName

@ --- form source path ---
Put LocalPathFN into dbPath
@Put DatabasePathFN into dbPath
Append '/' onto dbPath
Append dbname onto dbPath


Local exists,error
Local version,table,structure,dbid

@ --- try to open - if fails then create it ---
Put DbExistsFN(dbPath) into exists
If exists=1 then
    Put 'DB exists' into field 1
    Put DbOpenFN(dbPath,1) into error
    if error=1 then
        Put 'Could not open DB' after field 1
        Put dbPath after field 1
    endif
else
    Put 'DB does not exist' into field 1
    Put 1 into version
    Put 'books' into table

    Put '(' into structure
    Append '_id integer primary key autoincrement,' onto structure
    Append  ' title text not null,' onto structure
    Append  ' author text not null,' onto structure
    Append  ' comment text not null);' onto structure

    @ --- Create ---
    Put DbCreateFN(dbPath,version,table,structure) into dbid
    if dbid=0 then
        Put 'Could nopt create DB' after field 1
        Put dbPath after field 1
    endif
endif

I hope this helps.

Malkom

[Malkom]

that raises a question in my mind ... is it possible to test an application (run it inside HAC) in windows with a sq lite database file as local file (just like normal flatfiles)? or do i need to use another approach? like the http://localhost/ (xampp server) for example

I tried some apps for editing Android databases but some of them produced an incompatible database file - not sure if it was the SQLite structure or some file formating problem.

You could place your database file in the Game Book's Local folder on your desktop machine and work on it - then when you build the project it will be included in the built apk file.

[kaboekieman]

Hi Malkom thx for your input I have decided I will use flatfiles for my application... The main reason is that I can check the app on the fly (when using HAC). If it will be to difficult with inventory issues I will have another go with sqlite. Only the stranges thing. I have included two .rar files in this post so you can see the problem... In HAC emulator mode (when using run) everything lookes and works fine, but when I export it as apk and install it on my phone (HTX One X) this simple application doen't work that good (sometimes it hangs)... I think it has issues with the flatfile? Do you know what I do wrong? I use a flatfile (locatie.txt) which is stored in a "Local" folder of my app (see boek1.rar file). For your convenience I have also included the apk file (boek1_apk.rar).

The goal of this app (without fancy graphics) is to test the save state routine using a text file as a reference point for the last visited HAC page.

Standard (first time users) will have number 3 stored (first page) in the locatie.txt file!

home => main screen (1 button, if number in locatie.txt = 3, 2 buttons, if number in locatie.txt is not 3)
card2 => reset book (sure? yes: goto card 3, no: goto Home)
card3 => save number 3 in locatie.txt, first page game
card4 => save number 4 in locatie.txt, second page game

Hope you can help me out with this? thx, Jeroen

[Malkom]

Hi Malkom thx for your input I have decided I will use flatfiles for my application... The main reason is that I can check the app on the fly (when using HAC). If it will be to difficult with inventory issues I will have another go with sqlite. Only the stranges thing. I have included two .rar files in this post so you can see the problem... In HAC emulator mode (when using run) everything lookes and works fine, but when I export it as apk and install it on my phone (HTX One X) this simple application doen't work that good (sometimes it hangs)... I think it has issues with the flatfile? Do you know what I do wrong? I use a flatfile (locatie.txt) which is stored in a "Local" folder of my app (see boek1.rar file). For your convenience I have also included the apk file (boek1_apk.rar).

The goal of this app (without fancy graphics) is to test the save state routine using a text file as a reference point for the last visited HAC page.

Standard (first time users) will have number 3 stored (first page) in the locatie.txt file!

home => main screen (1 button, if number in locatie.txt = 3, 2 buttons, if number in locatie.txt is not 3)
card2 => reset book (sure? yes: goto card 3, no: goto Home)
card3 => save number 3 in locatie.txt, first page game
card4 => save number 4 in locatie.txt, second page game

Hope you can help me out with this? thx, Jeroen

Hi Jeroen

Thank you for the project and apk file.

I think its better to use the OpenTWriteAbs command instead of OpenTWriteLoc as its more consistent with paths.

When using the Local folder its necessary to use the function LocalPathFN otherwise the APK will just look for "Local" and not for the full path. HAC itself can use both but APKs cannot.

This code is from the earlier database stuff here might help:-

Code: [Select]

Local fname,fpath,fvar

@ filename
Put 'locatie.txt' into fname

@ --- form source path ---
Put LocalPathFN into fpath
Append '/' onto fpath
Append fname onto fpath

OPenTWriteAbs(fpath,fvar)
..
...

I hope this helps

Malkom

[kaboekieman]

Thx for your reply... I have made it even simpler (for me)... just to test if reading from a flatfile works... but I can't seem to get the correct code. Do you know what I do wrong?

Code: [Select]

@ initialiseer variabelen
Local fname, fpath, temp, fvar

@ database bestand
Put 'locatie.txt' into fname

@ volledige path naar database bestand
Put LocalPathFN into fpath
Append '/' onto fpath
Append fname onto fpath

message fpath

@ open database bestand en lees inhoud
OpenTReadAbs fpath,temp
    ReadTLine temp,fvar
CloseTRead temp

message fvar
I have stored the locatie.txt file in the "Local" folder. In this file there's is only a number, number: 3

When I test it in HAC ... fvar will pop up 3, but when I test it on my HTC One X fvar will be empty...  I feel like an idiot
p.s. on my phone I get the following message from fpath: "/mnt/sdcard/test.grailquest.nl/Local/locatie.txt"

I guess there is something wrong with the ReadTLine command? Because when I echo (use message) temp... the result will be 1 (if flatfile exists) and 0 (If I deleted the flatfile before compiling the apk) hjust to test things.

[Malkom]

Hi Jeroen

I see the problem - my fault for missing it.

The way the underlying Java works meant making an end-of-file check to fetch each line.
However, we didn't use the EndTFileFN function so no data was returned for use by the ReadTLine command.

Below is a mod of your script that works for me:-
it has 2 extra lines - one declares Local var fend, and the second tests for end-of file.

Code: [Select]

@ database bestand
Put 'locatie.txt' into fname

@ volledige path naar database bestand
Put LocalPathFN into fpath
Append '/' onto fpath
Append fname onto fpath

message fpath

Local fend

@ open database bestand en lees inhoud
OpenTReadAbs fpath,temp
    Put EndTFileFN(temp) into fend
    ReadTLine temp,fvar
CloseTRead temp

message fvar
The HAC file example project uses a WHILE loop that checks for end-of-file so any lines are automatically returned for use by the ReadTLIne.

I hope this helps you make progress.

Malkom

[kaboekieman]

Great! that worked ... thx Malkom
I will rewrite my setup and will upload a micro dungeon App soon which I will upload here in this forum... The dungeon isn't that interesting (it's really short), but it will feature a save state routine. Just for other people who are interested in this. After completing that I will work on the full screen mode.

Good work Malkom and thanks a lot