Recent

Author Topic: [SOLVED] [LAMW] jSQLiteDataAccess doesn't seem to create a database file  (Read 11117 times)

r.lukasiak

  • Full Member
  • ***
  • Posts: 138
Hi everyone,
I'm creating an app that needs Sqlite, I dropped jSQLiteDataAccess and jSqliteCursor onto my form. I set the database name and I fire:
Code: Pascal  [Select][+][-]
  1.  jSqliteDataAccess1.OpenOrCreate(jSqliteDataAccess1.DataBaseName)
no errors reported.
then I create a table using
Code: Pascal  [Select][+][-]
  1. jSqliteDataAccess1.CreateTable('CREATE TABLE.....')
no errors either but
Code: Pascal  [Select][+][-]
  1. jSqliteDataAccess1.Select('SELECT * FROM...;')
doesn't return anything.
Code: Pascal  [Select][+][-]
  1. jSqliteCursor.GetRowCount
returns 0.
Code: Pascal  [Select][+][-]
  1. jSQLiteDataAccess.FullPathDataBaseName
gives me /data/user/0/org.myapp.myapp/Databases. I checked the location and it doesn't exists.
I checked the demos and I didn't find anything different to what I'm doing.
I thought it may be related to permissions so the app asks for WRITE_EXTERNAL_STORAGE and it's grant.

Any idea what may be wrong?
« Last Edit: December 02, 2021, 10:14:47 pm by r.lukasiak »

dseligo

  • Hero Member
  • *****
  • Posts: 1180
Re: [LAMW] jSQLiteDataAccess doesn't seem to create a database file
« Reply #1 on: November 30, 2021, 04:09:17 pm »
After you create a table you need to insert data into it.

So, before you do "select * from ..." you need "insert into ... (..., ..., ...) values (..., ..., ...).

r.lukasiak

  • Full Member
  • ***
  • Posts: 138
