Problem INSERT sqlite

[Simona Meli]

hi, i can't insert the data in the table of my db. the insert method always gives me -1.

The database is open, the table exists, but no data is loaded, do you see any errors in the code?

I can't understand if it's a sql extension problem I'm using or if it's my problem ...

thanks to those who will be able to help me

[Ghica]

What does the definition of the table you are trying to insert to, look like?

It may be that you are trying to insert string variables without "" around the values, for example.

A way to debug your app is, to change the local variables into global ones and then, after you tried to insert, drag a get of that global variable to your work space, click with your right mouse button and choose DoIt.

You may be surprised at what you see. You can also try to build an insert statement by hand, and see what works and what not.

For more detailed help we would be interested in an .aia and a schema of your table.

Cheers, Ghica.

[Simona Meli]

Hi Ghica,

 

I also tried a table with only one field, but it always gives me an error and I don't understand why. I send you the images of the tables in which I would like to load the data Load and Detail and the age you requested.

 

I don't understand why I manage to save locally with tinyDB, on the Web with TinyWeb, but Sqlite absolutely not ...

I can get the data from the Db, but insert it no ... For insert data in sql the statment is (‘value1’, ‘value2’, etc…) how can I transform my list? with a join? how can I set the () only at the beginning and end of my list?

sorry, I have so many ideas, but they are a bit messy

 

 

Simona

[Ghica]

Hi Simone,

Looking at your blocks, there must be several things wrong. See my picture.

I do not know which SQLite extension you are using, it is not Taifun's extension I think.

So, what does the SQLite.Insert block expect? For the columns:

*  A real list as in App Inventor? Then, just the make a list block should do the job

* or, a comma separated string? Then you could use list to csv row, because you already have a list and now you want to make a comma separated string of it. 

BUT, I am not sure, because you did not tell what the block expects.

A similar question for the values: 

* Does the values parameter of the extension accept a list?

* Does it put quotes around the values if necessary?

In any case, your values will not be accepted.

Please tell more about the SQLite extension!

Cheers, Ghica.

[ABG]

I see another potential problem in your table definitions.

Many of your fields are foreign keys.

In order to insert a record with given foreign key values,

the target tables for those foreign keys must already have

records in place with those key values.

If you post your blocks, we can look to see if you are

inserting records in the proper order, 

Alternatively, relax those foreign key constraints

temporarily to see if that offers relief, then clean up the

mess.

ABG

[ABG]

Also, here is an extra question ...

(I do not have the answer to this, I have

only done read-only SQLite access so far.)

Are there any directories in Android that are

read-only, maybe like the Media folder,

that would be unfortunate places to put the SQLite database file?

ABG

[Ghica]

@ABG, the SQLite database is built-in, but there are no built-in blocks in App Inventor to access it, as for the TinyDB. But, there are a few SQLite extensions that can address the SQLite database, each with it's ow API.

I am only familiar with Taifun's extension, but that is not the one Simona is using, because it does not have the block he is showing. Therefore there is not much we can advise before we know what API to use.

Your observation about the foreign keys is very adequate!

Cheers, Ghica.

[ABG]

@Ghica, in my experiment with the Bennedum extension at

https://groups.google.com/d/msg/mitappinventortest/LwJVNkyvPgU/vTaelFfzFgAJ

it allowed me to specify a file location for the database file, in this case

in my Screen1.Initialize block.

Maybe that's something relevant.

ABG The Adequate

[Ghica]

@ABG, that looks like a really nice, but rather advanced extension! 

Maybe that is the one Simona is using, because it offers the "insert table columns values" block.

