Yet another SQLite extension (Free)

extension

#22

in the listpicker after picking event send another query to the database like this

SELECT wtr FROM Sheet1 WHERE Name_en = Listpicker.Selection

Taifun


Trying to push the limits! Snippets, Tutorials and Extensions from Pura Vida Apps by Taifun.


#23

thank you @Taifun
i tried this


but the result value is ()


#24
  1. don’t use the green = block, just write the = inside the text block
  2. always quotify your values, in your case the Listpicker.Selection
  3. use Do it to check your sql statement, see tip 4 here https://puravidaapps.com/learn.php

Taifun


#25

Rather than build a SQL statement, you could instead use the Select block like you’ve already used. Change the “columns” value to a list with just “wtr”, set “where” to “Name_en = ?”, and set “bindParams” to a list with List_Picker1.Selection.

In either case, you probably don’t want the output of the select to go directly into the text field since it’s a list (even with a single value). You probably want to get the first element from the result and put THAT into your text field.


#26

hi 1115
your sql is not formed properly…
try these block

HIH
Lildinti


#27

I’m back with another query. Please help me in resolving this issue.

My test app contains a text box and a button. Uploaded test.sqlite3 to the media folder. test.sqlite3 contains two coloumn
1 id(primary key, integer with autoincrement)
2 word(text)

There are two rows of data in the table:
1 apple
2 orange

This is the only block in the block editor. If I remove BeginTransaction and CommitTransaction block, my data is getting inserted properly. My results:

sqlite
sqlite database imported

ok

sqlite
sqlite database opened

ok

sqlite
sqlite database created

ok

sqlite
sqlite database closed

ok

sucess
(aple,orange,fig)

sqlite
sqlite selectsql: 3 rows

ok

sqlite
sqlite insert test id=3

ok

But if I use the BeginTransaction and committTransaction as shown in the above picture my app is frozen and not responding. I need to close the app.

How to save the data and avoid freezing of app?


#28

it works :grinning:


#29

hi @Karthikeyan_M
Haven’t done any work with transactions but by having a look at your blocks they seen ok. Maybe @frdfsnlght may have some idea why is failing?
In transactional SQL you usually have a begin transaction, a commit/rollback and also an end transaction statement.
I don’t see an End Transaction block in the extension, though. Maybe is not necessary?
@frdfsnlght is that correct?
Cheers
Lildinti


#30

No end transaction. Commit/rollback implicitly end the transaction.

@Karthikeyan_M Strictly speaking you don’t need the Begin/Commit around a single insert statement. If the Insert doesn’t work, it’s “rolled back” implicitly since it, uh, didn’t work.

However, it should work with the Begin/Commit also. I’m wondering if the dialog box is somehow interfering with the threading the extension is using. When I get a chance, I’ll reproduce your blocks and do some testing.


#31

@1115 I’m glad you got it working. However, you seem to be doing some horrendous string manipulation to get the result from a list. Why don’t you just use the “select list item” block with an index of 1?


#32

I tried without begin/commit and got a message that the value is inserted. But the data is not getting saved in db file. Any other suggestion?


#33

i don’t know how to use it :disappointed_relieved:


#34

Sir how to create sqlite file and import pls give examples


#35

download sqlitestudio from

https://sqlitestudio.pl/index.rvt?act=download
uske baad aap esko insal krke jo chahiye vo kro


#36

Or use something online like SQLiteOnline. Just execute SQL to create what you want, then File->Save DB will download the SQLite database file to your dekstop. Then upload as a media file in the builder.

You can see how to import the DB in the previous posts.


#37

@Karthikeyan_M During my debugging, I also removed the Begin/Commit transaction blocks and it works as expected. The data is saved. Perhaps something else is wrong?


#38

Hello guys,
Well I’ve been playing around with SQLite and doing a bit more digging about t with google and according to the SQLite docos, every statement that modifies the database, generates (implicitly) a transaction and is a design feature in compliance with SQL standards.
Someone questioned online the merits of using a transaction with single statements (a very common practice in SQL commercial development) and the answer was that is mostly used as a precaution if your database uses triggers. That said, triggers ARE run in transactions anyway, so is kinda superfluous.
Additionally, according to the SQLite docos, the COMMIT TRANSACTION statement IS an alias of the END TRANSACTION. Therefore no END TRANSACTION block is necessary.
What I would suggest @Karthikeyan_M, is use the SQLite.SQLError block and see what it comes up.
@frdfsnlght, can you check this is done in the extension?


Cheers,
Lildinti


#39

@Lildinti Yes, that’s how the extension works (you’re welcome to look at the source). The CommitTransaction block does a setTransactionSuccessful, then an endTransaction. The RollbackTransaction block just does an endTransaction.

I know transactions work in the extension based on the test app I wrote. They work as they should for both committing and rolling back.

I believe the problem @Karthikeyan_M is experiencing is a threading/deadlock issue (no fault of her own), perhaps because all the blocks are executed in rapid sequence. Those kind of problems are very tricky to debug, hence my delay in providing a solution yet.

I’m busy with some other things at the moment, but I will eventually get to dig into the problem.


#40

I tried the .SQLError block, unfortunately the frozen app is not displaying any error message.


#41

@frdfsnlght I removed sqlselect, open database, close database block in order to prevent the deadlock issue. But the issue persist and app is still not responding. Without the begintransaction/commitTransaction the data is getting saved but when you close and re-open the app it is not showing the saved data. May be data gets rollback to its original state. Have you had a moment please look into this issue.