Re: [LAMW] jSQLiteDataAccess doesn't seem to create a database file
« Reply #2 on: November 30, 2021, 08:58:17 pm »
yes, of course, I did it. I'm sorry I forgot to mention.
Code: Pascal  [Select][+][-]
  1. SQL.InsertIntoTable('INSERT INTO......);

Anyway even the database file is not created and it should after just creating a table.

dseligo

  • Hero Member
  • *****
  • Posts: 1180
Re: [LAMW] jSQLiteDataAccess doesn't seem to create a database file
« Reply #3 on: December 01, 2021, 02:17:08 am »
Did you set DatabaseName?

I tried this code in emulator and it works (I attached screenshot of result):
Code: Pascal  [Select][+][-]
  1. procedure TAndroidModule1.btnSQliteClick(Sender: TObject);
  2. var s: String;
  3.     i: Integer;
  4. begin
  5.   jSqliteDataAccess1.DataBaseName := 'my.db';
  6.   jSqliteDataAccess1.OpenOrCreate(jSqliteDataAccess1.DataBaseName);
  7.  
  8.   jSqliteDataAccess1.CreateTable('create table if not exists jedan (id int primary key, name text not null)');
  9.  
  10.   If jSqliteDataAccess1.InsertIntoTable('insert into jedan (id, name) values (10, ''dog'')') then
  11.     lvLog.Add('Row inserted.')
  12.   else
  13.     lvLog.Add('Row not inserted.');
  14.  
  15.  
  16.   s := jSqliteDataAccess1.Select('select * from jedan');
  17.   lvLog.Add('Res: '+s);
  18.  
  19.   i := jSqliteCursor1.GetRowCount;
  20.   lvLog.Add('Rowcount: '+IntToStr(i));
  21.  
  22.   s := jSQLiteDataAccess1.GetFullPathDataBaseName();
  23.   lvLog.Add(s);
  24. end;

dseligo

  • Hero Member
  • *****
  • Posts: 1180
Re: [LAMW] jSQLiteDataAccess doesn't seem to create a database file
« Reply #4 on: December 01, 2021, 02:24:34 am »
I tried this code in emulator and it works (I attached screenshot of result):

I tried this code in Galaxy S9 and it does work too.

r.lukasiak

  • Full Member
  • ***
  • Posts: 138
Re: [LAMW] jSQLiteDataAccess doesn't seem to create a database file
« Reply #5 on: December 01, 2021, 06:28:01 pm »
yes, I did. In Object Inspector and manually as well:

Code: Pascal  [Select][+][-]
  1. SQL.DataBaseName := 'mydb.db';
  2. SQL.OpenOrCreate(SQL.DataBaseName);
  3. if SQL.CreateTable('CREATE TABLE IF NOT EXISTS...') then showmessage('table created!') else showmessage('table not created');
  4.  
and I get the message "table created!", however no file is created in /data/user/0/org.myapp.myapp, there is no directory either
but when inserting:
Code: Pascal  [Select][+][-]
  1. sqlstr:='INSERT INTO ....';
  2. if SQL.InsertIntoTable(sqlstr) then showmessage('inserted!') else showmessage('not inserted');
  3.  
I get "not inserted".
now I come to think, maybe I have some syntax error. I'll try with some simple INSERT clause.

r.lukasiak

  • Full Member
  • ***
  • Posts: 138
Re: [LAMW] jSQLiteDataAccess doesn't seem to create a database file
« Reply #6 on: December 01, 2021, 06:48:26 pm »
it looks like I had some syntax error in the INSERT clause. Now it shows "database created" and "inserted" and then SELECT returns the right number of rows so it seems to be ok but... GetFullPathDataBaseName() gives me /data/user/0/org.myapp.myapp but it doesn't exist or at least I can't see it. Where else the file may be possibly saved?
I'm trying to use SQLiteManager to browse the db but I can't find the file.

I close the app, run it again and it still shows the inserted rows. Reinstalling the app apparently deletes the db because each another version I install starts with number of rows: 0.

I'm testing it on Nokia 5.4 which runs Android 11, in case it matters.
« Last Edit: December 01, 2021, 06:51:43 pm by r.lukasiak »

dseligo

  • Hero Member
  • *****
  • Posts: 1180
Re: [LAMW] jSQLiteDataAccess doesn't seem to create a database file
« Reply #7 on: December 01, 2021, 09:07:49 pm »
Code: Pascal  [Select][+][-]
  1. jSQLiteDataAccess.FullPathDataBaseName
gives me /data/user/0/org.myapp.myapp/Databases. I checked the location and it doesn't exists.

but... GetFullPathDataBaseName() gives me /data/user/0/org.myapp.myapp but it doesn't exist or at least I can't see it.

What do you mean by 'gives me'? How does it give you? Maybe the path doesn't fit on your screen?
Can you try my code (without modification)?

r.lukasiak

  • Full Member
  • ***
  • Posts: 138
Re: [LAMW] jSQLiteDataAccess doesn't seem to create a database file
« Reply #8 on: December 01, 2021, 09:37:25 pm »
I display it in a message after it creates the database:
Code: Pascal  [Select][+][-]
  1. showmessage(SQL.GetFullPathDataBaseName());

ok, I'll test your code.

r.lukasiak

  • Full Member
  • ***
  • Posts: 138
Re: [LAMW] jSQLiteDataAccess doesn't seem to create a database file
« Reply #9 on: December 01, 2021, 09:51:05 pm »
I've just tested your code and the effect is the same. It's supposedly creating the db, table and inserting a row but there is no db file in the given location.

dseligo

  • Hero Member
  • *****
  • Posts: 1180
Re: [LAMW] jSQLiteDataAccess doesn't seem to create a database file
« Reply #10 on: December 01, 2021, 11:25:07 pm »
I've just tested your code and the effect is the same. It's supposedly creating the db, table and inserting a row but there is no db file in the given location.

It creates db, it's just that you can't see it from some other app. It's probably Android's security or something similar.

Try this code and you should see your files:
Code: Pascal  [Select][+][-]
  1. procedure TAndroidModule1.jButton1Click(Sender: TObject);
  2. var sr: TSearchRec;
  3. begin
  4.   if FindFirst ('/data/user/0/org.lamw.applamwproject1/databases/*', faAnyFile, sr) = 0 then
  5.   begin
  6.     repeat
  7.       lvLog.Add(sr.Name + ' [' + IntToStr(sr.Size) + ']');
  8.     until FindNext(sr) <> 0;
  9.     FindClose(sr);
  10.   end
  11.   else
  12.     lvLog.Add('No files.');
  13. end;

r.lukasiak

  • Full Member
  • ***
  • Posts: 138
Re: [LAMW] jSQLiteDataAccess doesn't seem to create a database file
« Reply #11 on: December 02, 2021, 03:44:18 am »
That's what I was thinking, Android somehow blocks access to it. Anyway I don't really need the file, as long as the db works, I started looking for it because I couldn't have the db work. It just beats me where the heck the file is :D
I looks like the code you provided with neither can find the file. I added 3 rows and the fired your code.

r.lukasiak

  • Full Member
  • ***
  • Posts: 138
Re: [LAMW] jSQLiteDataAccess doesn't seem to create a database file
« Reply #12 on: December 02, 2021, 10:14:13 pm »
ok, it found it. The db is created from my app so it's named differently, I just copied your code as it is so it was searching a different location, my bad. Now I changed the search path and it's ok.
I still can't access the file from an SQLiteManager but at least I know the file exists and it's in tact :-)
Thanks a lot!

dseligo

  • Hero Member
  • *****
  • Posts: 1180
Re: [SOLVED] [LAMW] jSQLiteDataAccess doesn't seem to create a database file
« Reply #13 on: December 03, 2021, 12:13:02 am »
Np, glad it worked for you

 

TinyPortal © 2005-2018