SQLite extension (SPONSORED!)

database
extension

#1

PedrozaSQLite

Hello! Here I present to you my second extension ever created! It is an extension to access the local SQLite database!

Background

I know there is already a few extensions with this functionality, but here are the reasons why I made it:

  • First of all, thanks to AppInventor and all its distributions I took my first steps on how to code. Later, I thought about creating extensions and I found that I really like Java and the whole process of thinking and trying to run it correctly. So I took my chances and made my first extension that I published it here in Thunkable (Polyline Tools).
  • I knew that I wanted more, and decided to make one of the extensions I always wanted to have but couldn’t afford it.
  • My goal is to let other people like me use this extension, but always remembering the true meaning of AppInventor that is learning how to code. So if you feel like you can take the next step, I encourage you to try it. It’s really fun!
  • Also, by letting other people sponsor this extension I would have the opportunity to earn enough money to create my Developer Account.

So this is it! I hope you find this useful and if you find how to make it better, please let me know so I can learn from it!


For reference on how to write SQLite statements, I recommend for a good quick start TutorialPoint.com and SoloLearn’s SQL Fundamentals.


For other options availables:

  • Taifun’s SQLite Extension - Link (since Aug 11th, 2016).
  • Andrés Cotes’ SQLite Extension - Link (since Oct 2nd, 2017).
  • Juan Ruvalcaba’s SQLite Extension - Link (since September 8th, 2017).

##Description
Tool developed by Carlos Pedroza to access the application’s SQlite database based on SQLite Android Developer’s Reference.


FEATURES

  • SQL Statements executes asynchronously, very useful in large databases.
  • Good practices to avoid as possible SQL Injections.
  • Opportunity to use rawQuery and write SQL statements.
  • Compile a statement to reuse it later (on next release).
  • Query result in a list for easy use.

DISADVANTAGES

  • Not able to export and import databases as a file .sqlite (I don’t know how to do it, yet).

##Methods

  • AfterExecution: Event handler after the SQL statement is executed, returns whether the execution was succesfully executed.

  • AfterQuery: Event handler after the ExecuteRawQuery or Query is executed and returns a list with the selected data and number of records.

  • ErrorOcurred: Event handler when an error ocurred, returns a string with a message from the error.

  • Query: Executes pre-compiled QUERY statement with specified parameters. Parameters: 1) String table: Name of the table. 2) YailList columns: List of which columns to return, passing an empty list will return all columns. 3) String selection: Filter declaring which rows to return, formatted as an SQL WHERE clause, passing an empty string will return all rows. 4) YailList selectionArgs: List with the arguments that will replace onto ‘?’ in the selection filter. 5) String groupBy: A filter declaring how to group rows, formatted as an SQL GROUP BY clause (excluding the GROUP BY itself), passing an empty string will cause the row to not be grouped. 6) String having: A filter declare which row groups to include if row grouping is being used, passing an empty string will cause all row groups to be included. 7) String orderBy: How to order the rows, formatted as an SQL ORDER BY clause (excluding the ORDER BY itself), passing an empty string will use the default sort order (unordered). 8) String limit: Limits the number of rows returned by the query, formatted as LIMIT clause, passing an empty string denotes no LIMIT clause. Return: The result query is available in the AfterQuery event handler.

  • Insert: Executes pre-compiled INSERT statement with specified parameters. Parameters: 1) String table: Name of the table. 2) YailList columns: List with the columns that will contain the data to be inserted in the database. 3) YailList values: List with the data to be inserted in the database. Returns the row ID of the newly inserted row, or -1 if an error occurred.

  • Replace: Executes pre-compiled REPLACE OR INSERT INTO statement with specified parameters. Parameters: 1) String table: Name of the table. 2) YailList columns: List with the columns that will contain the data to be replaced in the database. 3) YailList values List with the data to be replaced in the database. Returns the row ID of the newly replaced row, or -1 if an error occurred.

  • Update: "Executes pre-compiled UPDATE statement with specified parameters. Parameters: 1) String table: Name of the table. 2) YailList columns List with the columns that will contain the data to be inserted in the database. 3) YailList values List with the data to be inserted in the database. 4) String whereClause: optional WHERE clause to apply when updating, leave an empty string to update all rows. Include ?s, which will be updated by the values from whereArgs. 5) YailList whereArgs: List with the columns that will contain the data to be updated in the database. Returns the row ID of the newly inserted row, or -1 if an error occurred.

  • Delete: Executes pre-compiled DELETE statement with specified parameters. Parameters: 1) String table: Name of the table. 2) String whereClause: Optional WHERE clause to apply when deleting (Example: ‘ID = ?’), pasing an empty a string will delete all rows. 3) List whereArgs: List with arguments for the WHERE clause. These arguments will be replaced by ‘?’ in the whereClause. Returns the number of rows affected if a whereClause is passed in, 0 otherwise.

  • SingleSQL: Execute a Single SQL Statement asynchronously and returns whether the transaction was succesful in the AfterExecution Event Handler. Use it when returned data isn’t needed. Parameters: String sql.

  • MultipleSQL: Execute Multiple SQL Statement asynchronously and returns whether the transaction was succesful in the AfterExecution Event Handler. Use it when returned data isn’t needed. Parameters: List of SQL statements.

  • RawQuery: Executes the provided rawQuery Statement asynchronously. Returns a YailList with the selected data and number of records in the AfterQuery Event. Parameters: 1)String SQL statement. 2) List selectionArgs: List with the arguments that will replace ‘?’ in where clause in the query, to prevent SQL injections.

  • GetPath: Returns the path to the database.

  • ClearDatabase: Clears the database to version 1. Use only while developing, this shouldn’t be used on production.

  • ReturnHeader:
    Returns whether the header row should be returned in the result of a Select statement.

    Specifies whether the header row should be returned in the result of a Select statement.

  • SupressToast:
    Returns whether Success Toast should be suppressed.

    Specifies whether Success Toast should be suppressed.


