sqlite extention and autocomplete extention

[ibrahim bikdashi]

Hello

 i am using this extension

https://github.com/frdfsnlght/aix-SQLite

i used import method and after selecting the data the output is going as the below
[table name, Value]

how i can change the out put to be only value ?

can you advice me with a good extension to use autocomplete  for 3500  record in sqlite?

check attachment

                                  

[ABG]

If you are going to grab the entire table contents into memory,

here is a sample app to do autocomplete in blocks ...

https://docs.google.com/document/d/1SakUBoYhbGbH4UtYARNgYoRAAO_dtpLyVp4Jx3Bri2k/edit?usp=sharing

If your table is too big to fit into memory, look into the

various SQL SELECT query filter subclauses:

  DIstinct

  Like

ABG

[ibrahim bikdashi]

Hi ABG

how i can use where clause

check the block because it didn't work

[ABG]

I have not used this extension yet.

If you export your project .aia file and post it

online in a shared location (like google Docs),

we can try to play with it to see where you went wrong.

(This board can't accept .aia files with extensions.)

Post your shared link in this thread.

ABG

[ibrahim bikdashi]

Hi ABG

please check the attachemnt link

https://drive.google.com/open?id=15rS8AbosudCoDqZoU4N_v1vVuwVUeNW9

u can check the aia i did 2 way in screen 3 one is working and the other not .

thank you

[ABG]

Thanks for letting me play with your project.

I have attached snapshots of points of interest,

so that other people on the board can easily 

see and comment.

In Screen1 and Screen2, you did not include in the media

drawer the .sqlite file you tried to open at

screen initialize time.

On the app level, you have duplicate extensions with overlapping

functionality, both for SQLite access and autocomplete.

That leaves your app open to unwanted interactions behind the scenes.

Keep it simple, and unit test, one extension at a time.

The SQLite extension (non-Pedroza) failed to open its

database, emitting a task-related error.

I suggest following up with the original author,

if he is available.

Many extensions for AI2 are flashes in the pan,

coded by first-time Java coders.  Also, Google 

works continually to change the Android OS, 

breaking old code.  If your free extension author has abandoned it,

you are out of luck.  (You get what you pay for.)

From what I see of your app, you don't need a full database.

A suitable pre-processed lookup table could be loaded from Media

using a File component, and either try it with

an autocompletion extension (good luck),

or your own lookup-in-pairs block under a Clock Timer.

ABG

[ibrahim bikdashi]

Hi ABG

thank you for helping and replying

1- may be i didn't mention this is not my real project this aia is for testing so i created multiple screens to test which design is working.

2- i didn't include the big database in the attachment because it is confidential data so in screen 1 and screen 2 it will not work because the database isn't attached.

3-maybe i didn't mention screen3 in my last reply to check it because it contains small database for testing and the issue that i was asking about filter before display.

since you checked all screen and u test screen 3 can you check the how i can create auto compleate without load all the data base in list view ? orhow i can use "where like" condition?

[ABG]

can you check the how i can create auto compleate without load all the data base in list view ? or how i can use "where like" condition?

Let's look at the SQLite syntax for the WHERE LIKE clause:

Home / SQLite Tutorial / SQLite Like: Querying Data Based On Pattern Matching

SQLite Like: Querying Data Based On Pattern Matching

Summary: in this tutorial, you will learn how to query data based on pattern matching using SQLite LIKE operator.

Introduction to SQLite LIKE operator

Sometimes, you don’t know exactly the complete keyword that you want to query. For example, you may know that your most favorite song contains the word,elevator but you don’t know exactly the name.

To query data based on partial information, you use the SQLite LIKE operator in the WHERE clause of the SELECT statement as follows:

1 2 3 4 5 6

SELECT column_list FROM table_name WHERE column_1 LIKE pattern;

Note that you can also use the LIKE operator in the WHERE clause of other statements such as DELETEand UPDATE.

There are two ways to construct a pattern using percent sign % and underscore _ wildcards:

1. The percent sign % wildcard matches any sequence of zero or more characters.
2. The underscore _ wildcard matches any single character.

The percent sign % wildcard examples

The percent sign s% matches any string that starts with s e.g.,son so, etc. The %er pattern matches any string that ends with er e.g., peter, clever, etc. And the %per% pattern matches any string that contains per such as percent, peeper, etc.

So in raw SQL your SELECT might be

 SELECT HHH from NANDU WHERE HHH LIKE '%brah%'

to search for HHH values containing brah anywhere within them.

(I chose this pattern because it would catch both Ibrahim and Abraham)

The % character is the match against anything.  If you want to just

do prefixes, just use the trailing %.

I don't see the advantage in using a bind parameter in a dynamic environment like this.

Use the AI2 text JOIN block to build up the WHERE clause:

JOIN(

   HHH LIKE '

   textbox.Text

   %'

)

This is a 3 way JOIN block, and the ' marks are necessary for the SQL generation.

ABG

 

[ABG]

By the way, you should check if you exceeded the

AI2 size limit (10 MB) when you upload your data.

That would change your startup on first run

to load the data then instead of building it in.

ABG

[ABG]

Personal commentary:

I have yet to see a case where an AI2 SQL SELECT block made things easier

or simpler compared to just building up your own SQL SELECT statement using text JOINs.

That covers Fusion Tables and all SQLite extensions.

Building up your own Sql statement gives you a place in your

blocks where you can apply a Blocks Editor Do It to see

the raw SQL, something you can't get from a SELECT block.

It also relieves you of having to worry about list representation choices

for each parameter.

ABG

[ibrahim bikdashi]

hi Abraham

thank for your reply i will try sql query .

 as i understood from your comment maybe select block maybe will not work with where caose .

if didnt work can does auoto complete have away to not load all the database at the first time ?

(what i mean the application freeze for about 3 min to load all the data from the select query then it give back the result can give any other idea can work with freezing the applicaTION)

[ABG]

as i understood from your comment maybe select block maybe will not work with where clause .

It probably would work, just be ugly.  Here's how to feed the SELECT block:

Leave the Bind parameter empty, since you are feeding the WHERE parameter directly.

 

Use the AI2 text JOIN block to build up the WHERE parameter:

JOIN(

   HHH LIKE '

   textbox.Text

   %'

)

This is a 3 way JOIN block, and the ' marks are necessary.

 

if didnt work can does auto complete have away to not load all the database at the first time ?

(what i mean the application freeze for about 3 min to load all the data from the select query then it give back the result can give any other idea can work with freezing the applicaTION)

 I think you might be able to avoid a big load if you build your table on your desktop pc

ahead of time with a clustered index on column HHH, no rowid, according to

https://www.sqlite.org/withoutrowid.html

then upload the pre-built .sqlite database file to Media.

This should reduce your .sqlite file size because the table will live in a b-tree

and it should speed up search by prefix (WHERE HHH LIKE 'prefix%').

For further optimization, delay your search until you have at least 3 characters in your

textbox.text value, to avoid pulling too much text out of the database.

Also, check if your textbox.text value has changed before trying to query,

to avoid wasted work.

The dynamic checking part in this sample app is similar,

but the data base is built up differently (no SQLite, and I should have used a File for the big text.)

https://docs.google.com/document/d/1SakUBoYhbGbH4UtYARNgYoRAAO_dtpLyVp4Jx3Bri2k/edit?usp=sharing

ABG

[ABG]

Here's another optimization to try to speed this up:

Instead of always just asking for all the rows that match your pattern,

first ask for the count of rows that match the pattern

(check SQLite SElect COUNT(*) syntax)

and only do the SELECT HHH if you have no more than

a reasonable number (20?) in your COUNt value.

All the smart voice controlled computers on TV science fiction

shows do this, and it just needs a little more coding.

ABG

[ABG]

I was able to test the autocomplete part using a small state names table,

aia at https://drive.google.com/file/d/1W35hxI7lfQD8p_ne3lF-7E4i-sqtvlva/view?usp=sharing

this example does not load the data base properly at first run, but can be coaxed manually

just to test the autocomplete part until the first run can be fixed.

See attached for relevant query and data base organization.

I used the free benneedum extension.

ABG

[ABG]

An important note for SQLite users ...

I was getting a 

java.lang.NoSuchFieldError: android.os.AsyncTask.SERIAL_EXECUTOR 

error in the AI2 MIT emulator on

SQLite database open, until I switched to a more modern

Android version in a GenyMotion emulator,

using the old style connect and the special Companion

for GenyMotion.

I did not test on any real devices.

ABG

[ABG]

Further note on my example ...

Sometimes things work even though they are wrong.

I forgot to include the column word in my column list in my SELECT

block, yet SQLite brought it back in the result anyway.

I need to fix that.

ABG

[ibrahim bikdashi]

Thank you abraham
I will tested tomorrow and I will reply
Thank you again

[ibrahim bikdashi]

thank you Abraham it works .

thank you for your help and for your support.

[ABG]

Glad to help.

After you test your project in production, could

you post a cleaned up Downloaded Blocks Image

in this thread, for reference?

There's a lot to go wrong at app startup opening

the right SQLite database, and we could use

working examples, covering first run and

subsequent runs.

ABG