Update Sqlite Database in my app

:uk: :us:
hi i am developing an app and i use sqlite.
My application requires to work without internet, that is why I have it locally and then when there is internet I send the database to a google drive.

When I start the application I verify connection and download the database, this database must be joined with the existing one (It may contain different data).

I tried to import the downloaded database into a sqlite2 and rename the tables and then import them into the sqlite1, but Importing does the database byte-for-byte, which I can’t add another table and then do:

INSERT INTO table SELECT * FROM new_table

Problem:

  • When importing, entering record by record takes too long. How can it be done optimally?

I use the org.bennedum.SQLite.aix library

regards


:ceuta_and_melilla:
hola, estoy desarrollando una aplicación y uso sqlite.
Mi aplicación requiere de funcionar sin internet, es por ello que la tengo de forma local y luego cuando hay internet envio la base de datos a un google drive.

Cuando inicio la aplicacion verifico conexion y descargo la base de datos, esta base de datos debe ser unida con la que existe(Puede contener datos diferentes).

Intente importar en una sqlite2 la base descargada y renombrar las tablas para luego importarlas en la sqlite1, pero Importar lo hace byte-for-byte la base de datos, lo que no puedo incorporar otra tabla y luego hacer:

INSERT INTO table SELECT * FROM new_table

Problema:

  • Cuando se importa al ingresar registro por registro tarda demasiado tiempo. ¿Como se puede hacer de forma optima?

yo uso la libreria org.bennedum.SQLite.aix

Desde ya muchas gracias

The query is in English and Spanish too

1 Like

Hi. I have been trying to solve this problem for 10 days. On some smartphones ( 1:30 minutes ,15000 records )the message “close app” or “wait for app” is displayed I’ve already used this aix you mentioned. did not solve
This aix does not work with the native Sqlite block. The two together generate an error

Tested Kodular native blocks Replace It didn’t. I created a unique app and created 3 types of Insert + Update (on Conflict) I put a stopwatch to be able to evaluate One of these options, made the Insert in 28 seconds, but the app closed by itself I used LOGCAT and it showed me a syntax error (I used the same command in blocks and on the Sqlitonline site - on the site the command is executed correctly and Kodular NO) YESTERDAY, I didn’t work because I need to rest. I need to know why is getting a syntax error.

I will also test these commands in the bennedum extension … because I only used the ready-made blocks that the extension has

This message is not for you @Pipo not even for you @Still-learning

Developing useful apps isn’t just about piecing together blocks. … It’s work, research and test…

Twelfth day of testing…

I have a mysql database that I look for this information and insert + update in Sqlite.
Test it out with Insert or Replace Into SQL command.
erroron
:point_up:
Syntax error in SQL command On Conflict Do Update still persists on Kodular… (on sqliteonline site it works)

You can fix it with an attach from the database.
What is your query that results in an error?

???

Insert Into On Confict Do Update

The other command I’m trying is Insert or Replace Into (which exists as a native block - but I did it manually and it’s faster - I don’t know why) , theoretically it’s reducing the time by 1/3.

My problem consists of not being able to import and merge two databases, since it gave me that error of it stopped or it takes a long time, about 8000 records, since it was traversed 1 to 1, I leave my solution below

Apply an ATTACH

attach database 'File.sql' as toMerge;

this makes that you can call a table from another database, and that you can use the following command to import quickly

INSERT INTO name_table SELECT * FROM toMerge.name_table 

Then you must unlink the database to avoid generating errors

DETACH DATABASE 'toMerge';

It should be clarified that in the tables I have the primary keys well defined

I do not know if this helps you, I am not a database expert but I have a little knowledge if I can help you with something … ask me

This command is summarized?
Because it would be missing which elements you want to insert in the table

No… these are the main parts… logically, it has the field names and the values + the primary key name that is in conflict + fields name to UPDATE.

Yes of course…

My data comes from an API, in JSON… web component…

So post resolved. :+1:

could you pass me the complete command?

After the UPDATE do you have a SET Column-name = Expresions?

Yes, :+1:
:point_down:

:point_down:
https://sqlite.org/lang_upsert.html

1 Like

yes, i was seeing that

I would not know how to help you

1 Like

This topic was automatically closed 30 days after the last reply. New replies are no longer allowed.