After some searching it appears that you can indeed have more than one database in an app (like you can also have two TinyDB's today), but it is a bit confusing. In Taifun's extension you can import a database from (exported) SQL statements, the bennedum extension also offers import from binary files containing a complete database it seems. 

@Simona, are you indeed using the bennedum extension?? 

I will make a small example.

Cheers, Ghica.

[Simona Meli]

I create the DB with sqlite studio and Django, in MIT app inventor I haven't tables. With app inventor I open DB, select utentes from list picker, and do login. If you want see my tables I take a photo

[Simona Meli]

I have many problems with foreign key in Django... 😩

[Simona Meli]

Yes I use it! I don't have many experience and I take it from an internet search

[Ghica]

Now I am even more confused. If you are using Django, what is the role of your app Inventor app?
And, even more important, if you use an SQLite database on your phone, your data will only be accessible to the user of the app personally.
Do you need to share the database with others?
Cheers, Ghica

[Simona Meli]

This is a project for track the igp lamb. With the app i read the rfid of lamb, and i want to store this code in my db and want to see this movimentation on my web page. Every utentes has his page, with personal data, company data and his rfids.
It is possible to do with mit app sqlite?

Ps. Thank you so much for your help!

[ABG]

What kind of lamb is an igp lamb ?

ABG

[Simona Meli]

When the lamb reaches the slaughterhouse within a certain number of hours, it respects the parameters of weight and health, making it conform to IGP = DOC

[Simona Meli]

And it must be only a Sardinian lamb!

[Ghica]

Hi Simona,
Thanks for the explanation! Some day I should go to Sardinia to eat igp lamb.
Maybe you could explain in your own language, Italian I suppose, what your app should do. I know enough Italian to understand it and otherwise (also for ABG) there is Google translate.

So far, I am not sure that you would want or need an SQLite database on your phone. Probably you need a database on a website that you are building with Django. That could be SQLite or MySQL, or you could do something completely different using CloudDB or Firebase.

So, there is company information that everybody needs to see. Does it change? If yes, how often?
Then, there is personal information. About you and about the lambs you are handling. Do you need to gather your information and that of your colleagues in a central place, so that everyone can see the information about all the lambs? If not, what do you do eventually with the information on your phone?

Probably you need a solution where you have an online database which you and your colleagues can access from your phone. The phone part you can certainly do with App Inventor, the Web part you are planning to do with Django. Personally I have never used Django and my Python knowledge is very rudimentary, so I am not sure how much I can help with that.
Cheers, Ghica

[Simona Meli]

The Platform web is for the entity consorzio, who verify payments and offer services for every farmer in sardinia, it is a supervisor for igp with asl. The app is for the conveyor who deliver lamb to the slouterhouse... my boss tell me use django, use mit app inventor, but he isn't a computer scientist! For now the project is a prototype...
I have a question: with sqlite in app inventor can i insert data in db? Is it possible?
If the project evolves, with db you advice to use? Thanks

Simona

[ABG]

The SQLite in Android devices has no connection to

external server based copies of SQLite.  It is only

able to store data in the Android device.

There seems to be a web front end in Django that you

might be able to talk to using the AI2 Web component.

See the Web Services section of FAQ

https://sites.google.com/view/ai2-faq-view/home

for examples of talking to other kinds of servers on the Web.

If you still see value in keeping data in phones in SQLite,

please post an English version of your SQLite schema

and your exported .aia file, along with a Rosetta Stone

ABG

[Simona Meli]

Ciao, come richiesto vi scrivo in Italiano...

nel sito web creato con Django l'utente consorzio visualizza tutti i dati delle aziende, quindi dati personali dei titolari e dati riguardanti i propri allevamenti e quindi gli agnelli. Ho predisposto inoltre una maschera per gestire i trasportatori e una per gestire i macelli (che non esistono ancora nella procedura odierna di gestione del marchio IGP).

Dal punto di vista dell'applicazione invece, seguiamo l'operato del trasportatore (che è la procedura che vorremo automatizzare con questa app), in cui lui sceglie l'allevamento di prelevamento dei capi, il macello di destinazione, l'orario di partenza del trasporto e poi legge, tramite il nostro "prototipo fisico" tutti gli rfid che sono assegnati ad ogni singolo agnello, nel mentre che vengono caricati sul camion che li porterà al macello. Tutto ciò per tenere traccia di ogni movimento dell'agnello affinchè le fasi della catena di monitoraggio vengano correttamente rispettate.

Io ho un DB Sqlite in cui mostrare tutti i dati presi dall'applicazione e un Tiny DB per salvare i dati in locale in modo che se mancasse la connessione internet per il trasferimento l'invio si potrebbe effettuare successivamente col pulsante Salva DB. (essendo un prototipo alle prime fasi, il controllo della connessione non l'ho ancora impostato)

Questo sotto è il link al mio drive contenente il file .aia dell'app, dovrebbe funzionare... 

https://drive.google.com/file/d/1o9byKVR2VZPHAYt8NXYsufPN3GqT6Lmj/view?usp=sharing

vi allego il pdf delle mie tabelle, i dati sono inventati da me, giusto per poter controllare che funzionassero correttamente col sito e con l'app.

Grazie mille a tutti, siete stati davvero gentili!

Simona

[Ghica]

Hi Simona,

Thanks for the .aia 

I am looking at it. I have many questions and there are problems.

1. As you said, on Screen1, there are fake userid's and passwords, but if they were real, this would not work. You can login to the local database, but not to your Django webserver. 

It is good practice if the user has logged in, to save the userid and password in a TinyDB, such that when the user opens the app again, he does not need to login.

Anyway, I found a fake userid and password and in this way I could continue to screen Trasportatore.

2. Apparently a button disappeared here. It is not a good idea to leave red crosses around.

3.

Never open Screen1 in this way. Use close screen instead!! Else you will have many problems later.

4. Here it seems that you expect your rfid to hve been read just after you sent the text.

That cannot be true, because you are reading it (and that is the correct way) in the Orologio1.Timer block.

What you should do, is enabling the timer (and disable it in the designer). when you read the rfid, do what you need to do and disable the timer again.

5. If you are storing the rfid in the TinyDB and want to add new rfid's later, you first need to read the rfid list from the TinyDB. And how are you saving the data and time??

6. The DatePicker.GotFocus will not help you in setting a specific date. This block is useless in this way. What did you want to do?

7. Trasportatore.initialize. You are trying to use a list of allevatore and utent, but these will always be empty here. What are you trying to do? As a result of your query, the macello elements look like this: ([9, macello1] [10, macello2] [11, macello3] [12, macello4]). Is that what you need?

8. Why do you have PolylineTools, PedrozaSQLite, SQLiteAddons and the SQLite extension you are really using? This is very confusing.

I am still studying Django, to be able to give you better advice. It will probably like: Do not use SQLite on your phone, but save the values you collect in a TinyDB, then at certain points send this data to your Django website. How to do that I do not know yet,

Your initial question: can my SQLite DB on the phone communicate with the Django website, here the question is NO. 

Be patient, I will study Django a bit more, but correct your app problems in the mean time.

Cheers, Ghica.

[Fabrizio Setini]

ciao Simona,

ho realizzato un'App che doveva lavorare in maniera simile alle tue esigenze

non conosco Django, ma posso riportare le linee di funzionamento e i mezzi utilizzati

Scopo della App:

La App doveva memorizzare sullo smartphone del dipendente il Rapportino Lavoro Giornaliero con dettaglio di orario e lavorazioni effettuate

La App doveva controllare in automatico la connessione Internet e se NON presente permettere di continuare a memorizzare in locale

Dato un intervallo di tempo definito la App previa connessione Internet attiva doveva scaricare i dati dallo smartphone al server e caricare sullo smartphone eventuali aggiornamenti degli archivi di base necessari per il funzionamento della App

In caso di mancata connessione riprovava in automatico al prossimo utilizzo della APP

Risorse utilizzate

Scelto SQLite come database dell'applicazione sullo smartphone

Scelto file in formato .CSV per trasferire i dati verso il server e dal server verso smartphone per aggiornare archivi sulla smartphone

Scelte ESTENSIONI TAIFUN SQLITE e TAIFUN FTP per gestire le operazioni sui dati

Tutto ha funzionato come desiderato

Ti allego copia dei blocchi utilizzati per una espressione INSERT

Spero di essere stato utile

Saluti Fabrizio

[Simona Meli]

Grazie mille, sei stato troppo gentile! Spero di riuscire a sbloccare la situazione grazie ai tuoi blocchi... per sqlite non c è molta documentazione in gir ne esempi esaustivi! Grazie davvero!

[Simona Meli]

Ciao Ghica, rispondo alle tua domande:

1. Su le user e password fittizie hai ragione, ma mi servivano per provare l'applicazione e verificare che il login funzionasse correttamente. Avevo impostato anche una pagina di registrazione, ma ho ritenuto che aggiungesse troppa complessità al prototipo (non è detto che il progetto venga finanziato e poi prodotto). Grazie comunque per i consigli che mi saranno utilissimi in futuro.

2. il bottone disconnetti era presente nella prima relise, ma l'ho eliminato dallo screen perchè disconnettendomi mi creava problemi con il dispositivo che legge rfid

3. Potresti specificarmi quale blocco usare per passare allo schermo successivo? Sinceramente io non ho mai creato app e siti prima, quindi tutto è nuovo per me, 

sto lavorando in questa azienda tramite una borsa di studio, quindi sono qui per imparare.

4. il nostro lettore rfid sta su una raspberry py, che dopo aver ricevuto uno 0 legge il codice, e successivamente invia il dato alla mia app via bluetooth

5.Hai ragione, io sto salvando solo gli rfid sul tinyDB, perchè quando ho provato a inserire data e ora non mi eseguiva il controllo del rfid già presente. 

6. Con i blocchi datapicker vorrei mi venissero selezionati data e ora corrente dell'operazione

7. Vorrei poter selezionare dalla lista allevatore e macello, gli utenti che sono coinvolti nell'operazione trasporto. Ora io ho le liste estratte dal DB, e selezionando i dati vorrei poterli salvare nuovamente sul DB ma sulla tabella carico.Gli rfid letti invece andrebbero nella tabella Dettaglio.

8.Ho caricato tutte le estensioni che ho trovato per vedere quale potessi usare per il mio scopo, comunque ho acquistato la taifun ieri, e ora ho anche quella, magari mi può aiutare.

grazie mille per il tuo tempo

Simona

[Ghica]

@Fabrizio, Wow! This must be the longest block I have seen in a long time, maybe ever. I am sure that you can shorten it by two-thirds by adding one or two procedures. BUT, if it works, it works! Never change a winning strategy.

@Simona, Since you want to switch to Taifun's SQLite extension, start with removing ALL others. The interface of Taifun's SQLite extension is a bit simpler than what you are using now, for example it does not have a bindParams possibility.

You could use Fabrizio's strategy, which is storing your data into the local SQLite database on your phone and then, when you connect, export the data as CSV files and send it to your website.

As for point 4, I understand now that you are using a Rasberry Pi, but that does not change that you should read the data as I said, using a clock, and only there do something with it, NOT in when leggiRFID.

As for point 5: Why would you save the RFID in a TinyDB at all, if you are also saving it in your SQLite database? (To be clear, you do not need an internet connection here).

When you have updated your app, post a new version! In the meantime I am still looking at Django.

Cheers, Ghica

[Fabrizio Setini]

Ciao Ghica

effettivamente realizzando il blocco avevo qualche perplessità sulla velocità di AI2, ma funziona bene e molto veloce.

Penso che le procedure devono essere utilizzate laddove servono istruzioni ripetitive e con trasmissione di parametri in input e risultati in output.

Nel blocco specifico non ve ne era necessità

saluti Fabrizio

[Alberto Filippetti]

Ciao Simona , ho bisogno di una app (a pagamento) simile a quella che stai sviluppando ma molto piu semplice, vuoi svilupparmela ?

questa la mia mail albertofi...@gmail.com