Deleting all rows from a database table.

[Malky]

I seem to have this problem with only one table (so far).

If I want to update tables I use the DELETE * from tablename which has always worked before. (Sometimes at the wrong time, but we learn!)

I get an error with one table for some reason?

Has anyone experienced this? I pulled the table onto the computer and tried the same thing with sqllite and it locks up? I did try DELETE & from 'tablename' and DELETE * from tablename where 1 and many variations, but nope!

Cheers.

Malky

 

[edgar_ortiz]

The correct statement is:

delete from table_name

Regards

Edgar

 

[abner69]

one additional note, if you are deleting a lot of records (or just want to shrink the size of your database, execute this sqlite command:
vacuum;

...Pablo

 

[Mahares]

I had a SQLite database with one table having 20000 records with a size of 3.6 MB. I deleted all 20000 records. The size dropped to 290 KB without issuing any VACUUM command. When is it necessary to vacuum and when is it not? I have yet to see a firm answer to this.

 

[eps]

You may well find that it depends on the version of SQLLite that's implemented.

It's probably something that was an issue in the earlier versions, but they've now automated it.

 

[Malky]

I tried the delete method previously mentioned with no success. I resorted back to DROP table_name and then recreating it with no success?

I have a scrollview with a list of items. When I need to update this from my own server, I use the following code.

Sub CreateMenuTable
   Log("menu table created")
   If Started = 0 Then
      SQL1.ExecNonQuery("DROP TABLE IF EXISTS menus")
      SQL1.ExecNonQuery("CREATE TABLE menus (id INTEGER PRIMARY KEY, en TEXT, ru TEXT, bg TEXT, gr TEXT, icon TEXT)")
   End If
End Sub

Although I change the text live on my server and do an update, the scrollview still displays the same data minus any changes? I checked the database and it has the amended text, but after recreating the scrollview I still see the previous text even after the new query to the database?

Malky

 

[Malky]

Ok, I still have a problem with this after many hours of trying stuff and I can only assume that the primary key value is the problem after dropping the table?????

I run this code to update the records (and remove any that need it) when I try to recreate the table from scratch:-

SQL1.ExecNonQuery("DROP TABLE IF EXISTS diction")
SQL1.ExecNonQuery("CREATE TABLE diction (id INTEGER PRIMARY KEY, en TEXT, bg TEXT, ru TEXT, gr TEXT)")

I get a primary key error ever time. If I remove the 'PRIMARY' part of the query then I end up with duplicates?

Any ideas welcome.

Cheers,

Malky

 

[sorex]

not sure if sqlite supports it but the right command to empty (read trash) a table is the truncate command.

never used sqlite so the delete might call a truncate command aswell if you don't supply record id's or other "checks"

 

[Malky]

Thanks but I've tried various suggested sql lite sql commands but b4a doesn't like them.

Tried the delete option again, tried many other commands and all my tables get duplicate records if I remove the PRIMARY KEY part. If I leave the PRIMARY KEY, I get the duplicate key errors again.

From my searching the internet etc, the DELETE command should reset the auto increment value.

Really frustrating!!!!!

Malky

 

[Erel]

Thanks but I've tried various suggested sql lite sql commands but b4a doesn't like them.

Basic4android doesn't do anything with the query or command. It just passes it to the native SQLite engine.

 

[sorex]

Although I change the text live on my server and do an update, the scrollview still displays the same data minus any changes? I checked the database and it has the amended text, but after recreating the scrollview I still see the previous text even after the new query to the database?

so your feed your local sqlite database with data coming from a webserver?

how do you pull it in?

smells more like it is reading in a cached copy, that also has been an issue with Flash. The trick to use there is using an url like this

url="http://www.mysite.com/getdata.php?r=" & r

where r is a random value or time ticks value used to prevent reading in cached info.

if it then still doesn't update right it's something on the sqlite side,
can you dump the json output to the sd to see it it pulls it in right?


Edit:

just had a look at one of my apps that also pulled in data from a webserver and the command

req.InitializeGet

works fine without the additional random trick, not sure if you use that aswell?

 

[Malky]

I'm passing the arguments as they should be, it's just nothing is happening?

I've tried to DROP the table and recreate it, no result.

I've tried DELETE FROM tablename, no result.

I've tried DELETE FROM tablename WHERE id > -1, no result?

The only way I can add the data is if I remove the PRIMARY key on the id column, but then I get duplicate rows.

The Scrollviews are cleared and recreated, plus I have pulled the DB from the phone and opened it and there are duplicate rows, so it's not the lists?

This is driving me crazy as I've been at this for night's on end now.

Malky

 

[sorex]

that's strange indeed.

there are other posts about issues but those were errors due to file locks or something

 

[Malky]

The only possible option I can see is to get a list of existing 'id's for each table and updating where a row exists, or inserting if it doesn't, but it seems a lot of code to get round something that should be simple and apparently works?

Malky

 

[edgar_ortiz]

Malky,

Post your project.

It's help to find the solution.

Regards

Edgar

 

[sorex]

Is this only to feed a listbox?

Why not just read it in from the website and copy the http response to the app folder just in case there is no internet connection?

you won't notice a difference fetching it from a local database or looping throught the offline copy or the http response. and you can keep all your different listbox data in 1 response/offline_copy to make things easier.

 

[Malky]

Hi as requested, I'm adding the code in case someone can help?

Please don't use any of the contact options as they are live!!

Basically, I start the app, select the language and when presented with the menu, I select 'Update' to emulate someone trying to update for any new data each week or month etc.

It's when I try the update that I get the problem with duplicate scrollview items.

The main reason for doing it this way is to allow it to be used offline because of the area coverage here.

Cheers,

Malky

 

[sorex]

It's when I try the update that I get the problem with duplicate scrollview items.

did you forget to clear the scrollview/listview content before adding the others?

 

[Malky]

I've never had to before?

Some of the scrollviews are repopulated each time a previous srcollview (scv) selection changes.

i.e. if I select item 1 from scv, then the next one contains only relations of item 1.
If I go back and select item 4, then the list appears with only items related to item 4.
At the end of the day that wouldn't matter anyway. After checking the db after an update attempt, it has duplicate entries!

Malky

 

[sorex]

I'm missing some libs so I can't test it untill I have those.

Can you give some hints on this?


Edit:

what happends if you put

Started=0

in Sub Update right under the Log("Update")