Best Practices

In order to avoid SQL Injections and use pre-compiled statements, it is a good practice to use this blocks to SELECT, INSERT, UPDATE, REPLACE and DELETE.

On the other hand, these block are useful for executing a single SQL statement that is NOT a SELECT or any other SQL statement that returns data (INSERT, UPDATE, REPLACE or DELETE). It has no means to return any data (such as the number of affected rows). Statements like CREATE or DROP table / trigger / view / index / virtual table, ALTER TABLE, REINDEX, RELEASE, SAVEPOINT and PRAGMA are very good options to use these block for.

When using rawQuery (using it a lot will cause performance issues because it compiles it over and over again) use the selectionArgs to provide the arguments that will replace ‘?’ in the where clause to prevent SQL injections. For example: SELECT * FROM table_name WHERE ID=?, the arguments in selectionArgs will replace all ?.


Price

This extension has been sponsored by @Peter_Mathijssen and @Diego_Marino! Thank you so much!

If you find this useful, you still can donate!
Donation Link: http://paypal.me/DrCarlosPedroza


Downloads

.AIX: com.pedroza.PedrozaSQLite.aix (23.4 KB)
.JAVA Available at: Github
Test .APK: PedrozaSQLite.apk (1.6 MB)
Test .AIA: PedrozaSQLite.aia (32.9 KB)


##Changelog

  • October 9, 2017 - Initial release.

UPCOMING:

  • More functions like CompileStatements in order to reuse precompiled statements.
  • Video tutorial :smiley:
  • Video tutorial on how to write SQLite statements

Best Regards from :venezuela:,
Carlos Pedroza


Local database to work offline
How to save files in the private directory?
#2

very good extension,better than others.

And I’m curious that your country’s wages are so low, graduated doctor only 5$/month?!

could you tell us which country are you from?


#3

I’m from Venezuela :venezuela:, this is because of actual currency exchange we are living here. Also, I´m doing work in rural zones, that is a requisite to start my postgraduate studies or to work in the private system. But don’t worry that much, life is cheaper than in other countries, what really is a problem is the high crime rates and food and medicine access is very low.


#4

Very inspiring!Please take care of yourself.


#5

i supported this extension, cause is great, and for the first time well explained.
ps, is there some way to import trhough an excel file?


#6

http://converttosqlite.com/convert/


#7

thx, i hope carlos will soon make an import tool…


#8

unlikely so I read in the link but import a database if possible


#9

Well done sir! You are an inspiration for other people who want to develop extensions and an example for a well done reference. Keep Thunking!


#10

Hi there…

First of all, thank you for the extension… i really need this for App Inventor.
Btw, i wanna ask, why cant i use / create other table than “phonebook”?

when i export ur “.aia” to AI2, it works well with companion and all. but when i try myself, even if its just change the word “phonebook” in your “create” statement, it would return -1.
why?


#11

+1 for a great extension, thankyou.


#12

Would you consider putting each of your PedrozSQLite.aia code blocks on here as a picture, until the reference is ready? I’d like to be able to look at them while I operate in my current project.


#13

Hello! I see you are new to the Community, welcome! This is a space to share ideas, questions, extensions, apps.

This thing you are asking you can do it by right clicking in the blocks viewer and select “Download Blocks as Image”

Here it is the result:

Happy Thunking!


#14

Sweet, thanks


#15

Quick question - can I use this extension with the iOS version of Thunkable?
I’ve been holding out for Google Sheets connectivity, but may just take this route instead.


#16

No, this extension is for the Android Version of Thunkable (was written in Java), also extensions for the iOS version is not currently possible.


#17

Wonderful, thanks Carlos!

Though, it’s freezing the app 3 solid minutes to insert 7783 lines from a 591Kb csv, so it’s a 3.283 Kb/sec on my Oneplus One using Thunkable live connection and local csv :frowning:

Finally it ends up in Thunkable Editor with this error message:

But it still inserted 7783 lines.

I use the same csv list in another app and it can be visible in a listview in less than 1 sec (I was surprised listview could handle 7783 lines).

So I guess, we need the function to import .sqlite into the app data.
BTW, mulitpleSQL button in .aia is throwing a Thunkable bug. So I can’t access screen2.


#18

Can you show your relevant blocks?


#19

OK.

global columns is simply the csv header as a list
global values is the list of values, same format as header.
Everything get inserted but it’s slow.

As you can see, I use a for each item loop, it’s pretty fast when simply reading records to a label text in place of initialize local ID (it only shows the last one though, like tbID.Text).


#20

Hello,

I still enjoy using this extension! I have an issue with increment and decreasing a counter in the SQL Table. I have figured out a solution only for setting the counter +1. Unfortunely, the decreasing does not work properly, it decreases -2 instead of -1. Also the SET command does only work in the raw query. Maybe one of you guys have an idea, how to solve this better.

cheers User81